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

Data PlatformZoom level 3

Stage 13: provenance, where each row came from

The last stage finishes the row. It records which silver run handled it and when, keeps the facts about the bronze batch it came from, adds a hash of its business values, and puts the columns in one fixed order.

Provenance means the record of where something came from. When a number in a report looks wrong, these columns are how you walk back from the silver row to the run, the batch and the file. The stage before this one is stage 12, history, which has its own pages.

Rows from historywith _row_key and _action13 Provenancerename, hash, stamp, reorderFinished silver rowsbusiness columns first,silver's columns last
No row is added, removed or set aside.
Goes inThe rows history produced, the run id and the load time from the context.
What happensOne column is renamed, three are added, and the columns are put in order.
Comes outThe rows exactly as the write edge will write them.

A worked example

One dealer row, shown as a list because it is wide. The left column is the column name in the order the stage produces. The values are made up, and long values are cut.

ColumnValueWhere it comes from
dealer_codeD-1001Business column, in contract order
dealer_nameSharma TyresBusiness column
regionnorthBusiness column
contact_emailtok_5c1e...Business column
contact_phonetok_9a40...Business column
updated_at2026-11-09 06:00:00Business column
segmentretailAnother source column, not in the contract. These come next, in alphabetical order.
_batch_keyb-17Stamped by the lander, carried through
_source_filedealers_page_3.jsonStamped by the lander
_window2026-11-09Stamped by the lander
_key_hash9b1c...Stage 11
_record_hashe07a...This stage
_valid_from, _is_current, _row_key and the other history columns...Stage 12
_dq_flags[]Stages 2, 4 and 9
_bronze_run_idingest-2026-11-09The lander's _run_id, renamed by this stage
_silver_run_idsilver-2026-11-09This stage
_silver_loaded_at2026-11-09 06:20:11This stage
_actionupsertStage 12. The write edge uses it and does not store it.

The settings that drive it

The stage reads no dataset setting. It uses the plan's list of kept columns, the run id and the load time from the context, and three fixed lists of column names in the plan module. It has no switch.

src/dataplatform/silver/plan.py:71The columns the lander stamps on every bronze row, and the transport columns some sources add.
LOAD_COLUMNS = ("_batch_key", "_run_id", "_source_file", "_window", "_extra", "_bronze_at")
TRANSPORT_COLUMNS = ("_op", "_feed_pos", "_committed_at", "_shard", "_seq", "_arrived_at")

The code

def provenance(df: DataFrame, ctx: StageCtx) -> Out:
    """Where each row came from and when silver took it: batch, file, bronze window and run, silver run, load time,
    and a record hash over the business columns. Columns come out in a fixed order: contract, other source columns,
    then load, transport and silver columns."""
    plan = ctx.plan
    business = [c.name for c in plan.kept]
    out = df.withColumnRenamed("_run_id", "_bronze_run_id") if "_run_id" in df.columns else df
    out = (
        out.withColumn("_record_hash", F.sha2(F.to_json(F.struct(*[F.col(c) for c in business])), 256))
        .withColumn("_silver_run_id", F.lit(ctx.run_id))
        .withColumn("_silver_loaded_at", F.lit(ctx.loaded_at).cast("timestamp"))
    )
    fixed = [*LOAD_COLUMNS, *TRANSPORT_COLUMNS, *SILVER_COLUMNS, "_action"]
    known = set(business) | set(fixed)
    extra = sorted(c for c in out.columns if c not in known)
    order = business + extra + [c for c in fixed if c in out.columns and c != "_run_id"]
    return Out(out.select(*order))
  1. The business columns. business is the names of the kept contract columns, in contract order.
  2. Rename the bronze run. Bronze calls the run that loaded the row _run_id. In silver there are two runs to tell apart, so the column becomes _bronze_run_id.
  3. The record hash. The business columns are packed into one structure, written as JSON text, and hashed with SHA-256. More on this below.
  4. The silver run. _silver_run_id is the run id from the context, the same on every row of the run.
  5. The load time. _silver_loaded_at is the load time from the context, as a timestamp. The stage does not read the clock. If no load time was passed, the column is null.
  6. The fixed columns. fixed is the load columns, then the transport columns, then silver's own columns, then _action.
  7. Everything else. extra is every column that is neither a business column nor a fixed one, sorted by name. These are the source columns the contract does not list, and columns added on the way such as a local time.
  8. Reorder. The final order is business, then extra, then the fixed columns that the frame actually has. _run_id is left out because it was renamed.

