How to build a data quality monitor

What matters here

• A quality monitor is four parts: scheduled checks, thresholds from history, an owner per table, and a failure record.
• A job succeeding is not the same as data arriving. Freshness and row count catch what a green pipeline hides.
• Set thresholds from historical behaviour, not from a guess. A table that normally varies by 40 percent should not alert at 10.
• Alert a named owner, not a shared channel. A channel everybody watches is a channel nobody watches.
• Fewer checks that people act on beat more checks that people mute. Muting is the failure, not the noise.
• Record every failure. The chronic sources are more useful to know than any individual incident.

Who this is for

You run data or analytics engineering, and something has been quietly wrong for longer than you would like to admit. What you want is to find out before the person reading the dashboard does.

How it works in practice

What the monitor does while nobody is watching, and what happens when something breaks.

1. 1

   Checks run on a schedule against registered tables

   Not the whole warehouse. Coverage of what people actually use beats coverage of everything.

2. 2

   Freshness and volume go first

   Because a job that succeeds and delivers nothing is the failure that survives longest.

3. 3

   Thresholds come from how the table behaves

   Proposed from history, with weekly and monthly seasonality handled, so Saturday is not an incident every week.

4. 4

   A failure alerts a named owner, not a channel

   With the downstream dashboards listed in the alert itself.

5. 5

   Related failures group into one alert

   A source system down is one cause and twelve symptoms, and only one of those is worth waking somebody for.

6. 6

   Every failure is kept, and the pattern reported

   Which tables fail most, which upstream sources are behind them, and which checks have never fired at all.

1. 1

   Check the things a green pipeline hides

   Freshness and volume first, because those catch the quiet failures.

   Copy

   In this Helix project, build me a data quality monitor.
   
   Start with the checks that catch the failure nobody notices: a job that
   succeeds and delivers nothing.
   
   Freshness: how long since this table last received a row, against an
   expectation per table. Volume: how many rows arrived in the last period,
   against what normally arrives. Those two catch more real problems than
   everything else combined, because a pipeline reporting success while
   silently delivering an empty file is the classic six-weeks-wrong scenario.
   
   Then the rest: null rate on fields that should never be null, uniqueness
   on anything that is meant to be a key, referential integrity between
   tables that should join cleanly, and range checks on the numbers people
   quote in meetings.
   
   Let me register which tables to watch rather than watching everything.
   Coverage of the tables people actually use beats coverage of the
   warehouse.
   
   I sign in through Helix, so do not build a login page. Read the signed-in
   user from the runtime. And do not put any key or connection string in the
   code: if this needs to reach another system, tell me and I will create the
   connection in my workspace.

   Worth knowing. Freshness and row count are unglamorous and they catch the failures that survive longest, because everything upstream reports success.

2. 2

   Thresholds learned, not guessed

   The difference between a monitor people read and one they mute.

   Copy

   Now set the thresholds, and do not ask me to invent them.
   
   For each check, look at how the table has actually behaved over a period I
   choose and propose a threshold from that: the normal range, and how far
   outside it is genuinely unusual. Show me the history and the proposal, and
   let me accept or adjust.
   
   Handle the obvious seasonality. A table that is quiet at weekends should
   not alert every Saturday, and one that spikes at month end should not
   alert every month end.
   
   Let me mark a failure as expected, with a reason, and use those to suggest
   threshold adjustments. A check that is dismissed as expected three times
   is a check with the wrong threshold, and the app should say so rather than
   waiting for somebody to notice.

   Worth knowing. Alert fatigue is the way these die. A monitor that fires forty times a week gets muted, and a muted monitor is worse than none because everyone believes it is watching.

