Lab 11 — Lift the Lakehouse to a Cloud Warehouse (Track A) or Stay Local (Track B)

Lesson 11 · Data Engineering with Python · ~45 min

By the end of this lab you will have materialised a daily_counts aggregate view on top of the L10 Iceberg table, run the materialize_vs_query.py benchmark, and confirmed the result with a direct query. Pick one track and finish it. Both tracks land at the same observable result, so all subsequent lessons build on either outcome.

Prerequisites

• L10 lab complete — the Iceberg default.events table at data-engineering/sample-pipeline/warehouse/ is populated and readable by DuckDB.
• Python 3.10+ virtual environment with the course requirements.txt installed.
• One terminal you can run python -m … in from data-engineering/sample-pipeline/.
• Track A only: a free-tier Snowflake or BigQuery account, and the L10 Iceberg directory uploaded to an S3 or GCS bucket in the same region as your warehouse (see Step 1A below).
• Track B only: nothing additional — DuckDB is already installed from L09.

Choosing your track

Track A — Step 1A: Upload the Iceberg directory

One-time preparation. From the repository root, sync the local Iceberg warehouse to your bucket:

# Snowflake / S3
aws s3 sync data-engineering/sample-pipeline/warehouse/ s3://my-bucket/warehouse/
export ICEBERG_S3_PATH=s3://my-bucket/warehouse/events

# BigQuery / GCS
gsutil rsync -r data-engineering/sample-pipeline/warehouse/ gs://my-bucket/warehouse/
export ICEBERG_GCS_PATH=gs://my-bucket/warehouse/events

Verify the upload: confirm that s3://my-bucket/warehouse/default/events/metadata/v1.metadata.json (or the GCS equivalent) exists. The warehouse reads the metadata directory; if it is missing, the DDL step will fail with a 403 or “table not found” error.

Track A — Step 2A (Snowflake): create the external volume and catalog integration

In the Snowflake console or via snowsql, run these three DDL statements once per account (they are idempotent with CREATE OR REPLACE):

CREATE EXTERNAL VOLUME iceberg_vol
  STORAGE_LOCATIONS = (
    (NAME = 'my_s3', STORAGE_PROVIDER = 'S3',
     STORAGE_BASE_URL = 's3://my-bucket/warehouse/',
     STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::ACCOUNT_ID:role/SnowflakeRole'));

CREATE CATALOG INTEGRATION iceberg_cat
  CATALOG_SOURCE = OBJECT_STORE
  TABLE_FORMAT = ICEBERG
  ENABLED = TRUE;

CREATE ICEBERG TABLE events
  EXTERNAL_VOLUME = 'iceberg_vol'
  CATALOG = 'iceberg_cat'
  BASE_LOCATION = 'warehouse/events';

After this step, SELECT count(*) FROM events in Snowflake should return the same row count as your L10 DuckDB query. If it returns 0, verify that the IAM role has s3:GetObject and s3:ListBucket on the entire s3://my-bucket/warehouse/ prefix — including metadata/.

Track A — Step 2A (BigQuery): create the external table

In the BigQuery console or via bq CLI, run:

CREATE OR REPLACE EXTERNAL TABLE my_dataset.events
WITH CONNECTION my_conn
OPTIONS (
  format = 'ICEBERG',
  uris = ['gs://my-bucket/warehouse/events/metadata/v*.metadata.json']
);

The uris glob resolves to the most-recent v<N>.metadata.json. Verify with SELECT count(*) FROM my_dataset.events.

Track A — Step 3A: Set env vars and run the adapter

Export the env vars listed in warehouse/snowflake_adapter.py (or the BigQuery equivalents):

export SNOWFLAKE_ACCOUNT=xy12345.us-east-1
export SNOWFLAKE_USER=my_user
export SNOWFLAKE_PASSWORD=...
export SNOWFLAKE_WAREHOUSE=COMPUTE_WH
export SNOWFLAKE_DATABASE=my_db
export SNOWFLAKE_SCHEMA=public
export ICEBERG_S3_PATH=s3://my-bucket/warehouse/events

cd data-engineering/sample-pipeline
python -m warehouse.snowflake_adapter
# Expected: snowflake: daily_counts view materialised.

Or for BigQuery:

export GOOGLE_CLOUD_PROJECT=my-project
export BIGQUERY_DATASET=my_dataset
export ICEBERG_GCS_PATH=gs://my-bucket/warehouse/events

cd data-engineering/sample-pipeline
python -m warehouse.bigquery_adapter
# Expected: bigquery: daily_counts view materialised.

In the warehouse console, daily_counts should now appear as a view over events. Confirm with:

-- Snowflake or BigQuery:
SELECT event_date, type, n
FROM daily_counts
ORDER BY event_date DESC
LIMIT 10;

Track B — Step 1B: Materialise locally

No env vars needed. Run from data-engineering/sample-pipeline/:

cd data-engineering/sample-pipeline
python -m warehouse.duckdb_local

Expected output:

duckdb_local: daily_counts materialised, N rows.

A file warehouse/duckdb_local.duckdb now exists. This is the persistent “warehouse” — open it any time without re-scanning the Iceberg metadata:

import duckdb
con = duckdb.connect("warehouse/duckdb_local.duckdb")
print(con.execute(
    "SELECT * FROM daily_counts ORDER BY event_date DESC LIMIT 10"
).fetchall())

Both tracks — Run the benchmark

This is the key step that frames the external-vs-materialised trade-off. Run from data-engineering/sample-pipeline/:

cd data-engineering/sample-pipeline
python -m warehouse.materialize_vs_query

Expected output (your numbers will differ by track and machine):

external (iceberg scan): 0.842s
materialized (CTAS):     0.013s
speedup: 65.0x

This is the number that justifies materialising aggregates. On a real cloud warehouse the ratio shifts — warehouse-native compute is faster at scanning external Iceberg than DuckDB is — but the materialised path still wins by 5–50× for aggregate queries.

Both tracks — Confirm the daily_counts view

Track A: from the warehouse console, run:

SELECT * FROM daily_counts
WHERE event_date >= current_date - 7
ORDER BY event_date DESC;

Track B: from Python:

import duckdb
con = duckdb.connect("warehouse/duckdb_local.duckdb")
print(con.execute(
    "SELECT * FROM daily_counts ORDER BY event_date DESC LIMIT 14"
).fetchall())

Both should return rows in the shape (event_date, type, n) — the same aggregate regardless of engine.

What you should leave with

Troubleshooting