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

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.

The highlighted box is the dispatcher. It hands the rows to one of the five functions below it. Click a box to zoom in.
Goes inThe rows that passed stages 1 to 11, each with a _key_hash. The silver table as it stands, handed in by the read edge. The plan.
What happensThe dispatcher calls the function for the plan's history type. That function works out what each row becomes and which stored rows must change.
Comes outRows to write. Each has the history columns, a _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-032
src/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.

TypeWhat one row isRow keyA change does thisA delete does thisTyre dataset
Type 2 scd2One version of a keyHash of the key hash and the version's start in the source orderA 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 scd1The latest state of a keyThe key hashThe change with the highest order value replaces the row.The row stays, marked deleted.Deliveries
Append appendOne eventThe key hashNothing. 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 snapshotOne row of one delivered fileHash of the key hash and the batch keyA 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_versionedOne version, with the source's own validity datesHash of the key hash and the version's start dateA 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.

ColumnWhat it isWhy it is built this way
_row_keyThe 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.
_actionupsert 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")
ListWhat it holds
SILVER_ONLYColumns stage 13 adds after history. They are dropped from the stored rows so that stored rows and new rows have the same columns.
RECOMPUTEDThe history columns. They are worked out again every time a key is touched.
WORKScratch 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.

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.

KeyWhat it meansAllowed valuesWhen absent
history.kindThe kind of datasetmaster, transaction, event, snapshotThe plan refuses the dataset
history.typeThe history type, when the kind's default is not wantedscd1, scd2, append, snapshot, source_versionedChosen from the kind
history.track_columnsType 2: the columns whose change opens a new versionContract columnsEvery kept contract column except the key and the order column
load.history_order_byThe column that gives the source's order of changesA contract columndedup.order_by is used. Types 1 and 2 are refused when neither is set.
load.deletes.detectHow a delete is foundchange_feed, key_compare, nonenone
load.deletes.applyWhat a delete does to silvermark_deleted, remove, nonenone
history.valid_from_column, history.valid_to_columnSource versioned: the source's own validity columnsContract columnsSource versioned is refused
history.gaps_allowedSource versioned: whether a gap between two versions is acceptedtrue, falsefalse

Key design decisions

DecisionWhy
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.
Not built yet, or known gaps
  • 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_deleted or remove an 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 remove rule. Source versioned history has its own table of test cases. The remove rule has none.
  • The plan checks the value of load.deletes.apply but not of load.deletes.detect. The manifest schema checks that one before the plan is built.

Look inside