Bigquery Observability

GoogleOfficialPublished by Google

Skill
Tool integration
Data and AI
Apache-2.0
No issues found

From the author

Provides data-retrieval best practices, tool selection guidance, and performant SQL query syntax for BigQuery telemetry across INFORMATION_SCHEMA, Cloud Monitoring, and the REST API. Use when the telemetry to fetch is already known, selecting telemetry tools, writing performant INFORMATION_SCHEMA queries, retrieving telemetry for diagnosing single-job performance bottlenecks, investigating slot contention, job concurrency and queue latency, analyzing reservation capacity, utilization and autoscaling saturation, or auditing capacity-based and on-demand compute and storage resource billable usage. Don't use for root-cause diagnosis or symptom troubleshooting when the cause is unknown (use bigquery-troubleshooting first), or for writing or optimizing business logic SQL (use bigquery-optimization).

— the description by Google

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

BigQuery Observability offers a structured approach to monitoring and analyzing BigQuery performance and resource usage. It guides users in selecting the right telemetry tools—ranging from INFORMATION_SCHEMA for historical analysis to the REST API for real-time job lookups—and provides optimized SQL templates for auditing compute, storage, and capacity. The skill emphasizes efficient data retrieval, least-privilege access, and performance guardrails to ensure accurate telemetry without unnecessary overhead.

What it does

Provides SQL query templates for INFORMATION_SCHEMA views.
Offers guidance on choosing between INFORMATION_SCHEMA, Cloud Monitoring, and REST APIs.
Includes best practices for performant telemetry retrieval and cost management.
Details schema dictionaries for compute, storage, and capacity metrics.
Defines workflows for diagnosing job bottlenecks and resource contention.
Enforces usage attribution for all Google Cloud CLI and API interactions.

When to use it

When you need to audit billable compute or storage resource usage.
When investigating job performance bottlenecks or slot contention.
When analyzing reservation capacity, utilization, or autoscaling saturation.
When you need to perform historical analysis or cohort comparisons of jobs.
When selecting the appropriate telemetry tool for real-time vs. historical monitoring.

Advantages

Reduces query overhead by providing optimized, partition-filtered SQL patterns.
Ensures least-privilege access by recommending specific view variants.
Standardizes diagnostic workflows across single-job and system-level issues.
Provides clear guidance on avoiding common pitfalls like full metadata scans.

Usage examples

“How do I check for slot contention in my BigQuery project?”: The agent provides a query template using the JOBS_TIMELINE view, filtered by the relevant time window, to identify periods of high slot usage and queue latency.
“I need to audit my on-demand compute spend for the last 30 days.”: The agent provides a structured SQL query using INFORMATION_SCHEMA.JOBS, including logic to deduplicate script wrappers and calculate billable bytes.

Install

Download the skill as a ZIP:
https://mp-staging.autogineer.com/agent-skills/google/bigquery-observability/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip bigquery-observability.zip -d .agents/skills

Documentation by Google

BigQuery Observability

Attribution

MUST READ: Before executing any gcloud, REST API, or MCP command, you MUST read Usage Attribution. For commands executed directly in the terminal (never in generated scripts, Terraform, or user code), prefix gcloud with CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-observability)" and append agent-skills/1.0 (skill:bigquery-observability) to User-Agent headers.

Tool Selection

Tool	Primary Use Cases	Strengths & Capabilities	When to Avoid / Limitations
`INFORMATION_SCHEMA` (`I_S`)	Historical analysis, cohort comparison (`normalized_literals`), discovery of fast/slow windows, reservation/project timelines, multi-job aggregates, cost/billing tracing.	Flexible SQL querying across `JOBS`, `JOBS_TIMELINE`, and `RESERVATIONS`; supports custom time windows and grouping.	Avoid for high-frequency real-time polling or single-job point-lookups (can consume slots and take seconds to execute).
REST API (`jobs.api` / `reservation.api`)	Single-job point-lookup, real-time stage bottleneck diagnosis, automated pipeline status checks, reservation/capacity commitment configuration inspection (`reservations.get`, `reservations.list`).	Zero-SQL overhead, fast REST/CLI point-lookups (`bq show -j`, `bq show --reservation`), instant access to `performanceInsights`, `queryPlan`, and structural metadata.	Avoid for aggregate analysis across thousands of jobs, cross-project historical comparison, or system timeline aggregations.
`Cloud Monitoring` (Metrics Explorer / Charts)	Real-time alerting, fleet-wide dashboards, continuous slot utilization tracking, high-level SLA/SLO monitoring.	Out-of-the-box charts for slot utilization, query throughput, `PENDING` queue depth, and execution latency; low-latency alerting without running queries.	Avoid for SQL-level debugging, individual query text inspection, or stage-level execution detail.

