The problem

A number everyone trusts is the real work

orders_2026-11-15.csv, 50,000 rows, 13 columns (7 shown)order_idorder_tsproduct_idproduct_nameregionstatustotalORD-010000002026-11-15 09:41:10P01031Office chair 1031Eastpaid142.41ORD-010000012026-11-15 08:49:37P00937Backpack 937Eastpaid192.69ORD-010000022026-11-15 19:48:09P00646Water bottle 646Westpaid183.65Wanted: a sales dashboardRevenue by day, product and region, for the last 3 yearsThe same number for the same question, whoever asks and however often the job runsGetting data onto a dashboard is easy. Getting a number everyone trusts onto it is the real work.Every night the shop's system drops a CSV of the day's orders into storage: about 50,000 rows. The sales team wants revenue by day,product and region over 3 years of history, and it wants to trust it: the same answer from every chart, every day. This case loads the file theobvious way, watches it break, and rebuilds it with the layers and loads from this course.

A nightly CSV of 50,000 orders; the team wants revenue by day, product and region they can trust.

The estimate

Count the rows, then measure the bytes

Rows50,000 a day × 1,095 days = 54.8 million rowsOne day, measuredCSV 5.4 MBParquet 1.2 MB, 4.4 times smaller3 yearsCSV 5.9 GB, Parquet 1.3 GBDashboard5 of 13 columns: date, product, region, status, total. In Parquet they are 41% of each file.54.8 million rows is small for a warehouse, but every byte a chart reads is paid for in time and money.Written as Parquet, one day measured 4.4 times smaller than the CSV, because each column is stored together and compresses well. And acolumn store reads only the columns a query names: the 5 the dashboard uses are 41% of the file. These two facts decidemost of the design. The sizes are real: one day of generated orders, written both ways for this lesson.

54.8 million rows. Parquet measured 4.4 times smaller, and the dashboard reads 5 of 13 columns.

The first design

Append each night’s file to one table, and point the dashboard at it

Nightly CSVINSERTordersone big tableSELECT ...Dashboard-- the nightly jobINSERT INTO ordersSELECT * FROM read_csv('orders_*.csv');-- every chartSELECT ... FROM orders GROUP BY ...;The obvious design: append each night’s file to one table, and point the dashboard at it.A scheduled job inserts each new file into one orders table, with the product name and category copied onto every row as theyarrive. Every chart on the dashboard runs its own query against that table. It is quick to build and works on the first day, andfor a few weeks nobody has a reason to doubt it.

A job inserts each file into one orders table; every chart queries it directly.

The cause

Three failures, and the worst is a number that looks fine

A re-run doubles a daythe job fails half way, is retried, and inserts the whole file again: 13.5 becomes 27 million dollarsHistory rewrites itselfa product moves from Office to Home; reloading products changes last year’s totalsEvery chart reads everything5.9 GB of CSV rows behind each refresh, 13 columns when it needs 5Three failures, and the dangerous one is the quiet one: a number that is wrong but looks fine.The double-counted day shows revenue of 27 million dollars instead of 13.5. Nobody gets an error; someone in sales spots a strange spiketwo weeks later. Overwriting product details changes the past, so a report run in March disagrees with the same report run in June.And every chart scans the whole history in a format built for spreadsheets. Each one breaks trust in a different way.

A retried load doubles a day; overwritten product details rewrite history; every chart reads everything.

The fix

Bronze keeps it, silver makes it right, gold makes it fast and stable

Bronzeraw files, as deliveredkept for ever, by load dateSilvertyped, one row per orderMERGE on order_idGoldstar schema + daily totalsproduct dimension, type 2MERGE INTO silver.orders tUSING new_orders s ON t.order_id = s.order_idWHEN MATCHED THEN UPDATE SET *WHEN NOT MATCHED THEN INSERT *;Bytes read for 90 days by region, to scale (log)CSV, every row and column, 5.9 GBParquet, 5 columns, 90 days: 45.3 MBGold daily table: 0.9 MBBronze keeps what arrived, silver makes it correct, gold makes it fast and stable. Each fixes one failure.Raw files are kept untouched, so any day can be rebuilt. Silver merges on order_id, so running a day twice gives the same total. Goldholds a star schema whose product dimension keeps each version of a product with its valid dates (type 2), so history stays as it was,plus a daily table by category and region: 21,900 rows for 3 years. A chart now reads 0.9 MB, not 5.9 GB.

Raw files kept; MERGE on order_id so re-runs are safe; a type 2 product dimension; a daily table of 21,900 rows.

The trade-off

More to run, in exchange for numbers that agree

First designLayeredTables16Nightly jobs14Checks that can stop a load04Type 2 product historypast reports stay the same, butevery join must pick the right versionOverwrite (type 1)simpler, but the past changesChosen: layered, with type 2 for product category and price; type 1 for spelling fixes to names.More tables and jobs to run, in exchange for numbers that agree with each other and with themselves.The layered design has six tables, four jobs and four checks where there was one of each. Each is small and does one thing, but eachcan fail and must be watched. Type 2 keeps history honest at the cost of more careful joins. The choice is made per column: acategory change matters to last year’s report, a corrected spelling does not.

6 tables, 4 jobs and 4 checks instead of one of each. Type 2 where the past matters, type 1 where it does not.

The finished pipeline

Ordinary steps, done every time, with checks that can stop it

1 Land raw filesbronze, by load dateLesson 9: lakehouses2 Incremental, idempotentwatermark + MERGELesson 10: pipelines3 Keys and one row eachorder_id, product_idLesson 1: keys4 Columns, star, type 2Parquet, dimensionsLesson 8: columns and stars5 Checks before goldcounts, unique, freshLesson 13: trust6 Window functionsrunning totals, ranksLesson 4: SQLEach stage opens the lesson that teaches it. Most of the trust comes from the two in colour.A pipeline people trust is mostly ordinary steps, done every time, with checks that can stop it.Before gold is rebuilt, four checks run: the row count matches the file, order_id is unique, no total is negative, and today’s file hasarrived. If any fails, gold keeps yesterday’s numbers and someone is alerted, which is better than a confident wrong number. Eachstage links to the lesson that teaches it.

Bronze, idempotent loads, keys, a columnar star, checks before gold, and SQL for analysis. Each links to its lesson.

A real case

At Airbnb, each metric is defined once and shared

“Which city had themost bookings last week?”Data Science and Financesometimes gave different answersMinervametrics and dimensions definedonce, in code, reviewed,versioned and backfilledUsed everywheredashboards, experiments,executive reports, notebooksScaleover 12,000 metrics and 4,000 dimensions, from more than 200 data producersOur caseone gold table defines revenue once; every chart reads it, so every chart agreesAt Airbnb, the same question got different answers until each metric was defined once and shared.Airbnb’s data teams found that simple questions, such as which city had the most bookings last week, got different answers fromdifferent teams, because each used slightly different tables and logic. They built Minerva, where metrics are defined once in code,reviewed and versioned, and served to every tool. Our gold layer is the same idea at small scale: one definition, many charts.

Teams gave different answers to the same question, so Airbnb built Minerva: define a metric once, use it everywhere.

Lesson complete

You finished “From a file of orders to a dashboard”

Nicely done.

Open in a new tab