dbt dimension surrogate keys and fact foreign keys - self-hash or left-join lookups?
retained text
I've started working with dbt and a cloud warehouse, where I'm thinking of moving from auto-incremented keys in favour of surrogate hash keys, both because auto-increment keys are not as much of a thing in cloud warehouses and also because hash keys are idempotent and much simpler to manage. One thing that I'm not sure of yet is this: If the surrogate key is a hash of the business key, should I self-hash the foreign key references in the fact table builds, or should I do the usual pattern of left join on dims + coalesce to sentinel value if not found? I can see the pros and cons of both methods. Some pros: * Fewer risks of fan-out in case of misshapen data (should be caught by dbt tests, but still) * Increased parallelism because of fewer dependencies * Simpler lineage * Late-arriving dims, early-arriving facts are not an issue * If the dimension happens to be massive, this can save compute time, therefore cutting costs. Some drawbacks of self-hash: * Reduced impact analysis clarity through the lineage because of no lookups. This can also be diminished via dbt docs * You must ensure that you hash the same way (logical ordering and also natural keys formatting). A left-join lookup is likely easier to catch in case of a missed lookup (idk). * Not exactly a drawback, but you probably want to left-join if you apply SCD2. * No clear way to ensure proper fallback to the sentinel value if that is something used in the analytics layer Edit: Some clarifications based on some comments I've seen. If I were to self-hash the key on the fact side, I definitely wouldn't also do a dim surrogate key lookup because that's redundant. If I were to look up, I'd always use the business key because it's best practice and also the lowest chance of code errors (maybe someone forgot a field or swapped the column orders). My initial plan is to left join lookup, as "usual" in most old-school on-prem data warehouses, except for a couple of high-cardinality dims that also happen to build themselves using the value found in the fact table's raw data. Basically moving out a degen dimension into a conformed dimension because it's a compound natural key and is used across many fact tables, and it keeps the analytics modelling simpler. I have a case of late-arriving dims where I might consider self-hash with a late reconcile when it arrives, but if I did, then that's somewhere where I'd consider self-hashing too, especially if the fact can't be fully rebuilt. Thankfully, most of my fact tables are small enough that full and incremental are only a few seconds of difference.
document fetched 2026-10-03 00:24 · canonical https://reddit.com/r/dataengineering/comments/1wvfvpl/dbt_dimension_surrogate_keys_and_fact_foreign