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/storage_footprints.md

Storage: Logical & Physical Pricing (Bytes Stored)

Storage Snapshot & Billing Model Comparison Query (Lines 18-107)
Dataset Storage Configuration Query (Lines 108-127)
Partition Level Aging & Storage Query (Lines 128-146)
Daily Storage Usage Timeline (Time-Integral Conversion) (Lines 147-175)
Table Partitioning, Clustering & Filter Enforcement Audit (Lines 176-238)
Search Index Coverage Query (Lines 239-262)
Placeholder Definitions (Lines 263-268)

Storage Snapshot & Billing Model Comparison Query

[!NOTE] The DECLARE parameters in the query below reflect default US multi-region list pricing ($/GiB/month). Because BigQuery storage pricing varies across regions (e.g., non-US regions such as europe-west1 or asia-northeast1), verify the current rates for your specific region at BigQuery Storage Pricing and update the DECLARE rates before running.

-- Default pricing parameters below reflect US multi-region standard list rates ($/GiB/month).
-- IMPORTANT: Storage rates vary by region (e.g., non-US regions).
-- Verify and update these DECLARE variables for your specific region before executing.
-- Reference: https://cloud.google.com/bigquery/pricing#storage
DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

WITH
  storage_sizes AS (
    SELECT
      table_schema AS dataset_name,
      -- Logical
      SUM(IF(deleted = FALSE, active_logical_bytes, 0)) / POWER(1024, 3)
        AS active_logical_gib,
      SUM(IF(deleted = FALSE, long_term_logical_bytes, 0)) / POWER(1024, 3)
        AS long_term_logical_gib,
      -- Physical
      SUM(active_physical_bytes) / POWER(1024, 3) AS active_physical_gib,
      SUM(active_physical_bytes - time_travel_physical_bytes) / POWER(1024, 3)
        AS active_no_tt_physical_gib,
      SUM(long_term_physical_bytes) / POWER(1024, 3)
        AS long_term_physical_gib,
      -- Restorable physical (Time Travel & Fail-Safe)
      SUM(time_travel_physical_bytes) / POWER(1024, 3)
        AS time_travel_physical_gib,
      SUM(fail_safe_physical_bytes) / POWER(1024, 3)
        AS fail_safe_physical_gib
    FROM
      `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
    WHERE
      total_physical_bytes + fail_safe_physical_bytes > 0
      AND table_type = 'BASE TABLE'
    GROUP BY
      1
  )
SELECT
  '{region}' AS region,
  dataset_name,
  ROUND(active_logical_gib, 2) AS active_logical_gib,
  ROUND(long_term_logical_gib, 2) AS long_term_logical_gib,
  ROUND(active_physical_gib, 2) AS active_physical_gib,
  ROUND(long_term_physical_gib, 2) AS long_term_physical_gib,
  ROUND(time_travel_physical_gib, 2) AS time_travel_physical_gib,
  ROUND(fail_safe_physical_gib, 2) AS fail_safe_physical_gib,
  -- Compression Ratios
  ROUND(
    SAFE_DIVIDE(active_logical_gib, active_no_tt_physical_gib), 2) AS active_compression_ratio,
  ROUND(
    SAFE_DIVIDE(long_term_logical_gib, long_term_physical_gib), 2) AS long_term_compression_ratio,
  -- Forecast Costs
  ROUND(
    active_logical_gib * active_logical_gib_price, 2) AS forecast_active_logical_cost,
  ROUND(
    long_term_logical_gib * long_term_logical_gib_price, 2) AS forecast_long_term_logical_cost,
  ROUND(
    (active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib)
      * active_physical_gib_price,
    2) AS forecast_active_physical_cost,
  ROUND(
    long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_long_term_physical_cost,
  -- Total Cost Delta (Positive = Physical is cheaper; Negative = Logical is cheaper)
  ROUND(
    (
      (active_logical_gib * active_logical_gib_price)
      + (long_term_logical_gib * long_term_logical_gib_price))
      - (
        (
          active_no_tt_physical_gib
          + time_travel_physical_gib
          + fail_safe_physical_gib)
          * active_physical_gib_price
        + (long_term_physical_gib * long_term_physical_gib_price)),
    2) AS forecast_total_cost_difference
FROM
  storage_sizes
ORDER BY(forecast_active_logical_cost + forecast_active_physical_cost) DESC;

Dataset Storage Configuration Query

Inspect dataset lifecycle parameters (storage_billing_model, max_time_travel_hours, default_partition_expiration_days):

SELECT
  schema_name AS dataset_name,
  option_name,
  option_value
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE
  option_name IN (
    'storage_billing_model',
    'max_time_travel_hours',
    'default_partition_expiration_days',
    'default_table_expiration_days');

Partition Level Aging & Storage Query

Inspect partition-level storage and modification timestamps to identify mature unpartitioned tables or historical partitions:

SELECT
  table_name,
  partition_id,
  last_modified_time,
  ROUND(total_logical_bytes / POWER(1024, 3), 2) AS total_logical_gib
FROM
  `{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.PARTITIONS
WHERE
  table_name = '{table_name}'
ORDER BY
  partition_id DESC;

Daily Storage Usage Timeline (Time-Integral Conversion)

Convert cumulative MiB * seconds storage metrics from TABLE_STORAGE_USAGE_TIMELINE to daily average GiB:

SELECT
  usage_date,
  table_schema AS dataset_name,
  -- Logical average daily GiB: (MiB * seconds / 86400 seconds) / 1024 MiB_per_GiB
  ROUND(SUM(billable_total_logical_usage) / 86400 / 1024, 2) AS avg_daily_logical_gib,
  ROUND(SUM(billable_active_logical_usage) / 86400 / 1024, 2) AS avg_daily_active_logical_gib,
  ROUND(SUM(billable_long_term_logical_usage) / 86400 / 1024, 2) AS avg_daily_long_term_logical_gib,
  -- Physical average daily GiB: (MiB * seconds / 86400 seconds) / 1024 MiB_per_GiB
  ROUND(SUM(billable_total_physical_usage) / 86400 / 1024, 2) AS avg_daily_physical_gib,
  ROUND(SUM(billable_active_physical_usage) / 86400 / 1024, 2) AS avg_daily_active_physical_gib,
  ROUND(SUM(billable_long_term_physical_usage) / 86400 / 1024, 2)
    AS avg_daily_long_term_physical_gib
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE
WHERE
  usage_date >= DATE_SUB(CURRENT_DATE('PST8PDT'), INTERVAL 7 DAY)
GROUP BY
  1, 2
ORDER BY
  usage_date DESC,
  avg_daily_logical_gib DESC;

Table Partitioning, Clustering & Filter Enforcement Audit

Verify whether referenced tables have DATE, TIMESTAMP, DATETIME, or INT64 integer-range partitioning (is_partitioning_column = 'YES' and data_type), which high-cardinality filtering and grouping columns are in the CLUSTER BY key (clustering_ordinal_position), and whether require_partition_filter is enabled in INFORMATION_SCHEMA.TABLE_OPTIONS. Tables where partitioning_column IS NULL are unpartitioned (DATE / TIMESTAMP / DATETIME indicate time-unit partitioning; INT64 indicates integer-range partitioning via RANGE_BUCKET), and tables where clustering_columns IS NULL lack clustering on high-cardinality filtering and grouping columns (WHERE and GROUP BY). TABLES, COLUMNS, and TABLE_OPTIONS support dataset ({dataset_id}) or regional (region-{region}) scoping.

-- Verify whether referenced tables have DATE, TIMESTAMP, DATETIME, or INT64
-- integer-range partitioning, high-cardinality clustering columns, and
-- require_partition_filter enabled:
SELECT
  c.table_schema AS dataset_id,
  c.table_name,
  MAX(IF(c.is_partitioning_column = 'YES', c.column_name, NULL))
    AS partitioning_column,
  MAX(IF(c.is_partitioning_column = 'YES', c.data_type, NULL))
    AS partitioning_data_type,
  CASE MAX(IF(c.is_partitioning_column = 'YES', c.data_type, NULL))
    WHEN 'DATE' THEN 'DATE partitioning'
    WHEN 'TIMESTAMP' THEN 'TIMESTAMP partitioning'
    WHEN 'DATETIME' THEN 'DATETIME partitioning'
    WHEN 'INT64' THEN 'INT64 integer-range partitioning'
    ELSE 'UNPARTITIONED'
    END AS partitioning_type,
  STRING_AGG(
    IF(c.clustering_ordinal_position IS NOT NULL, c.column_name, NULL),
    ', '
    ORDER BY c.clustering_ordinal_position)
    AS clustering_columns,  -- High-cardinality filtering and grouping columns in CLUSTER BY
  COALESCE(MAX(opt.option_value), 'false') AS require_partition_filter
FROM
  `{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN
  `{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.TABLES AS t
  ON
    c.table_catalog = t.table_catalog
    AND c.table_schema = t.table_schema
    AND c.table_name = t.table_name
LEFT JOIN
  `{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.TABLE_OPTIONS AS opt
  ON
    c.table_catalog = opt.table_catalog
    AND c.table_schema = opt.table_schema
    AND c.table_name = opt.table_name
    AND opt.option_name = 'require_partition_filter'
WHERE
  t.table_type = 'BASE TABLE'
GROUP BY
  c.table_schema,
  c.table_name
ORDER BY
  c.table_name;

Search Index Coverage Query

Inspect search index status, coverage, backlog, and storage for a dataset. A coverage_percentage of 0 means the index is not usable in a SEARCH query, even if some data has already been indexed. An index on a base table smaller than 10 GB is not populated, so its coverage_percentage stays 0.

SELECT
  table_name,
  index_name,
  index_status,
  coverage_percentage,
  unindexed_row_count,
  ROUND(total_logical_bytes / POWER(1024, 3), 2) AS index_logical_gib,
  analyzer,
  last_refresh_time,
  ddl
FROM
  `{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.SEARCH_INDEXES
ORDER BY
  coverage_percentage ASC;

Placeholder Definitions

{project_id}: Google Cloud project ID.
{region}: Storage region (e.g. us, europe-west1).
{dataset_id}: Dataset name/schema containing the table.
{table_name}: Table name to inspect.

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