Lesson 11 Cloud Warehouse
Theme:

Cloud Warehouse

Lesson 11 · Data Engineering with Python · ~15 min read

1. Why lift the lakehouse at all

Lesson 10 produced an Iceberg table on local disk readable by DuckDB. For one engineer on one laptop that is a complete lakehouse — schema enforcement, snapshots, time travel, and ACID writes, all from a single Python process with no daemons and no cloud accounts.

The reason to push the same table into a cloud warehouse is scale and sharing. Ten analysts each running their own ad-hoc queries against a table that has grown past a laptop’s RAM; a regulatory requirement for warehouse-native row-level security and column masking; or simply that your organisation already pays for Snowflake and the lakehouse should be where the analysts live, not where one engineer experiments. The key architectural point is that the move does not require any data migration. The same Parquet files that DuckDB reads in L10 are the files the cloud warehouse reads in L11 — you are adding a compute layer, not copying or reformatting data.

2. What “cloud warehouse” means in 2026

The term covers hosted, elastic SQL engines optimised for OLAP: Snowflake, Google BigQuery, Amazon Redshift, Databricks SQL Warehouse, Microsoft Fabric. They share key traits: columnar storage, massive query-time parallelism, separation of compute and storage, per-query or per-credit-hour billing, and — the development that matters for this lesson — first-class support for external tables over Iceberg.

Until roughly 2023 each warehouse had its own native table format: Snowflake’s internal Parquet, BigQuery’s Capacitor, Redshift’s AQUA format. Today every major vendor reads Iceberg directly, which means the same data file in your S3 or GCS bucket is readable by DuckDB and Snowflake and BigQuery without copies. This is a recent and important shift.

This lesson teaches the pattern (external Iceberg, view on top) using Snowflake’s syntax as the canonical example because Snowflake’s Iceberg support is the most mature in 2026. BigQuery’s syntax is almost identical and is shown alongside. The concepts transfer directly to Redshift Spectrum, Databricks SQL, and any other warehouse that implements the Iceberg spec.

3. The two-track plan

Not every reader has a paid cloud-warehouse account. The lab and homework run on two parallel tracks that produce the same observable outcome: a materialised daily_counts aggregate and a timing benchmark comparing external-table scan cost to materialised-scan cost.

Track A (cloud): Snowflake (default) or BigQuery. The reader uploads the L10 Iceberg directory to S3 or GCS, points the warehouse at it with a few DDL statements, and runs warehouse/snowflake_adapter.py or warehouse/bigquery_adapter.py. The adapters raise a NotImplementedError with a helpful message if the required env vars are unset — you cannot accidentally run them without credentials.

Track B (DuckDB local fallback): the reader stays on the laptop. The same materialize_daily_counts() function lives at sample-pipeline/warehouse/duckdb_local.py and writes to a persistent duckdb_local.duckdb file — a “warehouse” in the literal sense: a separate database file that lives long enough to be queried many times without re-scanning the Iceberg metadata on every access.

Both tracks land at the same daily_counts view shape. Your homework solutions transfer between tracks. If you start Track B and later get a Snowflake account, you re-run Track A’s adapter and the rest of the pipeline does not change.

4. Cost model breakdown

Understanding what you pay for is the most important practical skill in this lesson. There are four billing axes in a cloud-warehouse lakehouse setup.

Compute. Snowflake bills credits per warehouse-size per second a query is running. A small (X-Small) warehouse scanning 1 GB of Iceberg metadata might cost $0.005; a large warehouse doing a full table scan of 1 TB costs measurably more. BigQuery on-demand pricing is simpler: a flat rate per TB scanned — projecting fewer columns reduces the scanned bytes and therefore the bill.

Storage. With external Iceberg the warehouse does not store your data — your bucket does. You pay the S3 or GCS rate, which is typically 5–20× cheaper than warehouse-managed storage of the same data. This single architecture choice (external vs warehouse-native) is the largest cost lever in a modern lakehouse.

Egress. Reading a bucket from a different region than the warehouse incurs cross-region data-transfer charges. The standard production pattern: bucket and warehouse in the same region. Deviating from this adds cost and latency to every query.

Maintenance. Iceberg snapshot expiration and small-file compaction (L10’s maintenance.py) reduce both storage cost and scan cost. Skip them for a few months and queries that used to scan one big file end up scanning hundreds of small ones — multiplying both the bytes scanned and the warehouse-compute cost on every query.

The single most expensive query in a real-world lakehouse: SELECT * FROM events. No projection, no partition filter, on cloud-warehouse compute, against an external Iceberg table. Do not run it in production. Limit access with row-level policies and teach analysts to write WHERE event_date BETWEEN … before they touch the live table.

5. External vs materialized

The defining trade-off of an external-Iceberg setup: when does it pay to materialise a query result, and when is it better to scan the external table each time?

