### Configure Model and Call AutomateDV Hub Macro

Source: https://github.com/datavault-uk/automate-dv/blob/master/README.md

This example shows the basic structure for configuring a dbt model, providing metadata, and then calling the `hub` macro from AutomateDV. Ensure all required metadata variables are defined before calling the macro.

```jinja
{{- config(...)                          -}}

{%- set src_pk = ...                     -%}
...

{{ automate_dv.hub(src_pk, src_nk, src_ldts,
                   src_source, source_model) }}
```

--------------------------------

### Link Table Creation with automate_dv.link

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Creates a Link table to record relationships between two or more Hub surrogate keys. Accepts multiple source stage models for multi-source loads and deduplicates on the link primary key per load-date, ensuring incremental safety.

```jinja
-- models/vault/link_order_customer.sql
{{- config(materialized='incremental',
           unique_key='ORDER_CUSTOMER_HK',
           tags=['link'])
-}}

{%- set src_pk           = 'ORDER_CUSTOMER_HK' -%}
{%- set src_fk           = ['ORDER_HK', 'CUSTOMER_HK'] -%}
{%- set src_extra_columns = none -%}
{%- set src_ldts         = 'LOAD_DATETIME' -%}
{%- set src_source       = 'RECORD_SOURCE' -%}
{%- set source_model     = 'stg_orders' -%}

{{ automate_dv.link(
    src_pk=src_pk,
    src_fk=src_fk,
    src_extra_columns=src_extra_columns,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: LINK_ORDER_CUSTOMER table with columns
-- (ORDER_CUSTOMER_HK, ORDER_HK, CUSTOMER_HK, LOAD_DATETIME, RECORD_SOURCE).

```

--------------------------------

### Hub Table Creation with automate_dv.hub

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Loads unique business key surrogate hashes into a Hub table. Accepts one or multiple source stage models for multi-source loads. Deduplicates on the primary key per load-date timestamp and only inserts rows that do not already exist in the target, ensuring incremental safety.

```jinja
-- models/vault/hub_order.sql
{{- config(materialized='incremental',
           unique_key='ORDER_HK',
           tags=['hub'])
-}}

{%- set src_pk         = 'ORDER_HK' -%}
{%- set src_nk         = 'ORDER_ID' -%}
{%- set src_extra_columns = none -%}
{%- set src_ldts       = 'LOAD_DATETIME' -%}
{%- set src_source     = 'RECORD_SOURCE' -%}
-- Accept feeds from two stage models simultaneously
{%- set source_model   = ['stg_orders_web', 'stg_orders_mobile'] -%}

{{ automate_dv.hub(
    src_pk=src_pk,
    src_nk=src_nk,
    src_extra_columns=src_extra_columns,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: HUB_ORDER table with columns (ORDER_HK, ORDER_ID, LOAD_DATETIME,
-- RECORD_SOURCE). Only new hash keys are inserted on each run.

```

--------------------------------

### Stage Data Preparation with automate_dv.stage

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Builds a multi-CTE staging model from raw source data. Use this macro to derive new columns, hash business keys, nullify columns on missing keys, and add rank columns. It's the first macro called in any AutomateDV pipeline.

```jinja
-- models/staging/stg_orders.sql
{{- config(materialized='view') -}}

{%- set source_model = {'raw': 'orders'} -%}

{%- set derived_columns = {
    'EFFECTIVE_FROM': 'LOAD_DATE',
    'RECORD_SOURCE':  "!RAW_ORDERS"
} -%}

{%- set hashed_columns = {
    'ORDER_HK':   'ORDER_ID',
    'CUSTOMER_HK': 'CUSTOMER_ID',
    'ORDER_HASHDIFF': {
        'is_hashdiff': true,
        'columns': ['ORDER_ID', 'CUSTOMER_ID', 'ORDER_TOTAL', 'STATUS']
    }
} -%}

{%- set null_columns = {
    'CUSTOMER_HK': 'CUSTOMER_ID'
} -%}

{{ automate_dv.stage(
    include_source_columns=true,
    source_model=source_model,
    hashed_columns=hashed_columns,
    derived_columns=derived_columns,
    null_columns=null_columns
) }}

-- Expected: a view with original columns + ORDER_HK, CUSTOMER_HK (binary hashes),
-- ORDER_HASHDIFF (multi-column hash), EFFECTIVE_FROM, RECORD_SOURCE, and
-- CUSTOMER_HK nulled-out when CUSTOMER_ID IS NULL.

```

