Daily
Hear when a table stops updating
Each morning you're told if a table hasn't changed in the last day, so broken pipelines get noticed.
- Runs
- Daily
- Apps
- BigQuery
- AI
- Not needed
- Access
- read
Send this to your assistant
Create a ClickRuntime automation: every morning at 8am, email me if the analytics.events table hasn't been modified in the last 24 hours.
First time? Adding ClickRuntime to your assistant takes about two minutes.
Why use it
Why teams use this automation
An analytics team depends on a BigQuery table that a nightly pipeline is supposed to refresh. When the upstream load fails, because a source API changed, a credential expired or a job timed out, the table keeps serving yesterday's data with no error. Dashboards still render and executives read last week's figures as if they were current, sometimes making a decision off numbers three days old.
Once a day, ClickRuntime reads the table's metadata from BigQuery, looks at its lastModifiedTime and compares it with the start of today. If the table has not been touched, you get an alert naming the table. The check is cheap because it reads metadata rather than scanning the data itself.
It fits data teams who own several critical tables and want a safety net independent of the pipeline that feeds them.
What you get out of it
Stale data caught before it misleads
An alert on the same morning means someone fixes the load before the Monday report is built from old rows. Quiet staleness is the most damaging kind because nothing looks broken.
Pipeline failures surface without log-diving
The table itself tells you the pipeline did not run. You learn about the failure even if the failing job never sent its own error message. Even an orchestrator that crashed without logging anything still gets caught, because the evidence is the table itself.
Trust in dashboards holds up
When people know stale tables trigger a warning, they can rely on a dashboard that stayed silent. That confidence saves analysts from constantly defending the numbers. Fewer awkward corrections to reports that already went out.
Metadata checks keep costs near zero
Reading table metadata does not scan stored data, so checking a large table every day costs next to nothing. You can monitor many tables without a noticeable bill. Teams with dozens of tables can cover them all for pennies, which makes broad monitoring realistic.
A check independent of the producer
The monitor does not rely on the pipeline reporting its own failure. A crashed scheduler cannot also crash the thing that notices it. This separation is what makes it a genuine safety net rather than a duplicate of existing logs.
How it works
What happens, step by step
Every morning, ClickRuntime checks when each of your chosen BigQuery tables last changed.
It flags any table that has not been updated in the last day.
If any are stale, you get an email listing each one and how many hours ago it last changed, so a broken pipeline gets noticed.
Before it runs live
- Connect BigQuery
What you get
Build your own
Need it to work differently?
Your own rules
Conditions, thresholds, filters and exceptions, written the way you'd explain them to a colleague.
Any number of apps
One automation can read from one app and update several others. Over 248 apps are ready, and anything with an API works too.
AI where it helps
Summaries, classification, extraction and drafts use ClickRuntime's built-in AI, with no extra key.
On a schedule or on each event
Runs as often as every 5 minutes, or the moment an app sends an event, and keeps track of what it already handled.
Changes you can ask for
- Run it at a different time, more or less often, or only on weekdays
- Add an AI step, for example to summarize, sort or draft a reply
- Add another app, for example also send it to Slack
- Combine it with other BigQuery steps into one automation
Under the hood
The code that runs this automation
import time
from clickruntime import on_trigger, credentials, http, notify
TABLES = ["my-project/datasets/sales/tables/orders", "my-project/datasets/marketing/tables/sessions"]
MAX_AGE_HOURS = 24
@on_trigger
def run(event):
h = {"Authorization": f"Bearer {credentials.connection('google', 'work')}"}
stale = []
for t in TABLES:
meta = http.get(f"https://bigquery.googleapis.com/bigquery/v2/projects/{t}", headers=h).json()
age = (time.time() * 1000 - int(meta["lastModifiedTime"])) / 3_600_000
if age > MAX_AGE_HOURS:
stale.append(f"{meta['tableReference']['datasetId']}.{meta['tableReference']['tableId']}: last changed {age:.0f} hours ago")
if stale:
notify.email_me(f"{len(stale)} BigQuery tables are stale", "\n".join(stale))
return {"stale": len(stale)}FAQ
Common questions
How does it decide a table is out of date?
Can it watch more than one table?
Does a refreshed table always mean fresh data?
What permissions does it need?
Does it need AI?
How many runs does it use?
Can I change how it works?
Related
You might also like
Save API snapshots to a BigQuery table
Data from an API is appended to your table with a timestamp on each run, building a history you can query.
See how it worksGet your KPI query results every morning
Your saved SQL runs each morning and the results arrive as a table in your inbox.
See how it worksGet alerted when a metric crosses a limit
A query runs every hour and you hear in Slack as soon as the value passes your threshold.
See how it worksKeep exploring
Set up “Hear when a table stops updating” from your assistant
Copy the prompt above, or connect your assistant first. Free plan, no credit card.