/debugging-failed-data-quality-checks

name: debugging-failed-data-quality-checks
description: >
  Finds out why a data quality check failed or errored, looks at the rows it matched, and recommends
  the fix. Also covers a materialized view refresh that the data quality gate did not publish. Use
  when asked why a check is red, what rows failed, why a view shows "Not published", why a view's
  data is stale after a refresh, or how to get a blocked refresh published. Trigger terms: failed
  check, failing rows, errored check, data quality failure, not published, blocked materialization,
  unpublished refresh, check keeps failing.

Debugging failed data quality checks

A check run ends in one of four states. Read the state first, because it decides where the problem is:

| Status | Meaning | Where to look | | --------- | ---------------------------------------------- | -------------------------------------- | | `failed` | The query ran and the assertion found bad data | The data, or the assertion itself | | `errored` | The query could not run, so nothing was judged | The error text (never a data problem) | | `skipped` | The subject is gone | Whether the table or view still exists | | `passed` | The assertion held | Nothing |

`row_count` and `freshness` checks judge `observed_value`, not `failed_row_count`. A failed `row_count` means the count fell outside its bounds. A failed `freshness` means the newest row is older than the limit.

When to use this skill

• A check or a subject shows `failed` or `errored` and the user wants to know why
• The user wants to see the rows a check matched
• A materialized view's latest run shows an error that starts with `Not published:`
• A view kept serving old data after a refresh, and the data quality gate is on
• Another skill (`authoring-data-quality-checks` or `auditing-warehouse-view-health`) found a failing check or a blocked view and handed it off

Available tools

| Tool | Purpose | | ------------------------------------ | --------------------------------------------------------------------------- | | `posthog:data-quality-check-results` | A check's recent runs, with `compiled_query`, `error` and the config it ran | | `posthog:execute-sql` | Re-run a run's `compiled_query` to see the failing rows | | `posthog:data-quality-check-run` | Run a check now | | `posthog:data-quality-check-update` | Fix an assertion or change its severity | | `view-get` | A view's definition and status | | `view-run-history` | A view's materialization runs and their errors | | `view-run` | Materialize a view again |

To find the failing checks, query `system.information_schema.data_quality_checks` for `last_status IN ('failed', 'errored')`, or `system.information_schema.data_quality_health` for a subject's verdict.

Workflow

Step 1: Read the run

Call `posthog:data-quality-check-results` for the check. On the newest run, read:

• `status` and `error`
• `check_config` and `check_severity`: what the run asserted, which can differ from the check today
• `compiled_query`: HogQL that selects the failing rows. For `row_count` it returns the count instead, so judge that check by `observed_value`.
• `audited_staged_refresh`: true when the run was part of a gated audit of a refresh before it was published. An errored run can carry it too, so read `status` first.

Retention clears `compiled_query` after 30 days. For an older run, run the check again.

Step 2: Look at the failing rows

Run `compiled_query` with `posthog:execute-sql`. An empty `compiled_query` means there is nothing to replay: retention cleared it, or a gated run could not read the view's definition. Look at what it matched before you report anything. A failure means one of two things: the data is bad, or the assertion is wrong. The rows tell you which.

When `audited_staged_refresh` is true, the query does not read the published table. It holds the view's definition in a `WITH` clause and reads the view's source tables now. The sources may have changed since the run, so the rows and the count can differ from the run. A query that now returns zero rows does not prove that the refresh was clean. Compare with `failed_row_count` on the run.

Step 3: Read an errored run

An errored run did not judge the data. Match the error text:

| Error text | Cause and fix | | ------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `The staged files could not be read, so this data was not audited.` | The gate could not reach the refresh before publish. The refresh publishes without a verdict. Run the view again. | | `Metric checks cannot audit staged data.` | A metric check was part of a gated refresh. It runs on its own schedule instead. | | `The check query returned no rows.` | The aggregate query returned nothing. Run the check again and report it if it repeats.