Module 06: Raw Vault Architecture

Concept 01

Raw Vault Design Philosophy

🧠 ELI5 — Explain Like I’m Five

The Raw Vault is like a legal archive. When documents arrive, you file them exactly as they came — no editing, no re-formatting, no throwing anything away. Years later, when an auditor asks “what did your CRM say about customer X on March 3rd 2022?” you can pull the exact file. The Raw Vault does the same for data: every row ever received is preserved forever, unchanged, with a timestamp and a source label.

📚 Analogy

Think of the Raw Vault as a bank ledger. A bank never erases a debit or credit entry — every transaction is appended in chronological order. The ledger is the authoritative audit trail. Your Raw Vault is the ledger for all data that has ever flowed into your warehouse. Corrections don’t erase old entries; they append new ones with the corrected values.

The Three Commandments of the Raw Vault

No business rules applied. The Raw Vault contains exactly what the source sent. No data cleansing, no lookups, no joins, no derivations. If the source sends a NULL phone number, the vault stores NULL. If the source sends a nonsensical value, it stores that too. Business rules belong in the Business Vault layer.
Insert-only, never delete or update. The only mutation permitted is the optional LOAD_END_DATE update on Satellites (see Module 05). Every other operation is a pure INSERT. This is what makes the vault auditable and replayable.
Everything is hashed and sourced. Every row carries a RECORD_SOURCE identifier and a Hash Key derived deterministically from the business key. This enables parallel loading from multiple source systems without central coordination.

What the Raw Vault Does NOT Contain

No aggregations. No SUM, AVG, or COUNT results. All aggregations happen in the Business Vault or Information Marts.
No derived columns. No calculated fields, no flag columns derived by comparing two raw values. If CUSTOMER_STATUS = “ACTIVE” means something in the business, that derivation happens downstream.
No cross-source joins. The Raw Vault never merges attributes from CRM and ERP into a single row. Each source has its own Satellite. The Business Vault handles cross-source reconciliation.
No reference data resolution. If the source sends a currency code “USD,” the Raw Vault stores “USD.” Mapping it to “United States Dollar” via a currency reference table is a Business Vault concern.

💡

Why this matters for reloads: because the Raw Vault applies zero business rules, you can replay any load from staging and produce identical results. If a business rule changes, you only need to re-derive the Business Vault — not re-extract from the source. The Raw Vault is your permanent, unambiguous source of truth for the data as it was received.

Concept 02

Staging Layer Design

🧠 ELI5 — Explain Like I’m Five

Before raw data enters the vault, it goes through a staging layer — a preparation room where two critical things are added to every row: a Hash Key (a fingerprint of the business key) and a LOAD_DATE timestamp. Nothing else is transformed. Think of staging as the security checkpoint at an airport — you’re not changing the traveller; you’re adding their boarding pass and stamping their time of arrival.

What Staging Computes

The staging table is transient — it is truncated before each load cycle and rebuilt from the latest source extract. It serves only to feed the Raw Vault load. It is never queried for reporting.

Column Added in Staging	Formula	Purpose
`HUB_CUSTOMER_HK`	`MD5(UPPER(TRIM(CUSTOMER_ID)))`	Deterministic FK for Hub and Satellite joins
`HASH_DIFF`	`MD5(CONCAT(COL1,'\|',COL2,'\|',COL3))`	Change detection: compare to current satellite row
`LOAD_DATE`	`CURRENT_TIMESTAMP()` (set once per batch)	Technical load timestamp for all rows in this batch
`RECORD_SOURCE`	Constant string e.g. `'CRM_SALESFORCE_V2'`	Data lineage and audit trail

SQL — Staging Layer: STG_CUSTOMER (Snowflake / ANSI)

