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

Data PlatformZoom level 4

Type 2 history: keep every version of a key

Type 2 history keeps one row for every version of a business key. When a tracked value changes, the old version is closed and a new one is opened. Nothing is overwritten, so silver can answer what was true on any past day.

The tyre company needs this for its sales order lines. On-time delivery is judged against the date that was first promised to the dealer. If silver kept only the latest promised date, a line whose date was moved would always look on time.

The hard part is not opening a version. The hard part is that changes do not arrive in order. A change made at ten o'clock can reach silver after a change made at eleven. If silver trusted the order of arrival, the older value would become the current one. So the function on this page never looks at arrival order to decide history. It uses the order the source gave.

New changesthis batchStored versionsof the touched keysEventsin source orderVersionsby tracked hashOne rowper versionOpen: RowsRowsto mergezoom in ›1234
Read left to right. The highlighted box is the idea everything else rests on: stored versions and new changes are put in one list, in the source's order.
  1. Collect the events. The new changes of this batch and the versions silver already holds for the same keys become one list of events. Each event has a position in the source's order.
  2. Open versions. Walking each key's events in that order, an event opens a new version when its tracked values differ from the tracked values before it.
  3. Pick one row per version. A version may contain several events. The one with the highest order value gives the version its values.
  4. Date, flag and key the versions. Each version gets its validity times, its current flag, its deleted flag and a row key built from the key and the version's start. Stored rows that the new history no longer contains are marked for removal.

A worked example: a promised date that moves twice

Order O1, line 1 was promised for 10 November. The planner moved it to 11 November, and later to 12 November. The change feed gives every change a number, change_seq, that grows with each change at the source. The move to 12 November reached silver first. The move to 11 November reached silver after it.

ArrivesOrderLinePromised datechange_seq
firstO112026-11-1010
secondO112026-11-1230
third, lateO112026-11-1120

Silver ends with three versions in the source's order. The late change sits in the middle of the history. The version that started at 30 is still the current one.

OrderLinePromised date_seq_from_is_current
O112026-11-1010no
O112026-11-1120no
O112026-11-1230yes

The declarative spike uses the same order line and the same three sequence numbers, with both moves in one batch and the older one listed second. The same pattern, with smaller numbers, is case 5 of the contract below.

The settings that drive it

data-platform-catalog/slices/02-tyre/manifest/datasets/erp.sales_order_line.yaml:25The sales order line: type 2 on three columns, ordered by the change sequence, deletes marked.
history: {kind: transaction, type: scd2, track_columns: [promised_date, ordered_qty, line_status]}
load:
  pattern: history
  history_order_by: change_seq
  batch_key: [delivery_window, content_checksum]
  new_columns: hold_in_bronze
  deletes: {detect: change_feed, apply: mark_deleted}
  quarantine_stop_share: 0.01
  late_lookback_days: 35
KeyWhat it means hereValue for the sales order line
history.typeAsks for type 2 although the kind is transactionscd2
history.track_columnsOnly a change to one of these opens a versionpromised_date, ordered_qty, line_status
load.history_order_byThe source's order of changeschange_seq
load.deletesDeletes arrive in the change feed and are markedchange_feed, mark_deleted

The plan adds one more thing the function needs: the effective time. It is the time a change took effect, and it becomes the validity dates of versions. For a change feed it is the commit time the feed carries, in the column _committed_at. For other sources it is the order column, when that column is a timestamp. Materials and presses are full copies ordered by extracted_at, a timestamp, so their versions are dated by the time the copy was taken.

src/dataplatform/silver/plan.py:301How the plan picks the effective time.
    effective = "_committed_at" if read == "change_feed" else (order_by if by_type.get(order_by) == "timestamp" else "")

The code, step by step

Step 1: put stored versions and new changes in one list, in the source's order

src/dataplatform/stages/history.py:137The first lines of scd2(): the events, and the order they are walked in.
def scd2(inc: DataFrame, cur: DataFrame | None, plan: SilverPlan) -> DataFrame:
    o = plan.order_by
    new = _incoming(inc, plan).withColumn("_pos", F.col(o))
    events, rows = _events(new, cur, plan)
    # nulls placed explicitly in every window order (Spark's default, NULLS FIRST ascending): SQLFrame's window
    # orders leave it to the engine, and DuckDB puts nulls last (docs/DUCKDB-SPIKE.md)
    by_pos = Window.partitionBy("_key_hash").orderBy(F.col("_pos").asc_nulls_first(), F.col(o).asc_nulls_first(),
                                                     F.col("_existing").desc_nulls_last())