--------------------------------

### automate_dv.stage

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Builds a multi-CTE staging model from a raw source, optionally deriving new columns, hashing business keys into surrogate keys, nullifying columns on missing keys, and adding rank columns. It is always the first macro called in any AutomateDV pipeline.

```APIDOC
## automate_dv.stage

### Description
Builds a multi-CTE staging model from a raw source, optionally deriving new columns, hashing business keys into surrogate keys, nullifying columns on missing keys, and adding rank columns. It is always the first macro called in any AutomateDV pipeline.

### Parameters
- **include_source_columns** (boolean) - Required - Whether to include original source columns.
- **source_model** (object) - Required - Defines the source data. Example: `{'raw': 'orders'}`.
- **hashed_columns** (object) - Optional - Defines columns to be hashed. Example: `{'ORDER_HK': 'ORDER_ID', 'ORDER_HASHDIFF': {'is_hashdiff': true, 'columns': ['ORDER_ID', 'CUSTOMER_ID', 'ORDER_TOTAL', 'STATUS']}}`.
- **derived_columns** (object) - Optional - Defines columns to be derived. Example: `{'EFFECTIVE_FROM': 'LOAD_DATE', 'RECORD_SOURCE': '!RAW_ORDERS'}`.
- **null_columns** (object) - Optional - Defines columns to be nullified based on a condition. Example: `{'CUSTOMER_HK': 'CUSTOMER_ID'}`.

### Example
```jinja
{{ automate_dv.stage(
    include_source_columns=true,
    source_model={'raw': 'orders'},
    hashed_columns={
        'ORDER_HK':   'ORDER_ID',
        'CUSTOMER_HK': 'CUSTOMER_ID',
        'ORDER_HASHDIFF': {
            'is_hashdiff': true,
            'columns': ['ORDER_ID', 'CUSTOMER_ID', 'ORDER_TOTAL', 'STATUS']
        }
    },
    derived_columns={
        'EFFECTIVE_FROM': 'LOAD_DATE',
        'RECORD_SOURCE':  '!RAW_ORDERS'
    },
    null_columns={
        'CUSTOMER_HK': 'CUSTOMER_ID'
    }
) }}
```

### Expected Output
A view with original columns plus `ORDER_HK`, `CUSTOMER_HK` (binary hashes), `ORDER_HASHDIFF` (multi-column hash), `EFFECTIVE_FROM`, `RECORD_SOURCE`, and `CUSTOMER_HK` nulled-out when `CUSTOMER_ID` IS NULL.
```

--------------------------------

### automate_dv.hub

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Loads unique business key surrogate hashes into a Hub. Accepts one or multiple source stage models (multi-source load). Deduplicates on primary key per load-date timestamp and only inserts rows that do not already exist in the target (incremental-safe).

