### Example Output SQL (dbtvault)

Source: https://automate-dv.readthedocs.io/en/latest/macros

SQL query demonstrating the output structure for a dbtvault staging table load.

```sql
WITH row_rank_1 AS (
    SELECT * 
    FROM
    (
    SELECT rr.CUSTOMER_HK, rr.CUSTOMER_ID, rr.LOAD_DATETIME, rr.RECORD_SOURCE,
           ROW_NUMBER() OVER(
               PARTITION BY rr.CUSTOMER_HK
               ORDER BY rr.LOAD_DATETIME
           ) AS row_number
    FROM `dbtvault-341416`.`dbtvault`.`stg_customer` AS rr
    WHERE rr.CUSTOMER_HK IS NOT NULL
    AND rr.CUSTOMER_ID IS NOT NULL
    QUALIFY row_number = 1
),
records_to_insert AS (
    SELECT a.CUSTOMER_HK, a.CUSTOMER_ID, a.LOAD_DATETIME, a.RECORD_SOURCE
    FROM row_rank_1 AS a
)
SELECT * FROM records_to_insert

```

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

### Install Automate DV from GitHub

Source: https://automate-dv.readthedocs.io/en/latest/changelog

This version cannot be installed via dbt hub. Use this configuration to install directly from the GitHub repository.

```yaml
packages:
  - git: "https://github.com/Datavault-UK/automate-dv.git"
    revision: v0.7.6.1
```

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

### Base Load Output Example

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Shows the dbt run output for an initial or base load of an effective snapshot model using the `vault_insert_by_period` materialisation.

```text
15:24:08 | Concurrency: 4 threads (target='snowflake')
15:24:08 | 15:24:08 | 1 of 1 START vault_insert_by_period model TEST.EFF_SAT..... [RUN]
15:24:10 | 1 of 1 OK created vault_insert_by_period model TEST.EFF_SAT [BASE LOAD 1 in 1.78s]
15:24:10 | 15:24:10 | Finished running 1 vault_insert_by_period model in 3.99s.
```

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

### Multi-Column Hashing Example

Source: https://automate-dv.readthedocs.io/en/latest/best_practises/hashing

This SQL demonstrates how to construct a hash for multiple columns, handling NULLs and ensuring consistent concatenation for reliable hashing.

```sql
CAST(MD5_BINARY(NULLIF(CONCAT_WS('||', 
    IFNULL(NULLIF(UPPER(TRIM(CAST(CUSTOMER_ID AS VARCHAR))), ''), '^^'),
    IFNULL(NULLIF(UPPER(TRIM(CAST(DOB AS VARCHAR))), ''), '^^'),
    IFNULL(NULLIF(UPPER(TRIM(CAST(PHONE AS VARCHAR))), ''), '^^')
), '^^||^^||^^')) AS BINARY(16)) AS CUSTOMER_HK
```

```sql
CAST(MD5_BINARY(CONCAT_WS('||',
    IFNULL(NULLIF(UPPER(TRIM(CAST(CUSTOMER_ID AS VARCHAR))), ''), '^^'),
    IFNULL(NULLIF(UPPER(TRIM(CAST(DOB AS VARCHAR))), ''), '^^'),
    IFNULL(NULLIF(UPPER(TRIM(CAST(PHONE AS VARCHAR))), ''), '^^')
)) AS BINARY(16)) AS HASHDIFF
```

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

### Incremental Load Output Example

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Illustrates the dbt run output for an incremental load, showing processing for multiple periods and record insertions.

```text
15:24:16 | Concurrency: 4 threads (target='snowflake')
15:24:16 | 15:24:16 | 1 of 1 START vault_insert_by_period model TEST.EFF_SAT..... [RUN]
15:24:17 + Running for day 1 of 4 (2020-01-10) [model.automate_dv_test.EFF_SAT]
15:24:18 + Ran for day 1 of 4 (2020-01-10); 0 records inserted [model.automate_dv_test.EFF_SAT]
15:24:18 + Running for day 2 of 4 (2020-01-11) [model.automate_dv_test.EFF_SAT]
15:24:20 + Ran for day 2 of 4 (2020-01-11); 0 records inserted [model.automate_dv_test.EFF_SAT]
15:24:20 + Running for day 3 of 4 (2020-01-12) [model.automate_dv_test.EFF_SAT]
15:24:21 + Ran for day 3 of 4 (2020-01-12); 2 records inserted [model.automate_dv_test.EFF_SAT]
15:24:22 + Running for day 4 of 4 (2020-01-13) [model.automate_dv_test.EFF_SAT]
15:24:24 + Ran for day 4 of 4 (2020-01-13); 2 records inserted [model.automate_dv_test.EFF_SAT]
15:24:24 | 1 of 1 OK created vault_insert_by_period model TEST.EFF_SAT [INSERT 4 in 8.13s]
15:24:25 | 15:24:25 | Finished running 1 vault_insert_by_period model in 10.24s.
```

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

### Basic Hashed Columns Configuration

Source: https://automate-dv.readthedocs.io/en/latest/macros/stage_macro_configurations

Configure hashed columns to generate hash keys and hashdiffs. This example shows the basic setup for creating a customer hash key (CUSTOMER_HK) based on the CUSTOMER_ID.

