The problem
A number everyone trusts is the real work
A nightly CSV of 50,000 orders; the team wants revenue by day, product and region they can trust.
Every night the shop's system drops a file of the day's orders, about fifty thousand rows. The sales team wants a dashboard of revenue by day, product and region over three years, and it wants the same number for the same question, whoever asks and however often the job runs.
The estimate
Count the rows, then measure the bytes
54.8 million rows. Parquet measured 4.4 times smaller, and the dashboard reads 5 of 13 columns.
Fifty thousand orders a day for three years is about fifty-five million rows. We wrote one real day both ways: 5.4 megabytes as CSV, 1.2 as Parquet, 4.4 times smaller. Over three years that is 5.9 gigabytes against 1.3. And the dashboard needs only five of the thirteen columns, which a column store can read on their own.
The first design
Append each night’s file to one table, and point the dashboard at it
A job inserts each file into one orders table; every chart queries it directly.
The obvious design: a nightly job appends the file to one big orders table, and the dashboard queries it. Two statements, and it works on day one. For a few weeks, nobody has a reason to doubt it.
The cause
Three failures, and the worst is a number that looks fine
A retried load doubles a day; overwritten product details rewrite history; every chart reads everything.
One night the job fails half way and is retried. It inserts the whole file again, and that day's revenue shows twice its true value. No error, just a strange spike. A product moves from the Office category to Home, and reloading the product list changes last year's totals. And every chart scans every row and every column of three years of CSV.
The fix
Bronze keeps it, silver makes it right, gold makes it fast and stable
Raw files kept; MERGE on order_id so re-runs are safe; a type 2 product dimension; a daily table of 21,900 rows.
Bronze keeps every file exactly as it arrived, so any day can be rebuilt. Silver loads each day with a merge on the order id, so running it twice gives the same result. Gold holds a star schema whose product dimension keeps every version of a product with its dates, and a small daily table. A chart now reads well under a megabyte instead of 5.9 gigabytes.
The trade-off
More to run, in exchange for numbers that agree
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 layered design has six tables, four jobs and four checks, where there was one table and one job. Each is small, but each can fail and must be watched. Keeping product history makes joins more careful; overwriting is simpler but changes the past. So we choose per column: a category change matters to last year’s report, a corrected spelling does not.
The finished pipeline
Ordinary steps, done every time, with checks that can stop it
Bronze, idempotent loads, keys, a columnar star, checks before gold, and SQL for analysis. Each links to its lesson.
Here is the whole pipeline. Raw files kept in bronze. Incremental loads that are safe to re-run. Keys, so each order is one row. Columns, a star schema and product history. Checks before gold: the count matches the file, ids are unique, totals are not negative, and the file arrived. If any check fails, gold keeps yesterday’s numbers. Each stage opens the lesson that teaches it.
A real case
At Airbnb, each metric is defined once and shared
Teams gave different answers to the same question, so Airbnb built Minerva: define a metric once, use it everywhere.
At Airbnb, a simple question such as which city had the most bookings last week could get different answers from data science and from finance, because each used slightly different tables and logic. Airbnb built Minerva, where every metric and dimension is defined once, in code, reviewed and versioned. The same definitions feed dashboards, experiments and executive reports. It now holds over twelve thousand metrics. Our gold layer is the same idea, small: one definition, many charts.
Lesson complete
You finished “From a file of orders to a dashboard”
Nicely done.