Where to put data quality checks: write-time, transform-time, continuous

A data quality failure rarely announces itself. The server stays up. The job exits zero. The row count looks plausible. Then a week later someone in finance asks why the revenue number is off, and you trace it back to a source that quietly stopped sending one region's events six days ago. The number had been wrong in the dashboard the whole time, and nothing you'd built to catch bad data had said a word — because none of it was looking for data that simply didn't arrive.

That gap — between "the pipeline ran" and "the pipeline produced correct data" — is where quality checks live. The question isn't whether to check. It's where. A check can run in one of three places, and the place decides what it can see. Get the placement wrong and you can have thorough coverage on paper and still be blind to the failure that reaches your users.

The three places are: as a row is written, as the pipeline transforms it, and continuously against the table the data lands in. The first two run inside the system that produces your data. The third runs outside it, on its own clock. Most teams cover the first two well and leave the third empty — and the third is the one that catches the region that went quiet.

This post is about where checks belong. For one failure walked end to end — a payments webhook that silently stopped recording charges — the payments-webhook deep dive takes a single continuous check from symptom to alert. And if you're deciding what to actually run these on, the data quality tools comparison goes vendor by vendor, cron included.

Write-time: constraints, at the moment of insert

The cheapest layer is the one already installed: your database. A NOT NULL, a foreign key, a CHECK, a unique index — these reject a malformed row at the moment it's inserted, cost nothing to run, and never drift out of date.

-- Toy schema: orders(id, customer_id, status, amount).
-- Adapt the table and column names to yours — this is the shape, not a check to paste.

-- Every order belongs to a real customer
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id);

-- Status has to be one of the values the app knows about
ALTER TABLE orders
  ADD CONSTRAINT orders_status_valid
  CHECK (status IN ('placed', 'shipped', 'completed', 'returned'));

Add these first, whatever else you do. Postgres lets you add a CHECK or foreign key as NOT VALID so it's enforced for new writes immediately, then VALIDATE CONSTRAINT once you've cleaned up the history. ClickHouse is deliberately weaker — it accepts CONSTRAINT ... CHECK on insert but enforces neither foreign keys nor uniqueness, so more of the checking moves downstream.

The limit is the whole point of this layer. A write-time constraint can only judge a row that arrives. The source that stopped sending events violated nothing, because there was no row to violate anything. Absence is invisible at write time, by construction.

Transform-time: tests, each time the pipeline runs

The second layer asserts your assumptions as data moves through the pipeline. If your data flows through dbt, its tests are the cheapest coverage you'll add this year — the generic ones (unique, not_null, accepted_values, relationships) are a few lines of YAML next to the model, and singular tests are plain SQL that fails when it returns rows. Great Expectations is the heavyweight open-source version of the same idea, at the cost of hosting and scheduling it yourself.

This layer catches a real class of bug: a transform that starts producing wrong values, a source whose shape changed, a join that began dropping rows. Catch it in CI before it ships and the test pays for itself many times over.

But look at when it runs. A dbt test runs when dbt runs. A source can break a minute after the morning run, and nothing looks again until tomorrow's. Worse, these tests live inside the orchestrator — if the orchestrator itself stops, there's no run, so there's no failing run, so nothing fires. And a transform test only ever sees tables the pipeline touches; the table your backend writes to directly was never in the DAG to begin with. A test that runs inside the pipeline cannot report that the pipeline didn't run.

Continuous: checks against production, on their own schedule

Write-time and transform-time checks share a blind spot: they only fire when your code runs. A CHECK constraint validates a row at insert. A dbt test validates a model after it builds. Both are silent about everything that happens between runs — a source that stops delivering, an upstream schema change, a slow drift in a distribution.

Continuous checks run against production state on their own schedule, independent of any job. They ask questions like: has orders received a row in the last hour? and is today's per-region count within range of the trailing baseline? These don't belong in write-time or transform-time because the failure they catch isn't triggered by your code at all. It's a row that should be there and isn't — and only a check that reasons about expected presence, run from outside the pipeline, ever sees it.

The one pattern worth carrying to your own schema is freshness: the newest row is older than it should be. It needs no knowledge of why the data went stale — broken auth upstream, a dead scheduler, a paused job — only that the freshest row is past its expected cadence.

-- Toy schema: listings(id, region, updated_at). Pattern, not a check to paste —
-- your table, your cadence, your margin.
SELECT MAX(updated_at) AS freshest_row
FROM listings;
-- Alert if freshest_row < NOW() - INTERVAL '25 hours'

The threshold is one expected cadence plus a margin: a nightly import gets 25 hours, not 24, so a run that starts a little late doesn't trip it. Run it hourly and a stale source surfaces the morning it happens instead of after a customer emails. The transferable move is that the check derives "what should be here" from the data's own recent behaviour, not from a value it inspects — which is exactly why it's the only layer that catches an absence. (A partial failure — one region drops while the totals look fine — needs a check that groups by the entity; that's the shape the payments-webhook deep dive builds up in full.)

Placing the checks you already run

The useful exercise is to take every check you run today, drop it into one of the three layers, then ask which failure class is left uncovered.

If your data quality story is constraints plus dbt tests, you have the first two layers and a real hole under them. Both run inside the pipeline that produces your data, so both go dark in exactly the failure that matters most: the one where the pipeline stops. The row that never arrived isn't there to be rejected or transformed. The third layer is the only one that compares the data that should be there against what landed — and it's the one most teams discover they're missing the morning a customer finds the gap first.

Where to start

The continuous layer is the one you can't get from a constraint or a dbt test, and it's plain SQL you can run anywhere. A cron job that runs a query, compares it to a threshold, and posts to Slack is the honest floor — free, entirely yours, and the right call for the first handful of checks. What erodes is everything around the query: checks scatter across repos until nobody can list what's monitored, there's no history, and alerts land in a channel that belongs to everyone and so to no one.

Alertee runs SQL checks against your Postgres or ClickHouse on a schedule and turns failures into owned inbox items, so that's where our interest lies — but the layer matters more than the tool, and every check above runs equally well from psql or a cron line. Connect a read-only replica and write your first continuous check in a few minutes — free tier, no card. If you need lineage or learned anomaly models across a warehouse, that's a different aisle; the data quality tools comparison weighs those.