10 rows a day · 30 days · three tables
A pipeline small enough to print every row of, and complete enough to have every problem a real one has: duplicates, late arrivals, unparseable numbers, and a report someone needs to reproduce exactly as it looked last Tuesday.
A supplier drops a CSV into a folder every morning. Ten order lines, always. Somebody wrote a script that reads it, tidies it up, and refreshes a dashboard. The script has one table and it overwrites it.
It works until a Thursday when three things happen at once. Finance asks why Tuesday's revenue figure changed. The supplier resends Monday's file with two rows corrected. And a row arrives with the price written as 19,99 instead of 19.99, which the script silently turns into a null that nobody sees for a fortnight.
None of those are exotic. They are the normal weather of data work, and a single table cannot survive any of them, because it holds one state and no history. What it needs is not more cleaning logic. It needs somewhere to put the raw truth, somewhere to put the cleaned version, and somewhere to put the answer, with each one derivable from the one before.
That is the whole of the medallion idea, and the names are only names: bronze for exactly what arrived, silver for the cleaned and conformed version, gold for the shapes the business actually reads. The value is not in the word medallion. It is in the rule that each layer is rebuildable from the one upstream, so a mistake in cleaning costs you a reprocessing job rather than a lost history.
A kitchen with three surfaces. Deliveries land on the first one in their boxes, untouched. Prep happens on the second: washed, trimmed, weighed, labelled. Plating happens on the third. Nobody plates onto the delivery bench, and nobody throws away the boxes until the meal has gone out.
You can always go back a surface. That is the property being bought, and it is worth more than any individual cleaning rule.
Three tables need a table format that can append without rewriting, update without corrupting, and be read while it is being written. A folder of CSVs cannot do that.
Open a Delta table on disk and there is nothing mysterious in it. A directory of Parquet files, and a subdirectory called _delta_log containing numbered JSON files. Each JSON file is one version: a list of files added, files removed, and some statistics.
Reading the table means reading the log first, working out which Parquet files are live at the version you asked for, and then reading only those. That indirection is the entire trick, and everything else follows from it. Writers never edit a Parquet file: they write new ones and record the swap in a new log entry. A reader that started before the swap keeps reading the old list and sees a consistent table.
Three consequences worth naming, because they are the reason this piece uses Delta rather than a folder of Parquet.
Appends are cheap and atomic: a new file, one log entry, and either both happened or neither did. Updates and deletes are possible at all, by rewriting the affected files and recording the swap, which plain Parquet has no way to express. And every past version remains addressable, so versionAsOf gives you the table exactly as it was, which is how Tuesday's number gets reproduced on Thursday.
A library where nobody writes in the books. Corrections are printed as new pages, and a single ledger at the front says which pages are current as of each date. Ask for today and you follow today's ledger entry. Ask for last March and you follow March's, and the pages it points to are still on the shelf.
The ledger is the table. The pages are just storage.
With that in place, the first layer is almost embarrassingly simple, and the discipline is in what it refuses to do rather than what it does.
The bronze table is the raw feed, landed as it came, with every column read as a string so that nothing can fail to parse and nothing can be silently coerced. The only additions are ingestion metadata: which file this row came from, when it was loaded, and which batch it belonged to.
Ten rows a day, appended. After thirty days the table holds three hundred rows and thirty versions, and every one of those days can be isolated by its batch column without reading the file system.
The guard matters more than it looks. Pipelines get re-run: a scheduler retries, an engineer reruns yesterday after a fix, a file gets redelivered. Without a check on idempotency, every retry doubles a day. With one, re-running is free and safe, which is what lets you rerun without a meeting first.
A goods-in book at a warehouse door. You write down what turned up, the time, and the delivery note number. You do not open the boxes, you do not reject anything, and you do not write down what you think should have arrived.
Bronze is a receipt, not an opinion. If a value is wrong, that is information about the supplier, and destroying it at the door means never being able to prove it.
Which leaves everything wrong with the data still in the table. That is deliberate, and it is the next layer's job.
The first of the four things silver does here is typing: turn the strings into the types the rest of the pipeline needs, and decide what happens to rows that will not convert.
Casting in Spark returns null on failure rather than raising, which is the most dangerous default in the whole stack, because a failed cast and a legitimately empty field look identical downstream. So cast into a new column, compare, and route the mismatches to a quarantine table with the reason attached. Nothing is dropped. Somebody can look at the quarantine on Monday and tell the supplier about the commas.
A returns desk rather than a bin. The item that does not scan is put on the shelf behind the counter with a note saying why, and somebody deals with the shelf on Friday. Throwing it in the bin makes the shift look tidier and makes the stock count wrong forever.
A row you cannot process is a fact about your data, and it belongs somewhere you can count it.
The rows that survive are now correctly typed and still full of duplicates, because a supplier resending Monday means Monday's orders are in the table twice.
Duplicates arrive for dull reasons. A supplier resends a file, a retry fires twice, an upstream system emits a correction as a fresh event with the same identifier. Bronze holds all of them, correctly. Silver must hold one.
Which one is a decision, not a technicality. Pick a business key, here the order id, and a rule for choosing among the copies: usually the latest by event time, with ingestion time breaking ties. Number the rows within each key and keep the first.
Then write it with MERGE rather than an append, so a key already in silver is updated in place and a new one is inserted. That single operation is what makes the whole layer re-runnable: run the same day twice and the second run changes nothing.
Note the condition on the update. Without it, a replayed older copy would overwrite a newer correction, and replays are exactly the situation this is meant to survive. With it, the merge is not merely idempotent, it is order-independent: any sequence of the same batches produces the same silver table.
A guest list on the door. The same name turning up three times does not mean three guests. You decide, in advance, that the most recent booking wins, and you write that on the sheet so the person on the door at midnight makes the same call as the one at eight.
Deduplication is only half a mechanism. The other half is a stated rule for which copy is the truth.
One row per order, correctly typed. It is still written in whatever shape the supplier felt like using.
Standardisation is the unglamorous work that decides whether anyone can join your table to anything else. Country written as gb, GB and United Kingdom is three countries to a group-by. Prices in three currencies are not comparable until they are one.
Follow one real row through the whole silver stage, all four operations, with the values at each point.
Every column a string, exactly as the supplier wrote it, including the price with a currency symbol glued to the front and the country in lower case.
Quantity becomes an integer, price a decimal with two places, the timestamp a real timestamp. Had any of those failed, this row would be in quarantine instead and the rest of these steps would never run on it.
This order arrived twice. The copy with the later event time wins and the earlier one is dropped, silently but reproducibly.
Country upper-cased and mapped to its two-letter code, product name trimmed and title-cased, currency converted to the reporting currency at the rate for that date.
A derived line total, the order date pulled out of the timestamp for partitioning, and the region joined in from the country dimension. Nothing here came from the supplier.
One row, one order, fully typed, conformed to the vocabulary the rest of the warehouse uses. This is the table analysts should be allowed to query.
Three teams filing expenses, one in pounds, one in euros, one writing GB and one writing UK. Nobody is wrong. The total is meaningless until someone decides on a single vocabulary and converts everything into it.
Standardisation is the decision, written down as code, about what the warehouse's words mean. It is where most of the arguments in a data team actually live.
One thing has been quietly assumed through all of that: the country dimension and the customer record are fixed. Customers move.
A customer's country changes in June. What was the revenue for EMEA in May?
If the customer dimension holds only the current country, May's answer changes retroactively every time somebody moves, and the same query run twice gives two answers. Overwriting the old value is called a type 1 dimension and it is the right choice for correcting typos and the wrong one for real changes.
A type 2 dimension instead closes the old row and opens a new one, each stamped with the window during which it was true. The customer now has two rows, one flagged current, and any fact can be joined to whichever version was live when it happened.
An address book where you cross out the old address and write the new one, against one where you add a new card and date the old one. Only the second can answer where you posted the parcel in March.
Type 2 is not more thorough bookkeeping, it is a different question being answerable. It also costs a join with two inequality conditions on every fact query, which is why nobody does it for every column.
Four kinds of processing, one table, and still nothing a business user would recognise. Silver is correct, not useful.
Silver has one row per order. Nobody asks a question with one row per order. They ask for revenue by day and region, orders per customer per month, the top ten products this week. Each of those is a gold table with its own grain, built by aggregating silver.
The temptation with three hundred rows is to rebuild the whole thing every night, and for three hundred rows that is correct: it costs nothing and it cannot drift. The reason to learn the incremental version is that at three hundred million rows a full rebuild stops finishing before the morning, and the incremental logic is easier to write on day one than to retrofit.
The subtlety is the list of affected dates. It is not today. A late-arriving row with an event time from three days ago changes a gold row for three days ago, and an incremental job that only ever rebuilds today will leave that number wrong forever while reporting success every night.
A scoreboard rather than a match report. It answers one question instantly and it cannot answer anything else, so you keep the match report and rebuild the scoreboard from it whenever the report changes.
Gold is disposable by design. Anything in gold that cannot be rebuilt from silver is a bug.
Which makes the size of that rebuild window the most consequential number in the pipeline, and it is a number somebody picks.
One dial: how many days back each nightly run reprocesses. At zero, only today's date is rebuilt. At seven, the last week is rebuilt every night, so anything that turned up late for any of those days is picked up.
Below is the thirty day feed with its real distribution of late arrivals. Set the window, run the month, and read the two numbers that matter: how much revenue ends up correctly counted, and how many gold rows had to be rewritten to get it.
At a window of 0, 64 of the month's rows never reach gold, worth £5,229 of £19,240. They sit correctly in silver, where anybody querying silver would find them, and the dashboard built on gold is quietly short by 27 percent. Nothing errors, and that is the failure mode worth fearing, because it is invisible from the inside.
At 3 the miss falls to 41 rows and £3,028. At 7 it falls to 18 rows and £1,249, and the cost is that every night rewrites eight days of gold partitions instead of one.
Past 7 the numbers stop moving, and it is worth understanding why rather than turning the dial further. Those last 18 rows are not slightly late, they are resends of orders more than ten days old, and no window anyone would run nightly will ever catch them. They need a deliberate backfill, triggered by someone noticing, which is an argument for monitoring the age of incoming events rather than for a bigger window.
The right answer is not a number, it is a measurement: look at your own distribution of event time against ingestion time, pick the window that covers the percentile you can defend, and then say out loud that anything beyond it needs a manual backfill.
A monthly ledger you close on the third of the following month. Invoices arriving on the second are counted. One arriving on the tenth needs a decision by a person, and the reason you close at all is that leaving every month open forever means never being able to sign anything off.
The window is not a technical setting, it is a promise about how correct the numbers are and when.
That is the pipeline, complete. What remains is the housekeeping that decides whether it still works in a year.
Ten rows is a Parquet file of a few kilobytes. Three hundred and sixty five nights is three hundred and sixty five of them, plus a log entry each, plus whatever the merges rewrote. The data is trivial and the metadata is not: every query now opens hundreds of files to read a few hundred kilobytes, and each open costs more than the read.
Two maintenance commands handle it. OPTIMIZE rewrites many small files into few large ones, changing nothing about the contents. VACUUM deletes files that no live version needs any more, subject to a retention period, and it is the operation that ends time travel beyond that horizon.
VACUUM is the one to be careful with. Its retention period is the real limit on time travel: once those files are gone, versionAsOf for anything older fails, and the promise made in the first chapter about reproducing Tuesday's number quietly expires. Set it against how far back anyone might actually need to reproduce a figure, not against how much storage costs.
A filing room where every day's paperwork goes into its own envelope. After a year the room is envelopes. Consolidating twelve months into twelve folders changes nothing about what is written, and it is the difference between finding something in a minute and in an afternoon.
Compaction is not an optimisation you do when you have time. On a small daily feed it is the difference between a table that stays usable and one that degrades on a schedule.
Three honest limits before you take this anywhere real.
This shape is overkill for genuinely small and simple problems. Ten rows a day that arrive clean, are never resent, and feed one chart do not need three layers; they need a table and a backup. The architecture earns its cost when there are multiple sources, multiple consumers, or a legal need to explain a number from six months ago.
Deletion for privacy requests is real work rather than a delete statement. A DELETE removes a row from the current version, but the old files still hold it until VACUUM passes the retention horizon, and it is still sitting in bronze. Any credible right-to-erasure process has to reach every layer and account for retention.
And nothing here validates meaning. Every rule in this piece checks form: types, uniqueness, spelling, arithmetic. A supplier who sends ten perfectly formed rows with the wrong prices will pass every check in the pipeline, and the only defences are expectations about volume and distribution, checked and alerted on, which is a separate discipline that starts where this one stops.
Land what arrived and never touch it. Make one row mean one thing. Aggregate into the shape of the question. Keep every layer rebuildable from the one above it, and the pipeline becomes something you can fix rather than something you must not disturb.
The full thirty day feed with every dial exposed: how dirty the source is, how often it resends, how late events run, and how far back each night reprocesses. Run the month and read what reaches gold.
press s for the deeper cuts
At ten rows a day Spark is a lot of machinery. The delta-rs library gives you Delta tables from plain Python: from deltalake import DeltaTable, write_deltalake, then write_deltalake(path, df, mode="append") with a pandas or Polars frame, and DeltaTable(path, version=3).to_pandas() for time travel. Merge is supported through TableMerger. The layer design in this piece is unchanged; only the execution engine differs.
Partitioning writes each distinct value into its own directory, which makes filters on that column skip files entirely and makes rewriting one day cheap. It also means one directory per value, so partitioning by a high-cardinality column like customer id on a small table produces thousands of tiny directories and makes everything slower. A rough rule is at least a gigabyte per partition, which for ten rows a day means not partitioning silver at all until it grows, and using ZORDER instead.
The same layers work with a stream. Structured Streaming with trigger(availableNow=True) against a landing directory gives you exactly-once file processing with a checkpoint doing the idempotency work the batch guard does here, and Delta tables can be read as a stream so silver can be fed by bronze's change feed rather than by a date filter. The design does not change; the trigger does.
Enable delta.enableChangeDataFeed on a table and Delta records which rows were inserted, updated and deleted in each version. Downstream layers can then read only the changes between two versions rather than re-scanning a date range, which removes most of the guesswork about which gold partitions are affected. It costs extra write volume and is worth it once the tables are large.
Delta supports check constraints: ALTER TABLE ... ADD CONSTRAINT positive_qty CHECK (qty_int > 0). A write violating it fails rather than landing bad data, which is the right behaviour for silver and the wrong behaviour for bronze, where the whole point is that nothing is rejected at the door. Constraints and quarantine solve the same problem at different layers with opposite philosophies.
mergeSchema lets a write add new columns automatically. It is convenient and it means a supplier can silently change your table. The safer pattern is to leave it off, let the write fail, and treat a schema change as a code change with a review. Where it is genuinely needed, bronze is the place to allow it, since bronze is meant to accept whatever arrives.
Delta uses optimistic concurrency: two writers both prepare a commit and the loser retries. Appends to different partitions almost never conflict. Two jobs merging into the same table on the same keys will, and the second one raises a concurrent modification error. The usual fixes are partitioning the work so writers do not overlap, or serialising the merges, rather than retrying blindly.