Module 11: Cloud Data Vault

Concept 01

Cloud DV Architecture Overview

🧠 ELI5 — Explain Like I’m Five

On-premises Data Vault ran on fixed hardware — you bought servers and they were always there, always running. In the cloud, compute is rented by the second. This changes everything: you can spin up 100 parallel loaders for a few minutes and pay almost nothing, then shut them down. Cloud DV takes advantage of this elasticity to load faster and cost less than any on-premises equivalent.

How Cloud Changes the DV Architecture

Compute is elastic. Spinning up 50 parallel Satellite loads for 3 minutes costs the same as running 5 loaders for 30 minutes — or less. Architect for maximum parallelism; cloud billing rewards it.
Storage is cheap and separate from compute. In Snowflake, BigQuery, and Databricks, you pay for storage at blob-storage rates (pennies per GB/month) and for compute separately. This makes the insert-only, retention-forever DV pattern economically viable in a way it never was on-premises SAN storage.
Native semi-structured support. VARIANT (Snowflake), JSON (BigQuery), and struct columns (Delta/Spark) allow you to store raw source payloads alongside structured columns, enabling a schema-on-read Raw Vault variant for unstructured sources.
Zero-copy cloning. Snowflake and BigQuery allow you to clone an entire vault environment (dev/prod) in seconds with no data duplication cost. This makes dev/test/prod environment management trivial for DV teams.

💡

Architect insight: In cloud DV, the biggest cost lever is compute time per load window, not storage. Reducing a 2-hour nightly batch to 15 minutes through parallelism and incremental materialisation cuts your warehouse cost by ~87% for that window. Design your pipelines to be as parallel as possible, and use scheduled materialisation windows (not always-on warehouses) for batch loads.

Concept 02

Snowflake Data Vault Patterns

🧠 ELI5 — Explain Like I’m Five

Snowflake is the most popular cloud platform for Data Vault because its automatic micro-partitioning aligns perfectly with DV’s insert-only pattern. Every INSERT creates new micro-partitions; old partitions are never touched. Snowflake’s Time Travel feature lets you query any table as it was at any point in the past — which is redundant for satellites (which have built-in history) but invaluable for debugging load failures.

Snowflake-specific Optimisations

Cluster keys on SAT tables. Satellite tables grow large fast. Cluster on (HUB_{ENTITY}_HK, LOAD_DATE) to align micro-partitions with the most common query predicate (point-in-time lookup for a specific entity).
VARIANT columns for semi-structured payloads. Satellites that receive JSON payloads from APIs can store them as VARIANT alongside or instead of flattened columns. Snowflake’s columnar storage still prunes efficiently on the HK and LOAD_DATE cluster key.
Dynamic Tables for Business Vault. Snowflake Dynamic Tables can replace materialised view + scheduled task patterns for PIT and Bridge tables. They incrementally refresh on a defined lag, eliminating manual scheduling.
Zero-copy cloning for dev/test. CREATE DATABASE DEV_VAULT CLONE PROD_VAULT in seconds with no additional storage cost until the clone diverges from the original.

SQL — Snowflake: Satellite with Clustering & Time Travel

-- Satellite with Snowflake cluster key and data retention for Time Travel
CREATE OR REPLACE TABLE SAT_CUSTOMER_DEMOGRAPHICS (
    HUB_CUSTOMER_HK  CHAR(32)     NOT NULL,
    LOAD_DATE        TIMESTAMP_NTZNOT NULL,
    LOAD_END_DATE    TIMESTAMP_NTZNULL,
    HASH_DIFF        CHAR(32)     NOT NULL,
    RECORD_SOURCE    VARCHAR(200) NOT NULL,
    FIRST_NAME       VARCHAR(200),
    LAST_NAME        VARCHAR(200),
    DATE_OF_BIRTH    DATE,
    COUNTRY_CODE     CHAR(3),
    CONSTRAINT pk_sat_cust_demo PRIMARY KEY (HUB_CUSTOMER_HK, LOAD_DATE)
)
-- Cluster on HK + LOAD_DATE: aligns micro-partitions with PIT query pattern
CLUSTER BY (HUB_CUSTOMER_HK, DATE_TRUNC('day', LOAD_DATE))
DATA_RETENTION_TIME_IN_DAYS = 90;  -- Snowflake Time Travel window

