Building a Scalable Data Vault 2.0 Model in the Modern Data Stack: Hubs, Links, and Satellites with dbt and Snowflake

Introduction

You've inherited a data warehouse that's held together with duct tape and optimism. Three source systems feed customer data, each with its own concept of what a "customer" is. Your CRM uses email as the primary key. Your billing system uses a numeric account ID. Your support platform uses a UUID generated at ticket creation. Every quarter, when the business wants to answer "how many customers do we have?", someone spends two weeks reconciling these definitions in a spreadsheet. Sound familiar?

This is precisely the problem Data Vault 2.0 was designed to solve. It's not a silver bullet — no modeling pattern is — but for organizations dealing with multiple, heterogeneous source systems that change over time, Data Vault provides a principled, auditable, and scalable foundation for the raw layer of your warehouse. When combined with dbt's transformation capabilities and Snowflake's cloud architecture, you get a system that can absorb schema changes without rebuilding your entire model, trace every fact back to its source, and load data from multiple systems in parallel without the brittle join dependencies that plague traditional star schemas at ingestion time.

By the end of this lesson, you'll have built a working Data Vault 2.0 model from scratch using dbt and Snowflake. We'll move through the theory quickly and spend most of our time in the SQL, the dbt YAML, and the design decisions that separate a maintainable vault from a theoretical exercise.

What you'll learn:

• The core Data Vault 2.0 entities — Hubs, Links, and Satellites — and exactly when to use each one
• How to generate hash keys and load dates correctly, and why getting this wrong corrupts your entire vault
• How to implement Hubs, Links, and Satellites as incremental dbt models in Snowflake
• How to use dbt macros and the datavault4dbt package to eliminate boilerplate at scale
• How to structure your vault project layers and connect the vault to mart-layer consumption models
• Performance patterns specific to Snowflake: clustering keys, search optimization, and warehouse sizing for vault loads

---

Prerequisites

This is an expert-level lesson. You should already be comfortable with:

• dbt Core or Cloud at an intermediate level — if you need a refresher, start with the Introduction to dbt (Data Build Tool) before continuing
• Snowflake fundamentals including virtual warehouses, roles, and basic DDL
• Incremental model patterns in dbt — we'll build on these heavily; Incremental Models at Scale: Strategies for Efficiently Processing Late-Arriving Data and Partition Pruning in dbt is the right prerequisite reading
• SQL proficiency including window functions, CTEs, and MERGE semantics
• A basic understanding of data modeling alternatives so you can reason about trade-offs

You'll also need a Snowflake account (a trial works fine) and a dbt project initialized with a Snowflake connection.

---

Data Vault 2.0: The Mental Model Before the SQL

Before writing a single line of SQL, you need to internalize one fundamental principle: Data Vault separates structure from context from history. Everything else flows from that.

Traditional dimensional modeling asks you to design for query patterns. A star schema knows that analysts will filter by date and product and roll up revenue. That prior knowledge is embedded in its structure. This is efficient for reporting but brittle for integration — when a new source system arrives or a business rule changes, you often have to rebuild.

Data Vault makes a different trade-off: the raw vault layer is purely structural. It doesn't know or care what questions analysts will ask. It just answers: "Where do business objects live? How do they relate to each other? What did each source system say about them, and when?" The analytical perspective — the thing that actually answers business questions — lives in mart layers built on top of the vault.

The Three Core Constructs

Hubs represent unique business keys. A Hub is the authoritative record that a business object — a customer, a product, a location — exists. It contains exactly three things: a hash of the business key, the business key itself, and metadata about when and from where the record was first seen. Hubs never get updated. Once a row is inserted, it is never touched again. This is what gives the vault its auditability.

Links represent relationships between Hubs. When a customer places an order, that relationship is captured in a Link. Links contain hash keys pointing to the Hubs they connect, plus the same load metadata. Like Hubs, Link rows are never updated. A new relationship between the same entities creates a new Link row (or more precisely, the same Link row already exists and is idempotent to re-insert).

Satellites are where everything interesting lives. Satellites store the descriptive attributes — a customer's email address, name, and loyalty tier — and they capture the full history of changes. Every time a source system sends a new value for a customer attribute, a new Satellite row is inserted with a new load timestamp. You never update or delete Satellite rows. This is how Data Vault achieves its complete historical record.

Key insight: Data Vault's insert-only pattern is not just a convention — it's the source of its core guarantees. Because rows are never modified, you can always audit exactly what your system believed at any point in time, and you can replay any load window without side effects. This also plays beautifully with Snowflake's architecture, where write amplification from UPDATEs carries a real performance cost.

There are additional constructs in the full DV2.0 spec — Point-In-Time tables, Bridge tables, Reference tables — but Hubs, Links, and Satellites are the foundational layer. We'll touch on PITs and Bridges when we get to mart integration.

---

Setting Up Your Snowflake Environment

Let's establish the database structure before writing dbt models. You'll want clear schema separation between your staging layer (where raw source data lands), your raw vault (Hubs, Links, Satellites), and your business vault or mart layer.

-- Run this in Snowflake as SYSADMIN or equivalent
CREATE DATABASE IF NOT EXISTS ANALYTICS;