```APIDOC
## automate_dv.hub

### Description
Loads unique business key surrogate hashes into a Hub. Accepts one or multiple source stage models (multi-source load). Deduplicates on primary key per load-date timestamp and only inserts rows that do not already exist in the target (incremental-safe).

### Parameters
- **src_pk** (string) - Required - The primary key of the Hub (e.g., surrogate hash).
- **src_nk** (string) - Required - The business key from the source.
- **src_ldts** (string) - Required - The load date timestamp column from the source.
- **src_source** (string) - Required - The record source column from the source.
- **source_model** (string or list) - Required - The source stage model(s) to load from.
- **src_extra_columns** (list) - Optional - Additional columns to include in the Hub.

### Example
```jinja
{{ automate_dv.hub(
    src_pk='ORDER_HK',
    src_nk='ORDER_ID',
    src_ldts='LOAD_DATETIME',
    src_source='RECORD_SOURCE',
    source_model=['stg_orders_web', 'stg_orders_mobile']
) }}
```

### Expected Output
A HUB_ORDER table with columns (ORDER_HK, ORDER_ID, LOAD_DATETIME, RECORD_SOURCE). Only new hash keys are inserted on each run.
```

--------------------------------

### automate_dv.link

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Creates a Link recording the relationship between two or more Hub surrogate keys. Accepts multiple source stage models for multi-source loads and deduplicates on link primary key per load-date.

```APIDOC
## automate_dv.link

### Description
Creates a Link recording the relationship between two or more Hub surrogate keys. Accepts multiple source stage models for multi-source loads and deduplicates on link primary key per load-date.

### Parameters
- **src_pk** (string) - Required - The primary key of the Link (e.g., surrogate hash).
- **src_fk** (list) - Required - A list of foreign key columns (Hub surrogate hashes) that form the link.
- **src_ldts** (string) - Required - The load date timestamp column from the source.
- **src_source** (string) - Required - The record source column from the source.
- **source_model** (string) - Required - The source stage model to load from.
- **src_extra_columns** (list) - Optional - Additional columns to include in the Link.

### Example
```jinja
{{ automate_dv.link(
    src_pk='ORDER_CUSTOMER_HK',
    src_fk=['ORDER_HK', 'CUSTOMER_HK'],
    src_ldts='LOAD_DATETIME',
    src_source='RECORD_SOURCE',
    source_model='stg_orders'
) }}
```

### Expected Output
A LINK_ORDER_CUSTOMER table with columns (ORDER_CUSTOMER_HK, ORDER_HK, CUSTOMER_HK, LOAD_DATETIME, RECORD_SOURCE).
```

--------------------------------

### Generate Hashes with automate_dv.hash

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

This utility macro generates MD5 or SHA hashes for single columns (surrogate keys) or multiple columns (hashdiffs). Configure the hash algorithm and null placeholder via dbt variables.

```jinja
-- Direct usage inside a custom model or macro:
-- dbt_project.yml:
-- vars:
--   hash: SHA   # or MD5 (default)
--   concat_string: '||'
--   null_placeholder_string: '^^'

-- Single-column hash (surrogate key):
{{ automate_dv.hash(columns='CUSTOMER_ID', alias='CUSTOMER_HK') }}
-- Generates: MD5(UPPER(TRIM(CAST(CUSTOMER_ID AS VARCHAR)))) AS CUSTOMER_HK

-- Multi-column hashdiff (payload change detection, columns auto-sorted alphabetically):
{{ automate_dv.hash(
    columns=['EMAIL', 'PHONE', 'FIRST_NAME'],
    alias='CUSTOMER_HASHDIFF',
    is_hashdiff=true
) }}
-- Generates: MD5(UPPER(TRIM(CAST(EMAIL AS VARCHAR))) || '^^' ||
--                UPPER(TRIM(CAST(FIRST_NAME AS VARCHAR))) || '^^' ||
--                UPPER(TRIM(CAST(PHONE AS VARCHAR)))) AS CUSTOMER_HASHDIFF
-- Note: columns are sorted alphabetically when is_hashdiff=true.

```

--------------------------------

### Generate Extended Tracking Satellite (XTS) Table

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Use the `xts` macro to create a table that tracks which satellites contain data for a given Hub key. This is useful for optimizing queries against large satellite landscapes by providing a lookup index.

