Let’s Talk About the Problem Nobody Wants to Debug
If you’ve spent any real time building data pipelines on Databricks, you’ve probably seen this at some point:
It’s late. A dashboard is showing incorrect numbers.
After digging for hours, you trace the issue back to…
- Duplicate records
- CDC jobs are silently missing updates
- Or, in the worst cases, both
It’s not a glamorous problem – but it’s one of the most common causes of data quality issues in production pipelines.
In a medallion architecture (bronze → silver → gold), problems in bronze don’t stay there. They ripple downstream:
- Metrics get inflated because the same record is counted multiple times.
- Updates in source systems don’t reflect in analytics.
- Debugging becomes difficult because the issue originated upstream.
Why Is This Problem So Hard?
At first glance, it seems simple:
“Why not just drop duplicates?”
In reality, pipelines are messier:
- Source systems retry → duplicate records arrive
- Data arrives out of order → timestamps can’t be trusted
- No reliable “change flag” → full records arrive every time
- Comparing many columns → expensive and hard to maintain
Example:
customer_id | customer_name | amount |
C001 | Alice | 100 |
C001 | Alice | 100 |
These rows are clearly duplicates – but how do you efficiently detect this at scale across millions of records?
The Core Idea: One Hash to Rule Them All
Instead of comparing column by column:
Combine relevant columns → generate a single hash value
- Same data → same hash
- Changed data → different hash
This simplifies everything:
- Duplicate detection
- Change detection (CDC)
MD5 vs SHA-256 – Which One Should You Use?
Recommendation:
Feature | MD5 | SHA-256 |
Speed | Fast | Slower |
CPU Cost | Low | Higher |
Output Size | 32-char hex (128-bit) | 64-char hex (256-bit) |
Collision Resistance | Moderate | Very High |
Best For | CDC & Deduplication | Sensitive data |
Recommendation:
- Use MD5 for most pipelines (fast and sufficient)
- Use SHA-256 for sensitive or regulated data
Implementation in Databricks (Bronze → Silver)
Data Setup
Initial Data (Bronze vs Silver)
Bronze Table (Raw Data)
The Bronze table contains:
- Duplicate records
- Updated records
Silver Table (Existing Data)
The Silver table represents:
- Previously processed data
Step 1: Generate Hash Columns
Output: Hash Generation (MD5)
Source Data with Hash
Notice:
- Duplicate records have identical hash values
- Updated records generate different hashes
Target Data with Hash
Optional: Using SHA-256
For scenarios requiring stronger guarantees:
- SHA-256 produces longer and more collision-resistant hashes
- Comes with a slightly higher computational cost
Step 2: Remove Duplicates
Output: Deduplication
- Duplicate records are removed using hash comparison.
- This ensures clean input before CDC processing.
Step 3: CDC Logic (New + Updated Records)
Output: CDC Result
Interpretation:
- Records not found in target → NEW
- Records with changed hash → UPDATED
Step 4: Write to Silver Layer
Output: Silver Table
- Only business columns are written
- Helper columns are excluded
Things to Watch Out For
- Hash only relevant business columns
- Maintain consistent column order
- Handle null values properly
- Exclude helper columns before writing
- Do not use hash as a business key
Best Practices
- Deduplicate before CDC
- Use MD5 for performance-heavy workloads
- Use SHA-256 when needed
- Align schema before writing
- Optionally stores hash for future incremental loads
Final Thoughts
Handling CDC and duplicate data at scale can be complex – but hash-based techniques make it significantly simpler.
They provide:
- Simplicity
- Performance
- Scalability
Instead of comparing multiple columns, you only need to compare a single hash value.
Key Takeaway
“In large-scale data pipelines, efficiency comes from simplifying comparisons – and hash keys make that possible.”
Hash-based techniques provide a scalable and efficient approach to handling CDC and duplicate data in modern Databricks pipelines.