Prerequisites & Environment Setup

Before retrieving telemetry or running observability queries, ensure the Google Cloud environment and project are configured:

Google Cloud SDK: Ensure the Google Cloud SDK is installed and configured.
Project Selection: Set the active Google Cloud project:
```
gcloud config set project {project_id}
```
API Enablement: Ensure the BigQuery and Cloud Monitoring APIs are enabled:
```
gcloud services enable bigquery.googleapis.com monitoring.googleapis.com
```
Authentication: Authenticate the environment:
- CLI queries and bq commands: gcloud auth login
- SDKs and automated client tools: gcloud auth application-default login
- Service accounts: Set GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"
Billing & IAM Roles:
- Verify an active Google Cloud Billing account is attached to {project_id}.
- Ensure appropriate IAM roles:
  - roles/bigquery.jobUser: Running telemetry queries.
  - roles/bigquery.resourceViewer or roles/bigquery.admin: Organization-level jobs and reservation telemetry.
  - roles/monitoring.viewer: Cloud Monitoring metrics.
Companion Skills Installation: This skill is part of a 3-pillar operations suite (bigquery-observability, bigquery-optimization, bigquery-troubleshooting). If any companion skill is not yet installed in your environment, install the full suite:
```
npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting
```

Workflow

Single-Job Point-Lookup (Zero-SQL Overhead): For single-job slowness or inspection, always prioritize the REST API or CLI (bq show -j) first. It provides zero-SQL overhead and fast point-lookups for internal stage bottlenecks (performanceInsights, queryPlan, shuffle spill).
```
bq show --location={location} -j {project_id}:{job_id}
```
Diagnostic Transition Logic: If no job-level issues are found (e.g. no clear internal bottlenecks), the investigation should transition to system-level INFORMATION_SCHEMA queries (such as JOBS_TIMELINE or RESERVATIONS_TIMELINE) to check for broader issues like slot contention, queueing delay, or noisy neighbors.

Best Practices for Writing `INFORMATION_SCHEMA` Queries

Every query against a BigQuery INFORMATION_SCHEMA view must be qualified with either a region qualifier or a dataset qualifier, optionally prefixed by a project qualifier.

Qualification Syntax & Scope Matching

