Debugging Failed Data Quality Checks

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:

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

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:

Two messages come with skipped: The subject was deleted. and The subject no longer resolves. The table or view is gone. Delete the check or point it at the replacement.

Step 4: Understand a blocked refresh

When the team turns on "Block materialization on failing checks", a materialized view's error-severity checks run on each refresh before it is published. If one fails:

• The view's run shows an error that starts with Not published: (see view-run-history).
• The previous version of the view keeps serving queries.
• Downstream views in the same DAG are skipped for this run.
• The view's schedule is not paused. The next scheduled refresh runs the checks again.

Warn-severity checks never block a refresh.

Step 5: Recover

Fix the cause, then prove it:

1. Fix the upstream data, or fix the assertion with posthog:data-quality-check-update. If the failure is real but must not block the refresh, change severity to warn.
2. For a blocked view, call view-run to materialize it again. Otherwise call posthog:data-quality-check-run.
3. Read posthog:data-quality-check-results again and confirm the newest run passed.

Important notes

• Do not change a check's severity or assertion only to turn it green. Confirm with the user first.
• errored is never evidence of bad data, and passed on an empty table is not evidence of good data.
• A check reports against its subject, but a custom_sql query can read other tables. Read the query before you blame the subject.

Related

• authoring-data-quality-checks: writing and choosing checks
• auditing-warehouse-view-health: finding failing materialized views