Stale Dashboards Lie Confidently: Freshness Checks That Catch a Broken Pipeline First
The 60-second version
A credential expired and the Monday review debated a demand collapse that never happened. Three layers of scheduled validation queries — freshness, shape, reconciliation.
- What happened, in one line
- What to do about it this week
- What you can safely ignore
Nobody noticed the pipeline had been broken for six days. The dashboard kept rendering — cheerfully, confidently — with last Tuesday's data relabelled as this Tuesday's, because nothing in the system distinguished "no new rows" from "no new orders." The Monday business review debated a demand collapse that was, in fact, a credential expiry.
Stale dashboards are worse than no dashboards: they convert a pipeline bug into a business decision. The fix is a small suite of freshness and validation queries — row-count deltas, null-rate guards, spend-vs-API reconciliation — run on a schedule, read by a human. This post builds that suite.
The short version: trust, but verify on a schedule
The Validation Suite
Data JourneyFreshness
When did each table last grow? A table whose max timestamp stops advancing is either broken or finished — and pipelines are never finished.
Shape
Row counts, null rates and distinct-key counts per day. A 90% volume drop or a suddenly-null column announces itself here.
Reconciliation
Warehouse spend vs platform API, order counts vs store admin. Two independent systems agreeing is the only real proof.
A freshness check answers "how old is the newest data I can query?" Data freshness is the underlying property; the check is the query that measures it. Everything below is designed to run as scheduled SQL with results routed somewhere a human actually reads — an email, a Slack channel, a morning report. A check nobody reads is documentation, not monitoring.
Separate lateness from wrongness in every alert. "Meta spend for yesterday is missing" (freshness — wait or fix the sync) and "Meta spend for yesterday is half of the API" (correctness — investigate now) demand different responses at 9 AM. One alert text for both trains people to ignore both.
Layer 1: freshness — did the data arrive?
The cheapest possible check, and the one that would have caught the six-day outage in the intro:
Show query
Set per-table thresholds from the source's nature, not from optimism: an intraday export lagging 6 hours is late; a daily-transfer table lagging 6 hours is normal. Document the SLA next to the threshold so the on-call human knows which alerts can wait for the scheduled sync.
Layer 2: shape — did the data arrive whole?
Fresh data can still be broken data: a schema change that nulls a column, a filter that drops 90% of rows, a backfill that doubles them. Shape checks compare today against recent history:
Show query
Three alerts, three different mornings
Layer 3: reconciliation — is it true?
Two systems that agree were probably both right; one system alone is a claim. The highest-value reconciliation in marketing data:
- Warehouse spend vs platform API/billing. Within 2–3% is healthy (timing edges, timezone cuts). Beyond 5% is a real defect — usually a missed account, a changed timezone, or a dropped date range.
- Warehouse orders vs store admin. Exact-match territory; any gap is a pipeline bug, full stop.
- Attributed share vs history. The fraction of orders carrying a click ID should move slowly. A cliff means the capture plumbing broke (see the click-ID post), not that customers changed behaviour.
Reconcile at the grain the business decides on. Daily reconciliation in reporting currency and reporting timezone — the normalisation layer from the companion post. Reconciling raw UTC-day API numbers against IST-day warehouse numbers manufactures false alarms that teach everyone to mute the channel.
Operating the suite without alert fatigue
From Queries to a Habit
Process FlowStart with freshness on money tables
Spend and orders, daily. Two queries catch the catastrophic class of outage. Everything else is refinement.
Add shape guards with median bands
Fixed thresholds rot as the business grows; trailing-median bands grow with it. Review band widths quarterly.
Add one reconciliation per source pair
Spend vs API, orders vs admin. These are the checks that catch silent wrongness, and each is a single query.
Route to a read channel with an owner
A Slack channel with a named owner beats email. Unowned alerts become wallpaper within a month.
Frequently Asked Questions
Won't these checks cost a fortune to run on big tables?
No — they are aggregation queries over partitioned recent data: a day or two of partitions, a few columns, typically megabytes scanned. The suite's daily cost is ordinarily cents. If a check scans the full table history, restrict it to the trailing window — history does not change shape overnight.
How is this different from dbt tests or vendor observability?
It is the same instinct in scheduled-SQL form: for teams without a transformation framework or a data-observability vendor, these queries are the test suite. If you later adopt dbt, each check maps directly onto a test or snapshot — nothing is wasted.
What should the alert say to be actionable at 9 AM?
Table, layer (freshness/shape/recon), observed vs expected, and the documented runbook link. "raw_ad_spend freshness: newest event 2 days ago (SLA: daily) — runbook: check token, then backfill" resolves before standup. "Data issue detected" resolves never.
Summary & Next Steps
Dashboards do not go stale loudly; they go stale confidently. A three-layer suite — freshness (did it arrive), shape (did it arrive whole), reconciliation (is it true) — converts silent pipeline rot into a morning message with an owner.
- Use freshness checks on every money table, thresholded by source SLA.
- Use deduped views so backfill recoveries do not create double-count incidents on the way past.
- Use freshness literacy in reporting so provisional windows are labelled, not debated.
BigQuery Cost Estimator
Estimate what a query will cost before you run it — from validator bytes, with a table-scan simulator and a paste-your-query cost audit. On-demand BigQuery pricing, made concrete.
Chinmay Raibagkar
About author →Founder of DataLens AI. He helps non-technical teams read their ad and database numbers with confidence — which number to trust, what to do next, and what to ignore.