Append-Only Bronze, Dedup at Read

Level 8: Batch Pipelines & Orchestrationhard28 minappend-only tablesdedup at readROW_NUMBER over ingestion metadatamedallion bronze and silverrebuildable projectionsidempotency pattern selection

Keep the write path a blind append and make the read path idempotent instead, then choose between all three idempotency patterns without guidance.

Sometimes you cannot make the writer idempotent

Implementations 1 and 2 both assume you control the write. Often you do not. Several producers write into the same landing prefix, an at-least-once queue resends whatever it is not sure landed, and a vendor drops the same file twice with a new name. Coordinating a keyed merge across all of that costs more than it is worth, and every merge you add to the hot ingest path is latency the producers pay for.

Implementation 3 gives up on an idempotent writer and moves the property to the reader:

Bronze is append-only and immutable. Every delivery lands, duplicates included, with its ingestion metadata attached: when it was ingested and which file it came from. Nothing is ever updated or deleted in place, so the raw record of what arrived is intact and auditable.
Silver is a projection you rebuild. One row per business key, chosen from Bronze by ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC), rebuilt from scratch on every run. Because it is derived, it is disposable: drop it and rebuild it and you get the same table.

Bronze grows on a rerun. Silver does not change. That is the whole design.

The idempotency contract is scoped

This is the first place in the course where "is this idempotent?" needs a qualifier, because the honest answer is it depends which table you ask about. Bronze legitimately gains rows on every run, by design. Silver is byte-identical after one run or ten.

So the grader for this lesson names its scope: idempotencyTables: ['events_silver']. The double run compares the content of the projection only, and it deliberately ignores Bronze. That is not a grading convenience, it is how a production data contract is written: you promise stability for the tables downstream consumers read, and you say out loud which ones those are. A contract that claimed every table was stable would be false the first time an append landed.

Table

The three idempotent-write implementations. Naming WHEN each applies is the senior-signal answer; knowing only one is not.
pattern	reach for it when	what the run owns	what stays stable on a rerun
delete + insert	a partitioned batch fact, one date per run	one partition of the target	the whole target
merge on a key	a keyed dimension fed by changes or CDC	the keys present in the batch	the whole dimension
append + dedup at read	untrusted or multiple producers, at-least-once queues	nothing, the write is blind	the projection only, never the raw table

The three idempotent-write implementations. Naming WHEN each applies is the senior-signal answer; knowing only one is not.

Picking the winner needs a tiebreaker too

The dedup here has the same trap the merge lesson had. If two copies of an event share an ingested_at, ORDER BY ingested_at DESC alone is a coin flip. Add a second ordering column so the choice is decided: the source file name, an ingestion sequence, or a load id.

The tie is not hypothetical here. One load stamps every row it lands with the same ingested_at, so any event that arrives in two files of the same load ties by construction, and Bronze below holds exactly that case with two copies that disagree on their payload. When tied copies happen to be byte-identical the choice does not matter to the output, but writing the tiebreaker anyway means you never have to prove that they were.

Common mistake: treating Bronze as the table to clean up. Deleting duplicates out of Bronze destroys the audit trail (you can no longer answer "did we receive that file twice?"), reintroduces the write coordination you just avoided, and is not even necessary, because the projection already hides them. Bronze is immutable. Silver is where correctness lives.

Interview nuance: interviewers do not ask "how do you make a pipeline idempotent?" once and stop. They ask you to choose under constraints: multiple producers, a queue that resends, a source with no reliable key, a partition that must land atomically. Naming which of the three patterns fits and why is the senior-signal answer. Knowing exactly one of them and forcing it onto every problem is the junior one.

On a real platform this differs. This is the lakehouse posture, and the vocabulary is medallion: Bronze raw, Silver conformed, Gold aggregated. The projection is a dbt incremental or table model rebuilt from Bronze, or an Iceberg or Delta MERGE INTO that maintains it in place. The ingestion metadata that decides the winner is real: Databricks Auto Loader and Snowpipe both stamp a file name and a load time on every row for exactly this reason.

Sample data for this example

CREATE TABLE demo_bronze (event_id TEXT, ingested_at TEXT, source_file TEXT, duration_ms INTEGER);
INSERT INTO demo_bronze (event_id, ingested_at, source_file, duration_ms) VALUES
  ('E-1006', '2026-03-14 03:00:00', 'events-0142.json',   0),
  ('E-1006', '2026-03-14 04:30:00', 'events-0142.json', 150),
  ('E-1007', '2026-03-14 03:00:00', 'events-0142.json', 260),
  ('E-1007', '2026-03-14 04:30:00', 'events-0142.json', 260);

Worked example (SQL)

-- Bronze keeps every copy. The read decides which one Silver gets, and says so out loud.
SELECT event_id, ingested_at, source_file, duration_ms,
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC, source_file DESC) = 1
            THEN 'kept in silver'
            ELSE 'superseded copy, stays in bronze' END AS read_verdict
FROM demo_bronze
ORDER BY event_id, ingested_at DESC;

Apply

Your turn

The task this lesson builds to.

Write a script that appends events_batch to events_bronze unconditionally, then rebuilds events_silver as exactly one row per event_id keeping the copy with the latest ingested_at, over events_bronze(event_id, event_ts, ingested_at, source_file, payload_json), events_batch with the same columns, and events_silver(event_id, event_ts, payload_json).

Do not filter the append: Bronze takes every delivery, duplicates included, and it will legitimately be larger after a rerun. events_silver is the table that must be identical after one run or two, so rebuild it from scratch rather than adding to it. One event landed twice inside a single load, so its two copies share an ingested_at and disagree on the payload: break that tie with source_file DESC.

3 hints and 4 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a script that leaves fct_page_views(view_id, viewed_at, path) holding exactly one row per view_id, carrying the values from the copy with the latest ingested_at, over raw_page_views(view_id, viewed_at, ingested_at, source_file, path) and page_view_batch with the same columns.

page_view_batch redelivers rows raw_page_views already holds, and raw_page_views is the append-only landing zone, so every row it starts with must still be there when your script finishes. One view landed twice inside a single load, so its two copies share an ingested_at and disagree on path: the later source_file wins that tie. Choose the idempotency pattern yourself. The grader only checks the goal state, twice.

3 automated checks are waiting in the workspace.