Data warehouses, lakes & pipelines

A meal-kit subscription company wants to know which ad channel brings customers who are still subscribed six months later. The answer needs data from five systems that were never meant to meet:

None of these can join with another, and the database can't answer questions about the past, since a customer's plan is overwritten when it changes. Running big scans on the production database would also slow the app (OLTP vs OLAP & columnar storage). So the company copies all five into one analytical store, keeps the history, and reshapes it for questions.

There are three common homes for that copy, and they've been converging:

Cloud warehouses separate storage from compute. BigQuery stores tables in its own columnar format and scales storage and compute independently. Snowflake runs queries on virtual warehouses, independent compute clusters that don't affect each other's performance, so the finance team's month-end queries don't slow the dashboards.

A lake is just files, so two jobs writing at once, or a reader arriving mid-write, can see a mess. Table formats such as Apache Iceberg and Delta Lake fix that. Each change writes new files plus a new metadata file, then swaps the table's pointer to it atomically, so readers never see partial changes. Every version is a snapshot, which enables time travel and rollback to a good state.

The traditional order was ETL: extract from the source, transform on the way, load the cleaned result. Cloud warehouses made ELT common: load the raw data as it is, then transform it inside the warehouse with its own compute. The main reason: the raw source data stays available, so transformations can be changed and rerun later. ETL still fits industries such as finance and healthcare, where data must be validated before it enters the warehouse.

The raw layer is a historical archive that allows reprocessing without re-reading the source systems. For the meal-kit company that matters: a bug in the cleaning step is fixed by rerunning it from raw, without asking the ad platforms or the delivery partner for old data again (Batch processing). Changes from PostgreSQL arrive through change data capture rather than nightly full copies (Change data capture).

Marts are usually dimensional models. A fact table records measurement events, one row per event, with numeric measures and a key for each dimension. Dimension tables hold the descriptive context: who, what, where, when. Laid out with the fact in the middle and dimensions around it, the result is called a star schema:

The first decision is the grain: exactly what one fact row represents. The grain is the binding contract of the design, and the usual advice is the lowest level the business process captures, one row per box delivered, not per customer per month. Summaries can always be built from detail, but detail can't be recovered from summaries, and different grains belong in different fact tables.

In March, a customer named Ines switches from the two-person plan to the family plan. PostgreSQL updates the customer row; the old plan is gone. A report of revenue by plan for February would now wrongly count Ines's February boxes as family boxes. The warehouse must keep what was true when.

A type 2 slowly changing dimension does it by adding a row instead of updating one. Each version of the customer gets its own surrogate key, plus three columns: when the row became effective, when it expired, and whether it's current. New facts point to the new key, and old facts keep pointing to the old one:

February's boxes reference key 10441 and stay two-person; April's reference 11893. Table formats such as Delta Lake support the merge operations this needs, and also let a whole table be read as of an earlier snapshot.

The transformations are code: in dbt, each model is a SQL SELECT, and the tool builds tables from them in dependency order. Data changes under that code every day, so the pipeline also tests the data. dbt ships four generic tests, unique, not_null, accepted_values and relationships, and any SQL query that returns failing rows can be a test of its own:

Because each run can be repeated over the same raw partition, a fix or a backfill is simply another run. The company also tracks freshness: if the ad platform export hasn't arrived by 6 a.m., the morning dashboards say so instead of quietly showing yesterday's numbers.

The storage choice matters less than the habits around it. Personal data copied into the warehouse inherits every duty of the source, from access control to deletion requests (Privacy, residency & retention).