dbt/models/raw_vault/_client_h.yml
version: 2
models:
- name: client_h
description: >
Hub of Sunbelt Senior Insurance client numbers (increment 1 design D-3): one row per client key, with the
load timestamp and record source of the key's first arrival, plus the two ghost records.
columns:
- name: hk_client_h
tests:
# Layer 1
- unique:
name: uq_client_h_hk_client_h
config: {severity: warn}
- not_null:
name: nn_client_h_hk_client_h
config: {severity: warn}
# Layer 3: Permutation Coverage Method paths P1 to P8 (test plan, Pilot item 271).
# Expected hash keys are literals computed with datavault4dbt's own hash expression (guide G-8).
# Every staging expectation lists the two ghost records the stage adds (unknown -1, error -2).
# Engine note (2026-10-23; narrowed 2026-10-26): a unit test that fails on dbt v1 is tagged fusion_only; one that
# passes there carries no tag (re-checked on dbt 1.12.6). On dbt v1, a unit test replaces each
# input with a query block (for example __dbt__cte__sunbelt__client); datavault4dbt's macros describe their
# inputs to list columns (the stage its source, the hub its staging model), the describe fails on a query block,
# and the test errors with "'None' has no attribute 'database'". On the Fusion engine they pass. They run on the
# workstation's Fusion builds; v1 environments exclude them with: --exclude tag:fusion_only
unit_tests:
- name: unit_stg_sunbelt_client_p4_surface_variant_same_key
model: stg_sunbelt_client
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 4, client_number: 'A-1004', loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 37, client_number: 'a-1004', loaded_at: '2026-10-02T02:00:00'}
- {crm_row_id: 5, client_number: 'A-1005', loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 38, client_number: 'A-1005 ', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {crm_row_id: 4, hk_client_h: 'd451d5e07e1297d40e4e211e07d63c87'}
- {crm_row_id: 37, hk_client_h: 'd451d5e07e1297d40e4e211e07d63c87'}
- {crm_row_id: 5, hk_client_h: '97dcf5db15fab07747135a359e89eb79'}
- {crm_row_id: 38, hk_client_h: '97dcf5db15fab07747135a359e89eb79'}
- {crm_row_id: -1, hk_client_h: '00000000000000000000000000000000'}
- {crm_row_id: -2, hk_client_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_client_p5_internal_form_new_key
model: stg_sunbelt_client
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 7, client_number: 'A-1007', loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 39, client_number: 'A1007', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {crm_row_id: 7, hk_client_h: '6620e029877aae0b4a4cac7cc4dacce2'}
- {crm_row_id: 39, hk_client_h: '9b015df670245d76e164c5313771bad8'}
- {crm_row_id: -1, hk_client_h: '00000000000000000000000000000000'}
- {crm_row_id: -2, hk_client_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_client_p6_null_zero_key
model: stg_sunbelt_client
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 31, client_number: null, loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {crm_row_id: 31, hk_client_h: '00000000000000000000000000000000'}
- {crm_row_id: -1, hk_client_h: '00000000000000000000000000000000'}
- {crm_row_id: -2, hk_client_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_client_p7_blank_zero_key
model: stg_sunbelt_client
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 32, client_number: '', loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 41, client_number: ' ', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {crm_row_id: 32, hk_client_h: '00000000000000000000000000000000'}
- {crm_row_id: 41, hk_client_h: '00000000000000000000000000000000'}
- {crm_row_id: -1, hk_client_h: '00000000000000000000000000000000'}
- {crm_row_id: -2, hk_client_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_client_h_p1_new_key
model: client_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_client')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', client_number: 'A-1001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- input: ref('stg_sunbelt_policy_sale')
rows: []
expect:
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', client_number: 'A-1001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- name: unit_client_h_p2_repeat_keeps_first
model: client_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_client')
rows:
- {hk_client_h: 'a9e948587b971f1e9c3b104f4be78b81', client_number: 'A-1003', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- {hk_client_h: 'a9e948587b971f1e9c3b104f4be78b81', client_number: 'A-1003', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
- input: ref('stg_sunbelt_policy_sale')
rows: []
expect:
rows:
- {hk_client_h: 'a9e948587b971f1e9c3b104f4be78b81', ldts: '2026-10-01 02:00:00'}
- name: unit_client_h_p3_later_load_inserts
model: client_h
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_client')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', client_number: 'A-1001', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
- {hk_client_h: '210760676c52f84825af4c9a4c97b040', client_number: 'A-1031', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
- input: this
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', client_number: 'A-1001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- input: ref('stg_sunbelt_policy_sale')
rows: []
expect:
rows:
- {hk_client_h: '210760676c52f84825af4c9a4c97b040', client_number: 'A-1031', ldts: '2026-10-02 02:00:00'}
- name: unit_client_h_p8_duplicate_in_load_one_row
model: client_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_client')
rows:
- {hk_client_h: '854fb226c402caa6d757b70aba3330c2', client_number: 'A-1010', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- {hk_client_h: '854fb226c402caa6d757b70aba3330c2', client_number: 'a-1010', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- input: ref('stg_sunbelt_policy_sale')
rows: []
expect:
rows:
- {hk_client_h: '854fb226c402caa6d757b70aba3330c2', ldts: '2026-10-01 02:00:00'}
dbt/models/raw_vault/_increment_2.yml
version: 2
# Layer 3, test plan logic areas E, G, H, I, J, K, L and M. The toolkit's hub, link and satellite
# macros carry these behaviors, so these tests characterize them (TDD standard, section 2).
# Incremental tests give their new rows the load time 2099-01-01: the toolkit's satellite reads its high-water mark
# with a real query on the live table, not the mocked this (sat_v0, snowflake), so a fixture dated inside the loaded
# acts is filtered out once real data exists, and a no-new-row test would pass without proving anything.
# Layer 1, increment 2 (design section 8): every new hub and link, unique and not null on its hash key.
# Layer 1, D-40: each satellite's hash key to its parent, a relationships test. The links' hub keys are checked by the
# layer 4 assertion assert_increment_2_link_hub_keys_exist.
models:
- name: policy_h
columns:
- name: hk_policy_h
tests:
- unique:
name: uq_policy_h_hk_policy_h
config: {severity: warn}
- not_null:
name: nn_policy_h_hk_policy_h
config: {severity: warn}
- name: insured_h
columns:
- name: hk_insured_h
tests:
- unique:
name: uq_insured_h_hk_insured_h
config: {severity: warn}
- not_null:
name: nn_insured_h_hk_insured_h
config: {severity: warn}
- name: agent_h
columns:
- name: hk_agent_h
tests:
- unique:
name: uq_agent_h_hk_agent_h
config: {severity: warn}
- not_null:
name: nn_agent_h_hk_agent_h
config: {severity: warn}
- name: client_policy_l
columns:
- name: hk_client_policy_l
tests:
- unique:
name: uq_client_policy_l_hk_client_policy_l
config: {severity: warn}
- not_null:
name: nn_client_policy_l_hk_client_policy_l
config: {severity: warn}
- name: hk_client_h
tests:
- relationships:
name: rel_client_policy_l_hk_client_h
arguments:
to: ref('client_h')
field: hk_client_h
config: {severity: warn}
- name: hk_policy_h
tests:
- relationships:
name: rel_client_policy_l_hk_policy_h
arguments:
to: ref('policy_h')
field: hk_policy_h
config: {severity: warn}
- name: insured_policy_l
columns:
- name: hk_insured_policy_l
tests:
- unique:
name: uq_insured_policy_l_hk_insured_policy_l
config: {severity: warn}
- not_null:
name: nn_insured_policy_l_hk_insured_policy_l
config: {severity: warn}
- name: hk_insured_h
tests:
- relationships:
name: rel_insured_policy_l_hk_insured_h
arguments:
to: ref('insured_h')
field: hk_insured_h
config: {severity: warn}
- name: hk_policy_h
tests:
- relationships:
name: rel_insured_policy_l_hk_policy_h
arguments:
to: ref('policy_h')
field: hk_policy_h
config: {severity: warn}
- name: agent_policy_l
columns:
- name: hk_agent_policy_l
tests:
- unique:
name: uq_agent_policy_l_hk_agent_policy_l
config: {severity: warn}
- not_null:
name: nn_agent_policy_l_hk_agent_policy_l
config: {severity: warn}
- name: hk_agent_h
tests:
- relationships:
name: rel_agent_policy_l_hk_agent_h
arguments:
to: ref('agent_h')
field: hk_agent_h
config: {severity: warn}
- name: hk_policy_h
tests:
- relationships:
name: rel_agent_policy_l_hk_policy_h
arguments:
to: ref('policy_h')
field: hk_policy_h
config: {severity: warn}
- name: client_sunbelt_s
tests:
- dbt_utils.unique_combination_of_columns:
name: uq_client_sunbelt_s_pk
arguments:
combination_of_columns: [hk_client_h, ldts]
config: {severity: warn}
columns:
- name: ldts
tests:
- not_null:
name: nn_client_sunbelt_s_ldts
config: {severity: warn}
- name: hk_client_h
tests:
- not_null:
name: nn_client_sunbelt_s_hk_client_h
config: {severity: warn}
- relationships:
name: rel_client_sunbelt_s_hk_client_h
arguments:
to: ref('client_h')
field: hk_client_h
config: {severity: warn}
- name: client_policy_sunbelt_s
tests:
- dbt_utils.unique_combination_of_columns:
name: uq_client_policy_sunbelt_s_pk
arguments:
combination_of_columns: [hk_client_policy_l, ldts]
config: {severity: warn}
columns:
- name: ldts
tests:
- not_null:
name: nn_client_policy_sunbelt_s_ldts
config: {severity: warn}
- name: hk_client_policy_l
tests:
- not_null:
name: nn_client_policy_sunbelt_s_hk_client_policy_l
config: {severity: warn}
- relationships:
name: rel_client_policy_sunbelt_s_hk_client_policy_l
arguments:
to: ref('client_policy_l')
field: hk_client_policy_l
config: {severity: warn}
- name: insured_northfield_s
tests:
- dbt_utils.unique_combination_of_columns:
name: uq_insured_northfield_s_pk
arguments:
combination_of_columns: [hk_insured_h, ldts]
config: {severity: warn}
columns:
- name: ldts
tests:
- not_null:
name: nn_insured_northfield_s_ldts
config: {severity: warn}
- name: hk_insured_h
tests:
- not_null:
name: nn_insured_northfield_s_hk_insured_h
config: {severity: warn}
- relationships:
name: rel_insured_northfield_s_hk_insured_h
arguments:
to: ref('insured_h')
field: hk_insured_h
config: {severity: warn}
- name: policy_northfield_s
tests:
- dbt_utils.unique_combination_of_columns:
name: uq_policy_northfield_s_pk
arguments:
combination_of_columns: [hk_policy_h, ldts]
config: {severity: warn}
columns:
- name: ldts
tests:
- not_null:
name: nn_policy_northfield_s_ldts
config: {severity: warn}
- name: hk_policy_h
tests:
- not_null:
name: nn_policy_northfield_s_hk_policy_h
config: {severity: warn}
- relationships:
name: rel_policy_northfield_s_hk_policy_h
arguments:
to: ref('policy_h')
field: hk_policy_h
config: {severity: warn}
# Engine note (2026-10-26): a unit test that fails on dbt v1 is tagged fusion_only, for the reason in _client_h.yml;
# one that passes on dbt 1.12.6 carries no tag, so the v1 QA job runs it.
unit_tests:
- name: unit_policy_h_ph1_sales_only
model: policy_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_policy_h: '4421fbfe51494a436dddd51ad9fbdcea', carrier_code: 'NFL', policy_number: 'NF-100012', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows: []
expect:
rows:
- {hk_policy_h: '4421fbfe51494a436dddd51ad9fbdcea', rsrc: 'SSI'}
- name: unit_policy_h_ph2_carrier_only
model: policy_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows: []
- input: ref('stg_northfield_policy')
rows:
- {hk_policy_h: '0fbe3d46b048aff86aad058697c49c0d', carrier_code: 'NFL', policy_number: 'NF-100002', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_policy_h: '0fbe3d46b048aff86aad058697c49c0d', rsrc: 'NFL'}
- name: unit_policy_h_ph3_both_sources_same_load
model: policy_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', carrier_code: 'NFL', policy_number: 'NF-100001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', carrier_code: 'NFL', policy_number: 'NF-100001', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', ldts: '2026-10-01 02:00:00'}
- name: unit_policy_h_ph4_later_load_keeps_first
model: policy_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_policy_h: '6a6c0d4202959e1b979ed6240fb5bcf5', carrier_code: 'NFL', policy_number: 'NF-100011', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows:
- {hk_policy_h: '6a6c0d4202959e1b979ed6240fb5bcf5', carrier_code: 'NFL', policy_number: 'NF-100011', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_policy_h: '6a6c0d4202959e1b979ed6240fb5bcf5', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
- name: unit_client_h_ch1_sale_number_already_in_hub
model: client_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_client')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', client_number: 'A-1001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', client_number: 'A-1001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', ldts: '2026-10-01 02:00:00'}
- name: unit_client_h_ch2_sale_number_first_seen
model: client_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_client')
rows: []
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_h: 'd0bb749a44f95125e5c8335eb9426c40', client_number: 'A-1033', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_h: 'd0bb749a44f95125e5c8335eb9426c40', client_number: 'A-1033', ldts: '2026-10-02 02:00:00'}
- name: unit_insured_h_ih1_new_insured
model: insured_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_northfield_policy')
rows:
- {hk_insured_h: 'cf6c376d04392d59a92e2f9af65dac68', insured_number: 'NLI-55003', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_insured_h: 'cf6c376d04392d59a92e2f9af65dac68', insured_number: 'NLI-55003', ldts: '2026-10-01 02:00:00'}
- name: unit_insured_h_ih2_resend_no_new_row
model: insured_h
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_policy')
rows:
- {hk_insured_h: '4ac462dd38b65bb5f2ed0c5f77546e45', insured_number: 'NLI-55001', ldts: '2099-01-01 00:00:00', rsrc: 'NFL'}
- input: this
rows:
- {hk_insured_h: '4ac462dd38b65bb5f2ed0c5f77546e45', insured_number: 'NLI-55001', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows: []
- name: unit_insured_h_ih3_one_insured_two_policies
model: insured_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_northfield_policy')
rows:
- {hk_insured_h: '4ac462dd38b65bb5f2ed0c5f77546e45', insured_number: 'NLI-55001', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
- {hk_insured_h: '4ac462dd38b65bb5f2ed0c5f77546e45', insured_number: 'NLI-55001', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_insured_h: '4ac462dd38b65bb5f2ed0c5f77546e45'}
- name: unit_agent_h_ag1_both_sources_one_row
model: agent_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_agent_h: '233ba32975e37cf80441e2847eadef91', agent_npn: '17001001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows:
- {hk_agent_h: '233ba32975e37cf80441e2847eadef91', agent_npn: '17001001', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_agent_h: '233ba32975e37cf80441e2847eadef91', ldts: '2026-10-01 02:00:00'}
- name: unit_agent_h_ag2_carrier_only
model: agent_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows: []
- input: ref('stg_northfield_policy')
rows:
- {hk_agent_h: 'e6137991924f8e4f6df8d948789c1f8e', agent_npn: '17002001', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_agent_h: 'e6137991924f8e4f6df8d948789c1f8e', rsrc: 'NFL'}
- name: unit_agent_h_ag3_sales_only
model: agent_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_agent_h: 'c13cc1ef72792555af86fef17f6b2be4', agent_npn: '17001007', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows: []
expect:
rows:
- {hk_agent_h: 'c13cc1ef72792555af86fef17f6b2be4', rsrc: 'SSI'}
- name: unit_agent_h_ag4_one_agent_several_policies
model: agent_h
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_agent_h: '233ba32975e37cf80441e2847eadef91', agent_npn: '17001001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- {hk_agent_h: '233ba32975e37cf80441e2847eadef91', agent_npn: '17001001', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows: []
expect:
rows:
- {hk_agent_h: '233ba32975e37cf80441e2847eadef91'}
- name: unit_client_policy_l_l1_one_person_one_role
model: client_policy_l
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hk_policy_h: '3afc40c80305309aded589d6099568c3', role: 'owner', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', role: 'owner'}
- name: unit_client_policy_l_l2_two_people_two_roles
model: client_policy_l
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: '35070bf5206d1078eb34d43e979852b5', hk_client_h: 'f7698055a7796f4c6dd8411ac66b0df8', hk_policy_h: 'b4d7f4e1cc021fe406a4ad68216d4f16', role: 'owner', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- {hk_client_policy_l: '48c9d3c864d9fa7c29b2c9150582abeb', hk_client_h: 'c2df2cab17cb85cff2cadf5f3efa5a7f', hk_policy_h: 'b4d7f4e1cc021fe406a4ad68216d4f16', role: 'insured', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_h: 'f7698055a7796f4c6dd8411ac66b0df8', role: 'owner'}
- {hk_client_h: 'c2df2cab17cb85cff2cadf5f3efa5a7f', role: 'insured'}
- name: unit_client_policy_l_l3_one_person_two_roles
model: client_policy_l
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: '91610428e12b838c0e5a1e600f426146', hk_client_h: '854fb226c402caa6d757b70aba3330c2', hk_policy_h: '90e92ca73cd3ec3f2ea6ef07808fafb1', role: 'owner', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- {hk_client_policy_l: '01d5eaf067800197240ab2a34b2a2d41', hk_client_h: '854fb226c402caa6d757b70aba3330c2', hk_policy_h: '90e92ca73cd3ec3f2ea6ef07808fafb1', role: 'insured', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_policy_l: '91610428e12b838c0e5a1e600f426146', role: 'owner'}
- {hk_client_policy_l: '01d5eaf067800197240ab2a34b2a2d41', role: 'insured'}
- name: unit_client_policy_l_l5_undocumented_role_new_row
model: client_policy_l
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: 'c20b79ba279169d47a086f0c049b1ff4', hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hk_policy_h: '3afc40c80305309aded589d6099568c3', role: 'payor', ldts: '2099-01-01 00:00:00', rsrc: 'SSI'}
- input: this
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hk_policy_h: '3afc40c80305309aded589d6099568c3', role: 'owner', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_policy_l: 'c20b79ba279169d47a086f0c049b1ff4', role: 'payor'}
- name: unit_client_policy_l_l6_resend_no_new_row
model: client_policy_l
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hk_policy_h: '3afc40c80305309aded589d6099568c3', role: 'owner', ldts: '2099-01-01 00:00:00', rsrc: 'SSI'}
- input: this
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hk_policy_h: '3afc40c80305309aded589d6099568c3', role: 'owner', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows: []
- name: unit_insured_policy_l_l4_same_policy_two_role_keys
model: insured_policy_l
description: The policy key equals the one client_policy_l stores for A-1007 on NF-100007, and the person key is the insured's; nothing joins A-1007 to NLI-55007.
config:
tags: ['fusion_only']
given:
- input: ref('stg_northfield_policy')
rows:
- {hk_insured_policy_l: 'dd237da50cc05102da2b1b4c7fc36d2e', hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hk_policy_h: 'baf9908c5804664d4f4ca439c97ed621', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hk_policy_h: 'baf9908c5804664d4f4ca439c97ed621'}
- name: unit_agent_policy_l_la2_sources_disagree_two_rows
model: agent_policy_l
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_agent_policy_l: '3a6a1d9caf253a540830f1d78d74e5cb', hk_agent_h: '1646684df001e1f6e9950bbbcbe0afce', hk_policy_h: '6a6c0d4202959e1b979ed6240fb5bcf5', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows:
- {hk_agent_policy_l: 'd6dbc0e71fad14d3d03410bdb7d52144', hk_agent_h: '5323ec0c2a986419ec5b9ec151ee73aa', hk_policy_h: '6a6c0d4202959e1b979ed6240fb5bcf5', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_agent_h: '1646684df001e1f6e9950bbbcbe0afce', hk_policy_h: '6a6c0d4202959e1b979ed6240fb5bcf5'}
- {hk_agent_h: '5323ec0c2a986419ec5b9ec151ee73aa', hk_policy_h: '6a6c0d4202959e1b979ed6240fb5bcf5'}
- name: unit_agent_policy_l_la3_resend_no_new_row
model: agent_policy_l
config:
tags: ['fusion_only']
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_agent_policy_l: 'ad257663d1d908d4d78aa74032b2ae4c', hk_agent_h: '233ba32975e37cf80441e2847eadef91', hk_policy_h: '3afc40c80305309aded589d6099568c3', ldts: '2099-01-01 00:00:00', rsrc: 'SSI'}
- input: ref('stg_northfield_policy')
rows: []
- input: this
rows:
- {hk_agent_policy_l: 'ad257663d1d908d4d78aa74032b2ae4c', hk_agent_h: '233ba32975e37cf80441e2847eadef91', hk_policy_h: '3afc40c80305309aded589d6099568c3', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows: []
- name: unit_policy_northfield_s_st1_first_version
model: policy_northfield_s
config:
tags: ['fusion_only']
given:
- input: ref('stg_northfield_policy_known_policy')
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', hd_policy_northfield_s: 'bd4406bb651c5acd7da7aa07c01564ba', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', status: 'IF'}
- name: unit_policy_northfield_s_st2_unchanged_resend
model: policy_northfield_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_policy_known_policy')
rows:
- {hk_policy_h: '0fbe3d46b048aff86aad058697c49c0d', hd_policy_northfield_s: 'bd4406bb651c5acd7da7aa07c01564ba', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', ldts: '2099-01-01 00:00:00', rsrc: 'NFL'}
- input: this
rows:
- {hk_policy_h: '0fbe3d46b048aff86aad058697c49c0d', hd_policy_northfield_s: 'bd4406bb651c5acd7da7aa07c01564ba', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows: []
- name: unit_policy_northfield_s_st3_changed_value_new_version
model: policy_northfield_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_policy_known_policy')
rows:
- {hk_policy_h: 'b11eca74a12073083c635c5eaa17fca9', hd_policy_northfield_s: 'e2ab4a058b0ad60336834aec7774198b', face_amount: 25000, status: 'LP', effective_date: '2026-09-15', ldts: '2099-01-01 00:00:00', rsrc: 'NFL'}
- input: this
rows:
- {hk_policy_h: 'b11eca74a12073083c635c5eaa17fca9', hd_policy_northfield_s: 'bd4406bb651c5acd7da7aa07c01564ba', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_policy_h: 'b11eca74a12073083c635c5eaa17fca9', status: 'LP', ldts: '2099-01-01 00:00:00'}
- name: unit_policy_northfield_s_st5_absent_stays_current
model: policy_northfield_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_policy_known_policy')
rows:
- {hk_policy_h: '0fbe3d46b048aff86aad058697c49c0d', hd_policy_northfield_s: 'bd4406bb651c5acd7da7aa07c01564ba', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', ldts: '2099-01-01 00:00:00', rsrc: 'NFL'}
- input: this
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', hd_policy_northfield_s: 'bd4406bb651c5acd7da7aa07c01564ba', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
- {hk_policy_h: '0fbe3d46b048aff86aad058697c49c0d', hd_policy_northfield_s: 'bd4406bb651c5acd7da7aa07c01564ba', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows: []
# Layer 3, increment 2, added 2026-10-25 (Ray, decision 1a): logic areas P (staging change hashes), M extended to
# client_sunbelt_s and client_policy_sunbelt_s, and Q (version 1 views: load end dates). Change-hash literals computed
# with datavault4dbt's own expression and checked against all 81 live staged rows first (guide G-8).
- name: unit_stg_sunbelt_client_hd1_unchanged_same_hash
model: stg_sunbelt_client
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 1, client_number: 'A-1001', first_name: 'Rosa', last_name: 'Alvarez', state: 'FL', loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 1, client_number: 'A-1001', first_name: 'Rosa', last_name: 'Alvarez', state: 'FL', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401'}
- {hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401'}
- {hd_client_sunbelt_s: '00000000000000000000000000000000'}
- {hd_client_sunbelt_s: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_client_hd2_changed_value_new_hash
model: stg_sunbelt_client
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 1, client_number: 'A-1001', first_name: 'Rosa', last_name: 'Alvarez', state: 'FL', loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 34, client_number: 'A-1001', first_name: 'Rosa', last_name: 'Alvarez', state: 'GA', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401'}
- {hd_client_sunbelt_s: '3df756eb454e536f52a2b8c3f74298c5'}
- {hd_client_sunbelt_s: '00000000000000000000000000000000'}
- {hd_client_sunbelt_s: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_policy_sale_hd1_unchanged_same_hash
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 1, policy_number: 'NF-100001', client_number: 'A-1001', carrier_code: 'NFL', role: 'owner', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', agent_npn: '17001001', plan_name: 'Senior Term 20', loaded_at: '2026-10-01T02:00:00'}
- {sale_row_id: 12, policy_number: 'NF-100001', client_number: 'A-1001', carrier_code: 'NFL', role: 'owner', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', agent_npn: '17001001', plan_name: 'Senior Term 20', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305'}
- {hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305'}
- {hd_client_policy_sunbelt_s: '00000000000000000000000000000000'}
- {hd_client_policy_sunbelt_s: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_policy_sale_hd2_changed_value_new_hash
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 1, policy_number: 'NF-100001', client_number: 'A-1001', carrier_code: 'NFL', role: 'owner', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', agent_npn: '17001001', plan_name: 'Senior Term 20', loaded_at: '2026-10-01T02:00:00'}
- {sale_row_id: 12, policy_number: 'NF-100001', client_number: 'A-1001', carrier_code: 'NFL', role: 'owner', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', agent_npn: '17001001', plan_name: 'Final Expense 10K', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305'}
- {hd_client_policy_sunbelt_s: 'cbe24e2030372cc51108052ef39bac93'}
- {hd_client_policy_sunbelt_s: '00000000000000000000000000000000'}
- {hd_client_policy_sunbelt_s: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_northfield_policy_hd1_unchanged_same_hash
model: stg_northfield_policy
config:
tags: ['fusion_only']
given:
- input: source('northfield', 'policy')
rows:
- {policy_number: 'NF-100001', insured_id: 'NLI-55001', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', writing_agent_npn: '17001001', loaded_at: '2026-10-01T02:00:00'}
- {policy_number: 'NF-100001', insured_id: 'NLI-55001', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', writing_agent_npn: '17001001', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hd_policy_northfield_s: 'e6f14a2ee16022350f08582d4058dd38'}
- {hd_policy_northfield_s: 'e6f14a2ee16022350f08582d4058dd38'}
- {hd_policy_northfield_s: '00000000000000000000000000000000'}
- {hd_policy_northfield_s: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_northfield_policy_hd2_changed_value_new_hash
model: stg_northfield_policy
config:
tags: ['fusion_only']
given:
- input: source('northfield', 'policy')
rows:
- {policy_number: 'NF-100001', insured_id: 'NLI-55001', face_amount: 25000, status: 'IF', effective_date: '2026-09-15', writing_agent_npn: '17001001', loaded_at: '2026-10-01T02:00:00'}
- {policy_number: 'NF-100001', insured_id: 'NLI-55001', face_amount: 25000, status: 'LP', effective_date: '2026-09-15', writing_agent_npn: '17001001', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hd_policy_northfield_s: 'e6f14a2ee16022350f08582d4058dd38'}
- {hd_policy_northfield_s: '9485ee1686586ce7ecb7ef63abdaa1b0'}
- {hd_policy_northfield_s: '00000000000000000000000000000000'}
- {hd_policy_northfield_s: 'ffffffffffffffffffffffffffffffff'}
- name: unit_client_sunbelt_s_st1_first_version
model: client_sunbelt_s
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_client_known_client')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401', first_name: 'Rosa', last_name: 'Alvarez', state: 'FL', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401'}
- name: unit_client_sunbelt_s_st2_unchanged_resend
model: client_sunbelt_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_client_known_client')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401', first_name: 'Rosa', last_name: 'Alvarez', state: 'FL', ldts: '2099-01-01 00:00:00', rsrc: 'SSI'}
- input: this
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401', first_name: 'Rosa', last_name: 'Alvarez', state: 'FL', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows: []
- name: unit_client_sunbelt_s_st3_changed_value_new_version
model: client_sunbelt_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_client_known_client')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: '3df756eb454e536f52a2b8c3f74298c5', first_name: 'Rosa', last_name: 'Alvarez', state: 'GA', ldts: '2099-01-01 00:00:00', rsrc: 'SSI'}
- input: this
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401', first_name: 'Rosa', last_name: 'Alvarez', state: 'FL', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', state: 'GA', ldts: '2099-01-01 00:00:00'}
- name: unit_client_policy_sunbelt_s_st1_first_version
model: client_policy_sunbelt_s
config:
tags: ['fusion_only']
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', plan_name: 'Senior Term 20', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305'}
- name: unit_client_policy_sunbelt_s_st2_unchanged_resend
model: client_policy_sunbelt_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', plan_name: 'Senior Term 20', ldts: '2099-01-01 00:00:00', rsrc: 'SSI'}
- input: this
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', plan_name: 'Senior Term 20', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows: []
- name: unit_client_policy_sunbelt_s_st3_changed_value_new_version
model: client_policy_sunbelt_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'cbe24e2030372cc51108052ef39bac93', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', plan_name: 'Final Expense 10K', ldts: '2099-01-01 00:00:00', rsrc: 'SSI'}
- input: this
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305', sold_on: '2026-09-02', agent_name: 'Dana Whitaker', plan_name: 'Senior Term 20', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', plan_name: 'Final Expense 10K', ldts: '2099-01-01 00:00:00'}
- name: unit_client_sunbelt_s1_v1_single_version_current
model: client_sunbelt_s1
config:
tags: ['fusion_only']
given:
- input: ref('client_sunbelt_s')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', ldts: '2026-10-01 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_client_sunbelt_s1_v2_earlier_version_end_dated
model: client_sunbelt_s1
config:
tags: ['fusion_only']
given:
- input: ref('client_sunbelt_s')
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: 'a2c7bc57dd55c23e984c5f8ac50b2401', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', hd_client_sunbelt_s: '3df756eb454e536f52a2b8c3f74298c5', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', ldts: '2026-10-01 02:00:00', ledts: '2026-10-02 01:59:59.999999'}
- {hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04', ldts: '2026-10-02 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_client_policy_sunbelt_s1_v1_single_version_current
model: client_policy_sunbelt_s1
config:
tags: ['fusion_only']
given:
- input: ref('client_policy_sunbelt_s')
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', ldts: '2026-10-01 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_client_policy_sunbelt_s1_v2_earlier_version_end_dated
model: client_policy_sunbelt_s1
config:
tags: ['fusion_only']
given:
- input: ref('client_policy_sunbelt_s')
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'dff4ea605651138b0ff6c84f6dab8305', ldts: '2026-10-01 02:00:00', rsrc: 'SSI'}
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', hd_client_policy_sunbelt_s: 'cbe24e2030372cc51108052ef39bac93', ldts: '2026-10-02 02:00:00', rsrc: 'SSI'}
expect:
rows:
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', ldts: '2026-10-01 02:00:00', ledts: '2026-10-02 01:59:59.999999'}
- {hk_client_policy_l: '1399b6288e1f6004208ce689b209ba91', ldts: '2026-10-02 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_insured_northfield_s1_v1_single_version_current
model: insured_northfield_s1
config:
tags: ['fusion_only']
given:
- input: ref('insured_northfield_s')
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', ldts: '2026-10-01 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_insured_northfield_s1_v2_earlier_version_end_dated
model: insured_northfield_s1
config:
tags: ['fusion_only']
given:
- input: ref('insured_northfield_s')
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '39271be97cd6c2aaaa73dbaaaefdb649', ldts: '2026-10-02 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', ldts: '2026-10-01 02:00:00', ledts: '2026-10-02 01:59:59.999999'}
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', ldts: '2026-10-02 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_policy_northfield_s1_v1_single_version_current
model: policy_northfield_s1
config:
tags: ['fusion_only']
given:
- input: ref('policy_northfield_s')
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', hd_policy_northfield_s: 'e6f14a2ee16022350f08582d4058dd38', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', ldts: '2026-10-01 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_policy_northfield_s1_v2_earlier_version_end_dated
model: policy_northfield_s1
config:
tags: ['fusion_only']
given:
- input: ref('policy_northfield_s')
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', hd_policy_northfield_s: 'e6f14a2ee16022350f08582d4058dd38', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', hd_policy_northfield_s: '9485ee1686586ce7ecb7ef63abdaa1b0', ldts: '2026-10-02 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', ldts: '2026-10-01 02:00:00', ledts: '2026-10-02 01:59:59.999999'}
- {hk_policy_h: '3afc40c80305309aded589d6099568c3', ldts: '2026-10-02 02:00:00', ledts: '8888-12-31 23:59:59'}
- name: unit_insured_northfield_s_st1_first_version
model: insured_northfield_s
config:
tags: ['fusion_only']
given:
- input: ref('stg_northfield_policy_known_insured')
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020', insured_name: 'Lowe, Martin', birth_date: '1947-01-30', address: '12 Elm St, Flint MI', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020'}
- name: unit_insured_northfield_s_st2_unchanged_resend
model: insured_northfield_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_policy_known_insured')
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020', insured_name: 'Lowe, Martin', birth_date: '1947-01-30', address: '12 Elm St, Flint MI', ldts: '2099-01-01 00:00:00', rsrc: 'NFL'}
- input: this
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020', insured_name: 'Lowe, Martin', birth_date: '1947-01-30', address: '12 Elm St, Flint MI', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows: []
- name: unit_insured_northfield_s_st3_case_only_new_version
model: insured_northfield_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_policy_known_insured')
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '39271be97cd6c2aaaa73dbaaaefdb649', insured_name: 'Lowe, Martin', birth_date: '1947-01-30', address: '12 ELM ST, FLINT MI', ldts: '2099-01-01 00:00:00', rsrc: 'NFL'}
- input: this
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020', insured_name: 'Lowe, Martin', birth_date: '1947-01-30', address: '12 Elm St, Flint MI', ldts: '2026-10-01 02:00:00', rsrc: 'NFL'}
expect:
rows:
- {hk_insured_h: '8f1097a8e919696c8288f8ca5de44f62', address: '12 ELM ST, FLINT MI', ldts: '2099-01-01 00:00:00'}
dbt/models/raw_vault/_organization.yml
version: 2
# Layer 1, test plan 2.3.1 paths OK1 to OK4 (guide step 59), severity warn.
models:
- name: organization_h
columns:
- name: hk_organization_h
tests:
- unique:
name: uq_organization_h_hk_organization_h
config:
severity: warn
- not_null:
name: nn_organization_h_hk_organization_h
config:
severity: warn
- name: organization_northfield_s
tests:
- dbt_utils.unique_combination_of_columns:
name: uq_organization_northfield_s_pk
arguments:
combination_of_columns:
- hk_organization_h
- ldts
config:
severity: warn
columns:
- name: hk_organization_h
tests:
- not_null:
name: nn_organization_northfield_s_hk_organization_h
config:
severity: warn
- relationships:
name: rel_organization_northfield_s_hk_organization_h
arguments:
to: ref('organization_h')
field: hk_organization_h
config:
severity: warn
- name: ldts
tests:
- not_null:
name: nn_organization_northfield_s_ldts
config:
severity: warn
- name: organization_pinecrest_s
tests:
- dbt_utils.unique_combination_of_columns:
name: uq_organization_pinecrest_s_pk
arguments:
combination_of_columns:
- hk_organization_h
- ldts
config:
severity: warn
columns:
- name: hk_organization_h
tests:
- not_null:
name: nn_organization_pinecrest_s_hk_organization_h
config:
severity: warn
- relationships:
name: rel_organization_pinecrest_s_hk_organization_h
arguments:
to: ref('organization_h')
field: hk_organization_h
config:
severity: warn
- name: ldts
tests:
- not_null:
name: nn_organization_pinecrest_s_ldts
config:
severity: warn
unit_tests:
- name: unit_organization_h_oh1_new_key
model: organization_h
config:
tags:
- fusion_only
given:
- input: ref('stg_northfield_organization')
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_code: NFL
ldts: '2026-10-01 02:00:00'
rsrc: NFL
- input: ref('stg_pinecrest_organization')
rows: []
expect:
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_code: NFL
ldts: '2026-10-01 02:00:00'
rsrc: NFL
- name: unit_organization_h_oh2_repeat_keeps_first
model: organization_h
config:
tags:
- fusion_only
given:
- input: ref('stg_northfield_organization')
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_code: NFL
ldts: '2026-10-01 02:00:00'
rsrc: NFL
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_code: NFL
ldts: '2026-10-02 02:00:00'
rsrc: NFL
- input: ref('stg_pinecrest_organization')
rows: []
expect:
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
ldts: '2026-10-01 02:00:00'
- name: unit_organization_h_oh3_later_load_inserts
model: organization_h
config:
tags:
- fusion_only # dbt's latest engine cannot supply the hub's existing table as a fixture; runs under Fusion in development
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_organization')
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_code: NFL
ldts: '2026-10-02 02:00:00'
rsrc: NFL
- input: ref('stg_pinecrest_organization')
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
carrier_code: PMC
ldts: '2026-10-02 02:00:00'
rsrc: PMC
- input: this
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_code: NFL
ldts: '2026-10-01 02:00:00'
rsrc: NFL
expect:
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
carrier_code: PMC
ldts: '2026-10-02 02:00:00'
rsrc: PMC
- name: unit_organization_h_oh4_both_carrier_feeds
model: organization_h
config:
tags:
- fusion_only
given:
- input: ref('stg_northfield_organization')
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_code: NFL
ldts: '2026-10-02 02:00:00'
rsrc: NFL
- input: ref('stg_pinecrest_organization')
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
carrier_code: PMC
ldts: '2026-10-02 02:00:00'
rsrc: PMC
expect:
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
rsrc: NFL
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
rsrc: PMC
- name: unit_organization_northfield_s_ot1_first_version
model: organization_northfield_s
config:
tags:
- fusion_only
given:
- input: ref('stg_northfield_organization')
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
hd_organization_northfield_s: ccb0afb295bb639fa3c3a7ed24dec33d
carrier_name: Northfield Life
ldts: '2026-10-01 02:00:00'
rsrc: NFL
expect:
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_name: Northfield Life
ldts: '2026-10-01 02:00:00'
- name: unit_organization_northfield_s_ot2_unchanged_resend
model: organization_northfield_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_organization')
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
hd_organization_northfield_s: ccb0afb295bb639fa3c3a7ed24dec33d
carrier_name: Northfield Life
ldts: '2099-01-01 00:00:00'
rsrc: NFL
- input: this
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
hd_organization_northfield_s: ccb0afb295bb639fa3c3a7ed24dec33d
carrier_name: Northfield Life
ldts: '2026-10-01 02:00:00'
rsrc: NFL
expect:
rows: []
- name: unit_organization_northfield_s_ot3_changed_name_new_version
model: organization_northfield_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_northfield_organization')
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
hd_organization_northfield_s: 27c5a51e425637a848b2ce4394e03d74
carrier_name: Northfield Life Insurance Company
ldts: '2099-01-01 00:00:00'
rsrc: NFL
- input: this
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
hd_organization_northfield_s: ccb0afb295bb639fa3c3a7ed24dec33d
carrier_name: Northfield Life
ldts: '2026-10-01 02:00:00'
rsrc: NFL
expect:
rows:
- hk_organization_h: d17b6314d3888f183e3cb2c52c745d98
carrier_name: Northfield Life Insurance Company
ldts: '2099-01-01 00:00:00'
- name: unit_organization_pinecrest_s_ot1_first_version
model: organization_pinecrest_s
config:
tags:
- fusion_only
given:
- input: ref('stg_pinecrest_organization')
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
hd_organization_pinecrest_s: ce37930b9bd5a6b153bb685ee3198f0e
carrier_name: Pinecrest Mutual
ldts: '2026-10-01 02:00:00'
rsrc: PMC
expect:
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
carrier_name: Pinecrest Mutual
ldts: '2026-10-01 02:00:00'
- name: unit_organization_pinecrest_s_ot2_unchanged_resend
model: organization_pinecrest_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_pinecrest_organization')
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
hd_organization_pinecrest_s: ce37930b9bd5a6b153bb685ee3198f0e
carrier_name: Pinecrest Mutual
ldts: '2099-01-01 00:00:00'
rsrc: PMC
- input: this
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
hd_organization_pinecrest_s: ce37930b9bd5a6b153bb685ee3198f0e
carrier_name: Pinecrest Mutual
ldts: '2026-10-01 02:00:00'
rsrc: PMC
expect:
rows: []
- name: unit_organization_pinecrest_s_ot3_changed_name_new_version
model: organization_pinecrest_s
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_pinecrest_organization')
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
hd_organization_pinecrest_s: 791c8481bddf3bf9564b98f8d995083a
carrier_name: Pinecrest Mutual Insurance Company
ldts: '2099-01-01 00:00:00'
rsrc: PMC
- input: this
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
hd_organization_pinecrest_s: ce37930b9bd5a6b153bb685ee3198f0e
carrier_name: Pinecrest Mutual
ldts: '2026-10-01 02:00:00'
rsrc: PMC
expect:
rows:
- hk_organization_h: 03823cbeec4e049070eceeafa45b3560
carrier_name: Pinecrest Mutual Insurance Company
ldts: '2099-01-01 00:00:00'
dbt/models/raw_vault/agent_h.sql
{# Agent hub (design D-35; guide step 28): keyed by the national producer number, fed by both sources. #}
{{ datavault4dbt.hub(
hashkey='hk_agent_h',
business_keys=['agent_npn'],
source_models=[
{'name': 'stg_sunbelt_policy_sale', 'rsrc_static': 'SSI'},
{'name': 'stg_northfield_policy', 'rsrc_static': 'NFL'}
]
) }}
dbt/models/raw_vault/agent_policy_l.sql
{# Agent-policy link (design D-36; guide step 29): fed by both sources; disagreeing sources are kept as two rows. #}
{{ datavault4dbt.link(
link_hashkey='hk_agent_policy_l',
foreign_hashkeys=['hk_agent_h', 'hk_policy_h'],
source_models=[
{'name': 'stg_sunbelt_policy_sale', 'rsrc_static': 'SSI'},
{'name': 'stg_northfield_policy', 'rsrc_static': 'NFL'}
]
) }}
dbt/models/raw_vault/client_h.sql
{#
Hub of Sunbelt Senior client numbers (design D-3, D-32; guide steps 9 and 28): one row per client key, keeping
the first sighting. Two sources, the client list and the sales data; both carry SSI, so no rsrc_static and no
high-water mark (guide G-14).
#}
{{ datavault4dbt.hub(
hashkey='hk_client_h',
business_keys=['client_number'],
source_models=[
{'name': 'stg_sunbelt_client'},
{'name': 'stg_sunbelt_policy_sale'}
]
) }}
dbt/models/raw_vault/client_policy_l.sql
{# Client-policy link (design D-33; guide step 29): the role is a dependent child key. #}
{{ datavault4dbt.link(
link_hashkey='hk_client_policy_l',
foreign_hashkeys=['hk_client_h', 'hk_policy_h', 'role'],
source_models='stg_sunbelt_policy_sale'
) }}
dbt/models/raw_vault/client_policy_sunbelt_s.sql
{# Satellite (design D-34; guide step 30): the sale's details as Sunbelt Senior keeps them. #}
{{ datavault4dbt.sat_v0(
parent_hashkey='hk_client_policy_l',
src_hashdiff='hd_client_policy_sunbelt_s',
src_payload=['sold_on', 'agent_name', 'plan_name'],
source_model='stg_sunbelt_policy_sale'
) }}
dbt/models/raw_vault/client_policy_sunbelt_s1.sql
{# Version 1 view of client_policy_sunbelt_s (guide step 30, G-15): adds the load end date. #}
{{ config(materialized='view') }}
{{ datavault4dbt.sat_v1(
sat_v0='client_policy_sunbelt_s',
hashkey='hk_client_policy_l',
hashdiff='hd_client_policy_sunbelt_s'
) }}
dbt/models/raw_vault/client_sunbelt_s.sql
{# Satellite (design D-34; guide step 30): the client's details as Sunbelt Senior keeps them. #}
{{ datavault4dbt.sat_v0(
parent_hashkey='hk_client_h',
src_hashdiff='hd_client_sunbelt_s',
src_payload=['first_name', 'last_name', 'state'],
source_model='stg_sunbelt_client_known_client'
) }}
dbt/models/raw_vault/client_sunbelt_s1.sql
{# Version 1 view of client_sunbelt_s (guide step 30, G-15): adds the load end date. #}
{{ config(materialized='view') }}
{{ datavault4dbt.sat_v1(
sat_v0='client_sunbelt_s',
hashkey='hk_client_h',
hashdiff='hd_client_sunbelt_s'
) }}
dbt/models/raw_vault/insured_h.sql
{# Insured hub (design D-32; guide step 28): one row per Northfield Life insured number. #}
{{ datavault4dbt.hub(
hashkey='hk_insured_h',
business_keys=['insured_number'],
source_models='stg_northfield_policy'
) }}
dbt/models/raw_vault/insured_northfield_s.sql
{# Satellite (design D-34; guide step 30): the insured's details as Northfield Life keeps them. #}
{{ datavault4dbt.sat_v0(
parent_hashkey='hk_insured_h',
src_hashdiff='hd_insured_northfield_s',
src_payload=['insured_name', 'birth_date', 'address'],
source_model='stg_northfield_policy_known_insured'
) }}
dbt/models/raw_vault/insured_northfield_s1.sql
{# Version 1 view of insured_northfield_s (guide step 30, G-15): adds the load end date. #}
{{ config(materialized='view') }}
{{ datavault4dbt.sat_v1(
sat_v0='insured_northfield_s',
hashkey='hk_insured_h',
hashdiff='hd_insured_northfield_s'
) }}
dbt/models/raw_vault/insured_policy_l.sql
{# Insured-policy link (design D-33; guide step 29): Northfield Life's insured on its policy. #}
{{ datavault4dbt.link(
link_hashkey='hk_insured_policy_l',
foreign_hashkeys=['hk_insured_h', 'hk_policy_h'],
source_models='stg_northfield_policy'
) }}
dbt/models/raw_vault/organization_h.sql
{# Organization hub (mart design D-11; guide step 58): keyed by the carrier code, fed by each carrier's own record. #}
{{ datavault4dbt.hub(
hashkey='hk_organization_h',
business_keys=['carrier_code'],
source_models=[
{'name': 'stg_northfield_organization', 'rsrc_static': 'NFL'},
{'name': 'stg_pinecrest_organization', 'rsrc_static': 'PMC'}
]
) }}
dbt/models/raw_vault/organization_northfield_s.sql
{# Satellite (mart design D-11; guide step 59): the carrier's name, as Northfield Life sends it. #}
{{ datavault4dbt.sat_v0(
parent_hashkey='hk_organization_h',
src_hashdiff='hd_organization_northfield_s',
src_payload=['carrier_name'],
source_model='stg_northfield_organization'
) }}
dbt/models/raw_vault/organization_northfield_s1.sql
{# Version 1 view of organization_northfield_s (guide step 59, G-15): adds the load end date. #}
{{ config(materialized='view') }}
{{ datavault4dbt.sat_v1(
sat_v0='organization_northfield_s',
hashkey='hk_organization_h',
hashdiff='hd_organization_northfield_s'
) }}
dbt/models/raw_vault/organization_pinecrest_s.sql
{# Satellite (mart design D-11; guide step 59): the carrier's name, as Pinecrest Mutual sends it. #}
{{ datavault4dbt.sat_v0(
parent_hashkey='hk_organization_h',
src_hashdiff='hd_organization_pinecrest_s',
src_payload=['carrier_name'],
source_model='stg_pinecrest_organization'
) }}
dbt/models/raw_vault/organization_pinecrest_s1.sql
{# Version 1 view of organization_pinecrest_s (guide step 59, G-15): adds the load end date. #}
{{ config(materialized='view') }}
{{ datavault4dbt.sat_v1(
sat_v0='organization_pinecrest_s',
hashkey='hk_organization_h',
hashdiff='hd_organization_pinecrest_s'
) }}
dbt/models/raw_vault/policy_h.sql
{# Policy hub (design D-26; guide step 28): keyed by the issuing carrier's code and the policy number, two sources. #}
{{ datavault4dbt.hub(
hashkey='hk_policy_h',
business_keys=['carrier_code', 'policy_number'],
source_models=[
{'name': 'stg_sunbelt_policy_sale', 'rsrc_static': 'SSI'},
{'name': 'stg_northfield_policy', 'rsrc_static': 'NFL'}
]
) }}
dbt/models/raw_vault/policy_northfield_s.sql
{# Satellite (design D-34; guide step 30): the policy's details as Northfield Life keeps them. #}
{{ datavault4dbt.sat_v0(
parent_hashkey='hk_policy_h',
src_hashdiff='hd_policy_northfield_s',
src_payload=['face_amount', 'status', 'effective_date'],
source_model='stg_northfield_policy_known_policy'
) }}
dbt/models/raw_vault/policy_northfield_s1.sql
{# Version 1 view of policy_northfield_s (guide step 30, G-15): adds the load end date. #}
{{ config(materialized='view') }}
{{ datavault4dbt.sat_v1(
sat_v0='policy_northfield_s',
hashkey='hk_policy_h',
hashdiff='hd_policy_northfield_s'
) }}
dbt/models/staging/_stg_northfield_organization.yml
version: 2
# Layer 3, test plan 2.3.1, logic area R (path OS1); mart design D-11; profile P-21 (guide step 56).
# Expected hashes computed with the toolkit's own hash macro (G-8), 2026-11-01.
unit_tests:
- name: unit_stg_northfield_organization_os1_arrival_code_and_name
model: stg_northfield_organization
config:
tags: ['fusion_only']
given:
- input: source('northfield', 'organization')
rows:
- {carrier_code: 'NFL', carrier_name: 'Northfield Life', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hk_organization_h: 'd17b6314d3888f183e3cb2c52c745d98', hd_organization_northfield_s: 'ccb0afb295bb639fa3c3a7ed24dec33d'}
- {hk_organization_h: '00000000000000000000000000000000', hd_organization_northfield_s: '00000000000000000000000000000000'}
- {hk_organization_h: 'ffffffffffffffffffffffffffffffff', hd_organization_northfield_s: 'ffffffffffffffffffffffffffffffff'}
dbt/models/staging/_stg_northfield_policy.yml
version: 2
# Layer 3, test plan logic areas H and M (paths IH4 and ST4). ST4 is tested here, where the case-sensitive
# change hash is computed; the satellite only compares the hashes it is given.
unit_tests:
- name: unit_stg_northfield_policy_ih4_empty_insured_zero_key
model: stg_northfield_policy
config:
tags: ['fusion_only']
given:
- input: source('northfield', 'policy')
rows:
- {policy_number: 'NF-100013', insured_id: '', loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {hk_insured_h: '00000000000000000000000000000000'}
- {hk_insured_h: '00000000000000000000000000000000'}
- {hk_insured_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_northfield_policy_st4_case_only_new_hashdiff
model: stg_northfield_policy
config:
tags: ['fusion_only']
given:
- input: source('northfield', 'policy')
rows:
- {policy_number: 'NF-100008', insured_id: 'NLI-55008', insured_name: 'Lowe, Martin', birth_date: '1947-01-30', address: '12 Elm St, Flint MI', loaded_at: '2026-10-01T02:00:00'}
- {policy_number: 'NF-100008', insured_id: 'NLI-55008', insured_name: 'Lowe, Martin', birth_date: '1947-01-30', address: '12 ELM ST, FLINT MI', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {address: '12 Elm St, Flint MI', hd_insured_northfield_s: '00afbeadc7f25ecacde88a7e84af9020'}
- {address: '12 ELM ST, FLINT MI', hd_insured_northfield_s: '39271be97cd6c2aaaa73dbaaaefdb649'}
- {address: '(unknown)', hd_insured_northfield_s: '00000000000000000000000000000000'}
- {address: '(error)', hd_insured_northfield_s: 'ffffffffffffffffffffffffffffffff'}
dbt/models/staging/_stg_pinecrest_organization.yml
version: 2
# Layer 3, test plan 2.3.1, logic area R (path OS1); mart design D-11; profile P-21 (guide step 56).
# Expected hashes computed with the toolkit's own hash macro (G-8), 2026-11-01.
unit_tests:
- name: unit_stg_pinecrest_organization_os1_arrival_code_and_name
model: stg_pinecrest_organization
config:
tags: ['fusion_only']
given:
- input: source('pinecrest', 'organization')
rows:
- {carrier_code: 'PMC', carrier_name: 'Pinecrest Mutual', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {hk_organization_h: '03823cbeec4e049070eceeafa45b3560', hd_organization_pinecrest_s: 'ce37930b9bd5a6b153bb685ee3198f0e'}
- {hk_organization_h: '00000000000000000000000000000000', hd_organization_pinecrest_s: '00000000000000000000000000000000'}
- {hk_organization_h: 'ffffffffffffffffffffffffffffffff', hd_organization_pinecrest_s: 'ffffffffffffffffffffffffffffffff'}
dbt/models/staging/_stg_sunbelt_policy_sale.yml
version: 2
# Layer 3, test plan logic areas F, G and K (paths PH5 to PH8, CH3, AG5). Expected hash literals are computed
# with the toolkit's own standardization (guide G-8). Every staging expectation lists the two ghost records.
unit_tests:
- name: unit_stg_sunbelt_policy_sale_ph5_case_variant_same_key
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 1, policy_number: 'NF-100001', carrier_code: 'NFL', loaded_at: '2026-10-01T02:00:00'}
- {sale_row_id: 12, policy_number: 'nf-100001', carrier_code: 'NFL', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {sale_row_id: 1, hk_policy_h: '3afc40c80305309aded589d6099568c3'}
- {sale_row_id: 12, hk_policy_h: '3afc40c80305309aded589d6099568c3'}
- {sale_row_id: -1, hk_policy_h: '00000000000000000000000000000000'}
- {sale_row_id: -2, hk_policy_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_policy_sale_ph6_internal_form_new_key
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 13, policy_number: 'NF100009', carrier_code: 'NFL', loaded_at: '2026-10-02T02:00:00'}
- {sale_row_id: 99, policy_number: 'NF-100009', carrier_code: 'NFL', loaded_at: '2026-10-02T02:00:00'}
expect:
rows:
- {sale_row_id: 13, hk_policy_h: '3150d64383e2f4c875796d8c1da5c8e8'}
- {sale_row_id: 99, hk_policy_h: '12d26c525e80544eb7dd0dd2c75d64cf'}
- {sale_row_id: -1, hk_policy_h: '00000000000000000000000000000000'}
- {sale_row_id: -2, hk_policy_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_policy_sale_ph7_both_parts_missing_zero_key
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 8, policy_number: '', carrier_code: null, loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {sale_row_id: 8, hk_policy_h: '00000000000000000000000000000000'}
- {sale_row_id: -1, hk_policy_h: '00000000000000000000000000000000'}
- {sale_row_id: -2, hk_policy_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_policy_sale_ph8_one_part_missing_placeholder_key
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 2, policy_number: 'NF-100005', carrier_code: null, loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {sale_row_id: 2, hk_policy_h: 'd391ab28fdaf5ec47a993b23b29eb5c6'}
- {sale_row_id: -1, hk_policy_h: '00000000000000000000000000000000'}
- {sale_row_id: -2, hk_policy_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_policy_sale_ch3_empty_client_zero_key
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 9, policy_number: 'NF-100014', client_number: '', carrier_code: 'NFL', loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {sale_row_id: 9, hk_client_h: '00000000000000000000000000000000'}
- {sale_row_id: -1, hk_client_h: '00000000000000000000000000000000'}
- {sale_row_id: -2, hk_client_h: 'ffffffffffffffffffffffffffffffff'}
- name: unit_stg_sunbelt_policy_sale_ag5_empty_agent_zero_key
model: stg_sunbelt_policy_sale
config:
tags: ['fusion_only']
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 9, policy_number: 'NF-100014', agent_npn: '', carrier_code: 'NFL', loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {sale_row_id: 9, hk_agent_h: '00000000000000000000000000000000'}
- {sale_row_id: -1, hk_agent_h: '00000000000000000000000000000000'}
- {sale_row_id: -2, hk_agent_h: 'ffffffffffffffffffffffffffffffff'}
dbt/models/staging/_sunbelt__sources.yml
version: 2
sources:
- name: sunbelt
description: >
Source data of Sunbelt Senior Insurance, an acquired agency: its client list and its policy sales.
Synthetic data specified by the branch profile (Pilot item 267), landed by the land_act macro.
database: "{{ env_var('DBT_LANDING_STORAGE', 'LANDING') }}"
schema: SUNBELT
tables:
- name: client
identifier: CLIENT
description: Client rows as Sunbelt Senior Insurance's CRM delivered them; grain is one row per CRM row per load.
tests:
# Layer 2 (deviation DEV-2: dbt_utils instead of dbt_expectations)
- dbt_utils.unique_combination_of_columns:
name: uqc_sunbelt_client_loaded_at_crm_row_id
arguments:
combination_of_columns: ['LOADED_AT', 'CRM_ROW_ID']
config:
severity: warn
columns:
- name: CRM_ROW_ID
tests:
# Layer 1
- not_null:
name: nn_sunbelt_client_crm_row_id
config:
severity: warn
- name: LOADED_AT
tests:
- not_null:
name: nn_sunbelt_client_loaded_at
config:
severity: warn
- name: policy_sale
identifier: POLICY_SALE
description: Sunbelt Senior's policy sales; grain is one row per policy, client and role per load (profile 2.1.3).
tests:
# Layer 2, increment 2 (design section 8): the grain of profile 2.1.3
- dbt_utils.unique_combination_of_columns:
name: uqc_sunbelt_policy_sale_loaded_at_policy_client_role
arguments:
combination_of_columns: ['LOADED_AT', 'POLICY_NUMBER', 'CLIENT_NUMBER', 'ROLE']
config:
severity: warn
columns:
- name: LOADED_AT
tests:
# Layer 1, increment 2
- not_null:
name: nn_sunbelt_policy_sale_loaded_at
config:
severity: warn
- name: northfield
description: >
Source data of Northfield Life, a carrier: one row per policy with its insured inline (profile 2.1.4).
database: "{{ env_var('DBT_LANDING_STORAGE', 'LANDING') }}"
schema: NORTHFIELD
tables:
- name: policy
identifier: POLICY
description: Northfield Life's policy data; grain is one row per policy per load.
tests:
# Layer 2, increment 2 (design section 8): the grain of profile 2.1.4
- dbt_utils.unique_combination_of_columns:
name: uqc_northfield_policy_loaded_at_policy_number
arguments:
combination_of_columns: ['LOADED_AT', 'POLICY_NUMBER']
config:
severity: warn
columns:
- name: LOADED_AT
tests:
# Layer 1, increment 2
- not_null:
name: nn_northfield_policy_loaded_at
config:
severity: warn
- name: organization
identifier: ORGANIZATION
description: Northfield Life's own organization record, carrier code and name; grain is one row per carrier code per load (mart design D-11; profile P-21).
tests:
# Layer 2 (test plan 2.3.1, path OL1): one record per carrier code in each load
- dbt_utils.unique_combination_of_columns:
name: uqc_northfield_organization_loaded_at_carrier_code
arguments:
combination_of_columns: ['LOADED_AT', 'CARRIER_CODE']
config:
severity: warn
columns:
- name: CARRIER_NAME
tests:
# Layer 1 (test plan 2.3.1, path OL2): no carrier known by a code alone
- not_null:
name: nn_northfield_organization_carrier_name
config:
severity: warn
- name: pinecrest
description: >
Source data of Pinecrest Mutual, a carrier. On this branch only its organization record (guide step 55); Lab 1-2 adds
its table policy on Ray's branch, and the two merge without conflict.
database: "{{ env_var('DBT_LANDING_STORAGE', 'LANDING') }}"
schema: PINECREST
tables:
- name: organization
identifier: ORGANIZATION
description: Pinecrest Mutual's own organization record, carrier code and name; grain is one row per carrier code per load (mart design D-11; profile P-21).
tests:
# Layer 2 (test plan 2.3.1, path OL1): one record per carrier code in each load
- dbt_utils.unique_combination_of_columns:
name: uqc_pinecrest_organization_loaded_at_carrier_code
arguments:
combination_of_columns: ['LOADED_AT', 'CARRIER_CODE']
config:
severity: warn
columns:
- name: CARRIER_NAME
tests:
# Layer 1 (test plan 2.3.1, path OL2): no carrier known by a code alone
- not_null:
name: nn_pinecrest_organization_carrier_name
config:
severity: warn
dbt/models/staging/stg_northfield_organization.sql
{#
Staging view for Northfield Life's own organization record (mart design D-11; profile P-21; guide step 57).
Hard rule: an empty or whitespace-only carrier code becomes NULL before hashing (design D-24's rule).
#}
{{ datavault4dbt.stage(
source_model={'northfield': 'organization'},
ldts='TO_TIMESTAMP_NTZ(LOADED_AT)',
rsrc='!NFL',
derived_columns={
'carrier_code': {'value': "NULLIF(TRIM(carrier_code), '')", 'datatype': 'VARCHAR(16)', 'src_cols_required': 'carrier_code'}
},
hashed_columns={
'hk_organization_h': ['carrier_code'],
'hd_organization_northfield_s': {'is_hashdiff': true, 'columns': ['carrier_name']}
}
) }}
dbt/models/staging/stg_northfield_policy.sql
{#
Staging view for Northfield Life's policy data (design D-23, D-24, D-26, D-32, D-34, D-35; guide step 27).
D-24 hard rules: empty or whitespace-only insured, agent and policy numbers become NULL before hashing (unit test IH4
red 2026-10-25 01:12 UTC on beb49c9 without them, green with them). NFL is supplied as the carrier code (D-26).
#}
{{ datavault4dbt.stage(
source_model={'northfield': 'policy'},
ldts='TO_TIMESTAMP_NTZ(LOADED_AT)',
rsrc='!NFL',
derived_columns={
'carrier_code': {'value': "'NFL'", 'datatype': 'VARCHAR(8)'},
'insured_number': {'value': "NULLIF(TRIM(insured_id), '')", 'datatype': 'VARCHAR(16)'},
'agent_npn': {'value': "NULLIF(TRIM(writing_agent_npn), '')", 'datatype': 'VARCHAR(10)'},
'policy_number': {'value': "NULLIF(TRIM(policy_number), '')", 'datatype': 'VARCHAR(32)', 'src_cols_required': 'policy_number'}
},
hashed_columns={
'hk_policy_h': ['carrier_code', 'policy_number'],
'hk_insured_h': ['insured_number'],
'hk_agent_h': ['agent_npn'],
'hk_insured_policy_l': ['insured_number', 'carrier_code', 'policy_number'],
'hk_agent_policy_l': ['agent_npn', 'carrier_code', 'policy_number'],
'hd_insured_northfield_s': {'is_hashdiff': true, 'columns': ['insured_name', 'birth_date', 'address']},
'hd_policy_northfield_s': {'is_hashdiff': true, 'columns': ['face_amount', 'status', 'effective_date']}
}
) }}
dbt/models/staging/stg_northfield_policy_known_insured.sql
{#
The rows of stg_northfield_policy whose insured id is known: the source of satellite insured_northfield_s (fix to release 1, 2026-10-28).
A row without an insured id describes no one: staging maps it to the zero key, and a satellite holding it would
record one false entity for every such row. It stays out of the satellite and is raised as a finding by the
missing-key inspector, so nothing is lost silently (Code Review Standard, k12ki19). A view, like the staging it filters (a unit test can mock only a model the database holds).
#}
{{ config(materialized='view') }}
SELECT * FROM {{ ref('stg_northfield_policy') }} WHERE insured_number IS NOT NULL
dbt/models/staging/stg_northfield_policy_known_policy.sql
{#
The rows of stg_northfield_policy whose policy number is known: the source of satellite policy_northfield_s (fix to release 1, 2026-10-28).
A row without a policy number describes no one: staging maps it to the zero key, and a satellite holding it would
record one false entity for every such row. It stays out of the satellite and is raised as a finding by the
missing-key inspector, so nothing is lost silently (Code Review Standard, k12ki19). A view, like the staging it filters (a unit test can mock only a model the database holds).
#}
{{ config(materialized='view') }}
SELECT * FROM {{ ref('stg_northfield_policy') }} WHERE policy_number IS NOT NULL
dbt/models/staging/stg_pinecrest_organization.sql
{#
Staging view for Pinecrest Mutual's own organization record (mart design D-11; profile P-21; guide step 57).
Hard rule: an empty or whitespace-only carrier code becomes NULL before hashing (design D-24's rule).
#}
{{ datavault4dbt.stage(
source_model={'pinecrest': 'organization'},
ldts='TO_TIMESTAMP_NTZ(LOADED_AT)',
rsrc='!PMC',
derived_columns={
'carrier_code': {'value': "NULLIF(TRIM(carrier_code), '')", 'datatype': 'VARCHAR(16)', 'src_cols_required': 'carrier_code'}
},
hashed_columns={
'hk_organization_h': ['carrier_code'],
'hd_organization_pinecrest_s': {'is_hashdiff': true, 'columns': ['carrier_name']}
}
) }}
dbt/models/staging/stg_sunbelt_client.sql
{#
Staging view for Sunbelt Senior Insurance's client file (increment 1 guide step 8).
Hard rules, none of which changes what a value means:
D-5 LOADED_AT, delivered as text, is cast to a timestamp without time zone meaning UTC.
D-15 every row's record source is the static code SSI.
D-4 an empty or whitespace-only client number becomes NULL before hashing, so it takes the
all-zeros key instead of hashing as a value (unit test P7: red 2026-10-23 02:11 UTC on
d4236cf, the toolkit hashed '' and ' ' to 9d4568c0..., then green after this rule).
The derived column overwrites client_number; datavault4dbt computes derived columns before
hashed columns (macros/staging/snowflake/stage.sql, CTE order read 2026-10-22). Because an
overwrite removes the source column from the stage's input, the derived column declares it
with src_cols_required (without it the compiled SQL referenced a missing column).
Increment 2 (design D-34, guide step 27): the change hash of the client's satellite, client_sunbelt_s.
#}
{{ datavault4dbt.stage(
source_model={'sunbelt': 'client'},
ldts='TO_TIMESTAMP_NTZ(LOADED_AT)',
rsrc='!SSI',
derived_columns={
'client_number': {
'value': "NULLIF(TRIM(client_number), '')",
'datatype': 'VARCHAR(32)',
'src_cols_required': 'client_number'
}
},
hashed_columns={
'hk_client_h': ['client_number'],
'hd_client_sunbelt_s': {'is_hashdiff': true, 'columns': ['first_name', 'last_name', 'state']}
}
) }}
dbt/models/staging/stg_sunbelt_client_known_client.sql
{#
The rows of stg_sunbelt_client whose client number is known: the source of satellite client_sunbelt_s (fix to release 1, 2026-10-28).
A row without a client number describes no one: staging maps it to the zero key, and a satellite holding it would
record one false entity for every such row. It stays out of the satellite and is raised as a finding by the
missing-key inspector, so nothing is lost silently (Code Review Standard, k12ki19). A view, like the staging it filters (a unit test can mock only a model the database holds).
#}
{{ config(materialized='view') }}
SELECT * FROM {{ ref('stg_sunbelt_client') }} WHERE client_number IS NOT NULL
dbt/models/staging/stg_sunbelt_policy_sale.sql
{#
Staging view for Sunbelt Senior's policy sales data (design D-24, D-26, D-33, D-35, D-36; guide step 26).
D-24 hard rules: empty or whitespace-only policy numbers, carrier codes, client numbers and agent numbers become
NULL before hashing (unit tests PH7, CH3 and AG5 red 2026-10-25 01:12 UTC on beb49c9 without them, green with them).
Each overwrite declares src_cols_required (course record item 274, convention 5).
#}
{{ datavault4dbt.stage(
source_model={'sunbelt': 'policy_sale'},
ldts='TO_TIMESTAMP_NTZ(LOADED_AT)',
rsrc='!SSI',
derived_columns={
'policy_number': {'value': "NULLIF(TRIM(policy_number), '')", 'datatype': 'VARCHAR(32)', 'src_cols_required': 'policy_number'},
'carrier_code': {'value': "NULLIF(TRIM(carrier_code), '')", 'datatype': 'VARCHAR(8)', 'src_cols_required': 'carrier_code'},
'client_number': {'value': "NULLIF(TRIM(client_number), '')", 'datatype': 'VARCHAR(32)', 'src_cols_required': 'client_number'},
'agent_npn': {'value': "NULLIF(TRIM(agent_npn), '')", 'datatype': 'VARCHAR(10)', 'src_cols_required': 'agent_npn'}
},
hashed_columns={
'hk_policy_h': ['carrier_code', 'policy_number'],
'hk_client_h': ['client_number'],
'hk_agent_h': ['agent_npn'],
'hk_client_policy_l': ['client_number', 'carrier_code', 'policy_number', 'role'],
'hk_agent_policy_l': ['agent_npn', 'carrier_code', 'policy_number'],
'hd_client_policy_sunbelt_s': {'is_hashdiff': true, 'columns': ['sold_on', 'agent_name', 'plan_name']}
}
) }}