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.
Fix the highlighted field. Showing the last valid SQL.
Returns a row when the newest _LOADED_AT is older than 30 hours.
SELECT newest
FROM (
SELECT MAX(_LOADED_AT) AS newest FROM RAW.EVENTS
) s
WHERE newest IS NULL
OR newest < DATEADD(hour, -30, CURRENT_TIMESTAMP()) -- 30h thresholdReturns 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.
SELECT rows_24h, rows_prior_avg
FROM (
SELECT
COUNT(CASE WHEN _LOADED_AT >= DATEADD(hour, -24, CURRENT_TIMESTAMP()) THEN 1 END) AS rows_24h,
(
COUNT(CASE WHEN _LOADED_AT >= DATEADD(hour, -192, CURRENT_TIMESTAMP()) AND _LOADED_AT < DATEADD(hour, -168, CURRENT_TIMESTAMP()) THEN 1 END) +
COUNT(CASE WHEN _LOADED_AT >= DATEADD(hour, -360, CURRENT_TIMESTAMP()) AND _LOADED_AT < DATEADD(hour, -336, CURRENT_TIMESTAMP()) THEN 1 END) +
COUNT(CASE WHEN _LOADED_AT >= DATEADD(hour, -528, CURRENT_TIMESTAMP()) AND _LOADED_AT < DATEADD(hour, -504, CURRENT_TIMESTAMP()) THEN 1 END) +
COUNT(CASE WHEN _LOADED_AT >= DATEADD(hour, -696, CURRENT_TIMESTAMP()) AND _LOADED_AT < DATEADD(hour, -672, CURRENT_TIMESTAMP()) THEN 1 END)
) / 4.0 AS rows_prior_avg
FROM RAW.EVENTS
WHERE _LOADED_AT >= DATEADD(hour, -696, CURRENT_TIMESTAMP())
) s
WHERE rows_prior_avg > 0
AND ABS(rows_24h - rows_prior_avg) * 100 > rows_prior_avg * 30 -- 30% thresholdReturns a row for each column whose null rate is above 1%.
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 RAW.EVENTS
UNION ALL
SELECT 'EVENT_TYPE' AS column_name, 100.0 * (COUNT(*) - COUNT(EVENT_TYPE)) / NULLIF(COUNT(*), 0) AS null_pct FROM RAW.EVENTS
) s
WHERE null_pct > 1 -- 1% thresholdReturns each EVENT_ID value that appears more than once.
SELECT EVENT_ID, COUNT(*) AS occurrences
FROM RAW.EVENTS
GROUP BY EVENT_ID
HAVING COUNT(*) > 1Returns a row when no load has landed since the last expected run, so a job that never started still gets caught.
SELECT rows_in_window
FROM (
SELECT COUNT(*) AS rows_in_window
FROM RAW.EVENTS
WHERE _LOADED_AT >= DATEADD(hour, -24, CURRENT_TIMESTAMP()) -- 24h window
) s
WHERE rows_in_window = 0Each check is a plain SELECT. Save it to a file and run it from whatever already runs jobs for you.
# 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]Save the zero rows version as tests/orders_freshness.sql. dbt fails the test when the query returns any row.
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,
)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.
Snowflake folds unquoted names to upper case. RAW.EVENTS and raw.events are the same table, so the generator leaves all-upper and all-lower names alone and quotes mixed case, which is the only form that matches a table created with quotes.
CURRENT_TIMESTAMP() follows the session TIMEZONE parameter, and TIMESTAMP_NTZ columns carry no zone. If your loader writes UTC into an NTZ column, set TIMEZONE to UTC for the monitoring user.
Snowflake also has QUALIFY, which makes duplicate detection shorter, but the generated GROUP BY form is portable and just as cheap on a key column.
Connecting Snowflake to arcodash is covered on the Snowflake connector page.
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.
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.
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.
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.
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.
The demo is a live dashboard with its checks running. The trial is the full product for 14 days, no card.