CREATE SCHEMA IF NOT EXISTS ANALYTICS.STAGING;     -- Raw source data, typically loaded by Fivetran/Airbyte
CREATE SCHEMA IF NOT EXISTS ANALYTICS.RAW_VAULT;   -- Hubs, Links, Satellites
CREATE SCHEMA IF NOT EXISTS ANALYTICS.BUSINESS_VAULT; -- PITs, Bridges, Reference tables
CREATE SCHEMA IF NOT EXISTS ANALYTICS.MARTS;       -- Star schema consumption layer

-- Dedicated warehouse for vault loads (separate from query warehouse)
CREATE WAREHOUSE IF NOT EXISTS VAULT_LOAD_WH
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  COMMENT = 'Used for Data Vault ETL loads';

CREATE WAREHOUSE IF NOT EXISTS ANALYTICS_WH
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  COMMENT = 'Used for analytical queries against marts';

The separate warehouses matter. Vault loads are write-heavy and benefit from larger compute during load windows. Analytical queries against the mart layer have very different characteristics. Mixing them means your load jobs compete with analyst queries for resources — the kind of thing that causes Friday afternoon performance incidents.

For a deeper treatment of Snowflake access control structure, see Configuring Role-Based Access Control in Snowflake and BigQuery: Warehouses, Schemas, and Row-Level Security for Analytics Teams, which covers the role hierarchy and privilege grants you'll need in a production environment.

---

Designing Hash Keys: The Foundation Everything Else Rests On

Hash keys are the surrogate keys of Data Vault. Unlike a warehouse-generated sequence (AUTOINCREMENT in Snowflake), hash keys are deterministic — given the same input, you always get the same hash. This is what allows you to load data from multiple sources in parallel without coordinating on key assignment, and to join across Hub-Link-Satellite boundaries without lookup tables.

The Hashing Standard

Data Vault 2.0 specifies MD5 or SHA-256 as the hash algorithm. SHA-256 is preferred for new implementations due to its collision resistance. In Snowflake:

-- SHA-256 hash of a single business key
SELECT SHA2(UPPER(TRIM('customer@example.com')), 256) AS customer_hk;

-- For composite keys, concatenate with a consistent separator
SELECT SHA2(
    UPPER(TRIM('customer@example.com')) || '||' || UPPER(TRIM('US')),
    256
) AS customer_hk;

Three non-negotiable rules:

1. Standardize case before hashing. CUSTOMER@EXAMPLE.COM and customer@example.com must produce the same hash key. Always UPPER() or always LOWER() — pick one and enforce it everywhere.
2. Trim whitespace. Source systems are inconsistent. A trailing space in one system creates a phantom duplicate in your Hub.
3. Use a consistent separator for composite keys. The separator must not appear in the business key values themselves. || is the DV2.0 community standard.

Warning: Getting the hashing standard wrong is catastrophic and hard to recover from. If your staging models inconsistently apply case normalization — maybe one source UPPERs the key and another doesn't — you'll end up with duplicate Hub rows for the same real-world entity. Catching this in production means a full vault rebuild. Establish a dbt macro for hash key generation and use it everywhere, no exceptions.

Creating the Hash Key Macro

In your dbt project, create macros/generate_hash_key.sql:

{% macro generate_hash_key(columns, alias='hk') %}
    SHA2(
        CONCAT_WS('||',
            {% for col in columns %}
                UPPER(TRIM(COALESCE(CAST({{ col }} AS VARCHAR), '^^')))
                {%- if not loop.last %},{% endif %}
            {% endfor %}
        ),
        256
    ) AS {{ alias }}
{% endmacro %}

The COALESCE(..., '^^') handles NULLs explicitly. Without this, a NULL in any component of a composite key produces a NULL hash — and you can't use NULL as a hash key. The ^^ sentinel value is the DV2.0 community convention for representing NULL in composite keys.

---

Building the Staging Layer

The staging layer transforms raw source data into a format ready for vault loading. Each staging model adds the hash keys and load metadata that every vault table needs. This is dbt's staging layer doing the heavy lifting before data touches the vault.

Imagine we have two source systems feeding customer and order data: a Salesforce CRM and a Shopify e-commerce platform. Both are loaded into ANALYTICS.STAGING by an ingestion tool like Fivetran or Airbyte.

-- models/staging/stg_crm__customers.sql
WITH source AS (
    SELECT * FROM {{ source('crm', 'customers') }}
),

hashed AS (
    SELECT
        -- Hash Keys
        {{ generate_hash_key(['email_address'], alias='customer_hk') }},

        -- Business Keys
        email_address AS customer_bk,

        -- Descriptive attributes (for Satellite)
        first_name,
        last_name,
        phone_number,
        loyalty_tier,
        is_active,

        -- Load metadata
        CURRENT_TIMESTAMP()                AS load_dts,
        'CRM_SALESFORCE'                   AS record_source,

        -- Used for Satellite change detection
        SHA2(CONCAT_WS('||',
            COALESCE(UPPER(TRIM(first_name)), '^^'),
            COALESCE(UPPER(TRIM(last_name)), '^^'),
            COALESCE(UPPER(TRIM(phone_number)), '^^'),
            COALESCE(UPPER(TRIM(loyalty_tier)), '^^'),
            COALESCE(CAST(is_active AS VARCHAR), '^^')
        ), 256)                            AS hashdiff

    FROM source
)