-- Query vault state as of yesterday (Time Travel for debugging)
SELECT * FROM SAT_CUSTOMER_DEMOGRAPHICS
    AT (TIMESTAMP => DATEADD('day', -1, CURRENT_TIMESTAMP()))
WHERE HUB_CUSTOMER_HK = MD5('CUST-10042');

SQL — Snowflake Dynamic Table for PIT (Business Vault)

-- Snowflake Dynamic Table: auto-refreshes PIT_CUSTOMER every 10 minutes
CREATE OR REPLACE DYNAMIC TABLE PIT_CUSTOMER
    LAG = '10 minutes'
    WAREHOUSE = COMPUTE_WH
    REFRESH_MODE = INCREMENTAL
AS
WITH snapshot_dates AS (
    -- Generate daily snapshot dates for the last 3 years
    SELECT DATEADD('day', SEQ4(), '2022-01-01'::DATE) AS SNAPSHOT_DATE
    FROM TABLE(GENERATOR(ROWCOUNT => 1100))
)
SELECT
    h.HUB_CUSTOMER_HK,
    s.SNAPSHOT_DATE,
    MAX(demo.LOAD_DATE) FILTER (WHERE demo.LOAD_DATE <= s.SNAPSHOT_DATE)
        AS SAT_DEMOGRAPHICS_LD,
    MAX(cont.LOAD_DATE) FILTER (WHERE cont.LOAD_DATE <= s.SNAPSHOT_DATE)
        AS SAT_CONTACT_LD
FROM HUB_CUSTOMER h
CROSS JOIN snapshot_dates s
LEFT JOIN SAT_CUSTOMER_DEMOGRAPHICS demo ON demo.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
LEFT JOIN SAT_CUSTOMER_CONTACT      cont ON cont.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
GROUP BY h.HUB_CUSTOMER_HK, s.SNAPSHOT_DATE;

Snowflake cost tip: Satellite INSERT loads are very cheap because they only write new micro-partitions. The expensive operation is querying large Satellites without a cluster key aligned to your predicate. Always check SYSTEM$CLUSTERING_INFORMATION() on large SAT tables and re-cluster if the depth exceeds 3.

Concept 03

BigQuery Data Vault

🧠 ELI5 — Explain Like I’m Five

BigQuery is Google’s fully serverless data warehouse. Unlike Snowflake, you don’t manage virtual warehouses — queries just run. The billing model is per-byte-scanned, which makes partitioning and clustering essential for cost control in a Data Vault. Without them, every satellite query scans the entire table and you get a large invoice.

BigQuery-specific DV Patterns

Partition by LOAD_DATE. All Satellite tables should be partitioned by DATE(LOAD_DATE). This enables partition pruning: a query asking for today’s satellite records only scans today’s partition, not years of history.
Cluster by Hash Key. Within each partition, cluster on HUB_{ENTITY}_HK. Queries for a specific entity (the most common DV query) will scan only a small sorted block within the partition.
MERGE statement for Hub loads. BigQuery’s MERGE is efficient for the Hub idempotent insert pattern. On Satellite loads, prefer INSERT with a NOT EXISTS guard to avoid MERGE overhead on large tables.
Materialised Views for PIT tables. BigQuery Materialised Views incrementally refresh on DML changes to their base tables — making them ideal for PIT and Bridge tables in the Business Vault.
Authorised Views for data access control. Share raw vault data to downstream teams without granting access to underlying tables by creating authorised views per business domain.

SQL — BigQuery: Partitioned + Clustered Satellite

