SQL, data modeling, data quality, EDA, visualization · insight-1063
Build hashed surrogate keys from delimited, null-aware fields
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A model joins on a hash built by concatenating flight_no, date and fare_class, and two different rows produce the same key or a NULL field turns the whole key NULL.
Action
Use a tested key macro or construct the key with a delimiter, an explicit NULL marker for each field and a fixed cast and format per type, then assert the key is unique on the staging table. Do not change the recipe without a migration plan.
Reason
The string that is hashed must differ for every distinct combination of business fields, including their NULLs, or the key silently merges entities.
Boundary
Warehouse models, snapshots, incremental unique keys and cross-system entity IDs.
Symptom or risk
Plain concatenation can map different field combinations to one string and propagates NULL, so rows collide or silently fail to join.
Exceptions
Sources that already carry a stable surrogate or natural single-column key.
Trade-offs
Hashes are opaque in debugging and cost computation on wide rows.
Pitfalls
Changing the field order or the NULL marker in a later release, which re-keys all history.
Lessons and concepts
Claims and their sources
The dbt-utils generate_surrogate_key macro builds a hashed key from fields across databases and, unlike the older surrogate_key, does not treat nulls and blank strings as identical.
Kimball notes that natural keys come from source systems whose rules the warehouse team does not control, and recommends warehouse-assigned durable keys.