SELECT * FROM hashed

The hashdiff column is a hash of all the descriptive attributes in the Satellite. When you load a Satellite, you compare the incoming hashdiff against the most recent hashdiff for that Hub key. If they match, nothing has changed and you skip the insert. If they differ, a new row gets inserted. This is how you detect changes without column-by-column comparisons.

---

Building Hub Models

A Hub for customers looks like this. Note the careful use of {{ config(materialized='incremental') }} — Hubs are quintessentially incremental. You never want to full-refresh a Hub in production because that would re-insert every row you've ever seen.

-- models/raw_vault/hubs/hub_customer.sql
{{
    config(
        materialized='incremental',
        unique_key='customer_hk',
        on_schema_change='append_new_columns',
        cluster_by=['customer_hk'],
        tags=['raw_vault', 'hub']
    )
}}

WITH source_crm AS (
    SELECT
        customer_hk,
        customer_bk,
        load_dts,
        record_source
    FROM {{ ref('stg_crm__customers') }}
),

source_shopify AS (
    SELECT
        customer_hk,
        customer_bk,
        load_dts,
        record_source
    FROM {{ ref('stg_shopify__customers') }}
),

-- Union all sources
all_sources AS (
    SELECT * FROM source_crm
    UNION ALL
    SELECT * FROM source_shopify
),

-- In an incremental run, only consider new business keys
new_records AS (
    SELECT
        customer_hk,
        customer_bk,
        load_dts,
        record_source,
        ROW_NUMBER() OVER (
            PARTITION BY customer_hk
            ORDER BY load_dts ASC
        ) AS row_num
    FROM all_sources

    {% if is_incremental() %}
    WHERE customer_hk NOT IN (
        SELECT customer_hk FROM {{ this }}
    )
    {% endif %}
)

SELECT
    customer_hk,
    customer_bk,
    load_dts,
    record_source
FROM new_records
WHERE row_num = 1

Let's unpack the design decisions here:

Why ROW_NUMBER() with ORDER BY load_dts ASC? In a given load batch, the same business key might appear multiple times (e.g., a customer appears in both CRM and Shopify in the same load run). We want to insert only one Hub row per unique hash key, specifically the earliest-seen one. The Hub captures "first seen" — it doesn't track changes.

Why NOT IN (SELECT customer_hk FROM {{ this }})? This is the incremental filter. In the first run, the table doesn't exist so is_incremental() returns false and all records are loaded. In subsequent runs, we skip any hash key that already exists in the Hub. Hubs are append-only, insert-once — if a hash key is already there, we have nothing to do.

Why cluster_by=['customer_hk']? In Snowflake, the Hub will be joined constantly from Links and Satellites using the hash key. Clustering on customer_hk means Snowflake can prune micro-partitions efficiently when looking up specific hash keys. For very large Hubs, this can be the difference between a 30-second join and a sub-second one.

Note: In Snowflake, NOT IN (SELECT ...) can be expensive if the Hub table is very large. An alternative pattern is to use a LEFT JOIN and filter on NULLs, or to use a MERGE statement. For Hub tables exceeding ~100M rows, benchmark the incremental filter strategy and consider using MERGE via a custom dbt materialization or a post-hook. The datavault4dbt package handles this automatically.

---

Building Link Models

Links are structurally similar to Hubs but capture relationships. Let's model the relationship between customers and orders.

-- models/raw_vault/links/lnk_customer_order.sql
{{
    config(
        materialized='incremental',
        unique_key='customer_order_hk',
        on_schema_change='append_new_columns',
        cluster_by=['customer_order_hk', 'customer_hk'],
        tags=['raw_vault', 'link']
    )
}}

WITH source_orders AS (
    SELECT
        customer_order_hk,  -- Hash of customer_hk || order_hk
        customer_hk,
        order_hk,
        load_dts,
        record_source
    FROM {{ ref('stg_shopify__orders') }}
),

new_records AS (
    SELECT
        customer_order_hk,
        customer_hk,
        order_hk,
        load_dts,
        record_source,
        ROW_NUMBER() OVER (
            PARTITION BY customer_order_hk
            ORDER BY load_dts ASC
        ) AS row_num
    FROM source_orders

    {% if is_incremental() %}
    WHERE customer_order_hk NOT IN (
        SELECT customer_order_hk FROM {{ this }}
    )
    {% endif %}
)

SELECT
    customer_order_hk,
    customer_hk,
    order_hk,
    load_dts,
    record_source
FROM new_records
WHERE row_num = 1

The Link's hash key is a hash of its constituent Hub hash keys — in this case, customer_hk || order_hk. This makes the Link hash key deterministic and ensures idempotency. You can load the same order relationship ten times and end up with exactly one row in the Link table.

