Lesson 11 · Slide 1

Lesson 11 — Cloud Warehouse

Lift the lakehouse: Snowflake or BigQuery on top of your Iceberg tables — or stay local with DuckDB.

Data Engineering Module 4 Modern Lakehouse

Lesson 11 · Slide 2

Where Module 4 leaves us

L09 + L10

DuckDB queries Parquet. L10 adds Iceberg: schemas, snapshots, ACID. The local laptop is now a real lakehouse.

L11

Same lakehouse, scale-up engine. Snowflake or BigQuery reads the same Iceberg metadata. No copy. No load. New compute, same data.

Lesson 11 · Slide 3

What “cloud warehouse” means here

A hosted, elastic SQL engine where you don’t manage servers and you pay per query or per credit-hour.
Examples: Snowflake, BigQuery, Amazon Redshift, Databricks SQL.
Not the same as cloud-hosted Postgres — those are OLTP-focused. Warehouses are OLAP-focused with columnar storage and massive query-time parallelism.
The development that matters: every major warehouse now supports external tables over Iceberg. Your bucket stays your bucket; the warehouse only adds compute.

Lesson 11 · Slide 4

External tables on Iceberg

Both Snowflake (CREATE ICEBERG TABLE ... CATALOG = ... BASE_LOCATION = ...) and BigQuery (CREATE EXTERNAL TABLE ... OPTIONS (format='ICEBERG', uris=[...])) can register an existing Iceberg table without copying data.

The warehouse engine reads the Iceberg metadata, plans the query against the underlying Parquet files in your S3 / GCS bucket, and returns results.
Storage stays in your bucket. Compute is warehouse-side.
Same Iceberg metadata files read by DuckDB in L10 are now also read by Snowflake or BigQuery — one source of truth, multiple engines.

Lesson 11 · Slide 5

The two-track plan for this lesson

Track A — Cloud

You have a free-tier Snowflake or BigQuery account. The lab walks through registering the Iceberg table, creating daily_counts, and running the materialize-vs-query benchmark on warehouse hardware.

Track B — DuckDB local

You don’t. The lab substitutes warehouse/duckdb_local.py everywhere — same materialize_daily_counts() surface, same benchmark, just running locally. Concepts are identical; the credit-card billing is not.

Lesson 11 · Slide 6

Cost model: what you actually pay for

Pricing axis	Snowflake	BigQuery	DuckDB local
Compute	Credits/hour for warehouse size	Bytes scanned per query (on-demand) or slot-hours (reserved)	None (your CPU)
Storage	External = your S3 cost only	External = your GCS cost only	Local disk
Egress	Bucket egress to compute region	Bucket egress to compute region	N/A

External Iceberg tables move you from “paying for warehouse storage” to “paying only for your own bucket” — often a 10–50× storage cost reduction.

Lesson 11 · Slide 7

External vs materialized

External table

Every query reads the Iceberg metadata + scans Parquet through the warehouse engine. Storage costs are minimal; query costs scale with data scanned. Best for ad-hoc exploration.

Materialized view / CTAS

Pre-compute the result into warehouse-managed storage. Query costs collapse. Storage costs rise. Freshness lag opens. Best for aggregates queried many times.

Rule of thumb: materialise aggregates queried many times; query Iceberg directly for ad-hoc exploration and one-shot dashboards.

Lesson 11 · Slide 8

Snowflake’s external-Iceberg SQL

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

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';

Three DDL statements. From this point the table behaves like any Snowflake table for SELECT.

Lesson 11 · Slide 9

BigQuery’s external-Iceberg SQL

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

One DDL. BigQuery reads the most-recent v<N>.metadata.json matching the glob, then the snapshots it references.

Then the materialised aggregate is the same SQL as Snowflake:

CREATE OR REPLACE VIEW daily_counts AS
SELECT event_date, type, COUNT(*) AS n
FROM my_dataset.events
GROUP BY event_date, type;

Lesson 11 · Slide 10

Track B in 6 lines

from warehouse.duckdb_local import materialize_daily_counts
materialize_daily_counts()
# duckdb_local: daily_counts materialised, N rows.

import duckdb
con = duckdb.connect("warehouse/duckdb_local.duckdb")
con.execute("SELECT * FROM daily_counts LIMIT 5").fetchall()

Same daily_counts shape as Snowflake / BigQuery. Same materialize_vs_query.py benchmark. No cloud account.

Lesson 11 · Slide 11

When cloud warehouse beats DuckDB

Cloud wins when

Data exceeds a single machine’s RAM (tens of GB → TBs).
Many concurrent query authors (5+).
Warehouse-native row-level security, column masking, or audit is required.
Cross-region replication is mandated.
Your team already pays for a warehouse.

DuckDB wins when

Data fits on a laptop or single VM.
1–2 simultaneous query authors.
Cost control matters more than scale.
Development loop tightness (no auth round-trip, no warehouse spin-up).
Prototyping a pipeline that will later move to cloud.

Lesson 11 · Slide 12

The gotcha box — cloud-cost surprises

Querying an external table with SELECT * (no projection, no partition filter) scans every Parquet file. On 1 TB that’s one query that exhausts a significant chunk of your monthly budget.
“Materialized views auto-refresh” is true; how often they refresh is your bill. Default cadence is usually too aggressive for slow-changing data — check and override the refresh schedule.
Storage classes matter: Iceberg on S3 Standard vs S3 IA vs S3 Glacier. Iceberg metadata reads work on Standard; Glacier-tiered data files raise errors at query time.

Lesson 11 · Slide 13

What this lesson does not cover

Snowflake / BigQuery quotas and free-tier limits — read your provider’s documentation.
Cross-account / cross-region setup — same concepts apply; IAM / service account wiring is provider-specific.
Redshift Spectrum / Databricks SQL alternatives — same external-Iceberg pattern; syntax differs.
Spark — when you outgrow DuckDB and don’t want a managed warehouse, Spark’s Iceberg support is the next bridge. L09 mentioned this; a later module will cover it.
L12 picks up observability: OpenLineage hooks across all three engine choices (DuckDB, Snowflake, BigQuery).

Lesson 11 · Slide 14

Recap & what's next

Cloud warehouses read your existing Iceberg without copying — external tables on Iceberg are the modern pattern.
Materialise aggregates, query everything else directly — the external-vs-materialised trade-off is the most important cost lever in a modern lakehouse.
Track B = DuckDB-local equivalence — this module is completable offline; concepts transfer directly to cloud.

Coming up: Module 5 — observability, schema evolution, lineage, and cost governance. The production half of the course.