Hashing on Databricks
Hashing is a core element of Data Vault modeling. Hash keys are used to uniquely identify Hubs, Links, and Satellites, and they need to be consistent, reproducible, and efficient. A common debate is whether to use widely adopted hashing algorithms like MD5 or SHA-1, or to opt for faster and smaller hash functions such as xxhash64, which Databricks natively supports.
The question is simple: What if we stored hash keys as 64-bit integers (int64) using xxhash64 instead of 128-bit MD5 values? On the surface, this looks attractive — faster generation, better join performance, and reduced storage. But as we’ll explore in this article, the trade-offs around collisions, scalability, and platform independence make this a risky choice in Data Vault 2.0 architectures.
In this article:
Why Smaller Hash Keys Look Tempting
There are clear benefits to using smaller hash values such as int64:
- Speed of generation: Algorithms like xxhash64 or CRC-64 are significantly faster than MD5 or SHA-1. OpenSSL and Linux benchmarks consistently show xxhash64 outperforming older cryptographic functions.
- Join performance: Joining 64-bit integers is naturally faster than joining 128-bit binary or 32-character string columns.
- Storage efficiency: An int64 hash key requires only 8 bytes, compared to 16 bytes for binary MD5 or 32 bytes for an MD5 stored as a string.
From a performance perspective, the appeal is undeniable. But in Data Vault, performance is not the only concern. The fundamental question is: How safe are these smaller hashes when used as surrogate keys at scale?
The Risk of Collisions
A hash collision occurs when two different inputs produce the same hash value. In Data Vault, this means two different business keys could be treated as the same Hub, Link, or Satellite record — corrupting your data integrity.
With 64-bit hashes, the number of possible unique values is 2^64. While that sounds huge, probability tells a different story when you start loading millions or billions of rows. The so-called birthday paradox makes collisions far more likely than intuition suggests.
For example:
- With just 10,000 records, a 32-bit hash already has a 1 in 100 chance of collision.
- A 64-bit hash greatly reduces the risk, but at large scales (hundreds of millions of rows), the probability becomes uncomfortably high.
- A 128-bit hash (MD5) pushes collision risk into the realm of trillions of rows before it becomes statistically relevant.
That’s why MD5 and SHA-1 — despite being slower — are still standard in Data Vault: they provide mathematically safe keyspace sizes for large enterprise datasets.
Performance vs. Integrity
The argument for int64 hashing often emphasizes query performance:
- Joins on numeric columns are faster than joins on strings or binary.
- xxhash64 is faster to compute than MD5.
These points are true. But in practice, the cost of a collision far outweighs the performance gains. A single collision can undermine the entire lineage of your data warehouse. Once data integrity is compromised, every downstream analytic and report is suspect.
As a result, most Data Vault practitioners will gladly accept the slightly higher CPU cost of MD5 or SHA-1 in exchange for peace of mind.
Platform Portability Matters
Another issue with xxhash64 is platform availability. While Databricks supports it, you also need to consider:
- Can you compute xxhash64 in Snowflake, BigQuery, SQL Server, or Oracle?
- Can you reproduce xxhash64 consistently in Python, Java, or ETL tools?
- Will the algorithm be supported 5–10 years from now?
One of the strengths of MD5 and SHA-1 is their ubiquity. They are implemented in nearly every database, programming language, and ETL platform. This cross-platform reproducibility is essential when building a Data Vault that may span multiple systems. By contrast, xxhash64 locks you into Databricks (or requires custom implementations elsewhere).
Why 128-Bit is the Safe Minimum
Let’s compare hash sizes:
- CRC-32: Very fast, but collisions appear after ~10,000 records. Unusable for Data Vault.
- xxhash64 / CRC-64: Better, but collisions become likely as datasets grow into the hundreds of millions.
- MD5 (128-bit): Standard choice. Safe up to trillions of rows.
- SHA-1 (160-bit): Provides even more headroom for extremely large datasets.
For most enterprises, MD5 hits the sweet spot: fast enough, widely supported, and statistically collision-free at realistic data volumes.
Alternative Approaches
If performance is a serious concern, consider these approaches instead of shrinking hash size:
- Binary storage: Store MD5 as a 16-byte binary instead of a 32-character string. This cuts storage in half and improves join performance.
- Partitioning strategies: Optimize joins by partitioning your Data Vault tables, reducing the need for full-table joins.
- Hardware acceleration: Modern CPUs have optimized instructions for MD5 and SHA-1, making them faster than you might expect.
- Consider SHA-256 only if required: While SHA-2 offers stronger guarantees, it’s rarely necessary in Data Vault and adds performance overhead.
Summary: Should You Use xxhash64 in Data Vault?
While xxhash64 looks attractive in Databricks because of its speed and smaller footprint, it’s not a good fit for Data Vault 2.0:
- Collision risk is too high for large-scale data warehouses.
- Portability is limited — you risk vendor lock-in.
- Long-term maintainability suffers if your algorithm isn’t standardized across platforms.
In Data Vault, data integrity always comes first. That’s why MD5 (128-bit) or SHA-1 (160-bit) remain the recommended standards for hash keys. They provide the balance of performance, portability, and collision safety needed for enterprise-scale solutions.
Conclusion
If you’re working in Databricks, it might be tempting to adopt xxhash64 for hash keys. But resist that temptation. The risks of collisions and platform lock-in far outweigh the benefits. Stick with MD5 or SHA-1 for your Data Vault hash keys, store them efficiently as binary values, and optimize performance through storage and join strategies.
Remember: a faster broken key is still a broken key. In Data Vault, correctness and consistency are always the highest priority.
Watch the Video
Meet the Speaker
Michael Olschimke
Michael has more than 15 years of experience in Information Technology. During the last eight years he has specialized in Business Intelligence topics such as OLAP, Dimensional Modelling, and Data Mining. Challenge him with your questions!