```jinja
-- models/vault/xts_customer.sql
{{- config(materialized='incremental', unique_key='CUSTOMER_HK') -}}

{%- set src_pk     = 'CUSTOMER_HK' -%}
{%- set src_ldts   = 'LOAD_DATETIME' -%}
{%- set src_source = 'RECORD_SOURCE' -%}
{%- set source_model = ['stg_customer_core', 'stg_customer_crm'] -%}

{%- set src_satellite = { 
    'SAT_CUSTOMER_CORE': { 
        'sat_name': {'SATELLITE_NAME': 'SAT_CUSTOMER_CORE'},
        'hashdiff': {'HASHDIFF': 'CORE_HASHDIFF'}
    },
    'SAT_CUSTOMER_CRM': {
        'sat_name': {'SATELLITE_NAME': 'SAT_CUSTOMER_CRM'},
        'hashdiff': {'HASHDIFF': 'CRM_HASHDIFF'}
    }
} -%}

{{ automate_dv.xts(
    src_pk=src_pk,
    src_satellite=src_satellite,
    src_extra_columns=none,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: XTS_CUSTOMER with (CUSTOMER_HK, HASHDIFF, SATELLITE_NAME,
-- LOAD_DATETIME, RECORD_SOURCE). One row per satellite entry per load.
```

--------------------------------

### Period-based Loading with vault_insert_by_period

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

This custom dbt materialization processes large datasets in batches by iterating through time slices. The model SQL must contain the `__PERIOD_FILTER__` placeholder, which is dynamically replaced for each iteration.

```jinja
-- models/vault/hub_order.sql  (historical backfill example)
{{- config(
    materialized='vault_insert_by_period',
    meta={
        'period': 'month',
        'timestamp_field': 'LOAD_DATETIME',
        'date_source_models': 'stg_orders'
    }
) -}}

{%- set src_pk       = 'ORDER_HK' -%}
{%- set src_nk       = 'ORDER_ID' -%}
{%- set src_ldts     = 'LOAD_DATETIME' -%}
{%- set src_source   = 'RECORD_SOURCE' -%}
{%- set source_model = 'stg_orders' -%}

{{ automate_dv.hub(src_pk, src_nk, none, src_ldts, src_source, source_model) }}

-- AutomateDV replaces __PERIOD_FILTER__ with:
--   LOAD_DATETIME >= '2023-01-01' AND LOAD_DATETIME < '2023-02-01'
-- for each monthly iteration, committing after each slice.
-- Logs: "Running for month 1 of 24 (2023-01-01); 45320 records inserted"

```

--------------------------------

### Build Bridge Table with automate_dv.bridge

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Use this macro to construct a bridge table that connects multiple link tables from a hub. It filters for active relationships based on effectivity satellites at specified AS_OF dates.

```jinja
-- models/vault/bridge_customer_orders.sql
{{- config(
    materialized='incremental',
    unique_key='CUSTOMER_HK'
) -}}

{%- set src_pk            = 'CUSTOMER_HK' -%}
{%- set src_ldts          = 'LOAD_DATETIME' -%}
{%- set source_model      = 'hub_customer' -%}
{%- set as_of_dates_table = 'as_of_date' -%}
{%- set stage_tables_ldts = ['stg_orders'] -%}

{%- set bridge_walk = {
    'ORDER': {
        'bridge_link_pk':    'LINK_CUSTOMER_ORDER_HK',
        'bridge_start_date': 'EFF_SAT_CUSTOMER_ORDER_STARTDATE',
        'bridge_end_date':   'EFF_SAT_CUSTOMER_ORDER_ENDDATE',
        'bridge_load_date':  'EFF_SAT_CUSTOMER_ORDER_LDTS',
        'link_table':        'LINK_CUSTOMER_ORDER',
        'link_pk':           'LINK_CUSTOMER_ORDER_HK',
        'link_fk1':          'CUSTOMER_HK',
        'link_fk2':          'ORDER_HK',
        'eff_sat_table':     'EFF_SAT_CUSTOMER_ORDER',
        'eff_sat_pk':        'LINK_CUSTOMER_ORDER_HK',
        'eff_sat_start_date':'START_DATE',
        'eff_sat_end_date':  'END_DATE',
        'eff_sat_ldts':      'LOAD_DATETIME'
    }
} -%}

{{ automate_dv.bridge(
    src_pk=src_pk,
    src_extra_columns=none,
    as_of_dates_table=as_of_dates_table,
    bridge_walk=bridge_walk,
    stage_tables_ldts=stage_tables_ldts,
    src_ldts=src_ldts,
    source_model=source_model
) }}

-- Expected: BRIDGE_CUSTOMER_ORDERS with (CUSTOMER_HK, AS_OF_DATE,
-- LINK_CUSTOMER_ORDER_HK) — only rows where the effectivity sat end_date
-- equals max_datetime (i.e. the relationship is currently open).

```