```jinja
{% set yaml_metadata %}
hashed_columns:
  CUSTOMER_HK: CUSTOMER_ID
{% endset %}   

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ automate_dv.stage(include_source_columns=true,
                     source_model=source_model,
                     derived_columns=derived_columns,
                     null_columns=null_columns,
                     hashed_columns=metadata_dict['hashed_columns'],
                     ranked_columns=ranked_columns) }}
```

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

### Example Output SQL (Automate DV Test)

Source: https://automate-dv.readthedocs.io/en/latest/macros

SQL query showing output for Automate DV Test environment, using ROW_NUMBER for deduplication.

```sql
WITH row_rank_1 AS (
    SELECT * 
    FROM
    (
    SELECT rr.CUSTOMER_HK, rr.CUSTOMER_ID, rr.LOAD_DATETIME, rr.RECORD_SOURCE,
           ROW_NUMBER() OVER(
               PARTITION BY rr.CUSTOMER_HK
               ORDER BY rr.LOAD_DATETIME
           ) AS row_number
    FROM "AUTOMATE_DV_TEST"."TEST"."stg_customer" AS rr
    WHERE rr.CUSTOMER_HK IS NOT NULL
    AND rr.CUSTOMER_ID IS NOT NULL
    )
    WHERE l.row_number = 1
    ),
records_to_insert AS (
    SELECT a.CUSTOMER_HK, a.CUSTOMER_ID, a.LOAD_DATETIME, a.RECORD_SOURCE
    FROM row_rank_1 AS a
)
SELECT * FROM records_to_insert

```

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

### Hub Macro with Multiple Source Models

Source: https://automate-dv.readthedocs.io/en/latest/metadata

This example demonstrates defining a hub with multiple source models. The `source_model` parameter accepts a list of source model names.

```sql
{%- set yaml_metadata -%}
source_model: 
  - stg_web_customer_hashed
  - stg_crm_customer_hashed
src_pk: CUSTOMER_HK
src_nk: CUSTOMER_ID
src_ldts: LOAD_DATETIME
src_source: 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"]) }}

```

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

### Snowflake and MS SQL Server Scoping Example

Source: https://automate-dv.readthedocs.io/en/latest/macros/stage_macro_configurations

Demonstrates how derived columns can be used as components in hashed columns. This example shows date formatting in Snowflake and MS SQL Server, followed by its use in a HASHDIFF calculation.

```yaml
source_model: MY_STAGE
derived_columns:
  CUSTOMER_DOB_UK: "TO_VARCHAR(CUSTOMER_DOB::date, 'DD-MM-YYYY')"
  RECORD_SOURCE: "!RAW_CUSTOMER"
  EFFECTIVE_FROM: BOOKING_DATE
hashed_columns:
  CUSTOMER_HK: CUSTOMER_ID
  HASHDIFF:
    is_hashdiff: true 
    columns:
      - CUSTOMER_NAME
      - CUSTOMER_DOB_UK
      - CUSTOMER_PHONE
```

```yaml
source_model: MY_STAGE
derived_columns:
  CUSTOMER_DOB_UK: "CONVERT(VARCHAR(10), CONVERT(DATE, CUSTOMER_DOB, 103), 105)"
  RECORD_SOURCE: "!RAW_CUSTOMER"
  EFFECTIVE_FROM: BOOKING_DATE
hashed_columns:
  CUSTOMER_HK: CUSTOMER_ID
  HASHDIFF:
    is_hashdiff: true 
    columns:
      - CUSTOMER_NAME
      - CUSTOMER_DOB_UK
      - CUSTOMER_PHONE
```

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

### Hashed Columns Configuration Example (UPPER)

Source: https://automate-dv.readthedocs.io/en/latest/macros

Configure hashed columns in your model. This example shows hashing `CUSTOMER_ID` into `CUSTOMER_HK` using the default UPPER casing.

```yaml
source_model: raw_source
hashed_columns:
  CUSTOMER_HK: CUSTOMER_ID
```

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

### SQL: Full Data Transformation Pipeline

Source: https://automate-dv.readthedocs.io/en/latest/macros

This snippet demonstrates a comprehensive data pipeline, starting from source data, deriving new columns, handling nulls, generating hash keys, and finally ranking records. Use this as a template for complex transformations.

```sql
WITH source_data AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment
    FROM `dbtvault-341416`.`dbtvault`.`CUSTOMER`
),
derived_columns AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    C_CUSTKEY AS CUSTOMER_ID,
    '1998-01-01' AS LOAD_DATETIME,
    'TPCH_CUSTOMER' AS RECORD_SOURCE
    FROM source_data
),
null_columns AS (
    SELECT
    c_custkey,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID AS CUSTOMER_ID_ORIGINAL,
        IFNULL(CUSTOMER_ID, '-1') AS CUSTOMER_ID,
    C_NAME AS C_NAME_ORIGINAL,
        IFNULL(C_NAME, '-2') AS C_NAME
    FROM derived_columns
),
hashed_columns AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    CUSTOMER_ID,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID_ORIGINAL,
    C_NAME_ORIGINAL,
    CAST(UPPER(TO_HEX(MD5(NULLIF(UPPER(TRIM(CAST(C_CUSTKEY AS STRING))), '')))) AS STRING) AS CUSTOMER_HK
    FROM null_columns
),
ranked_columns AS (
    SELECT *,
    RANK() OVER (PARTITION BY CUSTOMER_HK ORDER BY LOAD_DATETIME) AS AUTOMATE_DV_RANK
    FROM hashed_columns
),
columns_to_select AS (
    SELECT
    c_custkey,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    CUSTOMER_ID,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID_ORIGINAL,
    C_NAME,
    C_NAME_ORIGINAL,
    CUSTOMER_HK,
    AUTOMATE_DV_RANK
    FROM ranked_columns
)
SELECT * FROM columns_to_select
```

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