-- STG_CUSTOMER: staging view / table over raw source extract
-- LOAD_DATE is a single constant for the entire batch execution
CREATE OR REPLACE VIEW STG_CUSTOMER AS
SELECT
    -- Hash Keys computed here, once, used everywhere downstream
    MD5(UPPER(TRIM(CUSTOMER_ID)))                    AS HUB_CUSTOMER_HK,

    -- HashDiff: concat all payload columns with a separator
    -- Use COALESCE so NULLs produce consistent hash inputs
    MD5(
        COALESCE(FIRST_NAME,  '') || '|' ||
        COALESCE(LAST_NAME,   '') || '|' ||
        COALESCE(EMAIL,       '') || '|' ||
        COALESCE(PHONE,       '') || '|' ||
        COALESCE(COUNTRY_CODE,'')
    )                                                 AS HASH_DIFF,

    -- Single batch timestamp: all rows in this load share the same LOAD_DATE
    '2024-03-15 08:00:00.000'::TIMESTAMP             AS LOAD_DATE,
    'CRM_SALESFORCE_V2'                               AS RECORD_SOURCE,

    -- Raw source columns: no transformation, no business rules
    CUSTOMER_ID, FIRST_NAME, LAST_NAME,
    EMAIL, PHONE, COUNTRY_CODE, CREATED_DATE
FROM RAW_LANDING.CRM_CUSTOMER_EXTRACT;

⚠

Critical: LOAD_DATE must be a single constant per batch, not CURRENT_TIMESTAMP() called per-row. If LOAD_DATE is computed per-row, two rows from the same source extract will have microsecond-different timestamps, making it impossible to identify the “current” record or reconstruct the vault state at a specific batch window. Set LOAD_DATE once at the pipeline start and pass it as a parameter to every staging query.

Staging Table vs Staging View

You can implement the staging layer as either a materialised table (TRUNCATE then INSERT) or a view over the raw landing zone. The trade-off:

Materialised staging table: Hash computation happens once. All downstream vault loads (Hub, Links, multiple Satellites) read pre-computed hashes from the same table. More predictable performance. Required for parallel loading.
Staging view: Hash is recomputed every time a vault load queries the view. Simpler deployment but can significantly increase compute cost when several Satellites share the same staging view.

For production implementations, always materialise the staging layer.

Concept 03

Parallel Loading Strategies

🧠 ELI5 — Explain Like I’m Five

In a traditional ETL pipeline, you load one table, then the next. In Data Vault, the design deliberately enables all Hubs, all Links, and all Satellites to load simultaneously. A staging table is computed first, then 30 loads can run in parallel — no locks, no dependencies between them. This is why large DV implementations can load hundreds of tables in minutes instead of hours.

Why Parallel Loading Works in DV2

Three design properties make parallel loading safe without coordination:

Idempotent inserts. Every load uses a NOT EXISTS or LEFT JOIN / WHERE NULL guard. Running the same load twice produces no duplicates — the second run finds existing keys and skips them. Multiple loaders can run without stepping on each other.
No foreign key constraints enforced at load time. Data Vault does not enforce FK constraints as database-level constraints (they are logical). A Satellite can load before its Hub with no error; the Hub load following it will insert the key that the Satellite already references.
Hashes eliminate lookup joins. The Hash Key is computed from the business key in staging. There is no need to look up a surrogate key from a parent table during loading — removing the inter-table dependency that serialises traditional ETL.

SQL — Hub Load (idempotent INSERT)

-- Hub load: insert new business keys only
-- Safe to run in parallel with any Satellite or Link load
INSERT INTO HUB_CUSTOMER (HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE)
SELECT DISTINCT
    stg.HUB_CUSTOMER_HK,
    stg.CUSTOMER_ID     AS CUSTOMER_BK,
    stg.LOAD_DATE,
    stg.RECORD_SOURCE
FROM  STG_CUSTOMER stg
WHERE NOT EXISTS (
    SELECT 1 FROM HUB_CUSTOMER h
    WHERE h.HUB_CUSTOMER_HK = stg.HUB_CUSTOMER_HK
);
-- This insert runs concurrently with SAT loads and LINK loads
-- No locking across tables because each target table is independent

Orchestration pattern: materialise staging first (Step 1), then fire all Hub, Link, and Satellite loads in parallel fan-out (Step 2). Most orchestrators support this natively: Airflow with TaskGroup, dbt with parallel execution, Azure Data Factory with pipeline fan-out, or DBT with concurrent model execution. The key insight: no vault load in Step 2 reads from another vault table during its load — each reads only from staging.

