cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Silver to gold › Keys

Data PlatformZoom level 2

Stable integer keys, and why gold needs them

Every dimension member has one integer key, and every fact row carries the key of the member it belongs to. The key is not a counter. It is computed from the member's own data, so rebuilding a table from nothing gives every member the same key it had before.

Without this, a rebuild would renumber the members, and every fact row and every saved report that holds a key would point at the wrong dealer.

Input namedealer_api.dealerBusiness keyD-1Version start9 Nov 2026, UTCOne line of textjoined with a barSHA-256first 15 hex digitsWhole numberunder 2 to the 60The same three inputs always give the same key, on any engine
Three values become one line of text. The text is hashed. The first 15 hex digits of the hash are the key.

Why not use silver's hashes

Silver already has two hashes. _key_hash is a hash of the business key, and _row_key identifies one version. Both are SHA-256 values kept as 64 characters of text. Gold computes its own key for four reasons.

ReasonWhat silver's hash givesWhat gold needs
One key per version_key_hash is the same for every version of a dealer.A fact row must point at one version, the one that was true at the event.
A whole number64 characters of text.A single whole-number column. It is smaller in every fact row, and joins and group-bys on it are cheaper.
Reserved membersA hash has no value that means nothing was found.Two keys that are never produced by the recipe: -1 for a member that has not arrived, -2 for a row that has no business key.
A recipe anyone can repeatThe hash is computed inside the silver stages.A recipe written as plain text, so Python, Spark and DuckDB all compute the same number and a test can prove it.

The recipe

src/dataplatform/gold/keys.py:26A version's start is always written the same way: UTC, microseconds, a Z at the end.
def canonical_time(t: datetime) -> str:
    """A version start as the recipe writes it (a naive time is taken as UTC)."""
    aware = t if t.tzinfo else t.replace(tzinfo=UTC)
    return aware.astimezone(UTC).strftime(TS_FORMAT)
src/dataplatform/gold/keys.py:32The text, then the key.
def key_text(dataset: str, business_key: tuple[object, ...], version_start: datetime | None = None) -> str:
    parts = [dataset, *("" if v is None else str(v) for v in business_key)]
    if version_start is not None:
        parts.append(canonical_time(version_start))
    # shiftleft: allow SL-COST-CODE-14 -- a string join (str.join), not a dataframe join
    return SEP.join(parts)


def surrogate_key(dataset: str, business_key: tuple[object, ...], version_start: datetime | None = None) -> int:
    """The member's key; the same inputs always give the same number."""
    digest = hashlib.sha256(key_text(dataset, business_key, version_start).encode()).hexdigest()
    return int(digest[:HEX_DIGITS], 16)

Walk through it. key_text makes a list that starts with the input's name, then each business key value as text with a missing value written as empty, then the version's start. The parts are joined with a bar. surrogate_key hashes that text with SHA-256, keeps the first 15 hex digits, and reads them as a number in base 16.

Fifteen hex digits are 60 bits. That is why 15 was chosen: the number is always positive and always fits in a 64-bit integer, whatever engine stores it. The input's name is part of the text so that a material M1 from one system and a material M1 from another never share a key.

A worked example

Each row is one member of the dealer dimension. The middle column is the text that is hashed. The right column is the key that results. These are computed with the function above.

MemberText that is hashedKey
D-1, first versiondealer_api.dealer|D-1|2026-11-09T00:00:00.000000Z80631743438072549
D-1, second versiondealer_api.dealer|D-1|2026-11-11T00:00:00.000000Z66182239882639827
D-2, only versiondealer_api.dealer|D-2|2026-11-09T10:00:00.000000Z1003977605158419583

The same recipe inside each engine

The dimension has millions of rows on a real system, so the key is computed by the engine, not row by row in Python. Each engine adapter repeats the recipe in its own functions.

src/dataplatform/adapters/spark_gold.py:59On Spark: the same text, the same hash, the same 15 digits, converted from base 16 to base 10.
def key_text(dataset: str, key: list[Column], version_start: Column | None) -> Column:
    """keys.key_text in the engine: the same text the Python recipe hashes."""
    parts = [F.lit(dataset), *[F.coalesce(c.cast("string"), F.lit("")) for c in key]]
    if version_start is not None:
        parts.append(as_text(version_start, TS_PIECES))
    return F.concat_ws(SEP, *parts)


