data-pipeline-quality

Testing Pyramid for Data

Run tests in this order. Cheapest and fastest first:

Layer	What It Catches	Examples
Schema tests (run first)	Structural failures	Column types, not-null, uniqueness, accepted values
Business rule tests	Logic errors	Cross-field validation, referential integrity, range checks
Integration tests (run last)	System-level drift	Cross-system reconciliation, end-to-end row counts

Schema tests are cheap. Run them on every pipeline execution. Business rule tests are mid-tier — run them on staging and production. Integration tests are expensive — run them on a schedule (daily or pre-release).

dbt Test Patterns

Generic tests for reusable checks. Apply across models:

models:
  - name: fct_encounters
    columns:
      - name: encounter_id
        tests: [not_null, unique]
      - name: encounter_type
        tests:
          - accepted_values:
              values: ['inpatient', 'outpatient', 'emergency', 'observation']
      - name: patient_id
        tests:
          - relationships:
              to: ref('dim_patient')
              field: patient_id

Custom generic test for row count tolerance:

{% test row_count_within_tolerance(model, min_count, max_count) %}
select count(*) as row_count
from {{ model }}
having count(*) < {{ min_count }} or count(*) > {{ max_count }}
{% endtest %}

Singular tests for business logic specific to one model. Use singular tests when the logic doesn't generalize.

Data Contracts

A data contract is a product spec for your data. It defines what consumers can depend on.

contract:
  name: fct_encounters
  version: 2
  owner: data-platform-team
  sla:
    freshness: "< 4 hours from source update"
    completeness: ">= 99.5% of expected rows"
    accuracy: ">= 99.9% match to source of record"
  schema:
    encounter_id: {type: bigint, nullable: false, unique: true}
    patient_id: {type: bigint, nullable: false}
    encounter_date: {type: date, nullable: false}