ADR 028 — dbt Test Policy: What to Test and Where¶

Status¶

Accepted

Context¶

As of May 2026, the xo-medallion project had 677 test definitions across Silver, Gold, and Bronze source files:

• 94 tests in Bronze _sources.yml files (never executed — no Airflow task runs source tests)
• 225 tests in Silver schema.yml files
• 358 tests in Gold schema.yml files

The majority were not_null tests on derived metric columns in agg_ and rpt_ models — columns that are legitimately NULL by design for many records (e.g., AI_WAVE for agents not on the AI program, CSAT_SCORE when no survey was received). These tests either always passed (zero signal) or generated false failures, and added Snowflake compute cost on every dbt test run.

A secondary issue: several fct_ models (e.g., fct_agent_summary_channel) were architecturally misclassified — they have a composite grain (date, eid, channel) with no single-column primary key, which is the defining characteristic of an agg_ model, not a fact.

Decision¶

Canonical Test Policy¶

Tests belong at the layer where data enters a new structural guarantee. Repeating tests downstream gives false confidence and wastes compute. The rule: test once, at the right layer.

Bronze Source Tests: Drop¶

No Airflow task runs dbt test --select source:*. Bronze source tests have never executed in production. Silver's not_null on TRY_TO_* cast structural columns provides equivalent signal — a failed cast surfaces as NULL in Silver, caught by the Silver test. Dead tests are removed entirely rather than left as unmaintained code.

TRY_TO_* Cast Columns¶

When Silver uses TRY_TO_TIMESTAMP_NTZ(col) or TRY_TO_NUMBER(col), a bad source value returns NULL silently. For columns where NULL indicates a real data quality failure (e.g., CREATED_AT — every contact must have a creation time), add not_null to the Silver column. For columns that are optional by business design (e.g., CSAT_SCORE, HANDLE_TIME), do not add not_null — the NULL is expected.

Summary-Level fct_ → agg_ Reclassification¶

fct_ models must have a single-column natural primary key at event grain (one row per contact, evaluation, etc.). Models with a composite grain of (date, eid, channel) are aggregates, not facts. New models matching this description must be named agg_* from the start.

Existing models — four summary-level models were identified during this audit: fct_wbp_agent_channel_summary, fct_wbp_agent_summary_daily, fct_cnd_agent_channel_summary, fct_cnd_agent_summary_daily. These retain their current names to avoid downstream churn. A surrogate FACT_KEY column (RECORD_KEY || '|' || CHANNEL for channel-level models) serves as the single-column PK to satisfy the test policy. Renaming to agg_* is deferred as a separate architectural cleanup.

No dbt_utils Dependency¶

The unique_combination_of_columns test from dbt_utils is not needed: - Event-level fct_ models have a single natural PK — standard unique + not_null works - Summary-level models are reclassified as agg_ — composite uniqueness is not tested at agg_, only not_null on grain columns

Consequences¶

• Test suite runs measurably faster (target: >50% reduction in test count)
• False failures from not_null tests on legitimately-nullable metric columns are eliminated
• Bronze source test files (_sources.yml) are cleaned of all test blocks — source definitions remain for source() references
• Four existing summary-level fct_ models receive a surrogate FACT_KEY PK to satisfy the test policy; rename to agg_* is deferred
• New models added after this ADR must follow this policy; Claude Code enforces it via dbt.md

Options Considered¶

Keep all tests, fix false failures by adding warn severity — Rejected. Warn-severity tests generate noise without actionable signal. They suggest "something to investigate" rather than a real assertion. The underlying issue is that the tests are wrong for the layer, not that the severity needs adjustment.

Add dbt_utils for composite PK testing on summary fct_ models — Rejected in favor of reclassifying those models as agg_. The composite grain is the structural indicator of an aggregate, not a fact.

Add a dedicated Bronze source test task to Airflow — Out of scope for this chore. If source freshness monitoring is needed in the future, it should be a separate initiative using dbt source freshness (source.yml freshness blocks), not column-level not_null tests.