Concept 01
Metadata-Driven Development
🧠 ELI5 — Explain Like I'm Five
In a large enterprise Data Vault, you might have 200+ Hub/Link/Satellite tables. Writing DDL and ETL for each one manually is madness. Metadata-driven development means storing the description of your vault structures in a configuration table, then running a code generator that writes the actual SQL. Change the config, regenerate the code.
The Metadata Registry Pattern
The foundation of metadata-driven development is a vault object registry — a set of configuration tables that describe every Hub, Link, and Satellite you need, along with their columns, data types, and relationships. The code generator reads these tables and produces DDL and ETL automatically.
Two core tables are all you need to start: one for the vault objects (which tables exist) and one for the columns (what each table contains). Everything else — naming conventions, hash algorithms, default constraints — lives in the templates, not the metadata.
-- Metadata: vault object registry
CREATE TABLE META_VAULT_OBJECT (
OBJECT_ID INT NOT NULL IDENTITY,
OBJECT_TYPE VARCHAR(10) NOT NULL, -- 'HUB', 'LINK', 'SAT'
OBJECT_NAME VARCHAR(200) NOT NULL, -- e.g., 'HUB_CUSTOMER'
SCHEMA_NAME VARCHAR(50) NOT NULL DEFAULT 'RAW_VAULT',
BUSINESS_KEY VARCHAR(200), -- for Hubs: the BK column name
PARENT_OBJECT VARCHAR(200), -- for Sats: the Hub/Link they belong to
SOURCE_SYSTEM VARCHAR(100),
IS_ACTIVE CHAR(1) DEFAULT 'Y',
CREATED_DATE DATE DEFAULT CURRENT_DATE(),
CONSTRAINT pk_mvo PRIMARY KEY (OBJECT_ID)
);
-- Metadata: column registry
CREATE TABLE META_VAULT_COLUMN (
COLUMN_ID INT NOT NULL IDENTITY,
OBJECT_ID INT NOT NULL, -- FK to META_VAULT_OBJECT
COLUMN_NAME VARCHAR(200) NOT NULL,
DATA_TYPE VARCHAR(100) NOT NULL,
IS_BUSINESS_KEY CHAR(1) DEFAULT 'N',
IS_HASH_DIFF CHAR(1) DEFAULT 'N',
IS_PAYLOAD CHAR(1) DEFAULT 'N',
HASH_DIFF_GROUP VARCHAR(50), -- which HDIFF this column belongs to
ORDINAL_POSITION INT,
CONSTRAINT pk_mvc PRIMARY KEY (COLUMN_ID)
);
From Metadata to Working Code
Once the registry is populated, a code generator iterates over the rows and emits DDL and ETL. The generator can be written in Python, Jinja2, or any scripting language. The principle is always the same: one template per vault type, fed with metadata rows, producing SQL output.
# Pseudo-code: generate Hub DDL from metadata
def generate_hub_ddl(hub_name, business_key_col, bk_type='VARCHAR(50)'):
return f"""
CREATE TABLE {hub_name} (
{hub_name}_HK CHAR(32) NOT NULL,
{business_key_col} {bk_type} NOT NULL,
LOAD_DATE TIMESTAMP NOT NULL,
RECORD_SOURCE VARCHAR(100) NOT NULL,
CONSTRAINT pk_{hub_name.lower()} PRIMARY KEY ({hub_name}_HK)
);
"""
# Generate all hubs from metadata table
hubs = query("SELECT OBJECT_NAME, BUSINESS_KEY FROM META_VAULT_OBJECT WHERE OBJECT_TYPE='HUB'")
for hub in hubs:
print(generate_hub_ddl(hub.OBJECT_NAME, hub.BUSINESS_KEY))
💡
Investment payback: A well-designed metadata layer pays back its investment in 2–3 sprints. After that, adding a new source system means adding rows to a config table and pressing ‘generate’ — not writing 50 SQL files.
Concept 02
dbt with Data Vault
🧠 ELI5 — Explain Like I'm Five
dbt (data build tool) is a transformation framework that turns SQL SELECT statements into materialized tables. It's become the industry-standard tool for building Data Vault structures on cloud platforms. The key: dbt's macro system lets you write a Hub load template once and reuse it for every hub with a single line of configuration.
How dbt Fits into a Data Vault Architecture
In a dbt-based Data Vault, each vault object becomes its own model file. The dependency chain is declared via ref() calls, and dbt ensures models always run in the correct order: staging first, then hubs and links in parallel, then satellites. No manual ETL orchestration required for the core load pattern.
The critical feature is incremental materialization. Instead of rebuilding a table from scratch every run, dbt appends only the new rows — which is exactly the insert-only pattern Data Vault requires. Combine this with a NOT IN or LEFT JOIN deduplication check and you have a complete vault loader in pure SQL.
-- models/staging/stg_crm_customer.sql
SELECT
customer_id AS customer_bk,
{{ dbt_utils.generate_surrogate_key(['customer_id']) }} AS hub_customer_hk,
{{ dbt_utils.generate_surrogate_key([
'first_name', 'last_name', 'email', 'date_of_birth'
]) }} AS hash_diff_demographics,
first_name, last_name, email, date_of_birth,
country_code, phone_number,
CURRENT_TIMESTAMP() AS load_date,
'CRM.SALESFORCE.CONTACT' AS record_source
FROM {{ source('crm', 'contacts') }}
WHERE customer_id IS NOT NULL
-- models/raw_vault/hub_customer.sql
{{ config(materialized='incremental', unique_key='hub_customer_hk') }}
SELECT
hub_customer_hk,
customer_bk,
load_date,
record_source
FROM {{ ref('stg_crm_customer') }}
{% if is_incremental() %}
WHERE hub_customer_hk NOT IN (SELECT hub_customer_hk FROM {{ this }})
{% endif %}
-- models/raw_vault/sat_customer_demographics.sql
{{ config(materialized='incremental', unique_key=['hub_customer_hk', 'load_date']) }}
WITH source AS (
SELECT * FROM {{ ref('stg_crm_customer') }}
),
current_sat AS (
SELECT * FROM {{ this }}
WHERE load_end_date IS NULL
)
SELECT
s.hub_customer_hk,
s.load_date,
NULL AS load_end_date,
s.hash_diff_demographics AS hash_diff,
s.record_source,
s.first_name, s.last_name, s.email, s.date_of_birth
FROM source s
LEFT JOIN current_sat c
ON c.hub_customer_hk = s.hub_customer_hk
WHERE c.hub_customer_hk IS NULL
OR c.hash_diff != s.hash_diff_demographics
The datavault4dbt Package
The datavault4dbt community package (github.com/ScalefreeCOM/datavault4dbt) provides ready-made macros for Hub, Link, and Satellite loading. Instead of writing the SQL above manually for every vault object, you declare each object in a YAML config file and call a single macro: datavault4dbt.hub(), datavault4dbt.link(), or datavault4dbt.sat(). Each macro handles hash key generation, incremental change detection, and insert-only logic automatically.
dbt vs raw SQL for DV: For traditional Kimball, dbt models are one-to-one with star schema objects. For Data Vault, each model maps to a vault object, and the ref() system enforces the staging → hub/link → satellite dependency order automatically. Hash key computation via dbt_utils.generate_surrogate_key ensures consistent input normalization across all sources — something manual MD5 calls frequently get wrong.
Concept 04
Auto-Generating DDL & ETL
🧠 ELI5 — Explain Like I'm Five
Whether you use dbt macros, Python scripts, or a commercial tool, the principle is the same: define the structure once in a template, feed it metadata, get working SQL out. This section shows what a complete self-built automation looks like using Jinja2 templates — the same engine that powers dbt itself.
Jinja2 Template-Based Code Generation
Jinja2 is a Python templating language that embeds variables, loops, and conditionals inside text files (including SQL). dbt uses Jinja2 internally. You can use it directly to build your own code generator without adopting the full dbt ecosystem — useful for platforms dbt doesn't support, or for generating non-SQL artefacts like documentation and test scripts.
A Jinja2 Hub DDL template is essentially a Hub CREATE TABLE statement with variables like {{ entity_name }} substituted at generation time. The same file generates every hub in your vault; only the variable values change per object.
-- hub_template.sql.j2
-- Generated: {{ generation_timestamp }}
-- Source: {{ source_system }}
CREATE TABLE IF NOT EXISTS {{ schema }}.HUB_{{ entity_name | upper }} (
HUB_{{ entity_name | upper }}_HK CHAR(32) NOT NULL
COMMENT 'MD5 hash of {{ bk_column }}',
{{ bk_column | upper }} {{ bk_type }} NOT NULL
COMMENT 'Business key from {{ source_system }}',
LOAD_DATE TIMESTAMP_NTZ NOT NULL
COMMENT 'Batch load timestamp',
RECORD_SOURCE VARCHAR(200) NOT NULL
COMMENT 'Source: {{ source_system }}',
CONSTRAINT pk_hub_{{ entity_name | lower }}
PRIMARY KEY (HUB_{{ entity_name | upper }}_HK)
)
COMMENT = 'Hub table for {{ entity_name }} entity. Insert-only.';
-- Index for business key lookups
CREATE INDEX IF NOT EXISTS idx_hub_{{ entity_name | lower }}_bk
ON {{ schema }}.HUB_{{ entity_name | upper }} ({{ bk_column | upper }});
Satellite ETL Template
The Satellite load template is more complex because it must iterate over a variable-length payload column list. Jinja2 {% for %} loops handle this elegantly — the template generates the correct column list regardless of how many payload columns the satellite has, driven entirely by what is in the metadata registry.
-- sat_load_template.sql.j2
INSERT INTO {{ schema }}.{{ sat_name }} (
{{ parent_hk_col }},
LOAD_DATE, LOAD_END_DATE, HASH_DIFF, RECORD_SOURCE,
{% for col in payload_columns %}{{ col.name }}{% if not loop.last %}, {% endif %}{% endfor %}
)
SELECT
stg.{{ parent_hk_col }},
stg.LOAD_DATE, NULL, stg.HASH_DIFF_{{ context }}, stg.RECORD_SOURCE,
{% for col in payload_columns %}stg.{{ col.name }}{% if not loop.last %}, {% endif %}{% endfor %}
FROM {{ staging_table }} stg
LEFT JOIN {{ schema }}.{{ sat_name }} sat
ON sat.{{ parent_hk_col }} = stg.{{ parent_hk_col }}
AND sat.LOAD_END_DATE IS NULL
WHERE sat.{{ parent_hk_col }} IS NULL
OR sat.HASH_DIFF != stg.HASH_DIFF_{{ context }};
✓ Code Generation Best Practices
- Version-control templates alongside metadata config
- Generate into a review folder before running in prod
- Include generation timestamp and source in file headers
- Write one generator unit test per vault type
- Validate generated DDL against a golden output in CI
✗ Common Mistakes
- Generating directly into production without review
- Hand-editing generated files (overwritten next run)
- No generation metadata in file headers
- Building a "perfect generic" framework before basics work
- Forgetting to regenerate tests when adding columns
Concept 05
Data Contracts & Interview Q&A
🧠 ELI5 — Explain Like I'm Five
A data contract is a formal agreement between a source system team and the data vault team specifying exactly what data will be delivered, in what format, at what frequency, and what SLAs apply. Without contracts, source systems change schemas without warning and break vault loads.
Why Data Contracts Are Critical for Data Vault
The business key is the foundation of every Hub. The HashDiff drives change detection in every Satellite. Both are critically sensitive to source schema changes. A column rename, a type change, or a new column silently added to the payload breaks downstream loads without any visible error until the next batch run — sometimes hours later.
A data contract makes these implicit assumptions explicit and binds the source team to a change management process. It transforms schema drift from an operational crisis into a managed event with defined notice periods and approval gates. For automated, metadata-driven vaults especially, a formal contract protects the entire code-generation pipeline from unannounced upstream changes.
# data_contract_crm_customers.yaml
contract:
name: "CRM Customer Daily Extract"
version: "2.1.0"
source_system: "Salesforce CRM"
owner: "CRM Engineering Team"
consumer: "Data Vault Raw Vault"
delivery:
schedule: "Daily at 02:00 UTC"
format: "Parquet"
landing_zone: "s3://data-lake/raw/crm/customers/"
schema:
business_key:
column: customer_id
type: STRING
nullable: false
description: "Salesforce Account ID (18-char)"
payload_columns:
- {name: first_name, type: STRING, nullable: true}
- {name: last_name, type: STRING, nullable: true}
- {name: email, type: STRING, nullable: true}
- {name: country_code, type: STRING, nullable: true, max_length: 3}
sla:
availability: "99.5%"
max_delay_minutes: 30
null_bk_threshold: "0%" # no null business keys allowed
change_management:
notice_required_days: 14
breaking_change_approval: "Data Architecture Team"
📝
Key elements of a DV data contract: the business key columns with exact types and NOT NULL guarantees, payload columns with types and nullable rules, delivery schedule and format, SLA metrics (availability and maximum delay), and a formal change management process with 14+ day notice periods and approval gates for breaking changes.
Architect-Level Interview Questions
Metadata-driven development means storing the description of your vault structures (entity names, business keys, column lists, source systems) in configuration tables rather than hard-coding them in SQL files. A code generator reads the metadata and produces DDL and ETL code.
Benefits: (1) Consistency — every Hub/Link/Sat follows exactly the same pattern with no manual deviation; (2) Speed — adding a new source system means adding config rows, not writing 50 files; (3) Self-documentation — the metadata IS the data dictionary; (4) Change management — regenerate everything when a naming convention changes; (5) Testing — metadata can drive automated test generation alongside the DDL itself.
For traditional Kimball, dbt models map one-to-one with star schema objects (one model per dimension or fact). For Data Vault, dbt models map to vault objects — staging models, hub models, link models, satellite models — and the workflow differences are significant:
(1) Incremental materialization with NOT EXISTS / LEFT JOIN logic replaces full rebuilds, enforcing the DV insert-only pattern; (2) Macros via datavault4dbt or custom packages generate standard DV patterns from YAML config, avoiding repeated SQL; (3) Hash key computation via dbt_utils.generate_surrogate_key rather than raw MD5 calls ensures consistent input normalization across all source feeds; (4) Model dependencies via ref() enforce load order (staging → hub/link → satellite → PIT/bridge) automatically.
A data contract is a formal schema agreement between a source system team and the data vault team. It specifies: the business key column(s) and their types, payload columns with types and nullable rules, delivery schedule, format, SLA, and change management process.
Importance for DV: (1) The business key is the foundation of every Hub — if the source silently renames the BK column, every vault load breaks; (2) Adding columns to the source is a breaking change for HashDiff computation — the satellite's hash changes even if the actual data values are identical; (3) The change management process ensures the vault team has notice (typically 14+ days) to update staging transforms before breaking changes arrive. Data contracts make the implicit explicit and turn schema drift from a crisis into a managed, predictable event.
Start small, prove the pattern, then expand. Resist the temptation to build a perfect generic framework upfront — a working generator for your 3 most common patterns is worth more than a theoretically perfect system that takes 6 months to build.
Step 1: Build the metadata tables (vault object registry + column registry). Step 2: Write one generator per vault type (Hub, Link, Sat) using Jinja2 templates or Python string templates. Step 3: Generate DDL for 3–4 vault objects from config and validate they match hand-written versions exactly. Step 4: Generate ETL (staging + vault load SQL) and run it against real source data. Step 5: Add generated test SQL (NOT NULL checks, referential integrity, HashDiff coverage). Build for the patterns you actually use, then generalize from there. A working generator deployed is worth far more than a perfectly designed generator still in development.
✅ Module 10 Key Takeaways
- Metadata-driven development stores vault structure descriptions in config tables and code-generates DDL and ETL — adding a new source system means adding config rows and pressing ‘generate’, not writing 50 SQL files by hand.
- dbt's incremental materialization and macro system — especially the datavault4dbt package — make it the industry standard for cloud Data Vault. Staging models, hub/link/sat loads, and dependency ordering all flow from SQL and YAML config.
- Commercial tools like WhereScape RED and TimeXtender suit enterprise environments that prefer GUI-driven automation with commercial support; dbt + datavault4dbt suits engineering-led cloud teams that want open-source flexibility and version-controlled code.
- Jinja2 templates are the simplest path to building your own metadata-driven code generator — define one template per vault type, feed it metadata rows, and get working SQL out for every vault object automatically.
- Data contracts formalize the agreement between source systems and the vault team, specifying business keys, delivery schedules, SLAs, and change management — turning schema drift from an operational crisis into a managed, predictable event.