### Example of Multi-Column Hashing with Custom Strings

Source: https://automate-dv.readthedocs.io/en/latest/best_practises/hashing

This SQL snippet demonstrates how to perform multi-column hashing using custom concatenation and null placeholder strings, applying transformations like TRIM, UPPER, and NULLIF.

```sql
CAST(MD5_BINARY(NULLIF(CONCAT_WS('!!', 
    IFNULL(NULLIF(UPPER(TRIM(CAST(CUSTOMER_ID AS VARCHAR))), ''), '##'),
    IFNULL(NULLIF(UPPER(TRIM(CAST(DOB AS VARCHAR))), ''), '##'), '!!',
    IFNULL(NULLIF(UPPER(TRIM(CAST(PHONE AS VARCHAR))), ''), '##')
), '##!!##!!##')) AS BINARY(16)) AS CUSTOMER_HK
```

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

### Link Model with Metadata Configuration

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_links

A dbt model for a Link, including configuration for incremental materialization and definition of metadata variables. This example shows how to define source model, primary key, foreign keys, load timestamp, and record source for the `link_customer_order`.

```sql
{{ config(materialized='incremental')         }}

{%- set source_model = "v_stg_orders"        -%}
{%- set src_pk = "CUSTOMER_ORDER_HK"         -%}
{%- set src_fk = ["CUSTOMER_HK", "ORDER_HK"] -%}
{%- set src_ldts = "LOAD_DATETIME"           -%}
{%- set src_source = "RECORD_SOURCE"         -%}

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

```

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

### Base Load Macro Example

Source: https://automate-dv.readthedocs.io/en/latest/macros

This SQL macro is used for a base load of customer data. It selects distinct customer records from the staging table without complex logic for incremental updates.

```sql
    WITH source_data AS (
    SELECT DISTINCT s.CUSTOMER_HK, s.HASHDIFF, s.CUSTOMER_PHONE, s.CUSTOMER_PHONE_LOCATOR_ID, s.CUSTOMER_NAME, s.EFFECTIVE_FROM, s.LOAD_DATETIME, s.RECORD_SOURCE
    FROM "AUTOMATE_DV_TEST"."TEST"."stg_customer" AS s
    WHERE s.CUSTOMER_HK IS NOT NULL
        AND s.CUSTOMER_PHONE IS NOT NULL
        AND s.CUSTOMER_PHONE_LOCATOR_ID IS NOT NULL
),
records_to_insert AS (
SELECT source_data.CUSTOMER_HK, source_data.HASHDIFF, source_data.CUSTOMER_PHONE, source_data.CUSTOMER_PHONE_LOCATOR_ID, source_data.CUSTOMER_NAME, source_data.EFFECTIVE_FROM, source_data.LOAD_DATETIME, source_data.RECORD_SOURCE
    FROM source_data
)
SELECT * FROM records_to_insert
```

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

### Incremental Load Macro Example

Source: https://automate-dv.readthedocs.io/en/latest/macros

This SQL macro is used for incremental loading of customer data. It identifies distinct customer records, counts sources, and determines which records to insert based on the latest available data and source counts.