Notice that the Link does not contain any descriptive attributes. It only contains keys and metadata. If a customer changes their shipping address on an order, that's not a structural change (the customer-order relationship still exists) — it's a contextual change, and it belongs in a Satellite attached to the Link.

Effectivity Satellites

One advanced concept worth introducing here: in Data Vault 2.0, you can attach a special type of Satellite to a Link called an Effectivity Satellite (SAL). This captures when a relationship became active and inactive. For example, if a customer is assigned to an account manager and that assignment later changes, the Effectivity Satellite records the load_end_dts of the old relationship.

-- models/raw_vault/satellites/sal_customer_order_effectivity.sql
{{
    config(
        materialized='incremental',
        unique_key=['customer_order_hk', 'load_dts'],
        cluster_by=['customer_order_hk'],
        tags=['raw_vault', 'satellite']
    )
}}

WITH source AS (
    SELECT
        customer_order_hk,
        load_dts,
        load_end_dts,
        record_source
    FROM {{ ref('stg_shopify__orders') }}
),

most_recent AS (
    SELECT customer_order_hk, MAX(load_dts) AS max_load_dts
    FROM {{ this }}
    {% if is_incremental() %}
    GROUP BY 1
    {% endif %}
),

new_records AS (
    SELECT s.*
    FROM source s

    {% if is_incremental() %}
    LEFT JOIN most_recent m ON s.customer_order_hk = m.customer_order_hk
    WHERE s.load_dts > COALESCE(m.max_load_dts, '1900-01-01')
    {% endif %}
)

SELECT * FROM new_records

---

Building Satellite Models

Satellites are the most complex vault entity because they require change detection. Every load, you need to determine: did anything actually change for this Hub key? If not, skip the insert. If yes, insert a new row.

-- models/raw_vault/satellites/sat_customer_crm.sql
{{
    config(
        materialized='incremental',
        unique_key=['customer_hk', 'load_dts'],
        on_schema_change='append_new_columns',
        cluster_by=['customer_hk', 'load_dts'],
        tags=['raw_vault', 'satellite']
    )
}}

WITH source AS (
    SELECT
        customer_hk,
        first_name,
        last_name,
        phone_number,
        loyalty_tier,
        is_active,
        load_dts,
        record_source,
        hashdiff
    FROM {{ ref('stg_crm__customers') }}
),

-- Get the most recent hashdiff for each customer_hk already in the Satellite
most_recent_satellite AS (
    SELECT
        customer_hk,
        hashdiff AS latest_hashdiff,
        load_dts AS latest_load_dts
    FROM (
        SELECT
            customer_hk,
            hashdiff,
            load_dts,
            ROW_NUMBER() OVER (
                PARTITION BY customer_hk
                ORDER BY load_dts DESC
            ) AS rn
        FROM {{ this }}
        {% if is_incremental() %}
        -- No WHERE needed here; we need latest row per key
        {% endif %}
    ) ranked
    WHERE rn = 1
),

-- Only insert rows where the hashdiff has changed
new_or_changed AS (
    SELECT
        s.customer_hk,
        s.first_name,
        s.last_name,
        s.phone_number,
        s.loyalty_tier,
        s.is_active,
        s.load_dts,
        s.record_source,
        s.hashdiff
    FROM source s

    {% if is_incremental() %}
    LEFT JOIN most_recent_satellite m ON s.customer_hk = m.customer_hk
    WHERE
        -- New customer (never seen before)
        m.customer_hk IS NULL
        OR
        -- Existing customer with changed attributes
        s.hashdiff != m.latest_hashdiff
    {% endif %}
)

SELECT * FROM new_or_changed

Tip: The most_recent_satellite CTE is the performance hotspot in Satellite loads at scale. That inner ROW_NUMBER() scan runs against your entire Satellite table every incremental run. For Satellites with billions of rows, consider materializing the "latest record per key" as a separate view or table (this is what Point-In-Time tables do). Alternatively, Snowflake's Search Optimization Service can dramatically accelerate the point lookup against customer_hk.

Handling Multiple Sources in the Same Satellite

If both CRM and Shopify provide customer attributes, you have a choice: one Satellite per source, or a merged Satellite. Data Vault 2.0 strongly recommends one Satellite per source system. This preserves source auditability — you can always ask "what did Shopify say about this customer on this date?" without it being contaminated by CRM data. You then reconcile at the mart layer.

This means you might have sat_customer_crm and sat_customer_shopify. Each follows the same pattern above, just with different source CTEs and potentially different columns. This is intentional and correct.

---

Automating Vault Builds with `datavault4dbt`

Writing the above patterns by hand for every entity becomes tedious fast. If you have 15 source systems with 50 business concepts each, you're looking at hundreds of Hub, Link, and Satellite models that are structurally identical with minor variations. This is exactly the problem the datavault4dbt open-source package solves.

Add it to your packages.yml:

packages:
  - package: ScalefreeCom/datavault4dbt
    version: [">=1.5.0", "<2.0.0"]

Run dbt deps to install. Now your Hub model collapses to this:

-- models/raw_vault/hubs/hub_customer.sql
{{
    config(
        materialized='incremental',
        unique_key='customer_hk'
    )
}}

