Automated data tests that run before a dashboard refresh publishes

Reference article · 6 min read

A public dashboard carries an implicit promise: the numbers on it were checked. When a bad load publishes, the cost is not only the correction. It is the follow-up questions from oversight bodies, the screenshots that keep circulating after the fix, and the slower acceptance of every later number the same system produces. In most cases the failure was detectable. Nobody was looking at the moment it mattered.

Automated data tests close that gap by making the check part of the refresh itself. The pipeline loads and transforms data into a staging area, runs a defined set of assertions against it, and only then promotes it to what the public or internal users see. The design work is deciding which assertions are worth running, where the thresholds sit, and what happens when one fails.

Separate the load from the reveal

You cannot test before publishing if loading and publishing are the same operation. A pipeline that truncates the production table and reloads it has already exposed whatever arrived. The structural change comes first: write into a staging table or a versioned snapshot, run tests against that, then promote it through an atomic swap, a view repoint, or a partition switch.

Promotion should be cheap to reverse. If the last known good version stays queryable until the new one is accepted, a held refresh is a non-event rather than an outage. This matters more than the sophistication of any individual test.

The checks worth automating first

Start with assertions that catch the failure modes that actually occur in agency reporting: a truncated extract, a duplicated file, a changed code list, a source system that stopped sending.

• Row count deltas. Compare the staged row count to the prior run and to the same period in prior cycles. Both a collapse and an unexplained jump are worth catching, since a doubled count usually means a file was processed twice.
• Null rates on required columns. Test the rate, not just the presence. A field that is normally complete and suddenly arrives ten percent empty signals an upstream mapping change even when no row is rejected.
• Uniqueness at the stated grain. Assert that the primary key or the composite grain of each table is unique. Duplicate keys silently inflate every aggregate downstream.
• Referential checks against reference data. Verify that county codes, agency identifiers, program codes, facility numbers, and similar dimension values all resolve to a current entry in the reference table. Unmatched values should be counted and reported, not quietly dropped into an "unknown" bucket that no one reviews.
• Range and domain assertions. Dates within a plausible window, amounts and rates within bounds, categorical fields limited to the allowed set, totals that reconcile to a control figure supplied by the source system.
• Freshness. Assert that the maximum event or transaction date in the staged data is recent enough for the reporting cycle. A pipeline that runs successfully against yesterday's file will keep publishing a stale dataset indefinitely without this check.

Setting thresholds that survive real data

Thresholds drawn from intuition tend to fire constantly and then get ignored. Derive them from an observed window of past runs, allow for known seasonality and short weeks, and be careful with small denominators where ordinary variation looks dramatic. Suppression rules for small counts, which many health, education, and justice datasets apply, can themselves cause legitimate swings.

Give every test an owner, a plain-language statement of what it protects, and a documented reason for its threshold. Tests nobody can explain are the first ones overridden under deadline pressure.

Blocking versus flagging

Not every failure deserves the same response. Assign each test a severity and an explicit action.

• Blocking. The refresh halts, the prior version stays published, and a notification goes to a named group. Reserve this for tests where publishing wrong numbers is worse than publishing late: uniqueness violations at the reporting grain, control totals that do not reconcile, freshness failures on a dataset with a filing or posting obligation.
• Warning. The refresh publishes and the failure is logged and routed for review within a defined window. Appropriate for drift in a distribution or a modest rise in unmatched codes.
• Informational. Recorded for trend analysis, with no immediate action.

A hold is itself a decision the public may notice, so pair it with a visible "data as of" date on the dashboard and a short internal runbook covering who investigates, who may override, and how an override is recorded. Overrides should require a named approver and leave a log entry. An override path that is easy and anonymous will become the normal path.

What to ask vendors and require in the contract

• Where do tests execute, in the pipeline or only inside the reporting tool, and do they run before or after users can see the result?
• Are test definitions stored as version-controlled configuration the agency owns and can read, or are they embedded in proprietary logic?
• Can agency staff add, modify, and disable tests without a change order?
• Is failure behavior configurable per test, including hold-and-notify with retention of the last good published version?
• Are test results retained as a queryable history, so recurring failures and threshold tuning can be reviewed?
• Will the vendor demonstrate the behavior at acceptance using a deliberately corrupted input file, showing that the bad data does not reach the published dashboard?

How to start

1. Inventory the public-facing or externally reported datasets and rank them by the consequence of publishing a wrong number.
2. For the top one, write down the three failures you would most regret, and implement those assertions first.
3. Run every test in warning mode for several cycles to tune thresholds before any test blocks a refresh.
4. Add the staging-and-promote pattern if the pipeline does not already have it.
5. Publish the runbook and the override log location, then expand test coverage dataset by dataset.

HQT is a technology and software company whose work includes data engineering, pipeline quality controls, and reporting systems for public sector programs.

Frequently asked questions