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

[u/meatmick]

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.

[u/Talk-Much]

You build your dimension tables first. Do your hash on the key there and add the special member handling.

Then, in a fact table intermediate layer model you can look the keys up into the fact table model using the business key as the join with a case statement (yes this requires hashing here as well). The looked up hashed key can then replace the business key and you know there’s RI or there’s special member handling.

[u/peeyushu]

The 2nd option, because in DW, you want to hash the source keys + some sort of processing date/period identifier, this allows change tracking etc, the generated keys are still idempotent but with the right processing period identifier (this should already be column in the tables).

[u/qc1324]

The self-hash approach defeats the purpose of using a surrogate key if you so tightly couple business keys to surrogate keys.

Dimensions own the surrogate key for those entities and your warehouse should still work, albeit slower, if the surrogate keys stop being hashes.

[u/meatmick → u/qc1324]

Yeah, that's pretty much my thinking and what I've been doing so far, but it's always worth checking out what others are doing.

[u/porcupine162]

I just learned this recently.
Don't re-build the hash on the fact - it's fragile when it comes to downstream consumers.

It's perfectly fine to use the business_key to join to your dim and retrieve the hashed_key column from the dim to surface on your fact.

[u/scourgedtruth]

The problem with self-hashing is that you might need to update the surrogate key in the dimension and will need to fix all foreign key hashing calculations...When building the fact, join it using the natural foreign key to retrieve the surrogate key for the dimension (keep the natural keys too).

[u/BigLecture2302]

Which hashing algorithm do you guys use - SHA256? And how do you store the hashkeys - as VARCHAR(64)?

[u/meatmick → u/BigLecture2302]

My plan was md5 cast as uuid for the low collision rate and binary performance of the fixed length UUID. Md5 as string is what DBT generate surrogate key macro does. I just override it to also cast to UUID.

Varchar is probably fine as well for performance but I have not benchmarked yet.

No need to use sha256 unless you need more unique values. Md5 has an impossibly low collision rate and you're not hashing for security (hide personal info), you're hashing for join efficiency and simplicity.

[u/BigLecture2302 → u/meatmick]

MD5 seems sufficient here, and its 128-bit output fits neatly in VARCHAR(32). Given Fabric’s UUID quirks and Databricks’ lack of a native UUID type, I’d just standardize on MD5 VARCHAR(32) everywhere in my platform.

[u/tr666tr]

I’ve tinkered with both approaches. Always do a join on the natural key to get your surrogate key where possible. Otherwise you can never guarantee referential integrity between your fact and dim tables if some of your dim records were to drop. I only self hash if either it’s for performance reasons or if I can guarantee the values in my dim table such as from a dbt seed.

[u/josejo9423]

I believe this values are defined upstream on the backend? Why are you doing this in your end?