Line by line. o is the name of the order column, change_seq for the order lines. _incoming turns the new rows into events, and each new event gets a position _pos equal to its order value. _events adds the stored versions of the same keys. Both helpers are explained on the hub page. A stored version arrives with _pos equal to its _seq_from, the order value at which it started.

by_pos is the heart of the function. It is a window, which means a rule for walking rows group by group. The group is one business key. Inside a key the rows are walked by position, then by the order column, and at a tie the stored version comes before a new change. Arrival order is not in this rule at all. That one fact is why a late change lands in its proper place.

The comment about nulls matters for portability. Spark and DuckDB disagree about where an empty value sorts unless the code says so. Every window on this page states it, so the same history comes out on both engines.

Step 2: decide which events open a version

src/dataplatform/stages/history.py:130Which columns are tracked.
def _tracked(plan: SilverPlan, columns: list[str]) -> list[str]:
    if plan.track:
        return list(plan.track)
    skip = set(plan.keys) | {plan.order_by}
    return [c.name for c in plan.kept if c.name not in skip and c.name in columns]

If the manifest lists tracked columns, those are used. If it lists none, every contract column that silver keeps is tracked, except the key columns and the order column. The order column must be left out, because it changes with every change and would open a version every time.

src/dataplatform/stages/history.py:145The tracked hash, the value before, and the two columns that mark versions.
    tracked = F.sha2(text_parts(_tracked(plan, events.columns)), 256)
    # last_value(..., ignoreNulls) rather than last(ignorenulls=True): the same window function, and the form every
    # engine the stages run on keeps the null skipping of (docs/DUCKDB-SPIKE.md)
    before = F.last_value("_t", F.lit(True)).over(by_pos.rowsBetween(Window.unboundedPreceding, -1))
    events = (
        # a delete event never opens a version (it marks the one it falls in); a stored version keeps its values
        events.withColumn("_t", F.when((F.col("_op") == "D") & ~F.col("_existing"), F.lit(None)).otherwise(tracked))
        .withColumn("_new", F.when(before.isNull(), True).when(F.col("_t").isNull(), False)
                    .otherwise(F.col("_t") != before))
        .withColumn("_ver", F.sum(F.col("_new").cast("int")).over(
            by_pos.rowsBetween(Window.unboundedPreceding, Window.currentRow)))
    )
ColumnWhat it holdsWhy
_tA hash of the event's tracked values. A hash is a short fingerprint: two rows with the same tracked values have the same hash. A new delete event has no hash.Comparing one fingerprint is simpler than comparing every tracked column. A delete says the row is gone, not that a value changed, so it must not look like a change.
beforeThe most recent hash among the earlier events of the same key, skipping events that have none.Skipping empty hashes means a delete does not break the chain. The event after a delete is compared with the values before the delete.
_newTrue when this event opens a version.See the three rules below.
_verA running count of the true values of _new within the key.Events with the same count belong to the same version.

The three rules for _new are read in order. If there is no hash before this event, it is the first event of the key and it opens the first version. Otherwise, if this event has no hash, it is a delete and it opens nothing. Otherwise it opens a version exactly when its hash differs from the hash before it.

Here is the worked example at this point, after the third batch. The first two rows are versions silver already holds. The third is the late change.

Event_posPromised date_tbefore_new_ver
stored version102026-11-10hash Anonetrue1
new change, late202026-11-11hash Bhash Atrue2
stored version302026-11-12hash Chash Btrue3

Step 3: one row per version

src/dataplatform/stages/history.py:157From events to versions: the start, the earliest time, and the row that represents the version.
    group = Window.partitionBy("_key_hash", "_ver")
    latest = group.orderBy(F.col(o).desc_nulls_last(), F.col("_existing").asc_nulls_first(), *arrival_desc(events))
    versions = events.withColumns({
        "_start": F.min("_pos").over(group),
        "_gts": F.min("_ts").over(group),
        "_rn": F.row_number().over(latest),
    }).where("_rn = 1")

A version can hold more than one event. A change to a column that is not tracked falls into the version that is open at that point. Three values are computed over the events of a version.

ColumnWhat it is
_startThe lowest position in the version. This is where the version begins in the source's order.
_gtsThe earliest effective time in the version.
_rnA rank. Rank 1 is the event with the highest order value. At a tie a new change beats a stored version, and after that the newest arrival wins.

Only rank 1 is kept. So a version takes its start from its earliest event and its values from its latest event. The tie rules make a resent change harmless: the same change delivered twice has the same order value, and the newest delivery simply replaces the older one.

