cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Bronze to silver › The thirteen stages › 12 History › Snapshot: windows

Data PlatformZoom level 4

Snapshot history: a newer file replaces its window

A snapshot file states everything about one window, such as one dealer's sales for one month. When a corrected file for the same window arrives, it replaces what the earlier file said. The earlier rows are kept, marked not current.

The tyre company's dealers send sell-out files this way. A dealer who finds a mistake sends the month again. If silver added the new file to the old one, the month's units would be counted twice.

File f1, dealer d1materials m1 and m2File f2, dealer d1material m1 onlyNewest file per windowby order column, then batchf2 rows: currentf1 rows: keptnot current
Both files are for the same dealer. The newer one becomes current. Every row of the older one stays in silver, switched off.
Goes inThe rows of the new files, and the current rows silver holds.
What happensFor each window the newest file is found. If it is at least as new as what silver holds for the window, its rows become current and the window's other current rows are switched off.
Comes outThe new rows, plus the older rows of the same windows with _is_current false.

A worked example

This is the second case of the test table. Dealer d1 sends file f1 with two materials. Later the dealer sends file f2 with a corrected count for material m1 and no row for m2.

Arrives inDealerMaterialUnitsFile
batch 1d1m13f1
batch 1d1m24f1
batch 2d1m15f2
DealerMaterialUnitsFile_is_current
d1m13f1no
d1m15f2yes
d1m24f1no

Material m2 is not in the newer file, so the dealer's window no longer has it. Its row is still in silver, but it is not current.

The settings that drive it

data-platform-catalog/slices/02-tyre/manifest/datasets/partner.dealer_sellout.yaml:19The business key and the order column of dealer sell-out.
dedup: {mode: business_key_latest, business_keys: [dealer_code, sale_month, material_code], order_by: file_name}
data-platform-catalog/slices/02-tyre/manifest/datasets/partner.dealer_sellout.yaml:24The kind is snapshot. The batch key is the file's checksum, so a renamed resend is the same batch.
history: {kind: snapshot}
load:
  pattern: overwrite_window
  batch_key: [content_checksum]  # a renamed resend is the same delivery (DUP-01): the name is not part of the key
  new_columns: hold_in_bronze
  deletes: {detect: none, apply: none}
  quarantine_stop_share: 0.05
  late_lookback_days: 90
src/dataplatform/silver/plan.py:321The window is not a setting. The plan takes the key without its last column.
        snapshot_by=keys[:-1] if history == "snapshot" else (),

The contract's primary key is what the window is cut from. The manifest above lists the key as dealer code, sale month and material code, so the window is a dealer's month. Files are ordered by file_name.

The code

src/dataplatform/stages/history.py:218The whole of snapshot history.
def snapshot(inc: DataFrame, cur: DataFrame | None, plan: SilverPlan) -> DataFrame:
    order = [F.col(plan.order_by)] if plan.order_by else []
    stamp = F.struct(*order, F.col("_batch_key"))
    win = F.sha2(F.concat_ws(SEP, *[F.col(c).cast("string") for c in plan.snapshot_by]), 256)
    by_win = Window.partitionBy("_win")
    new = (
        inc.withColumn("_win", win)
        .withColumn("_stamp", stamp)
        .withColumn("_newest", F.max("_stamp").over(by_win))
        .where(F.col("_stamp") == F.col("_newest"))
        .drop("_newest")
    )
    new_rows = new.withColumns({
        **_live(),
        "_row_key": F.sha2(F.concat_ws("|", F.col("_key_hash"), F.col("_batch_key")), 256),
    })
    if cur is None:
        return _finish(new_rows.drop("_win", "_stamp"), None, plan)
    old = working(cur).where(F.col("_is_current")).withColumn("_win", win).withColumn("_stamp", stamp)
    held = old.groupBy("_win").agg(F.max("_stamp").alias("_held"))
    incoming = new.groupBy("_win").agg(F.max("_stamp").alias("_in"))
    applies = incoming.join(held, "_win", "left").where(F.col("_held").isNull() | (F.col("_in") >= F.col("_held")))
    taken = new_rows.join(applies.select("_win"), "_win", "left_semi")
    superseded = (
        old.join(applies.select("_win"), "_win", "left_semi")
        .join(taken.select("_row_key"), "_row_key", "left_anti")
        .withColumn("_is_current", F.lit(False))
    )
    out = taken.unionByName(superseded, allowMissingColumns=True).drop("_win", "_stamp")
    return _finish(out, None, plan)
  1. Stamp each row. _stamp is the order value and the batch key together. Comparing stamps compares the order value first and the batch key second.
  2. Name the window. _win is a hash of the window columns.
  3. Keep the newest file of each window in this batch. Two files for the same window can arrive in one batch. Only the rows with the highest stamp per window are kept.
  4. Give the rows their columns. They are live rows. The row key is a hash of the key hash and the batch key, so rows from different files never collide.
  5. First load. With no silver table the new rows are returned as they are.
  6. Decide whether the new file applies. held is the highest stamp among silver's current rows per window. A window applies when silver has nothing for it, or the incoming stamp is at least as high.
  7. Switch off what was replaced. For the windows that apply, silver's current rows that are not among the rows being written are returned with _is_current false.

The comparison in step 6 is what stops an old file from winning. If a dealer's earlier file is sent again after the correction, its stamp is lower than what silver holds, so the window does not apply and nothing is written for it.

How it is tested

tests/dataplatform/test_silver_stages.py:431Four cases: a first file, a newer file, another window left alone, an older file sent again.
        ("a window's rows", [[("d1", "m1", 3, "f1"), ("d1", "m2", 4, "f1")]],
         [("d1", "m1", 3, True), ("d1", "m2", 4, True)]),
        ("a newer file replaces the window; the old rows stay, not current",
         [[("d1", "m1", 3, "f1"), ("d1", "m2", 4, "f1")], [("d1", "m1", 5, "f2")]],
         [("d1", "m1", 3, False), ("d1", "m1", 5, True), ("d1", "m2", 4, False)]),
        ("another window is untouched", [[("d1", "m1", 3, "f1")], [("d2", "m1", 9, "f2")]],
         [("d1", "m1", 3, True), ("d2", "m1", 9, True)]),
        ("an older file sent again does not win", [[("d1", "m1", 5, "f2")], [("d1", "m1", 3, "f1")]],
         [("d1", "m1", 5, True)]),
    ],
)
def test_history_snapshot(spark, case, batches, want):
    p = plan(kind="snapshot", keys=("dealer", "mat"), read="files",
             columns=[("dealer", "string"), ("mat", "string"), ("units", "integer"), ("file_name", "string")],
             dedup={"mode": "business_key_latest", "business_keys": ["dealer", "mat"], "order_by": "file_name"})
    silver = _apply(spark, p, batches, "dealer string, mat string, units long, file_name string")
    assert rows(silver, "dealer", "mat", "units", "_is_current") == want, case

In the recorded 14-day run of the tyre company at full scale (4 November 2026, local Spark), silver's sell-out rows equalled the oracle on every one of the 14 days, ending at 9,404 current rows. The figure is quoted from docs/W1C-SILVER.md.

Not built yet, or known gaps
  • The window cannot be declared. It is always the business key without its last column. The design note lists this as a gap in the manifest format.
  • A row dropped by a newer file has no deleted flag and no time of removal. It is only not current.
  • The declarative engine refuses snapshot history. Snapshot datasets run on the job engine.