Region-Qualified Syntax:
```
`{project_id}`.`region-{region}`.INFORMATION_SCHEMA.{view}
```
Example: `my-project`.`region-us`.INFORMATION_SCHEMA.JOBS
Applies to: Regional telemetry views (JOBS*, JOBS_TIMELINE*, RESERVATIONS*, CAPACITY_COMMITMENTS*, TABLE_STORAGE*, STREAMING_TIMELINE*). The client query execution location MUST match the region-{region} qualifier (or BigQuery throws: Not found: Table {project_id}:region-{region}.INFORMATION_SCHEMA.{view} was not found in location {location}).
Dataset-Qualified Syntax:
```
`{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.{view}
```
Example: `my-project`.`analytics`.INFORMATION_SCHEMA.TABLES
Applies to: Dataset-scoped views (PARTITIONS, SEARCH_INDEXES*, ROW_ACCESS_POLICIES). Never use region- with dataset views.
Dual-Scoped Views: Views like TABLES, COLUMNS, COLUMN_FIELD_PATHS, VIEWS, ROUTINES, and VECTOR_INDEXES can be qualified with either {dataset_id} or region-{region} depending on whether dataset or region-wide analysis is required.
Project Qualifier ({project_id}): Optional. If omitted, queries default to the project in which the query is executing. Specifying a project qualifier on organization-level views (e.g. JOBS_BY_ORGANIZATION) has no impact on results.

Principle of Least Privilege & Scope Selection

When constructing INFORMATION_SCHEMA queries, always select the scope and view variant with the least IAM permission requirement that satisfies the analytical need:

User-Level over Project-Level (_BY_USER): When diagnosing queries or sessions executed by the current user, use _BY_USER (e.g. JOBS_BY_USER, SESSIONS_BY_USER). This requires only bigquery.jobs.list (granted via roles/bigquery.user or roles/bigquery.jobUser), avoiding the need for bigquery.jobs.listAll or roles/bigquery.admin.
Dataset-Level over Region/Project-Level: When querying table metadata, columns, or views for a specific dataset, qualify with {dataset_id} rather than region-{region} when project-level metadata access is restricted. Dataset-scoped queries require permissions only on that target dataset.
Project-Level over Org/Folder-Level (_BY_PROJECT): Always start with project-scoped views before escalating to _BY_FOLDER or _BY_ORGANIZATION. Folder and organization queries require broad folder/org IAM permissions (bigquery.jobs.listAll or bigquery.tables.list at the Org/Folder node).
Metadata Roles over Data Roles: For table and storage introspection, prefer roles/bigquery.metadataViewer (which provides bigquery.tables.get and bigquery.tables.list) over roles/bigquery.dataViewer or roles/bigquery.dataOwner when data read access (bigquery.tables.getData) is not needed. (Note: INFORMATION_SCHEMA.PARTITIONS uniquely requires bigquery.tables.getData).

Execution Guardrails & Query Invariants

Mandatory Partition & Time Filtering: Always filter on creation_time (e.g., creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)) or usage_date to avoid full metadata table scans.
Script Wrapper Exclusion: Add AND (statement_type != 'SCRIPT' OR statement_type IS NULL) when aggregating compute spend to avoid double-counting parent scripts and child jobs.
Column Pruning: Never use SELECT * against INFORMATION_SCHEMA; only project required columns.
Dry Run & Cost Estimation: Use a dry run (bq query --dry_run --use_legacy_sql=false "{query}" or API dryRun=true) before executing complex queries, multi-view joins, or large scans to validate syntax and estimate totalBytesProcessed at zero cost.
Empty Regional Scope (0 Rows): If the execution location matches the qualifier, but the project has no datasets or jobs in that region, the query succeeds and returns 0 rows. Never assume 0 rows means 0 usage—always verify the target dataset locations.
Non-Hierarchical Region Scope: Region qualifiers are not hierarchical. Multi-regions do not encompass single regions (e.g. region-us returns only multi-region US metadata and does not include single regions like region-us-central1).
No Multi-Region Aggregation in SQL: Region qualifiers cannot be joined cross-region in a single query (e.g. region-us cannot join region-eu).
Uncached Execution & Minimum Scan Size: INFORMATION_SCHEMA query results are never cached. On-demand queries incur a minimum of 10 MB of data processing charges per execution.

Domain References & SQL Queries

Telemetry Query Guides

On-Demand Compute: Billed Bytes (references/compute_ondemand_billable.md): Authoritative Golden CTE (bytes_billed_cte), timezone-aligned billing date extraction (PST8PDT), BQML CREATE_MODEL 50x multiplier rules, script wrapper deduplication, and row-level security (RLS) masking checks.
Capacity Compute: Billable Slots & Commitments (references/compute_capacity_billable.md): Query templates for auditing billable capacity hours across 1-Year/3-Year commitments, uncovered baseline PAYG slots, and dynamic autoscaling hours.
Storage Footprints & Usage (Bytes Stored) (references/storage_footprints.md): Storage snapshot queries, compression ratio calculations, Time Travel / Fail-Safe churn, daily average GiB time-integrals, billing model evaluation, and search index coverage.

Performance & Troubleshooting Guides

Job Performance Queries (references/job_performance_queries.md): Queries for evaluating individual and aggregate job performance, stage bottleneck flags, comparable jobs via normalized literals (query_info.query_hashes.normalized_literals), BI Engine acceleration, metadata cache (cmeta) acceleration, search index usage, materialized view rewrite usage, and execution variance outliers.
Resource Contention Queries (references/resource_contention_queries.md): Queries for diagnosing slot contention, queue latency, per-minute concurrency/queue timelines, and 1-second reservation slot saturation.
Capacity & Configuration Queries (references/capacity_and_configuration_queries.md): Queries for evaluating second-by-second baseline/max capacity ceilings, autoscaling saturation timelines, and auditing configuration changes (RESERVATION_CHANGES_BY_PROJECT, ASSIGNMENT_CHANGES_BY_PROJECT).

Schema Dictionaries (Column Definitions & Units)

Compute & Capacity Schema Dictionary (references/schema_compute.md): Complete column dictionary, physical units, and least-privilege IAM roles for all compute, job, session, reservation, capacity commitment, and assignment views (JOBS*, JOBS_TIMELINE*, SESSIONS_BY_USER, SESSIONS_BY_PROJECT, RESERVATIONS*, RESERVATION_CHANGES*, RESERVATIONS_TIMELINE*, CAPACITY_COMMITMENTS*, CAPACITY_COMMITMENT_CHANGES_BY_PROJECT, ASSIGNMENTS*, ASSIGNMENT_CHANGES_BY_PROJECT).
Storage & Data Catalog Schema Dictionary (references/schema_storage.md): Complete column dictionary, physical units, and least-privilege IAM roles for all table storage, partition, column, snapshot, dataset, constraint, and replication views (TABLE_STORAGE*, TABLE_STORAGE_USAGE_TIMELINE*, TABLES*, TABLE_OPTIONS, COLUMNS, COLUMN_FIELD_PATHS, PARTITIONS, VIEWS, MATERIALIZED_VIEWS, TABLE_SNAPSHOTS*, TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, SCHEMATA*, SCHEMATA_OPTIONS, SCHEMATA_REPLICAS*, SCHEMATA_LINKS, SHARED_DATASET_USAGE).
Platform, Governance & Ingestion Schema Dictionary (references/schema_others.md): Complete column dictionary, physical units, and least-privilege IAM roles for all remaining views including Access Control (OBJECT_PRIVILEGES, ROW_ACCESS_POLICIES, ROW_ACCESS_POLICY_OPTIONS), Streaming Ingestion (STREAMING_TIMELINE_BY_PROJECT*, WRITE_API_TIMELINE_BY_PROJECT*), Configuration Options (PROJECT_OPTIONS*, EFFECTIVE_PROJECT_OPTIONS, ORGANIZATION_OPTIONS*, ORGANIZATION_OPTIONS_CHANGES), Insights & Recommendations (RECOMMENDATIONS*, INSIGHTS), and Indexes/BI Engine/Routines (SEARCH_INDEXES*, SEARCH_INDEX_COLUMNS, SEARCH_INDEX_OPTIONS, VECTOR_INDEXES*, VECTOR_INDEX_COLUMNS, VECTOR_INDEX_OPTIONS, BI_CAPACITIES, BI_CAPACITY_CHANGES, ROUTINES*, ROUTINE_OPTIONS, PARAMETERS).

Files

ATTRIBUTION.mdAdded by AutoGineer862 B
LICENSELicence file added by AutoGineer11.1 KiB
SKILL.md14.3 KiB
references/attribution.md1.9 KiB
references/capacity_and_configuration_queries.md6.6 KiB
references/compute_capacity_billable.md6.2 KiB
references/compute_ondemand_billable.md5.7 KiB
references/job_performance_queries.md11.1 KiB
references/resource_contention_queries.md9.3 KiB
references/schema_compute.md40.4 KiB
references/schema_others.md32.0 KiB
references/schema_storage.md34.3 KiB
references/storage_footprints.md10.1 KiB

references/compute_ondemand_billable.md

Billable On-Demand Compute (Bytes Billed)

CORE RULE: The Golden Base CTE (bytes_billed_cte) (Lines 10-112)
Modifying the Output (Agent Instructions) (Lines 113-121)

CORE RULE: The Golden Base CTE (bytes_billed_cte)

Whenever you are asked to analyze, sum, filter, or group on-demand compute costs, you MUST NOT query INFORMATION_SCHEMA.JOBS directly. You must ALWAYS use the following Common Table Expression (CTE) as your absolute foundation. This handles the extreme complexities of BigQuery billing, including parent/child script double-counting, BQML 50x multipliers, and PST timezone partitioning.

CREATE TEMP FUNCTION isBillable(error_result ANY TYPE)
AS (
  error_result IS NULL OR error_result.reason = 'stopped'
);

-- BigQuery hides the number of bytes billed on queries against tables with row-level security.
CREATE TEMP FUNCTION isMaybeUsingRowLevelSecurity(
  job_type STRING, tib_billed FLOAT64, error_result ANY TYPE)
AS (
  job_type = 'QUERY'
  AND tib_billed IS NULL
  AND isBillable(error_result)
);

WITH
  bytes_billed_cte AS (
    SELECT
      project_id,
      job_id,
      user_email,
      -- A hash grouping similar queries that only differ by literal values
      query_info.query_hashes.normalized_literals AS query_hash,
      total_bytes_billed,
      total_bytes_processed,
      referenced_tables,
      error_result,
      job_type,
      -- Jobs are billed by end_time in PST8PDT timezone, regardless of where the job ran
      EXTRACT(DATE FROM end_time AT TIME ZONE 'PST8PDT') AS billing_date,
      TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms,

      -- Multiply CREATE_MODEL by 50x. NOTE: I_S.JOBS does not track the specific model type.
      -- BQML pricing varies, so this conservatively assumes all models are the highest-billed (50x) types.
      (total_bytes_billed / 1024 / 1024 / 1024 / 1024)
        * CASE statement_type
          WHEN 'CREATE_MODEL' THEN 50
          ELSE 1
          END AS total_tb_billed,

      -- Flags queries where billed bytes were hidden/NULL due to Row-Level Security
      isMaybeUsingRowLevelSecurity(
        job_type,
        total_bytes_billed / 1024 / 1024 / 1024 / 1024,
        error_result) AS is_maybe_using_rls
    FROM
      -- JOBS and JOBS_BY_PROJECT are synonyms for project-scoped job history
      `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
      -- Partition pruning: Pad creation_time by -2/+1 days to cover the target PST end_time billing window
      DATE(creation_time)
        BETWEEN DATE_SUB('{start_date}', INTERVAL 2 DAY)
        AND DATE_ADD('{end_date}', INTERVAL 1 DAY)
      AND EXTRACT(DATE FROM end_time AT TIME ZONE 'PST8PDT')
        BETWEEN '{start_date}'
        AND '{end_date}'
      AND job_type = 'QUERY'
      AND reservation_id IS NULL
      -- Scripts do not incur cost; their child queries do. Exclude to avoid double counting.
      AND (statement_type <> 'SCRIPT' OR statement_type IS NULL)
      AND isBillable(error_result)
  )
