Data PlatformZoom level 3
Stage 10: duplicates, inside the batch and against silver
The tenth stage removes rows that say the same thing twice. It works in two steps. First it removes repeats among the new rows. Then, for datasets that only append, it removes new rows whose key is already in silver.
Sources repeat themselves all the time: a file is sent twice, a stream replays an hour after a restart, a change feed delivers the same change in two files. Without this stage every repeat would be counted twice in every total built on silver.
The five ways rows are grouped
The stage always does the same thing: it puts rows into groups, numbers the rows inside each group, and keeps number 1. What differs is how the groups are formed and which row gets number 1. The code checks the five cases in this order and takes the first that applies.
| When | A group is | The row kept | Why |
|---|---|---|---|
dedup.mode is none | Every row by itself | Every row | The dataset asked for no removal. |
dedup.mode is full_row | Rows equal in every contract column | The first to arrive | Only exact copies are repeats. |
| The history type is type 2 | Rows with the same key and the same order value | The first to arrive | A type 2 dataset must keep every distinct change. Two rows with the same key and the same change sequence are one change sent twice. |
dedup.mode is business_key_latest | Rows with the same key | The one with the highest order value. On a tie, the last to arrive. | Only the latest state matters. |
Otherwise, which means business_key_first | Rows with the same key | The first to arrive | An event happens once. Anything after the first is a repeat. |
The third line is easy to miss. Six of the nine tyre datasets say business_key_latest, and four of those keep type 2 history. If the stage took the mode literally for them, a batch holding three changes to one order line would be cut down to the last change, and the history stage would never see the other two. So for type 2 the stage ignores the word latest and keeps every distinct change.
A worked example: press readings
Press readings are events. Their manifest says to keep the first row per reading_id and to look back two days in silver.
data-platform-catalog/slices/02-tyre/manifest/datasets/press_stream.press_reading.yaml:18The press reading manifest: first arrival per reading id.dedup: {mode: business_key_first, business_keys: [reading_id]}data-platform-catalog/slices/02-tyre/manifest/datasets/press_stream.press_reading.yaml:29The same manifest: how far back silver is checked for replays.late_lookback_days: 2
The run's business date is 10 November. Silver already holds reading R-501, loaded in the window of 9 November. Four rows arrive, in this order.
| reading_id | press_id | temperature_c | Arrival order | Result |
|---|---|---|---|---|
| R-502 | PR-07 | 171.2 | 1 | Kept. |
| R-502 | PR-07 | 171.2 | 2 | Dropped. It is the second row of its group. |
| R-501 | PR-07 | 170.9 | 3 | Replayed. The key is in silver within the last two days. |
| R-503 | PR-08 | 168.4 | 4 | Kept. |
Two rows go on. The counts are duplicates.dropped = 1 and duplicates.replayed = 1. In the recorded 14-day run of the tyre company at full scale (4 November 2026, local Spark, in docs/W1C-SILVER.md), day 13 replayed part of the stream, and this stage dropped 19,440 replayed readings, leaving silver equal to the oracle.
The settings that drive it
| Setting | What it means | Allowed values | Default |
|---|---|---|---|
dedup.mode | How repeats inside the batch are recognised. | business_key_latest, business_key_first, full_row, none | none |
dedup.business_keys | The columns that identify a row for this stage. | A list of contract columns | The contract's primary key |
dedup.order_by, or load.history_order_by when set | The column that says which row is later. | A contract column | Empty |
load.late_lookback_days | How many days of silver are checked for a replay, counted back from the run's business date. | A whole number of days | Not set. All of silver is checked. |
processor_groups.dedup | A dataset setting in the metadata store. The value disabled makes the stage runner skip this stage for the dataset. | The settings registry allows enabled, disabled, opt_in and dataset_decides. The plan acts only on disabled. | Not set, so the stage runs |
This table shows what each tyre dataset declares and which of the five cases it therefore takes.
| Dataset | Mode | History | Case taken | Checked against silver |
|---|---|---|---|---|
| Dealers | business_key_latest | type 2 | Same key and order value | No |
| Materials | business_key_latest | type 2 | Same key and order value | No |
| Presses | business_key_latest | type 2 | Same key and order value | No |
| Sales order lines | business_key_latest | type 2 | Same key and order value | No |
| Deliveries | business_key_latest | type 1 | Latest per key | No |
| Dealer sell-out | business_key_latest, ordered by file name | snapshot | Latest per key | No |
| Production records | business_key_first | append | First per key | Yes, all of silver |
| Inspection results | business_key_first | append | First per key | Yes, all of silver |
| Press readings | business_key_first | append | First per key | Yes, the last 2 days |
The code
src/dataplatform/stages/rows.py:31The whole stage: five ways to rank, then the check against silver.def duplicates(df: DataFrame, ctx: StageCtx) -> Out:
"""Within the batch: business_key_first keeps the first arrival per key, business_key_latest the latest by the
order column, full_row drops identical rows. A type 2 history keeps every distinct change (the same key and order
value twice is one change). Against silver: a key already loaded (within the late lookback) is a replay (DUP-04)
for kinds that only append."""
plan = ctx.plan
keys = list(plan.dedup_keys)
arrival = arrival_order(df)
if plan.dedup_mode == "none":
ranked = df.withColumn("_rn", F.lit(1))
elif plan.dedup_mode == "full_row":
cols = [c.name for c in plan.kept]
ranked = df.withColumn("_rn", F.row_number().over(Window.partitionBy(*cols).orderBy(*arrival)))
elif plan.history == "scd2":
part = Window.partitionBy(*keys, plan.order_by).orderBy(*arrival)
ranked = df.withColumn("_rn", F.row_number().over(part))
elif plan.dedup_mode == "business_key_latest":
order = [F.col(plan.order_by).desc_nulls_last()] if plan.order_by else []
latest = Window.partitionBy(*keys).orderBy(*order, *arrival_desc(df))
ranked = df.withColumn("_rn", F.row_number().over(latest))
else: # business_key_first
ranked = df.withColumn("_rn", F.row_number().over(Window.partitionBy(*keys).orderBy(*arrival)))
kept, dropped = ranked.where("_rn = 1").drop("_rn"), ranked.where("_rn > 1").drop("_rn")
tally = {"dropped": dropped}
if plan.history == "append" and ctx.current is not None and plan.dedup_mode != "none":
seen = recent(ctx.current, plan, ctx.as_of).select(*keys).distinct().withColumn("_seen", F.lit(True))
marked = kept.join(seen, keys, "left")
kept = marked.where(F.col("_seen").isNull()).drop("_seen")
tally["replayed"] = marked.where(F.col("_seen").isNotNull()).drop("_seen")
return Out(kept, tally=tally)src/dataplatform/stages/common.py:104What first to arrive means: the transport columns the frame has, oldest first.ARRIVAL = ("_feed_pos", "_seq", "_arrived_at", "_committed_at", "_source_file", "_batch_key")
def arrival_order(df: DataFrame) -> list[Column]:
"""How rows arrived, oldest first: feed position, stream sequence, arrival time, file name, batch."""
return [F.col(c).asc_nulls_last() for c in ARRIVAL if c in df.columns] or [F.lit(0)]- Arrival order.
arrival_orderbuilds a sort order from whichever of six columns the frame has: the position in the change feed, the stream sequence, the arrival time, the commit time, the file name and the batch key. Missing values sort last. If the frame has none of them, a constant is used and the choice among equal rows is left to the engine. - Rank. Each of the five cases adds a working column
_rnwithrow_number()over a window. A window is a grouping with an order inside each group.partitionBynames the group andorderBythe order. - The latest case. The order is the order column from highest to lowest, with missing values last, and then arrival from newest to oldest. So if the same change is delivered twice, the later delivery wins the tie.
- Keep rank 1.
keptis the rows with_rn = 1.droppedis the rest, returned in the tally. - Decide whether to look at silver. Three things must hold: the history type is append, silver exists, and the mode is not
none. - Take the recent keys.
recentnarrows silver to the lookback. The key columns are selected, made distinct, and given a marker column_seen. - Left join. The kept rows are joined to those keys. A left join keeps every new row and fills
_seenonly where the key was found. - Split on the marker. Rows where
_seenis null are new and go on. Rows where it is set are replays and go into the tallyreplayed.
src/dataplatform/stages/rows.py:22Which part of silver counts as already loaded.def recent(current: DataFrame, plan: SilverPlan, as_of: str) -> DataFrame:
"""Silver rows inside the dataset's late lookback (by bronze window), or all of silver when it has none."""
if plan.lookback_days is None:
return current
since = (date.fromisoformat(as_of) - timedelta(days=plan.lookback_days)).isoformat()
return current.where(F.col("_window") >= F.lit(since))If the dataset has no lookback, all of silver is used. Otherwise the function takes the run's business date, subtracts the lookback days, and keeps the silver rows whose _window is on or after that date. _window is the day of the bronze batch that carried the row. It is not the time of the event. Both sides are dates written as text in the same form, so comparing the texts compares the dates.
The lookback is a trade. Checking every new reading against the whole readings table would grow more expensive every day. In the recorded run that table grew by 622,080 rows a day. Two days is enough for a stream that replays recent data, and the join stays the same size from day to day. The price is that a replay of older data is not recognised.
Why only append datasets are checked against silver follows from the next stage. For history kinds, the history stage lays the new rows out together with what silver holds, and a change seen twice produces the same row key and changes nothing. An appended row has no such second chance.
How it is tested
tests/dataplatform/test_silver_stages.py has five cases for repeats inside a batch and three for the check against silver. In the first table each row is an id, a value, an order value and a feed position.
tests/dataplatform/test_silver_stages.py:274The five cases inside a batch. The last is the type 2 case: three rows in, two distinct changes out. ("no dedup", {}, [("a", 1, 1, 1), ("a", 1, 1, 2)], [("a", 1), ("a", 1)]),
("first arrival per key (DUP-04)", {"dedup": {"mode": "business_key_first", "business_keys": ["id"]}},
[("a", 2, 1, 2), ("a", 1, 1, 1), ("b", 3, 1, 3)], [("a", 1), ("b", 3)]),
("latest by the order column", {"kind": "transaction",
"dedup": {"mode": "business_key_latest", "business_keys": ["id"],
"order_by": "seq"}},
[("a", 1, 2, 1), ("a", 2, 1, 2)], [("a", 1)]),
("identical rows", {"dedup": {"mode": "full_row", "business_keys": ["id"]}},
[("a", 1, 1, 1), ("a", 1, 1, 2), ("a", 2, 1, 3)], [("a", 1), ("a", 2)]),
("type 2 keeps every distinct change, drops a change sent twice",
{"kind": "master", "load": {"history_order_by": "seq"},
"dedup": {"mode": "business_key_latest", "business_keys": ["id"], "order_by": "seq"}},
[("a", 1, 1, 1), ("a", 2, 2, 2), ("a", 2, 2, 3)], [("a", 1), ("a", 2)]),tests/dataplatform/test_silver_stages.py:299The three cases against silver: no lookback, inside it, outside it. ("a key already in silver is a replay (DUP-04)", None, "2026-11-01", 1),
("inside the late lookback", 2, "2026-11-08", 1),
("outside the late lookback it is not looked at", 2, "2026-11-01", 0),tests/dataplatform/test_silver_stages.py:304Silver holds key a. The batch brings a and b.def test_duplicates_against_silver(spark, case, lookback, silver_window, replayed):
p = plan(dedup={"mode": "business_key_first", "business_keys": ["id"]},
load={"late_lookback_days": lookback} if lookback is not None else None)
current = spark.createDataFrame([("a", silver_window)], "id string, _window string")
out = run("duplicates", _prep(spark, [("a", 1, 1, 1), ("b", 1, 1, 2)], p), p, current, "2026-11-09")
assert out.tally["replayed"].count() == replayed, case
assert rows(out.df, "id") == ([("b",)] if replayed else [("a",), ("b",)])- A replay older than the lookback is not seen and is appended again. The third test case states this behaviour.
- The replay check compares keys only. A row with a known key and different values is dropped as a replay, and nothing records that the values differed.
- Dropped and replayed rows are counted but not kept.
- The settings registry lists the modes
none,exact,business_key_first,business_key_latestandflagfordedup.mode. The plan acceptsfull_rowand refusesexactandflag. The two lists do not agree. - With the stage switched off, the check against silver is switched off too.
- On the declarative engine this stage cannot run inside the flow. See the declarative engine.