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

Data PlatformZoom level 3

Stage 11: keys, the business key and its hash

The eleventh stage makes sure every row can be identified, and gives it one column that stands for its identity. The business key is the set of contract columns that identify a row, such as the order id and the line number. The stage checks that none of them is empty and adds _key_hash, a hash of the key.

A hash is a fixed-length fingerprint worked out from a text: the same text always gives the same fingerprint. With it, the history stage and the writer can group and join on one column of one type, whether a dataset's key is one text column or three columns of mixed types.

Rowsafter duplicates11 Keyscheck the parts, hash the keyRows with _key_hash64 hex charactersQuarantinerows with an empty key part
A row without a full key cannot be merged, so it does not go on.
Goes inRows, and the contract's primary key columns from the plan.
What happensEach key column is checked for a missing value. The parts are turned to text, joined with a separator and hashed.
Comes outRows with a new column _key_hash. Rows with an empty key part go to quarantine.

A worked example

The sales order line's key is order_id and line_no. The symbol ␟ in the third column stands for the separator character, which is invisible.

order_idline_noText that is hashed_key_hash
SO-100110SO-1001␟10d5474bf7d0a59c6d583e20daf204634aaf0a92fdfe3427b7af73ea0fc656a9eb
SO-1001missingnoneQuarantined: business key line_no is empty

The hash in the first row was worked out for this page with Python's SHA-256 over that text. The tests below prove that the stage gives the same result as Python for the same bytes.

The settings that drive it

SettingWhat it meansAllowed valuesDefault
The contract's primary keyThe columns marked primaryKey, in the order of primaryKeyPosition.One or more contract columnsNone. The plan refuses a dataset without one.
data-platform-catalog/slices/02-tyre/contracts/sales_order_line.odcs.yaml:18The two key columns of the sales order line contract.
      - {name: "order_id", logicalType: "string", required: true, primaryKey: true, primaryKeyPosition: 1}
      - {name: "line_no", logicalType: "integer", required: true, primaryKey: true, primaryKeyPosition: 2}

The stage has no switch and no other setting. It uses the contract's primary key, not the dedup.business_keys of the stage before.

The code

src/dataplatform/stages/rows.py:64The whole stage: three lines.
def keys(df: DataFrame, ctx: StageCtx) -> Out:
    """Every business key column present, else the row is quarantined; the key hash every later join uses."""
    checks = [(F.col(k).isNull(), f"business key {k} is empty") for k in ctx.plan.keys]
    good, bad = quarantine_rows(df, reasons(checks), "keys")
    return Out(good.withColumn("_key_hash", key_hash(ctx.plan.keys)), bad)
  1. One check per key column. The check is true when the column is null. Its text is business key <name> is empty.
  2. Split. quarantine_rows sets aside rows with any empty key part.
  3. Hash. The good rows get _key_hash from key_hash, which is the next thing to read.
src/dataplatform/stages/common.py:18The separator between key parts.
SEP = "\u001f"  # between key parts in a hash: never in a business value
src/dataplatform/stages/common.py:75How the parts become one text, and the text a hash.
def text_parts(columns: list[str] | tuple[str, ...]) -> Column:
    """The columns as one text, SEP between parts, a null part as null_mark()."""
    return F.concat_ws(SEP, *[F.coalesce(F.col(c).cast("string"), null_mark()) for c in columns])


def key_hash(columns: tuple[str, ...]) -> Column:
    return F.sha2(text_parts(columns), 256)
  1. Each part to text. Every key column is cast to text. A whole number 10 becomes 10. A date becomes 2026-11-09.
  2. A stand-in for a missing part. coalesce replaces a null part with the null mark, explained below.
  3. Join with the separator. concat_ws joins the parts with SEP between them.
  4. Hash. sha2(..., 256) is SHA-256. The result is 64 hex characters.

Why a separator is needed

Take two rows with a two-part key. One has the parts ab and c. The other has a and bc. Joined with nothing between them, both give abc and so the same hash. Two different rows would look like one, and the merge would overwrite one with the other. With a separator the texts are ab␟c and a␟bc, which differ.

