BigQuery
Monitoring Kit · v2

11 metrics. Every BigQuery
project. One setup.

Every BigQuery project exposes the same INFORMATION_SCHEMA.JOBS_BY_PROJECT and TABLE_STORAGE_BY_PROJECT views. These 11 queries turn them into live alerts with smart comparison — each metric checks against a 30-day rolling baseline so you only get paged when something is genuinely wrong. Query the region-us.INFORMATION_SCHEMA variant to match your project's region.

Start monitoring free →
11
Production-ready metrics
4
Categories covered
30-day
Smart comparison baseline
10 min
From connect to first alert
BigQuery Kit · 11 metrics · Live
MetricCategorySchedule
Slot Usage Spike — Last 24h
CostHourly
Bytes Billed per User Spike — Last 24h
CostDaily
Dataset Storage — Weekly Snapshot
CostWeekly
Long-Term Storage Accumulation
CostWeekly
Long-Running Jobs — Last 1h
PerformanceHourly
Job Wait Time Anomaly — Last 1h
PerformanceHourly
Job Failure Rate Spike — Last 1h
FailuresHourly
Failed Jobs — Last 1h
FailuresHourly
Stale Tables — 90-Day No Query
OptimizationWeekly
Peak Slot Usage — Yesterday
OptimizationDaily
Large Bytes Scanned — Last 24h
OptimizationDaily

Permissions

2 IAM roles. Read-only. That's it.

Lighthouse only reads metadata — job history, storage stats, slot usage. It cannot access your actual table data, run queries, create objects, or modify anything in your BigQuery project.

Job History Access

roles/bigquery.resourceViewer

One-time IAM grant. Covers all current and future jobs automatically.

gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:lighthouse@PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.resourceViewer"
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT
  • JOBS_TIMELINE_BY_PROJECT
  • Slot usage & reservation data
  • Failure data & error messages
  • Job wait time & bytes billed

Storage Metadata Access

roles/bigquery.metadataViewer

Read-only metadata only — no access to actual table data.

gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:lighthouse@PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.metadataViewer"
  • INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
  • Dataset and table metadata
  • Stale table detection
  • Active vs long-term storage breakdown

Read-only, always → Both IAM roles grant read-only access to operational metadata. Run them once and you're done — new jobs, tables, datasets, and activity are automatically covered. Lighthouse cannot write data, drop tables, create datasets, or access the actual contents of your tables — only operational metadata like job durations, slot consumption, and storage sizes.

How it works

We configure it. You get the alerts.

01

Connect BigQuery

Service account with read-only IAM roles, 3 clicks. Lighthouse reads your job metadata and storage stats — never your actual data. Takes under 5 minutes.

02

Deploy the kit

All 11 monitoring metrics go live in your Lighthouse workspace — pre-configured, pre-tuned, with 30-day smart comparison baselines. No SQL required.

03

Get Slack alerts

When costs spike, jobs fail, or slot usage anomalies appear, you get a Slack message with value, baseline, and context — before anyone has to ask.

Time windows explained → JOBS_BY_PROJECT retains 180 days of history. Real-time metrics use a -2h to -1h window to avoid partially-written rows — BigQuery can take up to 60 minutes to fully populate job records. TABLE_STORAGE_BY_PROJECT is a point-in-time snapshot updated hourly — weekly cadence queries read it as-is. No partial reads, no false drops.

💰Cost & Billing4⚡Performance2🚨Failures2🎯Optimization3
💰

Cost & Billing

4 metrics
01 / COSTReal-timeHourly

Slot Usage Spike — Last 24h

Why monitor

  • Compares today's slot consumption against a 30-day rolling baseline — alerts only when compute is genuinely anomalous, not just on normally busy days.
  • Slot-hours is the true cost signal — unlike bytes billed, slot usage reflects actual compute consumed, including flat-rate reservation waste.
  • Project-level segmentation isolates which workload is responsible when you run multiple projects under one billing account.
02 / COSTReal-timeDaily

Bytes Billed per User Spike — Last 24h

Why monitor

  • BigQuery-specific: a single SELECT * on a large unpartitioned table can generate a surprise bill — this catches it the same day, not on the invoice.
  • Per-user segmentation tells you exactly who to talk to — and whether it's a one-time mistake or a recurring pattern.
  • Estimated cost in USD ($6.25/TB at on-demand rates) makes the alert immediately actionable for finance and engineering alike.
03 / COSTAuditWeekly