-- BigQuery: satellite partitioned by load date, clustered by hash key
CREATE OR REPLACE TABLE `project.raw_vault.SAT_CUSTOMER_DEMOGRAPHICS`
(
    HUB_CUSTOMER_HK  STRING        NOT NULL,
    LOAD_DATE        TIMESTAMP     NOT NULL,
    LOAD_END_DATE    TIMESTAMP,
    HASH_DIFF        STRING        NOT NULL,
    RECORD_SOURCE    STRING        NOT NULL,
    FIRST_NAME       STRING,
    LAST_NAME        STRING,
    DATE_OF_BIRTH    DATE,
    COUNTRY_CODE     STRING
)
PARTITION BY DATE(LOAD_DATE)            -- prune by date on all time-range queries
CLUSTER BY HUB_CUSTOMER_HK             -- fast lookup within partition by entity
OPTIONS (
    require_partition_filter = FALSE,   -- allow unfiltered queries (for FULL scans)
    partition_expiration_days = NULL    -- never expire: Raw Vault is permanent
);

-- BigQuery MERGE for Hub load (idempotent)
MERGE `project.raw_vault.HUB_CUSTOMER` T
USING (
    SELECT DISTINCT HUB_CUSTOMER_HK, CUSTOMER_ID AS CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE
    FROM `project.staging.STG_CUSTOMER`
) S
ON T.HUB_CUSTOMER_HK = S.HUB_CUSTOMER_HK
WHEN NOT MATCHED THEN
    INSERT (HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE)
    VALUES (S.HUB_CUSTOMER_HK, S.CUSTOMER_BK, S.LOAD_DATE, S.RECORD_SOURCE);

⚠

BigQuery cost trap: avoid SELECT * on satellite tables. BigQuery charges per byte scanned. A Satellite with 20 columns and 10 years of history charged for every query is expensive. Always SELECT only the columns you need. For PIT-based point-in-time queries, join to the PIT table first to get the LOAD_DATE, then query only the specific satellite partition. The partition pruning brings costs down by 95%+ versus a full scan.

Concept 04

Databricks & Delta Lake

🧠 ELI5 — Explain Like I’m Five

Databricks runs on Apache Spark with Delta Lake — an open-source storage format that adds ACID transactions, schema evolution, and time travel to Parquet files on cloud object storage. For Data Vault, Delta Lake is powerful because its transaction log gives you full history of every write (just like DV’s own audit trail), and Z-ordering replaces traditional clustering for fast lookups on Hash Keys.

Delta Lake DV Patterns

Z-order on Hub and Satellite tables. After loading, run `OPTIMIZE table ZORDER BY (HUB_CUSTOMER_HK)` to co-locate all rows for the same entity in the same files. Point-in-time queries for one entity go from full-file-scan to a few-file-scan.

Delta Lake Time Travel. `SELECT * FROM sat_customer VERSION AS OF 42` or `TIMESTAMP AS OF '2024-01-01'` gives you the table state at any past transaction. This is DV’s own history complemented by the storage layer’s own history.

Schema evolution. When a source adds a new column, Delta Lake’s `MERGE SCHEMA` option on an INSERT can automatically add the new column to the target table — enabling agile Satellite extension without DDL migrations.

Streaming with Delta Live Tables. DLT pipelines can continuously merge streaming CDC events into Hub, Link, and Satellite tables using the MERGE INTO pattern, enabling near-real-time vault loading (see Concept 06).

PySpark / SQL — Databricks: Hub Load with Delta MERGE

-- Databricks SQL: Hub load using Delta MERGE (idempotent)
MERGE INTO raw_vault.HUB_CUSTOMER target
USING (
    SELECT DISTINCT
        HUB_CUSTOMER_HK,
        CUSTOMER_ID AS CUSTOMER_BK,
        LOAD_DATE,
        RECORD_SOURCE
    FROM staging.STG_CUSTOMER
) source
ON target.HUB_CUSTOMER_HK = source.HUB_CUSTOMER_HK
WHEN NOT MATCHED THEN
    INSERT (HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE)
    VALUES (source.HUB_CUSTOMER_HK, source.CUSTOMER_BK,
            source.LOAD_DATE, source.RECORD_SOURCE);