```sql
    WITH source_data AS (
    SELECT DISTINCT s.CUSTOMER_HK, s.CUSTOMER_PHONE, s.CUSTOMER_PHONE_LOCATOR_ID, s.CUSTOMER_NAME, s.HASHDIFF, s.EFFECTIVE_FROM, s.LOAD_DATETIME, s.RECORD_SOURCE
    FROM `dbtvault-341416`.`dbtvault`.`stg_customer` AS s
    WHERE s.CUSTOMER_HK IS NOT NULL
        AND s.CUSTOMER_PHONE IS NOT NULL
        AND s.CUSTOMER_PHONE_LOCATOR_ID IS NOT NULL
),
source_data_with_count AS (
    SELECT a.*,
           b.source_count
    FROM source_data a
    INNER JOIN (
        SELECT t.CUSTOMER_HK,
            COUNT(*) AS source_count
        FROM (SELECT DISTINCT s.CUSTOMER_HK, s.HASHDIFF, s.CUSTOMER_PHONE, s.CUSTOMER_PHONE_LOCATOR_ID FROM source_data AS s) AS t
        GROUP BY t.CUSTOMER_HK
    ) AS b
    ON a.CUSTOMER_HK = b.CUSTOMER_HK
),
latest_records AS (
    SELECT mas.CUSTOMER_HK, mas.HASHDIFF, mas.CUSTOMER_PHONE, mas.CUSTOMER_PHONE_LOCATOR_ID, mas.LOAD_DATETIME,
           mas.latest_rank,
           DENSE_RANK() OVER (PARTITION BY mas.CUSTOMER_HK
                              ORDER BY mas.HASHDIFF,
                                       mas.CUSTOMER_PHONE, mas.CUSTOMER_PHONE_LOCATOR_ID ASC
           ) AS check_rank
    FROM (
    SELECT inner_mas.CUSTOMER_HK, inner_mas.HASHDIFF, inner_mas.CUSTOMER_PHONE, inner_mas.CUSTOMER_PHONE_LOCATOR_ID, inner_mas.LOAD_DATETIME,
           RANK() OVER (PARTITION BY inner_mas.CUSTOMER_HK
                        ORDER BY inner_mas.LOAD_DATETIME DESC
           ) AS latest_rank
    FROM `dbtvault-341416`.`dbtvault`.`ma_sat_customer_address_incremental` AS inner_mas
    INNER JOIN (SELECT DISTINCT s.CUSTOMER_HK FROM source_data as s ) AS spk
        ON inner_mas.CUSTOMER_HK = spk.CUSTOMER_HK
    ) AS mas
    WHERE latest_rank = 1
),
latest_group_details AS (
    SELECT lr.CUSTOMER_HK,
           lr.LOAD_DATETIME,
           MAX(lr.check_rank) AS latest_count
    FROM latest_records AS lr
    GROUP BY lr.CUSTOMER_HK, lr.LOAD_DATETIME
),
records_to_insert AS (
    SELECT source_data_with_count.CUSTOMER_HK, source_data_with_count.CUSTOMER_PHONE, source_data_with_count.CUSTOMER_PHONE_LOCATOR_ID, source_data_with_count.CUSTOMER_NAME, source_data_with_count.HASHDIFF, source_data_with_count.EFFECTIVE_FROM, source_data_with_count.LOAD_DATETIME, source_data_with_count.RECORD_SOURCE
    FROM source_data_with_count
    WHERE EXISTS (
        SELECT 1
        FROM source_data_with_count AS stage
        WHERE NOT EXISTS (
            SELECT 1
            FROM (
                SELECT lr.CUSTOMER_HK, lr.HASHDIFF, lr.CUSTOMER_PHONE, lr.CUSTOMER_PHONE_LOCATOR_ID, lr.LOAD_DATETIME,
                       lg.latest_count
                FROM latest_records AS lr
                INNER JOIN latest_group_details AS lg
                    ON lr.CUSTOMER_HK = lg.CUSTOMER_HK
                    AND lr.LOAD_DATETIME = lg.LOAD_DATETIME
            ) AS active_records
            WHERE stage.CUSTOMER_HK = active_records.CUSTOMER_HK
                AND stage.HASHDIFF = active_records.HASHDIFF
                AND stage.CUSTOMER_PHONE = active_records.CUSTOMER_PHONE AND stage.CUSTOMER_PHONE_LOCATOR_ID = active_records.CUSTOMER_PHONE_LOCATOR_ID
                AND stage.source_count = active_records.latest_count
        )
        AND source_data_with_count.CUSTOMER_HK = stage.CUSTOMER_HK
    )
)
SELECT * FROM records_to_insert
```

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

### dbt Bridge Macro Metadata Configuration

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_bridges

Example of configuring metadata for the `bridge` macro, including source model, primary key, and detailed link relationship parameters for multiple satellites.

```yaml
{%- set yaml_metadata -%}
source_model: hub_customer
src_pk: CUSTOMER_PK
src_ldts: LOAD_DATETIME
as_of_dates_table: AS_OF_DATE
bridge_walk: {'CUSTOMER_ORDER': {'bridge_link_pk': 'LINK_CUSTOMER_ORDER_PK',
                             'bridge_end_date': 'EFF_SAT_CUSTOMER_ORDER_ENDDATE',
                             'bridge_load_date': 'EFF_SAT_CUSTOMER_ORDER_LOADDATE',
                             'link_table': 'LINK_CUSTOMER_ORDER',
                             'link_pk': 'CUSTOMER_ORDER_PK',
                             'link_fk1': 'CUSTOMER_FK',
                             'link_fk2': 'ORDER_FK',
                             'eff_sat_table': 'EFF_SAT_CUSTOMER_ORDER',
                             'eff_sat_pk': 'CUSTOMER_ORDER_PK',
                             'eff_sat_end_date': 'END_DATE',
                             'eff_sat_load_date': 'LOAD_DATETIME'},
              'ORDER_PRODUCT': {'bridge_link_pk': 'LINK_ORDER_PRODUCT_PK',
                                'bridge_end_date': 'EFF_SAT_ORDER_PRODUCT_ENDDATE',
                                'bridge_load_date': 'EFF_SAT_ORDER_PRODUCT_LOADDATE',
                                'link_table': 'LINK_ORDER_PRODUCT',
                                'link_pk': 'ORDER_PRODUCT_PK',
                                'link_fk1': 'ORDER_FK',
                                'link_fk2': 'PRODUCT_FK',
                                'eff_sat_table': 'EFF_SAT_ORDER_PRODUCT',
                                'eff_sat_pk': 'ORDER_PRODUCT_PK',
                                'eff_sat_end_date': 'END_DATE',
                                'eff_sat_load_date': 'LOAD_DATETIME'}}
stage_tables_ldts: {'STG_CUSTOMER_ORDER': 'LOAD_DATETIME',
                    'STG_CUSTOMER_PRODUCT': 'LOAD_DATETIME'}

```

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

### Vault Insert By Period: Config Level with Start Date

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Loads data from the start date to the current date. Uses the config level for dbt Core compatibility.

```sql
{{ config(materialized='vault_insert_by_period', timestamp_field='LOAD_DATE', period='day',
start_date='2020-01-30') }}

{{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk,
                       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) }}
```

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