{{
    datavault4dbt.hub(
        hashkey='customer_hk',
        business_key='customer_bk',
        src_ldts='load_dts',
        src_rsrc='record_source',
        source_models=[
            {'source_model': 'stg_crm__customers'},
            {'source_model': 'stg_shopify__customers'}
        ]
    )
}}

And your Satellite model becomes:

-- models/raw_vault/satellites/sat_customer_crm.sql
{{
    config(
        materialized='incremental',
        unique_key=['customer_hk', 'load_dts']
    )
}}

{{
    datavault4dbt.sat(
        hashkey='customer_hk',
        hashdiff='hashdiff',
        src_ldts='load_dts',
        src_rsrc='record_source',
        source_model='stg_crm__customers',
        tracked_columns=['first_name', 'last_name', 'phone_number', 'loyalty_tier', 'is_active']
    )
}}

The package handles the incremental logic, the change detection join, the deduplication window function, and the Snowflake-specific SQL dialect. It also supports more advanced patterns like multi-active Satellites and record tracking Satellites out of the box. For teams building real vault implementations, using datavault4dbt is the right call — it's well-maintained, battle-tested, and lets you focus on modeling decisions rather than boilerplate SQL.

If you're thinking about structuring a reusable macro library beyond just the vault patterns, Building and Managing dbt Packages: Reusable Macros, Models, and Tests Across Projects covers the package authoring process in depth.

---

Point-In-Time Tables and Bridge Tables

The raw vault is insert-only and normalized. Querying it directly for analytical use is painful: to get a customer's current attributes, you'd need to join Hub → Satellite with a window function to get the latest row. To get all attributes from multiple Satellites, you'd need multiple such joins. For complex queries spanning many entities, this becomes a performance nightmare.

This is where Point-In-Time (PIT) tables and Bridge tables come in. These live in the Business Vault layer and are fully derived from the raw vault. They're the "acceleration" layer that makes the vault queryable.

Point-In-Time Tables

A PIT table for customers pre-computes, for each customer and each load timestamp, which Satellite row was "current" at that moment. It's a key lookup table — no descriptive attributes, just hash keys and load timestamps.

-- models/business_vault/pit_customer.sql
{{
    config(
        materialized='table',  -- Rebuild daily or on schedule
        cluster_by=['customer_hk', 'snapshot_dts'],
        tags=['business_vault', 'pit']
    )
}}

WITH date_spine AS (
    {{ dbt_utils.date_spine(
        datepart='day',
        start_date="cast('2020-01-01' as date)",
        end_date="cast(current_date() as date)"
    ) }}
),

hub AS (
    SELECT customer_hk FROM {{ ref('hub_customer') }}
),

-- Cross join hub with date spine to get every customer-date combination
customer_dates AS (
    SELECT
        h.customer_hk,
        d.date_day AS snapshot_dts
    FROM hub h
    CROSS JOIN date_spine d
),

-- For each customer-date, find the latest Satellite rows as of that date
sat_crm_pit AS (
    SELECT
        cd.customer_hk,
        cd.snapshot_dts,
        MAX(s.load_dts) AS sat_crm_load_dts
    FROM customer_dates cd
    LEFT JOIN {{ ref('sat_customer_crm') }} s
        ON cd.customer_hk = s.customer_hk
        AND s.load_dts <= cd.snapshot_dts
    GROUP BY 1, 2
),

sat_shopify_pit AS (
    SELECT
        cd.customer_hk,
        cd.snapshot_dts,
        MAX(s.load_dts) AS sat_shopify_load_dts
    FROM customer_dates cd
    LEFT JOIN {{ ref('sat_customer_shopify') }} s
        ON cd.customer_hk = s.customer_hk
        AND s.load_dts <= cd.snapshot_dts
    GROUP BY 1, 2
)

SELECT
    cd.customer_hk,
    cd.snapshot_dts,
    COALESCE(sc.sat_crm_load_dts, '1900-01-01') AS sat_crm_load_dts,
    COALESCE(ss.sat_shopify_load_dts, '1900-01-01') AS sat_shopify_load_dts
FROM customer_dates cd
LEFT JOIN sat_crm_pit sc USING (customer_hk, snapshot_dts)
LEFT JOIN sat_shopify_pit ss USING (customer_hk, snapshot_dts)

Warning: PIT tables built with a daily date spine and a CROSS JOIN against a large Hub table can be extremely expensive in Snowflake. 1 million customers × 1,000 days = 1 billion rows in the PIT. Use Snowflake Dynamic Tables or consider incremental PIT builds that only compute new snapshots. The datavault4dbt package includes an incremental PIT macro that handles this correctly.

Connecting to the Mart Layer

With PITs in place, your mart-layer dimension models become clean and efficient:

-- models/marts/dim_customer.sql
{{
    config(
        materialized='table',
        cluster_by=['customer_hk']
    )
}}

WITH pit AS (
    SELECT *
    FROM {{ ref('pit_customer') }}
    WHERE snapshot_dts = CURRENT_DATE()  -- Current snapshot only for a Type 1 dim
),

