Are MD5 hashes a good default for surrogate keys?

2026-05-16 assumed audience: data and analytics engineers

In the extra-load-transform (ELT) paradigm of data engineering, a popular method to generate surrogate keys in the transformation step is to compute a MD5 hash of uniquely identifying columns and store it as a string. This is the approach of dbt’s built-in generate_surrogate_key macro.

The arguments for using a hash instead of an incrementing integer sequence are well worn. Hash keys are deterministic, idempotent, and work just as well on views as on materialized tables. These properties are particularly important for the typical workflow in the ELT paradigm, where views are common and tables downstream of the raw data load are re-built on each run.

But what are we giving up by using a full MD5 hash stored as a string?

Memory and performance considerations

MD5 hashes have 128 bits of entropy, but the MD5 function in most SQL dialects returns a 32-character hexadecimal string representation, which in practice often means ~32 bytes in memory. Compare this to 64-bit integer keys which are 8 bytes.

Database join performance tends to be dominated by memory access^{1 2}. Larger keys generally have worse cache locality and memory bandwidth characteristics. We would thus expect a join using string MD5 hash keys to be noticeably slower than one using 64-bit integer keys.

So, ideally we would want a surrogate key generation method that is both idempotent and fits in a 64-bit integer. Do major data warehouses have a reasonable way of generating a deterministic 64-bit integer hash? Well, we can always truncate the MD5 32-character text to 16 characters and convert that a 64-bit integer³. That’s probably the best option unless you’re in Snowflake which has this built-in with md5_number_lower64.

How much of a speedup can we expect from using a more efficient truncated MD5 key?

Benchmark

The benchmark runs an aggregation on a fact table with 100 million rows, that joins to a dimension table with 100 thousand rows. Not tiny; but not “big data” either. This is a typical dataset at my day job. It runs the same query with both string MD5 hash keys, and 64-bit integer truncated MD5 hash keys. I ran this in Snowflake on an XS warehouse.

The source code for the benchmark is available here.

That’s a ~30% speedup. Of course, there is a lot variation between data warehouses in how they implement join strategies, vectorization and compression, which will affect the magnitude of the speedup. However, the broad strokes of modern “shared-nothing” data warehouse systems are similar enough that I would expect the performance improvement to remain noticeable across systems.

Key collisions

A MD5 hash has 128 bits of entropy. Plugging this into a hash collision calculator, even at 100 trillion rows in a table the collision chance is less than 1 in a billion, which is to say that it “just works” for any practical dataset⁴. A 64-bit truncated version on the other hand has a ~2.7% chance at 1,000,000,000 rows, which means that collisions can’t always be assumed away.

Full 128-bit hash keys are a safe default, but many tables in a typical data warehouse will never get close to the size where 64-bit hash keys have a meaningful risk of collision. An employees table will probably be well under 100 million rows, and so will your corporate offices or product SKUs (unless you’re amazon.com).

So you probably have tables with cardinalities that fit comfortably within the range where 64-bit hash collisions are vanishingly unlikely, and if you’re building a star schema, end-user queries usually involve one or more joins on surrogate keys. Spend the extra minute to check if the cardinality of your data allows for the more efficient key type – given how often Databricks or Snowflake costs are a concern (pick your favorite expensive data warehouse!) it may be well worth your time.

If 30% faster queries let you downsize a L Snowflake warehouse for BI that’s on 8 hours a day, 5 days a week to an M warehouse, that’s ~$20,000 per annum saved⁵.