Concept 04

Error Mart Pattern

🧠 ELI5 — Explain Like I’m Five

When source data arrives with problems — a customer ID that is NULL, an order amount that is negative, a date that is impossible — the Raw Vault still loads it unchanged. But it also flags the problem by writing an error record to a separate Error Mart table. The data is never discarded; you have the original in the vault AND a record of what was wrong, so data quality teams can fix the source and reprocess.

The Two Rules of the Error Mart

Rule 1: Never reject data from the Raw Vault. Even if a row is technically invalid, it goes into the Raw Vault. The vault’s job is to receive, not to judge. Rejection at the vault layer causes data loss and breaks the audit trail.
Rule 2: Always record the anomaly. Every row that fails a validation check writes a companion record to the Error Mart. The Error Mart is queryable, reportable, and actionable — it is the data quality dashboard for your engineers and data stewards.

SQL — Error Mart Table DDL

CREATE TABLE ERROR_MART.DQ_ERRORS (
    ERROR_ID         BIGINT       IDENTITY,
    LOAD_DATE        TIMESTAMP    NOT NULL,   -- batch that produced the error
    RECORD_SOURCE    VARCHAR(100) NOT NULL,   -- origin system
    TARGET_TABLE     VARCHAR(100) NOT NULL,   -- e.g. 'HUB_CUSTOMER'
    BK_VALUE         VARCHAR(500),             -- the raw business key value
    ERROR_TYPE       VARCHAR(50)  NOT NULL,   -- NULL_BK, INVALID_DATE, etc.
    ERROR_SEVERITY   CHAR(1)      NOT NULL,   -- W=Warning, E=Error, F=Fatal
    ERROR_MESSAGE    VARCHAR(2000),            -- human-readable description
    RAW_ROW_JSON     VARIANT,                  -- full source row snapshot
    RESOLVED_FLAG    CHAR(1)      DEFAULT 'N',-- Y when the root cause is fixed
    RESOLVED_DATE    TIMESTAMP    NULL,
    CONSTRAINT pk_dq_errors PRIMARY KEY (ERROR_ID)
);

SQL — Writing to the Error Mart during Staging

-- Step 1: load raw vault as normal (even bad rows)
-- Step 2: in parallel, write anomalies to Error Mart
INSERT INTO ERROR_MART.DQ_ERRORS (
    LOAD_DATE, RECORD_SOURCE, TARGET_TABLE,
    BK_VALUE, ERROR_TYPE, ERROR_SEVERITY, ERROR_MESSAGE, RAW_ROW_JSON
)
SELECT
    '2024-03-15 08:00:00'::TIMESTAMP AS LOAD_DATE,
    'CRM_SALESFORCE_V2'               AS RECORD_SOURCE,
    'HUB_CUSTOMER'                    AS TARGET_TABLE,
    COALESCE(CUSTOMER_ID, '[NULL]')  AS BK_VALUE,
    CASE
        WHEN CUSTOMER_ID IS NULL              THEN 'NULL_BK'
        WHEN LENGTH(TRIM(CUSTOMER_ID)) = 0    THEN 'EMPTY_BK'
        WHEN CREATED_DATE > CURRENT_DATE()   THEN 'FUTURE_DATE'
    END                                       AS ERROR_TYPE,
    'E'                                       AS ERROR_SEVERITY,
    'Business key is null or empty'           AS ERROR_MESSAGE,
    OBJECT_CONSTRUCT(*)::VARIANT            AS RAW_ROW_JSON
FROM RAW_LANDING.CRM_CUSTOMER_EXTRACT
WHERE CUSTOMER_ID IS NULL
   OR LENGTH(TRIM(CUSTOMER_ID)) = 0
   OR CREATED_DATE > CURRENT_DATE();

💡

Architect note: the Error Mart is not a dump. Build a daily report from it: errors by source system, errors by type, trending error rates. Treat it as a data quality SLA dashboard. Source teams with rising error rates get notified. The Error Mart also enables safe source replays — filter the raw landing to exclude known-bad rows identified by the Error Mart, then reload the staging and vault for corrected data.