Dataset Storage — Weekly Snapshot

Why monitor

  • TABLE_STORAGE_BY_PROJECT is a live snapshot — shows exactly what you're paying for right now, split between active (higher rate) and long-term (lower rate) storage.
  • High long-term % signals stale data — data sitting untouched for 90+ days is automatically moved to long-term pricing, indicating cleanup opportunity.
  • Weekly cadence surfaces gradual storage growth before it shows up as a surprise line item on the bill.
04 / COSTAuditWeekly

Long-Term Storage Accumulation

Why monitor

  • BigQuery auto-promotes data to long-term after 90 days of no modification — long-term storage is cheaper but accumulates silently.
  • Tables with 90%+ long-term bytes are candidates for expiry policies or archiving — catching them weekly prevents bill creep.
  • Size filter (> 10 GB) keeps the alert focused — ignores small tables where the cost impact is negligible.
⚡

Performance

2 metrics
05 / PERFReal-timeHourly

Long-Running Jobs — Last 1h

Why monitor

  • 30 minutes is the threshold — anything above that in BigQuery is either a data volume regression, missing partition filter, or an expensive cross-join.
  • Returns job_id, user, and bytes processed — you can pull the full query plan from BigQuery console and pinpoint the bottleneck immediately.
  • Hourly cadence with -2h to -1h window — catches runaway jobs within the hour, not the next morning.
06 / PERFReal-timeHourly

Job Wait Time Anomaly — Last 1h

Why monitor

  • Wait time = time from query submission to execution start — spikes indicate slot contention, reservation pressure, or a quota hit.
  • Compares against 30-day daily max — filters out normal scheduling jitter, only alerts on genuine queuing anomalies.
  • Hourly detection means you catch reservation bottlenecks while the workload is still running, not after a slow day has already happened.

Want Slack alerts when any of these fire?

Connect BigQuery to Lighthouse in 10 minutes — free, read-only, no SQL required.

Start free →
🚨

Failures

2 metrics
07 / FAILReal-timeHourly

Job Failure Rate Spike — Last 1h

Why monitor

  • Failure rate vs absolute count — a spike from 2% to 34% is a real incident; 10 failures during a high-traffic hour might not be.
  • Compares against 7-day hourly max — catches genuine regressions while ignoring naturally noisy hours.
  • Includes total and failed counts so you can immediately assess impact scope before digging into logs.
08 / FAILReal-timeHourly

Failed Jobs — Last 1h

Why monitor

  • Returns every failure individually — error reason and message included so you know whether it's a quota hit, syntax error, or infrastructure issue.
  • Fixed threshold (> 0) — any failed job is worth knowing about, no baseline comparison needed.
  • Pairs with Q7 — Q7 tells you the rate is spiking; Q8 tells you exactly which jobs failed and why.
🎯

Optimization

3 metrics
09 / OPTAuditWeekly

Stale Tables — 90-Day No Query

Why monitor

  • Uses JOBS_BY_PROJECT referenced_tables to detect actual query activity — not just modification time, which can be updated by pipelines even when data is never read.
  • Cross-references TABLE_STORAGE_BY_PROJECT for size — so you can prioritize which stale tables to clean up first by storage cost.
  • Weekly cadence — tables accumulate gradually; a weekly audit is enough to stay ahead of storage waste.
10 / OPTHistoricalDaily

Peak Slot Usage — Yesterday

Why monitor

  • JOBS_TIMELINE_BY_PROJECT gives period-level granularity — you see slot consumption hour by hour, not just total-per-day.
  • Peak hour identification enables reservation right-sizing — if your peak is 3× your average, you may be over-provisioned or under-provisioned at different times.
  • Daily report cadence — yesterday's usage is fully settled, no partial-period noise.
11 / OPTReal-timeDaily

Large Bytes Scanned — Last 24h

Why monitor

  • 100 GB per query is the signal threshold — most well-written BigQuery queries scan far less; anything above that is usually a missing partition filter or an accidental SELECT *.
  • Returns the query text — you can see immediately whether it was a full table scan, a missing WHERE clause, or an intentional one-time export.
  • Estimated cost per query at on-demand rates ($6.25/TB) — makes it easy to prioritize which queries to optimize first.

Want Slack alerts when any of these fire?

Connect BigQuery to Lighthouse in 10 minutes — free, read-only, no SQL required.

Start free →

Live in Lighthouse

Real alerts, running right now

LH
LighthouseAPP