def surrogate(dataset: str, key: list[Column], version_start: Column | None) -> Column:
    """keys.surrogate_key in Spark: the same text, the same SHA-256, the first 15 hex digits."""
    text = key_text(dataset, key, version_start)
    return F.conv(F.substring(F.sha2(text, 256), 1, HEX_DIGITS), 16, 10).cast("long")
src/dataplatform/adapters/duckdb_gold.py:32On DuckDB there is no base conversion function, so the 15 digits are added up one by one.
def surrogate(dataset: str, key: list[Column], version_start: Column | None) -> Column:
    """keys.surrogate_key: the first 15 hex digits of SHA-256(key text) as a whole number, digit by digit (a hex
    digit's value from its character code: 0-9 are 48-57, a-f are 97-102)."""
    digest = F.sha2(key_text(dataset, key, version_start), 256)
    codes = [F.ascii(F.substring(digest, i + 1, 1)) for i in range(HEX_DIGITS)]
    digits = [F.when(c < 58, c - 48).otherwise(c - 87).cast("long") * F.lit(16 ** (HEX_DIGITS - 1 - i))
              for i, c in enumerate(codes)]
    return reduce(lambda a, b: a + b, digits).cast("long")

The DuckDB version looks different but does the same sum. It takes each of the 15 characters, turns the character into its value from 0 to 15 using its character code, multiplies by the right power of 16, and adds the 15 results.

The reserved members

src/dataplatform/gold/keys.py:46The rule for the key a fact row points at.
def member(business_key: tuple[object, ...], found: int | None) -> int:
    """The key a fact row points at: not applicable without a business key, unknown when no member matched."""
    if not business_key or all(v is None for v in business_key):
        return NOT_APPLICABLE
    return UNKNOWN if found is None else found
SituationKeyWhat happens next
The fact row's dealer matched a memberThe member's keyNothing. This is the normal case.
The fact row has a dealer code, but no member matched-1, unknownThe row is published and counted as an orphan. Its window is rebuilt on every later run until the member arrives, whatever the lookback.
The fact row has no dealer code at all-2, not applicableThe row is published. There is nothing to wait for.

In the engines this rule is written as an expression inside the key lookup, which The harness shows. The published windows that still hold a -1 are found by windows_with_unknown and added to the range of every run.

src/dataplatform/adapters/spark_gold.py:281Published windows that still hold a row on the unknown member.
    def windows_with_unknown(self, plan: GoldPlan, facts: DataFrame | None) -> set[str]:
        if facts is None or not plan.dimensions:
            return set()
        hit = reduce(lambda a, b: a | b, [F.col(d.key) == UNKNOWN for d in plan.dimensions])
        return {str(r[0]) for r in facts.where(hit).select(self.window_text(plan)).distinct().collect()}

Collisions

Two different texts can in principle give the same 15 digits. The recipe does not prevent that. The harness detects it: after shaping a dimension it counts duplicate keys, and if there is even one, the dimension is not written and the step detect.KEY-01 is recorded as failed.

The settings that drive it

WhereKeyEffect
Fact manifestdimensions[].business_keyThe fact columns compared with the member's business key.
Fact manifestdimensions[].as_ofThe fact column whose time picks the version.
Fact manifestchecks.orphan_rate_maxThe share of rows on -1 above which the build fails.
Keys moduleHEX_DIGITS, SEP, TS_FORMAT15, a bar, and the time format. These are constants, not settings.

How it is tested

tests/dataplatform/test_gold_plan.py tests the Python recipe with a table of pairs that must or must not give the same key. tests/dataplatform/test_gold_engine.py then tests the as-of lookup on the engine, and the same table of cases runs on Spark and on DuckDB.