--------------------------------

### Generate Reference Table (REF_TABLE)

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Use the `ref_table` macro to load a reference/code-table entity from one or more stage models into a deduplicated reference table. This macro only inserts new primary keys.

```jinja
-- models/vault/ref_order_status.sql
{{- config(materialized='incremental', unique_key='STATUS_HK') -}}

{%- set src_pk           = 'STATUS_HK' -%}
{%- set src_extra_columns = ['STATUS_DESCRIPTION', 'ACTIVE_FLAG'] -%}
{%- set src_ldts         = 'LOAD_DATETIME' -%}
{%- set src_source       = 'RECORD_SOURCE' -%}
{%- set source_model     = ['stg_order_status'] -%}

{{ automate_dv.ref_table(
    src_pk=src_pk,
    src_extra_columns=src_extra_columns,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: REF_ORDER_STATUS with distinct records:
-- (STATUS_HK, STATUS_DESCRIPTION, ACTIVE_FLAG, LOAD_DATETIME, RECORD_SOURCE).
```

--------------------------------

### Generate Point-in-Time (PIT) Table

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

The `pit` macro generates a PIT table by cross-joining Hub primary keys against an AS_OF dates calendar and LEFT JOINing each Satellite. This provides a performant query anchor for time-travel queries across multiple satellites.

```jinja
-- models/vault/pit_customer.sql
{{- config(
    materialized='incremental',
    unique_key='CUSTOMER_HK'
) -}}

{%- set src_pk             = 'CUSTOMER_HK' -%}
{%- set src_ldts           = 'LOAD_DATETIME' -%}
{%- set source_model       = 'hub_customer' -%}
{%- set as_of_dates_table  = 'as_of_date' -%}
{%- set stage_tables_ldts  = ['stg_customer_core', 'stg_customer_crm'] -%}

{%- set satellites = { 
    'SAT_CUSTOMER_CORE': { 
        'pk':   {'SAT_CUSTOMER_CORE_PK':   'CUSTOMER_HK'},
        'ldts': {'SAT_CUSTOMER_CORE_LDTS': 'LOAD_DATETIME'}
    },
    'SAT_CUSTOMER_CRM': {
        'pk':   {'SAT_CUSTOMER_CRM_PK':    'CUSTOMER_HK'},
        'ldts': {'SAT_CUSTOMER_CRM_LDTS':  'LOAD_DATETIME'}
    }
} -%}

{{ automate_dv.pit(
    src_pk=src_pk,
    src_extra_columns=none,
    as_of_dates_table=as_of_dates_table,
    satellites=satellites,
    stage_tables_ldts=stage_tables_ldts,
    src_ldts=src_ldts,
    source_model=source_model
) }}

-- Expected: PIT_CUSTOMER with one row per (CUSTOMER_HK, AS_OF_DATE) containing
-- SAT_CUSTOMER_CORE_PK, SAT_CUSTOMER_CORE_LDTS, SAT_CUSTOMER_CRM_PK,
-- SAT_CUSTOMER_CRM_LDTS — ghost values (0x000... / 1900-01-01) for missing rows.
```