sat_crm AS (
    SELECT * FROM {{ ref('sat_customer_crm') }}
),

sat_shopify AS (
    SELECT * FROM {{ ref('sat_customer_shopify') }}
),

hub AS (
    SELECT * FROM {{ ref('hub_customer') }}
)

SELECT
    h.customer_hk,
    h.customer_bk,
    h.record_source AS first_seen_source,

    -- CRM attributes (authoritative for identity)
    crm.first_name,
    crm.last_name,
    crm.phone_number,
    crm.loyalty_tier,

    -- Shopify attributes
    shp.email_address,
    shp.total_orders,
    shp.lifetime_value,

    -- Metadata
    pit.snapshot_dts AS valid_as_of

FROM pit
JOIN hub h USING (customer_hk)
-- Join to Satellite using the PIT-provided load timestamp
LEFT JOIN sat_crm crm
    ON pit.customer_hk = crm.customer_hk
    AND pit.sat_crm_load_dts = crm.load_dts
LEFT JOIN sat_shopify shp
    ON pit.customer_hk = shp.customer_hk
    AND pit.sat_shopify_load_dts = shp.load_dts

This pattern — PIT-mediated joins to Satellites — is what makes Data Vault queries both correct and fast. Without the PIT, every mart query would need a subquery or window function to find the "latest" Satellite row, which is both expensive and error-prone.

---

Project Structure and dbt Configuration

Organizing a Data Vault dbt project correctly from the start saves you significant pain later. Here's the structure we recommend:

models/
├── staging/
│   ├── crm/
│   │   ├── stg_crm__customers.sql
│   │   └── stg_crm__accounts.sql
│   └── shopify/
│       ├── stg_shopify__customers.sql
│       └── stg_shopify__orders.sql
├── raw_vault/
│   ├── hubs/
│   │   ├── hub_customer.sql
│   │   └── hub_order.sql
│   ├── links/
│   │   └── lnk_customer_order.sql
│   └── satellites/
│       ├── sat_customer_crm.sql
│       ├── sat_customer_shopify.sql
│       └── sat_order_shopify.sql
├── business_vault/
│   ├── pit_customer.sql
│   └── brd_customer_orders.sql
└── marts/
    ├── dim_customer.sql
    └── fct_orders.sql

In your dbt_project.yml, configure defaults at the folder level:

models:
  my_project:
    staging:
      +materialized: view
      +schema: staging
      +tags: ['staging']

    raw_vault:
      +materialized: incremental
      +schema: raw_vault
      +tags: ['raw_vault']
      hubs:
        +unique_key: '{{ model.config.get("unique_key") }}'
      links:
        +unique_key: '{{ model.config.get("unique_key") }}'
      satellites:
        +unique_key: ['customer_hk', 'load_dts']

    business_vault:
      +materialized: table
      +schema: business_vault
      +tags: ['business_vault']

    marts:
      +materialized: table
      +schema: marts
      +tags: ['marts']

For testing, every Hub needs a not_null and unique test on its hash key column. Every Satellite needs not_null on customer_hk and load_dts. Add relationship tests to ensure Link hash keys reference valid Hub hash keys.

# models/raw_vault/hubs/schema.yml
version: 2

models:
  - name: hub_customer
    description: "Hub for customer business keys across all source systems"
    columns:
      - name: customer_hk
        description: "SHA-256 hash of the customer business key (email address)"
        tests:
          - not_null
          - unique
      - name: customer_bk
        tests:
          - not_null
      - name: load_dts
        tests:
          - not_null
      - name: record_source
        tests:
          - not_null
          - accepted_values:
              values: ['CRM_SALESFORCE', 'SHOPIFY_ECOMM']

---

Scheduling and Orchestration Considerations

Data Vault loads must run in dependency order: staging → hubs → links → satellites → business vault → marts. dbt's DAG handles the model-level dependencies, but the operational orchestration — scheduling, retry logic, alerting — needs a separate tool.

For production vault deployments, dbt runs are typically orchestrated with Airflow. The vault load pattern maps naturally to task groups: one task group per source system for staging, then a parallel fan-out for hub and link loads (which have no inter-dependencies across source systems), then satellite loads, then business vault and marts. This parallelism is one of the architectural advantages of Data Vault: because Hubs don't depend on each other, you can load all source systems simultaneously.

For a complete treatment of scheduling dbt in Airflow with proper retry semantics and alerting, see Orchestrating dbt Runs with Airflow: Scheduling, Dependencies, and Error Handling in Production. And if you're thinking about CI/CD for your vault project — which you should be — Automating dbt Environment Promotion with CI/CD Pipelines, Slim CI, and State-Aware Deployments covers the state-aware deployment patterns that work especially well with incremental vault models.

---

Performance Optimization in Snowflake

Data Vault's insert-only pattern is a good fit for Snowflake's architecture, but there are several optimization patterns specific to vault workloads.

Clustering Strategy

Hubs: cluster on hub_hash_key. The most common query pattern against a Hub is "give me the record for this specific hash key" from a downstream Link or Satellite join.

Satellites: cluster on [hub_hash_key, load_dts]. Satellite queries almost always filter on the hash key, then range-scan on load_dts to find the relevant historical window.

