dbt/models/auditors/_auditors.yml
version: 2
# Layer 3 unit tests for the layer 6 auditor (test plan paths A1, A2). A1 carries a hub key from another source
# (k9): since increment 2, client_h also reads the sales data, so the hub may hold keys the client list never sent.
unit_tests:
- name: unit_auditor_sunbelt_client_hub_agree_no_finding
model: auditor_sunbelt_client_hub
given:
- input: ref('stg_sunbelt_client')
rows:
- {client_number: 'A-1001', hk_client_h: 'k1'}
- {client_number: 'A-1002', hk_client_h: 'k2'}
- {client_number: 'a-1002', hk_client_h: 'k2'}
- {client_number: '(unknown)', hk_client_h: '00000000000000000000000000000000'}
- input: ref('client_h')
rows:
- {hk_client_h: 'k1'}
- {hk_client_h: 'k2'}
- {hk_client_h: 'k9'}
- {hk_client_h: '00000000000000000000000000000000'}
- {hk_client_h: 'ffffffffffffffffffffffffffffffff'}
expect:
rows: []
- name: unit_auditor_sunbelt_client_hub_disagree_one_finding
model: auditor_sunbelt_client_hub
given:
- input: ref('stg_sunbelt_client')
rows:
- {client_number: 'A-1001', hk_client_h: 'k1'}
- {client_number: 'A-1002', hk_client_h: 'k2'}
- {client_number: 'A-1003', hk_client_h: 'k3'}
- input: ref('client_h')
rows:
- {hk_client_h: 'k1'}
- {hk_client_h: 'k2'}
- {hk_client_h: '00000000000000000000000000000000'}
- {hk_client_h: 'ffffffffffffffffffffffffffffffff'}
expect:
rows:
- {finder_type: 'audit', column_value: '3 source keys, 2 found in the hub'}
# Layer 3, test plan logic area O (paths A3 to A6): every usable key of each new source table is in the policy hub,
# whichever record source the hub kept (a key both sources send in one load is kept under one of them, path PH3).
- name: unit_auditor_sunbelt_policy_hub_agree_no_finding
model: auditor_sunbelt_policy_hub
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_policy_h: 'p1'}
- {hk_policy_h: 'p2'}
- {hk_policy_h: 'p3'}
- {hk_policy_h: '00000000000000000000000000000000'}
- input: ref('policy_h')
rows:
- {hk_policy_h: 'p1', rsrc: 'SSI'}
- {hk_policy_h: 'p2', rsrc: 'SSI'}
- {hk_policy_h: 'p3', rsrc: 'SSI'}
- {hk_policy_h: '00000000000000000000000000000000', rsrc: 'SYSTEM'}
expect:
rows: []
- name: unit_auditor_sunbelt_policy_hub_disagree_one_finding
model: auditor_sunbelt_policy_hub
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {hk_policy_h: 'p1'}
- {hk_policy_h: 'p2'}
- {hk_policy_h: 'p3'}
- input: ref('policy_h')
rows:
- {hk_policy_h: 'p1', rsrc: 'SSI'}
- {hk_policy_h: 'p2', rsrc: 'NFL'}
expect:
rows:
- {finder_type: 'audit', column_value: '3 source keys, 2 found in the hub'}
- name: unit_auditor_northfield_policy_hub_agree_no_finding
model: auditor_northfield_policy_hub
given:
- input: ref('stg_northfield_policy')
rows:
- {hk_policy_h: 'p1'}
- {hk_policy_h: 'p2'}
- {hk_policy_h: 'p3'}
- input: ref('policy_h')
rows:
- {hk_policy_h: 'p1', rsrc: 'NFL'}
- {hk_policy_h: 'p2', rsrc: 'NFL'}
- {hk_policy_h: 'p3', rsrc: 'SSI'}
expect:
rows: []
- name: unit_auditor_northfield_policy_hub_disagree_one_finding
model: auditor_northfield_policy_hub
given:
- input: ref('stg_northfield_policy')
rows:
- {hk_policy_h: 'p1'}
- {hk_policy_h: 'p2'}
- {hk_policy_h: 'p3'}
- input: ref('policy_h')
rows:
- {hk_policy_h: 'p1', rsrc: 'NFL'}
- {hk_policy_h: 'p4', rsrc: 'SSI'}
expect:
rows:
- {finder_type: 'audit', column_value: '3 source keys, 1 found in the hub'}
dbt/models/auditors/auditor_northfield_policy_hub.sql
{#
Layer 6 auditor (design D-10; guide step 31). Compares one system against another: every distinct usable policy key staged from NORTHFIELD.POLICY must be in
the policy hub, whichever record source the hub kept (path PH3: a key both sources send in one load is kept
under one of them). A first version compared the hub rows of its own record source and raised a false
finding at act 1 (2026-10-25); reduced to unit tests A4 to A6, red, then fixed.
Its disagree test was red 2026-10-25 01:12 UTC on beb49c9 before the comparison, green with it. Writes one summary finding, carrying both counts, only when they disagree.
Pattern: a reconciliation writes at most one finding.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH source_keys AS (
SELECT DISTINCT hk_policy_h
FROM {{ ref('stg_northfield_policy') }}
WHERE hk_policy_h NOT IN ('00000000000000000000000000000000', 'ffffffffffffffffffffffffffffffff')
),
counts AS (
SELECT COUNT(*) AS n, COUNT(h.hk_policy_h) AS found
FROM source_keys k
LEFT JOIN (SELECT DISTINCT hk_policy_h FROM {{ ref('policy_h') }}) h ON h.hk_policy_h = k.hk_policy_h
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'auditor_northfield_policy_hub'", "'NORTHFIELD'", "'NORTHFIELD.POLICY'"]) }} AS finding_pk,
'NORTHFIELD' AS source_system,
'NORTHFIELD.POLICY' AS source_table,
'POLICY_NUMBER' AS source_column,
c.n || ' source keys, ' || c.found || ' found in the hub' AS column_value,
'auditor_northfield_policy_hub' AS finder_name,
'audit' AS finder_type,
'Not every policy key of NORTHFIELD.POLICY is in the policy hub: ' || c.n || ' distinct usable keys staged, ' || c.found || ' found in policy_h.' AS detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM counts c
WHERE c.n <> c.found
dbt/models/auditors/auditor_sunbelt_client_hub.sql
{#
Layer 6 auditor (design D-10). Compares one system against another: every distinct usable client key staged
from the client list must be in client_h. Since increment 2 the hub also reads the sales data (design D-32), so
it may hold keys the client list never sent; a first version compared counts and raised a false finding at
increment 2's act 2 (2026-10-25), reduced to unit tests A1 and A2, red, then fixed.
Writes one summary finding, carrying both counts, only when a key is missing.
Pattern: a reconciliation writes at most one finding.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH source_keys AS (
SELECT DISTINCT hk_client_h
FROM {{ ref('stg_sunbelt_client') }}
WHERE client_number IS NOT NULL
AND hk_client_h NOT IN ('00000000000000000000000000000000', 'ffffffffffffffffffffffffffffffff')
),
counts AS (
SELECT COUNT(*) AS n, COUNT(h.hk_client_h) AS found
FROM source_keys k
LEFT JOIN (SELECT DISTINCT hk_client_h FROM {{ ref('client_h') }}) h ON h.hk_client_h = k.hk_client_h
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'auditor_sunbelt_client_hub'", "'SUNBELT'", "'SUNBELT.CLIENT'"]) }} AS finding_pk,
'SUNBELT' AS source_system,
'SUNBELT.CLIENT' AS source_table,
'CLIENT_NUMBER' AS source_column,
c.n || ' source keys, ' || c.found || ' found in the hub' AS column_value,
'auditor_sunbelt_client_hub' AS finder_name,
'audit' AS finder_type,
'Not every client key of SUNBELT.CLIENT is in client_h: ' || c.n || ' distinct usable keys staged, ' || c.found || ' found in client_h.' AS detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM counts c
WHERE c.n <> c.found
dbt/models/auditors/auditor_sunbelt_policy_hub.sql
{#
Layer 6 auditor (design D-10; guide step 31). Compares one system against another: every distinct usable policy key staged from SUNBELT.POLICY_SALE must be in
the policy hub, whichever record source the hub kept (path PH3: a key both sources send in one load is kept
under one of them). A first version compared the hub rows of its own record source and raised a false
finding at act 1 (2026-10-25); reduced to unit tests A4 to A6, red, then fixed.
Its disagree test was red 2026-10-25 01:12 UTC on beb49c9 before the comparison, green with it. Writes one summary finding, carrying both counts, only when they disagree.
Pattern: a reconciliation writes at most one finding.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH source_keys AS (
SELECT DISTINCT hk_policy_h
FROM {{ ref('stg_sunbelt_policy_sale') }}
WHERE hk_policy_h NOT IN ('00000000000000000000000000000000', 'ffffffffffffffffffffffffffffffff')
),
counts AS (
SELECT COUNT(*) AS n, COUNT(h.hk_policy_h) AS found
FROM source_keys k
LEFT JOIN (SELECT DISTINCT hk_policy_h FROM {{ ref('policy_h') }}) h ON h.hk_policy_h = k.hk_policy_h
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'auditor_sunbelt_policy_hub'", "'SUNBELT'", "'SUNBELT.POLICY_SALE'"]) }} AS finding_pk,
'SUNBELT' AS source_system,
'SUNBELT.POLICY_SALE' AS source_table,
'POLICY_NUMBER' AS source_column,
c.n || ' source keys, ' || c.found || ' found in the hub' AS column_value,
'auditor_sunbelt_policy_hub' AS finder_name,
'audit' AS finder_type,
'Not every policy key of SUNBELT.POLICY_SALE is in the policy hub: ' || c.n || ' distinct usable keys staged, ' || c.found || ' found in policy_h.' AS detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM counts c
WHERE c.n <> c.found
dbt/models/inspectors/_inspectors.yml
version: 2
# Layer 3 unit tests for the layer 5 inspector (test plan paths I1, I2). New model, no prior
# behavior to characterize, so the tests are written with the model and expected green.
unit_tests:
- name: unit_inspector_sunbelt_client_number_form_conforming_no_finding
model: inspector_sunbelt_client_number_form
given:
- input: ref('stg_sunbelt_client')
rows:
- {client_number: 'A-1001', hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04'}
- {client_number: 'a-1004', hk_client_h: 'd451d5e07e1297d40e4e211e07d63c87'}
- {client_number: '(unknown)', hk_client_h: '00000000000000000000000000000000'}
expect:
rows: []
- name: unit_inspector_sunbelt_client_number_form_nonconforming_one_finding_each
model: inspector_sunbelt_client_number_form
given:
- input: ref('stg_sunbelt_client')
rows:
- {client_number: 'A-1001', hk_client_h: '2eec4d22ff4ec7df061cc883ac8d1b04'}
- {client_number: 'A1007', hk_client_h: '9b015df670245d76e164c5313771bad8'}
- {client_number: 'A-01008', hk_client_h: 'x'}
expect:
rows:
- {column_value: 'A1007', finder_type: 'inspection'}
- {column_value: 'A-01008', finder_type: 'inspection'}
# Layer 3, test plan logic area N (paths I3, I4).
- name: unit_inspector_sunbelt_policy_number_form_conforming_no_finding
model: inspector_sunbelt_policy_number_form
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {policy_number: 'NF-100001', hk_policy_h: 'p1'}
- {policy_number: 'nf-100001', hk_policy_h: 'p1'}
expect:
rows: []
- name: unit_inspector_sunbelt_policy_number_form_nonconforming_one_finding
model: inspector_sunbelt_policy_number_form
given:
- input: ref('stg_sunbelt_policy_sale')
rows:
- {policy_number: 'NF-100009', hk_policy_h: 'p9'}
- {policy_number: 'NF100009', hk_policy_h: 'p9x'}
expect:
rows:
- {finder_type: 'inspection', column_value: 'NF100009'}
# Layer 3, the missing-key inspectors (fix to release 1, 2026-10-28): every key present raises nothing; each
# missing key raises one finding per row, named so the row can be found in landing storage.
- name: unit_inspector_sunbelt_client_key_missing_none_missing
model: inspector_sunbelt_client_key_missing
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 1, client_number: 'A-1001', loaded_at: '2026-10-01T02:00:00'}
expect:
rows: []
- name: unit_inspector_sunbelt_client_key_missing_one_finding_per_row
model: inspector_sunbelt_client_key_missing
given:
- input: source('sunbelt', 'client')
rows:
- {crm_row_id: 1, client_number: 'A-1001', loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 31, client_number: null, loaded_at: '2026-10-01T02:00:00'}
- {crm_row_id: 32, client_number: ' ', loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {source_column: 'CLIENT_NUMBER', column_value: 'CRM row 31, loaded 2026-10-01'}
- {source_column: 'CLIENT_NUMBER', column_value: 'CRM row 32, loaded 2026-10-01'}
- name: unit_inspector_sunbelt_policy_sale_key_missing_none_missing
model: inspector_sunbelt_policy_sale_key_missing
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 1, policy_number: 'NF-100001', client_number: 'A-1001', agent_npn: '17001001', loaded_at: '2026-10-01T02:00:00'}
expect:
rows: []
- name: unit_inspector_sunbelt_policy_sale_key_missing_one_finding_per_missing_key
model: inspector_sunbelt_policy_sale_key_missing
given:
- input: source('sunbelt', 'policy_sale')
rows:
- {sale_row_id: 7, policy_number: null, client_number: 'A-1013', agent_npn: '17001006', loaded_at: '2026-10-01T02:00:00'}
- {sale_row_id: 8, policy_number: 'NF-100014', client_number: '', agent_npn: null, loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {source_column: 'POLICY_NUMBER', column_value: 'Sale row 7, loaded 2026-10-01'}
- {source_column: 'CLIENT_NUMBER', column_value: 'Sale row 8, loaded 2026-10-01'}
- {source_column: 'AGENT_NPN', column_value: 'Sale row 8, loaded 2026-10-01'}
- name: unit_inspector_northfield_policy_key_missing_none_missing
model: inspector_northfield_policy_key_missing
given:
- input: source('northfield', 'policy')
rows:
- {policy_number: 'NF-100001', insured_id: 'NLI-55001', writing_agent_npn: '17001001', loaded_at: '2026-10-01T02:00:00'}
expect:
rows: []
- name: unit_inspector_northfield_policy_key_missing_one_finding_per_missing_key
model: inspector_northfield_policy_key_missing
given:
- input: source('northfield', 'policy')
rows:
- {policy_number: 'NF-100013', insured_id: null, writing_agent_npn: '17002001', loaded_at: '2026-10-01T02:00:00'}
expect:
rows:
- {source_column: 'INSURED_ID', column_value: 'Policy row NF-100013, loaded 2026-10-01'}
dbt/models/inspectors/inspector_northfield_policy_key_missing.sql
{#
Layer 5 inspector (fix to release 1, 2026-10-28; Code Review Standard k12ki19: no silent record loss).
Discloses each row of LANDING.NORTHFIELD.POLICY, the persistent staging that keeps every row as sent, that arrived without a
policy number, an insured id or a writing agent's number, missing or blank by staging's rule NULLIF(TRIM(...), ''). Staging
maps each such key to the zero key and the satellites keep the row out (design D-44), so the finding keeps it in view (D-45). One finding per row and missing
key (POLICY_NUMBER, INSURED_ID, WRITING_AGENT_NPN); the column value names the row so it can be found in landing storage. Never filters or corrects.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH policies AS (
SELECT NULLIF(TRIM(policy_number), '') AS policy_number, NULLIF(TRIM(insured_id), '') AS insured_number,
NULLIF(TRIM(writing_agent_npn), '') AS agent_npn,
'Policy row ' || COALESCE(NULLIF(TRIM(policy_number), ''), '(no policy number)') || ', loaded '
|| TO_VARCHAR(TO_TIMESTAMP_NTZ(loaded_at), 'YYYY-MM-DD') AS row_name
FROM {{ source('northfield', 'policy') }}
),
missing AS (
SELECT 'POLICY_NUMBER' AS source_column, row_name AS column_value,
'Policy row without a policy number: it stays out of the policy satellite.' AS detail
FROM policies WHERE policy_number IS NULL
UNION ALL
SELECT 'INSURED_ID', row_name, 'Policy row without an insured id: it stays out of the insured satellite, and its policy links to the unknown insured.' FROM policies WHERE insured_number IS NULL
UNION ALL
SELECT 'WRITING_AGENT_NPN', row_name, 'Policy row without a writing agent''s number: its policy links to the unknown agent.' FROM policies WHERE agent_npn IS NULL
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'inspector_northfield_policy_key_missing'", "'NORTHFIELD'", "'NORTHFIELD.POLICY'", "source_column", "column_value"]) }} AS finding_pk,
'NORTHFIELD' AS source_system,
'NORTHFIELD.POLICY' AS source_table,
source_column,
LEFT(column_value, 500) AS column_value,
'inspector_northfield_policy_key_missing' AS finder_name,
'inspection' AS finder_type,
detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM missing
dbt/models/inspectors/inspector_sunbelt_client_key_missing.sql
{#
Layer 5 inspector (fix to release 1, 2026-10-28; Code Review Standard k12ki19: no silent record loss).
Discloses each row of LANDING.SUNBELT.CLIENT, the persistent staging that keeps every row as sent, that arrived without a
client number, missing or blank by staging's rule NULLIF(TRIM(client_number), ''). Staging maps such a row to the zero key
and the client satellite keeps it out (design D-44), so this finding is what keeps it in view (D-45). One finding per row and missing
key (CLIENT_NUMBER); the column value names the row so it can be found in landing storage. Never filters or corrects.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH missing AS (
SELECT 'CLIENT_NUMBER' AS source_column,
'CRM row ' || crm_row_id || ', loaded ' || TO_VARCHAR(TO_TIMESTAMP_NTZ(loaded_at), 'YYYY-MM-DD') AS column_value,
'Client row without a client number: it names no client, so it stays out of the client satellite (it would merge into one false client).' AS detail
FROM {{ source('sunbelt', 'client') }}
WHERE NULLIF(TRIM(client_number), '') IS NULL
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'inspector_sunbelt_client_key_missing'", "'SUNBELT'", "'SUNBELT.CLIENT'", "source_column", "column_value"]) }} AS finding_pk,
'SUNBELT' AS source_system,
'SUNBELT.CLIENT' AS source_table,
source_column,
LEFT(column_value, 500) AS column_value,
'inspector_sunbelt_client_key_missing' AS finder_name,
'inspection' AS finder_type,
detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM missing
dbt/models/inspectors/inspector_sunbelt_client_number_form.sql
{#
Layer 5 inspector (design D-9; layer 5 is named inspector in this project, Ray 2026-10-22).
Discloses each distinct client number whose trimmed, uppercased form does not match the designed
form (the letter A, a hyphen, four digits). One finding per value; never filters or corrects.
Pattern: one finding per nonconforming value.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH nonconforming AS (
SELECT DISTINCT client_number AS column_value
FROM {{ ref('stg_sunbelt_client') }}
WHERE client_number IS NOT NULL
AND hk_client_h NOT IN ('00000000000000000000000000000000', 'ffffffffffffffffffffffffffffffff')
AND NOT REGEXP_LIKE(UPPER(TRIM(client_number)), '^A-[0-9]{4}$')
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'inspector_sunbelt_client_number_form'", "'SUNBELT'", "'SUNBELT.CLIENT'", "'CLIENT_NUMBER'", "column_value"]) }} AS finding_pk,
'SUNBELT' AS source_system,
'SUNBELT.CLIENT' AS source_table,
'CLIENT_NUMBER' AS source_column,
LEFT(column_value, 500) AS column_value,
'inspector_sunbelt_client_number_form' AS finder_name,
'inspection' AS finder_type,
'Client number not in the designed form (A, hyphen, four digits); it may name an existing client under another key (profile P-3).' AS detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM nonconforming
dbt/models/inspectors/inspector_sunbelt_policy_number_form.sql
{#
Layer 5 inspector (design D-30; guide step 31). Discloses each distinct policy number in Sunbelt Senior's
sales data whose trimmed, uppercased form does not match the carrier's form (NF, a hyphen, six digits).
Unit test I4 red 2026-10-25 01:12 UTC on beb49c9 before the filter, green with it. One finding per value; never filters or corrects.
Pattern: one finding per nonconforming value.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH nonconforming AS (
SELECT DISTINCT policy_number AS column_value
FROM {{ ref('stg_sunbelt_policy_sale') }}
WHERE policy_number IS NOT NULL
AND hk_policy_h NOT IN ('00000000000000000000000000000000', 'ffffffffffffffffffffffffffffffff')
AND NOT REGEXP_LIKE(UPPER(TRIM(policy_number)), '^NF-[0-9]{6}$')
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'inspector_sunbelt_policy_number_form'", "'SUNBELT'", "'SUNBELT.POLICY_SALE'", "'POLICY_NUMBER'", "column_value"]) }} AS finding_pk,
'SUNBELT' AS source_system,
'SUNBELT.POLICY_SALE' AS source_table,
'POLICY_NUMBER' AS source_column,
LEFT(column_value, 500) AS column_value,
'inspector_sunbelt_policy_number_form' AS finder_name,
'inspection' AS finder_type,
'Policy number not in the carrier''s form (NF, hyphen, six digits); it may name an existing policy under another key (profile P-12, path PH6).' AS detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM nonconforming
dbt/models/inspectors/inspector_sunbelt_policy_sale_key_missing.sql
{#
Layer 5 inspector (fix to release 1, 2026-10-28; Code Review Standard k12ki19: no silent record loss).
Discloses each row of LANDING.SUNBELT.POLICY_SALE, the persistent staging that keeps every row as sent, that arrived without a
policy number, a client number or an agent number, missing or blank by staging's rule NULLIF(TRIM(...), ''). Staging maps
each such key to the zero key, so the finding is what keeps the gap in view (design D-45). One finding per row and missing
key (POLICY_NUMBER, CLIENT_NUMBER, AGENT_NPN); the column value names the row so it can be found in landing storage. Never filters or corrects.
#}
{{ config(
materialized='merge_into',
meta={
'target_database': landing_storage(),
'target_schema': 'CONFIG',
'target_table': 'DATA_FINDING',
'unique_key': 'FINDING_PK',
'diff_key': 'FINDING_PK',
'ts_key': 'DETECTED_AT'
}
) }}
WITH sales AS (
SELECT NULLIF(TRIM(policy_number), '') AS policy_number, NULLIF(TRIM(client_number), '') AS client_number,
NULLIF(TRIM(agent_npn), '') AS agent_npn,
'Sale row ' || sale_row_id || ', loaded ' || TO_VARCHAR(TO_TIMESTAMP_NTZ(loaded_at), 'YYYY-MM-DD') AS row_name
FROM {{ source('sunbelt', 'policy_sale') }}
),
missing AS (
SELECT 'POLICY_NUMBER' AS source_column, row_name AS column_value,
'Sale without a policy number: its roles and agent link to the unknown policy.' AS detail
FROM sales WHERE policy_number IS NULL
UNION ALL
SELECT 'CLIENT_NUMBER', row_name, 'Sale without a client number: its role links to the unknown client.' FROM sales WHERE client_number IS NULL
UNION ALL
SELECT 'AGENT_NPN', row_name, 'Sale without an agent number: its policy links to the unknown agent.' FROM sales WHERE agent_npn IS NULL
)
SELECT
{{ dbt_utils.generate_surrogate_key(["'inspector_sunbelt_policy_sale_key_missing'", "'SUNBELT'", "'SUNBELT.POLICY_SALE'", "source_column", "column_value"]) }} AS finding_pk,
'SUNBELT' AS source_system,
'SUNBELT.POLICY_SALE' AS source_table,
source_column,
LEFT(column_value, 500) AS column_value,
'inspector_sunbelt_policy_sale_key_missing' AS finder_name,
'inspection' AS finder_type,
detail,
'detected' AS status,
CAST(NULL AS VARCHAR(32)) AS finding_id,
CAST(NULL AS VARCHAR(1024)) AS notes,
CAST(NULL AS VARCHAR(2048)) AS root_cause,
CURRENT_TIMESTAMP() AS detected_at,
CAST(NULL AS VARCHAR(64)) AS acknowledged_by,
CAST(NULL AS TIMESTAMP_TZ) AS acknowledged_at
FROM missing
dbt/seeds/_seeds.yml
version: 2
seeds:
- name: seed_source_system
description: >
The source system seed: one row per source system the vault receives data from, giving each
permanent code (on every vault row as its record source) its current name, description and owner.
Authored by the team; every change is a reviewed commit, so its history is the file's git history.
columns:
- name: SOURCE_SYSTEM_CODE
description: Permanent short code, never changed; the value vault rows carry as their record source.
tests:
- unique:
name: uq_seed_source_system_code
config: {severity: warn}
- not_null:
name: nn_seed_source_system_code
config: {severity: warn}
- name: SOURCE_NAME
description: The source system's current name.
- name: DESCRIPTION
description: What the source system is.
- name: OWNER
description: Who owns the source system.
dbt/seeds/seed_build_expectations.csv
version_tag,table_name,metric_name,condition,description
inc1,CLIENT_H,row_count,increased,Each landing act adds client keys to the hub (guide G-5)
inc1,DATA_FINDING,row_count|audit,< 1,The auditor finds no disagreement between source and hub (design D-10)
inc2,POLICY_H,row_count,increased,Each landing act adds policy keys (guide G-17)
inc2,AGENT_POLICY_L,row_count,increased,Each landing act adds agent-policy rows (guide G-17)
inc2,DATA_FINDING,row_count|audit,< 1,The auditors find no disagreement between the sources and the hubs (design D-10)
dbt/seeds/seed_source_system.csv
SOURCE_SYSTEM_CODE,SOURCE_NAME,DESCRIPTION,OWNER
SSI,Sunbelt Senior client file,"Nightly client file of Sunbelt Senior, an agency of Digest Insurance Distributors",Digest Insurance Distributors
SYSTEM,The vault itself: unknown ghost record,"Created by the vault, not sent by any source: the row that missing keys join to",The vault's team
ERROR,The vault itself: error ghost record,"Created by the vault, not sent by any source: the row that broken keys join to",The vault's team
NFL,Northfield Life policy data,"Policy data Northfield Life, a carrier, sends to Digest Insurance Distributors",Northfield Life
PMC,Pinecrest Mutual policy data,"Policy data Pinecrest Mutual, a carrier, sends to Digest Insurance Distributors",Pinecrest Mutual
dbt/tests/assert_client_h_covers_usable_source_keys.sql
{{ config(severity='warn', tags=['layer_4']) }}
{# Layer 4 (design D-3, D-4): every staged row with a usable client number has its key in the hub. Returns offending keys. #}
SELECT DISTINCT s.hk_client_h, s.client_number
FROM {{ ref('stg_sunbelt_client') }} s
WHERE s.client_number IS NOT NULL
AND s.hk_client_h NOT IN ('00000000000000000000000000000000', 'ffffffffffffffffffffffffffffffff')
AND NOT EXISTS (SELECT 1 FROM {{ ref('client_h') }} h WHERE h.hk_client_h = s.hk_client_h)
dbt/tests/assert_client_h_ghost_keys_belong_to_ghosts.sql
{{ config(severity='warn', tags=['layer_4']) }}
{#
Layer 4 (profile P-5, design D-7; added as deviation DEV-5, Ray 2026-10-23).
The all-zeros key must belong to the unknown ghost record (record source SYSTEM) and the all-f key
to the error ghost record (ERROR). Any other owner means a business row took a ghost's key, as
happened when the hub first ran in incremental mode and Sunbelt Senior Insurance's NULL client number claimed the
all-zeros key. Returns offending rows.
#}
SELECT hk_client_h, client_number, ldts, rsrc
FROM {{ ref('client_h') }}
WHERE (hk_client_h = '00000000000000000000000000000000' AND rsrc <> 'SYSTEM')
OR (hk_client_h = 'ffffffffffffffffffffffffffffffff' AND rsrc <> 'ERROR')
dbt/tests/assert_client_h_no_blank_business_key.sql
{{ config(severity='warn', tags=['layer_4']) }}
{# Layer 4 (design D-4): no hub row may carry an empty or whitespace-only business key. Returns offending rows. #}
SELECT hk_client_h, client_number
FROM {{ ref('client_h') }}
WHERE TRIM(client_number) = ''
dbt/tests/assert_hashdiff_keeps_case_only_change.sql
{{ config(severity='error', tags=['layer_4']) }}
{#
Layer 4, D-41 (Ray, 2026-10-26): a hashdiff is case-sensitive, so a change only in letter case is a change and the
satellite keeps it as a new version (unit test st3_case_only_new_version). Returns each pair of staged rows for one
insured whose payload differs only in case yet carries the same hashdiff: proof the hashdiff ignored case. The
2026-10-26 QA build on dbt Cloud upper-cased the hashdiff input and lost Martin Lowe's "12 ELM ST" version; this
catches that on any engine, at error severity because it loses history.
#}
WITH staged AS (
SELECT hk_insured_h, hd_insured_northfield_s, ldts,
CONCAT_WS('|', insured_name, TO_VARCHAR(birth_date), address) AS payload
FROM {{ ref('stg_northfield_policy') }}
WHERE hk_insured_h NOT IN ('00000000000000000000000000000000', 'ffffffffffffffffffffffffffffffff')
)
SELECT a.hk_insured_h, a.ldts AS earlier_ldts, b.ldts AS later_ldts, a.payload AS earlier_payload, b.payload AS later_payload
FROM staged a
JOIN staged b
ON b.hk_insured_h = a.hk_insured_h
AND b.ldts > a.ldts
WHERE b.hd_insured_northfield_s = a.hd_insured_northfield_s
AND UPPER(b.payload) = UPPER(a.payload)
AND b.payload <> a.payload
dbt/tests/assert_increment_2_link_hub_keys_exist.sql
{{ config(severity='warn', tags=['layer_4']) }}
{#
Layer 4, increment 2 (design section 8): every link row's hub keys exist in their hubs.
Returns each link row whose hub key is missing from its hub, with the link, the hub and the key.
#}
{%- set pairs = [
('client_policy_l', 'hk_client_h', 'client_h'), ('client_policy_l', 'hk_policy_h', 'policy_h'),
('insured_policy_l', 'hk_insured_h', 'insured_h'), ('insured_policy_l', 'hk_policy_h', 'policy_h'),
('agent_policy_l', 'hk_agent_h', 'agent_h'), ('agent_policy_l', 'hk_policy_h', 'policy_h')
] -%}
{%- for link, key, hub in pairs %}
SELECT '{{ link }}' AS link_table, '{{ hub }}' AS hub_table, l.{{ key }} AS missing_key
FROM {{ ref(link) }} l
LEFT JOIN {{ ref(hub) }} h ON h.{{ key }} = l.{{ key }}
WHERE h.{{ key }} IS NULL
{{ 'UNION ALL' if not loop.last }}
{%- endfor %}
dbt/tests/assert_insured_dim_masking_governance_role_real_values.sql
{# Layer 4, test plan SM1 (mart design D-9; guide step 69): in a session holding SYSADMIN, insured_dim shows the values
as sent. It checks only in such a session and returns no row in any other, where SM2 applies. #}
{{ config(severity='warn') }}
SELECT i.insured_key
FROM {{ ref('insured_dim') }} i
JOIN {{ ref('person_dim') }} p ON p.person_key = i.insured_key
WHERE IS_ROLE_IN_SESSION('SYSADMIN')
AND (i.birth_date IS DISTINCT FROM p.birth_date OR i.address IS DISTINCT FROM p.address)
dbt/tests/assert_insured_dim_masking_other_role_masked.sql
{# Layer 4, test plan SM2 (mart design D-9; guide step 69): in a session without SYSADMIN, insured_dim shows the mask,
the birth year only and the address reduced to its state. It checks only in such a session. #}
{{ config(severity='warn') }}
SELECT i.insured_key
FROM {{ ref('insured_dim') }} i
JOIN {{ ref('person_dim') }} p ON p.person_key = i.insured_key
WHERE NOT IS_ROLE_IN_SESSION('SYSADMIN')
AND (i.birth_date IS DISTINCT FROM LEFT(p.birth_date, 4) OR i.address IS DISTINCT FROM RIGHT(TRIM(p.address), 2))
dbt/tests/assert_policy_terms_fact_face_amount_conserved.sql
{# Layer 4, test plan FM1 (mart design D-1, section 5.3; guide step 73): face_amount summed over policy_terms_fact
equals its sum over the satellite's non-ghost versions. Returns a row when they differ. #}
{{ config(severity='warn') }}
WITH fact AS (SELECT SUM(face_amount) AS total FROM {{ ref('policy_terms_fact') }}),
satellite AS (SELECT SUM(face_amount) AS total FROM {{ ref('policy_northfield_s1') }} WHERE rsrc NOT IN ('SYSTEM', 'ERROR'))
SELECT fact.total AS fact_total, satellite.total AS satellite_total
FROM fact CROSS JOIN satellite
WHERE fact.total IS DISTINCT FROM satellite.total
dbt/tests/assert_vault_record_sources_resolve.sql
{{ config(severity='warn', tags=['layer_4']) }}
{#
Layer 4 (design D-17, D-23; profile P-9, P-10): every record source value in every hub and link resolves to a
code in the source system seed. Returns each unresolved value with its table and row count (guide step 32).
#}
{%- set vault = ['client_h', 'policy_h', 'insured_h', 'agent_h', 'client_policy_l', 'insured_policy_l', 'agent_policy_l'] -%}
WITH sources AS (
{%- for m in vault %}
SELECT '{{ m }}' AS vault_table, rsrc FROM {{ ref(m) }}{{ ' UNION ALL' if not loop.last }}
{%- endfor %}
)
SELECT v.vault_table, v.rsrc, COUNT(*) AS rows_without_a_source_system_code
FROM sources v
LEFT JOIN {{ ref('seed_source_system') }} s ON s.SOURCE_SYSTEM_CODE = v.rsrc
WHERE s.SOURCE_SYSTEM_CODE IS NULL
GROUP BY v.vault_table, v.rsrc
dbt/tests/desc_columns_described.sql
{# Layer 7, test plan SD3 (mart design 4.1a; guide step 77): every column of the conformed mart's views, read from the
warehouse, has a description in the property file that declares its model. Returns each undescribed column. #}
{{ config(severity='warn') }}
{%- set pairs = [] -%}
{%- if execute -%}
{%- for n in graph.nodes.values() if n.resource_type == 'model' and 'conformed_dm' in n.original_file_path -%}
{%- for c in n.columns.values() if (c.description | trim) -%}
{%- do pairs.append((n.name | lower, c.name | lower)) -%}
{%- endfor -%}
{%- endfor -%}
{%- endif %}
WITH described AS (
SELECT column1 AS model_name, column2 AS column_name FROM VALUES ('-', '-')
{%- for p in pairs %}, ('{{ p[0] }}', '{{ p[1] }}'){% endfor %}
)
SELECT LOWER(c.table_name) AS model_name, LOWER(c.column_name) AS column_name
FROM {{ target.database }}.INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN described d ON d.model_name = LOWER(c.table_name) AND d.column_name = LOWER(c.column_name)
WHERE c.table_schema = 'CONFORMED_DM' AND d.column_name IS NULL
dbt/tests/desc_metrics_described.sql
{# Layer 7, test plan SD2 (mart design 4.1a; guide step 77): every metric carries a description. Returns each metric
without one. #}
{{ config(severity='warn') }}
{%- set missing = [] -%}
{%- if execute -%}
{%- for m in (graph.metrics or {}).values() if not (m.description | trim) -%}
{%- do missing.append(m.name) -%}
{%- endfor -%}
{%- endif %}
SELECT column1 AS metric_name FROM VALUES ('-') WHERE FALSE
{%- for m in missing %}
UNION ALL SELECT '{{ m }}'
{%- endfor %}
dbt/tests/desc_semantic_models_described.sql
{# Layer 7, test plan SD1 (mart design 4.1a; guide step 77): every model of the conformed mart, the semantic models among
them, carries a description. Returns each model without one. #}
{{ config(severity='warn') }}
{%- set missing = [] -%}
{%- if execute -%}
{%- for n in graph.nodes.values() if n.resource_type == 'model' and 'conformed_dm' in n.original_file_path and not (n.description | trim) -%}
{%- do missing.append(n.name) -%}
{%- endfor -%}
{%- endif %}
SELECT column1 AS model_name FROM VALUES ('-') WHERE FALSE
{%- for m in missing %}
UNION ALL SELECT '{{ m }}'
{%- endfor %}
dbt/tests/expect_increment_2_staging_keeps_every_landed_row.sql
{{ config(severity='warn', tags=['layer_2']) }}
{#
Layer 2, increment 2 (design section 8: row counts as a source query against a target query, never a fixed number).
Each staging model holds every landed row of its source table plus the toolkit's two ghost records.
Returns a row for each source table whose staging count differs.
#}
WITH counts AS (
SELECT 'SUNBELT.POLICY_SALE' AS source_table,
(SELECT COUNT(*) FROM {{ source('sunbelt', 'policy_sale') }}) AS landed_rows,
(SELECT COUNT(*) FROM {{ ref('stg_sunbelt_policy_sale') }}) AS staged_rows
UNION ALL
SELECT 'NORTHFIELD.POLICY',
(SELECT COUNT(*) FROM {{ source('northfield', 'policy') }}),
(SELECT COUNT(*) FROM {{ ref('stg_northfield_policy') }})
)
SELECT source_table, landed_rows, staged_rows
FROM counts
WHERE staged_rows <> landed_rows + 2
dbt/tests/expect_sunbelt_client_row_count.sql
{{ config(severity='warn', tags=['layer_2']) }}
{#
Layer 2 row count (deviation DEV-2: a singular test in place of dbt_expectations).
Returns a row when the landed count differs from what the profile specifies for the
landing acts present: 33 after act 1, 43 after act 2 (Pilot item 267, P-1).
#}
WITH landed AS (
SELECT COUNT(*) AS row_count, COUNT(DISTINCT loaded_at) AS acts
FROM {{ source('sunbelt', 'client') }}
)
SELECT row_count, acts
FROM landed
WHERE NOT ((acts = 1 AND row_count = 33) OR (acts = 2 AND row_count = 43))
dbt/tests/recon_agents_named_two_routes.sql
{# Layer 6, test plan SR4 (mart design D-8; guide step 77): agents_named by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH f AS (
SELECT policy_key AS k1, COUNT(DISTINCT agent_key) AS n FROM {{ ref('agent_policy_fact') }} GROUP BY 1
),
v AS (
SELECT hk_policy_h AS k1, COUNT(DISTINCT hk_agent_h) AS n FROM {{ ref('agent_policy_l') }} WHERE rsrc NOT IN ('SYSTEM', 'ERROR') GROUP BY 1
)
SELECT COALESCE(f.k1, v.k1) AS k1, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_client_policy_roles_two_routes.sql
{# Layer 6, test plan SR2 (mart design D-1; guide step 77): client_policy_roles by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH f AS (
SELECT client_key AS k1, policy_key AS k2, role AS k3, COUNT(DISTINCT client_policy_role_key) AS n FROM {{ ref('client_policy_role_fact') }} GROUP BY 1, 2, 3
),
v AS (
SELECT hk_client_h AS k1, hk_policy_h AS k2, role AS k3, COUNT(DISTINCT hk_client_policy_l) AS n FROM {{ ref('client_policy_l') }} WHERE rsrc NOT IN ('SYSTEM', 'ERROR') GROUP BY 1, 2, 3
)
SELECT COALESCE(f.k1, v.k1) AS k1, COALESCE(f.k2, v.k2) AS k2, COALESCE(f.k3, v.k3) AS k3, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1 AND f.k2 IS NOT DISTINCT FROM v.k2 AND f.k3 IS NOT DISTINCT FROM v.k3
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_face_amount_current_two_routes.sql
{# Layer 6, test plan SR5 (mart design D-4; guide step 77): face_amount_current by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH f AS (
SELECT c.carrier_code AS k1, SUM(t.face_amount) AS n FROM {{ ref('policy_terms_fact') }} t JOIN {{ ref('policy_dim') }} p ON p.policy_key = t.policy_key JOIN {{ ref('carrier_dim') }} c ON c.carrier_key = p.carrier_key WHERE t.valid_to_date IS NULL GROUP BY 1
),
v AS (
SELECT h.carrier_code AS k1, SUM(s.face_amount) AS n FROM {{ ref('policy_northfield_s1') }} s JOIN {{ ref('policy_h') }} h ON h.hk_policy_h = s.hk_policy_h WHERE s.rsrc NOT IN ('SYSTEM', 'ERROR') AND s.ledts = '8888-12-31 23:59:59' GROUP BY 1
)
SELECT COALESCE(f.k1, v.k1) AS k1, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_face_amount_two_routes.sql
{# Layer 6, test plan SR6 (mart design D-1; guide step 77): face_amount by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH f AS (
SELECT policy_key AS k1, valid_from_date AS k2, SUM(face_amount) AS n FROM {{ ref('policy_terms_fact') }} GROUP BY 1, 2
),
v AS (
SELECT hk_policy_h AS k1, TO_DATE(ldts) AS k2, SUM(face_amount) AS n FROM {{ ref('policy_northfield_s1') }} WHERE rsrc NOT IN ('SYSTEM', 'ERROR') GROUP BY 1, 2
)
SELECT COALESCE(f.k1, v.k1) AS k1, COALESCE(f.k2, v.k2) AS k2, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1 AND f.k2 IS NOT DISTINCT FROM v.k2
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_policies_held_two_routes.sql
{# Layer 6, test plan SR1 (mart design D-1; guide step 77): policies_held by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH f AS (
SELECT client_key AS k1, policy_key AS k2, role AS k3, COUNT(DISTINCT policy_key) AS n FROM {{ ref('client_policy_role_fact') }} GROUP BY 1, 2, 3
),
v AS (
SELECT hk_client_h AS k1, hk_policy_h AS k2, role AS k3, COUNT(DISTINCT hk_policy_h) AS n FROM {{ ref('client_policy_l') }} WHERE rsrc NOT IN ('SYSTEM', 'ERROR') GROUP BY 1, 2, 3
)
SELECT COALESCE(f.k1, v.k1) AS k1, COALESCE(f.k2, v.k2) AS k2, COALESCE(f.k3, v.k3) AS k3, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1 AND f.k2 IS NOT DISTINCT FROM v.k2 AND f.k3 IS NOT DISTINCT FROM v.k3
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_policies_sold_two_routes.sql
{# Layer 6, test plan SR3 (mart design D-1; guide step 77): policies_sold by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH f AS (
SELECT agent_key AS k1, policy_key AS k2, first_named_by_source_key AS k3, COUNT(DISTINCT policy_key) AS n FROM {{ ref('agent_policy_fact') }} GROUP BY 1, 2, 3
),
v AS (
SELECT hk_agent_h AS k1, hk_policy_h AS k2, rsrc AS k3, COUNT(DISTINCT hk_policy_h) AS n FROM {{ ref('agent_policy_l') }} WHERE rsrc NOT IN ('SYSTEM', 'ERROR') GROUP BY 1, 2, 3
)
SELECT COALESCE(f.k1, v.k1) AS k1, COALESCE(f.k2, v.k2) AS k2, COALESCE(f.k3, v.k3) AS k3, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1 AND f.k2 IS NOT DISTINCT FROM v.k2 AND f.k3 IS NOT DISTINCT FROM v.k3
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_policy_versions_two_routes.sql
{# Layer 6, test plan SR7 (mart design D-1; guide step 77): policy_versions by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH f AS (
SELECT policy_key AS k1, COUNT(DISTINCT policy_terms_key) AS n FROM {{ ref('policy_terms_fact') }} GROUP BY 1
),
v AS (
SELECT hk_policy_h AS k1, COUNT(*) AS n FROM {{ ref('policy_northfield_s1') }} WHERE rsrc NOT IN ('SYSTEM', 'ERROR') GROUP BY 1
)
SELECT COALESCE(f.k1, v.k1) AS k1, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_records_not_accepted_two_routes.sql
{# Layer 6, test plan SR8 (mart design D-13; guide step 77): records_not_accepted by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{# It reads the findings the inspectors and auditors write in the same build, so it runs after them: on a fresh landing
the findings exist only once they have run (found by the dev rehearsal, 2026-11-01). #}
-- depends_on: {{ ref('inspector_northfield_policy_key_missing') }}
-- depends_on: {{ ref('inspector_sunbelt_client_key_missing') }}
-- depends_on: {{ ref('inspector_sunbelt_client_number_form') }}
-- depends_on: {{ ref('inspector_sunbelt_policy_number_form') }}
-- depends_on: {{ ref('inspector_sunbelt_policy_sale_key_missing') }}
-- depends_on: {{ ref('auditor_northfield_policy_hub') }}
-- depends_on: {{ ref('auditor_sunbelt_client_hub') }}
-- depends_on: {{ ref('auditor_sunbelt_policy_hub') }}
{{ config(severity='warn') }}
WITH f AS (
SELECT source_key AS k1, finder_type AS k2, COUNT(DISTINCT finding_key) AS n FROM {{ ref('finding_fact') }} WHERE finding_kind = 'form' GROUP BY 1, 2
),
v AS (
SELECT CASE source_system WHEN 'SUNBELT' THEN 'SSI' WHEN 'NORTHFIELD' THEN 'NFL' ELSE source_system END AS k1, finder_type AS k2, COUNT(DISTINCT finding_pk) AS n FROM {{ source('landing_config', 'data_finding') }} WHERE finder_type <> 'audit' AND ENDSWITH(finder_name, '_form') GROUP BY 1, 2
)
SELECT COALESCE(f.k1, v.k1) AS k1, COALESCE(f.k2, v.k2) AS k2, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1 AND f.k2 IS NOT DISTINCT FROM v.k2
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/recon_rows_missing_key_two_routes.sql
{# Layer 6, test plan SR9 (mart design D-13; guide step 77): rows_missing_key by two routes, the conformed fact's aggregation and a
direct query on the vault, returning every group where they differ. In development the first route is the fact's own
SQL, since the workstation cannot compile MetricFlow; the MetricFlow route is proven at QA by Mary's connector
probes P-9 to P-15 (Ray, 2026-11-01, answer 1a). #}
{# It reads the findings the inspectors and auditors write in the same build, so it runs after them: on a fresh landing
the findings exist only once they have run (found by the dev rehearsal, 2026-11-01). #}
-- depends_on: {{ ref('inspector_northfield_policy_key_missing') }}
-- depends_on: {{ ref('inspector_sunbelt_client_key_missing') }}
-- depends_on: {{ ref('inspector_sunbelt_client_number_form') }}
-- depends_on: {{ ref('inspector_sunbelt_policy_number_form') }}
-- depends_on: {{ ref('inspector_sunbelt_policy_sale_key_missing') }}
-- depends_on: {{ ref('auditor_northfield_policy_hub') }}
-- depends_on: {{ ref('auditor_sunbelt_client_hub') }}
-- depends_on: {{ ref('auditor_sunbelt_policy_hub') }}
{{ config(severity='warn') }}
WITH f AS (
SELECT source_key AS k1, finder_name AS k2, COUNT(DISTINCT finding_row) AS n FROM {{ ref('finding_fact') }} WHERE finding_kind = 'missing_key' GROUP BY 1, 2
),
v AS (
SELECT CASE source_system WHEN 'SUNBELT' THEN 'SSI' WHEN 'NORTHFIELD' THEN 'NFL' ELSE source_system END AS k1, finder_name AS k2, COUNT(DISTINCT source_table || ': ' || column_value) AS n FROM {{ source('landing_config', 'data_finding') }} WHERE finder_type <> 'audit' AND ENDSWITH(finder_name, '_key_missing') GROUP BY 1, 2
)
SELECT COALESCE(f.k1, v.k1) AS k1, COALESCE(f.k2, v.k2) AS k2, f.n AS fact_value, v.n AS vault_value
FROM f FULL OUTER JOIN v ON f.k1 IS NOT DISTINCT FROM v.k1 AND f.k2 IS NOT DISTINCT FROM v.k2
WHERE f.n IS DISTINCT FROM v.n
dbt/tests/sq_q09_client_policies_answer.sql
{# Layer 7, test plan SQ1 (Q-9; guide step 77): the saved query's answer beside the figures on Mary's release page, docs/releases/v2026-10-1.html, as of the production build of 2026-10-27 18:46 UTC, returning every
difference. In development the answer is the conformed facts' own SQL for the saved query (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH a AS (
SELECT role AS k1, COUNT(DISTINCT policy_key) AS n FROM {{ ref('client_policy_role_fact') }} GROUP BY 1
),
e AS (SELECT column1 AS k1, column2 AS n FROM VALUES ('owner', 9), ('insured', 3), ('payor', 1))
SELECT COALESCE(a.k1, e.k1) AS k1, a.n AS answer, e.n AS expected
FROM a FULL OUTER JOIN e ON a.k1 IS NOT DISTINCT FROM e.k1
WHERE a.n IS DISTINCT FROM e.n
dbt/tests/sq_q10_coverage_by_carrier_answer.sql
{# Layer 7, test plan SQ2 (Q-10; guide step 77): the saved query's answer beside the figures on Mary's release page, docs/releases/v2026-10-1.html, as of the production build of 2026-10-27 18:46 UTC, returning every
difference. In development the answer is the conformed facts' own SQL for the saved query (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH a AS (
SELECT c.carrier_name AS k1, SUM(t.face_amount) AS n FROM {{ ref('policy_terms_fact') }} t JOIN {{ ref('policy_dim') }} p ON p.policy_key = t.policy_key JOIN {{ ref('carrier_dim') }} c ON c.carrier_key = p.carrier_key WHERE t.valid_to_date IS NULL GROUP BY 1
),
e AS (SELECT column1 AS k1, column2 AS n FROM VALUES ('Northfield Life', 125000))
SELECT COALESCE(a.k1, e.k1) AS k1, a.n AS answer, e.n AS expected
FROM a FULL OUTER JOIN e ON a.k1 IS NOT DISTINCT FROM e.k1
WHERE a.n IS DISTINCT FROM e.n
dbt/tests/sq_q11_agent_per_policy_answer.sql
{# Layer 7, test plan SQ3 (Q-11; guide step 77): the saved query's answer beside the figures on Mary's release page, docs/releases/v2026-10-1.html, as of the production build of 2026-10-27 18:46 UTC, returning every
difference. In development the answer is the conformed facts' own SQL for the saved query (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH a AS (
SELECT IFF(n_agents >= 2, 'disagree', 'agree') AS k1, COUNT(*) AS n FROM (SELECT policy_key, COUNT(DISTINCT agent_key) AS n_agents FROM {{ ref('agent_policy_fact') }} GROUP BY 1) GROUP BY 1
),
e AS (SELECT column1 AS k1, column2 AS n FROM VALUES ('agree', 15), ('disagree', 1))
SELECT COALESCE(a.k1, e.k1) AS k1, a.n AS answer, e.n AS expected
FROM a FULL OUTER JOIN e ON a.k1 IS NOT DISTINCT FROM e.k1
WHERE a.n IS DISTINCT FROM e.n
dbt/tests/sq_q13_client_arrivals_answer.sql
{# Layer 7, test plan SQ4 (Q-13; guide step 77): the saved query's answer beside the direct vault query's figures read in development at 2026-11-01 04:36 UTC, until the release page shows this question (Ray, 2026-11-01, answer 2a), returning every
difference. In development the answer is the conformed facts' own SQL for the saved query (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH a AS (
SELECT c.source_key AS k1, c.first_known_date AS k2, COUNT(DISTINCT f.policy_key) AS n FROM {{ ref('client_policy_role_fact') }} f JOIN {{ ref('client_dim') }} c ON c.client_key = f.client_key GROUP BY 1, 2
),
e AS (SELECT column1 AS k1, column2 AS k2, column3 AS n FROM VALUES ('SSI', '2026-10-01'::DATE, 8), ('SSI', '2026-10-02'::DATE, 1), ('SYSTEM', NULL, 1))
SELECT COALESCE(a.k1, e.k1) AS k1, COALESCE(a.k2, e.k2) AS k2, a.n AS answer, e.n AS expected
FROM a FULL OUTER JOIN e ON a.k1 IS NOT DISTINCT FROM e.k1 AND a.k2 IS NOT DISTINCT FROM e.k2
WHERE a.n IS DISTINCT FROM e.n
dbt/tests/sq_q14_policy_versions_answer.sql
{# Layer 7, test plan SQ5 (Q-14; guide step 77): the saved query's answer beside the direct vault query's figures read in development at 2026-11-01 04:36 UTC, until the release page shows this question (Ray, 2026-11-01, answer 2a), returning every
difference. In development the answer is the conformed facts' own SQL for the saved query (Ray, 2026-11-01, answer 1a). #}
{{ config(severity='warn') }}
WITH a AS (
SELECT p.policy_number AS k1, COUNT(DISTINCT t.policy_terms_key) AS n FROM {{ ref('policy_terms_fact') }} t JOIN {{ ref('policy_dim') }} p ON p.policy_key = t.policy_key GROUP BY 1
),
e AS (SELECT column1 AS k1, column2 AS n FROM VALUES ('NF-100001', 1), ('NF-100002', 1), ('NF-100003', 2), ('NF-100005', 1), ('NF-100007', 1), ('NF-100008', 1), ('NF-100009', 1), ('NF-100011', 1), ('NF-100013', 1))
SELECT COALESCE(a.k1, e.k1) AS k1, a.n AS answer, e.n AS expected
FROM a FULL OUTER JOIN e ON a.k1 IS NOT DISTINCT FROM e.k1
WHERE a.n IS DISTINCT FROM e.n
dbt/tests/sq_q15_records_not_accepted_answer.sql
{# Layer 7, test plan SQ6 (Q-15; guide step 77): the saved query's answer beside the figures on Mary's release page, docs/releases/v2026-10-1.html, as of the production build of 2026-10-27 18:46 UTC, returning every
difference. In development the answer is the conformed facts' own SQL for the saved query (Ray, 2026-11-01, answer 1a). #}
{# It reads the findings the inspectors and auditors write in the same build, so it runs after them: on a fresh landing
the findings exist only once they have run (found by the dev rehearsal, 2026-11-01). #}
-- depends_on: {{ ref('inspector_northfield_policy_key_missing') }}
-- depends_on: {{ ref('inspector_sunbelt_client_key_missing') }}
-- depends_on: {{ ref('inspector_sunbelt_client_number_form') }}
-- depends_on: {{ ref('inspector_sunbelt_policy_number_form') }}
-- depends_on: {{ ref('inspector_sunbelt_policy_sale_key_missing') }}
-- depends_on: {{ ref('auditor_northfield_policy_hub') }}
-- depends_on: {{ ref('auditor_sunbelt_client_hub') }}
-- depends_on: {{ ref('auditor_sunbelt_policy_hub') }}
{{ config(severity='warn') }}
WITH a AS (
SELECT source_key AS k1, COUNT(DISTINCT finding_key) AS n FROM {{ ref('finding_fact') }} WHERE finding_kind = 'form' GROUP BY 1
),
e AS (SELECT column1 AS k1, column2 AS n FROM VALUES ('SSI', 3))
SELECT COALESCE(a.k1, e.k1) AS k1, a.n AS answer, e.n AS expected
FROM a FULL OUTER JOIN e ON a.k1 IS NOT DISTINCT FROM e.k1
WHERE a.n IS DISTINCT FROM e.n