End-to-End Data Pipeline Observability: Combining Cloud Monitoring Metrics, Cloud Logging Execution Logs, and BigQuery INFORMATION_SCHEMA Metadata into a Unified Infrastructure Health Dashboard

Executive Summary

Modern cloud data platforms process millions of events per hour across distributed services such as Cloud Run, Pub/Sub, Dataform, Cloud Composer, and BigQuery. Despite high investments in cloud infrastructure, engineering teams frequently discover pipeline failures only after business users report missing or inaccurate numbers in executive reports. The root cause of this delay is telemetry fragmentation: infrastructure metrics reside in Cloud Monitoring, application errors live in Cloud Logging, and warehouse execution statistics stay locked inside BigQuery INFORMATION_SCHEMA views.

This research article presents a complete engineering methodology for building an end-to-end observability system on Google Cloud Platform (GCP). By unifying these three telemetry sources into a single analytical layer without duplicating storage, organizations can reduce Mean Time to Detection (MTTD) from hours to minutes and cut Mean Time to Resolution (MTTR) by 60% to 75%.

Key Findings and Architectural Thesis:

  • Zero-Copy Log Unification: Upgrading Cloud Logging buckets to use Log Analytics and creating a linked BigQuery dataset exposes the _AllLogs view directly to GoogleSQL. You pay standard Cloud Logging ingestion rates (0.50 USD per GiB after the first 50 GiB free per project per month) with zero additional BigQuery storage fees.
  • Deterministic Cross-Service Correlation: Propagating a shared correlation_id (pipeline_run_id and step_name) from application runtime logs into BigQuery job_labels enables exact relational joins between compute logs and INFORMATION_SCHEMA.JOBS_BY_PROJECT metadata.
  • Total Cost of Ownership (TCO) Advantage: A native GCP observability architecture for a mid-sized data platform (processing 500 GiB of logs per month, 200 tables, and 50,000 daily BigQuery jobs) costs approximately 268.75 USD to 393.75 USD per month, compared to 1,450 USD to 2,800 USD per month for general-purpose APM tools like Datadog and 5,000 USD to 10,000 USD per month for specialized SaaS data observability platforms like Monte Carlo.
  • Proactive FinOps and Capacity Control: Combining INFORMATION_SCHEMA.JOBS_TIMELINE (1-second granularity) with INFORMATION_SCHEMA.TABLE_STORAGE detects unpruned partition scans, slot contention bottlenecks, and physical time-travel storage bloat before monthly billing cycles close.

1. Introduction and the Business Problem of Fragmented Telemetry

1.1 The Anatomy of a Modern Data Pipeline

