BigQuery Observability

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` (Monarch / 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:
bash
```
gcloud config set project {project_id}
```
API Enablement: Ensure the BigQuery and Cloud Monitoring APIs are enabled:
bash
```
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.

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).
bash
```
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:

googlesql

`{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:

googlesql

`{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

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, and billing model evaluation.

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, 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

bigquery-observability

NPX Install

Tags

SKILL.md Content