### Vault Insert By Period: Config Level with Start and Stop Dates

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Loads data between specified start and stop dates. Uses the config level for dbt Core compatibility.

```sql
{{ config(materialized='vault_insert_by_period', timestamp_field='LOAD_DATE', period='day',
start_date='2020-01-30', stop_date='2020-04-30') }}

{{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk,
                       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) }}
```

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

### SQL Macro: All Configurations

Source: https://automate-dv.readthedocs.io/en/latest/macros

This macro includes all configurations, processing source columns, null columns, hashed columns, and ranked columns. It's useful for a comprehensive data pipeline.

```sql
WITH source_data AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment
    FROM "dbtvault_db"."development"."CUSTOMER"
),
derived_columns AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    C_CUSTKEY AS CUSTOMER_ID,
    '1998-01-01' AS LOAD_DATETIME,
    'TPCH_CUSTOMER' AS RECORD_SOURCE
    FROM source_data
),
null_columns AS (
    SELECT
    c_custkey,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID AS CUSTOMER_ID_ORIGINAL,
        COALESCE(CUSTOMER_ID, '-1') AS CUSTOMER_ID,
    C_NAME AS C_NAME_ORIGINAL,
        COALESCE(C_NAME, '-2') AS C_NAME
    FROM derived_columns
),
hashed_columns AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    CUSTOMER_ID,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID_ORIGINAL,
    C_NAME_ORIGINAL,
    DECODE(MD5(NULLIF(UPPER(TRIM(CAST(C_CUSTKEY AS VARCHAR))), '')), 'hex') AS CUSTOMER_HK
    FROM null_columns
),
ranked_columns AS (
    SELECT *,
    RANK() OVER (PARTITION BY CUSTOMER_HK ORDER BY LOAD_DATETIME) AS AUTOMATE_DV_RANK
    FROM hashed_columns
),
columns_to_select AS (
    SELECT
    c_custkey,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    CUSTOMER_ID,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID_ORIGINAL,
    C_NAME,
    C_NAME_ORIGINAL,
    CUSTOMER_HK,
    AUTOMATE_DV_RANK
    FROM ranked_columns
)
SELECT * FROM columns_to_select

```

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

### Vault Insert By Period: Meta Level with Start Date

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Loads data from the start date to the current date. Uses the meta level for dbt Core and Fusion compatibility.

```sql
{{ config(materialized='vault_insert_by_period',
          meta={'timestamp_field': 'LOAD_DATE', 'period': 'day', 'start_date': '2020-01-30'}) }}

{{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk,
                       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) }}
```

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

### Vault Insert By Period: Meta Level with Start and Stop Dates

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Loads data between specified start and stop dates. Uses the meta level for dbt Core and Fusion compatibility.

```sql
{{ config(materialized='vault_insert_by_period',
          meta={'timestamp_field': 'LOAD_DATE', 'period': 'day',
                'start_date': '2020-01-30', 'stop_date': '2020-04-30'}) }}

{{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk,
                       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) }}
```

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

### SQL Macro for Single-Source Base Load (Alternative Syntax)

Source: https://automate-dv.readthedocs.io/en/latest/macros

This macro demonstrates an alternative syntax for a single-source base load. It uses a subquery to rank records and then selects the latest one for each customer hash key.

```sql
WITH row_rank_1 AS (
    SELECT CUSTOMER_HK, CUSTOMER_ID, LOAD_DATETIME, RECORD_SOURCE
    FROM (
        SELECT rr.CUSTOMER_HK, rr.CUSTOMER_ID, rr.LOAD_DATETIME, rr.RECORD_SOURCE,
               ROW_NUMBER() OVER(
                   PARTITION BY rr.CUSTOMER_HK
                   ORDER BY rr.LOAD_DATETIME
               ) AS row_number
        FROM "AUTOMATE_DV_TEST"."TEST"."stg_customer" AS rr
        WHERE rr.CUSTOMER_HK IS NOT NULL
    ) h
    WHERE h.row_number = 1
),
records_to_insert AS (
    SELECT a.CUSTOMER_HK, a.CUSTOMER_ID, a.LOAD_DATETIME, a.RECORD_SOURCE
    FROM row_rank_1 AS a
)
```

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

### SQL Macro: Only Source Columns

Source: https://automate-dv.readthedocs.io/en/latest/macros

This macro selects only the source columns from the customer table. It's a basic example for retrieving raw data.

```sql
WITH source_data AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment
    FROM "AUTOMATE_DV_TEST"."TEST"."CUSTOMER"
),
columns_to_select AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment
    FROM source_data
)
SELECT * FROM columns_to_select

```

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

### SQL Macro: All Configurations

Source: https://automate-dv.readthedocs.io/en/latest/macros

This macro processes customer data through multiple stages including deriving columns, handling nulls, hashing, and ranking. It's useful for comprehensive data preparation pipelines.

