Reproducible Bi-Temporal SCD2 Using _hash(business_columns)_ + _row_number()_ per Hash
🧩 Part of the Model-Driven Data Quality series — exploring reproducible, metadata-driven patterns for historization, deduplication, and bi-temporal data management.
In a metadata-driven data platform, historization is the foundation of reproducibility and auditability. Our bi-temporal SCD2 design records both valid-time (business reality) and system-time (warehouse load time), allowing us to reconstruct any dataset as it was known at any point in time.
Most implementations assume that the source defines a primary key — but in reality, many source systems don’t. They simply deliver snapshots or extracts of attributes without a declared unique identifier.
The Key Insight
For bi-temporal historization, we don’t actually need a logical primary key. We only need a deterministic identity to detect changes between deliveries.
That identity comes from the hash of business attributes. We use this hash to determine inserts, updates, and deletes across successive deliveries.
However, when duplicates exist — multiple identical rows producing the same hash — we can’t distinguish them unless we extend the key. That’s where the row number per hash comes in.
Hash-Based Change Detection
In every historization batch:
- We compute a business hash from the defining attributes.
- We compare hashes between deliveries to classify records as inserts, updates, or deletes.
- We use this delta to maintain the bi-temporal SCD2 tables (open/close versions, update PIT tables, etc.).
This mechanism works even when no primary key exists — because the hash key acts as the technical identity for change detection.
The only case we can’t handle directly is duplicate rows with the same hash, because they appear identical in the change set.
Preserving Duplicates Reproducibly
We don’t deduplicate, and we don’t throw data away. Instead, we extend the hash with a row number calculated per hash group, using a deterministic ordering:
row_number() over (
partition by hash(business_columns)
order by ingest_line_no, sha256(raw_payload)
)
This ensures that even identical records within a single delivery receive a unique, stable identity.
We then define:
business_hash_ext = concat(hash(business_columns), ‘:’, lpad(dup_rn, 8, ‘0’))
This extended hash allows us to preserve every row exactly as it was delivered, while maintaining reproducible, idempotent historization.
Determinism and Auditability
Because the ordering is deterministic (based on persisted attributes like ingest_line_no or a tie-breaker hash),
- Reprocessing the same delivery produces identical results.
- Shuffling the file order doesn’t change the outcome.
- Downstream SCD2 logic remains intact.
This is what we mean by reproducible historization:
Same input → same hashes → same change set → same historical state.
Relationship to Governed Deduplication
This pattern complements the governed deduplication approach described in Correct, Governed Deduplication in SQL (without breaking your pipelines).
The difference in purpose is simple:
Press enter or click to view image in full size
So while we don’t require a primary key for historization, we do use it (if available) later in the data pipeline to:
- Identify which rows are duplicate business instances,
- Flag and report them in data quality dashboards, and
- Prevent double counting in downstream integrations or analytics.
In other words:
The hash key drives change detection and historization, while the primary key (if defined) helps mark and interpret duplicates for quality governance.
Metadata-Driven Implementation
Declaratively, this looks like:
historization:
business_hash:
algo: sha256
columns: [col1, col2, col3]
duplicates:
strategy: row_number_per_hash
order_by:
– ingest_line_no
– sha256_raw_payload
identity:
key_template: “${business_hash}:${dup_rn|pad=8}”
dq_rules:
detect_duplicates:
key: primary_key
label: ‘DQ_DUPLICATE’
severity: warning
This means:
- The historization process doesn’t depend on the presence of a primary key.
- The DQ and reporting layers can still use one to detect and report business-level duplicates.
Why This Matters
- Reproducibility — Re-running the same load produces exactly the same SCD2 timeline.
- Auditability — Every input record is traceable to a unique hash-row-number combination.
- Governance — Later DQ stages can leverage logical keys to mark or filter duplicates without changing the historical truth.
- Transparency — The model captures both how the data changed and how it was delivered.
Closing Thoughts
Historization is not about cleaning data — it’s about preserving it accurately and reproducibly. When the source doesn’t define a key, we define identity through hashes and deterministic row numbering, not through arbitrary deduplication.
Then, downstream, data quality governance (as in Correct, Governed Deduplication in SQL) can interpret, flag, and visualize duplicates while your bi-temporal architecture stays pure and fully auditable.
Historization preserves the truth.
Deduplication interprets it.
Metadata connects the two.
🔗 Related Article
Correct, Governed Deduplication in SQL (without breaking your pipelines) How to detect, flag, and quarantine duplicates deterministically — without halting your data pipelines.
This companion article explains the governed deduplication pattern used downstream in your data quality and consumption layers. Together, both articles show how to:
- Preserve every delivered record reproducibly in your bi-temporal historization, and
- Apply governed deduplication later to flag and monitor duplicates while keeping data products clean and auditable.