cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Bronze to silver › The thirteen stages › 10 Duplicates

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.

Good rows of the batchplus silver as it stands10 Duplicatesrank within a group, keep rank 1Rows keptone per groupDroppedrepeats inside the batchReplayedkey already in silver
The two dashed boxes are counts. Dropped and replayed rows are not stored.

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.

WhenA group isThe row keptWhy
dedup.mode is noneEvery row by itselfEvery rowThe dataset asked for no removal.
dedup.mode is full_rowRows equal in every contract columnThe first to arriveOnly exact copies are repeats.
The history type is type 2Rows with the same key and the same order valueThe first to arriveA 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_latestRows with the same keyThe one with the highest order value. On a tie, the last to arrive.Only the latest state matters.
Otherwise, which means business_key_firstRows with the same keyThe first to arriveAn 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.

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_idpress_idtemperature_cArrival orderResult
R-502PR-07171.21Kept.
R-502PR-07171.22Dropped. It is the second row of its group.
R-501PR-07170.93Replayed. The key is in silver within the last two days.
R-503PR-08168.44Kept.

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

SettingWhat it meansAllowed valuesDefault
dedup.modeHow repeats inside the batch are recognised.business_key_latest, business_key_first, full_row, nonenone
dedup.business_keysThe columns that identify a row for this stage.A list of contract columnsThe contract's primary key
dedup.order_by, or load.history_order_by when setThe column that says which row is later.A contract columnEmpty
load.late_lookback_daysHow many days of silver are checked for a replay, counted back from the run's business date.A whole number of daysNot set. All of silver is checked.
processor_groups.dedupA 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.

DatasetModeHistoryCase takenChecked against silver
Dealersbusiness_key_latesttype 2Same key and order valueNo
Materialsbusiness_key_latesttype 2Same key and order valueNo
Pressesbusiness_key_latesttype 2Same key and order valueNo
Sales order linesbusiness_key_latesttype 2Same key and order valueNo
Deliveriesbusiness_key_latesttype 1Latest per keyNo
Dealer sell-outbusiness_key_latest, ordered by file namesnapshotLatest per keyNo
Production recordsbusiness_key_firstappendFirst per keyYes, all of silver
Inspection resultsbusiness_key_firstappendFirst per keyYes, all of silver
Press readingsbusiness_key_firstappendFirst per keyYes, 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)]
  1. Arrival order. arrival_order builds 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.
  2. Rank. Each of the five cases adds a working column _rn with row_number() over a window. A window is a grouping with an order inside each group. partitionBy names the group and orderBy the order.
  3. 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.
  4. Keep rank 1. kept is the rows with _rn = 1. dropped is the rest, returned in the tally.
  5. Decide whether to look at silver. Three things must hold: the history type is append, silver exists, and the mode is not none.
  6. Take the recent keys. recent narrows silver to the lookback. The key columns are selected, made distinct, and given a marker column _seen.
  7. Left join. The kept rows are joined to those keys. A left join keeps every new row and fills _seen only where the key was found.
  8. Split on the marker. Rows where _seen is null are new and go on. Rows where it is set are replays and go into the tally replayed.
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",)])
Not built yet, or known gaps
  • 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_latest and flag for dedup.mode. The plan accepts full_row and refuses exact and flag. 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.