← sources

dbt dimension surrogate keys and fact foreign keys - self-hash or left-join lookups?

source: reddit (reddit:dataengineering)published 2026-10-02 00:30ingested 2026-10-03 02:36

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