External table (CREATE EXTERNAL TABLE … or CREATE ICEBERG TABLE …): the warehouse runs every query against the underlying Iceberg metadata and the Parquet files in your bucket. Storage cost: only your bucket rate. Query cost: scales with the bytes scanned. Best for ad-hoc queries, exploration, and dashboards that run once per day or less.

Materialized aggregate (CREATE TABLE … AS SELECT … or CREATE MATERIALIZED VIEW …): the warehouse pre-computes the result and stores it in warehouse-managed storage. Query cost collapses to a warehouse-native scan — very fast and very cheap. Storage cost: now you pay the warehouse rate for the materialised result. Freshness lag: the materialised copy is only as fresh as the last refresh. 

-- External (one DDL; queries the Iceberg every time):
CREATE EXTERNAL TABLE events ...;
SELECT event_date, count(*) FROM events GROUP BY event_date;

-- Materialized (one CTAS; queries the materialised copy):
CREATE OR REPLACE VIEW daily_counts AS
SELECT event_date, count(*) AS n FROM events GROUP BY event_date;

Both produce the same result the first time. Repeat the query 1000 times: the external path re-scans Parquet 1000 times; the materialised path scans the small daily_counts table 1000 times at a fraction of the cost. The materialize_vs_query.py benchmark in the lab makes this concrete with real timing numbers.

6. The two-track lab in code

The three materialize_daily_counts() implementations in sample-pipeline/warehouse/ all expose the same function signature and produce the same daily_counts view shape: 

# Track A (Snowflake): registers Iceberg as external, creates the view.
from warehouse.snowflake_adapter import materialize_daily_counts
materialize_daily_counts()    # raises NotImplementedError if env vars are unset.

# Track A alternative (BigQuery): same shape.
from warehouse.bigquery_adapter import materialize_daily_counts
materialize_daily_counts()    # raises NotImplementedError if env vars are unset.

# Track B (DuckDB local): same shape, no cloud account.
from warehouse.duckdb_local import materialize_daily_counts
materialize_daily_counts()    # writes to warehouse/duckdb_local.duckdb

The cloud adapters env-gate at call time and raise a NotImplementedError whose message names the missing env vars and points at duckdb_local.py. That failure is intentional: it stops a Track-B reader from accidentally importing a cloud adapter and waiting for a confusing connection timeout.

The materialize_vs_query.py benchmark runs on both tracks. On Track A the numbers reflect warehouse hardware (the materialised path wins by 5–50× for aggregate queries). On Track B the numbers reflect DuckDB on your laptop — the shape of the trade-off is identical; the absolute values differ.

7. When cloud warehouse beats DuckDB

The practical decision matrix for picking the right engine tier:

Pick cloud warehouse when: data exceeds a single machine’s RAM (tens of gigabytes to terabytes); five or more concurrent query authors; warehouse-native row-level security, column masking, or audit trails are required; cross-region replication is mandated; the data team already pays for a cloud warehouse and introducing a second stack would cost more than it saves.

Stay on DuckDB local when: data fits on a laptop or single VM; one to two simultaneous query authors; cost optimisation matters more than horizontal scale; the development loop tightness (no auth round-trip, no warehouse spin-up latency) improves iteration speed; or you are prototyping a pipeline that will later move to cloud and want zero infrastructure commitment during early development.

For the in-between case — data too big for a laptop but too small to justify a cloud warehouse — Spark or Trino on a single beefy VM are reasonable middle answers. Both read Iceberg natively. L11 does not cover them; they are the “next scale tier” beyond DuckDB when a cloud warehouse is not the right fit.

8. Common pitfalls

Common pitfalls

  1. Bucket and warehouse in different regions. Cross-region egress is billed on every query and adds latency. Always co-locate the bucket and the warehouse region. When moving an existing pipeline to cloud, verify the bucket region first — it is easy to overlook and the bill is a surprise.
  2. SELECT * on an external table with no WHERE clause. With no partition filter, the warehouse scans every Parquet file in the entire Iceberg table. On 1 TB of data that is one query that can exhaust a significant portion of your monthly budget. Always push at least a WHERE event_date >= … predicate.
  3. Missing bucket permissions on the metadata directory. Snowflake and BigQuery need read access to the metadata/ subdirectory as well as the data/ subdirectory. A common mistake is granting the warehouse service principal access only to the data files — this breaks metadata lookups and produces cryptic 403 errors mid-query.
  4. Running expire_snapshots while the warehouse has cached metadata. Expiring a snapshot deletes its data files. If a cloud warehouse cached metadata pointing at those files, queries issued after expiration can return errors mid-result-set. Schedule snapshot expiration during a known low-traffic window and clear warehouse caches afterward.
  5. Auto-refresh cadence too aggressive on materialised views. Default auto-refresh schedules are often set to run on every write event or every few minutes. For a slow-changing aggregate like daily event counts, refreshing more than once per hour wastes warehouse credits without improving data freshness for downstream consumers. Always check the refresh cadence against the consumer’s actual SLO.

References