DV2.1 pattern library with templates for Hub, Satellite, Link, DC Satellite, MA Satellite and Reference Table. Based on automate_dv macros for dbt on Azure SQL.
Trigger phrases:
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: 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: 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: 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>
HASH(FK ^^ DCK1 ^^ DCK2)src_cdk (child-defined key) in the automate_dv configEnsure the source system block exists under raw_vault: