schema-design

BigQuery Schema Design

Parent Skill: /bigquery
Path: /bigquery/schema-design

Purpose

Design optimal table schemas with partitioning, clustering, and data types for performance and cost efficiency.

When to Use

Trigger when:

• Keywords: CREATE TABLE, partition, cluster, schema, design, structure
• User asks: "how to structure table", "setup partitioning", "optimize storage"

Chat commands:

/bigquery/schema-design create events table with partitioning
/bigquery/schema-design add clustering to existing table
/bigquery/schema-design design schema for time-series data

Requirements

---

Partitioning Strategies

1. Date/Timestamp Partitioning (Most Common)

-- Daily partitions (recommended for event data)
CREATE OR REPLACE TABLE `dataset.events`
PARTITION BY DATE(timestamp)
OPTIONS (
  partition_expiration_days = 365,  -- Auto-delete after 1 year
  require_partition_filter = TRUE   -- Force users to filter by date
) AS
SELECT
  timestamp,
  user_id,
  event_name,
  properties
FROM `dataset.raw_events`;

When to use:

• Event/log data
• Time-series data
• Data queried by date ranges

2. Integer Range Partitioning

-- Partition by user ID ranges
CREATE OR REPLACE TABLE `dataset.users`
PARTITION BY RANGE_BUCKET(user_id, GENERATE_ARRAY(0, 1000000, 10000))
AS
SELECT * FROM `dataset.raw_users`;

When to use:

• Queries filter by numeric ranges
• Data evenly distributed across ranges

3. Ingestion Time Partitioning

CREATE OR REPLACE TABLE `dataset.events`
PARTITION BY _PARTITIONTIME
AS SELECT * FROM `dataset.raw_events`;

---

Clustering

Single Column Clustering

CREATE OR REPLACE TABLE `dataset.events`
PARTITION BY DATE(timestamp)
CLUSTER BY user_id
AS SELECT * FROM `dataset.raw_events`;

Multi-Column Clustering (Order Matters!)

-- Cluster order: most filtered columns first
CREATE OR REPLACE TABLE `dataset.events`
PARTITION BY DATE(timestamp)
CLUSTER BY tenant_id, user_id, event_name  -- Order by selectivity
AS SELECT * FROM `dataset.raw_events`;