The fixed order matters more than it looks. Rows from two batches are stacked by column name elsewhere, but a table whose columns come in a stable, predictable order is easier to read, to compare and to test. The test for this stage checks that the first three columns are the contract's.

How the record hash is computed

Take the test's row: id is a, v is 1, seq is 2. The three steps are these.

StepResult
F.struct(...) packs the business columns, in contract orderone value with three named fields
F.to_json(...) writes it as text{"id":"a","v":1,"seq":2}
F.sha2(..., 256) hashes the texta54293011ea10149452d63d0aecefc4d8b15607ca6493bc30910599c25022f31

JSON is used and not a plain join of the values, because JSON carries the column names and the types. The number 1 and the text "1" are written differently, and a value cannot slide from one column into the next. So the key hash needs a separator and the record hash does not.

The hash covers the kept contract columns only. It does not cover other source columns, _extra, or any underscore column. Two rows with the same business values from two different batches have the same record hash.

One difference between engines is recorded in the tests. Spark leaves a missing field out of the JSON. DuckDB writes it as null. The texts differ, so the hashes differ. A record hash must therefore be compared only with hashes made by the same engine.

How it is tested

tests/dataplatform/test_silver_stages.py has six cases, one per column the stage is responsible for, a test that the record hash changes with a business value, and exact values in the table of engine checks.

tests/dataplatform/test_silver_stages.py:579The six cases. Each names a column and a check on its value.
        ("the bronze run kept under its own name", "_bronze_run_id", lambda v: v == "ingest-1"),
        ("the silver run stamped", "_silver_run_id", lambda v: v == "silver-1"),
        ("the batch kept", "_batch_key", lambda v: v == "b1"),
        ("the file kept", "_source_file", lambda v: v == "f1"),
        ("a record hash over the business columns", "_record_hash", lambda v: len(v) == 64),
        ("a load time", "_silver_loaded_at", lambda v: v is not None),
tests/dataplatform/test_silver_stages.py:587The test body. It also checks the column order and that _run_id is gone.
def test_provenance(spark, case, col, check):
    p = plan()
    out, _, _ = run_stages(p, frame(spark, "id string, v long, seq long", [("a", 1, 1)]), None, "silver-1",
                           "2026-11-09", ts(0))
    assert check(out.collect()[0][col]), case
    assert out.columns[:3] == ["id", "v", "seq"]
    assert "_run_id" not in out.columns
tests/dataplatform/test_silver_stages.py:596Rows a and b differ in the id, c in the value: three different hashes.
def test_record_hash_changes_with_a_business_value(spark):
    p = plan()
    out, _, _ = run_stages(p, frame(spark, "id string, v long, seq long", [("a", 1, 1), ("b", 1, 1), ("c", 2, 1)]),
                           None, "s", "2026-11-09")
    h = {r["id"]: r["_record_hash"] for r in out.collect()}
    assert len({h["a"], h["b"], h["c"]}) == 3
tests/dataplatform/test_silver_stages.py:648The exact value: the hash equals SHA-256 of the JSON text.
    ("record hash: business values as JSON", lambda s: _record_of(s, "id string, v long, seq long", ("a", 1, 2)),
     _sha('{"id":"a","v":1,"seq":2}')),
tests/dataplatform/test_silver_stages.py:652The known difference when a business value is missing.
    # SQLFrame's to_json keeps null fields (Spark leaves them out); a record hash is compared within one engine only
    ("record hash: a null business value", lambda s: _record_of(s, "id string, v long, seq long", ("a", None, 2)),
     {"spark": _sha('{"id":"a","seq":2}'), "duckdb": _sha('{"id":"a","v":null,"seq":2}')}),
Not built yet, or known gaps
  • Nothing in the stages reads _record_hash back. The history stage drops it when it takes silver's rows into its working shape, and this stage computes it again. Type 2 history decides whether a version changed from its own fingerprint of the tracked columns. The record hash is there for people and for later checks, not for the engine's decisions.
  • The record hash differs between Spark and DuckDB for a row with a missing business value.
  • A rerun of the same batch gives the same business values and the same row key, but a new silver run id and a new load time. So a rerun's merge rewrites these two columns on the rows it touches. This is my reading of the merge, which sets every column of a matched row.
  • The settings registry has a key processor_groups.provenance. No code reads it. The stage cannot be switched off.
  • The load time is one value for the whole run, not the moment each row was written.