The separator must be a character that cannot appear in a key value. If it were a dash, the parts a-b and c would collide with a and b-c. The code uses U+001F, a control character whose name is unit separator and whose purpose is exactly this. Stage 3 removes control characters from text, and U+001F is in the set it removes, so a cleaned key value cannot contain it.

The null mark

src/dataplatform/stages/common.py:68The text that stands for a missing part.
def null_mark() -> Column:
    """The text standing for a null key part in a hash: one NUL character. Built by chr(0) rather than a literal so
    every engine reads the same character (a NUL inside a SQL string literal does not survive on all of them;
    docs/DUCKDB-SPIKE.md). The hash is the same SHA-256 of the same bytes on Spark and DuckDB (tested)."""
    return F.expr("chr(0)")

In this stage a missing key part never reaches the hash, because the row is quarantined first. But text_parts is shared. The history stage uses it to fingerprint the tracked columns of a version, and those may be missing. There a null must not look like an empty text, so it is replaced by a single null character. The comment explains why it is written as chr(0): a null character typed inside a SQL text does not survive on every engine.

What usually catches an empty key first

In the tyre contracts every key column is also marked required. The quality stage, two stages earlier, has a critical rule for every required column. So an empty key part is normally caught there, and the batch is held. The quarantine in this stage acts when a key column is not marked required, or when the dataset has changed the critical action to flag.

How it is tested

tests/dataplatform/test_silver_stages.py has two cases for the stage, a test that the hash tells the parts apart, and a table of exact values that must be the same on every engine.

tests/dataplatform/test_silver_stages.py:317The two stage cases: a full key, and an empty part.
        ("keys present", [("a", 1), ("b", 2)], []),
        ("an empty key part is quarantined", [("a", None), ("b", 2)], ["business key n is empty"]),
tests/dataplatform/test_silver_stages.py:331ab and c, a and bc, and ab and c again: two different hashes, not one and not three.
def test_keys_hash_is_stable_and_distinguishes_parts(spark):
    p = plan(columns=[("id", "string"), ("n", "string"), ("seq", "integer")], keys=("id", "n"))
    df = run("project", frame(spark, "id string, n string, seq long", [("ab", "c", 1), ("a", "bc", 1),
                                                                         ("ab", "c", 2)]), p).df
    h = [r[0] for r in rows(run("keys", df, p).df, "_key_hash")]
    assert len(set(h)) == 2
tests/dataplatform/test_silver_stages.py:636Exact values: the hash must equal Python's SHA-256 of the parts joined by the separator.
SAME = [
    ("key hash: text parts", lambda s: _key_of(s, "id string, n string", ("ab", "c"), ("id", "n")),
     _sha(f"ab{SEP_}c")),
    ("key hash: parts never run together", lambda s: _key_of(s, "id string, n string", ("a", "bc"), ("id", "n")),
     _sha(f"a{SEP_}bc")),
    ("key hash: a whole number part", lambda s: _key_of(s, "id string, n long", ("a", 10), ("id", "n")),
     _sha(f"a{SEP_}10")),
    ("key hash: a date part", lambda s: _key_of(s, "id string, n date", ("a", date(2026, 11, 9)), ("id", "n")),
     _sha(f"a{SEP_}2026-11-09")),
tests/dataplatform/test_silver_stages.py:655One known difference between engines, kept in a table of its own.
    # a timestamp as text: Spark "2026-11-09 00:00:00", DuckDB's TIMESTAMPTZ adds the offset
    ("key hash: a timestamp part", lambda s: _key_of(s, "id string, t timestamp", ("a", ts(0)), ("id", "t")),
     {"spark": _sha(f"a{SEP_}2026-11-09 00:00:00"), "duckdb": _sha(f"a{SEP_}2026-11-09 00:00:00+00")}),
Not built yet, or known gaps
  • A key part that is a timestamp hashes differently on Spark and on DuckDB, because the two write a timestamp as text differently. The test file records this. A dataset with a timestamp in its key must stay on one engine. Text, whole number and date parts hash the same on both.
  • The hash is of the text form. The whole number 10 and the text 10 give the same hash.
  • The stage does not check that a key is unique. That is the work of the duplicates and history stages.
  • A key part that is an empty text after hygiene is already null, so it is caught. With hygiene switched off, an empty text would count as present.