-- After load: Z-order to optimise point-in-time queries
OPTIMIZE raw_vault.SAT_CUSTOMER_DEMOGRAPHICS
    ZORDER BY (HUB_CUSTOMER_HK, LOAD_DATE);

-- Time travel: see satellite as of a specific version
SELECT * FROM raw_vault.SAT_CUSTOMER_DEMOGRAPHICS
VERSION AS OF 158
WHERE HUB_CUSTOMER_HK = MD5('CUST-10042');

Concept 05

Cloud Cost Optimisation

The Five Cost Levers in Cloud DV

Lever	Action	Typical Savings
Warehouse sizing	Use XS warehouse for staging materialisation; scale up only for parallel vault loads	40–60% compute cost reduction
Load window compression	Maximise fan-out parallelism; 15-min window at 2x cost < 60-min window at 1x cost	Depends on data volume
Incremental satellite loads	HashDiff comparison ensures only changed rows are inserted; unchanged rows never written	60–90% storage growth reduction vs full loads
Cluster/partition alignment	Cluster satellite tables by HK so PIT queries scan <1% of data	90%+ scan reduction on BigQuery (direct billing impact)
Information Mart refresh strategy	Refresh mart tables only when underlying vault data changes; avoid time-based full rebuilds	50–80% mart compute cost reduction

💡

Real-world pattern: a typical DV implementation with 50 satellite tables loading 10M rows/day can run its entire nightly load in under 20 minutes on a Snowflake M warehouse (auto-suspended when done) for approximately $15–40/day total compute cost. Without parallelism and cluster keys, the same load on an always-on XL warehouse costs 10× more and takes 3× longer.

Concept 06

Interview Q&A: Cloud Data Vault

Architect-Level Questions

How does Snowflake’s micro-partitioning align with Data Vault’s insert-only design? ▶

Snowflake automatically partitions data into 16–512 MB compressed micro-partitions as rows are inserted. Because Data Vault tables are insert-only, every batch of inserts appends new micro-partitions without modifying existing ones. This means:

(1) No write amplification. Traditional row-store databases rewrite pages when rows are updated. DV on Snowflake never touches existing micro-partitions — a perfect alignment of design philosophies.

(2) Natural time-range partition pruning. Satellite INSERTs cluster naturally by LOAD_DATE (recent inserts = recent partitions). A query filtering on LOAD_DATE > ‘2024-01-01’ prunes all older micro-partitions automatically.

(3) Explicit clustering for HK lookups. The natural insertion order is by time, not by entity. Point-in-time queries that filter on HUB_CUSTOMER_HK must scan all micro-partitions without a cluster key. Defining a cluster key on (HUB_CUSTOMER_HK, DATE_TRUNC(‘day’, LOAD_DATE)) reorganises micro-partitions to co-locate rows for the same entity, enabling 90%+ partition pruning on entity-centric queries.

Why is BigQuery’s per-byte billing model a significant concern for Data Vault, and how do you mitigate it? ▶

Data Vault Satellite tables grow unboundedly by design — every change to every attribute produces a new row, forever. Without mitigation, a 5-year-old satellite with 500M rows would scan all 500M rows for every point-in-time query, regardless of the answer set size, costing $2.50+ per query at standard BigQuery pricing.

Mitigation strategies:

(1) Partition by DATE(LOAD_DATE). Partition pruning limits scans to relevant date ranges. A query for “current record” with LOAD_END_DATE IS NULL only scans the most recent partition (assuming LOAD_END_DATE is populated) or all partitions (if not). Populate LOAD_END_DATE or use a PIT table that pre-computes the latest LOAD_DATE per entity.

(2) Cluster by HUB_ENTITY_HK within each partition. Sorted blocks within each partition enable BigQuery to skip most blocks for single-entity lookups.