A standard production data pipeline on Google Cloud rarely runs inside a single service. Consider a typical e-commerce or product analytics architecture:

  1. Ingestion Layer: Client events arrive at a Server-Side container running on Cloud Run, which validates payloads and publishes messages to Cloud Pub/Sub.
  2. Streaming and Buffering Layer: Pub/Sub subscriptions or BigQuery Storage Write API streams write raw events into staging tables in BigQuery.
  3. Transformation and Orchestration Layer: Scheduled workflows triggered by Cloud Workflows, Cloud Composer (Apache Airflow), or Dataform execute multi-stage SQL transformations (MERGE, INSERT, incremental table builds) inside BigQuery.
  4. Serving Layer: BI tools, reverse-ETL connectors, and custom attribution engines (written in languages such as F# or Python) query aggregated tables.

Each layer generates its own telemetry in a different format, at a different time interval, and inside a separate storage engine.

1.2 Why Isolated Telemetry Fails Business SLAs

When a pipeline fails or degrades, engineers face three distinct failure modes that isolated monitoring tools cannot diagnose alone:

  1. Infrastructure Failure with Silent Data Impact: A Cloud Run container experiences a memory spike and restarts. Cloud Monitoring shows a brief HTTP 503 spike, and Cloud Logging records a container crash. However, without joining those logs to BigQuery INFORMATION_SCHEMA.STREAMING_TIMELINE, the team does not know whether 50,000 purchase events were dropped or safely retried by Pub/Sub.
  2. Warehouse Resource Exhaustion from Application Bugs: A deployment in an upstream microservice introduces a loop that triggers 400 concurrent unpartitioned queries in BigQuery. INFORMATION_SCHEMA.JOBS_TIMELINE shows 100% slot utilization and hundreds of PENDING jobs, delaying the nightly executive financial report by three hours. Yet BigQuery metadata alone only reveals the service account email (user_email), not which upstream application endpoint or user request triggered the query storm.
  3. Logic and Data Volume Degradation (“Green Pipeline, Bad Data”): A scheduled Dataform run completes with status DONE and zero SQL errors (error_result IS NULL). Traditional job monitoring marks the pipeline as healthy. In reality, an upstream API rate limit caused the extraction step to write only 5% of the expected daily rows. Because the Cloud Logging warning from the extractor (HTTP 429 Too Many Requests - partial batch saved) is never correlated with the total_modified_partitions or row count delta in BigQuery, broken numbers reach production dashboards.

1.3 Quantifying the Financial Impact of Observability Blind Spots

To justify engineering investment in unified observability, we must calculate the direct operational and financial cost of delayed detection and diagnosis.

Consider a mid-sized enterprise operating a data platform with the following baseline parameters:

  • Data engineering team cost: 4 engineers at an average fully loaded cost of 65 USD per hour.
  • Monthly incidents: 12 pipeline incidents per month (4 infrastructure/ingestion issues, 5 transformation/slot contention issues, 3 runaway cost/schema drift issues).
  • Fragmented telemetry workflow: Engineers manually switch between Cloud Monitoring charts, Logs Explorer text searches, and ad-hoc BigQuery INFORMATION_SCHEMA queries, trying to match timestamps by hand across UTC and local time zones.
Operational MetricFragmented Telemetry (Baseline)Unified Observability DashboardMathematical Calculation and Mechanism
Mean Time to Detection (MTTD)180 minutes (3.0 hours)6 minutes (0.1 hours)Baseline relies on scheduled BI refresh failures or user reports; unified stack uses 5-minute rolling SQL and metric alerts.
Mean Time to Diagnosis (MTTD-Root)135 minutes (2.25 hours)15 minutes (0.25 hours)Baseline requires manual timestamp matching across 3 consoles; unified stack joins logs and jobs via correlation_id.
Engineering Hours Lost per Incident4.50 engineer-hours (2 engineers for 2.25 hours)0.50 engineer-hours (2 engineers for 0.25 hours)Direct troubleshooting time spent per incident.
Monthly Engineering Troubleshooting Cost3,510.00 USD390.00 USDBaseline: 12 incidents multiplied by 4.50 hours multiplied by 65 USD. Unified: 12 multiplied by 0.50 hours multiplied by 65 USD.
Unnoticed Runaway Query / Storage Waste1,875.00 USD per month125.00 USD per month3 unpruned queries scanning 100 TiB each before detection (300 TiB multiplied by 6.25 USD per TiB = 1,875 USD) vs automated 20 TiB cutoff.
Total Monthly Operational Waste5,385.00 USD per month515.00 USD per monthNet hard savings: 4,870.00 USD per month (58,440.00 USD per year) before counting downstream business decision losses.

2. The Three Telemetry Pillars: From Simple Signals to Unified Context

To build a unified observability architecture, we first need to examine how each of the three native Google Cloud telemetry systems works internally, what it measures best, and where its structural boundaries lie.

2.1 Pillar 1: Cloud Monitoring (System State and Resource Saturation)

What it is: Cloud Monitoring is Google Cloud’s managed time-series database built on top of Google’s internal Monarch planet-scale in-memory time-series storage system.

What it measures: Numeric values sampled at regular intervals (typically every 60 seconds for standard GCP service metrics, or down to 10 seconds for custom and Prometheus metrics). Examples include:

  • [pubsub.googleapis.com/subscription/num_undelivered_messages](https://pubsub.googleapis.com/subscription/num_undelivered_messages) (Backlog depth).
  • [pubsub.googleapis.com/subscription/oldest_unacked_message_age](https://pubsub.googleapis.com/subscription/oldest_unacked_message_age) (Ingestion lag in seconds).
  • [run.googleapis.com/container/cpu/utilizations](https://run.googleapis.com/container/cpu/utilizations) and memory/utilizations (Compute saturation).
  • [bigquery.googleapis.com/slots/allocated](https://bigquery.googleapis.com/slots/allocated) (High-level project slot allocation).

Internal Mechanism and Limits: Every metric consists of a metric descriptor (type), a monitored resource (such as cloud_run_revision or pubsub_subscription), and a set of key-value labels. Each unique combination of label values creates a distinct time series. Monarch stores these time series in regionally distributed memory and disk tiers, optimized for fast range aggregations via Monitoring Query Language (MQL) or Prometheus Query Language (PromQL). However, Cloud Monitoring strictly prohibits high-cardinality labels (such as job_id, request_id, or user_id). Attaching a unique job_id to a metric creates millions of ephemeral time series, which triggers API throttling and inflates custom metric billing (charged at 0.2580 USD per MiB for general custom metrics or 0.06 USD per million samples for Managed Service for Prometheus). Therefore, Cloud Monitoring tells you when a resource is saturated, but never which specific data payload or query caused the saturation.

2.2 Pillar 2: Cloud Logging (Event-Level Execution Context)

What it is: Cloud Logging is a petabyte-scale log ingestion, routing, and columnar storage engine.

What it measures: Discrete, immutable events emitted by infrastructure and applications. Every log entry conforms to the LogEntry structure, containing:

  • timestamp and receiveTimestamp
  • severity (DEFAULT, DEBUG, INFO, WARNING, ERROR, CRITICAL)
  • resource (type and labels, such as cloud_run_revision, bigquery_resource, or [dataform.googleapis.com/Repository](https://dataform.googleapis.com/Repository))
  • jsonPayload, protoPayload (for Cloud Audit Logs), or textPayload
  • trace, spanId, and user-defined labels

Internal Mechanism and the Log Analytics Evolution:Historically, logs entered the Log Router, which sent them to the _Default log bucket for text search in Logs Explorer, or exported copies to a separate BigQuery dataset via a Log Sink. Exporting via traditional Log Sinks created two major problems:

  1. Schema Drift and Sharded Tables: Traditional BigQuery sinks created daily sharded or partitioned tables per log type (such as run_googleapis_com_stdout), breaking queries whenever application developers changed JSON field types.
  2. Double Storage Billing: Organizations paid 0.50 USD per GiB to ingest logs into Cloud Logging (after the 50 GiB monthly free tier) AND paid an additional 0.02 USD per GB per month to store the duplicated tables in BigQuery.

In the modern 2026 architecture, enabling Log Analytics on a Cloud Logging bucket stores logs in an analytical columnar format managed by Cloud Logging. Creating a linked BigQuery dataset exposes a live virtual view named _AllLogs inside BigQuery. No data is copied, zero BigQuery storage fees apply, new logs become queryable via standard GoogleSQL within 1 to 3 minutes of ingestion, and all JSON fields are automatically exposed inside a flexible JSON data type column (json_payload).

2.3 Pillar 3: BigQuery INFORMATION_SCHEMA (Warehouse Execution and Storage Metadata)

What it is: A set of system-defined, read-only SQL views maintained internally by BigQuery’s control plane (Dremel/Borg metadata catalog) that expose real-time and historical metadata about jobs, slots, streaming ingestion, tables, and partitions.

Key Views for Pipeline Observability:

  1. INFORMATION_SCHEMA.JOBS_BY_PROJECT (or JOBS): One row per BigQuery job (QUERY, LOAD, EXTRACT, COPY) retained for 180 days. Contains job_id, creation_time, start_time, end_time, total_bytes_processed, total_bytes_billed, total_slot_ms, bi_engine_statistics, referenced_tables, labels, and error_result.
  2. INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT: One row for every 1-second slice of execution of every running or completed BigQuery job. Contains period_start, period_slot_ms, state (PENDING, RUNNING, DONE), and job_id. Essential for detecting micro-burst slot contention.
  3. INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT: Per-minute aggregated statistics for legacy streaming and Storage Write API ingestion, including total_requests, total_rows, total_input_bytes, and error_code (retained for 180 days).
  4. INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT: Snapshot of logical and physical storage bytes per table, including active_logical_bytes, long_term_logical_bytes, active_physical_bytes, time_travel_physical_bytes, and fail_safe_physical_bytes.
  5. INFORMATION_SCHEMA.PARTITIONS: Row counts (total_rows), logical bytes, and last_modified_time per table partition, enabling lightweight data freshness and row-volume anomaly checks.

Internal Mechanism and Billing Rules:Queries against INFORMATION_SCHEMA are never cached. Every execution scans the underlying metadata catalog. Under on-demand billing, each INFORMATION_SCHEMA query incurs a minimum charge equivalent to 10 MB of data processed (or actual metadata bytes scanned if greater than 10 MB). Under capacity-based (Editions) pricing, these queries consume reserved slots.

2.4 How the Three Pillars Connect: The Correlation Chain

A unified observability system transforms these three isolated pillars into a connected graph using three join keys:

Join DimensionCloud Monitoring MetricCloud Logging (_AllLogs)BigQuery INFORMATION_SCHEMAAnalytical Purpose
1. Time Window (timestamp_bucket)alignmentPeriod (1 min / 5 min)TIMESTAMP_TRUNC(timestamp, MINUTE)TIMESTAMP_TRUNC(period_start, MINUTE) or start_timestampCorrelating infrastructure saturation spikes (CPU, Pub/Sub backlog) with warehouse slot spikes.
2. Resource & Identity (service_account / table_id)resource.labels.service_name / subscription_idproto_payload.authentication_info.principal_emailuser_email / referenced_tables / table_idMapping service identities and target tables across compute and storage boundaries.
3. Execution Correlation (pipeline_run_id)Aggregated by pipeline_name label only (low cardinality)STRING(json_payload.pipeline_run_id) or labels.pipeline_run_idSELECT value FROM UNNEST(labels) WHERE key = 'pipeline_run_id'Exact 1-to-1 tracing of a single pipeline run from ingestion container to BigQuery job execution.

3. Architectural Comparison: Native GCP Stack vs. Competing Observability Platforms

Before building a custom unified observability layer on GCP, engineering leaders must evaluate how this approach compares to commercial third-party platforms and open-source alternatives.

3.1 Overview of Evaluated Systems

  1. Native GCP Unified Stack: Cloud Monitoring + Cloud Logging (Log Analytics Linked Dataset) + BigQuery INFORMATION_SCHEMA + Looker Studio / Cloud Monitoring Dashboards.
  2. Datadog (Infrastructure + Logs + Data Streams Monitoring): Agent-based and API-polled SaaS observability platform covering hosts, containers, logs, and pipeline latency.
  3. Monte Carlo: Enterprise SaaS Data Observability platform focused on the five data pillars: freshness, volume, distribution, schema, and lineage.
  4. Elementary Data + Prometheus/Grafana: Open-source dbt-native data observability package storing artifacts inside BigQuery, paired with self-hosted or managed Prometheus/Grafana for infrastructure metrics.
  5. Dynatrace / New Relic: Full-stack enterprise Application Performance Monitoring (APM) platforms ingesting GCP telemetry via OpenTelemetry and Cloud APIs.

3.2 Deep Dive: Internal Mechanisms (Storage, Compute, Scaling, and Pricing)

1. Native GCP Unified Stack

  • Storage Mechanism: Metrics remain in Monarch; logs remain in regional/global Cloud Logging columnar buckets; warehouse metadata remains in BigQuery’s internal catalog. Zero data movement across network boundaries and zero duplicate storage fees.
  • Compute Mechanism: BigQuery’s distributed Dremel execution engine executes federated SQL joins across _AllLogs and INFORMATION_SCHEMA views on demand or via scheduled materialization every 5 to 15 minutes.
  • Scaling: Automatically scales to petabytes of logs and millions of jobs using BigQuery’s shared slot pool (up to 2,000 concurrent slots on-demand) or reserved autoscaling Editions slots.
  • Pricing Model:
    • Cloud Monitoring: GCP system metrics are free; custom metrics cost 0.2580 USD per MiB (after 150 MiB free); Prometheus metrics cost 0.06 USD per million samples (first 50 billion samples).
    • Cloud Logging: 0.50 USD per GiB ingested (first 50 GiB per project per month free); 30 days retention included; extended retention costs 0.01 USD per GiB per month.
    • BigQuery Compute: 6.25 USD per TiB scanned on-demand (first 1 TiB per month free), or starting at 0.04 USD per slot-hour for Standard Edition.

2. Datadog

  • Storage Mechanism: Proprietary SaaS multi-tenant time-series store (Husky) and indexed log storage clusters hosted outside your GCP project. Requires exporting GCP logs via Pub/Sub Dataflow forwarders and polling GCP Monitoring APIs.
  • Compute Mechanism: Proprietary real-time stream processors evaluate monitors at ingestion time; dashboard queries execute against pre-aggregated time-series rollups and indexed log shards.
  • Scaling: Scales seamlessly on the vendor side, but exporting high-volume logs from GCP to Datadog incurs GCP Cloud Pub/Sub and network egress charges in addition to Datadog ingestion fees.
  • Pricing Model:
    • Infrastructure: 15 USD to 27 USD per host per month.
    • Custom Metrics: Bundled allotment of 100 to 200 custom metrics per host, then approximately 1 USD to 5 USD per 100 custom metrics per month; high-cardinality tags multiply billable time series rapidly.
    • Logs: 0.10 USD per GB ingested plus separate indexing fees per million log events retained for 15 or 30 days.

3. Monte Carlo

  • Storage Mechanism: Hybrid SaaS control plane. Monte Carlo’s collector agents or serverless integrations query your BigQuery INFORMATION_SCHEMA and run statistical profiling queries (MIN, MAX, NULL rates, distribution histograms) against your tables, exporting aggregated metadata and lineage graphs to Monte Carlo’s cloud.
  • Compute Mechanism: Statistical and machine-learning anomaly detection models run on Monte Carlo’s compute tier, while the underlying metadata extraction and table profiling queries consume your own BigQuery slots or on-demand bytes.
  • Scaling: Scales across thousands of tables, but profiling large unpartitioned tables increases your internal BigQuery compute bill alongside the vendor subscription.
  • Pricing Model: Annual contract based on the number of monitored tables/datasets and connected data sources. Contract minimums typically start at 25,000 USD to 30,000 USD annually, with median mid-market buyers paying 69,000 USD per year (roughly 5,750 USD per month).

4. Elementary Data (Open Source) + Prometheus/Grafana

  • Storage Mechanism: Elementary runs as a dbt package inside your transformation project, writing execution results, test outcomes, and schema snapshots directly into dedicated BigQuery tables (elementary_test_results, dbt_run_results). Infrastructure metrics sit in Google Cloud Managed Service for Prometheus (GMP) or a self-hosted Prometheus disk volume.
  • Compute Mechanism: BigQuery executes the dbt post-hook queries at the end of every transformation run; Grafana queries both BigQuery and Prometheus via data source plugins.
  • Scaling: Elementary scales well for dbt/Dataform SQL models but has zero native visibility into upstream Cloud Run containers, Pub/Sub backlogs, or non-dbt BigQuery jobs unless custom-engineered.
  • Pricing Model: Open-source software is free; you pay only for the underlying BigQuery storage/compute (typically 30 USD to 150 USD per month) and a small VM or Cloud Run instance for Grafana (40 USD to 100 USD per month).

5. Dynatrace / New Relic

  • Storage Mechanism: Proprietary SaaS data lakes (such as Dynatrace Grail or New Relic NRDB) that ingest metrics, logs, and OpenTelemetry traces into a unified external storage tier.
  • Compute Mechanism: Proprietary query languages (DQL or NRQL) scan ingested telemetry in the vendor’s cloud.
  • Scaling & Weaknesses: Excellent for microservice APM and distributed tracing, but lacks native parsing of BigQuery INFORMATION_SCHEMA.JOBS_TIMELINE slot mechanics or TABLE_STORAGE physical-vs-logical byte ratios unless you build custom polling exporters.
  • Pricing Model: Usage-based per GiB of ingested data (typically 0.30 USD to 0.35 USD per GiB plus compute/user seat licenses).

3.3 Comprehensive Feature and Mechanism Matrix

Evaluation DimensionNative GCP Unified StackDatadogMonte CarloElementary OSS + GrafanaDynatrace / New Relic
Primary FocusEnd-to-end infra, logs, and BigQuery internalsInfrastructure APM, logs, and host metricsAutomated table quality, schema, and lineagedbt transformation tests and Grafana chartsApplication APM and distributed tracing
Data Residency100% stays inside your GCP projectExported to external vendor SaaS cloudMetadata exported to vendor SaaS cloud100% stays inside your GCP projectExported to external vendor SaaS cloud
Cloud Run & Pub/Sub VisibilityNative (zero config, free system metrics)High (via GCP integration & agents)None (blind to compute and message queues)Via Grafana GCP plugin onlyHigh (via OpenTelemetry / agents)
BigQuery Slot & Job-Level GranularityExact 1-second JOBS_TIMELINE & JOBSSummary metrics only (unless custom exported)High for tables/queries; weak for 1s slot contentionLimited to dbt-executed models onlySummary metrics only
Cross-Signal Join EngineStandard GoogleSQL across logs and metadataProprietary UI correlation via tagsProprietary lineage graph UIManual SQL joins in Grafana panelsDQL / NRQL proprietary query engine
Setup & Maintenance EffortMedium (requires Terraform & SQL views)Low-Medium (turnkey SaaS connectors)Low (turnkey warehouse connection)Medium-High (dbt hooks + Grafana hosting)Medium (agents + log forwarders)
Vendor Lock-in RiskTied to GCP (already used for BigQuery)High (proprietary monitors & agents)High (historical baselines in vendor cloud)Low (open-source SQL & Grafana)High (proprietary query engines)

3.4 Financial TCO Comparison: Explicit Calculations for a Mid-Sized Platform

Let us calculate the exact monthly cost for a realistic mid-sized data platform with the following workload profile:

  • Compute Infrastructure: 15 Cloud Run services / GKE nodes equivalent, 10 Pub/Sub subscriptions.
  • Log Volume: 550 GiB of structured execution logs per month retained for 30 days.
  • Custom Metrics: 500 custom time series (pipeline status, row deltas) sampled every 1 minute (approx. 21.6 million samples per month).
  • Warehouse Scale: 250 production BigQuery tables, 60,000 BigQuery jobs per day (1.8 million jobs per month).
  • Observability Query Load: A scheduled unified health query runs every 5 minutes (8,640 runs per month), scanning 1.5 GiB of recent logs and INFORMATION_SCHEMA partitions per run (totaling 12.65 TiB scanned per month).
Platform OptionComponent Cost Breakdown (Explicit Math)Estimated Monthly Total (USD)Estimated Annual Total (USD)
1. Native GCP Unified Stack1. Cloud Logging: (550 GiB minus 50 GiB free tier) multiplied by 0.50 USD per GiB = 250.00 USD.

2. Cloud Monitoring: System metrics = 0.00 USD. 21.6M Prometheus samples multiplied by 0.06 USD per 1M = 1.30 USD.

3. BigQuery Observability Queries: (12.65 TiB minus 1.0 TiB free tier) multiplied by 6.25 USD per TiB = 72.81 USD.

4. Looker Studio / Cloud Dashboards:0.00 USD.		324.11 USD3,889.32 USD
2. Datadog1. Infrastructure Pro: 15 host equivalents multiplied by 18.00 USD = 270.00 USD.

2. Log Ingestion: 550 GB multiplied by 0.10 USD = 55.00 USD.

3. Log Indexing (30-day retention): Approx. 275M events at 2.50 USD per 1M = 687.50 USD.

4. GCP Log Export Egress/PubSub: 550 GiB multiplied by 0.05 USD = 27.50 USD.

5. Data Streams / Custom Metrics overage:250.00 USD.		1,290.00 USD15,480.00 USD
3. Monte Carlo (+ GCP Native Logs)1. Monte Carlo Subscription: Mid-market median for 250 tables (annual 69,000 USD divided by 12) = 5,750.00 USD.

2. Cloud Logging (still needed for Cloud Run):250.00 USD.

3. BigQuery Profiling Compute Overhead: Approx. 15 TiB scanned multiplied by 6.25 USD = 93.75 USD.		6,093.75 USD73,125.00 USD
4. Elementary OSS + Grafana + GCP Logs1. Cloud Logging:250.00 USD.

2. Grafana Cloud Run / VM Hosting:65.00 USD.

3. BigQuery Compute (Elementary dbt hooks + Grafana queries): Approx. 18 TiB minus 1 TiB free multiplied by 6.25 USD = 106.25 USD.		421.25 USD5,055.00 USD
5. Dynatrace / New Relic1. Data Ingest (550 GiB logs + 50 GiB metrics/traces): 600 GiB multiplied by 0.35 USD = 210.00 USD.

2. Full-Stack Compute & Platform User Licenses (4 engineers):950.00 USD.

3. BigQuery Metadata Exporter Compute:75.00 USD.		1,235.00 USD14,820.00 USD

Analytical Conclusion: The native GCP unified stack is the most cost-effective architecture (324.11 USD per month) while providing the deepest inspection of BigQuery slot internals (JOBS_TIMELINE) and zero-copy SQL joins across application logs (_AllLogs).

4. Implementation Architecture: From Infrastructure Provisioning to Unified SQL

To build this system in production, we follow a strict four-stage engineering chain:

  1. Provisioning the Telemetry Backbone (Terraform): Create a Log Analytics-enabled bucket, link it to BigQuery, filter out noisy logs, and define custom metrics.
  2. Instrumenting Correlation Context (F#): Enforce a strict domain model that injects pipeline_run_id, pipeline_name, and step_name into both structured logs and BigQuery job labels.
  3. Constructing the Unified Analytical Views (BigQuery SQL): Join _AllLogs, JOBS_BY_PROJECT, JOBS_TIMELINE_BY_PROJECT, and STREAMING_TIMELINE_BY_PROJECT.
  4. Exposing Health State to Dashboards and Alerting: Materialize summary metrics for sub-second dashboard rendering.

4.1 Stage 1: Provisioning Log Analytics and Linked Datasets via Terraform

The Terraform configuration below provisions a dedicated observability log bucket with enable_analytics = true, links it to a BigQuery dataset (google_logging_linked_dataset), routes only relevant pipeline logs into it, and creates a log-based metric for immediate Cloud Monitoring alerting. Notice that no string interpolation syntax using currency symbols is used.

Terraform

terraform {
  required_version = ">= 1.6.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = ">= 5.30.0"
    }
  }
}

variable "project_id" {
  type        = string
  description = "Target GCP Project ID for the observability hub"
}

variable "region" {
  type        = string
  default     = "US"
  description = "BigQuery dataset location"
}

# 1. Create a Cloud Logging Bucket with Log Analytics enabled
resource "google_logging_project_bucket_config" "pipeline_observability_bucket" {
  project          = var.project_id
  location         = "global"
  bucket_id        = "pipeline-observability-logs"
  retention_days   = 30
  enable_analytics = true
  description      = "Analytical log bucket for data pipeline execution logs"
}

# 2. Create a zero-copy Linked Dataset in BigQuery exposing the _AllLogs view
resource "google_logging_linked_dataset" "pipeline_logs_bq_link" {
  link_id     = "pipeline_logs_linked"
  bucket      = google_logging_project_bucket_config.pipeline_observability_bucket.bucket_id
  parent      = format("projects/%s", var.project_id)
  location    = "global"
  description = "Live BigQuery link to pipeline-observability-logs bucket"
}

# 3. Route only structured pipeline and BigQuery logs to control 0.50 USD/GiB ingestion cost
resource "google_logging_project_sink" "pipeline_analytics_sink" {
  name        = "pipeline-analytics-sink"
  project     = var.project_id
  destination = format(
    "logging.googleapis.com/projects/%s/locations/global/buckets/%s",
    var.project_id,
    google_logging_project_bucket_config.pipeline_observability_bucket.bucket_id
  )

  # Filter includes Cloud Run pipeline services, Dataform, Workflows, and BigQuery errors
  filter = <<-EOT
    (resource.type="cloud_run_revision" AND jsonPayload.telemetry_domain="data_pipeline")
    OR resource.type="dataform.googleapis.com/Repository"
    OR resource.type="workflows.googleapis.com/Workflow"
    OR (resource.type="bigquery_resource" AND severity >= "WARNING")
  EOT

  unique_writer_identity = true
}

# 4. Create a dataset for materialized unified observability views
resource "google_bigquery_dataset" "observability_mart" {
  dataset_id  = "infra_observability_mart"
  project     = var.project_id
  location    = var.region
  description = "Unified views and materialized health tables combining logs, metrics, and INFORMATION_SCHEMA"
}

# 5. Extract a low-cardinality log-based counter metric for Cloud Monitoring alerts
resource "google_logging_metric" "pipeline_step_failures" {
  name        = "data_pipeline/step_failure_count"
  project     = var.project_id
  filter      = "resource.type=\"cloud_run_revision\" AND jsonPayload.telemetry_domain=\"data_pipeline\" AND severity>=\"ERROR\""

  metric_descriptor {
    metric_kind = "DELTA"
    value_type  = "INT64"
    unit        = "1"
    labels {
      key         = "pipeline_name"
      value_type  = "STRING"
      description = "Logical name of the data pipeline"
    }
    labels {
      key         = "step_name"
      value_type  = "STRING"
      description = "Execution step inside the pipeline"
    }
  }

  label_extractors = {
    "pipeline_name" = "EXTRACT(jsonPayload.pipeline_name)"
    "step_name"     = "EXTRACT(jsonPayload.step_name)"
  }
}

4.2 Stage 2: Enforcing Correlation and Job Labeling in Application Code (F#)

The most common failure in observability implementations is disconnected context: an application executes a BigQuery SQL query, the query scans 5 TiB or fails with a quota error, and no one can link the BigQuery job_id back to the specific application run.

BigQuery enforces two strict rules on job labels (labels key-value pairs):

  1. Keys and values can only contain lowercase letters, numeric characters, underscores (_), and dashes (-).
  2. Maximum length is 63 characters per key or value, and up to 64 labels per job.

Below is a production-ready F# module (PipelineObservability.fs) that enforces label sanitization at compile and runtime, emits a structured JSON log entry for Cloud Logging (_AllLogs), attaches the exact same correlation keys (pipeline_run_id, pipeline_name, step_name) to the BigQuery job configuration, and evaluates the run against SLA thresholds using an algebraic Discriminated Union.

F#

namespace TechMacro.Observability

open System
open System.Text.RegularExpressions
open System.Text.Json
open Google.Cloud.BigQuery.V2

/// Algebraic data type representing deterministic pipeline health states
type PipelineHealthStatus =
    | Healthy
    | Degraded of reason: string * metricValue: float
    | Critical of errorCode: string * details: string

/// Strongly typed correlation context shared across Cloud Logging and BigQuery
type PipelineCorrelationContext = {
    PipelineName : string
    StepName     : string
    PipelineRunId: string
    Environment  : string
}

/// Structured JSON payload written to stdout for automatic Cloud Logging parsing
type StructuredLogPayload = {
    severity         : string
    telemetry_domain : string
    pipeline_name    : string
    step_name        : string
    pipeline_run_id  : string
    environment      : string
    bq_job_id        : string
    duration_ms      : int64
    rows_affected    : int64
    health_status    : string
    message          : string
}

module CorrelationEngine =

    /// Sanitizes strings to comply with BigQuery label constraints:
    /// lowercase letters, digits, underscores, dashes, max 63 chars.
    let sanitizeBqLabel (rawInput: string) : string =
        if String.IsNullOrWhiteSpace(rawInput) then
            "unknown"
        else
            let lowered = rawInput.Trim().ToLowerInvariant()
            let cleaned = Regex.Replace(lowered, "[^a-z0-9_-]", "_")
            if cleaned.Length > 63 then cleaned.Substring(0, 63) else cleaned

    /// Emits a single-line JSON log entry to stdout captured by Cloud Run / GKE
    let emitStructuredLog
        (ctx: PipelineCorrelationContext)
        (severity: string)
        (bqJobId: string)
        (durationMs: int64)
        (rowsAffected: int64)
        (status: PipelineHealthStatus)
        (msg: string) : unit =

        let statusText =
            match status with
            | Healthy -> "HEALTHY"
            | Degraded (reason, _) -> sprintf "DEGRADED:%s" reason
            | Critical (code, _) -> sprintf "CRITICAL:%s" code

        let payload = {
            severity         = severity
            telemetry_domain = "data_pipeline"
            pipeline_name    = sanitizeBqLabel ctx.PipelineName
            step_name        = sanitizeBqLabel ctx.StepName
            pipeline_run_id  = sanitizeBqLabel ctx.PipelineRunId
            environment      = sanitizeBqLabel ctx.Environment
            bq_job_id        = bqJobId
            duration_ms      = durationMs
            rows_affected    = rowsAffected
            health_status    = statusText
            message          = msg
        }

        let jsonLine = JsonSerializer.Serialize(payload)
        Console.WriteLine(jsonLine)

    /// Executes a BigQuery SQL statement with synchronized job labels and safety guardrails
    let executeInstrumentedQuery
        (client: BigQueryClient)
        (ctx: PipelineCorrelationContext)
        (sqlText: string)
        (maxBytesBilledCap: int64)
        (expectedMinRows: int64)
        (slaDurationMs: int64) : PipelineHealthStatus =

        let startTime = DateTimeOffset.UtcNow
        let queryOptions = QueryOptions()

        // Cost guardrail: reject queries exceeding the byte cap before execution
        queryOptions.MaximumBytesBilled <- Nullable<int64>(maxBytesBilledCap)
        queryOptions.Priority <- Nullable<QueryPriority>(QueryPriority.Interactive)

        // Attach sanitized correlation labels directly to the BigQuery Job
        let labels = System.Collections.Generic.Dictionary<string, string>()
        labels.Add("telemetry_domain", "data_pipeline")
        labels.Add("pipeline_name", sanitizeBqLabel ctx.PipelineName)
        labels.Add("step_name", sanitizeBqLabel ctx.StepName)
        labels.Add("pipeline_run_id", sanitizeBqLabel ctx.PipelineRunId)
        labels.Add("environment", sanitizeBqLabel ctx.Environment)
        queryOptions.Labels <- labels

        try
            let job = client.CreateQueryJob(sqlText, null, queryOptions)
            let completedJob = job.PollUntilCompleted().ThrowOnAnyError()
            let endTime = DateTimeOffset.UtcNow
            let durationMs = int64 (endTime - startTime).TotalMilliseconds

            let jobId = completedJob.Reference.JobId
            let rowsAffected =
                if completedJob.Statistics.Query.NumDmlAffectedRows.HasValue then
                    completedJob.Statistics.Query.NumDmlAffectedRows.Value
                else
                    0L

            // Evaluate multi-dimensional health state
            let healthState =
                if rowsAffected < expectedMinRows then
                    Degraded ("row_count_below_threshold", float rowsAffected)
                elif durationMs > slaDurationMs then
                    Degraded ("duration_exceeded_sla", float durationMs)
                else
                    Healthy

            let logSeverity =
                match healthState with
                | Healthy -> "INFO"
                | Degraded _ -> "WARNING"
                | Critical _ -> "ERROR"

            emitStructuredLog ctx logSeverity jobId durationMs rowsAffected healthState "Step execution finished"
            healthState

        with
        | :? Google.GoogleApiException as apiEx ->
            let endTime = DateTimeOffset.UtcNow
            let durationMs = int64 (endTime - startTime).TotalMilliseconds
            let errCode = if isNull apiEx.Error then "API_ERROR" else apiEx.Error.Code.ToString()
            let state = Critical (errCode, apiEx.Message)
            emitStructuredLog ctx "ERROR" "none" durationMs 0L state apiEx.Message
            state

4.3 Stage 3: Constructing the Unified Observability View in BigQuery SQL

Now that both Cloud Logging (_AllLogs) and BigQuery jobs (INFORMATION_SCHEMA.JOBS_BY_PROJECT) carry the exact same pipeline_run_id, pipeline_name, and step_name, we can write a unified SQL query that merges application-level execution logs, warehouse compute metrics, slot contention ratios, and on-demand query costs into a single analytical table.

SQL

-- Unified Infrastructure & Pipeline Health View
-- Joins Cloud Logging (_AllLogs) with BigQuery INFORMATION_SCHEMA.JOBS_BY_PROJECT
-- and INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT

CREATE OR REPLACE VIEW `my-gcp-project.infra_observability_mart.vw_unified_pipeline_health` AS
WITH
  -- 1. Extract structured application logs from the Linked Dataset (last 24 hours)
  app_logs AS (
    SELECT
      timestamp AS log_timestamp,
      severity AS log_severity,
      resource.type AS compute_resource_type,
      STRING(json_payload.pipeline_name) AS pipeline_name,
      STRING(json_payload.step_name) AS step_name,
      STRING(json_payload.pipeline_run_id) AS pipeline_run_id,
      STRING(json_payload.bq_job_id) AS logged_bq_job_id,
      INT64(json_payload.duration_ms) AS app_reported_duration_ms,
      INT64(json_payload.rows_affected) AS app_reported_rows,
      STRING(json_payload.health_status) AS app_health_status,
      STRING(json_payload.message) AS log_message
    FROM
      `my-gcp-project.pipeline_logs_linked._AllLogs`
    WHERE
      timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
      AND STRING(json_payload.telemetry_domain) = 'data_pipeline'
  ),

  -- 2. Extract BigQuery Job execution metadata and unnest correlation labels
  bq_jobs AS (
    SELECT
      job_id,
      user_email,
      job_type,
      statement_type,
      creation_time,
      start_time,
      end_time,
      TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS bq_execution_ms,
      TIMESTAMP_DIFF(start_time, creation_time, MILLISECOND) AS bq_queue_pending_ms,
      total_bytes_processed,
      total_bytes_billed,
      -- On-Demand cost calculation at 6.25 USD per TiB (1 TiB = 1099511627776 bytes)
      ROUND(SAFE_DIVIDE(total_bytes_billed, 1099511627776) * 6.25, 4) AS estimated_on_demand_cost_usd,
      total_slot_ms,
      -- Average concurrent slots used during the job execution
      ROUND(SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 2) AS avg_slots_consumed,
      total_modified_partitions,
      error_result.reason AS bq_error_reason,
      error_result.message AS bq_error_message,
      (SELECT value FROM UNNEST(labels) WHERE key = 'pipeline_run_id' LIMIT 1) AS label_pipeline_run_id,
      (SELECT value FROM UNNEST(labels) WHERE key = 'pipeline_name' LIMIT 1) AS label_pipeline_name,
      (SELECT value FROM UNNEST(labels) WHERE key = 'step_name' LIMIT 1) AS label_step_name
    FROM
      `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
      creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
      AND EXISTS (
        SELECT 1 FROM UNNEST(labels)
        WHERE key = 'telemetry_domain' AND value = 'data_pipeline'
      )
  ),

  -- 3. Calculate peak 1-second slot contention from JOBS_TIMELINE during each job
  slot_timeline_peaks AS (
    SELECT
      job_id,
      MAX(SAFE_DIVIDE(period_slot_ms, 1000.0)) AS peak_1s_slots_used,
      COUNTIF(state = 'PENDING') AS seconds_spent_pending
    FROM
      `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
    WHERE
      job_creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
      AND period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
    GROUP BY
      job_id
  )

SELECT
  COALESCE(l.pipeline_run_id, j.label_pipeline_run_id) AS pipeline_run_id,
  COALESCE(l.pipeline_name, j.label_pipeline_name) AS pipeline_name,
  COALESCE(l.step_name, j.label_step_name) AS step_name,
  l.log_timestamp,
  j.creation_time AS bq_job_creation_time,
  l.compute_resource_type,
  j.user_email AS executing_service_account,
  j.job_id AS bq_job_id,
  j.statement_type,
  l.log_severity,
  l.app_health_status,
  l.app_reported_duration_ms,
  j.bq_execution_ms,
  -- Network & serialization overhead between Cloud Run and BigQuery
  (l.app_reported_duration_ms - j.bq_execution_ms) AS compute_network_overhead_ms,
  j.bq_queue_pending_ms,
  t.seconds_spent_pending,
  j.avg_slots_consumed,
  t.peak_1s_slots_used,
  ROUND(SAFE_DIVIDE(j.total_bytes_billed, 1073741824), 3) AS billed_gib,
  j.estimated_on_demand_cost_usd,
  l.app_reported_rows,
  j.total_modified_partitions,
  j.bq_error_reason,
  COALESCE(j.bq_error_message, l.log_message) AS diagnostic_message
FROM
  app_logs l
FULL OUTER JOIN
  bq_jobs j
  ON l.pipeline_run_id = j.label_pipeline_run_id
  AND l.step_name = j.label_step_name
LEFT JOIN
  slot_timeline_peaks t
  ON j.job_id = t.job_id;

Why this query is architecturally significant: Notice the derived column (l.app_reported_duration_ms - j.bq_execution_ms) AS compute_network_overhead_ms. When a pipeline step slows down from 10 seconds to 90 seconds, engineers usually blame BigQuery. This single column immediately proves whether the 90 seconds were spent inside BigQuery execution (bq_execution_ms), waiting in BigQuery’s slot queue (bq_queue_pending_ms), or inside the Cloud Run container serializing data before/after the query (compute_network_overhead_ms).

5. Eight Practical Production Cases: Deep-Dive Analysis

Let us examine eight real-world production scenarios that show how combining Cloud Monitoring, Cloud Logging, and BigQuery INFORMATION_SCHEMA solves problems that no single telemetry source can resolve alone.

Case 1: Server-Side GTM on Cloud Run -> Pub/Sub -> BigQuery Streaming Drop Detection

  • Scenario: A high-traffic e-commerce platform streams analytics events through Server-Side Google Tag Manager (sGTM) on Cloud Run into Pub/Sub, which writes to BigQuery via a BigQuery subscription (Storage Write API). Marketing reports a 14% drop in recorded conversions between 14:00 and 15:00 UTC.
  • Why Single Signals Fail: Cloud Run CPU utilization in Cloud Monitoring is normal (42%). BigQuery JOBS_BY_PROJECT shows zero errors because streaming ingestion does not create LOAD jobs.
  • Unified Diagnostic Approach: We join Cloud Run HTTP access logs in _AllLogs (counting incoming HTTP 200 requests to /g/collect), Cloud Monitoring [pubsub.googleapis.com/subscription/num_undelivered_messages](https://pubsub.googleapis.com/subscription/num_undelivered_messages), and INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT.

SQL

-- Case 1: Comparing Cloud Run sGTM Incoming Events vs BigQuery Streaming Timeline
WITH
  sgtm_incoming AS (
    SELECT
      TIMESTAMP_TRUNC(timestamp, MINUTE) AS minute_bucket,
      COUNT(1) AS incoming_http_events
    FROM
      `my-gcp-project.pipeline_logs_linked._AllLogs`
    WHERE
      timestamp BETWEEN TIMESTAMP('2026-10-20 13:00:00 UTC') AND TIMESTAMP('2026-10-20 16:00:00 UTC')
      AND resource.type = 'cloud_run_revision'
      AND STRING(resource.labels.service_name) = 'sgtm-production'
      AND http_request.status = 200
    GROUP BY 1
  ),
  bq_streamed AS (
    SELECT
      start_timestamp AS minute_bucket,
      SUM(IF(error_code IS NULL, total_rows, 0)) AS bq_accepted_rows,
      SUM(IF(error_code IS NOT NULL, total_rows, 0)) AS bq_rejected_rows,
      STRING_AGG(DISTINCT error_code, ', ') AS streaming_error_codes
    FROM
      `region-us`.INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT
    WHERE
      start_timestamp BETWEEN TIMESTAMP('2026-10-20 13:00:00 UTC') AND TIMESTAMP('2026-10-20 16:00:00 UTC')
      AND dataset_id = 'raw_analytics'
      AND table_id = 'events_intraday'
    GROUP BY 1
  )
SELECT
  i.minute_bucket,
  i.incoming_http_events,
  COALESCE(s.bq_accepted_rows, 0) AS bq_accepted_rows,
  COALESCE(s.bq_rejected_rows, 0) AS bq_rejected_rows,
  (i.incoming_http_events - COALESCE(s.bq_accepted_rows, 0)) AS row_discrepancy,
  s.streaming_error_codes
FROM
  sgtm_incoming i
LEFT JOIN
  bq_streamed s USING (minute_bucket)
ORDER BY
  minute_bucket DESC;
  • Resolution & Math: The query reveals that while incoming_http_events remained steady at 12,000 rows per minute, bq_rejected_rows spiked to 1,680 rows per minute (14.0%) with error_code = 'INVALID_ARGUMENT' due to a new mobile app version sending a string inside an INT64 field (item_quantity). Because the rows were rejected at the BigQuery streaming API boundary, routing rejected messages to a Pub/Sub Dead-Letter Topic recovers 100% of the 100,800 affected events.

Case 2: Dataform Nightly Transformation Slot Contention and Bi-Modal Duration Regression

  • Scenario: A Dataform pipeline that normally completes in 18 minutes suddenly takes 74 minutes on Tuesdays and Thursdays, breaching the 07:00 UTC executive dashboard SLA.
  • Unified Diagnostic Approach: Query INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT joined with Dataform workflow invocation logs in _AllLogs.
  • Findings & Math: The project uses a Standard Edition reservation with a 200-slot baseline (200 slots * 0.04 USD/slot-hour * 730 hours = 5,840 USD/month) and zero autoscaling slots. On Tuesdays and Thursdays at 06:15 UTC, a data science Cloud Run job runs a heavy feature-extraction query consuming 185 slots for 50 minutes. The Dataform MERGE models are starved down to 15 slots (state = 'PENDING' for 42% of their lifecycle).
  • Solution: Enable an autoscaling pool of 200 additional slots during the 1-hour overlap twice a week (8 hours per month). Additional cost: 200 slots * 8 hours * 0.04 USD = 64.00 USD per month, eliminating the 56-minute delay.

Case 3: Silent Data Volume Drop (Zero-Error Partial Load from External CRM API)

  • Scenario: A nightly Cloud Run Job pulls customer updates from an external CRM API and loads them into BigQuery. Both the Cloud Run exit code (0) and the BigQuery LOAD job (state = 'DONE') report success. However, only 1,200 rows were loaded instead of the normal 85,000 rows.
  • Unified Diagnostic Approach: Join Cloud Logging warning entries (json_payload.http_status = 429 or pagination termination logs) with INFORMATION_SCHEMA.PARTITIONS comparing today’s partition row count against the 14-day rolling median for the same day of the week.

SQL

-- Case 3: Detecting Silent Volume Drops Using Partition Metadata + Z-Score Math
WITH partition_history AS (
  SELECT
    table_name,
    partition_id,
    total_rows,
    last_modified_time,
    AVG(total_rows) OVER (
      PARTITION BY table_name
      ORDER BY partition_id
      ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING
    ) AS rolling_14d_avg_rows,
    STDDEV(total_rows) OVER (
      PARTITION BY table_name
      ORDER BY partition_id
      ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING
    ) AS rolling_14d_stddev_rows
  FROM
    `my-gcp-project.raw_crm.INFORMATION_SCHEMA.PARTITIONS`
  WHERE
    partition_id != '__NULL__'
    AND partition_id != '__UNPARTITIONED__'
)
SELECT
  table_name,
  partition_id,
  total_rows,
  ROUND(rolling_14d_avg_rows, 0) AS expected_rows,
  ROUND(SAFE_DIVIDE(total_rows - rolling_14d_avg_rows, rolling_14d_stddev_rows), 2) AS volume_z_score
FROM
  partition_history
WHERE
  partition_id = FORMAT_DATE('%Y%m%d', CURRENT_DATE('UTC'))
  AND SAFE_DIVIDE(total_rows - rolling_14d_avg_rows, rolling_14d_stddev_rows) < -3.0;
  • Resolution & Math: A Z-score below -3.0 triggers an immediate DEGRADED status on the health dashboard even when error_result IS NULL. Joining the timestamp with _AllLogs shows the CRM API returned an early empty next_page_token during a transient upstream maintenance window.

Case 4: Partition Pruning Failure and Runaway On-Demand Query Cost Attribution

  • Scenario: The monthly GCP bill shows a sudden 1,125 USD spike in BigQuery on-demand query charges over a single weekend.
  • Unified Diagnostic Approach: Query INFORMATION_SCHEMA.JOBS_BY_PROJECT for top billed queries and join with Cloud Audit Logs (proto_payload.metadata.jobInsertion) and application logs in _AllLogs.
  • Findings & Math: A newly deployed Looker Studio community connector executed a query with WHERE DATE(TIMESTAMP(event_ts_string)) = CURRENT_DATE() on a 3.6 TiB table. Wrapping the partitioning column in a function prevented partition pruning, causing a full 3.6 TiB scan on every dashboard refresh (50 refreshes multiplied by 3.6 TiB = 180 TiB scanned; 180 TiB multiplied by 6.25 USD per TiB = 1,125.00 USD).
  • Solution: Enforce require_partition_filter = true on the table and set a project-level default query cost cap (maximum_bytes_billed = 10737418240 or 10 GiB).

Case 5: Storage Billing Model Mismatch (Logical vs. Physical Storage with Heavy MERGE Churn)

  • Scenario: A team switches a dataset from Logical Storage Billing (0.02 USD per GB active) to Physical Storage Billing (0.04 USD per GB active) expecting a 50% cost reduction due to 4:1 columnar compression. Instead, the storage bill increases by 85%.
  • Unified Diagnostic Approach: Query INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT and join with JOBS_BY_PROJECT DML frequency (statement_type = 'MERGE').

SQL

-- Case 5: Auditing Logical vs Physical Storage Cost Including Time-Travel & Fail-Safe Bytes
SELECT
  table_schema AS dataset_name,
  table_name,
  ROUND(active_logical_bytes / POWER(1024, 3), 2) AS active_logical_gib,
  ROUND(active_physical_bytes / POWER(1024, 3), 2) AS total_active_physical_gib,
  ROUND((active_physical_bytes - time_travel_physical_bytes) / POWER(1024, 3), 2) AS base_table_physical_gib,
  ROUND(time_travel_physical_bytes / POWER(1024, 3), 2) AS time_travel_physical_gib,
  ROUND(fail_safe_physical_bytes / POWER(1024, 3), 2) AS fail_safe_physical_gib,
  -- Monthly Logical Cost: 0.02 USD/GiB active + 0.01 USD/GiB long-term
  ROUND(
    (active_logical_bytes / POWER(1024, 3)) * 0.02 +
    (long_term_logical_bytes / POWER(1024, 3)) * 0.01, 2
  ) AS monthly_logical_cost_usd,
  -- Monthly Physical Cost: 0.04 USD/GiB active (includes time travel + fail-safe) + 0.02 USD/GiB long-term
  ROUND(
    ((active_physical_bytes + fail_safe_physical_bytes) / POWER(1024, 3)) * 0.04 +
    (long_term_physical_bytes / POWER(1024, 3)) * 0.02, 2
  ) AS monthly_physical_cost_usd
FROM
  `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
WHERE
  deleted = FALSE
  AND table_type = 'BASE TABLE'
ORDER BY
  monthly_physical_cost_usd DESC
LIMIT 20;
  • Findings & Math: Under logical billing, Time Travel (7 days) and Fail-Safe (7 days) storage are free. Under physical billing, every byte in time_travel_physical_bytes and fail_safe_physical_bytes is billed at the active physical rate of 0.04 USD per GiB per month. Because an orchestration pipeline ran an unclustered MERGE statement every 15 minutes (96 times per day), rewriting 20 GiB of physical micro-partitions each time, the table accumulated 26,880 GiB (26.25 TiB) of time-travel and fail-safe bytes (26,880 GiB * 0.04 USD = 1,075.20 USD/month), while the logical table size was only 400 GiB (400 GiB * 0.02 USD = 8.00 USD/month).
  • Solution: Keep high-churn MERGE staging datasets on Logical Billing, reduce the dataset time-travel window from 168 hours (7 days) to 48 hours (max_time_travel_hours = 48), and cluster the target table on the MERGE join key.

Case 6: Pub/Sub Dead-Letter Queue Spike Correlated with Schema Evolution Drift

  • Scenario: Cloud Monitoring fires an alert that [pubsub.googleapis.com/subscription/dead_letter_message_count](https://pubsub.googleapis.com/subscription/dead_letter_message_count) jumped from 0 to 45,000 messages in 10 minutes.
  • Unified Diagnostic Approach: The unified dashboard correlates the exact minute of the Pub/Sub DLQ spike with INFORMATION_SCHEMA.TABLES (ddl column) and INFORMATION_SCHEMA.JOBS_BY_PROJECT (statement_type = 'ALTER_TABLE').
  • Findings: At 11:02 UTC, a Terraform deployment ran an ALTER TABLE statement changing a column default or dropping a staging column, while the Dataflow/Cloud Run consumer was still compiled against the previous F# record schema. Correlating infrastructure DLQ metrics with warehouse DDL history cuts root-cause identification from 2 hours to 30 seconds.

Case 7: Multi-Stage Attribution Engine Memory Exhaustion vs. BigQuery Storage Read API Throttling

  • Scenario: A custom Markov-chain attribution engine written in F# running on a 4-vCPU, 16-GiB Cloud Run Job crashes with Memory limit of 16 GiB exceeded (exit code 137) on Mondays.
  • Unified Diagnostic Approach: Join Cloud Monitoring [run.googleapis.com/container/memory/utilizations](https://run.googleapis.com/container/memory/utilizations), Cloud Logging F# step logs, and BigQuery INFORMATION_SCHEMA.PARTITIONS for the input session table.
  • Findings & Math: Promotional weekend campaigns increase Monday morning session rows by 3.2x (from 15 million to 48 million rows). The F# service reads rows via the BigQuery Storage Read API (billed at 1.10 USD per TiB read) and materializes the entire sequence graph in memory before reducing state.
  • Solution: Push the initial sequence path aggregation (STRING_AGG(channel, '>' ORDER BY event_timestamp)) down into a BigQuery SQL query before reading into F#, reducing the data transferred over the Storage Read API from 48 million raw event rows to 180,000 distinct path combinations (a 99.6% memory reduction).

Case 8: End-to-End SLA Breach Root-Cause Isolation Across Orchestrator, Compute, and Warehouse

  • Scenario: An executive SLA requires the daily revenue mart to be refreshed by 06:00 UTC. It finishes at 06:28 UTC. The pipeline has 4 stages: Cloud Workflows orchestrator -> Cloud Run API extractor -> Pub/Sub buffer -> Dataform BigQuery SQL transformations.
  • Unified Diagnostic Approach: By querying the unified health table grouped by pipeline_run_id, the dashboard decomposes the 28-minute delay across every boundary:
Pipeline StageBaseline DurationIncident DurationVarianceTelemetry Source Identifying the Variance
1. Cloud Workflows Orchestration0.5 min0.5 min0.0 minCloud Logging ([workflows.googleapis.com/Workflow](https://workflows.googleapis.com/Workflow))
2. Cloud Run API Extraction12.0 min14.0 min+2.0 minCloud Logging (_AllLogs app_reported_duration_ms)
3. Pub/Sub Ingestion Buffer1.5 min19.5 min+18.0 minCloud Monitoring (oldest_unacked_message_age) + STREAMING_TIMELINE
4. Dataform BigQuery SQL16.0 min24.0 min+8.0 minINFORMATION_SCHEMA.JOBS_TIMELINE (state = 'PENDING' slot wait)
Total End-to-End Run30.0 min58.0 min+28.0 minUnified Dashboard (64.3% Pub/Sub quota throttling, 28.6% BigQuery slot wait)

6. Nine Standard Production Problems and Solutions (From Simple to Complex)

When engineering teams implement end-to-end observability combining Cloud Monitoring, Cloud Logging, and BigQuery INFORMATION_SCHEMA, they consistently encounter nine operational problems. Below is the exact root cause for each problem followed by three progressive solutions ordered from simple to complex.

Problem 1: High Cloud Logging Ingestion Costs from Verbose Logs and Data Access Audit Logs

  • Exact Root Cause: Enabling BigQuery DATA_READ and DATA_WRITE Audit Logs or leaving debug logging active in high-throughput Cloud Run containers generates terabytes of JSON payloads. At 0.50 USD per GiB after the first 50 GiB, a pipeline generating 80 GiB of logs per day (2,400 GiB per month) costs (2,400 - 50) * 0.50 USD = 1,175.00 USD per month just in log ingestion.
  • Solutions (Simple to Complex):
    1. Simple (Exclusion Filters in Log Router): Add an exclusion filter on the _Default sink to drop severity = "DEBUG" and exclude noisy health-check endpoints (httpRequest.requestUrl : "/healthz"). Takes 5 minutes in the console and cuts ingestion immediately.
    2. Intermediate (Stop Relying on BigQuery Data Access Audit Logs): Disable DATA_READ and DATA_WRITE audit logs for BigQuery if you only need query cost and table lineage. INFORMATION_SCHEMA.JOBS_BY_PROJECT already provides user_email, referenced_tables, and total_bytes_billed with zero Cloud Logging ingestion fees.
    3. Complex (Dynamic Sampling and Tiered Storage Routing via Terraform): Configure the F# or Python logging middleware to emit 100% of WARNING and ERROR logs, but sample INFO logs at 5% (1 in 20) during healthy runs. Route raw unsampled debug logs directly to a regional Cloud Storage bucket sink (costing 0.02 USD per GB per month instead of 0.50 USD per GiB in Cloud Logging—a 96% cost reduction).

Problem 2: Observability Queries Against INFORMATION_SCHEMA Inflating the BigQuery Bill

  • Exact Root Cause: Because INFORMATION_SCHEMA views are never cached, connecting a live BI dashboard (such as Looker Studio or Grafana) with 15 panels directly to INFORMATION_SCHEMA.JOBS_BY_PROJECT and refreshing every 1 minute triggers 15 panels * 1,440 minutes = 21,600 queries per day (648,000 queries per month). If an unpartitioned filter scans 500 MB of job metadata per query, 648,000 * 0.5 GiB = 316.4 TiB scanned per month, costing 316.4 * 6.25 USD = 1,977.50 USD per month.
  • Solutions (Simple to Complex):
    1. Simple (Enforce Partition Key Filters): Always filter JOBS_BY_PROJECT on creation_time and JOBS_TIMELINE_BY_PROJECT on both job_creation_time (the partitioning column) and period_start. Filtering to the last 1 hour drops metadata scanned to the 10 MB minimum billing floor.
    2. Intermediate (Incremental Scheduled Queries into a Partitioned Mart Table): Never connect BI dashboards directly to raw INFORMATION_SCHEMA or _AllLogs views. Instead, run a single scheduled query every 5 minutes that appends the last 5 minutes of unified telemetry into a partitioned, clustered native BigQuery table (infra_observability_mart.pipeline_health_history).
    3. Complex (BI Engine In-Memory Acceleration + Isolated Slot Reservation): Allocate 1 GiB to 2 GiB of BigQuery BI Engine capacity on the materialized observability mart dataset and run the 5-minute collector job inside a dedicated 50-slot baseline reservation Pool so observability queries never compete with production workloads or incur on-demand byte charges.

Problem 3: High-Cardinality Labels in Cloud Monitoring Causing Time-Series Explosions

  • Exact Root Cause: Engineers extract high-cardinality fields like pipeline_run_id, job_id, or customer_id into Cloud Monitoring log-based metrics (google_logging_metric). Because Cloud Monitoring creates a separate time series for every unique label combination, 50,000 pipeline runs create 50,000 active time series, hitting the 100,000 time-series limit per metric descriptor, slowing down dashboard queries, and triggering read API and ingestion charges.
  • Solutions (Simple to Complex):
    1. Simple (Strict Cardinality Cap Rule): Restrict Cloud Monitoring metric labels to static, bounded dimensions whose combined product is less than 500 permutations: environment (3 values) multiplied by pipeline_name (30 values) multiplied by status (3 values) = 270 time series.
    2. Intermediate (Split Alerting from Forensics): Use low-cardinality log-based metrics in Cloud Monitoring exclusively to trigger the alert (“Pipeline X failed in Production”), and embed a parameterized link in the alert notification documentation that opens the unified BigQuery/Looker dashboard filtered by pipeline_name to inspect the high-cardinality pipeline_run_id and job_id.
    3. Complex (Automated Cardinality Linter in CI/CD): Add a policy validation step (using Open Policy Agent / Conftest) in your Terraform CI pipeline that parses all google_logging_metric resources and blocks any pull request where label_extractors reference fields ending in _id, _uuid, _timestamp, or _email.

Problem 4: Disconnected Context Between Orchestrators (Dataform/Airflow) and BigQuery Job IDs

  • Exact Root Cause: Managed orchestrators like Dataform or Cloud Composer submit SQL queries to BigQuery using autogenerated job IDs (bquxjob_... or dataform-...). When an engineer looks at INFORMATION_SCHEMA.JOBS_BY_PROJECT, they see hundreds of jobs executed by the same service account (dataform-sa@...), making it difficult to map a slow SQL query to a specific DAG run or upstream trigger.
  • Solutions (Simple to Complex):
    1. Simple (SQL Comment Headers): Inject structured SQL comments at the top of every query template (for example, /* {"pipeline":"finance_daily", "model":"fct_orders"} */). Parse these comments from the query column in JOBS_BY_PROJECT using REGEXP_EXTRACT(query, r'/\* (\{.*?\}) \*/').
    2. Intermediate (Native Job Labels in Dataform and dbt): Configure labels inside workflow_settings.yaml in Dataform or query-comment / labels in dbt_project.yml so every generated BigQuery job automatically populates the labels array in INFORMATION_SCHEMA.JOBS_BY_PROJECT.
    3. Complex (End-to-End W3C Trace Context Propagation): Pass a single traceparent ID from the initial Cloud Workflows or Airflow trigger through HTTP headers into Cloud Run, attach it to Pub/Sub message attributes, and inject the trace ID as labels.trace_id on every BigQuery job via the F#/Python execution wrapper shown in Section 4.2.

Problem 5: Ingestion Latency Mismatch Across Telemetry Sources (“The Missing Join Window”)

  • Exact Root Cause: Different telemetry subsystems become queryable at different speeds:
    • INFORMATION_SCHEMA.JOBS_TIMELINE: 1 to 10 seconds delay.
    • Cloud Monitoring metrics: 60 to 120 seconds delay.
    • Cloud Logging Linked Dataset (_AllLogs): 60 to 180 seconds (1 to 3 minutes) delay.
    • INFORMATION_SCHEMA.TABLE_STORAGE: Several minutes delay. If a scheduled observability query runs at 12:05:00 for the window 12:00:00 to 12:05:00 using an INNER JOIN between JOBS_BY_PROJECT and _AllLogs, jobs executed at 12:04:15 will appear in JOBS_BY_PROJECT but not yet in _AllLogs, causing false missing-log anomalies.
  • Solutions (Simple to Complex):
    1. Simple (Use FULL OUTER JOIN and a 3-Minute Watermark Delay): Always query up to TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MINUTE) and use FULL OUTER JOIN (as shown in Section 4.3) so late-arriving logs do not drop job records.
    2. Intermediate (Sliding Window Upsert / MERGE Pattern): Have the 5-minute observability materialization job look back over a 30-minute sliding window and MERGE into the target observability mart table on (pipeline_run_id, step_name, bq_job_id), updating NULL log fields once the _AllLogs records arrive.
    3. Complex (Dual-Path Architecture): Separate real-time alerting (which queries Cloud Monitoring log-based counters directly from the Log Router stream with sub-30-second latency) from root-cause analytical aggregation (which runs in BigQuery with a 5-minute watermark).

Problem 6: Regional Scope Fragmentation in BigQuery INFORMATION_SCHEMA

  • Exact Root Cause: While a Cloud Logging linked dataset created in the global location is visible globally, BigQuery INFORMATION_SCHEMA views for jobs and streaming (JOBS_BY_PROJECT, JOBS_TIMELINE_BY_PROJECT, STREAMING_TIMELINE_BY_PROJECT) are strictly region-scoped (region-us, region-eu, region-europe-central2). Furthermore, BigQuery prohibits joining tables or views located in different regions within a single standard SQL query.
  • Solutions (Simple to Complex):
    1. Simple (Colocate the Linked Dataset and Observability Mart): Create the BigQuery linked dataset and the infra_observability_mart dataset in the exact same region (for example, US or EU) where 100% of your production BigQuery datasets reside.
    2. Intermediate (Scheduled Cross-Region Data Transfer for Multi-Region Orgs): If your organization operates pipelines in both EU and US, deploy a local observability materialization query in each region (obs_mart_eu and obs_mart_us), and use the BigQuery Data Transfer Service (DTS) cross-region copy job every 15 minutes to replicate the compact summary table from EU to US.
    3. Complex (Centralized Pub/Sub Telemetry Bus): For strict multi-region architectures, have regional Cloud Scheduler jobs query their local INFORMATION_SCHEMA views, publish compact JSON summary rows to a global Cloud Pub/Sub topic, and stream them into a single central BigQuery observability table via a Pub/Sub BigQuery subscription.

Problem 7: Over-Privileged IAM Roles Required to Query INFORMATION_SCHEMA

  • Exact Root Cause: Querying INFORMATION_SCHEMA.JOBS_BY_PROJECT and JOBS_TIMELINE_BY_PROJECT requires the bigquery.jobs.listAll permission (included in roles/bigquery.resourceViewer or roles/bigquery.admin), and querying TABLE_STORAGE_BY_PROJECT requires roles/bigquery.metadataViewer. Granting bigquery.jobs.listAll directly to 30 analysts or dashboard viewers exposes the full SQL text (query column) of every query run in the project—including queries containing sensitive PII literals in WHERE clauses.
  • Solutions (Simple to Complex):
    1. Simple (Dedicated Service Account for the Materialization Job): Grant roles/bigquery.resourceViewer and roles/bigquery.metadataViewer ONLY to a dedicated sa-observability-collector@... service account that populates the summary mart table, and grant dashboard users roles/bigquery.dataViewer only on the summary mart dataset.
    2. Intermediate (Authorized Views with SQL Text Redaction): Create an Authorized View over JOBS_BY_PROJECT that omits or hashes PII literals from the query column using REGEXP_REPLACE(query, r"'[^']*'", "'[REDACTED]'") while preserving structural SQL patterns and performance metrics.
    3. Complex (BigQuery Row-Level Security + Policy Tags): Apply Row-Level Access Policies (CREATE ROW ACCESS POLICY) on the materialized observability mart table mapped to Google Workspace groups (team_marketing, team_finance) based on the pipeline_name or executing_service_account column, combined with Column-Level Security policy tags on the diagnostic_message and raw_sql columns.

Problem 8: Alert Fatigue from Static Thresholds on Cyclical Workloads

  • Exact Root Cause: Setting a static alert condition (such as “Alert if hourly rows loaded into events_intraday is less than 50,000″ or “Alert if pipeline duration exceeds 20 minutes”) causes false-positive alerts every Sunday at 03:00 UTC when user traffic naturally drops by 70%, or every Monday at 09:00 UTC when weekly rollup jobs legitimately take 32 minutes.
  • Solutions (Simple to Complex):
    1. Simple (Hour-of-Week Baseline Comparison in SQL): Compare the current metric against the same hour of the day and day of the week (EXTRACT(DAYOFWEEK FROM timestamp) and EXTRACT(HOUR FROM timestamp)) over the previous 4 weeks rather than a flat static constant.
    2. Intermediate (PromQL / MQL Rolling Standard Deviation in Cloud Monitoring): Use PromQL in Cloud Monitoring alerting policies to compare current pipeline latency against a 7-day offset mean plus 3 standard deviations (avg_over_time and stddev_over_time with offset 7d).
    3. Complex (Automated Time-Series Forecasting with AI.FORECAST / ARIMA_PLUS in BigQuery ML): Train an automated ARIMA_PLUS model in BigQuery ML on your pipeline_health_history table (incorporating weekly seasonality and holiday calendars) and alert only when actual volume or duration falls outside the 99% prediction interval (prediction_interval_lower_bound / prediction_interval_upper_bound).

Problem 9: Temporary and Script Tables Polluting Storage and Metadata Health Views

  • Exact Root Cause: Multi-statement SQL scripts (BEGIN ... END;), Dataform assertions, and temporary session tables generate thousands of ephemeral tables in hidden datasets named _script... or _c.... When engineers query INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT or JOBS_BY_PROJECT, these ephemeral tables skew table count metrics, trigger false “unpartitioned table” warnings, and clutter lineage views.
  • Solutions (Simple to Complex):
    1. Simple (Explicit SQL Filtering on Dataset Prefixes and Table Types): Always include WHERE NOT STARTS_WITH(table_schema, '_') AND table_type = 'BASE TABLE' AND deleted = FALSE when querying TABLE_STORAGE_BY_PROJECT.
    2. Intermediate (Separate Parent Jobs from Child Script Jobs in JOBS_BY_PROJECT): Multi-statement scripts create one parent job (parent_job_id IS NULL) and multiple child jobs (parent_job_id IS NOT NULL). Summing total_bytes_billed or total_slot_ms across all rows double-counts or fragments script execution. Group child jobs by COALESCE(parent_job_id, job_id) to analyze the script as a single logical unit while drilling into child steps only on failure.
    3. Complex (Automated Ephemeral Storage Leak Detector): While _script tables are normally cleaned up after 24 hours, abandoned temp tables in staging datasets often persist indefinitely. Add a dedicated panel on the unified dashboard that sums active_physical_bytes where STARTS_WITH(table_schema, '_') OR REGEXP_CONTAINS(table_name, r'(?i)(tmp|temp|staging_bak)') and creation_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR) to catch leaked temporary storage.

7. Designing the Unified Infrastructure Health Dashboard: Layout and Composite Scoring

A well-engineered observability dashboard must not overwhelm operators with 50 disconnected line charts. Instead, it should follow a top-down four-tier visual hierarchy backed by a deterministic Composite Pipeline Health Score.

7.1 Mathematical Formulation of the Composite Pipeline Health Score

To summarize infrastructure and data health at the executive layer, each pipeline p is evaluated every 5 minutes across four normalized sub-scores ranging from 0 (critical failure) to 100 (optimal):

  1. Execution Reliability Score (S_rel): 100 if zero ERROR logs in _AllLogs and zero failed jobs in JOBS_BY_PROJECT; 0 if any fatal step failed.
  2. Freshness & Latency Score (S_lat): 100 if end_to_end_duration_ms is less than or equal to target SLA (T_sla); decays linearly from 100 down to 0 as duration approaches 2.0 * T_sla.
  3. Data Volume Integrity Score (S_vol): 100 if the partition row delta Z-score (|Z_vol|) is less than or equal to 2.0; 50 if |Z_vol| is between 2.0 and 3.0; 0 if |Z_vol| is greater than 3.0.
  4. Resource & Cost Efficiency Score (S_cost): 100 if bq_queue_pending_ms is less than 10% of execution time and bytes billed are within budget; 50 if slot queue wait exceeds 25% of runtime or bytes billed exceed 150% of the 14-day rolling mean.

The overall Composite Pipeline Health Score (H_p) uses a weighted minimum-bounded formula so that a fatal failure (S_rel = 0) immediately forces the overall score to 0:

  • If S_rel = 0, then H_p = 0.
  • Otherwise, H_p = (0.35 * S_lat) + (0.35 * S_vol) + (0.30 * S_cost).

7.2 Four-Tier Dashboard Layout Matrix

Dashboard TierTarget AudienceKey Visual PanelsUnderlying Telemetry SourceAction Triggered by Panel
Tier 1: Executive SLA & Health MatrixEngineering Leads, Data Product Owners1. Composite Health Scorecards (H_p) per pipeline.

2. 24-Hour SLA Attainment Percentage.

3. Total Daily Platform Run Cost (USD).		Materialized infra_observability_mart.pipeline_health_history table.Immediate Go / No-Go visibility on whether downstream BI reports are safe to use.
Tier 2: End-to-End Pipeline Flow & VolumeData Engineers, On-Call SREs1. Gantt / Waterfall timeline by pipeline_run_id.

2. Row volume reconciliation (Cloud Run In vs Pub/Sub vs BigQuery Partition Rows).

3. Partition Z-score anomaly table.		Joined _AllLogs + STREAMING_TIMELINE + PARTITIONS.Isolates which exact boundary (Compute, Queue, or Warehouse) dropped rows or added latency.
Tier 3: Compute & Warehouse SaturationPlatform Architects, DBA / FinOps Engineers1. 1-Second Slot Utilization vs Baseline/Autoscale Cap (RUNNING vs PENDING jobs).

2. Cloud Run CPU/Memory & Pub/Sub Unacked Message Age.

3. Compute vs Network vs BigQuery time split.		JOBS_TIMELINE_BY_PROJECT + Cloud Monitoring PromQL metrics.Determines whether to adjust BigQuery slot reservations or scale Cloud Run concurrency limits.
Tier 4: FinOps, Storage & Forensic LogsData Engineers, FinOps Auditors1. Top 20 Expensive Queries by user_email and pipeline_name.

2. Logical vs Physical Storage + Time-Travel bloat table.

3. Drill-through error log inspector (diagnostic_message).		JOBS_BY_PROJECT + TABLE_STORAGE_BY_PROJECT + _AllLogs.Identifies specific SQL queries to optimize, datasets to switch between Logical/Physical billing, and exact stack traces.

8. Summary of Engineering Principles

  1. Eliminate Telemetry Silos Without Duplicating Data: Never export gigabytes of logs into standalone BigQuery tables via legacy sinks when a Log Analytics linked dataset (_AllLogs) provides live SQL access at zero additional BigQuery storage cost.
  2. Enforce Correlation at the Code Boundary: Metrics, logs, and warehouse metadata can only be joined deterministically if your application runtime (whether written in F#, Python, or Go) injects sanitized pipeline_run_id, pipeline_name, and step_name keys into both structured JSON logs and BigQuery job_labels.
  3. Protect the Observability Budget: Filter out verbose debug logs at the Log Router before they incur 0.50 USD per GiB ingestion fees, keep high-cardinality identifiers out of Cloud Monitoring metrics, and materialize INFORMATION_SCHEMA joins on a 5-minute schedule rather than querying uncached system views directly from BI dashboards.
  4. Measure Data Correctness Alongside Execution State: A pipeline that finishes with state = 'DONE' and error_result IS NULL is only healthy if its row volume, partition modification count, and execution duration fall within statistically validated bounds across every stage of the infrastructure.

Similar Posts