⚠️ Slot Usage Spike — my-project used 847 slot-hours in the last 24h. 30-day max: 312 slot-hours · 2.7× above baseline.

LH
LighthouseAPP

⚠️ Bytes Billed Spike — analyst@company.com billed 4.8 TB in the last 24h (est. $30). 30-day max: 0.9 TB · 5.3× above baseline.

LH
LighthouseAPP

📦 Storage Snapshot — analytics_prod: 2.4 TB total (68% long-term). raw_events: 890 GB, 100% active. 3 datasets exceed 500 GB.

LH
LighthouseAPP

📦 Long-Term Storage — events_2023: 340 GB, 97% long-term. orders_archive: 89 GB, 100% long-term. Consider partitioning or expiry policies.

LH
LighthouseAPP

⚠️ Long-Running Job — 2 jobs exceeded 30 min in the last hour. Longest: 52 min by analyst@company.com · 1.2 TB processed.

LH
LighthouseAPP

⚠️ Job Wait Time Anomaly — job abc123 waited 4.2 min to start. 30-day max: 0.8 min · 5.3× above baseline. Possible slot contention.

LH
LighthouseAPP

🚨 Job Failure Rate Spike — 34% of jobs failed in the last hour (41 of 121). 7-day max: 4.2% · 8.1× above baseline.

LH
LighthouseAPP

🚨 Failed Job — analyst@company.com · resourcesExceeded: Query exceeded resource limits. Job: bqjob_r123. 3 similar failures in the last hour.

LH
LighthouseAPP

📋 Stale Tables — events_2022 (340 GB) last queried 127 days ago. orders_backup (89 GB) never queried. 8 tables flagged this week.

LH
LighthouseAPP

📊 Slot Usage Report — peak yesterday: 14:00 UTC · 2,840 slot-hours. 6 jobs ran > 500 slot-hours each. Off-peak (22:00-06:00): 12% of total.

LH
LighthouseAPP

⚠️ Large Scan — analyst@company.com scanned 2.1 TB in a single query (est. $13.13). Missing partition filter on events table.

LH
LighthouseAPP

⚠️ Slot Usage Spike — my-project used 847 slot-hours in the last 24h. 30-day max: 312 slot-hours · 2.7× above baseline.

LH
LighthouseAPP

⚠️ Bytes Billed Spike — analyst@company.com billed 4.8 TB in the last 24h (est. $30). 30-day max: 0.9 TB · 5.3× above baseline.

LH
LighthouseAPP

📦 Storage Snapshot — analytics_prod: 2.4 TB total (68% long-term). raw_events: 890 GB, 100% active. 3 datasets exceed 500 GB.

LH
LighthouseAPP

📦 Long-Term Storage — events_2023: 340 GB, 97% long-term. orders_archive: 89 GB, 100% long-term. Consider partitioning or expiry policies.

LH
LighthouseAPP

⚠️ Long-Running Job — 2 jobs exceeded 30 min in the last hour. Longest: 52 min by analyst@company.com · 1.2 TB processed.

LH
LighthouseAPP

⚠️ Job Wait Time Anomaly — job abc123 waited 4.2 min to start. 30-day max: 0.8 min · 5.3× above baseline. Possible slot contention.

LH
LighthouseAPP

🚨 Job Failure Rate Spike — 34% of jobs failed in the last hour (41 of 121). 7-day max: 4.2% · 8.1× above baseline.

LH
LighthouseAPP

🚨 Failed Job — analyst@company.com · resourcesExceeded: Query exceeded resource limits. Job: bqjob_r123. 3 similar failures in the last hour.

LH
LighthouseAPP

📋 Stale Tables — events_2022 (340 GB) last queried 127 days ago. orders_backup (89 GB) never queried. 8 tables flagged this week.

LH
LighthouseAPP

📊 Slot Usage Report — peak yesterday: 14:00 UTC · 2,840 slot-hours. 6 jobs ran > 500 slot-hours each. Off-peak (22:00-06:00): 12% of total.

LH
LighthouseAPP

⚠️ Large Scan — analyst@company.com scanned 2.1 TB in a single query (est. $13.13). Missing partition filter on events table.

These metrics are running in Lighthouse right now

Want alerts when any
of these fire?

Connect your BigQuery project and get all 11 metrics live in under 10 minutes — free, read-only, with smart comparison out of the box.

Already have metrics in mind? Describe them in plain English — Lighthouse writes the SQL.

Start free · 10 min setup →

No credit card required · Read-only access · Cancel anytime