datavault-patterns

Data Vault 2.1 Pattern Library

When to Use

Trigger phrases:

• "Create hub", "Add hub"
• "Create satellite", "Add satellite"
• "Create link"
• "DC pattern", "Dependent child"
• "MA Sat", "Multi-Active"
• "Reference table"

Decision Logic

Stable Business Key?             → HUB
Descriptive attributes?          → SATELLITE (on Hub)
Relationship between 2+ entities? → LINK
Entity without own BK?           → DC SATELLITE (on Link)
Multiple simultaneous values?    → MA SATELLITE (src_cdk)
Stable lookup values?            → REFERENCE TABLE
Time range of a relationship?    → EFFECTIVITY SATELLITE (on Link)
Point-in-time query optimization? → PIT TABLE

Hub Template

{#
    Hub: hub_<entity>
    Source: <staging_model>
    Business Keys: <business_key>
    Version: <YYYY-MM-DD> V1.0 Initial
#}

{{ config(
    materialized='incremental',
    as_columnstore=false,
    post_hook=["{{ create_hash_index('hk_<entity>') }}"]
) }}

{%- set yaml_metadata -%}
source_model: "<staging_model>"
src_pk: "hk_<entity>"
src_nk: "<business_key>"
src_ldts: "dss_load_date"
src_source: "dss_record_source"
{%- endset -%}

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ automate_dv.hub(src_pk=metadata_dict["src_pk"],
                   src_nk=metadata_dict["src_nk"],
                   src_ldts=metadata_dict["src_ldts"],
                   src_source=metadata_dict["src_source"],
                   source_model=metadata_dict["source_model"]) }}

Mandatory Hub Fields:

Column	Type	Description
hk_<entity>	CHAR(64)	SHA-256 Hash of Business Key
<business_key>	varies	Natural Key
dss_load_date	DATETIME2(6)	First load timestamp
dss_record_source	VARCHAR(50)	Source system identifier

Satellite Template

{#
    Satellite: sat_<entity>
    Parent Hub: hub_<entity>
    Source: <staging_model>
    Payload: <payload_columns>
    Version: <YYYY-MM-DD> V1.0 Initial
#}

{{ config(
    materialized='incremental',
    as_columnstore=false,
    post_hook=[
        "{{ create_hash_index('hk_<entity>') }}",
        "{{ update_satellite_current_flag('hk_<entity>', 'dss_load_date') }}"
    ]
) }}

{%- set yaml_metadata -%}
source_model: "<staging_model>"
src_pk: "hk_<entity>"
src_hashdiff:
  source_column: "hd_<entity>"
  alias: "hashdiff"
src_payload:
  - COLUMN_1
  - COLUMN_2
  - COLUMN_N
src_eff: "dss_start_date"
src_ldts: "dss_load_date"
src_source: "dss_record_source"
{%- endset -%}

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ automate_dv.sat(src_pk=metadata_dict["src_pk"],
                   src_hashdiff=metadata_dict["src_hashdiff"],
                   src_payload=metadata_dict["src_payload"],
                   src_eff=metadata_dict["src_eff"],
                   src_ldts=metadata_dict["src_ldts"],
                   src_source=metadata_dict["src_source"],
                   source_model=metadata_dict["source_model"]) }}

Important: src_hashdiff must have alias: "hashdiff" (not the column name!).

Mandatory Satellite Fields:

Column	Type	Description
hk_<entity>	CHAR(64)	FK to Hub
hashdiff	CHAR(64)	SHA-256 Hash of payload (alias!)
dss_load_date	DATETIME2(6)	Load timestamp
dss_start_date	DATETIME2(6)	Validity start
dss_end_date	DATETIME2(6)	Validity end (NULL = current)
dss_is_current	CHAR(1)	'Y' / 'N'
dss_record_source	VARCHAR(50)	Source system
payload...	varies	Attribute columns

Link Template

{#
    Link: link_<e1>_<e2>
    Source: <staging_model>
    Foreign Keys: hk_<entity_1>, hk_<entity_2>
    Version: <YYYY-MM-DD> V1.0 Initial
#}

{{ config(
    materialized='incremental',
    as_columnstore=false,
    post_hook=["{{ create_hash_index('hk_link_<e1>_<e2>') }}"]
) }}

{%- set yaml_metadata -%}
source_model: "<staging_model>"
src_pk: "hk_link_<e1>_<e2>"
src_fk:
  - "hk_<entity_1>"
  - "hk_<entity_2>"
src_ldts: "dss_load_date"
src_source: "dss_record_source"
{%- endset -%}

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ automate_dv.link(src_pk=metadata_dict["src_pk"],
                    src_fk=metadata_dict["src_fk"],
                    src_ldts=metadata_dict["src_ldts"],
                    src_source=metadata_dict["src_source"],
                    source_model=metadata_dict["source_model"]) }}

Link Hash Key Calculation (in Staging):

CONVERT(CHAR(64), HASHBYTES('SHA2_256',
    ISNULL(CAST(FK_1 AS NVARCHAR(MAX)), '') + '^^' +
    ISNULL(CAST(FK_2 AS NVARCHAR(MAX)), '')
), 2) AS hk_link_<e1>_<e2>

DC Satellite (Dependent Child)

• No own Hub → Satellite on Link
• Link has only 1 FK (the parent Hub)
• Hash Key = HASH(FK ^^ DCK1 ^^ DCK2)

MA Satellite (Multi-Active)

• Additional src_cdk (child-defined key) in the automate_dv config
• Allows multiple simultaneously valid values per Hash Key

dbt_project.yml Configuration

Ensure the source system block exists under raw_vault:

models:
  <project_name>:
    raw_vault:
      <source_system>:
        +schema: vault_<source_system>
        +materialized: incremental
        +incremental_strategy: append
        +as_columnstore: false

Schema YAML Template

version: 2

models:
  - name: hub_<entity>
    description: "Hub for <entity>"
    columns:
      - name: hk_<entity>
        data_type: "CHAR(64)"
        description: "Hash Key"
        tests: [not_null, unique]
      - name: <business_key>
        data_type: "..."
        description: "Business Key"
        tests: [not_null]
      - name: dss_load_date
        data_type: "DATETIME2(6)"
      - name: dss_record_source
        data_type: "VARCHAR(50)"