Data Warehouses vs Lakes

Why last year's revenue takes five minutes on your Postgres

Streams emit v1 in 300ms and restate v2 when Tuesday's trips land Friday, yet both versions need a home analysts can scan without touching the transactional primary. Your Postgres, a row-oriented transactional database, handles 2k short writes per second gracefully but scans 500M rows for five minutes to sum revenue by product. OLTP, meaning online transaction processing tuned for point reads and writes, stores whole rows with B+ tree indexes. OLAP, meaning online analytical processing tuned for scans and aggregates, stores columns with per-column compression. The storage matches the question shape, so running analytics on the transactional primary contends with live writes while answering slowly.

CDC, meaning change-data-capture tailing the write-ahead log, via Debezium, an open-source log-tailing connector, publishes row changes onward. ELT, meaning loading raw data first then transforming inside the warehouse, via dbt, a SQL-based transformation framework, then models it nightly. Snowflake here means the cloud warehouse service, Redshift means Amazon's warehouse, BigQuery means Google's serverless warehouse.

Keeping cheap raw lakes apart from governed warehouses

The naive lake dumps raw Parquet, meaning a columnar file format, into object storage and calls it a warehouse, which soon yields concurrent overwrites and lawless schemas. A data lake keeps raw dumps in open formats with schema-on-read, meaning structure applied at query time, queried by Trino or Presto, both distributed SQL engines. A warehouse adds governed tables, clustering keys, time travel, access control, and an optimizer. Lakehouse formats such as Iceberg, Delta Lake, and Hudi add snapshot isolation plus schema evolution over the same Parquet files, so one copy serves science, logs, and governed queries.

Why scanning two columns beats reading five hundred million rows

Your revenue query touches 2 of 40 columns across 500M rows. Row-oriented Postgres reads all 40 columns per row, roughly 500M times 2KB or 1TB off disk. Columnar Parquet, a compressed columnar file format, reads 2 columns with dictionary plus run-length compression, roughly 500M times 30 bytes or 15GB, then vectorized SUM runs over compressed runs. Partition pruning, meaning skipping whole date directories, plus zonemaps, meaning min-max stats per row-group, cut further. Like a restaurant storeroom where you pull only the two labeled shelves instead of every box, same question, near 60x less I/O before indexing.

500M rows × 40 cols, query needs (product_id, revenue):
  row store:  500M × ~2KB/row ≈ 1TB scanned, 5+ min, B+tree useless for full scan
  columnar:   2 cols × ~15GB compressed + partition pruning (year=2025 → skip 80%)
              + min/max zonemaps per row-group → skip groups outside range
              ≈ 3–15GB scanned, seconds on separated compute

Parquet extras that matter: dictionary encoding for low-cardinality strings,
  RLE for repeats, footer with per-column stats for predicate pushdown

Moving Postgres rows into Snowflake through CDC, merge, and dbt

Debezium, an open-source log tailer, reads the Postgres write-ahead log and publishes before/after images per commit to Kafka, a distributed log bus. A loader MERGEs on primary key into staging hourly while dbt, a SQL transformation framework, builds tested marts nightly. Rejected alternative: nightly full-table reloads from Postgres. Copying 500M rows near 2KB each moves 1TB every night for hours, and one grain drift breaks every downstream dashboard at once. The working path merges incrementally and guards schema changes as additive-only, since dropping a source column breaks every SELECT-star mart built on it.

Giving raw Parquet snapshots, schemas, and governance with lakehouse formats

Raw Parquet is cheap and lawless: concurrent writers overwrite and readers see halves. Iceberg, Delta Lake, and Hudi, all open table formats adding a metadata layer over Parquet, bring snapshot isolation, ID-based schema evolution, and time travel, so Trino, Spark, and Snowflake share one copy instead of three ETL copies. Optimistic concurrency retries conflicts rather than last-writer-wins.

Modeling marts with grain discipline instead of dashboard arguments

Raw CDC landings mirror Postgres tables with cryptic names and deleted flags, which nobody should query directly. dbt, a SQL transformation framework, builds the contract: staging views that rename and cast, dimension tables for slow-changing entities with SCD-2 versioning, meaning one row per entity version with validity dates, and one fact table per business event. Grain, meaning what one row represents, is the whole game: one row per order-line keeps revenue summable and joins fanout-free.

raw.orders (CDC landing: id, user_id, amt_cents, _deleted, _ts)
  → stg_orders (rename, cents→dollars, filter _deleted=false)
  → dim_users (SCD-2: one row per user version, valid_from/valid_to)
  → fct_orders (one row per order: order_key, user_key, dollars, ordered_at)
Tests that ship: unique+not_null on every *_key, accepted_values on status,
  relationships (fct.user_key → dim.user_key), freshness (< 2h since CDC watermark)