```sql
WITH source_data AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment
    FROM "AUTOMATE_DV_TEST"."TEST"."CUSTOMER"
),
derived_columns AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    C_CUSTKEY AS CUSTOMER_ID,
    '1998-01-01' AS LOAD_DATETIME,
    'TPCH_CUSTOMER' AS RECORD_SOURCE
    FROM source_data
),
null_columns AS (
    SELECT
    c_custkey,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID AS CUSTOMER_ID_ORIGINAL,
        ISNULL(CUSTOMER_ID, '-1') AS CUSTOMER_ID,
    C_NAME AS C_NAME_ORIGINAL,
        ISNULL(C_NAME, '-2') AS C_NAME
    FROM derived_columns
),
hashed_columns AS (
    SELECT
    c_custkey,
    c_name,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    CUSTOMER_ID,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID_ORIGINAL,
    C_NAME_ORIGINAL,
    CONVERT(BINARY(16), HASHBYTES('MD5', NULLIF(UPPER(TRIM(CAST(C_CUSTKEY AS VARCHAR(MAX)))), '')), 2) AS CUSTOMER_HK
    FROM null_columns
),
ranked_columns AS (
    SELECT *,
    RANK() OVER (PARTITION BY CUSTOMER_HK ORDER BY LOAD_DATETIME) AS AUTOMATE_DV_RANK
    FROM hashed_columns
),
columns_to_select AS (
    SELECT
    c_custkey,
    c_address,
    c_nationkey,
    c_phone,
    c_acctbal,
    c_mktsegment,
    c_comment,
    CUSTOMER_ID,
    LOAD_DATETIME,
    RECORD_SOURCE,
    CUSTOMER_ID_ORIGINAL,
    C_NAME,
    C_NAME_ORIGINAL,
    CUSTOMER_HK,
    AUTOMATE_DV_RANK
    FROM ranked_columns
)
SELECT * FROM columns_to_select

```

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

### Create a Reference Table Model

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_ref_tables

Use this template to create a new Reference Table model. Define parameters like `src_pk`, `src_extra_columns`, `src_ldts`, `src_source`, and `source_model` to configure the table.

```sql
{{ 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) }}
```

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

### Hub Model with Metadata Configuration

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_hubs

Configure a Hub model by defining source model and key columns. The `incremental` materialization is recommended for Hubs.

```sql
{{ config(materialized='incremental')    }}

{%- set source_model = "v_stg_orders"   -%}
{%- set src_pk = "CUSTOMER_HK"          -%}
{%- set src_nk = "CUSTOMER_ID"          -%}
{%- set src_ldts = "LOAD_DATETIME"      -%}
{%- set src_source = "RECORD_SOURCE"    -%}

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

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

### Define Stage Layer Column for Multiple Satellite Names

Source: https://automate-dv.readthedocs.io/en/latest/metadata

Example of mapping multiple stage layer columns to different `SATELLITE_NAME`s when tracking multiple satellites. This is part of the `derived_columns` configuration.

```jinja
...
derived_columns:
    SATELLITE_NAME_1: "!SAT_CUSTOMER_DETAILS"
    SATELLITE_NAME_2: "!SAT_ORDER_DETAILS"
...

```

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

### Generate Hub with YAML Metadata

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_hubs

This snippet demonstrates generating a Hub entity using metadata defined in a YAML string. The `fromyaml` function parses the string into a dictionary, which is then passed to the `automate_dv.hub` macro. This approach is useful for managing metadata separately.

```sql
{%- set yaml_metadata -%}
source_model: 
    - v_stg_orders_web
    - v_stg_orders_crm
    - v_stg_orders_sap
src_pk: CUSTOMER_HK
src_nk: CUSTOMER_ID
src_ldts: LOAD_DATETIME
src_source: 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_ldts"],
                   source_model=metadata_dict["source_model"]) }}

```

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

### Basic Hub Model Template

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_hubs

This is the basic template for creating a Hub model. It uses the `hub` macro with essential parameters.

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

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

### Complete Effectivity Satellite Model Configuration

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_eff_satellites

A complete dbt model for an effectivity satellite, including materialization and all necessary metadata parameters for the `eff_sat` macro. Use this as a starting point for your own effectivity satellites.

```sql
{{ config(materialized='incremental')  }}

{%- set source_model = "v_stg_orders" -%}
{%- set src_pk = "CUSTOMER_NATION_HK" -%}
{%- set src_dfk = "CUSTOMER_HK"       -%}
{%- set src_sfk = "NATION_HK"         -%}
{%- 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"  -%}

{{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk,
                       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) }}
```

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

### Stage with Minimal Source Model Configuration

Source: https://automate-dv.readthedocs.io/en/latest/metadata

This snippet demonstrates configuring only the source model for a staging process. Use when no other metadata transformations are needed.

```yaml
{%- set yaml_metadata -%}
source_model: raw_source
{%- endset -%}

{% set metadata_dict = fromyaml(yaml_metadata) %}

{% set source_model = metadata_dict["source_model"] %}

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

```

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

### Idempotent Load Logic with LEFT OUTER JOIN

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Example incremental logic demonstrating how to ensure idempotent loads in custom models using LEFT OUTER JOIN. This prevents duplicate records from being loaded.

```sql
{%- if is_incremental() %}
LEFT JOIN {{ this }} AS d
ON a.CUSTOMER_HK = d.CUSTOMER_HK
WHERE d.CUSTOMER_HK IS NULL
{%- endif %}

```

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

### Vault Insert By Period: Config Level with Date Range and Source Models

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Loads data between specified start and stop dates, overriding with provided date_source_models. Uses the config level for dbt Core compatibility.

```sql
{{ config(materialized='vault_insert_by_period', timestamp_field='LOAD_DATE', period='day',
start_date='2020-01-30', stop_date='2020-04-30', date_source_models=var('source_model')) }}

{{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk,
                       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) }}