(3) Use PIT tables as query entry points. Route all mart queries through the PIT table, which pre-computes the relevant LOAD_DATE per entity per snapshot. The downstream satellite JOIN then hits a specific LOAD_DATE predicate, enabling full partition pruning.

(4) Use Materialised Views for Business Vault. BigQuery MVs cache query results and charge only for the incremental refresh, not a full recompute. Business Vault PIT and Bridge tables as MVs reduce mart query compute costs significantly.

What is the architectural difference between batch and streaming Data Vault loading? ▶

Batch loading: data is extracted from the source in discrete batches (hourly, daily), staged, and loaded into the vault. LOAD_DATE is a constant per batch. All rows in a batch share the same technical timestamp. This is the canonical DV loading pattern and supports full parallelism.

Streaming loading: CDC events (INSERT/UPDATE/DELETE from a transaction log like Debezium, Kafka, or Kinesis) are consumed continuously and loaded into the vault with near-real-time latency. Each event carries its own event timestamp. The vault loading logic must handle out-of-order events, duplicate delivery, and exactly-once semantics.

Key differences:

(1) LOAD_DATE. In batch, LOAD_DATE is the batch start time — a single constant. In streaming, LOAD_DATE is the event processing time or the source commit timestamp. The choice determines the semantics of “as of” queries.

(2) Idempotency. Batch loads are naturally idempotent (re-running inserts nothing new via HashDiff check). Streaming loads must implement exactly-once delivery guards (e.g., deduplication by event ID + commit LSN) to prevent duplicate satellite rows.

(3) Throughput vs latency trade-off. Batch maximises throughput via bulk parallel inserts. Streaming minimises latency but introduces per-event overhead. Many architectures use streaming for critical low-latency sources (financial transactions) and batch for bulk loads (overnight CRM extracts).

How would you design a cloud DV to support both batch and streaming sources in the same vault? ▶

Use a unified Raw Vault with source-specific RECORD_SOURCE values and satellite splitting by source:

(1) The same Hub table serves both batch and streaming sources. A Hub row for CUSTOMER_ID=10042 is inserted once regardless of whether it first arrives via batch or streaming. Hash keys are identical because the computation is deterministic.

(2) Separate Satellites per source and loading pattern. SAT_CUSTOMER_DEMOGRAPHICS_BATCH (loaded from nightly CRM extract) and SAT_CUSTOMER_EVENTS_STREAM (loaded from real-time Kafka events) are separate tables. They share the same Hub FK but track different attribute sets at different velocities.

(3) PIT tables in the Business Vault unify the timeline. A PIT table joins across both batch-loaded and stream-loaded satellites at a common snapshot date, giving downstream marts a single coherent view regardless of how each satellite was populated.

(4) Handle LOAD_DATE semantics consistently. Agree on a convention: LOAD_DATE for batch = batch processing start time; LOAD_DATE for streaming = event consumption time (not source commit time, unless bi-temporal). Document this choice in your data catalog, as it determines how “as-of” queries behave across the two satellite types.

✅ Module 11 Key Takeaways

Cloud platforms make DV economically viable at scale: compute is elastic, storage is cheap, and parallelism is free. Design your load pipelines for maximum concurrency to minimise the compute window and cost.
Snowflake DV: use cluster keys on (HUB_ENTITY_HK, DATE_TRUNC(‘day’, LOAD_DATE)) for satellite tables. Use Dynamic Tables for Business Vault PIT refresh. Zero-copy clone for dev/test environments.
BigQuery DV: partition all satellite tables by DATE(LOAD_DATE) and cluster by HK. Route mart queries through PIT tables to enable partition pruning. Use Materialised Views for Business Vault to reduce incremental refresh costs.
Databricks Delta Lake: Z-order satellite tables by HK after each load batch. Use Delta MERGE for idempotent Hub loading. Schema evolution with MERGE SCHEMA eliminates DDL migrations when sources add columns.
Streaming and batch loading can coexist in the same Raw Vault: same Hubs, separate source-specific Satellites. PIT tables in the Business Vault unify both timelines for downstream marts.