Part 169: Google BigQuery SQL: Partitioning, Clustering & Cost Control

Mastering serverless SQL on petabytes with strict query byte control.

1. How BigQuery Pricing & Execution Works

BigQuery is fully serverless (Dremel execution engine): - Under on-demand pricing, you pay per terabyte of data scanned (e.g. $6.25/TB). - Writing SELECT * FROM events on a 10TB table costs $62.50 for a single query execution! - Columnar projection and partition pruning are essential for cost control.

2. Partitioned Tables

Partitions segment tables into chunks based on a date, timestamp, or integer range:

CREATE TABLE `project.analytics.events` (
    event_id STRING,
    event_name STRING,
    user_id INT64,
    event_timestamp TIMESTAMP
)
PARTITION BY DATE(event_timestamp)
OPTIONS (
    partition_expiration_days = 365,
    require_partition_filter = TRUE -- Blocks queries without partition filter!
);

3. Clustered Tables

Within each partition, clusters sort data based on up to 4 columns:

CREATE TABLE `project.analytics.user_actions` (
    event_date DATE,
    country STRING,
    user_id INT64,
    action STRING
)
PARTITION BY event_date
CLUSTER BY country, action;

4. Critical Best Practices to Prevent Cost Explosions

1. Never use SELECT *: Always explicitly select only the columns needed.
2. LIMIT does not reduce scanned bytes: In BigQuery, SELECT * FROM tbl LIMIT 10 still scans the entire table!
3. Use require_partition_filter = TRUE: Prevents junior analysts or rogue scripts from accidentally full-scanning petabytes.

Key Takeaways

• Partitioning controls partition pruning; clustering controls fine-grained block pruning within partitions.

In Part 170, we explore Google BigQuery Complex Types: Nested & Repeated Fields with UNNEST()!