Bigquery Observability

BigQuery Observability

Tool Selection

<!-- mdformat off -->

<!-- mdformat on -->

Prerequisites & Environment Setup

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

1. Google Cloud SDK: Ensure the Google Cloud SDK is installed and configured.

2. Project Selection: Set the active Google Cloud project:

   gcloud config set project {project_id}

3. API Enablement: Ensure the BigQuery and Cloud Monitoring APIs are enabled:

   gcloud services enable bigquery.googleapis.com monitoring.googleapis.com

4. 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"

5. 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.

6. 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

1. 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}

2. 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

1. 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}).

2. 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.

3. 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.

4. 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:

1. 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.
2. 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.
3. 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).
4. 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).