Concept 05

Full vs Delta Loading

🧠 ELI5 — Explain Like I’m Five

A full load sends every row from the source every day — like re-photographing an entire album rather than just the new photos. A delta load sends only rows that changed since the last extract — just the new photos. Delta is faster, but harder to detect deletions. Data Vault handles both patterns cleanly because its insert-only, idempotent design means re-loading the same data never corrupts the vault.

Dimension	Full Load	Delta Load
Data volume per batch	Entire source table	Only changed rows
Deletion detection	Natural — absent rows = soft delete	Requires explicit CDC delete flag
Source system requirement	None — any system can do a full dump	Source must expose change timestamps or CDC log
Network and extract cost	High — scales with total table size	Low — scales with change volume
Vault idempotency behaviour	Re-running inserts nothing new (HashDiff matches)	Re-running inserts nothing new (same guarantee)
Typical use case	Reference tables, small dimensions, initial load	Large fact-equivalent sources, high-velocity data

Detecting Soft Deletes with Full Loads

Full loads enable a powerful pattern: if a customer was in yesterday’s full extract but is absent from today’s, you can infer a soft delete. Implement this with an Effectivity Satellite on the entity’s Hub, or with a dedicated SAT_CUSTOMER_DELETION satellite that tracks the deletion event.

SQL — Detecting Soft Deletes via Full Load Comparison

-- Find HKs that had IS_ACTIVE='Y' but are absent from today's full extract
-- These are candidates for soft deletion (apply grace period before closing)
INSERT INTO SAT_CUSTOMER_ACTIVE_STATUS (
    HUB_CUSTOMER_HK, LOAD_DATE, HASH_DIFF, RECORD_SOURCE, IS_ACTIVE, DELETION_REASON
)
SELECT
    sat.HUB_CUSTOMER_HK,
    '2024-03-15 08:00:00'::TIMESTAMP  AS LOAD_DATE,
    MD5('N|SOFT_DELETE')              AS HASH_DIFF,
    'CRM_SALESFORCE_V2'               AS RECORD_SOURCE,
    'N'                               AS IS_ACTIVE,
    'Absent from full extract'        AS DELETION_REASON
FROM SAT_CUSTOMER_ACTIVE_STATUS sat
WHERE sat.IS_ACTIVE  = 'Y'
  AND sat.LOAD_END_DATE IS NULL
  AND NOT EXISTS (
    SELECT 1 FROM STG_CUSTOMER stg
    WHERE stg.HUB_CUSTOMER_HK = sat.HUB_CUSTOMER_HK
  );

Grace period for deletions: apply the same caution as Effectivity Satellites — absent for one batch might mean a source outage, not a true deletion. Only mark as deleted after N consecutive missing batches. Store the absence count in the deletion satellite payload for auditability.

Concept 06

Interview Q&A: Raw Vault Architecture

Intermediate / Senior Questions

What business rules are permitted in the Raw Vault? ▶

None. The Raw Vault is a zero-business-rules zone. Every row is stored exactly as received from the source system. No data cleansing, no referential lookups, no derivations, no filtering of “invalid” rows.

The only transformations permitted in staging are: computing Hash Keys from business keys (a technical transformation, not a business rule), computing HashDiff from payload columns, stamping LOAD_DATE and RECORD_SOURCE. Everything else is a business rule that belongs in the Business Vault layer.

This constraint is what makes the Raw Vault an auditable, replayable archive. If a business rule changes, only the Business Vault needs to be recomputed — the Raw Vault data is permanent and unambiguous.

Why must LOAD_DATE be a single constant per batch, not CURRENT_TIMESTAMP() per row? ▶

Three concrete reasons:

(1) Snapshot consistency. All rows in one load batch represent a single logical snapshot of the source at one moment. If LOAD_DATE differs per row by milliseconds, you cannot reconstruct the vault state at a precise batch window — every query would need to know which rows are “from the same batch” via a separate batch metadata table.