tests/dataplatform/test_gold_plan.py:110Pairs of inputs, and whether the two keys must be equal.
        ("same inputs, same key", ("erp.material", ("M1",), T0), ("erp.material", ("M1",), T0), True),
        ("another version start", ("erp.material", ("M1",), T0), ("erp.material", ("M1",), T0.replace(hour=11)), False),
        ("another member", ("erp.material", ("M1",), T0), ("erp.material", ("M2",), T0), False),
        ("another source", ("erp.material", ("M1",), T0), ("mes.material", ("M1",), T0), False),
        ("a naive time is UTC", ("x.y", ("1",), T0), ("x.y", ("1",), T0.replace(tzinfo=None)), True),
        ("a zone does not change the instant", ("x.y", ("1",), T0),
         ("x.y", ("1",), T0.astimezone(__import__("zoneinfo").ZoneInfo(TZ))), True),
    ],
)
def test_surrogate_keys_are_deterministic(case, a, b, same):
    assert (K.surrogate_key(*a) == K.surrogate_key(*b)) is same
tests/dataplatform/test_gold_engine.py:225One fact row per case: a dealer, a time, and the key it must get. V1 and V2 are the two versions of D-1.
        ("an event in the first version", "D-1", ts(1), V1),
        ("an event after the change: the new version (ADR-005)", "D-1", ts(50), V2),
        ("exactly at the change: the new version (half open)", "D-1", ts(48), V2),
        ("before the member's first version: still that member", "D-1", ts(-100), V1),
        ("a member that has not arrived: unknown", "D-9", ts(1), K.UNKNOWN),
        ("no business key: not applicable", None, ts(1), K.NOT_APPLICABLE),
    ],
)
def test_keys_as_of_the_event(spark, engine, dim, case, dealer, t, key):
    facts = spark.createDataFrame([("1", dealer, t, d(10))], "id string, dealer_code string, t timestamp, day date")
    [row] = engine.add_keys(FACT, facts, {"dim_dealer": dim}).collect()
    assert row["dealer_sk"] == key

The third and fourth cases are the two edges. An event exactly at the change gets the new version, because the span is open at its end. An event 100 hours before the dealer's first record still gets the first version, because that version reaches back to 1900.

tests/dataplatform/test_gold_harness.py:113Dealer D-3 arrives days after its order. The order's window is rebuilt although it is outside the 5-day lookback.
def test_a_late_member_is_rekeyed_outside_the_lookback(shop):
    shop.silver("order", ORDER, [order("A", "D-3", 9), order("B", "D-1", 12), order("C", "D-2", 12)])
    silver_loaded(shop.store, "src.order", DAYS[:1])
    t = shop.run(DAYS[0]).by_name()["fact_orders"]
    assert [r["dealer_sk"] for r in shop.facts() if r["order_id"] == "A"] == [-1]
    assert any(s["step"] == "detect.LATE-02" and s["status"] == "warned" for s in shop.steps("gold-1"))
    assert t.status == "succeeded"
    shop.silver("dealer", DEALER, [*DEALERS, {"dealer_code": "D-3", "region": "west", "_valid_from": ts(24 * 9)}])
    silver_loaded(shop.store, "src.order", DAYS[1:7])
    t = shop.run(DAYS[6]).by_name()["fact_orders"]  # day 9 is outside the 5-day lookback on the 15th
    assert "2026-11-09" in t.replaced
    assert [r["dealer_sk"] > 0 for r in shop.facts()] == [True, True, True]
    assert not any(s["step"] == "detect.LATE-02" for s in shop.steps("gold-2"))

In the recorded 14-day run at one tenth of full scale (5 November 2026), a new dealer's record arrived late on purpose. Six of its order lines sat on the unknown member on day 11 and ten on day 12, and none on day 13 after the record arrived. No row was dropped on any day. The table is in docs/W1D-GOLD.md.

Not built yet, or known gaps
  • A collision is detected, not resolved. If one ever happens the dimension stops being written until someone changes the recipe.
  • The Python function member states the rule and is tested, but the engines do not call it. They repeat the rule as an engine expression.
  • The manifest's keys.unknown_member and keys.not_applicable values are not used by the engine. The reserved keys are always -1 and -2.
  • The specification does not say how an event before a member's first version should join. Joining the first version is a decision taken in the code.