Optimize BigQuery Table Partitioning

This skill covers BigQuery table partitioning strategy, performance optimization, and cost management. It frames partition-type selection around your data: time-unit partitioning for timestamp/datetime columns with predictable time-based query patterns, integer-range partitioning for numeric columns with known ranges (user IDs, geographic codes), and ingestion-time partitioning as the fallback when no suitable partitioning column exists. Granularity guidance maps daily partitioning to typical 1-10GB daily volumes, hourly partitioning to high-load streaming data above 10GB/hour or real-time analytics, and monthly partitioning to historical data with sparse query patterns.

It provides working SQL for both time-unit partitioned tables (with clustering and a partition-expiration policy) and integer-range partitioned tables using RANGE_BUCKET, plus query-optimization guidance: filtering on the partition column to enable partition pruning versus a relative-time filter that forces a full table scan, using _PARTITIONTIME for ingestion-time-partitioned tables, and querying a specific partition directly via a $YYYYMMDD partition decorator. Advanced patterns cover dynamic partition replacement with CREATE OR REPLACE TABLE ... AS SELECT, copying or deleting specific partitions, and monitoring partition size and row-count distribution via INFORMATION_SCHEMA.PARTITIONS to catch uneven data distribution.

CREATE TABLE `project.dataset.events`
(
  event_timestamp TIMESTAMP,
  user_id INT64,
  event_type STRING,
  properties JSON
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
OPTIONS(
  partition_expiration_days=365,
  description="Daily partitioned events table with 1-year retention"
);

Use this skill when designing or optimizing a partitioned BigQuery table - choosing a partition type and granularity, adding clustering, writing partition-pruning-friendly queries, or migrating an existing table to a partitioned one.

It is not a fit for query logic or schema design unrelated to partitioning - it's scoped to how a table's physical layout (partitioning and clustering) interacts with query patterns and cost, not to the SQL business logic itself.

Inputs are your table's query patterns (which columns get filtered), data volume and growth rate, and whether a suitable timestamp or numeric range column exists. Outputs are CREATE TABLE statements with the right PARTITION BY/CLUSTER BY clauses, partition-pruning-friendly query patterns, partition management SQL (copy, delete, or efficiently replace a partition via CREATE OR REPLACE TABLE ... AS SELECT), and monitoring queries against INFORMATION_SCHEMA.PARTITIONS that surface both partition size/row counts and per-row byte skew across partitions.

Data engineers designing or tuning BigQuery tables who need concrete partition-type and clustering guidance - including ordering clustering columns by query frequency and selectivity rather than arbitrarily - four named anti-patterns to avoid (over-partitioning into sub-100MB partitions, filtering on the wrong column, missing partition filters, and uneven partition distribution), and cost controls like partition_expiration_days and require_partition_filter=true to prevent accidental full-table scans.