Access Views, SCD2, and Building Reproducible Pipelines

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:

• av_party_core
• av_person_core
• av_order_line

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:

1. Source layer → raw historized data.
2. Source subtypes → translation per system.
3. Supertype table → one table with subtype columns + discriminator.
4. Subtype views → filtered projections.
5. Characteristic tables → persisted derivations, dual SCD2.
6. Access Views → stable contracts combining mapped + derived + reference data.
7. 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.