authoring-data-quality-checks

Authoring data quality checks

A check is one assertion about one warehouse table, view, or HogQL catalog metric. It compiles to a count-only HogQL query and passes when it finds zero failing rows, like dbt test. Failing rows are never stored; only counts and the compiled query are, so to see the offending rows you re-run the stored query yourself.

row_count is the exception. It passes when the observed count is within its configured min/max bounds, so its failed_row_count comes back null and its stored query returns that single count, not offending rows. Read the observed count to judge it rather than looking for matched rows.

Reads go through SQL (system.information_schema.data_quality_*); writes and runs go through the data-quality MCP tools.

Before you write anything: look

Two queries save you from the two most common mistakes — duplicating a check, and checking a column that doesn't exist.

-- What is already covered?
SELECT name, subject_name, column_name, check_type, config, severity, last_status
FROM system.information_schema.data_quality_checks
WHERE subject_name = 'orders'

-- What columns are there, and what do they mean?
SELECT column_name, data_type, description
FROM system.information_schema.columns
WHERE table_name = 'orders'

Re-creating a byte-identical check is a harmless no-op — checks are keyed by a fingerprint of the subject, type, column, and config, so an identical create upserts. A near-duplicate is not harmless: it doubles the noise for whoever reads the results. If an existing check's assertion is close but wrong, edit the existing check. Updates preserve its identity and history; the subject stays fixed by the URL. An edit that duplicates another check's assertion is rejected.

Choosing checks

Aim for a handful that would actually catch a real regression, not blanket coverage. A model with twenty checks nobody reads is worse than three that fail meaningfully.

Reach for these first, in roughly this order:

• not_null on the columns downstream joins and filters depend on. The single highest-value check. A null join key silently drops rows.
• unique on whatever the model claims is its grain. If orders is one row per order, say so.
• relationships on foreign keys. Catches the join that quietly stopped matching after an upstream change.
• accepted_values on status and category columns whose downstream logic branches on them.
• freshness on the timestamp column of anything that syncs. Catches a dead pipeline, which no row-level check will.
• row_count bounds when you know the plausible range. Good for catching a truncated sync.
• custom_sql only when nothing above expresses the invariant — e.g. cross-column arithmetic (select 1 from orders where total != subtotal + tax). Every row it returns counts as a failure.

Call posthog:data-quality-check-types for each type's exact config schema rather than guessing.

One tool set covers every kind of subject. Call posthog:data-quality-subjects for the tables, views and metrics you can read, then pass the subject_type and id it gives you as subject_type and subject_uuid in posthog:data-quality-check-create. Only a subject marked editable can carry a check; the others can still be the target of a relationships check. After that a check is addressed by its own id: -update, -delete, -run and -results take no subject.

Checks on PostHog tables

events, persons and groups take checks like any other subject: pass subject_type: "posthog_table" with the id posthog:data-quality-subjects gives you. Every check type works on them, and relationships can point at one as its target too.

These tables are large, so consider lookback_hours before you author a check on one. It bounds the rows the check reads by the table's own time column: timestamp on events, and created_at on persons and groups, which is when each was first seen rather than when it last changed. Without it the check reads the whole table, which is allowed and sometimes what you want -- an unbounded unique on events.uuid says something a windowed one cannot. A check that runs out of ClickHouse's execution budget reports errored with the message; adding a window is usually the fix.

On a relationships check, lookback_hours bounds the rows it checks and to_lookback_hours bounds the rows it looks for a match among. Set the second one carefully: a narrow target window makes rows fail for being old rather than for being wrong.

custom_sql takes no lookback_hours. Put the time filter in the query yourself, or it reads the whole table.

Nothing triggers these tables the way a sync triggers a source table, so their checks run on a schedule, daily by default. Change it with posthog:data-quality-check-schedule.

Checks on catalog metrics