--------------------------------

### Implement Multi-Active Satellite Table with automate_dv.ma_sat

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Use this model for entities that legitimately hold multiple active records per primary key at the same time, distinguished by a Child Dependent Key (CDK). It detects changes across the entire group of active CDK rows before inserting.

```sql
-- models/vault/ma_sat_customer_contacts.sql
{{- config(materialized='incremental', unique_key='CUSTOMER_HK') -}}

{%- set src_pk           = 'CUSTOMER_HK' -%}
{%- set src_cdk          = ['CONTACT_TYPE', 'CONTACT_VALUE'] -%}
{%- set src_hashdiff     = 'CONTACT_HASHDIFF' -%}
{%- set src_payload      = ['CONTACT_NOTES'] -%}
{%- set src_extra_columns = none -%}
{%- set src_eff          = none -%}
{%- set src_ldts         = 'LOAD_DATETIME' -%}
{%- set src_source       = 'RECORD_SOURCE' -%}
{%- set source_model     = 'stg_customer_contacts' -%}

{{ automate_dv.ma_sat(
    src_pk=src_pk,
    src_cdk=src_cdk,
    src_hashdiff=src_hashdiff,
    src_payload=src_payload,
    src_extra_columns=src_extra_columns,
    src_eff=src_eff,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: MA_SAT_CUSTOMER_CONTACTS with one row per (CUSTOMER_HK, CONTACT_TYPE,
-- CONTACT_VALUE) combination per load cycle where at least one CDK changed.
```

--------------------------------

### Implement Satellite Table with automate_dv.sat

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Use this model to track the full history of descriptive attributes for a Hub or Link entity. It inserts a new record only when the payload has changed, preserving a complete audit trail. Supports ghost records and optional per-source filtering.

```sql
-- models/vault/sat_order_details.sql
{{- config(materialized='incremental',
           unique_key='ORDER_HK',
           tags=['satellite']) -}}

{%- set src_pk           = 'ORDER_HK' -%}
{%- set src_hashdiff     = 'ORDER_HASHDIFF' -%}
{%- set src_payload      = ['ORDER_TOTAL', 'STATUS', 'UPDATED_AT'] -%}
{%- set src_extra_columns = none -%}
{%- set src_eff          = none -%}
{%- set src_ldts         = 'LOAD_DATETIME' -%}
{%- set src_source       = 'RECORD_SOURCE' -%}
{%- set source_model     = 'stg_orders' -%}

{{ automate_dv.sat(
    src_pk=src_pk,
    src_hashdiff=src_hashdiff,
    src_payload=src_payload,
    src_extra_columns=src_extra_columns,
    src_eff=src_eff,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: SAT_ORDER_DETAILS with one row per change event:
-- (ORDER_HK, ORDER_HASHDIFF, ORDER_TOTAL, STATUS, UPDATED_AT,
--  LOAD_DATETIME, RECORD_SOURCE).
-- Ghost records inserted if vars('enable_ghost_records', true).
```

--------------------------------

### Vault Insert by Rank Materialization

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Use this materialization for rank-based batch loading of Data Vault structures. It requires a pre-computed integer rank column and processes one rank value per iteration. Ensure the source model includes a LOAD_RANK column (e.g., DENSE_RANK() over LOAD_DATETIME). The maximum number of rank values processed per run is 100,000.

```jinja
-- models/vault/sat_order_details.sql
{{- config(
    materialized='vault_insert_by_rank',
    meta={
        'rank_column': 'LOAD_RANK',
        'rank_source_models': 'stg_orders'
    }
) -}}

{%- set src_pk       = 'ORDER_HK' -%}
{%- set src_hashdiff = 'ORDER_HASHDIFF' -%}
{%- set src_payload  = ['ORDER_TOTAL', 'STATUS'] -%}
{%- set src_ldts     = 'LOAD_DATETIME' -%}
{%- set src_source   = 'RECORD_SOURCE' -%}
{%- set source_model = 'stg_orders' -%}

{{ automate_dv.sat(src_pk, src_hashdiff, src_payload, none, none,
                   src_ldts, src_source, source_model) }}

-- The stg_orders model must include a LOAD_RANK column (DENSE_RANK() over LOAD_DATETIME).
-- AutomateDV iterates rank 1 → max_rank, replacing __RANK_FILTER__
-- with: LOAD_RANK = 1, then LOAD_RANK = 2, etc.
-- Max 100,000 rank values per run (raises error if exceeded).

```

