BigQuery data freshness check and alerts with SQL

Connect BigQuery

Add a connector of type BigQuery. You need a project ID and a service account JSON key, with a location and a default dataset both optional. The BigQuery connector docs have the field list.

There’s no firewall to open. arcodash signs in to Google’s API with the service account key, so BigQuery needs no inbound allowlist, and the fact that arcodash Cloud has no fixed outbound IPs doesn’t matter here. What does matter is what the service account can do, because arcodash runs the SQL you write. Give it two roles from Google’s BigQuery roles:

• roles/bigquery.jobUser on the project. It allows running queries, and carries no data access on its own. Query costs are billed to this project.
• roles/bigquery.dataViewer on each dataset you want to chart or check. It reads table data and metadata.

gcloud projects add-iam-policy-binding my-project \
  --member="serviceAccount:[email protected]" \
  --role="roles/bigquery.jobUser"

Grant dataViewer on the datasets from the dataset’s Sharing panel, not on the whole project, unless you mean it. Then create a JSON key for the account and paste the full file contents into arcodash.

Set location if your datasets live in a single region (for example europe-west1) rather than the US or EU multi-regions. The default dataset only narrows schema browsing in the editor; without it arcodash lists every dataset in the project, which is slow on a big one. Queries are always GoogleSQL with backtick-quoted names, such as `my-project.analytics.orders`.

Keep scheduled checks cheap

BigQuery’s on-demand model bills the bytes a query processes. A scheduled check runs all day, so a bad one is a recurring bill. These rules come from Google’s documentation:

• LIMIT doesn’t help. “Applying a LIMIT clause to a query doesn’t affect the amount of data that is read” (cost controls). SELECT MAX(loaded_at) ... LIMIT 1 still reads what MAX needs across the whole table.
• Partition filters prune. Filtering on the partitioning column makes BigQuery scan only the matching partitions (partitioned tables). On an ingestion-time table that column is the _PARTITIONTIME pseudocolumn, with _PARTITIONDATE as the UTC date version. On a table partitioned by a column, filter that column.
• Use constant expressions. Pruning needs the filter written against constants or functions like CURRENT_TIMESTAMP(). A value that comes from a subquery doesn’t prune (querying partitioned tables).
• Dry run first. A dry run reports the bytes a query would process without running it. The console shows the same estimate before you hit run. Paste a check into the editor and read the number before you put it on a schedule.
• A cap is a safety net. Maximum bytes billed makes a query fail, without charge, when its estimate is over the limit (cost controls). Use it from the bq CLI or API while you develop checks.
• Require a partition filter on big tables. The table option makes any query without a partition filter fail, so nobody schedules a full scan by accident.

Google also gives each project 1 TiB of free on-demand querying a month. A handful of pruned or metadata checks should sit far under that; confirm with a dry run.

A freshness check in GoogleSQL

For a table that’s partitioned by ingestion time, return the lag in seconds from a bounded window:

SELECT COALESCE(
         TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(loaded_at), SECOND),
         999999999) AS seconds_since_last_row
FROM `my-project.analytics.orders`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY);

A check in arcodash is a scheduled query returning one row; the monitor reads a column of it and compares with a value (alerts overview). Monitor seconds_since_last_row with > and 7200 for two hours.

Two details here earn the length. The window means the query reads two days of partitions, not the table’s history. The COALESCE handles the bad case where nothing landed in the window: MAX of no rows is NULL, and a NULL never trips a threshold, so an empty window reads as 999,999,999 seconds instead. If you partition by a column of your own, such as event_date, put the filter on that column instead of _PARTITIONTIME.

TIMESTAMP_DIFF takes the end first: TIMESTAMP_DIFF(end, start, SECOND). The docs describe it as counting whole units, truncated, so use seconds and compare in seconds.

Freshness from metadata, with no table scan

BigQuery keeps a last-write time per table. INFORMATION_SCHEMA.TABLE_STORAGE exposes it as storage_last_modified_time, “the most recent time that data was written to the table”:

SELECT COALESCE(
         TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), storage_last_modified_time, SECOND),
         999999999) AS seconds_since_last_write
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_schema = 'analytics'
  AND table_name = 'orders';

Replace region-us with your dataset’s region. Google requires the qualifier and requires the query to run in that region, so set the connector’s location to match. The view needs bigquery.tables.get and bigquery.tables.list, which dataViewer and metadataViewer carry. Google documents the view as not real time: updates are typically delayed seconds to minutes, so it suits thresholds in hours, not minutes. The column is NULL when a table has no data, which the COALESCE turns into “very stale”.

This tells you when data was last written, not what the newest event is. A pipeline that rewrites the table without new rows still moves it. For “is the latest event recent”, use the MAX(loaded_at) query. For “did the load job run at all”, metadata is cheaper and good enough.

A volume check and a metric alert

Count yesterday’s complete day so the number doesn’t depend on what time the check runs:

SELECT COUNT(*) AS rows_yesterday
FROM `my-project.analytics.orders`
WHERE _PARTITIONDATE = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

Monitor rows_yesterday with < and a floor from your slowest day, or use a baseline monitor and let it learn. Schedule it daily, shortly after your load finishes; the scheduled refresh editor has no timezone picker, so a daily time is UTC, which lines up with _PARTITIONDATE.

For a rate that needs a tighter window, bound the partitions and then the rows:

SELECT 100 * AVG(IF(status = 'failed', 1, 0)) AS failed_pct
FROM `my-project.analytics.payments`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

Monitor failed_pct with > and 5. The partition filter is what keeps the bill small; the created_at filter is what makes the answer correct. A schedule pauses itself after 10 consecutive failed runs, so an expired or deleted service account key shows up as a paused schedule rather than a retry storm.

Gotchas for monitoring BigQuery

• Everything is UTC. CURRENT_TIMESTAMP() is a TIMESTAMP, and CURRENT_DATE() defaults to UTC. DATETIME columns have no zone, so know which one your pipeline wrote.
• CURRENT_TIMESTAMP() is fixed per statement. Every call in one query returns the same instant, which is what you want.
• Metadata lag. TABLE_STORAGE isn’t real time, so don’t set a metadata check tighter than a few minutes.
• Region match. A regional INFORMATION_SCHEMA query has to run in the same region as the view, so set the connector’s location to your datasets’ location.

To generate freshness, row count, null and duplicate checks for a BigQuery table, use the BigQuery data check generator.

Dashboards on BigQuery

A dashboard is saved queries as tiles (building dashboards). Each viewer load can run a query, so keep tiles partition-bounded and aggregated, and dry-run every one.

Signups per day, last 30 days:

SELECT DATE(created_at) AS day, COUNT(*) AS signups
FROM `my-project.analytics.users`
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY day
ORDER BY day;

Largest tables in a dataset, from metadata, for a table tile:

SELECT table_name, total_rows, total_logical_bytes
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_schema = 'analytics'
ORDER BY total_logical_bytes DESC
LIMIT 10;

Pair the freshness number with the chart it protects, as a counter tile, and the dashboard explains its own staleness. The data quality monitors page covers null-rate and duplicate checks, which follow the same one-row pattern.

docs

BigQuery setup in the docs

Connection fields, an example configuration and caveats.