The Rows We Threw Away

Friday, 16:40, quarter close. Finance asks:

“Take out anything the customer sent back within 14 days. Then show me net revenue by brand — for the last three years.”

It sounds like a WHERE clause. The answer: the last 90 days, yes. The other 34 months, no — not at any price.

Nobody deleted anything by mistake. The 2019 pipeline was doing exactly what it was designed to do: every night it pulled orders out of the shop database, computed revenue per brand per day, and wrote four columns into the warehouse. It ran for four years without a failure.

It also threw away, every night, every column nobody had thought to ask about yet — including returned_at. The warehouse holds 1.5M rows of daily_brand_revenue and not a single order line. The source database keeps order-line detail for 90 days and archives the rest.

The T ran before the L. That is the entire bug.

Both pipelines start at the same database and end at the same dashboard. The only structural difference is where the shaping step sits.

ETL (2019)   Shop DB ──extract──▶ Transform (python, own server) ──load──▶ Warehouse (aggregates only) ──▶ Dashboard
                                   └─ 57 unused columns dropped here, forever
             new question ──▶ must re-extract from the SOURCE  ✗ source keeps 90 days

ELT (today)  Shop DB ──extract──▶ Warehouse · RAW layer (all 60 columns) ──SQL──▶ dbt models ──▶ Dashboard
             rule changes ──▶ re-run the model over 3 years of retained raw  ✓

• ETL — Extract, Transform outside, Load. The warehouse receives only what the 2019 version of the business thought was worth keeping.
• ELT — Extract, Load unchanged, Transform inside. The warehouse receives the record; the shaping becomes a view of that record, recomputable on demand.
• Business definitions change far more often than schemas do. ETL bakes today’s definitions into the only copy you have.

Three things flipped the industry to ELT between roughly 2015 and 2020: columnar cloud storage got cheap enough that keeping everything stopped being a budget decision; warehouse compute became elastic and billed separately from storage, making the warehouse the strongest transform engine in the building; and expressing transformations as SQL (dbt) let analysts change business logic in a reviewed pull request instead of filing a ticket against someone’s Python job.

Three numbers worth remembering:

• €12/month — storing 3 years of raw order lines (≈1.1 TB at long-term rates). The insurance premium.
• 40 min — full re-run of the revenue model over all of that raw history.
• 34 of 36 months — the history the ETL pipeline could not restate at any budget.

Keeping the raw layer costs about as much per month as a team lunch. Not keeping it cost a quarter-close.

When ETL is still the right answer

• Personal data that must never land. Mask, tokenise or drop before the load — don’t create a copy you will later have to prove you deleted.
• Enormous volume reduction. Parsing 10 TB of raw logs or images down to 5 GB of events before loading is the transform paying for itself.
• The source won’t let you. On-premise systems, expensive egress, or a vendor API that only returns aggregates.
• Streaming enrichment, where the transform lives in the stream itself (Lesson 03).

In practice most modern pipelines are EtLT: a light “t” before the load (mask personal data, fix broken types, drop true junk) and the heavy T afterwards in SQL, where it can be reviewed and re-run.

1. “Do we keep the raw extract — and for how long?” If the answer is “we only keep the modelled tables,” every future change of definition is a data-recovery project, not a code change.
2. “Where does this transformation actually run — SQL in the warehouse, or a script somewhere?” The second answer usually means one person can change it, and the business logic isn’t visible in a pull request.
3. “If Finance changes a definition today, can we restate three years without asking the backend team?” The single question that tests the raw layer, the backfill, and whether re-runs are safe — all at once.

pip install duckdb, then duckdb lesson02.db. Every statement below has been run as written.

-- STEP 1 — load raw. No cleaning, no filtering, nothing dropped.
CREATE TABLE raw_orders AS
SELECT
  i                                             AS order_id,
  DATE '2023-01-01' + (i % 900)::INT            AS sold_at,
  ['Gucci','Prada','Levis','COS'][1 + (i % 4)]  AS brand,
  round(20 + (i * 7 % 480), 2)                  AS sale_price,
  CASE WHEN i % 9 = 0
       THEN DATE '2023-01-01' + (i % 900)::INT + 5 END AS returned_at
FROM range(1, 20000) t(i);

-- STEP 2 — transform on top. This view IS the T in ELT.
CREATE VIEW fct_revenue AS
SELECT date_trunc('month', sold_at) AS month, brand, SUM(sale_price) AS revenue
FROM raw_orders GROUP BY 1, 2;

SELECT * FROM fct_revenue ORDER BY month, brand LIMIT 4;
-- 2023-01 | Gucci | 48092

-- STEP 3 — Friday 16:40: the rule changes, returns don't count.
CREATE OR REPLACE VIEW fct_revenue AS
SELECT date_trunc('month', sold_at) AS month, brand, SUM(sale_price) AS revenue
FROM raw_orders
WHERE returned_at IS NULL          -- the entire change
GROUP BY 1, 2;

SELECT * FROM fct_revenue ORDER BY month, brand LIMIT 4;
-- 2023-01 | Gucci | 43032   ← three years restated, instantly

-- STEP 4 — now be the 2019 pipeline: throw the column away.
ALTER TABLE raw_orders DROP returned_at;
SELECT * FROM fct_revenue LIMIT 1;
-- Binder Error: Referenced column "returned_at" not found

That error is the whole lesson. In DuckDB it costs one CREATE TABLE to get the column back. In production, the column was in a source system that stopped retaining it 34 months ago.

ETL decides what is worth keeping before anyone has asked the question. ELT keeps the record and decides afterwards — as many times as the business changes its mind.

The raw layer is not hoarding. It is the only thing that makes a data platform correctable, and the next lessons (backfills, tests, slowly changing dimensions) all assume it exists.

Vocabulary