Links: cluster on [link_hash_key] and optionally on the constituent Hub hash keys if you frequently query "all orders for a given customer."

-- Apply clustering via Snowflake DDL (or via dbt cluster_by config)
ALTER TABLE ANALYTICS.RAW_VAULT.SAT_CUSTOMER_CRM
CLUSTER BY (CUSTOMER_HK, LOAD_DTS);

-- Monitor clustering health
SELECT SYSTEM$CLUSTERING_INFORMATION(
    'ANALYTICS.RAW_VAULT.SAT_CUSTOMER_CRM',
    '(CUSTOMER_HK, LOAD_DTS)'
);

Search Optimization for Point Lookups

For large Satellite tables where you frequently look up a specific customer_hk, Snowflake's Search Optimization Service can be a game-changer. It builds a persistent search access path that eliminates the need to scan entire tables for equality predicates.

ALTER TABLE ANALYTICS.RAW_VAULT.SAT_CUSTOMER_CRM
ADD SEARCH OPTIMIZATION ON EQUALITY(CUSTOMER_HK);

Note: Search Optimization Service adds to your Snowflake bill — it's typically priced at a multiple of the storage cost of the indexed table. Benchmark whether your query patterns actually benefit before enabling it everywhere. It's most impactful on very large Satellites (>500M rows) with frequent point-lookup patterns, which is exactly what PIT-mediated mart queries produce.

Warehouse Sizing for Vault Loads

Vault loads are not uniform. Hub loads are relatively fast — they're small inserts of distinct keys. Satellite loads are the bottleneck: the change detection join that scans the existing Satellite to find the latest hashdiff is expensive at scale. For Satellite tables exceeding 100 million rows, a MEDIUM warehouse may not be sufficient for the incremental scan. Profile your Satellite loads at MEDIUM and LARGE warehouse sizes — the Snowflake Credit spend difference is often less than the time saved when factoring in load window constraints.

If you're interested in managing the cost implications of these decisions systematically, Cost Management in Cloud Data Platforms provides frameworks for setting resource monitors and budgeting for warehouse-intensive workloads.

---

Hands-On Exercise

Build a minimal but complete Data Vault implementation for a retail scenario with two source systems: a POS system and an e-commerce platform. Both systems have customers who may also exist in the other system.

Step 1: Set up Snowflake schemas Create ANALYTICS.STAGING, ANALYTICS.RAW_VAULT, and ANALYTICS.MARTS in a Snowflake trial account.

Step 2: Create seed data In your dbt project, create seed files that simulate raw source data:

-- seeds/raw_pos_customers.csv
customer_id,email,first_name,last_name,store_id,member_since
POS001,alice@example.com,Alice,Johnson,STORE_NYC,2021-03-15
POS002,bob@example.com,Bob,Smith,STORE_LA,2020-11-02
POS003,carol@example.com,Carol,Williams,STORE_NYC,2022-07-19

-- seeds/raw_ecomm_customers.csv
user_uuid,email_address,display_name,signup_date,email_verified
UUID-AAA,alice@example.com,Alice J.,2021-03-10,true
UUID-BBB,dave@example.com,Dave K.,2022-01-05,true
UUID-CCC,carol@example.com,carolw,2022-07-20,false

Step 3: Build staging models Create stg_pos__customers.sql and stg_ecomm__customers.sql. Each should add:

• customer_hk using SHA-256 of the email address (normalized)
• hashdiff covering all descriptive columns
• load_dts = CURRENT_TIMESTAMP()
• record_source as a literal string constant

Step 4: Build the Hub Create hub_customer.sql in models/raw_vault/hubs/ as an incremental model that unions both staging sources and deduplicates on customer_hk.

Step 5: Build the Satellites Create sat_customer_pos.sql and sat_customer_ecomm.sql. Run a dbt run --select sat_customer_pos twice — verify that the second run inserts 0 rows (no data changed).

Then update one row in the POS seed to change first_name for POS001 from "Alice" to "Alice M." and re-run. Verify that a new row was inserted with a new load_dts and the old row remains untouched.

Step 6: Build a mart dimension Create dim_customer.sql that joins Hub + both Satellites to produce a unified current view of customers, with a data_source_count column that indicates how many source systems know about this customer.

Expected result: Your dim_customer should have 4 rows (Alice, Bob, Carol, Dave), with Alice and Carol showing data_source_count = 2 and Bob and Dave showing data_source_count = 1. Alice should appear with her updated name "Alice M." from the POS system.

---

Common Mistakes & Troubleshooting

Mistake 1: Inconsistent Hashing Standards Across Staging Models

Symptom: Hub has duplicate-looking rows, or entities that exist in both systems are not being matched.

Cause: One staging model applies UPPER(TRIM(...)) before hashing; another doesn't. Or one uses a different separator for composite keys.

Fix: Create a single generate_hash_key macro and enforce its use via dbt tests. Add a test that checks Hub cardinality against a known-good count from a reference source.

Mistake 2: Using `updated_at` Instead of Load Timestamp in Satellites

Symptom: Late-arriving data doesn't get loaded because the updated_at is older than the latest Satellite row.

