dbt/dbt_project.yml
name: digest_datavault
version: 0.1.0
config-version: 2
profile: digest_datavault
model-paths: ["models"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
test-paths: ["tests"]
target-path: target
clean-targets: [target, dbt_packages]
# The vault's declared keys, from its layer 1 tests (macro declare_vault_keys; Ray, 2026-10-28).
on-run-end:
- "{{ declare_vault_keys() }}"
# Mary's connector reads the conformed mart as CLAUDE_WS_ROLE alone, in every environment (Ray, 2026-11-01, answer 1a)
- "grant usage on schema {{ target.database }}.CONFORMED_DM to role CLAUDE_WS_ROLE"
models:
digest_datavault:
# One schema per layer; the database names the tier (macros/pipeline/generate_schema_name.sql).
staging:
+materialized: view
+schema: STAGING
raw_vault:
+materialized: incremental
+schema: RAW_VAULT
mart:
+materialized: view
+schema: MART
# The conformed mart (mart design D-6; guide step 60): Mary's facts and dimensions, as views.
marts:
conformed_dm:
+materialized: view
+schema: CONFORMED_DM
+grants:
select: ["CLAUDE_WS_ROLE"] # read-only, for Mary's connector; the same in every environment
# Layer 5 inspectors and layer 6 auditors write findings to LANDING.CONFIG.DATA_FINDING;
# each model declares the merge_into materialization itself (increment 1 guide G-1).
inspectors:
+tags: ['finder', 'inspector']
auditors:
+tags: ['finder', 'auditor']
seeds:
digest_datavault:
+schema: REFERENCE
+transient: true # as production holds them; stated so both dbt engines build them alike (dev rehearsal, 2026-11-01)
vars:
# Load timestamps are stored as TIMESTAMP_NTZ and always mean UTC.
# datavault4dbt defaults to TIMESTAMP_TZ, which fills in the session time zone
# for zone-less values (landed as -07:00 for CLAUDE_WS). NTZ never consults the session.
datavault4dbt.timestamp_default_dtype: TIMESTAMP_NTZ
# D-41 (Ray, 2026-10-26): hashdiffs are case-sensitive, a case-only change is a change. The package sets this
# itself, but dbt Cloud's 2026-10-26 QA build compiled it case-insensitive; set here so every engine reads it
# from the root project.
datavault4dbt.hashdiff_input_case_sensitive: true
dbt/macros/declare_vault_keys.sql
{#
The vault's declared keys, from one definition: its layer 1 tests (Ray, 2026-10-28, answers 2y and 4y).
Each unique test on one column, or dbt_utils.unique_combination_of_columns, becomes its table's primary key;
each relationships test becomes a foreign key to the table it names. Snowflake keeps them as information, not
enforced, so loads never fail on them; GET_DDL then reports them, and infra/snowflake/ddl/dv.sql carries them
for a modeling tool. Only tables and seeds are keyed (views cannot be), and a key already present is left alone,
so this is safe to run after every build. Runs as the project's on-run-end hook.
#}
{% macro declare_vault_keys() %}
{% if not execute %}{{ return('') }}{% endif %}
{% set keyed = ['table', 'incremental', 'seed'] %}
{% set by_name = {} %}
{% for n in graph.nodes.values() %}
{% if n.resource_type in ['model', 'seed'] %}{% do by_name.update({n.name: n}) %}{% endif %}
{% endfor %}
{% set pks = {} %}
{% set fks = [] %}
{% for t in graph.nodes.values() %}
{% if t.resource_type == 'test' and t.test_metadata and t.attached_node and t.attached_node in graph.nodes %}
{% set owner = graph.nodes[t.attached_node] %}
{% if owner.config.materialized in keyed %}
{% set kw = t.test_metadata.kwargs %}
{% if t.test_metadata.name == 'unique' and kw.column_name %}
{% do pks.update({owner.name: [kw.column_name]}) %}
{% elif t.test_metadata.name == 'unique_combination_of_columns' %}
{% do pks.update({owner.name: kw.combination_of_columns}) %}
{% elif t.test_metadata.name == 'relationships' %}
{% set target = (kw.to | string).split("'")[1] if "'" in (kw.to | string) else (kw.to | string) %}
{% do fks.append({'table': owner.name, 'column': kw.column_name, 'to': target, 'field': kw.field}) %}
{% endif %}
{% endif %}
{% endif %}
{% endfor %}
{% set statements = [] %}
{% for table, cols in pks.items() | sort %}
{% set n = by_name[table] %}
{% do statements.append({'table': n, 'name': 'pk_' ~ table,
'sql': 'primary key (' ~ cols | join(', ') ~ ')'}) %}
{% endfor %}
{% for fk in fks | sort(attribute='table') %}
{% set n = by_name[fk.table] %}
{% set target = by_name.get(fk.to) %}
{% if target and target.config.materialized in keyed and fk.to in pks %}
{% do statements.append({'table': n, 'name': 'fk_' ~ fk.table ~ '_' ~ fk.column,
'sql': 'foreign key (' ~ fk.column ~ ') references ' ~ target.database ~ '.' ~ target.schema ~ '.' ~ target.alias ~ ' (' ~ fk.field ~ ')'}) %}
{% endif %}
{% endfor %}
{% set added = [] %}
{% for s in statements %}
{% set rel = s.table.database ~ '.' ~ s.table.schema ~ '.' ~ s.table.alias %}
{% set present = run_query("select count(*) from " ~ s.table.database ~ ".information_schema.table_constraints where table_schema = upper('" ~ s.table.schema ~ "') and table_name = upper('" ~ s.table.alias ~ "') and constraint_name = upper('" ~ s.name ~ "')") %}
{% if present.columns[0].values()[0] == 0 %}
{% do run_query('alter table ' ~ rel ~ ' add constraint ' ~ s.name ~ ' ' ~ s.sql) %}
{% do added.append(s.name) %}
{% endif %}
{% endfor %}
{{ log('declare_vault_keys: ' ~ statements | length ~ ' keys defined, ' ~ added | length ~ ' added', info=True) }}
{% endmacro %}
dbt/macros/development/dev_drop_orphans.sql
{#
Development cleanup (Ray, 2026-10-23): a build never drops what the project no longer defines, so removed
or renamed models leave objects behind. This macro lists every table and view in the target database that
no model, seed or snapshot defines, and drops them only when dry_run is false. It refuses any database whose
name does not start with DEV__: cleanup is legitimate only in developer databases.
List: dbt run-operation dev_drop_orphans
Drop: dbt run-operation dev_drop_orphans --args '{dry_run: false}'
Schemas left empty by the drop, and not used by the project, are dropped as well.
#}
{% macro dev_drop_orphans(dry_run=true) %}
{%- set db = target.database | upper -%}
{%- if not db.startswith('DEV__') -%}
{{ exceptions.raise_compiler_error("Refused: " ~ db ~ " is not a developer database (DEV__...). Cleanup never runs in QA or production.") }}
{%- endif -%}
{%- set defined = [] -%}
{%- set project_schemas = [] -%}
{%- for node in graph.nodes.values() if node.resource_type in ['model', 'seed', 'snapshot'] and (node.database | upper) == db -%}
{%- do defined.append((node.schema | upper) ~ '.' ~ ((node.alias or node.name) | upper)) -%}
{%- if (node.schema | upper) not in project_schemas %}{% do project_schemas.append(node.schema | upper) %}{% endif -%}
{%- endfor -%}
{%- set existing = run_query("SELECT table_schema, table_name, table_type FROM " ~ db ~ ".information_schema.tables WHERE table_schema NOT IN ('INFORMATION_SCHEMA', 'PUBLIC') ORDER BY 1, 2") -%}
{%- set orphans = [] -%}
{%- for row in existing.rows -%}
{%- if (row[0] ~ '.' ~ row[1]) not in defined -%}{%- do orphans.append(row) -%}{%- endif -%}
{%- endfor -%}
{%- set schemas = run_query("SELECT schema_name FROM " ~ db ~ ".information_schema.schemata WHERE schema_name NOT IN ('INFORMATION_SCHEMA', 'PUBLIC')") -%}
{%- set orphan_schemas = [] -%}
{%- for s in schemas.rows if s[0] not in project_schemas -%}{%- do orphan_schemas.append(s[0]) -%}{%- endfor -%}
{{ log(db ~ ": " ~ (orphans | length) ~ " objects and " ~ (orphan_schemas | length) ~ " schemas not defined by the project" ~ (" (dry run, nothing dropped)" if dry_run else ""), info=true) }}
{%- for row in orphans -%}
{{ log(" " ~ row[2] ~ " " ~ row[0] ~ "." ~ row[1], info=true) }}
{%- if not dry_run -%}
{%- do run_query("DROP " ~ ("VIEW" if row[2] == 'VIEW' else "TABLE") ~ " IF EXISTS " ~ db ~ "." ~ row[0] ~ "." ~ row[1]) -%}
{%- endif -%}
{%- endfor -%}
{%- for s in orphan_schemas -%}
{{ log(" SCHEMA " ~ s, info=true) }}
{%- if not dry_run -%}{%- do run_query("DROP SCHEMA IF EXISTS " ~ db ~ "." ~ s) -%}{%- endif -%}
{%- endfor -%}
{% endmacro %}
dbt/macros/landing_storage.sql
{% macro landing_storage() %}
{#-
Returns the database name of the landing and storage layer:
- landed sources in <db>.SUNBELT (and one schema per later source)
- Pragmatic Data Platform history in <db>.STORAGE (later increments)
- findings in <db>.CONFIG.DATA_FINDING (inspectors and auditors, via merge_into meta)
- build baselines in <db>.CONFIG.BUILD_BASELINE (capture_baseline, verify_build)
Default: 'LANDING' (increment 1 design D-1). Override with the environment
variable DBT_LANDING_STORAGE. YAML files cannot call macros and use
env_var('DBT_LANDING_STORAGE', 'LANDING') directly.
-#}
{{ return(env_var('DBT_LANDING_STORAGE', 'LANDING')) }}
{% endmacro %}
dbt/macros/materializations/merge_into.sql
-- Custom materialization: merge_into
-- Bridges dbt's one-model-one-table constraint to UDM's multi-source integration.
-- Each source model writes independently to a shared DDL-created target table.
-- The model's SELECT is the transformation. The MERGE is the load. No buffer.
--
-- Config (inside meta):
-- target_database: 'LANDING' (optional, defaults to current database)
-- target_schema: 'INTEGRATION'
-- target_table: 'PARTY'
-- unique_key: 'PARTY_HKEY'
-- diff_key: 'PARTY_HDIFF' (optional, for change detection)
-- ts_key: 'LOAD_TS_UTC' (optional, for newer-wins resolution)
-- exclude_from_update: ['CREATED_AT','CREATED_BY'] (optional)
-- retraction_where: 'tgt.CATEGORY_ID IN (...)' (optional)
-- Ownership scope for retraction. After the MERGE, rows matching this
-- predicate (alias tgt) that are ABSENT from the model's current emission
-- are DELETED. Cures the sparse-scheme orphan: a model that emits rows
-- only for members of a computed set otherwise
-- leaves stale rows behind when a member drops out of the set, because
-- MERGE only touches rows present in the source. Total schemes self-heal
-- and need no scope; sparse schemes declare the partition they own.
--
-- Author: Ray Serrano, March 2026
{% materialization merge_into, adapter='snowflake' %}
{%- set meta = config.get('meta', {}) -%}
{%- set target_database = meta.get('target_database', target.database) -%}
{%- set target_schema = meta.get('target_schema', target.schema) -%}
{%- set target_table = meta.get('target_table') -%}
{%- set unique_key = meta.get('unique_key') -%}
{%- set diff_key = meta.get('diff_key') -%}
{%- set ts_key = meta.get('ts_key', 'LOAD_TS_UTC') -%}
{%- set exclude_from_update = meta.get('exclude_from_update', []) -%}
{%- set retraction_where = meta.get('retraction_where') -%}
{#- DAT-459 (Ray, 2026-09-25): a cleanup condition names its tables as tokens, <<ref:model_name>> or
<<source:source_name.table_name>>, and they are resolved here, at run time. A ref() or source() written in the
model's meta is rendered at parse time to a stand-in name (the calling model's schema and the referenced model's
name), which is wrong whenever the referenced object lives elsewhere. The model declares each token's dependency
with a depends_on comment. -#}
{%- if retraction_where -%}
{%- set ns = namespace(w=retraction_where) -%}
{%- for kind, name in modules.re.findall('<<(ref|source):([A-Za-z0-9_.]+)>>', retraction_where) -%}
{%- if kind == 'ref' -%}
{%- set rel = ref(name) -%}
{%- else -%}
{%- set parts = name.split('.') -%}
{%- set rel = source(parts[0], parts[1]) -%}
{%- endif -%}
{%- set ns.w = ns.w.replace('<<' ~ kind ~ ':' ~ name ~ '>>', rel | string) -%}
{%- endfor -%}
{%- set retraction_where = ns.w -%}
{%- endif -%}
{% if not target_table %}
{{ exceptions.raise_compiler_error("merge_into requires meta.target_table") }}
{% endif %}
{% if not unique_key %}
{{ exceptions.raise_compiler_error("merge_into requires meta.unique_key") }}
{% endif %}
-- Build the target relation (DDL-created table, not dbt-managed)
{%- set target_relation = api.Relation.create(
database=target_database,
schema=target_schema,
identifier=target_table
) -%}
-- Compile the model SQL (the SELECT that produces source rows)
{%- set source_sql = sql -%}
-- Get column names from the target table for UPDATE SET and INSERT
{%- set columns = adapter.get_columns_in_relation(target_relation) -%}
{%- set column_names = columns | map(attribute='name') | list -%}
{%- set update_columns = [] -%}
{%- for col in column_names -%}
{%- if col != unique_key and col not in exclude_from_update -%}
{%- do update_columns.append(col) -%}
{%- endif -%}
{%- endfor -%}
-- Build MERGE statement
{%- set merge_sql -%}
MERGE INTO {{ target_relation }} AS tgt
USING (
{{ source_sql }}
) AS src
ON tgt.{{ unique_key }} = src.{{ unique_key }}
{% if diff_key %}
WHEN MATCHED
AND tgt.{{ diff_key }} != src.{{ diff_key }}
{% if ts_key %}
AND src.{{ ts_key }} >= tgt.{{ ts_key }}
{% endif %}
THEN UPDATE SET
{% for col in update_columns %}
tgt.{{ col }} = src.{{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
{% else %}
WHEN MATCHED THEN UPDATE SET
{% for col in update_columns %}
tgt.{{ col }} = src.{{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
{% endif %}
WHEN NOT MATCHED THEN INSERT (
{% for col in column_names %}
{{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
)
VALUES (
{% for col in column_names %}
src.{{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
)
{%- endset -%}
{{ log("MERGE INTO " ~ target_relation ~ " using model " ~ model.unique_id, info=True) }}
{%- call statement('main') -%}
{{ merge_sql }}
{%- endcall -%}
{% if retraction_where %}
-- Retraction: delete rows in this model's ownership scope that the
-- current emission no longer contains. NOT IN is safe here because unique
-- keys are generated surrogate hashes and never NULL.
{%- set retraction_sql -%}
DELETE FROM {{ target_relation }} tgt
WHERE ({{ retraction_where }})
AND tgt.{{ unique_key }} NOT IN (
SELECT src.{{ unique_key }} FROM (
{{ source_sql }}
) src
)
{%- endset -%}
{{ log("RETRACTION on " ~ target_relation ~ " for model " ~ model.unique_id, info=True) }}
{%- call statement('retraction') -%}
{{ retraction_sql }}
{%- endcall -%}
{% endif %}
{{ return({'relations': []}) }}
{% endmaterialization %}
dbt/macros/operations/land_act.sql
{#
Landing acts for the course's synthetic source data (Standard: Operations as dbt Macros, k12ki286).
The stand-in for the source systems: the course's sources are synthetic, so land_act delivers what a source would.
Landing data flows down (Ray, 2026-10-25, answer 1y): it is delivered to production, LANDING, and QA and developer
landing get it only by cloning LANDING. Code still flows up: a change to this macro is proven first in a developer
clone (DEV__...), which therefore stays allowed. Loads one act of one table into the landing database named by
DBT_LANDING_STORAGE.
Tables: client (SUNBELT.CLIENT, increment 1), policy_sale (SUNBELT.POLICY_SALE) and policy (NORTHFIELD.POLICY),
increment 2 (guide step 24); pinecrest_policy (PINECREST.POLICY), for Ray's lab 2 (design D-38; guide step 34); northfield_organization (NORTHFIELD.ORGANIZATION)
and pinecrest_organization (PINECREST.ORGANIZATION), each carrier's organization record (mart design D-11; guide step 54). Act 1 LOADED_AT 2026-10-01T02:00:00; act 2 2026-10-02T02:00:00.
Rows are specified by the branch profile (k35ki267, results P-1, P-16, P-20 and P-21); names are invented.
List: dbt run-operation land_act --args '{act: 1, table: policy_sale}'
Load: dbt run-operation land_act --args '{act: 1, table: policy_sale, dry_run: false}'
Replace: dbt run-operation land_act --args '{act: 1, table: policy_sale, dry_run: false, replace: true}'
Allowed targets: LANDING (delivery) and DEV__ clones (proving the macro); refuses every other database, LANDING_QA
included, since QA receives landing data only by clone; refuses to load an act already present unless replace is true.
#}
{% macro land_act(act, table='client', dry_run=true, replace=false) %}
{%- set db = (env_var('DBT_LANDING_STORAGE', 'LANDING')) | upper -%}
{%- if not (db == 'LANDING' or db.startswith('DEV__')) -%}
{{ exceptions.raise_compiler_error('Refused: ' ~ db ~ ' receives landing data only by cloning LANDING. land_act delivers to LANDING, or to a developer clone (DEV__...) to prove a change to this macro.') }}
{%- endif -%}
{%- set targets = {
'client': {'relation': 'SUNBELT.CLIENT', 'columns': 'CRM_ROW_ID, CLIENT_NUMBER, FIRST_NAME, LAST_NAME, STATE, LOADED_AT'},
'policy_sale': {'relation': 'SUNBELT.POLICY_SALE', 'columns': 'SALE_ROW_ID, POLICY_NUMBER, CLIENT_NUMBER, CARRIER_CODE, ROLE, SOLD_ON, AGENT_NAME, AGENT_NPN, PLAN_NAME, LOADED_AT'},
'policy': {'relation': 'NORTHFIELD.POLICY', 'columns': 'POLICY_NUMBER, INSURED_ID, INSURED_NAME, BIRTH_DATE, ADDRESS, FACE_AMOUNT, STATUS, EFFECTIVE_DATE, WRITING_AGENT_NPN, LOADED_AT'},
'pinecrest_policy': {'relation': 'PINECREST.POLICY', 'columns': 'POLICY_NUMBER, PLAN_NAME, FACE_AMOUNT, STATUS, EFFECTIVE_DATE, WRITING_AGENT_NPN, LOADED_AT'},
'northfield_organization': {'relation': 'NORTHFIELD.ORGANIZATION', 'columns': 'CARRIER_CODE, CARRIER_NAME, LOADED_AT'},
'pinecrest_organization': {'relation': 'PINECREST.ORGANIZATION', 'columns': 'CARRIER_CODE, CARRIER_NAME, LOADED_AT'}
} -%}
{%- if table not in targets -%}
{{ exceptions.raise_compiler_error('Unknown table ' ~ table ~ '; use client, policy_sale, policy, pinecrest_policy, northfield_organization or pinecrest_organization.') }}
{%- endif -%}
{%- set loaded_at = {1: '2026-10-01T02:00:00', 2: '2026-10-02T02:00:00'}[act | int] -%}
{%- set client_values -%}
{%- if (act | int) == 1 %}
(1, 'A-1001', 'Rosa', 'Alvarez', 'FL', '2026-10-01T02:00:00'),
(2, 'A-1002', 'Mark', 'Bennett', 'GA', '2026-10-01T02:00:00'),
(3, 'A-1003', 'Lena', 'Castillo', 'OH', '2026-10-01T02:00:00'),
(4, 'A-1004', 'Omar', 'Dorsey', 'TX', '2026-10-01T02:00:00'),
(5, 'A-1005', 'Grace', 'Ellison', 'AZ', '2026-10-01T02:00:00'),
(6, 'A-1006', 'Tomas', 'Fuentes', 'NC', '2026-10-01T02:00:00'),
(7, 'A-1007', 'Irene', 'Garrity', 'PA', '2026-10-01T02:00:00'),
(8, 'A-1008', 'Paul', 'Holloway', 'MI', '2026-10-01T02:00:00'),
(9, 'A-1009', 'Nadia', 'Ibarra', 'TN', '2026-10-01T02:00:00'),
(10, 'A-1010', 'Victor', 'Jensen', 'SC', '2026-10-01T02:00:00'),
(11, 'A-1011', 'Elena', 'Kowalski', 'FL', '2026-10-01T02:00:00'),
(12, 'A-1012', 'Hugo', 'Lindqvist', 'GA', '2026-10-01T02:00:00'),
(13, 'A-1013', 'Clara', 'Moreau', 'OH', '2026-10-01T02:00:00'),
(14, 'A-1014', 'Ivan', 'Navarro', 'TX', '2026-10-01T02:00:00'),
(15, 'A-1015', 'Sofia', 'Okafor', 'AZ', '2026-10-01T02:00:00'),
(16, 'A-1016', 'Dario', 'Pellegrini', 'NC', '2026-10-01T02:00:00'),
(17, 'A-1017', 'Mae', 'Quintero', 'PA', '2026-10-01T02:00:00'),
(18, 'A-1018', 'Felix', 'Rasmussen', 'MI', '2026-10-01T02:00:00'),
(19, 'A-1019', 'Lucia', 'Salcedo', 'TN', '2026-10-01T02:00:00'),
(20, 'A-1020', 'Abel', 'Thornton', 'SC', '2026-10-01T02:00:00'),
(21, 'A-1021', 'Nora', 'Underwood', 'FL', '2026-10-01T02:00:00'),
(22, 'A-1022', 'Emil', 'Valdez', 'GA', '2026-10-01T02:00:00'),
(23, 'A-1023', 'Iris', 'Whitfield', 'OH', '2026-10-01T02:00:00'),
(24, 'A-1024', 'Raul', 'Xavier', 'TX', '2026-10-01T02:00:00'),
(25, 'A-1025', 'Vera', 'Yamada', 'AZ', '2026-10-01T02:00:00'),
(26, 'A-1026', 'Otto', 'Zamora', 'NC', '2026-10-01T02:00:00'),
(27, 'A-1027', 'Ada', 'Aldana', 'PA', '2026-10-01T02:00:00'),
(28, 'A-1028', 'Leon', 'Brandt', 'MI', '2026-10-01T02:00:00'),
(29, 'A-1029', 'Eva', 'Cordero', 'TN', '2026-10-01T02:00:00'),
(30, 'A-1030', 'Bruno', 'Delacroix', 'SC', '2026-10-01T02:00:00'),
(31, NULL, 'Pia', 'Esposito', 'FL', '2026-10-01T02:00:00'),
(32, '', 'Karl', 'Falk', 'GA', '2026-10-01T02:00:00'),
(33, 'a-1010', 'Victor', 'Jensen', 'SC', '2026-10-01T02:00:00')
{%- else %}
(1, 'A-1001', 'Rosa', 'Alvarez', 'FL', '2026-10-02T02:00:00'),
(2, 'A-1002', 'Mark', 'Bennett', 'GA', '2026-10-02T02:00:00'),
(34, 'A-1003', 'Lena', 'Castillo', 'OH', '2026-10-02T02:00:00'),
(35, 'A-1031', 'Lucas', 'Ferraro', 'OH', '2026-10-02T02:00:00'),
(36, 'A-1032', 'June', 'Halvorsen', 'TX', '2026-10-02T02:00:00'),
(37, 'a-1004', 'Omar', 'Dorsey', 'TX', '2026-10-02T02:00:00'),
(38, 'A-1005 ', 'Grace', 'Ellison', 'AZ', '2026-10-02T02:00:00'),
(39, 'A1007', 'Irene', 'Garrity', 'PA', '2026-10-02T02:00:00'),
(40, 'A-01008', 'Paul', 'Holloway', 'MI', '2026-10-02T02:00:00'),
(41, ' ', 'Dina', 'Guerrero', 'OH', '2026-10-02T02:00:00')
{%- endif %}
{%- endset -%}
{%- set policy_sale_values -%}
{%- if (act | int) == 1 %}
(1, 'NF-100001', 'A-1001', 'NFL', 'owner', '2026-09-02', 'Dana Whitaker', '17001001', 'Senior Term 20', '2026-10-01T02:00:00'),
(2, 'NF-100005', 'A-1005', NULL, 'owner', '2026-09-03', 'Marcus Reyes', '17001006', 'Final Expense 10K', '2026-10-01T02:00:00'),
(3, 'NF-100006', 'A-1006', 'NFL', 'owner', '2026-09-04', 'Marcus Reyes', '17001006', 'Senior Term 20', '2026-10-01T02:00:00'),
(4, 'NF-100006', 'A-1008', 'NFL', 'insured', '2026-09-04', 'Marcus Reyes', '17001006', 'Senior Term 20', '2026-10-01T02:00:00'),
(5, 'NF-100007', 'A-1007', 'NFL', 'insured', '2026-09-05', 'Dana Whitaker', '17001001', 'Guaranteed Whole Life', '2026-10-01T02:00:00'),
(6, 'NF-100010', 'A-1010', 'NFL', 'owner', '2026-09-08', 'Marcus Reyes', '17001006', 'Final Expense 10K', '2026-10-01T02:00:00'),
(7, 'NF-100010', 'A-1010', 'NFL', 'insured', '2026-09-08', 'Marcus Reyes', '17001006', 'Final Expense 10K', '2026-10-01T02:00:00'),
(8, '', 'A-1013', NULL, 'owner', '2026-09-09', 'Marcus Reyes', '17001006', 'Senior Term 20', '2026-10-01T02:00:00'),
(9, 'NF-100014', '', 'NFL', 'owner', '2026-09-10', 'Dana Whitaker', '', 'Guaranteed Whole Life', '2026-10-01T02:00:00')
{%- else %}
(10, 'NF-100012', 'A-1033', 'NFL', 'owner', '2026-09-28', 'Priya Natarajan', '17001007', 'Senior Term 20', '2026-10-02T02:00:00'),
(11, 'NF-100011', 'A-1011', 'NFL', 'owner', '2026-09-12', 'Owen Fitch', '17001004', 'Final Expense 10K', '2026-10-02T02:00:00'),
(12, 'nf-100001', 'A-1001', 'NFL', 'owner', '2026-09-02', 'Dana Whitaker', '17001001', 'Senior Term 20', '2026-10-02T02:00:00'),
(13, 'NF100009', 'A-1009', 'NFL', 'owner', '2026-09-11', 'Marcus Reyes', '17001006', 'Final Expense 10K', '2026-10-02T02:00:00'),
(14, 'NF-100001', 'A-1001', 'NFL', 'payor', '2026-09-02', 'Dana Whitaker', '17001001', 'Senior Term 20', '2026-10-02T02:00:00')
{%- endif %}
{%- endset -%}
{%- set policy_values -%}
{%- if (act | int) == 1 %}
('NF-100001', 'NLI-55001', 'Alvarez, Rosa', '1952-03-14', '41 Palm Ave, Tampa FL', 25000, 'IF', '2026-09-15', '17001001', '2026-10-01T02:00:00'),
('NF-100002', 'NLI-55001', 'Alvarez, Rosa', '1952-03-14', '41 Palm Ave, Tampa FL', 10000, 'IF', '2026-08-20', '17002001', '2026-10-01T02:00:00'),
('NF-100003', 'NLI-55003', 'Brennan, Ada', '1949-07-02', '9 Oak St, Savannah GA', 15000, 'IF', '2026-07-01', '17002001', '2026-10-01T02:00:00'),
('NF-100005', 'NLI-55005', 'Ellison, Grace', '1955-11-23', '300 Mesa Rd, Tucson AZ', 10000, 'IF', '2026-09-18', '17001006', '2026-10-01T02:00:00'),
('NF-100007', 'NLI-55007', 'Garrity, Irene', '1950-05-09', '12 Birch Ln, Erie PA', 20000, 'IF', '2026-09-19', '17001001', '2026-10-01T02:00:00'),
('NF-100008', 'NLI-55008', 'Lowe, Martin', '1947-01-30', '12 Elm St, Flint MI', 15000, 'IF', '2026-06-10', '17002001', '2026-10-01T02:00:00'),
('NF-100009', 'NLI-55009', 'Ibarra, Nadia', '1953-08-17', '7 River Rd, Knoxville TN', 10000, 'IF', '2026-09-22', '17001006', '2026-10-01T02:00:00'),
('NF-100011', 'NLI-55011', 'Kowalski, Elena', '1951-12-05', '88 Bay Dr, Naples FL', 10000, 'IF', '2026-09-25', '17001005', '2026-10-01T02:00:00'),
('NF-100013', '', 'Duarte, Felipe', '1948-04-11', '5 Hill Ct, Mesa AZ', 10000, 'IF', '2026-05-15', '17002001', '2026-10-01T02:00:00')
{%- else %}
('NF-100002', 'NLI-55001', 'Alvarez, Rosa', '1952-03-14', '41 Palm Ave, Tampa FL', 10000, 'IF', '2026-08-20', '17002001', '2026-10-02T02:00:00'),
('NF-100003', 'NLI-55003', 'Brennan, Ada', '1949-07-02', '9 Oak St, Savannah GA', 15000, 'LP', '2026-07-01', '17002001', '2026-10-02T02:00:00'),
('NF-100008', 'NLI-55008', 'Lowe, Martin', '1947-01-30', '12 ELM ST, FLINT MI', 15000, 'IF', '2026-06-10', '17002001', '2026-10-02T02:00:00')
{%- endif %}
{%- endset -%}
{%- set pinecrest_policy_values -%}
{%- if (act | int) == 1 %}
('PM-300001', 'Pinecrest Final Expense', '10000.00', 'IF', '2026-08-12', '17001001', '2026-10-01T02:00:00'),
('PM-300002', 'Pinecrest Whole Life', '15000.00', 'IF', '2026-08-20', '17001006', '2026-10-01T02:00:00'),
('pm-300003', 'Pinecrest Term 20', '25000.00', 'IF', '2026-09-01', '17001007', '2026-10-01T02:00:00'),
('PM-300004 ', 'Pinecrest Final Expense', '10000.00', 'IF', '2026-09-03', '17001001', '2026-10-01T02:00:00'),
('', 'Pinecrest Term 20', '20000.00', 'IF', '2026-09-08', '17001004', '2026-10-01T02:00:00'),
('PM300006', 'Pinecrest Whole Life', '12000.00', 'IF', '2026-09-10', '17002001', '2026-10-01T02:00:00'),
('PM-300007', 'Pinecrest Term 20', '25000.00', 'IF', '2026-09-15', '17001005', '2026-10-01T02:00:00'),
('PM-300008', 'Pinecrest Final Expense', '8000.00', 'IF', '2026-09-22', '17001006', '2026-10-01T02:00:00')
{%- else %}
('PM-300001', 'Pinecrest Final Expense', '10000.00', 'IF', '2026-08-12', '17001001', '2026-10-02T02:00:00'),
('PM-300002', 'Pinecrest Whole Life', '15000.00', 'LP', '2026-08-20', '17001006', '2026-10-02T02:00:00'),
(' ', 'Pinecrest Final Expense', '9000.00', 'IF', '2026-09-28', '17001001', '2026-10-02T02:00:00'),
('PM-300009', 'Pinecrest Whole Life', '18000.00', 'IF', '2026-09-30', '17001007', '2026-10-02T02:00:00')
{%- endif %}
{%- endset -%}
{#- P-21: one organization record per carrier and load; Pinecrest Mutual's first arrives in act 2. -#}
{%- set northfield_organization_values -%}
{%- if (act | int) == 1 %}
('NFL', 'Northfield Life', '2026-10-01T02:00:00')
{%- else %}
('NFL', 'Northfield Life', '2026-10-02T02:00:00')
{%- endif %}
{%- endset -%}
{%- set pinecrest_organization_values -%}
{%- if (act | int) == 2 %}
('PMC', 'Pinecrest Mutual', '2026-10-02T02:00:00')
{%- endif %}
{%- endset -%}
{%- set values = {'client': client_values, 'policy_sale': policy_sale_values, 'policy': policy_values, 'pinecrest_policy': pinecrest_policy_values,
'northfield_organization': northfield_organization_values, 'pinecrest_organization': pinecrest_organization_values}[table] -%}
{%- set rel = db ~ '.' ~ targets[table]['relation'] -%}
{%- set present = run_query("SELECT COUNT(*) FROM " ~ rel ~ " WHERE LOADED_AT = '" ~ loaded_at ~ "'").columns[0].values()[0] -%}
{{ log(rel ~ ': act ' ~ act ~ ' (' ~ loaded_at ~ ') has ' ~ present ~ ' rows present' ~ (' (dry run, nothing changed)' if dry_run else ''), info=true) }}
{%- if not dry_run -%}
{%- if present > 0 and not replace -%}
{{ exceptions.raise_compiler_error('Act ' ~ act ~ ' of ' ~ table ~ ' is already loaded (' ~ present ~ ' rows). Pass replace: true to reload it.') }}
{%- endif -%}
{%- if present > 0 -%}{%- do run_query("DELETE FROM " ~ rel ~ " WHERE LOADED_AT = '" ~ loaded_at ~ "'") -%}{%- endif -%}
{%- if values | trim -%}
{%- do run_query('INSERT INTO ' ~ rel ~ ' (' ~ targets[table]['columns'] ~ ') VALUES ' ~ values) -%}
{%- else -%}
{{ log('Act ' ~ act ~ ' of ' ~ table ~ ' has no rows in the profile; nothing to load.', info=true) }}
{%- endif -%}
{%- set after = run_query("SELECT COUNT(*) FROM " ~ rel ~ " WHERE LOADED_AT = '" ~ loaded_at ~ "'").columns[0].values()[0] -%}
{{ log('Loaded act ' ~ act ~ ' of ' ~ table ~ ': ' ~ after ~ ' rows now present.', info=true) }}
{%- endif -%}
{% endmacro %}
dbt/macros/operations/vault_checks.sql
{#
Routine checks of what the branch's documents promise (Standard: Operations as dbt Macros, k12ki286).
Reads only. Run in any environment: dbt run-operation vault_checks
Reports: landed rows per act, hub rows and client keys, record sources with their names from the source
system seed, and findings per finder in the landing clone's CONFIG.
#}
{% macro vault_checks() %}
{%- set land = (env_var('DBT_LANDING_STORAGE', 'LANDING')) | upper -%}
{%- set hub = ref('client_h') -%}
{%- set seed = ref('seed_source_system') -%}
{%- set checks = [
("Landed rows per act", "SELECT LOADED_AT, COUNT(*) FROM " ~ land ~ ".SUNBELT.CLIENT GROUP BY 1 ORDER BY 1"),
("Landed sales rows per act", "SELECT LOADED_AT, COUNT(*) FROM " ~ land ~ ".SUNBELT.POLICY_SALE GROUP BY 1 ORDER BY 1"),
("Landed carrier rows per act", "SELECT LOADED_AT, COUNT(*) FROM " ~ land ~ ".NORTHFIELD.POLICY GROUP BY 1 ORDER BY 1"),
("Hub rows, client keys", "SELECT COUNT(*), COUNT_IF(rsrc NOT IN ('SYSTEM', 'ERROR')) FROM " ~ hub),
("Record sources", "SELECT h.rsrc, s.SOURCE_NAME, COUNT(*) FROM " ~ hub ~ " h LEFT JOIN " ~ seed ~ " s ON s.SOURCE_SYSTEM_CODE = h.rsrc GROUP BY 1, 2 ORDER BY 1"),
("Findings per finder", "SELECT FINDER_NAME, COUNT(*) FROM " ~ land ~ ".CONFIG.DATA_FINDING GROUP BY 1 ORDER BY 1")
] -%}
{{ log("vault_checks: vault " ~ target.database ~ ", landing " ~ land, info=true) }}
{%- for title, sql in checks -%}
{{ log("== " ~ title, info=true) }}
{%- for row in run_query(sql).rows -%}
{{ log(" " ~ (row.values() | join(" | ")), info=true) }}
{%- endfor -%}
{%- endfor -%}
{% endmacro %}
dbt/macros/pipeline/generate_schema_name.sql
{# Uses a custom schema exactly as written, so dbt_project.yml decides the schema per layer, not each developer's credentials. #}
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- if custom_schema_name is none -%}
{{ target.schema }}
{%- else -%}
{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}
dbt/macros/verification/baseline_metrics.sql
{%- macro baseline_metrics(db, ls) -%}
{#
The measured set shared by capture_baseline and verify_build (guide decision G-4; increment 2, guide step 32):
every landed source table, every vault table by row count, and the findings by finder type. A vault table not
yet built counts 0, so a first build's 'before' snapshot does not fail.
#}
{%- set vault_tables = ['CLIENT_H', 'POLICY_H', 'INSURED_H', 'AGENT_H', 'CLIENT_POLICY_L', 'INSURED_POLICY_L', 'AGENT_POLICY_L',
'CLIENT_SUNBELT_S', 'INSURED_NORTHFIELD_S', 'POLICY_NORTHFIELD_S', 'CLIENT_POLICY_SUNBELT_S'] -%}
SELECT 'SUNBELT.CLIENT' AS table_name, 'row_count' AS metric_name, COUNT(*) AS metric_value FROM {{ ls }}.SUNBELT.CLIENT
UNION ALL SELECT 'SUNBELT.POLICY_SALE', 'row_count', COUNT(*) FROM {{ ls }}.SUNBELT.POLICY_SALE
UNION ALL SELECT 'NORTHFIELD.POLICY', 'row_count', COUNT(*) FROM {{ ls }}.NORTHFIELD.POLICY
{%- for t in vault_tables %}
{%- set rel = adapter.get_relation(database=db, schema='RAW_VAULT', identifier=t) %}
UNION ALL SELECT '{{ t }}', 'row_count', ({{ "SELECT COUNT(*) FROM " ~ db ~ ".RAW_VAULT." ~ t if rel is not none else "SELECT 0" }})
{%- endfor %}
UNION ALL SELECT 'DATA_FINDING', 'row_count', COUNT(*) FROM {{ ls }}.CONFIG.DATA_FINDING
UNION ALL SELECT 'DATA_FINDING', 'row_count|inspection', COALESCE(COUNT_IF(finder_type = 'inspection'), 0) FROM {{ ls }}.CONFIG.DATA_FINDING
UNION ALL SELECT 'DATA_FINDING', 'row_count|audit', COALESCE(COUNT_IF(finder_type = 'audit'), 0) FROM {{ ls }}.CONFIG.DATA_FINDING
{%- endmacro -%}
dbt/macros/verification/capture_baseline.sql
{%- macro capture_baseline() -%}
{#
Captures row counts before a build. Run first:
dbt run-operation capture_baseline
Writes <ls>.CONFIG.BUILD_BASELINE with snapshot_type = 'before', where <ls> is
landing_storage(). verify_build compares 'before' with 'after'.
The measured set is this vault's (increment 1 guide decision G-4).
#}
{% set db = target.database %}
{% set ls = landing_storage() %}
{# Clear the previous 'before' snapshot for this database #}
{% do run_query("
DELETE FROM " ~ ls ~ ".CONFIG.BUILD_BASELINE
WHERE snapshot_type = 'before' AND database_name = '" ~ db ~ "'
") %}
{#- The measured set lives in baseline_metrics (increment 2, guide step 32). -#}
{% do run_query("
INSERT INTO " ~ ls ~ ".CONFIG.BUILD_BASELINE (snapshot_type, table_name, metric_name, metric_value, database_name)
SELECT 'before', table_name, metric_name, metric_value, '" ~ db ~ "'
FROM (" ~ baseline_metrics(db, ls) ~ ")
") %}
{{ log("Baseline captured for " ~ db, info=True) }}
{%- endmacro -%}
dbt/macros/verification/verify_build.sql
{%- macro verify_build() -%}
{#
Captures the 'after' snapshot, compares it with capture_baseline's 'before', then
evaluates the declared expectations in seed_build_expectations. Run last:
dbt run-operation verify_build
WARN if a guardrail is violated (a table loses more than half its rows) or an
expectation is not met; LOOKOUT only when everything passes.
The measured set is this vault's (guide decision G-4); the expectations seed is read from REFERENCE, where
dbt_project.yml places seeds (2026-10-23; supersedes guide decision G-3).
#}
{% set db = target.database %}
{% set ls = landing_storage() %}
{% do run_query("
DELETE FROM " ~ ls ~ ".CONFIG.BUILD_BASELINE
WHERE snapshot_type = 'after' AND database_name = '" ~ db ~ "'
") %}
{#- The measured set lives in baseline_metrics (increment 2, guide step 32). -#}
{% do run_query("
INSERT INTO " ~ ls ~ ".CONFIG.BUILD_BASELINE (snapshot_type, table_name, metric_name, metric_value, database_name)
SELECT 'after', table_name, metric_name, metric_value, '" ~ db ~ "'
FROM (" ~ baseline_metrics(db, ls) ~ ")
") %}
{# ================================================================ #}
{# COMPARE BEFORE VS AFTER #}
{# ================================================================ #}
{% set comparison = run_query("
WITH before_snap AS (
SELECT table_name, metric_name, metric_value
FROM " ~ ls ~ ".CONFIG.BUILD_BASELINE
WHERE snapshot_type = 'before' AND database_name = '" ~ db ~ "'
),
after_snap AS (
SELECT table_name, metric_name, metric_value
FROM " ~ ls ~ ".CONFIG.BUILD_BASELINE
WHERE snapshot_type = 'after' AND database_name = '" ~ db ~ "'
)
SELECT
COALESCE(b.table_name, a.table_name) AS table_name,
COALESCE(b.metric_name, a.metric_name) AS metric_name,
b.metric_value AS before_value,
a.metric_value AS after_value,
COALESCE(a.metric_value, 0) - COALESCE(b.metric_value, 0) AS delta,
CASE
WHEN b.metric_value IS NULL THEN 'NEW'
WHEN a.metric_value IS NULL THEN 'GONE'
WHEN a.metric_value = b.metric_value THEN 'UNCHANGED'
WHEN a.metric_value > b.metric_value THEN 'INCREASED'
WHEN a.metric_value < b.metric_value THEN 'DECREASED'
END AS status,
CASE
WHEN b.metric_value IS NOT NULL AND b.metric_value > 0
AND (COALESCE(a.metric_value, 0) / b.metric_value) < 0.5
THEN 'GUARDRAIL'
ELSE ''
END AS alert
FROM before_snap b
FULL OUTER JOIN after_snap a
ON b.table_name = a.table_name
AND b.metric_name = a.metric_name
ORDER BY COALESCE(b.table_name, a.table_name), COALESCE(b.metric_name, a.metric_name)
") %}
{# ================================================================ #}
{# DISPLAY COMPARISON #}
{# ================================================================ #}
{{ log("", info=True) }}
{{ log("========== BUILD VERIFICATION: " ~ db ~ " ==========", info=True) }}
{{ log("", info=True) }}
{{ log("--- COMPARISON ---", info=True) }}
{% set failures = [] %}
{% for row in comparison %}
{% set tbl = row[0] %}
{% set metric = row[1] %}
{% set before = row[2] %}
{% set after = row[3] %}
{% set delta = row[4] %}
{% set status = row[5] %}
{% set alert = row[6] | trim %}
{% set before_str = before | string if before is not none else "—" %}
{% set after_str = after | string if after is not none else "—" %}
{% set delta_prefix = "+" if delta > 0 else "" %}
{% set line = tbl ~ " | " ~ metric ~ " | " ~ before_str ~ " -> " ~ after_str ~ " (" ~ delta_prefix ~ delta | string ~ ") " ~ status %}
{% if alert == 'GUARDRAIL' %}
{{ log(" !!! " ~ line ~ " — GUARDRAIL: >50% DROP", info=True) }}
{% do failures.append("GUARDRAIL: " ~ tbl ~ " " ~ metric ~ " dropped >50%") %}
{% elif status == 'NEW' %}
{{ log(" +++ " ~ line, info=True) }}
{% elif status == 'GONE' %}
{{ log(" --- " ~ line, info=True) }}
{% elif status != 'UNCHANGED' %}
{{ log(" >>> " ~ line, info=True) }}
{% else %}
{{ log(" " ~ line, info=True) }}
{% endif %}
{% endfor %}
{# ================================================================ #}
{# EVALUATE EXPECTATIONS #}
{# ================================================================ #}
{{ log("", info=True) }}
{{ log("--- EXPECTATIONS ---", info=True) }}
{% set expectations = run_query("
SELECT e.version_tag, e.table_name, e.metric_name, e.condition, e.description,
CAST(a.metric_value AS FLOAT) AS actual_value
FROM " ~ db ~ ".REFERENCE.SEED_BUILD_EXPECTATIONS e
LEFT JOIN " ~ ls ~ ".CONFIG.BUILD_BASELINE a
ON a.table_name = e.table_name
AND a.metric_name = e.metric_name
AND a.snapshot_type = 'after'
AND a.database_name = '" ~ db ~ "'
ORDER BY e.version_tag, e.table_name, e.metric_name
") %}
{% for row in expectations %}
{% set version_tag = row[0] %}
{% set tbl = row[1] %}
{% set metric = row[2] %}
{% set condition = row[3] | trim %}
{% set description = row[4] %}
{% set actual = row[5] %}
{% set passed = false %}
{% if actual is none %}
{% set passed = false %}
{% elif condition == '> 0' %}
{% set passed = (actual > 0) %}
{% elif condition.startswith('>= ') %}
{% set threshold = condition.replace('>= ', '') | float %}
{% set passed = (actual >= threshold) %}
{% elif condition.startswith('> ') %}
{% set threshold = condition.replace('> ', '') | float %}
{% set passed = (actual > threshold) %}
{# a declared decrease (Ray, 2026-09-11): the rebuild standard requires every removal of rows to be
declared; these two operators let the declaration be checked. Origin: the DAMA-76 rebuild. #}
{% elif condition.startswith('<= ') %}
{% set threshold = condition.replace('<= ', '') | float %}
{% set passed = (actual <= threshold) %}
{% elif condition.startswith('< ') %}
{% set threshold = condition.replace('< ', '') | float %}
{% set passed = (actual < threshold) %}
{% elif condition == 'increased' %}
{% set before_val = run_query("
SELECT CAST(metric_value AS FLOAT) FROM " ~ ls ~ ".CONFIG.BUILD_BASELINE
WHERE table_name = '" ~ tbl ~ "' AND metric_name = '" ~ metric ~ "'
AND snapshot_type = 'before' AND database_name = '" ~ db ~ "'
") %}
{% if before_val and before_val.rows | length > 0 %}
{% set passed = (actual > before_val.rows[0][0]) %}
{% else %}
{% set passed = (actual > 0) %}
{% endif %}
{% elif condition == 'unchanged' %}
{% set before_val = run_query("
SELECT CAST(metric_value AS FLOAT) FROM " ~ ls ~ ".CONFIG.BUILD_BASELINE
WHERE table_name = '" ~ tbl ~ "' AND metric_name = '" ~ metric ~ "'
AND snapshot_type = 'before' AND database_name = '" ~ db ~ "'
") %}
{% if before_val and before_val.rows | length > 0 %}
{% set passed = (actual == before_val.rows[0][0]) %}
{% else %}
{% set passed = false %}
{% endif %}
{% endif %}
{% set actual_str = actual | string if actual is not none else "NOT FOUND" %}
{% if passed %}
{{ log(" PASS [" ~ version_tag ~ "] " ~ tbl ~ " | " ~ metric ~ " | " ~ condition ~ " | actual: " ~ actual_str ~ " | " ~ description, info=True) }}
{% else %}
{{ log(" FAIL [" ~ version_tag ~ "] " ~ tbl ~ " | " ~ metric ~ " | " ~ condition ~ " | actual: " ~ actual_str ~ " | " ~ description, info=True) }}
{% do failures.append("EXPECTATION: [" ~ version_tag ~ "] " ~ tbl ~ " " ~ metric ~ " expected " ~ condition ~ " but got " ~ actual_str) %}
{% endif %}
{% endfor %}
{# ================================================================ #}
{# VERDICT #}
{# ================================================================ #}
{{ log("", info=True) }}
{{ log("--- VERDICT ---", info=True) }}
{% if failures | length > 0 %}
{{ log("", info=True) }}
{{ log("WARNINGS — " ~ failures | length ~ " issue(s):", info=True) }}
{% for f in failures %}
{{ log(" " ~ f, info=True) }}
{% endfor %}
{{ log("", info=True) }}
{{ log("Build did not stop. Review warnings; the goal is a lookout build (zero warnings).", info=True) }}
{{ log("", info=True) }}
{% else %}
{{ log("", info=True) }}
{{ log("LOOKOUT — all guardrails and expectations met. Zero warnings.", info=True) }}
{{ log("", info=True) }}
{% endif %}
{%- endmacro -%}
dbt/packages.yml
packages:
- git: "https://github.com/rayserrano2735/datavault4dbt.git"
revision: 694df249dd9ed7aa36ea1881c1c73f81e4e80feb # fork pinned at upstream 694df24
- git: "https://github.com/dbt-labs/dbt-utils.git"
revision: 49b932cb1f6fd0f7dfbf9048512db6415f570b20 # tag 1.4.1, pinned by commit; finding keys and layer 2 uniqueness (guide G-9)
# From GitHub, not the dbt hub: the container's egress gateway re-signs TLS and the
# local Fusion engine cannot download from the hub (2026-10-23). dbt_expectations is
# not used for the same reason; its dbt_date dependency resolves only through the hub.
# Both packages are pinned by exact commit here, so package-lock.yml is not tracked: each environment's
# dbt deps writes its own, and dbt Cloud (v1) and the workstation (Fusion) compute its hash differently.
dbt/selectors.yml
# Named selections, so environments run the right set by name instead of remembering exclusions (Ray, 2026-10-23).
selectors:
- name: v1_safe
description: >
Everything except the unit tests tagged fusion_only. On dbt v1 a unit test replaces its input with a
query block, and datavault4dbt's macros cannot describe it; those tests run on the Fusion engine only.
For dbt Cloud environments on v1: dbt build --selector v1_safe
definition:
union:
- method: fqn
value: "*"
- exclude:
- method: tag
value: fusion_only
- name: qa_build
description: >
The QA job's build: today the same set as v1_safe, written out in full (a selector that only names
another selector selected everything when tested on 2026-10-23). Its own name lets the QA job diverge later.
definition:
union:
- method: fqn
value: "*"
- exclude:
- method: tag
value: fusion_only