Only metrics with a saved HogQLQuery definition support checks. Markdown, Trends, Funnels, event series, and metrics without definitions do not. A metric can return any number of rows and columns.

Create a custom_sql check with an empty column_name. Include {metric} exactly once as a relation:

SELECT *
FROM {metric}
WHERE orders < 100

The check queries the owning metric's current saved output. The example assumes that output has an orders column. Every returned row is a failure; zero rows passes. Query {metric} directly or through a subquery. Metric check SQL cannot define CTEs, including nested CTEs and scalar WITH bindings. CTEs and saved parameters inside the metric definition remain supported. Other placeholders are not accepted in the check.

Use the metric's Tests tab, or posthog:data-quality-check-create with subject_type: "metric". The catalog addresses a metric by name, but a check names it by UUID. Pass subject_type=metric to posthog:data-quality-check-types and it offers only Custom SQL.

Saving validates SQL composition without executing it. Run the check to verify column names and results. Every run reloads the saved metric: if an edit removes a column used by the check, the next run errors. Fix the check SQL or restore the expected metric output.

Severity and triggers

Severity is a decision about consequences, not about confidence. Use error when the failure means downstream numbers should not be trusted — those failures mark the subject failing and notify. Use warn for things worth surfacing that nobody would act on today. When unsure, warn is the safer default: an error check that cries wolf gets everything ignored.

Table and view triggers: A check runs when its subject's data changes: a materialized view's checks run as part of its refresh (and, when the team turns the gate on, a refresh whose error-severity checks fail is not published), a source table's checks run after each completed sync, and a plain view's checks run when its DAG runs. Checks on a view outside any DAG only run on demand.

Subject schedules: A metric and a PostHog table have no data-change event to run on, so their checks run on a schedule instead. The first saved check creates an enabled daily schedule for all checks on that subject. The Tests tab lets you change the interval or turn automatic runs off. Manual runs remain available. Scheduled checks use the latest definition author's access, falling back to the creator; manual runs use the initiating user's access. Underlying and additional tables must be readable.

Temporal owns each metric's cadence and pause state. Paused schedules have no next execution time. Reload after an unavailable schedule response before retrying an edit; the edit may have succeeded. Automatic runs skip overlaps and catch up missed occurrences only within 15 minutes. Disabling or deleting every check preserves the schedule preferences. Deleting the metric removes its schedule.

Verify what you wrote

Author, run once, read the result. A check nobody has run is a guess.

1. posthog:data-quality-check-create
2. posthog:data-quality-check-run — returns a suite run
3. Poll system.information_schema.data_quality_check_runs (or posthog:data-quality-check-results) for the outcome

A failed result on the first run is the interesting case: either you found real bad data, or the assertion is wrong. Take the compiled_query off the run, execute it with posthog:execute-sql, and look at what it actually matched before reporting anything. That compiled_query comes from posthog:data-quality-check-results; the information_schema poll in step 3 does not return it. An errored result is never a data problem — the query could not run at all, usually a column name typo or a subject that no longer exists.

Judging a source before you use it

When an analysis depends on a warehouse table or view, check its verdict first:

SELECT subject_name, health, checks_total, checks_failing, last_run_at
FROM system.information_schema.data_quality_health

• failing — an error-severity check found bad data. Say so in your answer; don't quietly use it.
• erroring — a check couldn't run. The data may be fine, but nobody is watching it.
• warn — only warn-severity failures. Usable, worth a mention.
• healthy — checks ran and passed.
• unknown / absent — no checks, or none have run. Absence of failures is not evidence of health.

For the history behind a verdict, system.information_schema.data_quality_check_runs carries recent executions with status, failing-row count, and errors. For metric checks, the failing-row count describes the assertion result, not a scalar metric value. Open the failing-row query from the Tests tab to inspect the current rows that violate the check.

Related

• setting-up-data-catalog — what the data means: metrics, trust marks, relationships.
• querying-posthog-data — the schema-discovery and HogQL rules these queries follow.