ADR-055: SD06 test layering — dbt unit tests for SQL behaviour, Vitest policy tests for SQL structure

Status	Proposed
Date	2026-05-04
Deciders	CTO, Head of Risk Engineering
Affects repos	bank-risk-platform

---

Status: Proposed

Context

bank-risk-platform (SD06) modules contain three categories of test: TypeScript Vitest unit tests, Vitest integration tests against deployed AWS/Snowflake, and Vitest policy tests that assert each policies_satisfied row in the module's wiki entry.

During the MOD-038 design review it became clear that the policy test layer had drifted into a pattern that conflates structural assertions with behavioural assertions. The pattern used regex matches against dbt SQL source files:

// From MOD-098 REP-001-calc-correctness.test.ts
expect(sql).toMatch(
  /attribution_mode\s*=\s*['"]dedicated['"][\s\S]+?warehouse_total_credits\s*\*\s*credit_unit_cost_usd/i,
);

This test passes if the SQL file literally contains that text in that order. It fails on a semantically-equivalent refactor (IFF(attribution_mode = 'dedicated', ...)) and passes on a real bug that doesn't change the regex match. The claim that this test asserts FR-394 dedicated-warehouse arithmetic is overstated — it asserts only that the source file contains certain tokens in a certain order.

dbt 1.8 introduced unit tests that compile and execute a model against fixture input rows and assert the output rows match an expected set. This is the correct tool for SQL behavioural assertions. It runs as part of dbt build with no additional CI step required.

Decision

Test layering

All SD06 modules adopt the following test taxonomy from V1 onward. Existing modules (MOD-085, MOD-098) are retrofitted as part of the ADR-049 Lambda-to-Alert migration pass.

Layer	Tool	What it proves
TypeScript code	Vitest unit	TypeScript handler and domain logic correctness
dbt SQL behaviour	dbt unit tests (1.8+)	The model's SELECT produces correct output for given fixture inputs
Output data quality	dbt schema tests	not_null, unique, accepted_values, relationships, custom singular tests
Source freshness	dbt source freshness	loaded_at_field thresholds on raw CDC sources
Deployed infrastructure	Vitest integration	Deployed Snowflake objects and AWS resources match IaC declarations
Static SQL structure	Vitest policy	Source file encodes the policy requirement structurally

The split between Vitest policy and dbt unit tests

A test belongs in Vitest policy if and only if it asserts something about the source file that is not observable from the model's compiled output:

• governance_meta_grants.sql contains a REVOKE UPDATE/DELETE clause (GOV-006 LOG immutability — verifiable by reading the file).
• unattributed_costs.sql references source('metering', 'config') for the threshold rather than a hardcoded literal (DT-004 AUTO — verifiable by reading the file).
• A DDL file uses CREATE OR REPLACE or CREATE ... IF NOT EXISTS (idempotency contract — verifiable by reading the file).

A test belongs in dbt unit tests if it asserts something about the behaviour of a transformation:

• Given a fixture with attribution_mode='dedicated', the output row's attributed_credits equals warehouse_total_credits (FR-394 arithmetic).
• Given a fixture where unattributed_share = 0.04 and the threshold from the config row is 0.05, the output exceeds_threshold is FALSE (FR-396 boundary).
• Given five carry-forward-TRUE rows and no FALSE rows, consecutive_carry_forward_days is 5 (FR-384 streak count).

Litmus test: if a semantically-equivalent SQL refactor would break the test, it is in the wrong layer. Behaviour tests survive refactors. Structure tests are deliberately tied to file content.

dbt unit test syntax (1.8+)

Stored alongside the model in schema.yml:

unit_tests:
  - name: dedicated_warehouse_attributes_full_credits
    model: snowflake_credit_daily
    given:
      - input: source('account_usage', 'warehouse_metering_history')
        rows:
          - {start_time: '2026-04-29 10:00', warehouse_name: 'TENANT_A_WH', credits_used: 42}
    expect:
      rows:
        - {tenant_id: 'tenant-A', attribution_mode: 'dedicated', attributed_credits: 42}

Unit tests run automatically as part of dbt build. No new CI step required.

dbt singular tests for cross-table assertions

dbt singular tests (tests/<custom>.sql) are appropriate for cross-table consistency checks (e.g., FR-227 reconciliation correctness) where the assertion returns 0 rows on success. Both unit tests and singular tests are part of the dbt build suite.

Coverage expectations

Layer	Typical count per module
Vitest unit	0 (monitoring-only modules) to 30+ (Lambda-heavy modules)
Vitest policy	5–10, scoped to structural file assertions only
Vitest integration	8–15, deployed-shape verification
dbt schema tests	10–30 (one or two per published column)
dbt unit tests	5–15 (one per non-trivial CASE / JOIN / aggregation branch)
dbt source freshness	5–10 (one per CDC source)

The existing ≥80% line coverage threshold in CLAUDE.md applies to TypeScript Vitest tests only. dbt coverage is measured as model coverage — the number of non-trivial models with at least one dbt unit test.

Reference implementation

MOD-038 (data quality monitor) is the first module built with this taxonomy from V1. Its dbt/models/MOD-038-data-quality-monitor/schema.yml contains dbt schema tests, dbt unit tests, and source freshness declarations. Its tests/policy/ directory contains only structural file assertions.

Consequences

Positive: - Behaviour tests are refactor-resilient. A valid SQL rewrite no longer breaks a test. - Policy tests are scoped correctly — the file content they assert is the actual policy evidence (a REVOKE clause, a source reference, an idempotency marker). - dbt unit tests use native dbt tooling. No additional CI step, no extra dependency. - CALC-mode policy obligations that depend on arithmetic correctness are now genuinely verified at the SQL execution layer.

Negative: - dbt unit tests consume a small amount of Snowflake warehouse credits per CI run. - MOD-085 and MOD-098 require a one-time retrofit (bundled with the ADR-049 Lambda-to-Alert migration pass — not a standalone effort).

Alternatives considered

• Keep Vitest regex tests. Rejected — they provide a false sense of behavioural coverage while being brittle to valid refactors.
• Skip SQL behavioural testing entirely. Rejected — CALC-mode policy obligations require behavioural assertions; leaving them untested is a compliance gap.

Cross-references

• ADR-046 §2 — dbt owns all SD06 transformations; this ADR assigns test responsibilities consistent with that ownership.
• ADR-049 — Snowflake-native compute; the test refactor for MOD-085/098 is bundled with the Lambda-to-Alert migration.

---

All ADRs Compiled 2026-05-22 from source/entities/adrs/ADR-055.yaml