-- Default executable query: Daily spend & RLS masked query audit
SELECT
  '{region}' AS region,
  billing_date,
  ROUND(SUM(total_tb_billed), 4) AS total_tb_billed,
  COUNTIF(is_maybe_using_rls) AS jobs_using_row_level_security
FROM
  bytes_billed_cte
GROUP BY
  region,
  billing_date
ORDER BY
  billing_date;

-- Alternative troubleshooting aggregations:
-- 1. Top users by spend:
-- SELECT user_email, SUM(total_tb_billed) AS total_tb_billed FROM bytes_billed_cte GROUP BY 1 ORDER BY 2 DESC;
--
-- 2. Top queries by spend & processed bytes (job-level attribution, no multi-table fan-out):
-- SELECT query_hash, COUNT(1) AS query_count, SUM(total_tb_billed) AS total_tb_billed, SUM(total_bytes_processed) AS total_bytes_processed, ANY_VALUE(referenced_tables) AS sample_referenced_tables FROM bytes_billed_cte GROUP BY 1 ORDER BY 3 DESC LIMIT 10;
--
-- 3. Candidate tables referenced by high-scan queries (IMPORTANT: NEVER use SUM(total_bytes_billed) or SUM(total_bytes_processed) with UNNEST(referenced_tables), because job-level bytes repeat for every joined table; use COUNT(DISTINCT job_id), MAX(total_bytes_processed), and join TABLE_STORAGE for actual table size):
-- SELECT ref.project_id AS table_project_id, ref.dataset_id, ref.table_id, COUNT(DISTINCT c.job_id) AS referencing_job_count, MAX(c.total_bytes_processed) AS max_job_bytes_processed, MAX(s.total_logical_bytes) AS table_logical_bytes FROM bytes_billed_cte AS c, UNNEST(c.referenced_tables) AS ref LEFT JOIN `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.TABLE_STORAGE AS s ON ref.project_id = s.project_id AND ref.dataset_id = s.table_schema AND ref.table_id = s.table_name GROUP BY 1, 2, 3 ORDER BY table_logical_bytes DESC, max_job_bytes_processed DESC LIMIT 20;

⚠️ BQML Multiplier Caveat: BigQuery ML pricing for on-demand queries depends on the specific type of model being created. Because INFORMATION_SCHEMA.JOBS does not track which type of model was created, the total_tb_billed calculation conservatively assumes all CREATE_MODEL statements were creating the higher-billed (50x multiplier) model types. Keep this in mind when diagnosing massive BQML spikes.

Modifying the Output (Agent Instructions)

Placeholders: Replace {project_id} with the target Google Cloud project ID, {region} with the dataset or job execution region (e.g. us, europe-west1), {start_date} with the start date (YYYY-MM-DD), and {end_date} with the end date (YYYY-MM-DD).
Custom Aggregations: To answer specific user questions, take the Golden CTE above and append your specific aggregation logic to the bottom of the script.

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Needs: Needs a command-line tool, Needs Node.js

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Published by

Google

Publisher

Google (Official)

Source

google/skills · skills/cloud/bigquery-observability · commit 2194d3b

Licence

Apache License 2.0 (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-08

AutoGineer is not affiliated with or endorsed by Google.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download bigquery-observability.zip