3. 3

   Alert a person, not a channel

   Because a channel everybody watches is a channel nobody watches.

   Copy

   Give every monitored table a named owner and alert them directly.
   
   A shared channel is where alerts go to be scrolled past. If a check fails
   and nobody is individually responsible for it, nobody is.
   
   Set severity per check, and make it drive the response: a critical
   freshness failure on a table feeding the executive dashboard is not the
   same as a null rate drifting on a field nobody reads. Only the critical
   ones should reach anybody out of hours.
   
   Group alerts. If a source system is down, twelve tables fail at once and
   that is one alert about one cause, not twelve about twelve symptoms.
   
   And show downstream impact in the alert itself: which dashboards and
   reports depend on this table. An owner who can see that the board pack
   reads from it will respond differently.

4. 4

   Keep the failures, find the pattern

   Because the chronic source matters more than any single incident.

   Copy

   Record every failure: which check, which table, when it started, when it
   cleared, who resolved it, and what they found.
   
   Then report the things no individual alert can tell you. Which tables fail
   most often. Which upstream sources are behind those failures. Mean time to
   resolution by owner. And checks that have never fired, which are either
   well-behaved tables or badly configured checks, and it is worth knowing
   which.
   
   Add a simple status page showing every monitored table, when it was last
   checked, and whether it is currently healthy. Put the last check time on
   it prominently: a monitor that has itself stopped running is the worst
   possible failure, because everything looks fine.
   
   And report coverage: which tables that feed a dashboard have no checks at
   all. That gap is where the next six-week problem is waiting.

5. 5

   Connect it to real systems

   The step that turns a working prototype into something with your data in it.

   Copy

   Now connect it to the systems we actually run, so it works on real data
   rather than the rows you seeded.
   
   Anything in angle brackets is a placeholder. Swap it for whatever we use
   instead, and have the app name the connection it wants rather than assume
   a vendor, so changing my mind later is a config change and not a rewrite.
   
   <Snowflake>. Run the checks on a schedule. Read-only access, scoped to the
   tables you register.
   
   <Looker>. Read which dashboards depend on a table, so the alert can say
   what breaks downstream.
   
   <PagerDuty>. Alert the named table owner, and only page for the critical
   ones.
   
   <Okta>. Turn the owner recorded against a table into a real person with a
   current address, and flag any whose account has gone.
   
   <dbt>. Read test results you already run, so this monitors what dbt does
   not rather than duplicating it.
   
   <Fivetran>. Read sync status, which explains a freshness failure before
   anyone starts debugging the table.
   
   <Slack>. Alert the named table owner, with the downstream dashboards
   listed in the message.
   
   Only write where I have said to write. Everything else is read only.
   
   Do not write a key, a token or a connection string anywhere in the code,
   and do not ask me to paste one. Tell me which connections you need and I
   will create each as an authentication in my Helix workspace. Reference
   them by alias.

   Worth knowing. Every name in angle brackets is a placeholder for whatever you run. Helix holds the credential as a workspace authentication, so the app references an alias and never the secret itself.

6. 6

   Ship it

   The last step of every build: a URL, and the right people on it.

   Copy

   Deploy my app.

   Worth knowing. Your assistant runs helix deploy underneath and the app comes back as a URL. This reads your warehouse and its alerts drive people out of hours, so set Access Control before sharing and give every table a named owner.

What it connects to

This one is almost entirely a read, and it should be a narrow one.

• Snowflake

  Run the checks on a schedule. Read-only access, scoped to the tables you register.

• Looker

  Read which dashboards depend on a table, so the alert can say what breaks downstream.

• PagerDuty

  Alert the named table owner, and only page for the critical ones.

• Okta

  Turn the owner recorded against a table into a real person with a current address, and flag any whose account has gone.

• dbt

  Read test results you already run, so this monitors what dbt does not rather than duplicating it.

• Fivetran

  Read sync status, which explains a freshness failure before anyone starts debugging the table.

• Slack

  Alert the named table owner, with the downstream dashboards listed in the message.

Each line says whether the app reads, writes or both. Anything not described as writing should be read only.

Named systems are the ones most teams actually run, not a list of the only ones that work. Swap in whatever you use.

Each connection is an authentication in your Helix workspace, referenced by alias. The app names the connection it wants and never holds the credential, so nothing here ends up in your code and any of it can be rotated or revoked without a redeploy.

Related guides