--------------------------------

### Implement Effectivity Satellite Table with automate_dv.eff_sat

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

Use this model to record the open/closed status of a Link relationship over time. It creates opening records for new relationships, closing records for ended relationships, and reopening records for reinstated relationships. Supports auto end-dating via `is_auto_end_dating` model meta config.

```sql
-- models/vault/eff_sat_order_customer.sql
{{- config(
    materialized='incremental',
    unique_key='ORDER_CUSTOMER_HK',
    meta={'is_auto_end_dating': true}
) -}}

{%- set src_pk         = 'ORDER_CUSTOMER_HK' -%}
{%- set src_dfk        = 'ORDER_HK' -%}
{%- set src_sfk        = 'CUSTOMER_HK' -%}
{%- set src_extra_columns = none -%}
{%- set src_start_date = 'START_DATE' -%}
{%- set src_end_date   = 'END_DATE' -%}
{%- set src_eff        = 'EFFECTIVE_FROM' -%}
{%- set src_ldts       = 'LOAD_DATETIME' -%}
{%- set src_source     = 'RECORD_SOURCE' -%}
{%- set source_model   = 'stg_orders' -%}

{{ automate_dv.eff_sat(
    src_pk=src_pk,
    src_dfk=src_dfk,
    src_sfk=src_sfk,
    src_extra_columns=src_extra_columns,
    src_start_date=src_start_date,
    src_end_date=src_end_date,
    src_eff=src_eff,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: EFF_SAT_ORDER_CUSTOMER with open records (END_DATE = max_datetime)
-- and closed records when a relationship ends. Auto end-dating closes the prior
-- open record when a new SFK appears for the same DFK.
```

--------------------------------

### Generate Non-Historised (Transactional) Link Table (NH_LINK)

Source: https://context7.com/datavault-uk/automate-dv/llms.txt

The `nh_link` macro loads transactional link records that are immutable. It retains payload columns alongside the relationship keys. `t_link` is a deprecated alias for this macro.

```jinja
-- models/vault/nh_link_order_payment.sql
{{- config(materialized='incremental', unique_key='ORDER_PAYMENT_HK') -}}

{%- set src_pk           = 'ORDER_PAYMENT_HK' -%}
{%- set src_fk           = ['ORDER_HK', 'PAYMENT_HK'] -%}
{%- set src_payload      = ['PAYMENT_AMOUNT', 'PAYMENT_METHOD'] -%}
{%- set src_extra_columns = none -%}
{%- set src_eff          = 'EFFECTIVE_FROM' -%}
{%- set src_ldts         = 'LOAD_DATETIME' -%}
{%- set src_source       = 'RECORD_SOURCE' -%}
{%- set source_model     = 'stg_payments' -%}

{{ automate_dv.nh_link(
    src_pk=src_pk,
    src_fk=src_fk,
    src_payload=src_payload,
    src_extra_columns=src_extra_columns,
    src_eff=src_eff,
    src_ldts=src_ldts,
    src_source=src_source,
    source_model=source_model
) }}

-- Expected: NH_LINK_ORDER_PAYMENT retaining payload columns:
-- (ORDER_PAYMENT_HK, ORDER_HK, PAYMENT_HK, PAYMENT_AMOUNT,
--  PAYMENT_METHOD, EFFECTIVE_FROM, LOAD_DATETIME, RECORD_SOURCE).
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.