Step 4: validity dates, flags and the row key

src/dataplatform/stages/history.py:164The last lines of scd2(): the columns silver stores for each version.
    per_key = Window.partitionBy("_key_hash").orderBy(F.col("_start").asc_nulls_first())
    deleted = F.col("_op") == "D"
    out = versions.withColumns({
        "_valid_from": F.min("_gts").over(per_key.rowsBetween(Window.currentRow, Window.unboundedFollowing)),
        "_is_current": F.lead("_start").over(per_key).isNull(),
        "_is_deleted": deleted,
        "_deleted_at": F.when(deleted, F.col("_dts")),
        "_seq_from": F.col("_start"),
        "_row_key": F.sha2(F.concat_ws("|", F.col("_key_hash"), F.col("_start").cast("string")), 256),
    }).withColumn("_valid_to", F.lead("_valid_from").over(per_key))
    return _finish(out, _stale(rows, out), plan)
ColumnHow it is computedWhat to notice
_valid_fromThe lowest effective time among this version and every later version of the key.Looking at later versions too keeps the dates in step with the source's order. A version can never start after a version that follows it.
_valid_toThe _valid_from of the next version. Empty for the last version.The end of one version is exactly the start of the next. There are no gaps and no overlaps.
_is_currentTrue when no version follows.At most one version per key is current.
_is_deletedTrue when the row that represents the version is a delete.A delete marks the version it falls in. It does not close it.
_deleted_atThe effective time of that delete.
_seq_fromThe version's start in the source's order.The next run reads it back as the stored version's position.
_row_keyA hash of the key hash, a bar character and the version's start.This is what makes each version its own row in the merge.

The rule for _valid_from deserves an example. In case 5 below, the late change was committed at 01:05, an hour after the newer change was committed at 00:05. Dated by its own commit time, the middle version would start after the version that follows it. The rule gives it the lowest time among itself and the later version, which is 00:05. Its validity then runs from 00:05 to 00:05. It has zero length. The version is in history and can be read, but a lookup for the value that was true at some moment never lands on it.

The last line calls _stale and _finish. A stored row of a touched key whose row key is not among the new rows gets the action delete. Case 6 below shows why that is needed. Every other row gets upsert. The writer then applies them in one merge on _row_key.

Why running the same batch again changes nothing

Nothing in the function depends on when it runs or how often. Positions come from the order column. Versions come from the tracked hash. The row key comes from the key and the version's start. A second run of the same batch adds events that have the same order value as events already folded into the stored versions. They fall into the same versions, the same row wins rank 1 with the same values, and every row key is one silver already has. The merge updates each row to what it already is. Case 8 proves it. One stage earlier, stage 10 has already dropped a change that appears twice inside one batch.

src/dataplatform/stages/rows.py:44Stage 10 for a type 2 dataset: every distinct change is kept, and the same key and order value twice is one change.
    elif plan.history == "scd2":
        part = Window.partitionBy(*keys, plan.order_by).orderBy(*arrival)
        ranked = df.withColumn("_rn", F.row_number().over(part))

The eight cases every engine must pass

The behaviour above is written down as data in one file. Each case lists the batches of changes in arrival order and what silver must mean afterwards. A change row is the key, a tracked value, a note that is not tracked, the sequence number, the operation, the minute it was committed, and its position in the feed. Think of the value as the promised date and the note as a free-text remark on the order line.

