Free tool

BigQuery data check SQL generator

Freshness, row count, null rate, duplicates and a job heartbeat, written for your database. Pick a dialect, name your table, copy the SQL. Runs in your browser, nothing you type is sent anywhere.

See the SQL

Your checks

Freshness

Zero rows means healthy

Returns a row when the newest ingested_at is older than 30 hours.

Quoted for BigQuery: my-project. The name has characters or casing the dialect would otherwise change, or is a reserved word.
Assumes a TIMESTAMP column. For DATETIME, compare against DATETIME_SUB(CURRENT_DATETIME(), ...) instead.

SELECT newest
FROM (
  SELECT MAX(ingested_at) AS newest FROM `my-project`.analytics.events
) s
WHERE newest IS NULL
   OR newest < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 HOUR) -- 30h threshold

Row count

Zero rows means healthy

Returns a row when the last 24 hours are more than 30% away from the same weekday over the last 4 weeks. Needs 28 days of history.

Quoted for BigQuery: my-project. The name has characters or casing the dialect would otherwise change, or is a reserved word.
Assumes a TIMESTAMP column. For DATETIME, compare against DATETIME_SUB(CURRENT_DATETIME(), ...) instead.

SELECT rows_24h, rows_prior_avg
FROM (
  SELECT
    COUNT(CASE WHEN ingested_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) THEN 1 END) AS rows_24h,
    (
      COUNT(CASE WHEN ingested_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 192 HOUR) AND ingested_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 168 HOUR) THEN 1 END) +
      COUNT(CASE WHEN ingested_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 360 HOUR) AND ingested_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 336 HOUR) THEN 1 END) +
      COUNT(CASE WHEN ingested_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 528 HOUR) AND ingested_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 504 HOUR) THEN 1 END) +
      COUNT(CASE WHEN ingested_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 696 HOUR) AND ingested_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 672 HOUR) THEN 1 END)
    ) / 4.0 AS rows_prior_avg
  FROM `my-project`.analytics.events
  WHERE ingested_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 696 HOUR)
) s
WHERE rows_prior_avg > 0
  AND ABS(rows_24h - rows_prior_avg) * 100 > rows_prior_avg * 30 -- 30% threshold

Null rate

Zero rows means healthy

Returns a row for each column whose null rate is above 1%.

Quoted for BigQuery: my-project. The name has characters or casing the dialect would otherwise change, or is a reserved word.
Scans the whole table. Add a WHERE on the timestamp column to limit it to recent rows.

SELECT column_name, null_pct
FROM (
  SELECT 'user_id' AS column_name, 100.0 * (COUNT(*) - COUNT(user_id)) / NULLIF(COUNT(*), 0) AS null_pct FROM `my-project`.analytics.events
  UNION ALL
  SELECT 'event_name' AS column_name, 100.0 * (COUNT(*) - COUNT(event_name)) / NULLIF(COUNT(*), 0) AS null_pct FROM `my-project`.analytics.events
) s
WHERE null_pct > 1 -- 1% threshold

Duplicates

Zero rows means healthy

Returns each event_id value that appears more than once.

Quoted for BigQuery: my-project. The name has characters or casing the dialect would otherwise change, or is a reserved word.

SELECT event_id, COUNT(*) AS occurrences
FROM `my-project`.analytics.events
GROUP BY event_id
HAVING COUNT(*) > 1

Heartbeat

Zero rows means healthy

Returns a row when no load has landed since the last expected run, so a job that never started still gets caught.

Quoted for BigQuery: my-project. The name has characters or casing the dialect would otherwise change, or is a reserved word.
Assumes a TIMESTAMP column. For DATETIME, compare against DATETIME_SUB(CURRENT_DATETIME(), ...) instead.

SELECT rows_in_window
FROM (
  SELECT COUNT(*) AS rows_in_window
  FROM `my-project`.analytics.events
  WHERE ingested_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) -- 24h window
) s
WHERE rows_in_window = 0

How to run these

Each check is a plain SELECT. Save it to a file and run it from whatever already runs jobs for you.

Cron and psql

# hourly; mails you when the query returns any rows (grep . exits 0 only when it sees output)
0 * * * * psql "$DATABASE_URL" -At -f freshness.sql | grep . && echo "freshness check failed" | mail -s "data check" [email protected]

dbt singular test

Save the zero rows version as tests/orders_freshness.sql. dbt fails the test when the query returns any row.

Airflow

Use the One number style with SQLValueCheckOperator. The operator fails the task when the query returns no rows, so the zero rows style would fail a healthy check. The count always returns one row, andpass_value=0 passes only when it is 0.

SQLValueCheckOperator(
    task_id="orders_freshness",
    conn_id="warehouse",
    sql=FRESHNESS_SQL,
    pass_value=0,
)

arcodash monitor

Paste the One number version as a query, save it, and add a monitor on the failing column with the condition above 0. The monitor runs on a schedule and posts to Slack or email when it trips.

BigQuery notes

A project id with a hyphen, like my-project, has to be wrapped in backticks. The generator does that per part, so project.dataset.table works as typed.

Time math uses TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 HOUR), which needs a TIMESTAMP column. A DATETIME column must be compared with CURRENT_DATETIME() and DATETIME_SUB instead.

BigQuery bills by bytes scanned. On a partitioned table, add a filter on the partition column to the row count and null rate checks so they read recent partitions only.

Connecting BigQuery to arcodash is covered on the BigQuery connector page.

Questions

Is anything sent anywhere?

No. The SQL is generated by JavaScript in your browser and nothing you type leaves the page. The link you can copy keeps your settings in the part of the address after the #, which browsers never send to a server.

Why do the checks return zero rows when everything is fine?

Each check selects only the problems: a stale table, a count that moved too far, a column with too many nulls, a duplicated key. No problems means no rows, which is also how dbt tests and most scheduler checks read a result. If your tool needs a single value, switch to One number and the check returns a count of failing rows instead.

Why does the row count check need 28 days of history?

It compares the last 24 hours with the average of the same weekday over the previous 4 weeks, so a quiet Sunday is judged against other Sundays instead of a busy Tuesday. With less than 4 weeks of data the average is built from fewer days and the check is noisier.

What about time zones?

The checks compare your timestamp column with the database clock. If the column stores UTC and the session runs in another zone, the freshness threshold shifts by the offset. Each dialect page lists what to set so the two agree.

How do I get alerted when a check fails?

Run the SQL on a schedule and act on the result. A cron job, a dbt test or an Airflow task works. In arcodash, save the check as a query, wrap it as SELECT COUNT(*) AS failing FROM (<check>) t, and add a monitor that alerts Slack or email when failing is above 0.

get started

Trust the number. Try it on your data.

The demo is a live dashboard with its checks running. The trial is the full product for 14 days, no card.

Start free: 14 days, no card See a live demo

querying in minutesunlimited seats

BigQuery data check SQL generator

Your checks

Freshness

Row count

Null rate

Duplicates

Heartbeat

How to run these

Cron and psql

dbt singular test

Airflow

arcodash monitor

BigQuery notes

Questions

Is anything sent anywhere?

Why do the checks return zero rows when everything is fine?

Why does the row count check need 28 days of history?

What about time zones?

How do I get alerted when a check fails?

Keep reading

Data freshness: what it is and how to check it

Null rate: how to calculate and alert on it in SQL

What is a volume check? Row count checks in SQL

Data freshness monitoring with SQL

SQL alert when a metric drops

Trust the number. Try it on your data.