Every doubled-revenue dashboard you will ever debug is a grain violation: order-lines joined to order-level dimensions without deduplication fan out and multiply. Tests on keys plus relationships catch it at build time.

Paying storage pennies and compute dollars without the shock bill

Warehouse cost splits into cheap per-TB-month storage and expensive per-credit-second compute while a warehouse runs. An always-on X-Large serving three analysts costs roughly 32x a right-sized X-Small that auto-suspends after 60 idle seconds. Before rewriting queries, separate warehouses per workload so one runaway never blocks or bills the others, auto-suspend aggressively, and let repeated dashboards hit the result cache instead of credits.

Clustering by date so last Tuesday reads 400 partitions, not 300k

A 50TB events table splits into about 300k micropartitions, meaning small immutable row groups in Snowflake-style warehouses. Ordered by event date, last Tuesday's checkouts read 400 of them; ingested out of order from five sources, every partition holds every day and the same query scans all 300k. Clustering depth measures that rot while automatic clustering rewrites the worst partitions behind the scenes. Key order matters: (date, country) prunes date-first queries while country-first skips nothing, so list WHERE columns in priority order.

Well-clustered by event_date: query 1 day → 400/300k partitions ≈ 0.1% scanned
  ≈ 50TB × 0.1% ≈ 50GB + column pruning (2/40 cols) ≈ ~3GB billed; seconds.
Unclustered (UUID insert order): same query → 300k/300k = 100% scanned
  ≈ 50TB → minutes + the bill that pages finance, not engineering.
Clustering key order matters: (date, country) prunes date-first queries;
  country-first queries skip nothing. Key columns = your WHERE clauses, in order.

Serving two hundred 9am dashboards from cache instead of credits

Two hundred managers opening one revenue dashboard means two hundred identical multi-gigabyte scans without caching. Three layers absorb it: the warehouse result cache where identical SQL within 24h returns free, scheduled BI extracts refreshed hourly into memory, and dbt aggregate marts pre-joining once for many filtered reads. Dashboards parameterize by day rather than second so cache keys match.

Backfilling one poisoned Tuesday without rebuilding the week

A broken feed poisons only Tuesday's partition, but rerunning the week recomputes six good days with six fresh chances to break them. Partition-aligned idempotence, meaning rerunning one date yields the same partition, makes backfill a parameter: rebuild Tuesday to a temp location, atomically swap that partition, and let incrementally reading marts pick up exactly one changed day. Guard with row-count plus revenue-sum assertions before and after, since a backfill halving revenue is worse than the stale partition it replaced.

dbt run --vars '{"run_date": "2025-09-02"}' → rebuilds fct_orders for Tuesday only
  swap: write tuesday_tmp → atomic partition swap → downstream sees one clean cutover
  late-arriving facts: MERGE on (order_key) per partition; replays dedupe, inserts land
  guard: row-count + sum(revenue) assertions before AND after; backfill that silently
    halves revenue is worse than the stale partition it replaced.

Testing the mart at 3am so the boardroom never does

A dbt model, meaning a versioned SQL transformation, without tests is a rumor with a schedule. Ship unique plus not-null on each key, accepted-values on every status, relationship tests on every foreign key, freshness against the CDC watermark, and enforced contracts, meaning schemas that fail builds on breaking change. The model fails at 3am instead of the 9am board meeting, which is the entire point.

Recovering the dbt run that failed halfway through twelve models

The nightly dbt build, meaning a DAG of versioned SQL models, fails on model 7 of 12 at 4am with models 1 through 6 already swapped into production marts. The naive rerun rebuilds all twelve, recomputing six good models with six fresh chances to introduce skew while the SLA burns. The working rerun scopes to the failure plus downstream: models 1 through 6 are immutable partitions already committed, so rerun from model 7 with its upstream selectors, letting models 8 through 12 consume the already-committed outputs. Idempotent model design, meaning rerunning one model yields identical rows, makes partial reruns safe, while non-idempotent appends force full rebuilds. Tag models by freshness tier so the 9am dashboards depend only on models that already succeeded.

DAG: m1 → m2 → ... → m6 ✓ committed → m7 ✗ failed → m8..m12 never ran
  naive: dbt run (all 12, ~3h) → dashboards late + 6 good models recomputed
  scoped: dbt run --select m7+ (failed + downstream, ~1h) → m1..m6 reused as-is
  guard: each model writes *_tmp then swaps atomically; partial failure leaves
    no half-written mart, only a stale-but-consistent one dashboards can still read.

Stop scanning production to answer questions

Two hundred dashboards read from cache at 9am and model 7 reruns scoped in an hour while 1 through 6 stay committed. Yet every dashboard still calls the warehouse synchronously and waits. What happens when one dependency hangs for 5 seconds and every caller above it hangs too?