```

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

### Configured As of Date Model with Metadata

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_as_of_date

An As of Date model configured with specific metadata for datepart, start_date, and end_date, and set to materialize as a table. This model generates a date spine for a specified period.

```sql
{{ config(materialized='table') }}

{%- set datepart = "day" -%}
{%- set start_date = "TO_DATE('2021/01/01', 'yyyy/mm/dd')" -%}
{%- set end_date = "TO_DATE('2021/04/01', 'yyyy/mm/dd')" -%}

WITH as_of_date AS (
    {{ dbt_utils.date_spine(datepart=datepart, 
                            start_date=start_date,
                            end_date=end_date) }}
)

SELECT DATE_{{datepart}} as AS_OF_DATE FROM as_of_date

```

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

### Directly Define Multiple Satellite Metadata

Source: https://automate-dv.readthedocs.io/en/latest/metadata

Configure metadata for multiple satellites by directly defining variables. This approach is useful for complex setups and utilizes the `xts` macro. Ensure correct dictionary structure for `src_satellite`.

```jinja
{%- set source_model = "v_stg_customer" -%}
{%- set src_pk = "CUSTOMER_HK" -%}
{%- set src_satellite = "SAT_CUSTOMER_DETAILS": {
                          "sat_name": {"SATELLITE_NAME": "SATELLITE_NAME_1"}, 
                          "hashdiff": {"HASHDIFF": "CUSTOMER_HASHDIFF"}
                        }, "SAT_ORDER_DETAILS": {
                          "sat_name": {"SATELLITE_NAME": "SATELLITE_NAME_2"},
                          "hashdiff": {"HASHDIFF": "ORDER_HASHDIFF"}
                        }} -%
{%- set src_ldts = "LOAD_DATETIME" -%}
{%- set src_source = "RECORD_SOURCE" -%}

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

```

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

### Set Load Datetime using run_started_at

Source: https://automate-dv.readthedocs.io/en/latest/best_practises/loading

Configure the `LOAD_DATETIME` using dbt's `run_started_at` variable, formatted to include microseconds. This is recommended for audit purposes when an ingestion tool's timestamp is not available.

```yaml
source_model: MY_STAGE
derived_columns:
  LOAD_DATETIME: TO_TIMESTAMP('{{ run_started_at.strftime("%Y-%m-%d %H:%M:%S.%f") }}')
```

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

### Vault Insert By Period: Meta Level with Date Range and Source Models

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Loads data between specified start and stop dates, overriding with provided date_source_models. Uses the meta level for dbt Core and Fusion compatibility.

```sql
{{ config(materialized='vault_insert_by_period',
          meta={'timestamp_field': 'LOAD_DATE', 'period': 'day',
                'start_date': '2020-01-30', 'stop_date': '2020-04-30',
                'date_source_models': var('source_model')}) }}

{{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk,
                       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) }}
```

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

### Single-Column Hashing with MD5

Source: https://automate-dv.readthedocs.io/en/latest/best_practises/hashing

Demonstrates the process of hashing a single column, including casting to VARCHAR, trimming whitespace, converting to uppercase, handling empty strings as NULL, and finally applying MD5_BINARY and casting to BINARY. This process is designed for consistent hashing across different inputs.

```sql
CAST((MD5_BINARY(NULLIF(UPPER(TRIM(CAST(BOOKING_REF AS VARCHAR))), ''))) AS BINARY(16)) AS BOOKING_HK
```

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

### Rank Column Creation (Ranked Columns Config)

Source: https://automate-dv.readthedocs.io/en/latest/materialisations

Configure rank columns using the `ranked_columns` setting within the stage macro. This method is recommended for its flexibility with derived or hashed columns.

```yaml
source_model: "MY_STAGE"
ranked_columns:
  AUTOMATE_DV_RANK:
    partition_by: "CUSTOMER_HK"
    order_by: "LOAD_DATETIME"
```

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

### Ranked Columns Configuration (Multi-Item)

Source: https://automate-dv.readthedocs.io/en/latest/macros/stage_macro_configurations

Configure ranked columns with multiple fields for 'partition_by' and 'order_by'. This allows for more granular ranking.

```yaml
source_model: MY_STAGE
ranked_columns:
  AUTOMATE_DV_RANK:
    partition_by: 
        - CUSTOMER_HK
        - CUSTOMER_REF
    order_by: 
        - RECORD_SOURCE
        - LOAD_DATETIME
  SAT_BOOKING_RANK:
    partition_by: BOOKING_HK
    order_by: LOAD_DATETIME
```

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

### Multi-Item Parameters - Ranked Columns Configuration

Source: https://automate-dv.readthedocs.io/en/latest/macros/stage_macro_configurations

Configures ranking for multiple items by specifying multiple fields in `partition_by` and `order_by`. Suitable for complex ranking logic.

```yaml
source_model: MY_STAGE
ranked_columns:
  AUTOMATE_DV_RANK:
    partition_by: 
      - CUSTOMER_HK
      - CUSTOMER_REF
    order_by: 
      - RECORD_SOURCE: DESC
      - LOAD_DATETIME: ASC
  SAT_BOOKING_RANK:
    partition_by: BOOKING_HK
    order_by: LOAD_DATETIME
```

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

### Define a Hub Macro with adapter.dispatch

Source: https://automate-dv.readthedocs.io/en/latest/extending

This macro uses `adapter.dispatch` to find platform-specific implementations. It defines the macro namespace as 'automate_dv'.

```sql
{%- macro hub(src_pk, src_nk, src_ldts, src_source, source_model) -%}

