Every gold surrogate key is a SHA-256 over the normalised business key. Not an identity column.
Why
A surrogate key is written into every fact row ever loaded, so changing the recipe afterwards is not a refactor — it is re-keying the warehouse in one transaction with every consumer that stored a key. And both failure modes are silent: a collision does not raise, it produces a fact row that joins to the wrong dimension member. The revenue is not missing, it is somewhere else, and you find out when a customer disputes an invoice.
How
sqlThe two key recipes in this warehouse — shorthand
-- gold.dim_customer
sha2(concat_ws('||', customer_id), 256) AS customer_sk
-- gold.fct_order_line
sha2(concat_ws('||', order_id, line_number), 256) AS order_line_sk
-- Shorthand: both lines omit the normalisation. Nothing in this warehouse
-- builds a key from them. make_sk() below is the executable form, and a key
-- hashed without trim/upper/coalesce does not equal the one already stored
-- in the fact — the join returns nothing and raises nothing.
Determinism is the reason. A hash key is computable on the fact side without reading the dimension, so fct_order_line and dim_customer build in parallel, and a full rebuild reproduces identical keys — which is what makes the idempotency test possible. Identity values depend on write order, so a backfill renumbers the dimension while every stored extract keeps the old numbers.
The separator is not decoration. Concatenate without one and two different keys collapse into one hash:
sqlRun this. It returns true.
SELECT sha2(concat('4711', '2'), 256)
= sha2(concat('471', '12'), 256) AS collides;
-- true — both hash the string '47112'.
--
-- Order 4711 line 2 and order 471 line 12 now share one order_line_sk.
-- Nothing errors. The dimension join fans out and one line's revenue
-- is attributed to the other.
concat_ws fixes that and introduces the second landmine: it silently skips NULLs, so concat_ws('||','A',NULL,'B') and concat_ws('||','A','B') are one string and a key with a missing middle component collides with a shorter one. Coalesce first, to a sentinel that cannot occur in the data.
pythonsrc/dwh/common/keys.py — the only place a key is ever built
from pyspark.sql import Column, functions as F
SEPARATOR = "||"
NULL_SENTINEL = "~"
def make_sk(*columns: str) -> Column:
"""Deterministic surrogate key: trim, upper, coalesce, join, sha2-256."""
parts = [
F.coalesce(F.upper(F.trim(F.col(c).cast("string"))), F.lit(NULL_SENTINEL))
for c in columns
]
return F.sha2(F.concat_ws(SEPARATOR, *parts), 256)
' 4711' and '4711' are one customer to a human and two to SHA-256, and leading whitespace out of a fixed-width SAP extract is not hypothetical. Check the source before applying upper, though: it silently merges keys differing only in case — right for SAP, wrong for a case-sensitive system.
Doing it
One helper, one moduleAn inline sha2 expression in a model file is the copy that will diverge.
Pick a separator that cannot occur in a business keyAdd an ingestion expectation rejecting keys that contain it.
Pin the recipe with a test containing hand-computed hashesIt stops a well-meant cleanup from changing it.
Keep the business key visible beside the _skYou cannot reverse a hash for a support question.
Embedding it
Make them produce the collision themselves in a notebookTwo minutes, and nobody on that team writes concat into a key again.
In review, ask where the key is builtIf the answer is an expression in the model rather than make_sk, let them name the finding.
Hand them the versioning problem: how would we change the separator next year?They arrive at 'not cheaply', which is the lesson.
Defaults
sha2(..., 256) over the normalised business key, stored as STRING
trim, upper, coalesce, concat_ws, sha2 — in that order, in one function
Identity columns only where the value is never referenced outside the tableIn a warehouse that is almost nowhere.
Never truncate the hash
Gotchas
concat instead of concat_wsOrder 4711 line 2 and order 471 line 12 hash identically, the fact gets a duplicate key, and revenue moves between customers with no error anywhere.
concat_ws skipping NULLsA key with a missing middle component hashes like a shorter key, so two dimension members merge into one. Row counts stay plausible; the customer is wrong.
Truncating the hash to eight hex charactersThat leaves 32 bits — a coin-flip chance of collision at about 77,000 keys, and dim_customer will pass that.
Identity columns plus a full refreshThe dimension renumbers, the fact keeps the old numbers, nothing fails, and the joins now return different customers than yesterday.
Unity Catalog accepts PRIMARY KEY, UNIQUE and FOREIGN KEY declarations on Delta tables. All three are informational metadata. Nothing is checked, on write or ever.
Why
Teams read a declared primary key as a guarantee and stop testing for duplicates. The duplicate then arrives from a source that delivered one customer twice in one extract, and the first symptom is revenue three percent high because the dimension join fanned out.
Declare them anyway: they document the model, BI tools infer relationships from them, and RELY genuinely helps the optimizer when the constraint holds and the query runs on Photon. Documentation with a conditional performance side effect is fine — as long as nobody mistakes it for enforcement, and nobody sizes a workload on a rewrite that the nightly job's compute never performs.
How
sqlDeclared, violated, accepted — in that order
-- every column of a primary key must ALREADY be NOT NULL,
-- or the ADD CONSTRAINT is rejected before it declares anything
ALTER TABLE gold.dim_customer ALTER COLUMN customer_sk SET NOT NULL;
ALTER TABLE gold.dim_customer ALTER COLUMN _tech_valid_from SET NOT NULL;
-- dim_customer is SCD2, so the key is the VERSION, not the customer
ALTER TABLE gold.dim_customer
ADD CONSTRAINT pk_dim_customer
PRIMARY KEY (customer_sk, _tech_valid_from);
-- now insert the same version twice
INSERT INTO gold.dim_customer
(customer_sk, customer_id, customer_name, customer_segment, country,
_tech_valid_from, _tech_valid_to, _tech_is_current, _tech_hash)
VALUES
('a1b2', 'C-4711', 'Muster GmbH', 'A', 'DE',
TIMESTAMP '2026-07-01 00:00:00', NULL, true, 'h1'),
('a1b2', 'C-4711', 'Muster GmbH', 'B', 'DE',
TIMESTAMP '2026-07-01 00:00:00', NULL, true, 'h2');
-- 2 rows inserted. No error. The primary key is still declared.
RELY is where it gets interesting: it tells the optimizer to trust the declaration, permitting two documented rewrites — dropping a DISTINCT that a unique key already guarantees, and eliminating a join to a dimension whose columns are not selected. The platform still verifies nothing, so the duplicate above produces a plan built on a false premise. The violation returns wrong results faster.
Three qualifiers decide whether RELY does anything at all, and all three are easy to miss. The default is NORELY: a declaration you do not qualify buys documentation and nothing else. RELY on a FOREIGN KEY needs Databricks Runtime 15.4 or later, and on UNIQUE it needs 18.2 or later; on PRIMARY KEY it has been available since 14.2. And the rewrites themselves require Photon-enabled compute.
sqlRELY is opt-in, per constraint — and only honest on the dimensions that are not historised
-- dim_sales_region has exactly one row per member, provable by the
-- uniqueness assertion that runs every build. That is the whole
-- precondition for RELY.
ALTER TABLE gold.dim_sales_region ALTER COLUMN region_sk SET NOT NULL;
ALTER TABLE gold.dim_sales_region
ADD CONSTRAINT pk_dim_sales_region PRIMARY KEY (region_sk) RELY;
-- the fact side of the same relationship. RELY on a FOREIGN KEY needs
-- DBR 15.4+; on an older runtime the keyword is simply not available.
ALTER TABLE gold.fct_order_line
ADD CONSTRAINT fk_fct_region FOREIGN KEY (region_sk)
REFERENCES gold.dim_sales_region RELY;
-- withdrawing trust is a drop and a re-declare — unqualified is NORELY
-- again. Plan it as a two-statement change, not an in-place edit.
ALTER TABLE gold.fct_order_line DROP CONSTRAINT fk_fct_region;
ALTER TABLE gold.fct_order_line
ADD CONSTRAINT fk_fct_region FOREIGN KEY (region_sk)
REFERENCES gold.dim_sales_region;
UNIQUE ... RELY is the same lever with a lower ceremony cost — a natural key can be declared unique without becoming the table's primary key — and from DBR 18.2 a foreign key may reference a declared UNIQUE constraint rather than only the primary key. Both are worth knowing for dim_date and dim_sales_region. Neither rescues dim_customer, for the reason in the callout below.
Two Delta constraint types are enforced at write time, and that is where the effort belongs:
sqlBusiness rules 3 and 5, enforced by the engine rather than by hope
ALTER TABLE gold.dim_customer
ADD CONSTRAINT country_domain
CHECK (country IN ('DE', 'AT', 'CH', 'NL'));
ALTER TABLE gold.fct_order_line
ADD CONSTRAINT amount_positive
CHECK (status = 'CANCELLED' OR amount_eur > 0);
ALTER TABLE gold.fct_order_line
ALTER COLUMN order_line_sk SET NOT NULL;
-- not a business rule: a NULL _tech_hash makes the SCD2 MERGE's
-- tgt._tech_hash <> src._tech_hash comparison NULL, so that customer
-- silently stops versioning. NOT NULL is the cheapest place to stop it.
ALTER TABLE gold.dim_customer
ALTER COLUMN _tech_hash SET NOT NULL;
Adding a CHECK validates existing rows first, so the ALTER fails on a table that already violates it — a free audit of data you believed was clean, and a two-hour surprise if you meet it inside a release window.
Doing it
Declare PK, UNIQUE and FK wherever they are trueThey cost nothing and are the model's most-read documentation.
Add RELY only where a test proves the constraint on every runName that test in the decision record.
Before crediting RELY with anything, check two facts on the client's workspaceThe runtime (15.4+ for FK, 18.2+ for UNIQUE), and whether the compute running the query has Photon on. A RELY on a non-Photon cluster is a comment.
Put the rules that must stop a load into CHECK constraintsThe rest go in the SQL assertion suite.
Get the SCD2 primary key right: (customer_sk, _tech_valid_from), never customer_sk alone
Embedding it
Run declare-then-insert-duplicate in front of the teamNinety seconds, and it is the only way the belief actually dies.
Ask which test backs each RELY in the repoNo test, no RELY — and let them apply that to each other rather than to you.
Have them decide per business rule whether it belongs in a CHECK or an expectationA CHECK means the load fails at 03:00; an expectation means the row is quarantined. Owning that trade-off beats inheriting a default.
Defaults
PK, UNIQUE and FK for documentationUniqueness and orphans are enforced by tests that run every build.
RELY only behind a passing testOnly on the non-historised dimensions, and only where the reading compute runs Photon.
NOT NULL and CHECK for the invariants that should stop a load
The primary key of an SCD2 dimension includes the interval start
Gotchas
PRIMARY KEY (customer_sk) on an SCD2 dimensionWrong from the second version row onward, nothing complains, and it survives to become the ER diagram everyone trusts.
Declaring the primary key before the columns are NOT NULLDatabricks rejects the ADD CONSTRAINT outright, so you notice this one immediately — but it lands in the deployment script, not the notebook, which means it fails in the release pipeline at the point where the table already exists and the rollback story is unwritten. Set NOT NULL in the CREATE TABLE.
A CHECK constraint whose expression evaluates to NULLNULL is not false, so the row is accepted: CHECK (amount_eur > 0) passes every row where amount_eur is NULL, which is exactly the population you added the constraint for. Pair every CHECK on a nullable column with NOT NULL, or write the check as amount_eur IS NOT NULL AND amount_eur > 0.
RELY on an untested constraintThe optimizer drops a join, the query gets measurably faster, and the wrong number arrives sooner. An unexplained speed-up after adding RELY is a symptom, not a win.
Proving RELY harmless on classic job computePhoton is off there by default, so the rewrite never fires and the constraint looks inert — until the same query runs on the serverless SQL warehouse behind the dashboard, where Photon is on, the join is eliminated and the duplicate finally shows. The job and the report then disagree with no code change between them, and the compute difference is the last thing anyone suspects.
Writing RELY on a FOREIGN KEY on a runtime below 15.4, or on UNIQUE below 18.2You do not get the optimization you designed for, so a plan reviewed on a current workspace behaves differently on the client's pinned LTS cluster — and the constraint still reads as RELY in the DDL that was signed off.
Joining fct_order_line to dim_customer on customer_sk aloneA customer with three versions triples that customer's fact rows; revenue is overstated by an amount small enough to survive review.
Believing a declared FK rejects orphansNothing rejects anything — the orphan assertion in the SQL testing route is the enforcement; unwritten, orphans are simply absent from every inner-joined report.