Data Expectations and Assertions

• Write executable assertions for the properties consumers depend on
• Choose strict failures vs warnings for different classes of check
• Classify expectations into schema, volume, metric and content-level checks

• Consumers stop discovering broken data hours after the fact — checks fail at the boundary, not in the dashboard.
• Assertions encode tribal knowledge ('pmt_expected_range comes from the legacy feed') into something re-runnable.
• A failing check pins blame to the exact property and dataset, cutting root-cause time.
• They make 'ship it anyway' an explicit decision with a visible cost, not a silent default.

The inspector doesn't re-design the building; they verify each load-bearing rule — floors hold weight, wiring is grounded, exits exist. Expectations verify the rules data must hold; only the failing rule gets the repair crew.

• Schema assertions: the column exists, has the right type, is not nullable where it must be.
• Volume assertions: row counts are within an expected range, with no zero or absurd spikes.
• Metric assertions: SUM/AVG/COUNT on key metrics are within bounds (e.g. revenue > 0, never negative).
• Content assertions: values belong to allowed sets, formats parse, keys refer to existing parents.
• Statistical assertions (expectations suites): distributions fit baselines, drift in percentile ranks is within tolerance.
• Severity, not boolean: warnings don't block, failures do — each assertion carries a severity and an owner.

• A media company runs 40 assertions on its daily ad-impressions table; the run stops when count-of-rows or count-of-unique-id violates, so downstream dashboards never see partial data.
• A fintech enforces non-null and positive-amount assertions on the payments landing table per batch, failing before the dbt models execute.
• dbt teams mark 'warn_if' thresholds for row counts and 'error_if' for critical uniqueness, so minor skew raises an issue without hard-failing the DAG.

• Assert the grain first: a 'species' count vs expected always catches double-joins before downstream aggregations.
• Version expectations alongside pipeline code in the same repository.
• Write expectations from observed distributions first — reject ranges you've actually seen, then tighten.
• Make thresholds reference expectations (baseline window) rather than hand-picked magic numbers.

• Making every check a hard failure so teams tune alerts off or hoist them — signal loss.
• Only asserting counts and never content, so structurally fine junk flows downstream.
• Hard-coding thresholds that become wrong as the business grows, triggering noisy red runs.
• Checking post-upstream data but never the raw source, so root causes hide.

• When an expectation fails, look at the tail of the failing values — the first 5 offenders often share the root cause.
• Cross the failing assertion with its dimension: null spike (completeness), duplicate spike (uniqueness), range violation (validity).
• Re-run the batch on a single partition to separate a data defect from an infra hiccup.
• Compare the failing table's schema hash with last known-good to catch upstream contract drift.