How to keep bi-temporal historization reproducible when the source’s primary key is conditional, incomplete, or context-dependent.
🧩 Part of the Model-Driven Data Quality series — exploring reproducible, metadata-driven patterns for historization, deduplication, and bi-temporal data management.
Introduction
In my previous article, Historizing Source Tables Without a Primary Key, I showed how we can build a reproducible, bi-temporal SCD2 pattern using technical hash keys instead of relying on source-defined primary keys.
But real-world data rarely fits neatly into theory.
Sometimes, we do receive a declared primary key — yet one of its columns can contain NULLs depending on the type of data in the same physical table.
For example, a single source table may store both actuals and forecasts, each following a different logical key definition.
In this article, we’ll explore how to historize such conditionally keyed datasets using the same reproducible hash-based foundation — and how to define logical primary key expressions for downstream data quality and modeling.
A metadata-driven historization pipeline: a single mixed source table (actuals & forecasts) is historized using a technical hash key, then split into logical views where subtype-specific primary keys and data quality checks are applied.
When “Primary Keys” Aren’t Always Primary
In the ideal world, every source table comes with a well-defined primary key — a stable, non-null identifier for each business object.
In the real world, we often get something else entirely.
Many source systems reuse the same physical table to store different logical datasets, distinguished by a discriminator column such as:
data_type = 'actual' | 'forecast' | 'budget'granularity = 'account' | 'customer' | 'region'
Each subtype may follow a different primary key definition. For example:
When all these records land in the same physical table, one of the primary key columns (like scenario) may be NULL for rows where it’s not relevant — making the overall “physical” PK inconsistent and partially null.
The Problem with Nulls in Keys
Most SCD2 or bi-temporal frameworks assume a complete, non-null key. Once a key column contains nulls, you break:
- Change detection — hash(primary_key) no longer uniquely identifies business state.
- Reproducibility — identical deliveries may hash differently due to null semantics.
- Downstream DQ checks — duplicate detection becomes unreliable.
This makes naïve historization impossible if you rely purely on the declared PK.
The Solution: Technical Hash + Logical Key Expression
We separate two identities:
Technical Identity (for historization)
A deterministic hash over all business attributes, ensuring every record — regardless of subtype or nulls — receives a unique and reproducible signature. This is the same business_hash pattern described in Historizing Source Tables Without a Primary Key.
sha256(concat_ws('|', col1, col2, col3, ...))
This guarantees we can:
- Detect changes between deliveries.
- Preserve duplicates deterministically.
- Reprocess the same input with identical outcomes.
Logical Primary Key Expression (for data quality and modeling)
Defined per logical subtype — an expression that dynamically selects the relevant key columns depending on the record type.
CASE
WHEN data_type = ‘actual’ THEN concat(account_id, ‘:’, date)
WHEN data_type = ‘forecast’ THEN concat(account_id, ‘:’, date, ‘:’, scenario)
ELSE NULL
END AS logical_pk_expr
This expression acts as the semantic key used for:
- Duplicate detection and DQ reporting.
- Defining uniqueness constraints in logical models.
- Creating clean, subtype-specific access views.
Metadata-Driven Example
In a metadata-driven YAML configuration, this could look like:
historization:
business_hash:
algo: sha256
columns: [“account_id”, “date”, “scenario”, “data_type”, “amount”]
identity:
key_template: “${business_hash}:${dup_rn|pad=8}”
logical_keys:
– name: logical_pk_actual
expression: “CASE WHEN data_type=’actual’ THEN concat(account_id, ‘:’, date) END”
– name: logical_pk_forecast
expression: “CASE WHEN data_type=’forecast’ THEN concat(account_id, ‘:’, date, ‘:’, scenario) END”
dq_rules:
– rule: duplicates_by_logical_key
key: “coalesce(logical_pk_actual, logical_pk_forecast)”
severity: warning
This design keeps the technical layer fully generic — capable of historizing any mix of data — while allowing the logical layer to define precise business-level uniqueness.
Logical Views per Subtype
After historization, we expose Source Access Views for each subtype. These are lightweight logical layers that:
- Filter the data to the relevant discriminator value.
- Drop non-applicable columns.
- Declare primary keys appropriate to that subtype.
For example:
CREATE OR REPLACE VIEW src_vw_actual AS
SELECT
account_id,
date,
amount,
valid_from,
valid_to
FROM hist.sales
WHERE data_type = ‘actual’;
ALTER VIEW src_vw_actual
ADD CONSTRAINT pk_actual PRIMARY KEY (account_id, date);
and similarly for src_vw_forecast, defining (account_id, date, scenario) as the logical PK.
Benefits
✅ Reproducible historization
The technical hash guarantees deterministic change detection, regardless of nulls or subtype logic.
✅ Flexible logical modeling
Logical PK expressions allow each subtype to define its own business rules.
✅ Consistent DQ checks
Data quality rules can evaluate uniqueness per subtype, without blocking ingestion.
✅ Metadata transparency
All logic (hash, subtype discrimination, PK expression) lives declaratively in metadata — traceable and versioned alongside the model.
Closing Thoughts
Historization is a technical preservation problem — capturing what was delivered, exactly as it was.
Primary keys are a logical modeling concern — describing what uniquely identifies a business object in a specific context.
When both worlds collide — such as when a single physical table mixes multiple logical datasets — we keep them apart:
- Use a technical hash for stable, bi-temporal historization.
- Use logical primary key expressions for subtype-specific data quality and modeling.
- Reunite both through metadata, keeping your platform deterministic, auditable, and semantically rich.
Historization captures the facts. Logical keys interpret them.
🧩 Part of the Model-Driven Data Quality series — exploring reproducible, metadata-driven patterns for historization, deduplication, and bi-temporal data management.
🔗 Related Reading
- Historizing Source Tables Without a Primary Key
How to design a fully reproducible bi-temporal SCD2 pattern without relying on logical keys. - Correct, Governed Deduplication in SQL (Without Breaking Your Pipelines)
A deterministic, auditable approach to detect, flag, and quarantine duplicates with row-level DQ breadcrumbs. - Upcoming: Bridging Historization, Deduplication, and DQ Dashboards — a deeper dive into how these metadata-driven patterns tie together in a unified data observability framework.