Data PlatformZoom level 3
Stage 12: history, chosen by the kind of dataset
Stage 12 decides what a new row means for the silver table. It may be a new version of something silver already holds, a replacement, a new event, or a delete. The answer depends on the kind of dataset, so the stage has five ways of working and picks one from the plan.
Without this stage silver would be a pile of changes. A reader could not tell which row of a material is the one that is true today, or what a promised date was last week.
_key_hash. The silver table as it stands, handed in by the read edge. The plan._row_key that names the row in silver, and an _action that tells the writer to write it or remove it.History follows the kind of dataset
A dataset states its kind once in its manifest. The kind is one of master, transaction, event or snapshot. The plan turns the kind into a history type. A dataset may name a history type directly, and then that wins.
src/dataplatform/silver/plan.py:60The default history type for each kind, and the five types the engine knows.HISTORY_BY_KIND = {"master": "scd2", "transaction": "scd1", "event": "append", "snapshot": "snapshot"} # ADR-016
HISTORY_TYPES = ("scd1", "scd2", "append", "snapshot", "source_versioned") # ADR-016, ADR-032src/dataplatform/silver/plan.py:276An explicit type wins over the kind. An unknown type is refused. history = str(hist.get("type") or HISTORY_BY_KIND[kind])
if history not in HISTORY_TYPES:
raise PlanError(f"{name}: history type {history!r} is not one of {HISTORY_TYPES}")The five types side by side
Each row of this table is one history type. The columns say what one stored row stands for, how its row key is built, what a change to an existing key does, what a delete does, and which dataset of the tyre company uses it.
| Type | What one row is | Row key | A change does this | A delete does this | Tyre dataset |
|---|---|---|---|---|---|
Type 2 scd2 | One version of a key | Hash of the key hash and the version's start in the source order | A change to a tracked column opens a new version. Any other change updates the version it falls in. | Marks the version it falls in as deleted. Nothing is removed. | Materials, presses, dealers, and sales order lines (a transaction that asks for type 2) |
Type 1 scd1 | The latest state of a key | The key hash | The change with the highest order value replaces the row. | The row stays, marked deleted. | Deliveries |
Append append | One event | The key hash | Nothing. An event is never changed. A repeat was dropped by stage 10. | Nothing. Events have no delete handling. | Lab inspection results, production records, press readings |
Snapshot snapshot | One row of one delivered file | Hash of the key hash and the batch key | A newer file for the same window becomes current. The window's older rows stay, not current. | A row missing from the newer file stops being current. There is no deleted flag. | Dealer sell-out |
Source versioned source_versioned | One version, with the source's own validity dates | Hash of the key hash and the version's start date | A newer delivery of the same key and start replaces the stored one. | A last version that the source closed marks the key deleted. | None of the nine tyre datasets |
The dispatcher
src/dataplatform/stages/history.py:310The whole dispatcher. Four types share one signature. The fifth also returns rows to set aside.KINDS = {"scd2": scd2, "scd1": scd1, "append": append, "snapshot": snapshot}
def history(df: DataFrame, ctx: StageCtx) -> Out:
cur = None if ctx.current is None else working(ctx.current)
if ctx.plan.history == "source_versioned":
rows, bad = source_versioned(df, cur, ctx.plan)
return Out(rows, bad)
return Out(KINDS[ctx.plan.history](df, cur, ctx.plan))The dispatcher first puts the stored silver rows into the working shape. Then it looks at the plan's history type. Source versioned history is called on its own because it can set bad keys aside, so it returns two things. The other four are looked up in KINDS and called the same way: new rows, silver as it stands, and the plan.
What _row_key and _action mean
The history stage never writes. It labels each row so that the writer has nothing left to decide. The writer is explained on the writer page.
| Column | What it is | Why it is built this way |
|---|---|---|
_row_key | The identity of one row in the silver table. The writer matches on it and on nothing else. | It is computed from the data, never from the run. The same batch run twice gives the same row keys, so the second run finds every row already there. |
_action | upsert means write this row. delete means remove the stored row with this row key. | A remove is needed in two cases. A late change can move the start of a version, which changes its row key and leaves the old row stale. And a dataset can ask for deleted rows to be removed instead of marked. |
The helpers every keyed type shares
Type 2 and type 1 are built from the same six small functions. Read them once here. The pages for each type then only show what is different.
src/dataplatform/stages/history.py:27Three lists of column names the helpers use.SILVER_ONLY = ("_silver_run_id", "_silver_loaded_at", "_record_hash")
RECOMPUTED = ("_valid_from", "_valid_to", "_is_current", "_is_deleted", "_deleted_at", "_seq_from", "_row_key")
WORK = ("_pos", "_ts", "_dts", "_existing", "_t", "_new", "_ver", "_start", "_gts", "_rn")| List | What it holds |
|---|---|
SILVER_ONLY | Columns stage 13 adds after history. They are dropped from the stored rows so that stored rows and new rows have the same columns. |
RECOMPUTED | The history columns. They are worked out again every time a key is touched. |
WORK | Scratch columns used while computing. They never reach silver. |
src/dataplatform/stages/history.py:32Stored rows in the working shape.def working(current: DataFrame) -> DataFrame:
"""Silver rows in the stages' working shape (bronze run under its bronze name, silver-only columns gone)."""
# renamed only when present (snapshot passes rows already in the working shape; renaming a missing column is a
# no-op on Spark and an error on SQLFrame, docs/DUCKDB-SPIKE.md)
out = current.withColumnRenamed("_bronze_run_id", "_run_id") if "_bronze_run_id" in current.columns else current
return out.drop(*[c for c in SILVER_ONLY if c in out.columns])working makes the stored rows look like rows in flight. Silver calls the bronze run _bronze_run_id. In flight the same value is called _run_id, so the column is renamed back. The three columns that stage 13 adds are dropped.
src/dataplatform/stages/history.py:50New rows become events.def _incoming(df: DataFrame, plan: SilverPlan) -> DataFrame:
out = df.withColumn("_op", _op(df)).withColumn("_ts", _ts(df, plan)).withColumn("_existing", F.lit(False))
out = out.withColumn("_dts", F.col("_ts")) # when a delete took effect; a stored version brings its own (_existing)
if plan.delete_apply == "none":
out = out.where(F.col("_op") != "D")
return out_incoming adds three columns to the new rows. _op is the operation. A change feed sends I for insert, U for update and D for delete. A source that sends no operation gets U on every row. _ts is the effective time, which is the time the change took effect at the source. The plan names the column that holds it, and it is empty when the dataset has none. _existing is false, which marks the row as new. The last line is the first place deletes are decided: when the dataset's delete rule is none, rows with operation D are thrown away here.
src/dataplatform/stages/history.py:58Stored versions become events too.def _existing(versions: DataFrame) -> DataFrame:
op = F.when(F.col("_is_deleted"), F.lit("D")).otherwise(_op(versions))
out = versions.withColumn("_op", op).withColumn("_ts", F.col("_valid_from")).withColumn("_existing", F.lit(True))
# a deleted version keeps the time of its delete, not the start of its validity (the time must survive a rerun)
out = out.withColumn("_dts", F.when(F.col("_is_deleted"), F.coalesce(F.col("_deleted_at"), F.col("_valid_from")))
.otherwise(F.col("_valid_from")))
return out.withColumn("_pos", F.col("_seq_from")).drop(*[c for c in RECOMPUTED if c != "_seq_from"]).drop(
"_seq_from"
)_existing does the same for rows silver already holds. A stored row that is marked deleted gets operation D. Its time is its _valid_from. Its position in the source order, _pos, is its _seq_from, which is the order value at which the version started. Then the old history columns are dropped, because they will be computed again.
src/dataplatform/stages/history.py:101Every event of the keys this batch touches.def _events(inc: DataFrame, cur: DataFrame | None, plan: SilverPlan) -> tuple[DataFrame, DataFrame | None]:
"""(every event of the keys this batch touches, silver's rows of those keys)."""
if cur is None:
return inc, None
dels = _key_compare_deletes(inc, cur, plan)
events = inc if dels is None else inc.unionByName(dels, allowMissingColumns=True)
touched = events.select("_key_hash").distinct()
rows = cur.join(touched, "_key_hash", "left_semi")
return events.unionByName(_existing(rows), allowMissingColumns=True), rows_events builds the list the history is computed from. On the first load there is no silver, so the events are the new rows alone. Otherwise it adds any deletes found by comparing a full copy with silver (the deletes page explains that). It then lists the keys this batch touches and takes from silver only the rows of those keys. A key the batch does not mention is never read and never rewritten. The function returns the events and the stored rows of the touched keys.
src/dataplatform/stages/history.py:112Stored rows the new history no longer contains.def _stale(rows: DataFrame | None, out: DataFrame) -> DataFrame | None:
"""Silver rows of the touched keys whose row key the new history no longer has: removed."""
if rows is None:
return None
gone = rows.select("_row_key").join(out.select("_row_key"), "_row_key", "left_anti")
return gone.withColumn("_action", F.lit("delete"))src/dataplatform/stages/history.py:120Scratch columns dropped, the action set.def _finish(out: DataFrame, stale: DataFrame | None, plan: SilverPlan) -> DataFrame:
out = out.drop(*[c for c in WORK if c in out.columns]).withColumn("_action", F.lit("upsert"))
if plan.delete_apply == "remove": # a key whose current version is deleted loses every version it has
live_delete = (F.col("_is_current") & F.col("_is_deleted")).cast("int")
gone = F.max(live_delete).over(Window.partitionBy("_key_hash")) > 0
out = out.withColumn("_action", F.when(gone, F.lit("delete")).otherwise(F.lit("upsert")))
return out if stale is None else out.unionByName(stale, allowMissingColumns=True)_stale compares row keys. A stored row of a touched key whose row key is not in the new result is stale, and gets the action delete. _finish drops the scratch columns and sets the action to upsert. When the delete rule is remove, a row marked deleted gets the action delete instead. Last, the stale rows are added.
The settings that drive stage 12
Each row is one key of the dataset manifest. The columns give its meaning, the values the code accepts, and what happens when it is absent.
| Key | What it means | Allowed values | When absent |
|---|---|---|---|
history.kind | The kind of dataset | master, transaction, event, snapshot | The plan refuses the dataset |
history.type | The history type, when the kind's default is not wanted | scd1, scd2, append, snapshot, source_versioned | Chosen from the kind |
history.track_columns | Type 2: the columns whose change opens a new version | Contract columns | Every kept contract column except the key and the order column |
load.history_order_by | The column that gives the source's order of changes | A contract column | dedup.order_by is used. Types 1 and 2 are refused when neither is set. |
load.deletes.detect | How a delete is found | change_feed, key_compare, none | none |
load.deletes.apply | What a delete does to silver | mark_deleted, remove, none | none |
history.valid_from_column, history.valid_to_column | Source versioned: the source's own validity columns | Contract columns | Source versioned is refused |
history.gaps_allowed | Source versioned: whether a gap between two versions is accepted | true, false | false |
Key design decisions
| Decision | Why |
|---|---|
| History is laid out in the source's order, never in arrival order. | Changes can arrive late or out of order. Only the source knows the true order, and it says so in the order column. |
| A touched key's history is computed again from its stored versions and the new changes. | One computation covers a first load, a normal change, a late change and a rerun. There is no separate code path for each. |
| Row keys come from the data. | A rerun produces the same rows, so it changes nothing. |
| Deletes mark, they do not remove, unless the dataset asks. | Gold and auditors need to know that a row existed and when it went away. |
- Events have little delete handling. When deletes are not applied, an append dataset drops a delete row of an event feed, as type 1 and type 2 do. With
mark_deletedorremovean append dataset does nothing special: a delete row is appended like any other row. - A snapshot has no deleted flag. A row that the newer file leaves out is only marked not current.
- No tyre dataset uses source versioned history or the
removerule. Source versioned history has its own table of test cases. Theremoverule has none. - The plan checks the value of
load.deletes.applybut not ofload.deletes.detect. The manifest schema checks that one before the plan is built.