src/dataplatform/silver/history_contract.py:20The eight cases, exactly as the tests read them.
CASES: list[tuple[str, list[list[Change]], list[Version]]] = [
    ("first load: one current version per key",
     [[("a", 1, "x", 1, "I", 0, 1), ("b", 5, "x", 2, "I", 0, 2)]],
     [("a", 1, 1, True, False), ("b", 5, 2, True, False)]),
    ("a tracked change opens a version",
     [[("a", 1, "x", 1, "I", 0, 1)], [("a", 2, "x", 3, "U", 5, 2)]],
     [("a", 1, 1, False, False), ("a", 2, 3, True, False)]),
    ("an untracked change updates the version in place",
     [[("a", 1, "x", 1, "I", 0, 1)], [("a", 1, "y", 3, "U", 5, 2)]],
     [("a", 1, 1, True, False)]),
    ("several changes in one batch become several versions, in order",
     [[("a", 1, "x", 1, "I", 0, 1), ("a", 3, "x", 5, "U", 9, 3), ("a", 2, "x", 3, "U", 5, 2)]],
     [("a", 1, 1, False, False), ("a", 2, 3, False, False), ("a", 3, 5, True, False)]),
    ("an older change arriving after a newer one goes into history, the newer stays current (SCD-02)",
     [[("a", 1, "x", 1, "I", 0, 1)], [("a", 3, "x", 5, "U", 5, 2)], [("a", 2, "x", 4, "U", 65, 3)]],
     [("a", 1, 1, False, False), ("a", 2, 4, False, False), ("a", 3, 5, True, False)]),
    ("a late change equal to the version after it moves that version's start",
     [[("a", 1, "x", 1, "I", 0, 1)], [("a", 3, "x", 5, "U", 5, 2)], [("a", 3, "x", 4, "U", 4, 3)]],
     [("a", 1, 1, False, False), ("a", 3, 4, True, False)]),
    ("a delete marks the key deleted, nothing removed (DEL-01)",
     [[("a", 1, "x", 1, "I", 0, 1)], [("a", 1, "x", 2, "D", 5, 2)]],
     [("a", 1, 1, True, True)]),
    ("the same batch twice changes nothing",
     [[("a", 1, "x", 1, "I", 0, 1), ("a", 2, "x", 2, "U", 1, 2)],
      [("a", 1, "x", 1, "I", 0, 1), ("a", 2, "x", 2, "U", 1, 2)]],
     [("a", 1, 1, False, False), ("a", 2, 2, True, False)]),
]

Each case is shown below as two tables. The first table lists the changes in the order they arrived. The second table shows silver afterwards. The test checks the key, the value, _seq_from, _is_current and _is_deleted. The note and the two validity times were read by running the cases through the stage functions on the DuckDB engine while this page was written. All times are on 9 November 2026.

Case 1: first load

It proves that a first load gives each key one current version.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 1b5x2I00:00
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1x100:00openyesno
b5x200:00openyesno

Case 2: a tracked change opens a version

It proves that a change to a tracked value closes the old version and opens a new one. The old version ends at the minute the new one starts.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 2a2x3U00:05
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1x100:0000:05nono
a2x300:05openyesno

Case 3: a change that is not tracked updates the version in place

It proves that a change to a column outside the tracked list opens nothing. The version keeps its start and takes the new note.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 2a1y3U00:05
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1y100:00openyesno

Case 4: several changes in one batch

It proves that order inside a batch does not matter. The change with sequence 5 is listed before the change with sequence 3, and the versions still come out in sequence order.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 1a3x5U00:09
batch 1a2x3U00:05
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1x100:0000:05nono
a2x300:0500:09nono
a3x500:09openyesno

Case 5: an older change arrives after a newer one

This is the late change. It proves that a change with a lower sequence, arriving in a later batch, goes into its place in history and does not become current. The late change was committed 65 minutes after the first load, which is later than the newer change.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 2a3x5U00:05
batch 3a2x4U01:05
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1x100:0000:05nono
a2x400:0500:05nono
a3x500:05openyesno
What silver holds afterwards, laid out in the source's ordervalue 1starts at sequence 1value 2starts at sequence 4value 3, currentstarts at sequence 5arrived firstsequence 1arrived secondsequence 5arrived thirdsequence 4, lateThe order the three changes arrived in, left to right
The bottom row is the order of arrival. The top row is the history silver keeps. The dashed box arrived last and still lands in the middle, because its sequence is 4.

Follow the dashed box. When batch 3 arrives, silver holds two versions of the key, starting at sequence 1 and at sequence 5. They become two stored events at positions 1 and 5. The late change is a new event at position 4. Walked by position, the late change is second. Its value differs from the value before it, so it opens a version. The stored version at position 5 differs from the late change, so it still opens a version, with the same start and therefore the same row key as before. It stays the last version, so it stays current. The middle version has zero length, as explained above.

Case 6: a late change that equals the version after it

It proves that a late change can move the start of a version. The late change has value 3 at sequence 4. The stored version with value 3 starts at sequence 5. They have the same tracked value, so they are one version, and that version really began at sequence 4.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 2a3x5U00:05
batch 3a3x4U00:04
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1x100:0000:04nono
a3x400:04openyesno

The version's start moved from 5 to 4, so its row key changed. The stage returns three rows for this batch: the first version with a new end, the current version under its new row key, and the old row key with the action delete. Without that removal silver would hold the same version twice.

Case 7: a delete

It proves that a delete marks the key and removes nothing. The version stays current and keeps its start. It is flagged deleted, and _deleted_at holds the delete's commit time, 00:05.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 2a1x2D00:05
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1x100:00openyesyes