(2) Point-in-Time query correctness. PIT tables and as-of queries rely on filtering satellite history by LOAD_DATE. With per-row timestamps, a customer’s satellite row might have LOAD_DATE 08:00:00.001 while another’s is 08:00:00.999 — they appear to be from different batches even though they were loaded in the same pipeline run.

(3) Idempotent reload safety. When re-running a batch (after a failure), you must reproduce the exact same LOAD_DATE. A pipeline that captures the batch start time as a parameter and passes it to all staging queries is deterministic; one that calls CURRENT_TIMESTAMP() per row produces different timestamps on re-run, creating duplicate satellite rows.

How does the Error Mart differ from simply rejecting bad rows at the source? ▶

Source-side rejection discards data permanently — there is no record that the bad row ever arrived. If the source is later corrected and replayed, there is no way to verify that the corrected row addresses the same anomaly.

The Error Mart approach:

(1) Preserves the bad data in the Raw Vault. The row is loaded unchanged, giving you the exact payload as received — including any anomalous values that might be symptoms of a deeper source problem.

(2) Creates an actionable audit record. The Error Mart record identifies the batch, the source, the specific validation that failed, and the raw row as JSON. Data stewards can investigate without needing to re-extract from the source.

(3) Enables controlled reprocessing. After the source is fixed, you can re-extract just the corrected rows, filter the staging data, and reload only those satellite versions — without touching the original bad rows in the vault.

When should you use a full load vs a delta load for a source system? ▶

Use a full load when: (1) the source table is small (<1M rows) and extract cost is negligible, (2) the source system cannot expose change timestamps or CDC logs, (3) you need soft-delete detection — absent rows in a full extract naturally indicate a deletion without requiring a delete flag, (4) it’s the initial historical load.

Use a delta load when: (1) the source table is large and full extraction would be slow or expensive, (2) the source system exposes a reliable change timestamp column or CDC stream, (3) the data velocity is high (thousands of changes per minute), (4) network bandwidth between source and landing zone is constrained.

The Data Vault’s idempotent INSERT patterns work equally well for both — the HashDiff check ensures that loading the same unchanged row twice (as in a full load re-sending stable data) inserts nothing new. The architecture imposes no preference; the decision is driven by source system capability and volume.

What makes Data Vault loading inherently parallel, and what is the one serialisation point? ▶

Data Vault loading is parallel because: (1) Hash Keys are computed from business keys in staging — no lookup joins to parent tables during vault loading, (2) FK constraints are logical, not enforced by the database engine, so Satellite loads don’t block on Hub loads, (3) every vault INSERT is idempotent, so concurrent writers on different target tables can’t corrupt each other.

The one serialisation point is staging materialisation. All Hub, Link, and Satellite loads must read from a fully materialised staging table to get consistent hash values. If you fired Satellite loads before staging was complete, you’d risk loading with partial or stale data. So the load order is strictly: (Step 1) materialise staging, then (Step 2) all vault loads in parallel. Within Step 2, there are no further ordering constraints.

✅ Module 06 Key Takeaways

The Raw Vault stores data exactly as received — no business rules, no cleansing, no derivations. It is an insert-only, auditable archive. This property allows full replays when business rules change.
Staging materialises Hash Keys, HashDiff, a single LOAD_DATE constant, and RECORD_SOURCE. LOAD_DATE must be a pipeline parameter, not a per-row CURRENT_TIMESTAMP() call, to ensure snapshot consistency and idempotent reloads.
Parallel loading is the default pattern: materialise staging first, then fire all Hub, Link, and Satellite loads simultaneously. Hash Keys eliminate cross-table lookup dependencies; insert idempotency eliminates race conditions.
The Error Mart never rejects data from the vault. Bad rows are stored unchanged alongside an Error Mart record that captures the violation type, severity, and full row payload. Data quality is monitored and acted on separately from vault ingestion.
Full loads enable natural soft-delete detection (absent rows = deleted). Delta loads minimise extract volume but require source-side change tracking. Data Vault’s idempotent inserts work correctly with both patterns.