Module 12: DV to Dimensional Modeling

Concept 01

Information Mart Architecture

🧠 ELI5 — Explain Like I’m Five

Data Vault stores everything with maximum flexibility and history. But business analysts don’t want to write 8-table joins to get a customer’s current name. Information Marts are pre-computed, flattened, business-friendly views built from the vault. They answer specific business questions in a form that BI tools, analysts, and ML models can consume directly — without needing to understand vault structure.

📚 Analogy

The Raw Vault is a library with every book ever printed, catalogued precisely. The Business Vault is the reference desk that helps you find related books. The Information Mart is the curated reading list for a specific subject — a selection already assembled and ordered for a particular audience. The library still exists; the reading list just makes it accessible to people who don’t know the cataloguing system.

Three Types of Information Mart Output

Star Schema. A central fact table surrounded by dimension tables. The classic BI reporting structure. Built from vault Links (facts) and Hub+Satellite combinations (dimensions). Optimised for OLAP queries with GROUP BY and aggregations.
Wide / flat table (One Big Table). A denormalised table with all dimension attributes pre-joined onto each fact row. Zero joins needed at query time. Common for ML feature stores and self-service analytics where users write ad-hoc SQL.
Data Product. A business-domain-scoped, versioned, SLA-backed dataset. Exposed via a well-defined interface (API, Snowflake share, BigQuery authorised view). Built on top of vault data but treated as an independent product with its own schema contract.

Key principle: Information Marts should never read directly from the Raw Vault. They always read through the Business Vault (PIT tables, Bridge tables). The PIT table pre-computes the LOAD_DATE pointer for each satellite at each snapshot date, making mart queries simple, fast, and predictable regardless of how complex the underlying vault history is.

Concept 02

DV to Star Schema

🧠 ELI5 — Explain Like I’m Five

A Star Schema has two types of table: a Fact table (what happened: sales, clicks, shipments — with measures like amount and quantity) and Dimension tables (who/what/where/when was involved: customers, products, dates). In Data Vault, the Link table maps to a Fact, and Hub + Satellites map to a Dimension. The mart just flattens and joins them.

The Mapping Pattern

LINK_ORDER_PRODUCT → FACT_SALES. The Link records the relationship (which order contained which product). A Satellite on the Link (SAT_L_ORDER_PRODUCT_DETAILS) holds the measures — quantity, unit price, discount. Together they give you one fact row per order-product line.
HUB_CUSTOMER + SAT_CUSTOMER_* → DIM_CUSTOMER. Join the Hub to its Satellites (via PIT table) to get the current or as-of attribute values. Assign a surrogate dimension key (integer) for BI tool compatibility.
Date dimension. Data Vault does not model time as a Hub — dates are denormalised as foreign keys in Link Satellites. Build a separate DIM_DATE calendar table in the mart layer.

SQL — DIM_CUSTOMER built from Hub + PIT + Satellites

