Data Quality: Fail the Job, Open a Ticket, or Ignore Red Forever

Forty-seven dbt tests had been red for so long that Slack stopped threading them. The one that mattered — unique_order_id on fct_orders — was red for 11 days. A retry had doubled a partition. Finance published $2.4M twice. The test did its job. The policy did not: red meant “someone will look” and nobody did.

I already wrote the Python primitives in quality checks and where they belong on the write path in data contracts. This post is the operations policy: which four checks first, when the job dies, when a ticket is enough, and what to do with tests that stay red.

The four that pay for themselves

1. Freshness — is the newest timestamp inside the SLA?
2. Volume — is the row count inside a band, not an exact count?
3. Schema — did columns, types, or nullability change?
4. Uniqueness — is the business key unique on the slice you just wrote?

Nulls and ranges are fifth. I still lead with these four because every ugly incident I have explained was one of them: empty sync, late file, renamed column, duplicated grain.

flowchart TD
  batch["New batch"] --> f["Freshness"]
  f --> v["Volume"]
  v --> s["Schema"]
  s --> u["Uniqueness"]
  f -->|SLA break| fail["Fail job + page"]
  v -->|zero / 10x| fail
  s -->|breaking| fail
  u -->|dup keys| fail
  v -->|soft anomaly| tix["Ticket, load gold"]
  s -->|additive| tix

Contract breaks fail. Soft anomalies ticket. “Always red” is not a third option.

from datetime import datetime, timezone, timedelta

def evaluate(row_count: int, max_ts: datetime, unique_violations: int,
             schema_breaking: bool, weekday: bool) -> str:
    now = datetime.now(timezone.utc)
    stale = max_ts < now - timedelta(hours=26)
    empty = weekday and row_count == 0
    if schema_breaking or unique_violations or stale or empty:
        return "fail"
    if row_count > 10 * 80_000:  # 80k = 30-day weekday median
        return "ticket"
    return "pass"

Fail means the orchestrator stops before BI reads the table. Ticket means gold still updates and an owner gets a due date. I do not quarantine uniqueness failures into gold; I quarantine messy-but- useful rows (bad emails, out-of-range amounts) when the grain is still honest.

Permanently red is a culture bug

A test that has failed for 11 days is not a test. It is noise. I cap the “accepted” red list at zero. If a test is wrong, delete it or fix the threshold. If the data is wrong, fix the data or fail the job. The $2.4M duplicate lived in a project with 47 red tests because we used warn-severity for everything “until we clean up.” We never cleaned up.

Severity I actually use:

• Fail the job: uniqueness, required schema, PII contract, freshness on tables that close the books.
• Ticket, do not block: volume 3× median on a table with known promo spikes, a new optional column, a source we cannot page.
• Do not add: a test nobody can name an owner for.

dbt severity: warn on a unique test is how we got 11 quiet days. Warn is for tickets. Unique is fail.

dbt versus GX versus a vendor

dbt tests are the right place for grain and relationships on models you own. They run after the model writes. Pair them with source freshness. They will not see a silent empty extract unless you add that job.

Great Expectations / Soda / a Python module belong before load when the pipeline is not dbt, or when you need to fail the producer. Same four checks. Different runtime.

Warehouse observability vendors (Monte Carlo-class) earn the bill when you have hundreds of tables, weak ownership, and misses that are already business incidents. Twenty marts and a tight dbt CI: start with tests and freshness. Buy when the miss rate is the incident, not when a conference booth says “observability.”

Incremental models need the check on the slice and a periodic full-table unique — a slice can look clean while the table has duplicates from last Tuesday. That pairing is in dbt incrementals.

-- dbt: unique fails the run. warn is a ticket, not a unique test.
-- models/marts/fct_orders.yml
--   tests:
--     - unique:
--         column_name: order_id
--         config:
--           severity: error
--           meta: { owner: "orders-platform" }

SELECT order_id, count(*) AS n
FROM {{ ref('fct_orders') }}
GROUP BY 1
HAVING count(*) > 1;

I put meta.owner on every test that can fail the DAG. PagerDuty goes to that rotation, not to #data-quality. A channel everyone watches and nobody owns is how 11 days happen.

Pitfalls

Warn-severity on uniqueness. You trained the team that red is wallpaper. Unique is stop. Volume anomalies can ticket.

Four hundred tests, twelve owners, no policy. People mute Slack. Start with the four checks on the ten tables that close the books. Add tests when you add an owner.

Only warehouse tests. The empty extract never reached dbt. A freshness job on the raw table, or a contract on the write path, would have caught Stripe-at-zero. Tests after load are the second line.

Buying a vendor to replace a policy. The vendor will find more red. Without fail-versus-ticket you will ignore those too.

Checking only the incremental slice. Last Tuesday’s duplicates stay. Weekly full-table unique on facts that money touches.

The finance incident closed with two changes: unique_order_id fails the DAG, and a weekly job that fails CI if more than zero tests are red on main. Policy on one page. The four checks. An owner per table. Red means stop or ticket, never wallpaper.