bigquery

Google BigQuery

BigQuery is Google's serverless, highly scalable, and cost-effective multi-cloud data warehouse. It processes terabytes in seconds.

When to Use

Serverless Enterprise Data Warehousing: Querying petabytes of structured and semi-structured data with zero infrastructure management.
Real-Time Streaming Analytics: Ingesting and querying millions of events per second with sub-second data freshness.
In-Database Machine Learning (BigQuery ML): Training and scoring regression, classification, and forecasting models using standard SQL.
Federated Multi-Cloud Queries (BigQuery Omni): Querying data stored in AWS S3 and Azure Blob Storage directly without egress replication.

Quick Start

-- Standard SQL
SELECT name, COUNT(*) as count
FROM `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY name
ORDER BY count DESC
LIMIT 10;

Core Concepts

Capacitor Columnar Storage & Dremel Query Engine

Data is stored in proprietary Capacitor columnar format with dynamic tree-based Dremel execution slots:

-- Partitioned & Clustered Table DDL
CREATE OR REPLACE TABLE `my_project.analytics.user_events`
(
  event_id STRING,
  user_id STRING,
  event_name STRING,
  event_timestamp TIMESTAMP,
  metadata JSON
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_name
OPTIONS(
  description="Partitioned clickstream events with clustering for point lookups",
  require_partition_filter=true
);

Semi-Structured Native JSON Querying

Queries nested JSON attributes efficiently without schema migrations:

SELECT
  user_id,
  JSON_VALUE(metadata.device.os) AS os_name,
  JSON_EXTRACT_SCALAR(metadata, '$.cart.total') AS cart_total
FROM `my_project.analytics.user_events`
WHERE DATE(event_timestamp) = CURRENT_DATE()
  AND JSON_VALUE(metadata.action) = 'checkout';

BigQuery ML Model Training

Trains and evaluates predictive models entirely inside SQL:

CREATE OR REPLACE MODEL `analytics.churn_prediction_model`
OPTIONS(model_type='logistic_reg', input_label_cols=['has_churned']) AS
SELECT
  total_spend,
  session_count,
  has_churned
FROM `analytics.customer_features`;

Common Patterns

Partitioning and Clustering for Cost-Optimized Queries

Problem: Full table scans across billions of event rows lead to astronomical query costs and high latency.

Solution: Partition by date and cluster by high-cardinality lookup keys:

CREATE TABLE `project.analytics.events` (
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
OPTIONS (
  require_partition_filter = TRUE,
  partition_expiration_days = 365
);

Best Practices

Do:

Always Enforce require_partition_filter=true: Prevent runaway query billing by requiring users to specify date partitions in WHERE clauses.
Cluster by High-Cardinality Filter Columns: Order tables by user ID, customer ID, or category to minimize scanned bytes.
Use BigQuery Storage Write API: Stream real-time data using the gRPC-based Storage Write API for reduced cost and guaranteed atomicity.
Inspect Bytes Billed in Query Validator: Check dry-run scanned byte estimates before executing heavy exploratory queries.

Don't:

Use SELECT *: BigQuery charges per byte scanned; querying all columns drains query budgets.
Use ORDER BY in subqueries: Global sorting requires single-node processing; sort only in the final outer query with LIMIT.
Export large query results to single CSVs: Use partitioned wildcards (EXPORT DATA OPTIONS(...)) for multi-part exports.

Troubleshooting

Error	Cause	Solution
`Cannot query without partition filter`	`require_partition_filter` enabled but query lacked date WHERE clause.	Include `WHERE DATE(event_timestamp) >= 'YYYY-MM-DD'` in query.
`Resources exceeded during query execution`	Memory exhaustion from large skew in `JOIN` or `ORDER BY`.	Pre-aggregate data, filter early, and avoid `ORDER BY` without `LIMIT`.
`Quota exceeded: Exceeded rate limits`	Too many concurrent DDL/DML mutation queries per table.	Batch append mutations using BigQuery Storage Write API rather than small single INSERTs.

References

BigQuery Documentation