A practical approach to consistent, testable data pipelines.
Use source subtypes as translation blocks to integrate data into a clean, business-aligned model without duplication.
Press enter or click to view image in full size
The Problem with Ad-Hoc Joins
Without discipline, integration pipelines often look like spaghetti:
- One query pulls from raw source tables.
- Another joins a derived table directly.
- Yet another mixes historized and current records inconsistently.
The result? Fragile, irreproducible transformations.
Access Views: The Contract Layer
We solve this with Access Views (AVs).
Every downstream transformation reads only from AVs, never directly from base tables.
For example:
Access Views:
- Compose supertype data + derived sets + reference lookups.
- Enforce SCD2 overlap logic.
- Guarantee a stable contract to consumers.
Example (simplified):
CREATE VIEW av_party_core AS
SELECT
p.party_sk,
p.party_bk,
p.party_type,
p.name_full,
a.age_years, r.name_en AS country_name,
GREATEST(p.valid_from_ts, a.valid_from_ts) AS valid_from_ts,
LEAST(p.valid_to_ts, a.valid_to_ts) AS valid_to_ts,
CASE WHEN LEAST(p.valid_to_ts, a.valid_to_ts) IS NULL THEN 1 ELSE 0 END AS is_current
FROM party_core p
LEFT JOIN party_core_age a
ON a.party_sk = p.party_sk
AND (p.valid_to_ts IS NULL OR p.valid_to_ts > a.valid_from_ts)
AND (a.valid_to_ts IS NULL OR a.valid_to_ts > p.valid_from_ts)
LEFT JOIN country_ref r ON r.country_code = p.country_code;
Dual SCD2 Historization
We historize both raw and derived data with dual SCD2:
- Functional time → the business validity window (e.g., contract dates).
- Technical time → the load/versioning time (when our pipeline created the record).
This guarantees reproducibility. If derivation rules change tomorrow, we still keep yesterday’s derived outputs for audit.
Why This Matters
- Stable interfaces → AVs shield consumers from upstream changes.
- Minimal duplication → only derivations are persisted.
- Reproducibility → dual SCD2 ensures we can always explain past results.
- Composable pipelines → AVs chain cleanly, no leapfrogging queries.
Putting It All Together
Our integration layer now looks like this:
- Source layer → raw historized data.
- Source subtypes → translation per system.
- Supertype table → one table with subtype columns + discriminator.
- Subtype views → filtered projections.
- Characteristic tables → persisted derivations, dual SCD2.
- Access Views → stable contracts combining mapped + derived + reference data.
- Delivery models → denormalized stars, wide tables, APIs.
Final Thoughts
We end up with an integration layer that is:
- Business-shaped → aligned to the LDM, easy to explain.
- Decomposed → lineage and historization at the right granularity.
- Lean → minimal duplication, derived sets only.
- Reproducible → dual SCD2 guarantees auditability.
- Composable → Access Views provide stability and clarity.
This is beyond Data Vault: we keep decomposition but tie it back to the business model.