What can go wrong when a warehouse surrogate key is a hash of the business key columns?
answer
- concatenation without a separator is ambiguous
- what does NULL do to a string concat?
- two spellings, two dimension rows
- normalize, sentinel, safe delimiter
basics
~20 sConcatenating raw columns collides when the delimiter is ambiguous, and produces different keys when NULLs, case or whitespace vary between loads. Normalize each part, replace NULL with a sentinel, and join with a delimiter that cannot occur in the data.
solid answer
~50 sFour failures recur. **Ambiguous concatenation**: hashing `a || b` makes ('AB','C') and ('A','BC') the same key — two distinct entities collapse into one dimension row. **NULL propagation**: in standard SQL concatenating anything with NULL yields NULL, so one missing component turns the whole key into NULL or, worse, into the hash of a shorter string. **Normalization drift**: one source sends `'ACME '`, another `'acme'`, and you get two dimension rows for one customer — the fact split follows silently. **Recipe instability**: change the trim rule, the column order or the delimiter and every key in the warehouse changes at once, orphaning existing facts. The fixes are mechanical: cast each part to text, trim and case-fold it, `COALESCE` NULL to an explicit sentinel string, join with a delimiter that cannot appear in the data, and treat the whole recipe as a versioned contract.
go deeper
Know that a hash surrogate key is computed from the business key columns, so the exact text fed in — spacing, casing, separators — decides whether two rows get the same key.
Explain delimiter ambiguity and NULL propagation concretely, and write the normalized expression: cast, trim, case-fold, coalesce to a sentinel, join with a delimiter absent from the data.
Show that you treat the recipe as a contract — one shared implementation, fixed-input tests, and a change handled as a coordinated rebuild of the dimension and its facts.
Weigh reproducibility against key width across the whole fact estate, decide the digest and storage form, and own what a recipe change costs downstream before anyone proposes an improvement.
## Why hash keys are attractive, and where they bite A deterministic hash over the business key gives a warehouse a surrogate key that is reproducible across rebuilds, machines and environments, computable in parallel with no central counter. The failures are not in the hash function — they are in the string you feed it. Every one of them is a data-correctness bug that produces no error. ## Ambiguous concatenation Without a separator, the boundary between components is lost: ```text source_system='AB', entity_id='C' -> 'ABC' source_system='A', entity_id='BC' -> 'ABC' -- same key, two entities ``` Two distinct business entities share a dimension row. Facts for both then aggregate together and the total looks plausible, so nobody investigates. Adding a delimiter fixes it only if the delimiter cannot occur in the data — `|` is a poor choice for free-text fields, `~` or a control character is safer, and the truly safe version also normalizes or rejects values containing the delimiter. ## NULL propagation In standard SQL, `'x' || NULL` is NULL, not `'x'`. So a business key with an optional component produces either a NULL surrogate key — the exact thing the key was supposed to prevent — or, if an engine is lenient about concatenation, the same hash as a row where the component was genuinely absent for a different reason. Replace each part explicitly: ```sql -- fragile digest(source_system || entity_id) -- deliberate: cast, trim, fold, sentinel, safe delimiter digest( COALESCE(UPPER(TRIM(CAST(source_system AS VARCHAR))), '~NULL~') || '~' || COALESCE(UPPER(TRIM(CAST(entity_id AS VARCHAR))), '~NULL~') ) ``` Note the cast: implicit numeric-to-string conversion is not portable, and `1.0` versus `1` hashing differently is a very unpleasant afternoon. ## Normalization drift The same entity arrives as `'ACME'`, `'acme'`, `' ACME'` and `'ACME\t'` from different feeds or after a source-side change. Each variant hashes differently, so the dimension quietly grows a second row for one customer and the facts split between them. Nothing errors, no orphan appears, and the only symptom is that a customer's numbers look smaller than expected. Decide the normalization once — trim, fold case, collapse internal whitespace, strip a known prefix — apply it identically everywhere the key is computed, and put it in one shared macro or function rather than copy-pasting the expression into every model. ## Algorithm and width Use a full-width digest from a standard cryptographic-strength family. With that, accidental collisions are not the practical worry. What *is* a worry is **truncating** the hash to save bytes, or reaching for a short non-cryptographic checksum: collision probability rises sharply as width falls, and a collision here means two entities silently merged. If width matters, store the digest as raw bytes rather than as a hex string — half the size for the same value, and the hex form is the version most people accidentally pick. Also remember a hash is one-way. Keep the business-key columns in the dimension row; without them, no one can tell what a key represents, reconcile against the source, or debug a duplicate. ## The recipe is a contract The most expensive hash-key failure is a well-intentioned improvement. Someone adds a trim, changes the delimiter, or reorders the components — and every key in the warehouse changes at once. The next run inserts an entirely new set of dimension rows, and every fact loaded before that points at values that no longer exist. Guard it: - Compute keys in exactly one place, referenced by every model. - Treat a change to the recipe as a coordinated rebuild of the dimension *and* its facts, planned like a migration. - Test it: a fixed set of inputs with expected output digests, asserted on every build, so an accidental change fails loudly. ## Where hash keys are the wrong tool For a dimension that keeps history, the key must identify the *version*, not just the entity — which means hashing something more than the business key, and means a fact loader holding only a business key still needs a point-in-time lookup to know which version applies. And if fact tables are enormous and storage-constrained, a 32-byte key repeated across billions of rows is a real cost that a narrow counter avoids. Hash keys buy reproducibility; be able to say what you paid for it.
- Why is truncating a hash surrogate key to save bytes a bad trade?Because the failure mode is a silent merge. Collision probability grows sharply as the digest narrows, and two entities sharing a dimension row aggregate together with no error and a plausible-looking total. If width really matters, store the full digest as raw bytes rather than as a hex string — that halves the size without weakening the key at all.
- Why should the expression that computes a hash key live in exactly one place?Because the key is only stable while every model computes it identically. Copy-pasted expressions drift — someone adds a trim in one model, changes a delimiter in another — and the two produce different keys for the same entity, splitting a dimension. One shared macro or function plus a test asserting fixed inputs map to fixed digests turns drift into a build failure.
Building a hash key from raw columns is like writing a filing label by running words together: 'ANNSMITH' could be Ann Smith or Anns Mith, and you only find out when two files end up in one folder.
saying these in an interview costs you the question
- Concatenates business key columns with no delimiter at all
- Forgets that concatenating with NULL yields NULL in SQL
- Hashes raw values without trimming or case folding
- Truncates the digest to save space in the fact table
- Changes the hash recipe without rebuilding dependent facts