The delete event has no tracked hash, so it opens no version. It falls into the version that is open. It has the highest order value in that version, so it becomes the row that represents it, and its operation D sets the flag. The deletes page covers what happens next, including a key that comes back.

Case 8: the same batch twice

It proves that a rerun changes nothing. The second batch repeats the first, row for row.

Arrives inKeyValue (tracked)Note (not tracked)SequenceOperationCommitted at
batch 1a1x1I00:00
batch 1a2x2U00:01
batch 2a1x1I00:00
batch 2a2x2U00:01
KeyValueNote_seq_from_valid_from_valid_to_is_current_is_deleted
a1x100:0000:01nono
a2x200:01openyesno

How it is tested

The history section of tests/dataplatform/test_silver_stages.py runs all thirteen stages for each batch and keeps silver in memory, applying the row keys and actions the way the merge would.

tests/dataplatform/test_silver_stages.py:349The test's stand-in for the merge: rows whose key is not in the new output are kept, upserts are added.
def _apply(spark, p, batches, ddl=FEED):
    """Run every stage for each batch and keep silver as a frame (a merge on _row_key done in memory)."""
    current = None
    for n, data in enumerate(batches, 1):
        df = frame(spark, ddl, data, batch=f"b{n}", window=f"2026-11-{8 + n:02d}")
        out, _, _ = run_stages(p, df, current, f"silver-{n}", f"2026-11-{8 + n:02d}", hold=stage_hold(df))
        out = hold(out)
        keep = [] if current is None else (
            current.join(out.select("_row_key"), "_row_key", "left_anti").select(*current.columns).collect())
        new = out.where("_action = 'upsert'").drop("_action")
        current = new if not keep else new.unionByName(spark.createDataFrame(keep, current.schema))
        current = hold(current)
    return current
tests/dataplatform/test_silver_stages.py:369One test, run once for each of the eight cases.
@pytest.mark.parametrize(("case", "batches", "want"), HISTORY_CONTRACT, ids=[c[0] for c in HISTORY_CONTRACT])
def test_history_type2(spark, case, batches, want):
    """The SCD Type 2 contract both silver engines pass (ADR-035; silver/history_contract.py)."""
    silver = _apply(spark, plan(**SCD2), _feed(batches))
    assert rows(silver, "id", "v", "_seq_from", "_is_current", "_is_deleted") == want, case
    assert_history_invariants(silver)

After each case the test also checks three properties of the whole table: every row key appears once, a key has at most one current version, and the versions of a key never overlap. A second test pins the zero-length validity of the late change.

tests/dataplatform/test_silver_stages.py:377The late change again, this time checking the validity times.
def test_history_type2_validity_never_overlaps(spark):
    """The late change committed after the newer one: its validity collapses, the current version starts first."""
    silver = _apply(spark, plan(**SCD2), [[("a", 1, "x", 1, "I", ts(0), 1)], [("a", 3, "x", 5, "U", ts(5), 2)],
                                          [("a", 2, "x", 4, "U", ts(65), 3)]])
    got = {r["_seq_from"]: (r["_valid_from"], r["_valid_to"]) for r in silver.collect()}
    assert got[1][1] == got[4][0] == got[5][0]
    assert got[4][1] == got[5][0]
    assert got[5][1] is None

The same file is run on Spark and on DuckDB, so the eight cases hold on both engines the stages run on. The engines page explains how the declarative engine is held to the same cases.

What the recorded run shows

In the recorded 14-day run of the tyre company at full scale (4 November 2026, local Spark), a promised date was changed out of order on day 8. The design note records the verdict: three versions, the late change with sequence 87216 in history and not current, and the current promised date 2026-11-20. On day 14 silver held 113,457 current order lines in 121,322 versions, equal to the oracle. The numbers are quoted from docs/W1C-SILVER.md. The run was not repeated for this page.

Not built yet, or known gaps
  • A stored version remembers its start and its latest values, not every change that fell into it. If a tracked value changes and later changes back, and the middle change arrives after the last one was already folded into the stored version, the middle change is placed after that version and becomes current. Checked by running it: value 1 at sequence 1, a note change at sequence 3, then a late value 2 at sequence 2 leaves value 2 current. The source's true latest value is 1. A test case named late-after-flip-back in test_silver_stages.py states the right answer and is marked as a known failure, so the bug stays visible and the case turns red the day it is fixed.
  • When the order column is not a timestamp and the source is not a change feed, there is no effective time, so _valid_from and _valid_to stay empty. The versions are still right. The full copy test cases run this way.
  • Every version of a touched key is written again in the merge, not only the versions that changed.