How does a hashdiff column detect changed rows in an SCD Type 2 dimension load?
answer
- one value instead of thirty comparisons
- what happens if one attribute is NULL?
- concatenate, then compare against the stored hash
- tracked columns define the history policy
- delimiter and NULL sentinel are mandatory
basics
~20 sA hashdiff hashes the concatenated tracked attributes of an incoming source row and compares that value to the hash stored on the key's current dimension version. Equal means skip the row; different means close the old version and insert a new one.
solid answer
~50 sA hashdiff is one fixed-width digest computed over the concatenation of exactly the attributes you have decided to version. The dimension stores it on each row, so the load joins staging to the current version on the business key and compares two single values instead of writing a NULL-unsafe `col1 <> col1 OR col2 <> col2 ...` chain across thirty columns. Building it safely matters more than the algorithm: cast every attribute to text, `coalesce` NULLs to a sentinel, separate fields with a delimiter that cannot occur in the data, and keep column order, trimming and casing fixed — otherwise `'ab' || 'c'` and `'a' || 'bc'` collide, or one NULL turns the whole concatenation NULL and the change is silently missed. The tracked set deliberately excludes Type 1 attributes and audit columns like `loaded_at`, which would otherwise re-version every row every run.
code
sql · 9 lines-- the hash function name varies by engine; the input construction is the portable part
select
s.customer_id,
md5(
coalesce(cast(s.customer_name as varchar(4000)), '~') || '|' ||
coalesce(cast(s.city as varchar(4000)), '~') || '|' ||
coalesce(cast(s.tier as varchar(4000)), '~')
) as hashdiff
from stg_customer sgo deeper
Know that the load does not blindly insert a row every night: it compares one stored hash against a freshly computed one and only writes when they differ. Be able to say what the hash is computed from.
Be ready to write the hash expression and defend it: explicit casts, a NULL sentinel, a delimiter, a fixed column order, and a tracked column list that excludes audit and Type 1 attributes.
Expect to diagnose the two failure modes in production — every row versioning nightly, or no row ever versioning — and trace each back to the hash input construction or to comparing against the wrong dimension rows.
Own the tracked column set as a contract: it defines what history the business is paying to store, it is versioned with the model, and changing it is a deployment event with downstream consequences, not a quiet edit.
## What a hashdiff is A hashdiff (sometimes called a change hash or row checksum) is a single fixed-width value — typically an MD5, SHA-1 or SHA-256 digest rendered as hex — computed from the concatenation of the dimension attributes whose changes you want to record as history. It is stored as an ordinary column on the dimension row, next to the business key and the effective-dating columns. On each load the pipeline computes the same hash over the incoming source row and compares it with the hash already stored on that key's current version. Equal hashes mean "nothing I track changed, do nothing". Different hashes mean "this is a genuine change: close the current version and insert a new one". A key with no matching current row is a brand-new member and is simply inserted. Note what the hash is *not* for. It is not a security device and not a uniqueness key; it is a cheap equality proxy for "is this row's tracked payload the same as last time". ## Why not compare column by column The naive alternative is a predicate that ORs a comparison per attribute: ```sql where s.name <> d.name or s.city <> d.city or s.tier <> d.tier ``` Three problems make this the classic source of missed changes. First, it is NULL-unsafe: if `d.city` is NULL and `s.city` becomes `'Berlin'`, `NULL <> 'Berlin'` evaluates to unknown, the row is not selected, and the change vanishes. Fixing that needs a `coalesce` or `is distinct from` on every column. Second, it does not scale or survive maintenance — a wide dimension yields a 40-line predicate that someone eventually edits and forgets a column. Third, the comparison cost sits in the join predicate rather than in one equality on a narrow column. A single stored hash collapses all of that into `d.hashdiff <> s.hashdiff`, which is trivially readable and trivially reviewable. ## Building the hash input safely The algorithm hardly matters; the *input construction* is where loads break. - **Cast everything to text explicitly.** Implicit casts differ between engines and between a numeric `1` and a string `'1'`, so a source-type change can silently re-version the whole dimension. - **Replace NULLs with a sentinel.** In standard SQL, concatenating a NULL yields NULL, so one NULL attribute makes the whole hash NULL — and `NULL <> NULL` is unknown, so the row never looks changed. `coalesce(cast(col as varchar), '~')` fixes it. Pick a sentinel that cannot legitimately appear. - **Use a delimiter.** Without one, `('ab','c')` and `('a','bc')` hash identically, so a value shifting across a field boundary is invisible. - **Fix the column order and normalization.** Order, trimming and case folding must be deterministic and identical on both sides of the comparison, and must not change casually — see below. - **Compute the hash once, in one place.** If the staging side and the dimension side ever compute it with different expressions, every row looks changed forever. ```sql md5( coalesce(cast(name as varchar(4000)), '~') || '|' || coalesce(cast(city as varchar(4000)), '~') || '|' || coalesce(cast(tier as varchar(4000)), '~') ) ``` ## Choosing the tracked set The hashdiff defines your history policy, so its column list is a design decision, not an afterthought. Include only attributes whose change should produce a new dimension version. Deliberately exclude: - attributes handled as an overwrite (Type 1) — a corrected spelling should not create a version; - pipeline audit columns (`loaded_at`, `batch_id`, `source_file`), which change every run and would re-version the entire dimension nightly; - volatile or derived columns recomputed downstream. Because the stored hash encodes the column list implicitly, changing that list makes every stored hash incomparable with the newly computed one, and the next run versions every member at once. Treat the tracked set as part of the model's contract and keep it beside the dimension in code. ## Collisions and other failure modes A collision means two different attribute payloads hash to the same value, and the change is missed. With a 128-bit or wider digest over the row counts of a real dimension the probability is negligible; with a 32-bit CRC over millions of rows it is not. Prefer a cryptographic-strength digest even though you are not doing cryptography — the width is what you are buying. Other realistic failures: comparing against *all* dimension rows instead of only the current version (which matches an old version and produces flapping); trailing whitespace or case differences from a source that has become case-insensitive; and non-deterministic ordering when the hash input is built from an unordered structure. Each shows up the same way — either no versions appear when the source clearly changed, or every row versions on every run.
- Why do many teams keep a second hash over the business key alongside the hashdiff?A key hash gives a single narrow join column for a compound or wide business key, so the current-version lookup joins on one value rather than four. It is a convenience for joining and distributing, not for change detection — the hashdiff still carries the tracked payload, and the two must never be conflated in the load predicate.
- How would you handle a source column that is genuinely volatile but occasionally meaningful, like a last-login timestamp?Keep it out of the hashdiff, or you version the member on every run. If analysts need it, load it as a Type 1 overwrite on the current version, or move it to a fact — a per-event attribute belongs in a fact table, not in a dimension whose history you pay for on every change.
- What tells you a hashdiff was built wrong when the load looks like it is working?Two symptoms. Every member versions on every run — usually an audit column inside the hash input, or the two sides computing the hash differently. Or no member ever versions despite obvious source changes — usually NULL propagating through the concatenation, or the comparison running against the wrong dimension rows.
It is the same idea as comparing file checksums instead of diffing two documents line by line: one short value tells you whether anything you care about moved, and only then do you do the expensive work.
saying these in an interview costs you the question
- Hashing every source column including loaded_at and batch_id
- Concatenating attributes without a NULL sentinel or delimiter
- Believing the hash guarantees uniqueness or acts as a key
- Comparing the incoming hash against all versions, not the current one
- Claiming a 32-bit checksum is collision-safe at warehouse scale