-- DIM_CUSTOMER: built for a specific snapshot date via PIT table
-- The PIT table pre-resolves which SAT LOAD_DATE to use for each entity
CREATE OR REPLACE VIEW MART.DIM_CUSTOMER AS
SELECT
    -- Surrogate key for BI tools (they don't like CHAR(32) as FK)
    ROW_NUMBER() OVER (ORDER BY h.HUB_CUSTOMER_HK)  AS CUSTOMER_DIM_SK,
    h.CUSTOMER_BK                                       AS CUSTOMER_BK,
    h.HUB_CUSTOMER_HK                                   AS CUSTOMER_HK,

    -- Demographic attributes (from SAT via PIT LOAD_DATE pointer)
    demo.FIRST_NAME,
    demo.LAST_NAME,
    demo.DATE_OF_BIRTH,
    demo.COUNTRY_CODE,

    -- Contact attributes
    cont.EMAIL,
    cont.PHONE,

    -- Financial attributes
    fin.CREDIT_LIMIT,
    fin.RISK_SCORE

FROM HUB_CUSTOMER           h

-- PIT table tells us which LOAD_DATE to join for each satellite
JOIN BIZ_VAULT.PIT_CUSTOMER  pit
    ON  pit.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
    AND pit.SNAPSHOT_DATE = CURRENT_DATE()   -- or pass a specific date

LEFT JOIN SAT_CUSTOMER_DEMOGRAPHICS demo
    ON  demo.HUB_CUSTOMER_HK = pit.HUB_CUSTOMER_HK
    AND demo.LOAD_DATE       = pit.SAT_DEMOGRAPHICS_LD  -- exact PIT pointer

LEFT JOIN SAT_CUSTOMER_CONTACT cont
    ON  cont.HUB_CUSTOMER_HK = pit.HUB_CUSTOMER_HK
    AND cont.LOAD_DATE       = pit.SAT_CONTACT_LD

LEFT JOIN SAT_CUSTOMER_FINANCIALS fin
    ON  fin.HUB_CUSTOMER_HK  = pit.HUB_CUSTOMER_HK
    AND fin.LOAD_DATE        = pit.SAT_FINANCIALS_LD;

SQL — FACT_SALES built from Link + Link Satellite

-- FACT_SALES: Link provides entity relationships; Link SAT provides measures
CREATE OR REPLACE VIEW MART.FACT_SALES AS
SELECT
    -- Dimension surrogate keys (looked up from DIM tables)
    dc.CUSTOMER_DIM_SK,
    dp.PRODUCT_DIM_SK,
    dd.DATE_DIM_SK,

    -- Degenerate dimensions (no matching dim table needed)
    lnk.HUB_ORDER_HK          AS ORDER_HK,

    -- Measures from Link Satellite
    ls.QUANTITY_ORDERED,
    ls.UNIT_PRICE,
    ls.DISCOUNT_AMOUNT,
    ls.QUANTITY_ORDERED * ls.UNIT_PRICE - ls.DISCOUNT_AMOUNT AS NET_SALES_AMOUNT

FROM  LINK_ORDER_CUSTOMER_PRODUCT    lnk
JOIN  SAT_L_ORDER_CUSTOMER_PRODUCT_DETAILS  ls
    ON  ls.LINK_ORDER_CUSTOMER_PRODUCT_HK = lnk.LINK_ORDER_CUSTOMER_PRODUCT_HK
    AND ls.LOAD_END_DATE IS NULL   -- current satellite record for this link
JOIN  MART.DIM_CUSTOMER  dc  ON  dc.CUSTOMER_HK = lnk.HUB_CUSTOMER_HK
JOIN  MART.DIM_PRODUCT   dp  ON  dp.PRODUCT_HK  = lnk.HUB_PRODUCT_HK
JOIN  MART.DIM_DATE      dd  ON  dd.DATE_VALUE  = ls.ORDER_DATE;

Concept 03

DV to Wide / Flat Tables

🧠 ELI5 — Explain Like I’m Five

A wide table (One Big Table / OBT) pre-joins everything a specific team needs into one giant row. Instead of a data analyst writing 8-table JOINs to answer a question about a customer’s purchase history, they just query one table and filter. Wide tables are faster for analysts, easier for ML models, and simpler for self-service. The trade-off is storage cost and refresh latency.

When to Use Wide Tables

ML feature store: each row = one entity (customer, product) with all features pre-computed and pre-joined. No joins at training time — maximises throughput for large model training runs.
Self-service analytics: business users who write SQL but don’t understand the vault structure. One table per business domain (customer, order, product) that they can freely filter and aggregate.
Operational dashboards: dashboards that run 100+ queries per minute benefit from a materialised wide table over a complex mart view that re-joins the vault on every render.

SQL — Wide Customer Table (One Big Table pattern)

-- Wide Customer OBT: current snapshot of all customer attributes from all satellites
-- Materialised daily; partitioned by COUNTRY_CODE for regional team access control
CREATE OR REPLACE TABLE MART.WIDE_CUSTOMER
PARTITION BY COUNTRY_CODE AS
SELECT
    -- Identity
    h.CUSTOMER_BK,
    h.HUB_CUSTOMER_HK,

    -- Demographics (from PIT-resolved LOAD_DATE)
    demo.FIRST_NAME, demo.LAST_NAME,
    demo.DATE_OF_BIRTH,
    DATEDIFF('year', demo.DATE_OF_BIRTH, CURRENT_DATE()) AS AGE,
    demo.COUNTRY_CODE, demo.GENDER_CODE,

    -- Contact
    cont.EMAIL, cont.PHONE,

    -- Financials
    fin.CREDIT_LIMIT, fin.RISK_SCORE, fin.PAYMENT_TERMS,

    -- Loyalty
    loy.LOYALTY_TIER, loy.POINTS_BALANCE,

    -- Computed (Business Vault derived attributes)
    bv.LIFETIME_ORDER_COUNT,
    bv.LIFETIME_NET_SPEND,
    bv.DAYS_SINCE_LAST_ORDER,

    CURRENT_TIMESTAMP() AS SNAPSHOT_TS

FROM HUB_CUSTOMER            h
JOIN BIZ_VAULT.PIT_CUSTOMER   pit ON pit.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
                                   AND pit.SNAPSHOT_DATE = CURRENT_DATE()
LEFT JOIN SAT_CUSTOMER_DEMOGRAPHICS  demo ON demo.HUB_CUSTOMER_HK=pit.HUB_CUSTOMER_HK AND demo.LOAD_DATE=pit.SAT_DEMOGRAPHICS_LD
LEFT JOIN SAT_CUSTOMER_CONTACT       cont ON cont.HUB_CUSTOMER_HK=pit.HUB_CUSTOMER_HK AND cont.LOAD_DATE=pit.SAT_CONTACT_LD
LEFT JOIN SAT_CUSTOMER_FINANCIALS    fin  ON fin.HUB_CUSTOMER_HK=pit.HUB_CUSTOMER_HK AND fin.LOAD_DATE=pit.SAT_FINANCIALS_LD
LEFT JOIN SAT_CUSTOMER_LOYALTY       loy  ON loy.HUB_CUSTOMER_HK=pit.HUB_CUSTOMER_HK AND loy.LOAD_DATE=pit.SAT_LOYALTY_LD
LEFT JOIN BIZ_VAULT.COMPUTED_SAT_CUSTOMER  bv ON bv.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK;

Concept 04

SCD Type 1/2/3/6 from Data Vault

🧠 ELI5 — Explain Like I’m Five

Traditional dimensional modeling needs you to decide upfront whether a customer dimension is Type 1 (overwrite history), Type 2 (keep all versions), Type 3 (keep current and previous only), or Type 6 (hybrid). Data Vault records all historical versions in the Satellite so you can produce any SCD type from the same source data — you decide the SCD type in the mart, not in the vault.

SCD Type	Behaviour	How to Build from DV Satellite
Type 1	Overwrite: always show current value only	Query satellite with `LOAD_END_DATE IS NULL` (or PIT LOAD_DATE). No history in the mart.
Type 2	Track all history as separate rows with effective dates	Project all satellite rows, converting LOAD_DATE → EFF_FROM and LOAD_END_DATE → EFF_TO. Each satellite row becomes one SCD2 row.
Type 3	Keep current and previous value only; two columns	Use LAG(attribute, 1) OVER (PARTITION BY HK ORDER BY LOAD_DATE) to project current and one prior value into `COL_CURRENT` and `COL_PREVIOUS`.
Type 6	Type 2 rows PLUS a Type 1 current-value column on every row	Build SCD2 rows from satellite, then add a JOIN or window function to stamp the current value onto every historical row.

SQL — SCD Type 2 Dimension from Satellite History

-- SCD Type 2 dimension from satellite history
-- Each satellite row = one SCD2 row with effective date range
CREATE OR REPLACE VIEW MART.DIM_CUSTOMER_SCD2 AS
SELECT
    ROW_NUMBER() OVER (ORDER BY h.HUB_CUSTOMER_HK, demo.LOAD_DATE)
                                            AS CUSTOMER_DIM_SK,
    h.CUSTOMER_BK,
    h.HUB_CUSTOMER_HK,

    demo.FIRST_NAME, demo.LAST_NAME,
    demo.EMAIL, demo.COUNTRY_CODE,

    -- Effective date range: SAT LOAD_DATE becomes effective-from
    demo.LOAD_DATE                         AS EFF_FROM_DATE,
    COALESCE(demo.LOAD_END_DATE,
             '9999-12-31'::TIMESTAMP)    AS EFF_TO_DATE,

    -- Current-row flag (SCD2 standard)
    CASE WHEN demo.LOAD_END_DATE IS NULL
         THEN 'Y' ELSE 'N' END            AS IS_CURRENT_ROW

FROM HUB_CUSTOMER h
JOIN SAT_CUSTOMER_DEMOGRAPHICS demo
    ON demo.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK;
-- Note: LOAD_END_DATE must be populated for this pattern to work correctly.
-- If using PIT-only approach (no LOAD_END_DATE), compute EFF_TO via LEAD().

💡

The critical DV advantage: with a traditional DW, choosing SCD Type 1 means you lose history forever. With Data Vault, the satellite stores all history regardless. You can add a SCD Type 2 dimension mart years later and backfill it with complete historical data from the satellite. The vault never forgets — the mart decides how much history to expose.

Concept 05

Data Product Design

🧠 ELI5 — Explain Like I’m Five

A Data Product treats a dataset like a software product — it has an owner, a documented interface (schema contract), SLAs (freshness, availability), and a versioning policy. Downstream teams can depend on it without worrying that it will change unexpectedly. Data Vault is the perfect foundation for Data Products because the raw vault decouples source system changes from the product interface.

The Five Attributes of a Good Data Product

Discoverable. Listed in a data catalog (Collibra, DataHub, dbt docs) with a plain-English description, owner contact, and sample queries.
Addressable. Has a stable, versioned endpoint — a Snowflake share name, a BigQuery table with a release tag, or a dbt semantic model name that does not change between minor updates.
Trustworthy. Has documented freshness SLA (“updated daily by 07:00 UTC”), row-count lineage checks, and a data quality score exposed in the catalog.
Self-describing. Column-level descriptions in the schema, tagged with sensitivity labels (PII, GDPR, financial). No consumer should need to ask the owner what CREDIT_LIMIT_USD means.
Interoperable. Follows organisation-wide naming conventions and uses common domain keys (CUSTOMER_BK, PRODUCT_BK) that join cleanly across products from different domains.

YAML — dbt Data Product Schema Contract (schema.yml)

# dbt schema.yml: Data Product contract for WIDE_CUSTOMER
models:
  - name: wide_customer
    description: |
      Current-snapshot customer data product. All attributes as-of today's date.
      Owner: Customer Domain Team | SLA: refreshed daily by 06:00 UTC
      Version: 2.1.0 | Breaking changes require 30-day notice period
    meta:
      owner: customer-domain@company.com
      freshness_sla: "06:00 UTC daily"
      data_product_version: "2.1.0"
    columns:
      - name: customer_bk
        description: "Natural business key from CRM. Stable identifier."
        tests: [not_null, unique]
      - name: email
        description: "Primary contact email. PII — masked in non-prod environments."
        meta:
          sensitivity: PII
          gdpr_category: contact_data
      - name: credit_limit
        description: "Approved credit limit in USD. From ERP financials satellite."
        tests: [not_null, { dbt_utils.accepted_range: { min_value: 0 } }]

Versioning discipline: use semantic versioning for Data Products (MAJOR.MINOR.PATCH). Adding a column is MINOR (backward-compatible). Renaming or removing a column is MAJOR (breaking). Alert all consumers of a MAJOR change with a 30-day deprecation window. Store each major version as a separate schema or view alias (WIDE_CUSTOMER_V1, WIDE_CUSTOMER_V2) until the prior version is sunset.

Concept 06

Interview Q&A: DV to Dimensional Modeling

Architect-Level Questions

Should Information Marts query the Raw Vault directly or always go through the Business Vault? ▶

Always through the Business Vault (PIT and Bridge tables). Never directly from the Raw Vault.

Three reasons:

(1) Performance. Direct satellite queries require a MAX(LOAD_DATE) subquery or LOAD_END_DATE filter per entity to find the current record. With millions of entities and hundreds of satellite rows per entity, this is enormously expensive without pre-computation. PIT tables pre-compute the exact LOAD_DATE for each satellite at each snapshot date, reducing mart joins to equality lookups instead of range scans.

(2) Consistency. If two mart queries both need “the current customer record,” they should produce the same result regardless of the millisecond they run. A PIT table with a defined snapshot date granularity ensures all mart queries reference the same LOAD_DATE for the same snapshot — without PIT, concurrent queries hitting a live satellite can see different “current” records if a load is in progress.

(3) Separation of concerns. The Raw Vault is a persistence layer; the Business Vault is the query layer. Mart queries that bypass this boundary couple report logic to physical vault structure, making vault refactoring (e.g., splitting a satellite, renaming a column) break mart code. PIT and Bridge tables act as a stable interface between the vault and consumers.

How does Data Vault enable you to produce SCD Type 2 dimensions retroactively? ▶

Because the Raw Vault Satellite is insert-only and stores every version of every attribute with a LOAD_DATE timestamp, the complete SCD2 history already exists in the satellite from day one — you simply haven’t materialised it as a mart dimension yet.

In a traditional DW, if you loaded a Type 1 (overwrite) customer dimension for 3 years and then decide to switch to Type 2, you have no historical data to build the Type 2 history from — it was overwritten.

With DV, your satellite may have 50 rows per customer spanning 3 years. To build a SCD2 dimension: project all satellite rows, set EFF_FROM = LOAD_DATE, set EFF_TO = the next row’s LOAD_DATE (or NULL for the current row), assign surrogate keys. You get the complete 3-year SCD2 history immediately, without re-extracting anything from the source.

This is the architectural proof that Data Vault decouples the storage decision (keep all history) from the presentation decision (which SCD type to show consumers). You decide the SCD type at mart build time, not at vault design time.

What is a Data Product and how does it differ from an Information Mart? ▶

An Information Mart is a technical artefact: a schema, a set of tables and views designed for BI or analytical queries. It may or may not have formal SLAs, ownership, or versioning.

A Data Product is a governed, versioned, SLA-backed, discoverable dataset with an owner who treats it like a product team treats a software API. It has:

(1) A schema contract. Column names, types, and semantics are documented and versioned. Breaking changes require a deprecation process.

(2) Freshness and availability SLAs. Consumers can depend on it being updated by a specific time each day with a defined error budget.

(3) Data quality metrics. Row counts, null rates, referential integrity scores are monitored and exposed in a catalog. Consumers know the data quality level before querying.

(4) Access control. Sensitive columns (PII, financial) are masked or access-controlled by consumer tier. The product owner manages access grants, not a central DBA.

In practice, a Data Product is often built on top of an Information Mart — the mart provides the physical query layer, and the Data Product layer adds governance, discoverability, and the consumer-facing contract.

✅ Module 12 Key Takeaways

Information Marts are the business-facing query layer. They always read through the Business Vault (PIT and Bridge tables) — never directly from the Raw Vault. PIT tables pre-compute the satellite LOAD_DATE for each snapshot, turning complex range queries into equality lookups.
Links map to Fact tables; Hub + Satellite combinations map to Dimension tables. Star Schemas give BI tools optimised GROUP BY performance; Wide/OBT tables give self-service analysts and ML pipelines zero-join access.
Data Vault enables any SCD type from the same underlying satellite history. Choose the SCD type in the mart, not at vault design time. You can add SCD2 retroactively using years of pre-existing satellite history — something impossible with traditional overwrite-based ETL.
Data Products elevate Information Marts to governed, versioned, SLA-backed assets with documented schema contracts. Use semantic versioning, catalog documentation, and PII tagging. Breaking changes require consumer notification and a deprecation window.
The DV philosophy of separation of concerns means: Raw Vault stores everything, Business Vault enables queryability, Information Marts present domain-specific views, Data Products add governance. Each layer has a single responsibility.