Cause: Using the source system's updated_at as load_dts instead of the actual load time. If a record is corrected retroactively in the source system, its updated_at might be days in the past.

Fix: Always use CURRENT_TIMESTAMP() at staging time as load_dts. Store the source system's timestamp as a descriptive attribute in the Satellite. You can use the source timestamp for change detection logic separately if needed.

Mistake 3: Full Refresh on Incremental Vault Models

Symptom: Hubs and Satellites suddenly have no data, then re-load from scratch causing downtime.

Cause: Someone ran dbt run --full-refresh targeting raw vault models.

Fix: Tag raw vault models and configure your CI/CD pipeline to explicitly block --full-refresh on those tags. Add a pre-hook that raises an error if --full-refresh is detected on Hub or Satellite models.

Mistake 4: Missing `hashdiff` Column in Staging

Symptom: Satellite loads are slow because change detection falls back to column-by-column comparison, or all rows are re-inserted on every load.

Cause: The hashdiff was not computed in staging, or it was computed on a subset of columns.

Fix: The hashdiff must cover all descriptive columns in the Satellite — no exceptions. If you add a new column to the Satellite, you must also add it to the hashdiff computation in staging. Maintain this relationship via a shared macro or datavault4dbt configuration that generates both.

Mistake 5: Building Hub-to-Hub Joins in the Raw Vault

Symptom: A "Hub" model has foreign key references to another Hub's hash key.

Cause: Treating the raw vault like a normalized relational schema. Developers instinctively add account_hk to hub_customer because "a customer belongs to an account."

Fix: Remove it. The customer-account relationship is a Link. Hubs are islands of business key identity. All relationships live in Links. This is a design principle, not a preference.

Key insight: The urge to denormalize at the Hub level usually comes from mart-layer query needs bleeding upward into vault design. Keep the concern separation clean: raw vault models structure, mart models serve. If a mart query is cumbersome, the solution is a better PIT or Bridge table, not a polluted Hub.

Mistake 6: Forgetting the Ghost Record in Satellites

Symptom: PIT table joins return NULL for all Satellite attributes for early time periods, even though the customer existed then.

Cause: There's no "initial" Satellite row for the customer — the Satellite only has rows when changes occurred.

Fix: For each Hub insertion, consider inserting a corresponding initial Satellite row with load_dts matching the Hub's load_dts. Alternatively, handle NULLs gracefully in PIT and mart queries using COALESCE.

---

Summary & Next Steps

You've built a complete Data Vault 2.0 implementation from scratch. Let's review what you've learned:

Core Data Vault constructs: Hubs capture the existence of business objects through their business keys. Links capture relationships between Hubs without descriptive attributes. Satellites capture the full history of descriptive attributes for Hubs and Links, using hashdiff-based change detection to avoid redundant inserts.

Hash key design: SHA-256 with consistent normalization (UPPER, TRIM, NULL handling) is non-negotiable. A shared dbt macro enforces this across all staging models.

Incremental patterns in dbt: Hubs and Links are insert-once (if the hash key exists, skip). Satellites are insert-on-change (compare hashdiff, insert only when changed). Both use dbt's incremental materialization with is_incremental() guard conditions.

The automation path: datavault4dbt eliminates boilerplate Hub, Link, and Satellite SQL, letting you express vault structure as configuration rather than hand-written SQL at scale.

Mart integration: Point-In-Time tables bridge the raw vault's normalized insert-only structure to the columnar, denormalized query patterns that analytical consumers need.

Snowflake optimization: Cluster Hubs on hash key, Satellites on [hash_key, load_dts]. Use Search Optimization for large Satellite point lookups. Size vault load warehouses separately from analytical query warehouses.

Where to Go From Here

The natural next step is bringing Change Data Capture into your ingestion layer so that your staging models reflect database-level changes from source systems in near-real-time, rather than relying on full extracts. Implementing Change Data Capture with Debezium and Airbyte: Streaming Relational Database Changes into Your Cloud Warehouse is the right companion to what you've built here — CDC is the ideal ingestion pattern for feeding a Data Vault because it delivers exactly the "what changed and when" semantics that the Satellite pattern was designed to absorb.

You should also think carefully about data quality at the vault boundary. Your Satellites are only as reliable as what comes in from staging. Implementing dbt Tests and Data Quality Checks in Production Pipelines covers the testing patterns you need at the staging-to-vault boundary, including custom generic tests for vault-specific constraints like "every Link hash key must reference valid Hub hash keys."

Finally, consider lineage. A mature vault implementation spans dozens of source systems, hundreds of models, and multiple teams. Tracking lineage from ingestion through vault through mart layers is how you understand impact when a source schema changes. Multi-Hop Data Lineage Tracking Across the Modern Data Stack shows how to instrument this with OpenLineage and Marquez.

Data Vault isn't a replacement for dimensional modeling — it's a complement to it. The vault absorbs complexity at the raw layer; the mart layer delivers the clean, queryable structure that business users and BI tools need. Getting both layers right, and building the automation that makes the whole thing maintainable at scale, is the craft of modern data engineering.