{{- adapter.dispatch('hub', 'automate_dv')(src_pk=src_pk, src_nk=src_nk,
                                           src_ldts=src_ldts, src_source=src_source,
                                           source_model=source_model) -}}

{%- endmacro -%}

```

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

### Ranked Columns Configuration (Single Item)

Source: https://automate-dv.readthedocs.io/en/latest/macros/stage_macro_configurations

Define ranked columns using RANK() window function. Specify 'partition_by' and 'order_by' for ranking logic.

```yaml
source_model: MY_STAGE
ranked_columns:
  AUTOMATE_DV_RANK:
    partition_by: CUSTOMER_HK
    order_by: LOAD_DATETIME
  SAT_BOOKING_RANK:
    partition_by: BOOKING_HK
    order_by: LOAD_DATETIME
```

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

### Load Incremental Satellite (Simplified)

Source: https://automate-dv.readthedocs.io/en/latest/macros

This SQL snippet shows a simplified method for loading an incremental satellite. It focuses on identifying new records by comparing the current HASHDIFF with the previous one.

```sql
WITH source_data AS (
    SELECT a.CUSTOMER_HK, a.HASHDIFF, a.CUSTOMER_NAME, a.CUSTOMER_ADDRESS, a.CUSTOMER_PHONE, a.ACCBAL, a.MKTSEGMENT, a.COMMENT, a.EFFECTIVE_FROM, a.LOAD_DATETIME, a.RECORD_SOURCE
    FROM `dbtvault-341416`.`dbtvault`.`stg_customer` AS a
    WHERE a.CUSTOMER_HK IS NOT NULL
),
first_record_in_set AS (
    SELECT
    sd.CUSTOMER_HK, sd.HASHDIFF, sd.CUSTOMER_NAME, sd.CUSTOMER_ADDRESS, sd.CUSTOMER_PHONE, sd.ACCBAL, sd.MKTSEGMENT, sd.COMMENT, sd.EFFECTIVE_FROM, sd.LOAD_DATETIME, sd.RECORD_SOURCE
    FROM source_data as sd
    QUALIFY ROW_NUMBER() OVER (PARTITION BY sd.CUSTOMER_HK ORDER BY sd.LOAD_DATETIME ASC) = 1
),
records_to_insert AS (
    SELECT frin.CUSTOMER_HK, frin.HASHDIFF, frin.CUSTOMER_NAME, frin.CUSTOMER_ADDRESS, frin.CUSTOMER_PHONE, frin.ACCBAL, frin.MKTSEGMENT, frin.COMMENT, frin.EFFECTIVE_FROM, frin.LOAD_DATETIME, frin.RECORD_SOURCE
    FROM first_record_in_set AS frin
    LEFT JOIN `dbtvault-341416`.`dbtvault`.`satellite_ghost_incremental` AS lr
        ON lr.CUSTOMER_HK = frin.CUSTOMER_HK
        AND lr.HASHDIFF = frin.HASHDIFF
        WHERE lr.HASHDIFF IS NULL
    UNION DISTINCT
    SELECT usr.CUSTOMER_HK, usr.HASHDIFF, usr.CUSTOMER_NAME, usr.CUSTOMER_ADDRESS, usr.CUSTOMER_PHONE, usr.ACCBAL, usr.MKTSEGMENT, usr.COMMENT, usr.EFFECTIVE_FROM, usr.LOAD_DATETIME, usr.RECORD_SOURCE
    FROM source_data as usr
    WHERE usr.HASHDIFF != LAG(usr.HASHDIFF) OVER (
        PARTITION BY usr.CUSTOMER_HK
        ORDER BY usr.LOAD_DATETIME ASC)
)
SELECT * FROM records_to_insert
```

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

### Generate Hub with Variable Metadata

Source: https://automate-dv.readthedocs.io/en/latest/tutorial/tut_hubs

Use this snippet to generate a Hub entity by directly passing metadata variables to the `automate_dv.hub` macro. Ensure all required source model and key variables are defined.

```sql
{{ config(materialized='incremental') }}

{%- set source_model = ["v_stg_orders_web", 
                        "v_stg_orders_crm", 
                        "v_stg_orders_sap"] -%}

{%- set src_pk = "CUSTOMER_HK" -%}
{%- set src_nk = "CUSTOMER_ID" -%}
{%- set src_ldts = "LOAD_DATETIME" -%}
{%- set src_source = "RECORD_SOURCE" -%}

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

```

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

### Basic Derived Column Configuration

Source: https://automate-dv.readthedocs.io/en/latest/macros/stage_macro_configurations

Illustrates the basic usage of the derived_columns configuration within the stage macro to create a new column, RECORD_SOURCE, with a static value.

```jinja-sql
{% set yaml_metadata %}
source_model: <your_raw_staging_model_name>
derived_columns:
  RECORD_SOURCE: "!WORDPRESS"
{% endset %}   

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ automate_dv.stage(include_source_columns=true,
                     source_model=metadata_dict['source_model'],
                     derived_columns=metadata_dict['derived_columns'],
                     null_columns=metadata_dict['null_columns'],
                     hashed_columns=metadata_dict['hashed_columns'],
                     ranked_columns=metadata_dict['ranked_columns']) }}
```