ETL and the data warehouse - yesterday, correctly

Why this design

Every company past a certain size has a warehouse that finance, product and leadership read every morning, fed by nightly jobs pulling from the production databases and a dozen SaaS tools. The naive version runs "SELECT everything new since last time" and appends it, and within a month it has duplicates from a retried job, gaps from a row that committed late, and a revenue number that disagrees with the billing system. Interviewers use this question to see whether you design a run as an idempotent function of a date, whether you know why loads overwrite partitions instead of appending, how late-arriving data is handled, and whether quality checks gate publication. Data engineers will recognise every failure on this board from an incident.

What you will learn

• Why a run is keyed by a logical date, and rerunning it any number of times produces the same partitions.
• Why extraction uses a window that reaches back past the watermark, and what the lookback costs: four days of changes re-read every night.
• Why raw extracts are immutable per run, and transforms can be replayed from them.
• How a slowly changing dimension (type 2) keeps "the customer’s country at the time of the order".
• Why partition overwrite makes retries safe and append makes them duplicates.
• What the quality gate checks, and why a failed check blocks the swap instead of logging a warning.
• The honest weakness: updated_at cannot see hard deletes or anything older than the lookback.

How to read this board

1. Diagram, left to right: EXTRACT (a window on somebody else’s database), ORCHESTRATE (one run per logical date), TRANSFORM (dimensions first, then facts, then the gate), SERVE (the warehouse). Press play for the walkthrough.
2. Below it, BACKFILL: the same DAG pointed at old dates.
3. Then the DATA MODEL: a star schema and the run bookkeeping.
4. Pages 1-3 · Requirements, Capacity and Storage: 60 M changed rows a day, read four times over.
5. Page 4 · Availability - the 07:00 deadline and the four hours of slack that make one full retry possible.
6. Page 5 · How it works, then Page 6 · Deployment, where a transform change is a backfill.

The warehouse is the analytical copy of the business: orders, customers, products, payments, marketing spend, modelled for questions like "revenue by region by week" rather than for serving a website. It is fed in batch from systems it does not own.

Ask these before designing

• How fresh must it be? "Yesterday by 07:00" is a batch warehouse. "Within five minutes" is a streaming system with different failure modes; do not build that by running batch every five minutes.
• Does the source hard-delete? If yes, an updated_at extract will never see it, and you need either soft deletes in the source, a periodic full key comparison, or change data capture.
• Can the source tolerate us? A full table scan of the orders primary at 01:00 is somebody’s outage. Extract from a replica, throttled.
• Which time zone is "yesterday"? A logical date in UTC and a business day in New York disagree for five hours, and every report must say which.
• Is history rewritten? If a refund ten days later should change the original order’s revenue, the fact table must be revisable that far back - or refunds must be facts of their own, which is usually the better answer.

Functional

• Extract changed rows from 40 source tables and 6 SaaS APIs nightly.
• Land raw extracts immutably; stage them typed and deduplicated by key.
• Maintain type-2 dimensions (customer, product) and daily-partitioned facts.
• Reprocess the last 3 logical dates every night to absorb late data.
• Gate publication on data quality checks; publish by atomic partition swap.
• Backfill any date range on demand with the same code.

Non-functional

• Ready by 07:00 UTC, 99% of days.
• Idempotent: any run, rerun any number of times, produces identical partitions.
• Never publish a partition that failed a check.
• Source impact capped: at most 50 MB/s read from any replica.
• Revenue reconciles to the billing system within 0.01% for every published date.

Out of scope

Real-time dashboards, reverse ETL, the BI tool itself, ML feature stores.