Section D · Schema evolution & quality

Quality Tests & dbt Patterns

dbt tests for the contract data model — uniqueness, referential integrity, business-logic assertions, freshness. Materialization patterns for the marts layer that the customer's analysts query.

Why dbt tests matter here

The contract data model is where the customer's BI dashboards, ERP integrations, and renewal alerts read from. If a row in agreements has a wrong current_term_end, every downstream consumer is wrong. Tests catch the wrongness before it ships.

The discipline:

Tests run on every model materialization.
CI fails when tests fail.
Production failures alert; data isn't promoted to marts until tests pass.

Schema tests

The basics — uniqueness, not-null, accepted-values. Defined in YAML alongside the model.

models/marts/_models.yml

version: 2

models:
  - name: dim_agreements
    description: Current view of agreements (latest extraction per agreement_id)
    columns:
      - name: agreement_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: supplier_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_suppliers')
              field: supplier_id
      - name: agreement_type
        tests:
          - not_null
          - accepted_values:
              values: ['master_service_agreement', 'statement_of_work',
                       'amendment', 'nda', 'dpa', 'baa',
                       'supplier_agreement', 'invoice', 'purchase_order']
      - name: status
        tests:
          - not_null
          - accepted_values:
              values: ['active', 'expired', 'terminated', 'superseded', 'draft']

  - name: fct_obligations_current
    description: Latest obligation per agreement+type (non-superseded)
    columns:
      - name: obligation_id
        tests:
          - unique
          - not_null
      - name: agreement_id
        tests:
          - relationships:
              to: ref('dim_agreements')
              field: agreement_id
      - name: confidence
        tests:
          - dbt_utils.expression_is_true:
              expression: "confidence BETWEEN 0 AND 1"

Business-logic tests

Tests that enforce domain rules, not just structural constraints.

No agreement without parties

tests/no_agreement_without_parties.sql

-- This test returns rows that FAIL the assertion.
-- Empty result = passing test.
SELECT a.agreement_id
FROM {{ ref('dim_agreements') }} a
LEFT JOIN {{ ref('fct_extractions_current') }} e
  ON e.document_id IN (
    SELECT document_id FROM {{ ref('document_agreement_links') }}
    WHERE agreement_id = a.agreement_id AND role = 'master'
  )
WHERE a.status = 'active'
  AND (e.fields->'parties' IS NULL
       OR jsonb_array_length(e.fields->'parties'->'value') < 2);

Current_term_end is after effective_date

SELECT agreement_id
FROM {{ ref('dim_agreements') }}
WHERE effective_date IS NOT NULL
  AND current_term_end IS NOT NULL
  AND current_term_end <= effective_date;

Obligation amount is positive

SELECT obligation_id
FROM {{ ref('fct_obligations_current') }}
WHERE obligation_type = 'payment'
  AND (value->>'amount')::NUMERIC <= 0;

No spend with non-existent supplier

SELECT s.spend_id
FROM {{ ref('fct_spend') }} s
LEFT JOIN {{ ref('dim_suppliers') }} sup ON sup.supplier_id = s.supplier_id
WHERE sup.supplier_id IS NULL;

No active agreements ending in the past

-- Suggests a status update job is broken
SELECT agreement_id, current_term_end
FROM {{ ref('dim_agreements') }}
WHERE status = 'active'
  AND current_term_end < CURRENT_DATE - INTERVAL '7 days';

Freshness

dbt's source freshness tests fail if the upstream raw source hasn't received new data recently.

models/staging/_sources.yml

sources:
  - name: contract_intelligence_raw
    schema: raw
    tables:
      - name: documents
        loaded_at_field: ingested_at
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 72, period: hour}
      - name: extractions
        loaded_at_field: extracted_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 48, period: hour}

Row-count thresholds

Detect silent data outages. Custom dbt test or dbt_utils macro.

tests/spend_volume_baseline.sql

-- Fail if last week's spend volume is < 50% of the 30-day average
WITH last_week AS (
  SELECT COUNT(*) AS n
  FROM {{ ref('fct_spend') }}
  WHERE accrued_at >= CURRENT_DATE - INTERVAL '7 days'
),
baseline AS (
  SELECT COUNT(*) / 30.0 * 7 AS expected
  FROM {{ ref('fct_spend') }}
  WHERE accrued_at BETWEEN CURRENT_DATE - INTERVAL '37 days'
                        AND CURRENT_DATE - INTERVAL '8 days'
)
SELECT 1 WHERE (SELECT n FROM last_week) < (SELECT expected FROM baseline) * 0.5;

Materialization patterns

Staging layer (views)

One staging model per raw source. Light cleaning, no joins, type coercion. Materialized as views — cheap, always fresh.

Intermediate layer (views or ephemeral)

Joins between sources; entity resolution; date manipulation. Often ephemeral (inlined into downstream queries) for query performance.

Marts layer (tables / incremental tables)

The customer-facing layer. dim_agreements, dim_suppliers, dim_documents, fct_obligations_current, fct_spend. Materialized as tables for query performance.

For large fact tables (fct_spend on a large customer), incremental materialization:

models/marts/fct_spend.sql

{{
  config(
    materialized='incremental',
    unique_key='spend_id',
    on_schema_change='append_new_columns'
  )
}}

SELECT
  s.spend_id, s.customer_id, s.supplier_id, s.agreement_id,
  s.amount, s.currency, s.accrued_at, s.spend_category,
  s.amount * fx.rate AS amount_usd
FROM {{ source('contract_intelligence_raw', 'spend') }} s
LEFT JOIN {{ ref('dim_fx_rates') }} fx
  ON fx.from_currency = s.currency AND fx.rate_date = s.accrued_at

{% if is_incremental() %}
  WHERE s.accrued_at > (SELECT MAX(accrued_at) FROM {{ this }})
{% endif %}

Project layout

models/
├── staging/
│   ├── _sources.yml
│   ├── stg_documents.sql
│   ├── stg_extractions.sql
│   ├── stg_suppliers.sql
│   ├── stg_agreements.sql
│   └── stg_spend.sql
├── intermediate/
│   ├── int_extractions_current.sql      # latest extraction per document
│   ├── int_obligations_current.sql      # latest obligation per agreement+type
│   ├── int_supplier_hierarchy.sql       # recursive parent resolution
│   └── int_spend_with_fx.sql
└── marts/
    ├── _models.yml
    ├── dim_customers.sql
    ├── dim_suppliers.sql
    ├── dim_documents.sql
    ├── dim_agreements.sql
    ├── fct_obligations_current.sql
    ├── fct_spend.sql
    └── analytics/
        ├── renewal_pipeline.sql         # business-facing rollups
        ├── off_contract_spend.sql
        └── supplier_consolidation_opportunities.sql

The marts/analytics subdirectory is where customer-facing rollups live. dbt models, not raw queries — the customer's analysts can extend them.

Running & alerting

CI: every PR runs dbt build (run + test) against a sample dataset. Failures block merge.
Production schedule: dbt builds on an Airflow / Dagster schedule, typically nightly with hourly incremental updates for fct_spend.
Test failures: alert to ops Slack channel; SEV-3 by default; promote to SEV-2 if the failing test blocks customer-facing dashboards.
Source freshness warnings: route to a daily digest, not paging.
Documentation: dbt docs site generated and published; customer's data team has access. The data dictionary lives here.