authoring-data-quality-checks

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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.

```sql
-- 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:

```sql
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

From PostHog/skills, MIT.