1. The Problem
Upstream system emitted 50,000 duplicate payment records with new primary keys. Schema validation passed, but financial dashboards overstated revenue by $1.2M.
2. What I Initially Thought
3. What I Learned
Data DNA fingerprinting calculates a SHA-256 cryptographic hash over business field combinations (`sha2(concat_ws('||', tenant_id, amount, timestamp), 256)`).
4. What I Built
Data DNA Fingerprint calculation module in PySpark generating `data_dna_hash` for every record to block semantic duplicates at Silver layer.
from pyspark.sql.functions import sha2, concat_ws, col
# Generate immutable Data DNA SHA-256 fingerprint over core business payload
df_with_dna = df.withColumn(
"data_dna_hash",
sha2(concat_ws("||", col("tenant_id"), col("amount"), col("event_timestamp")), 256)
)
# Filter duplicate business events regardless of primary key mutation!
deduped_df = df_with_dna.dropDuplicates(["data_dna_hash"])5. The Experiment
Duplicate upstream records with mutated primary keys passed schema checks, corrupting financial reports by $1.2M.
Implemented SHA-256 Data DNA fingerprint generation and duplicate key suppression in Silver layer.
100% of duplicate business payloads detected and quarantined regardless of primary key mutation.
6. What Went Wrong
Included a volatile `ingestion_timestamp` field in the SHA-256 hash calculation, causing identical payloads to produce different hashes.
7. Engineering Decision & Trade-offs
Restricted Data DNA hash calculation strictly to immutable business domain attributes.
8. What I Would Do Differently in Production
Maintain a bloom filter index of Data DNA hashes for fast lookup during high-throughput streaming deduplication.
Questions I Can Now Answer Confidently in an Interview:
- What is Data DNA fingerprinting and how does it catch semantic duplicates?
- Why do traditional primary key checks fail when upstream systems retry requests with new IDs?
- How do you select the correct business columns to include in a Data DNA hash function?