BigQuery's serverless architecture offers incredible scale, but without proper optimization, teams often face slot shortages during peak hours, runaway query costs, and frustrated business users abandoning self-service analytics. The key lies in understanding BigQuery's unique execution model: how slots work, when to use clustering vs partitioning, and how to structure queries that take advantage of BigQuery's distributed processing power. This playbook provides battle-tested optimization strategies specifically designed for enterprise and mid-market teams managing complex analytical workloads at scale.

Performance Yardsticks

• Dashboard query latency · Good Performance: < 3s · Warning Zone: 3-10s · Action Required: > 10s

• Slot utilization (peak hours) · Good Performance: < 80% · Warning Zone: 80-95% · Action Required: > 95%

• Query cost per TB processed · Good Performance: < $5/TB · Warning Zone: $5-8/TB · Action Required: > $8/TB

• Ad-hoc query response time · Good Performance: < 30s · Warning Zone: 30-120s · Action Required: > 120s

• ETL job completion time · Good Performance: Within SLA · Warning Zone: 10-20% over SLA · Action Required: > 20% over SLA

• Bytes processed vs. billed · Good Performance: > 90% efficiency · Warning Zone: 70-90% · Action Required: < 70%

• Concurrent query queue time · Good Performance: < 5s · Warning Zone: 5-30s · Action Required: > 30s

‍

Workload Taxonomy

• BI Dashboards · Characteristics: High-frequency analytical queries, 5-500 concurrent users, sub-3s SLA requirements, predictable access patterns · Performance Priorities: Ultra-low latency, consistent performance, cost predictability · Common Pain Points: Dashboard timeouts, slot contention during business hours, inconsistent response times

• Ad-hoc Analytics · Characteristics: Exploratory queries with complex joins, variable data volumes, unpredictable patterns, data scientist workflows · Performance Priorities: Query flexibility, reasonable cost per analysis, fast iteration cycles · Common Pain Points: Expensive full table scans, complex join optimization, result set size limits

• ETL/Streaming · Characteristics: High-volume data processing, scheduled batch jobs, streaming inserts, data pipeline orchestration · Performance Priorities: Throughput optimization, cost efficiency, reliable completion times · Common Pain Points: Slot starvation, partition pruning failures, streaming buffer delays

‍

BI Dashboard Optimization Tactics

Implement Clustered Tables for Repeated Dashboard Filters

When your executive dashboards consistently filter by the same dimensions - region, product_category, or date ranges - BigQuery's clustering delivers sub-second query performance by physically co-locating related data. You'll see the most dramatic improvements on tables larger than 1GB where dashboard queries regularly filter on the clustered columns.

Here's what happens when you implement clustering on a sales dashboard that filters by region and date:

-- Create clustered table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_clustered`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT 
  region,
  sale_date,
  revenue,
  order_id,
  customer_id,
  product_category
FROM `project.dataset.sales_raw`;

-- Dashboard query leverages clustering
SELECT 
  region,
  SUM(revenue) as total_revenue,
  COUNT(order_id) as order_count
FROM `project.dataset.sales_clustered`
WHERE region IN ('US-WEST', 'US-EAST')
  AND sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region;

-- Create clustered table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_clustered`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT 
  region,
  sale_date,
  revenue,
  order_id,
  customer_id,
  product_category
FROM `project.dataset.sales_raw`;

-- Dashboard query leverages clustering
SELECT 
  region,
  SUM(revenue) as total_revenue,
  COUNT(order_id) as order_count
FROM `project.dataset.sales_clustered`
WHERE region IN ('US-WEST', 'US-EAST')
  AND sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region;

-- Create clustered table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_clustered`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT 
  region,
  sale_date,
  revenue,
  order_id,
  customer_id,
  product_category
FROM `project.dataset.sales_raw`;

-- Dashboard query leverages clustering
SELECT 
  region,
  SUM(revenue) as total_revenue,
  COUNT(order_id) as order_count
FROM `project.dataset.sales_clustered`
WHERE region IN ('US-WEST', 'US-EAST')
  AND sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region;

-- Create clustered table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_clustered`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT 
  region,
  sale_date,
  revenue,
  order_id,
  customer_id,
  product_category
FROM `project.dataset.sales_raw`;

-- Dashboard query leverages clustering
SELECT 
  region,
  SUM(revenue) as total_revenue,
  COUNT(order_id) as order_count
FROM `project.dataset.sales_clustered`
WHERE region IN ('US-WEST', 'US-EAST')
  AND sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region;

This clustering strategy reduces data scanning from terabytes to gigabytes because BigQuery only reads the relevant clustered blocks.

Alternatives: Partitioning by date works well for time-series dashboards but lacks the multi-dimensional optimization that clustering provides. Materialized views offer even faster performance for highly repetitive dashboard queries, though they add storage costs and complexity. For workloads requiring sub-second latency across multiple filter dimensions, e6data's decentralized architecture eliminates coordinator bottlenecks entirely, delivering consistent performance regardless of concurrent dashboard users at 1000+ QPS.

Optimize Dashboard Aggregations with Materialized Views

Dashboard queries that repeatedly aggregate the same metrics - monthly revenue, top products, user engagement scores - benefit enormously from materialized views. When you pre-compute these aggregations, your dashboard queries execute in milliseconds instead of seconds, and BigQuery automatically maintains the views as underlying data changes.

The magic happens when multiple dashboards query the same business metrics but with different filters:

-- Create materialized view for common dashboard metrics
CREATE MATERIALIZED VIEW `project.dataset.daily_sales_summary`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT
  DATE(order_timestamp) as sale_date,
  region,
  product_category,
  SUM(revenue) as daily_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  COUNT(order_id) as order_count,
  AVG(order_value) as avg_order_value
FROM `project.dataset.orders`
GROUP BY 1, 2, 3;

-- Dashboard queries now execute in milliseconds
SELECT 
  region,
  SUM(daily_revenue) as total_revenue,
  SUM(unique_customers) as total_customers
FROM `project.dataset.daily_sales_summary`
WHERE sale_date BETWEEN '2024-09-01' AND '2024-09-30'
  AND region = 'US-WEST'
GROUP BY region;

-- Create materialized view for common dashboard metrics
CREATE MATERIALIZED VIEW `project.dataset.daily_sales_summary`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT
  DATE(order_timestamp) as sale_date,
  region,
  product_category,
  SUM(revenue) as daily_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  COUNT(order_id) as order_count,
  AVG(order_value) as avg_order_value
FROM `project.dataset.orders`
GROUP BY 1, 2, 3;

-- Dashboard queries now execute in milliseconds
SELECT 
  region,
  SUM(daily_revenue) as total_revenue,
  SUM(unique_customers) as total_customers
FROM `project.dataset.daily_sales_summary`
WHERE sale_date BETWEEN '2024-09-01' AND '2024-09-30'
  AND region = 'US-WEST'
GROUP BY region;

-- Create materialized view for common dashboard metrics
CREATE MATERIALIZED VIEW `project.dataset.daily_sales_summary`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT
  DATE(order_timestamp) as sale_date,
  region,
  product_category,
  SUM(revenue) as daily_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  COUNT(order_id) as order_count,
  AVG(order_value) as avg_order_value
FROM `project.dataset.orders`
GROUP BY 1, 2, 3;

-- Dashboard queries now execute in milliseconds
SELECT 
  region,
  SUM(daily_revenue) as total_revenue,
  SUM(unique_customers) as total_customers
FROM `project.dataset.daily_sales_summary`
WHERE sale_date BETWEEN '2024-09-01' AND '2024-09-30'
  AND region = 'US-WEST'
GROUP BY region;

-- Create materialized view for common dashboard metrics
CREATE MATERIALIZED VIEW `project.dataset.daily_sales_summary`
PARTITION BY sale_date
CLUSTER BY region, product_category
AS SELECT
  DATE(order_timestamp) as sale_date,
  region,
  product_category,
  SUM(revenue) as daily_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  COUNT(order_id) as order_count,
  AVG(order_value) as avg_order_value
FROM `project.dataset.orders`
GROUP BY 1, 2, 3;

-- Dashboard queries now execute in milliseconds
SELECT 
  region,
  SUM(daily_revenue) as total_revenue,
  SUM(unique_customers) as total_customers
FROM `project.dataset.daily_sales_summary`
WHERE sale_date BETWEEN '2024-09-01' AND '2024-09-30'
  AND region = 'US-WEST'
GROUP BY region;

BigQuery automatically refreshes materialized views, so your dashboards always show current data without manual intervention.

Alternatives: Scheduled queries with destination tables provide similar performance benefits but require manual refresh logic. BI Engine offers sub-second dashboard performance through in-memory caching, though it's limited to specific data sizes and query patterns. For teams needing guaranteed sub-second latency with complex dashboard hierarchies, e6data's stateless architecture scales to 1000+ concurrent users without the memory limitations of traditional BI acceleration layers.

Leverage Table Sampling for Interactive Dashboard Development

When your data science team builds new dashboard prototypes, they don't need to query entire production datasets to validate visualizations and logic. BigQuery's table sampling lets you work with statistically representative data subsets, dramatically reducing development costs and iteration time while maintaining query pattern accuracy.

Here's how you implement intelligent sampling for dashboard development workflows:

-- Sample 1% of data for dashboard prototyping
SELECT 
  product_category,
  customer_segment,
  AVG(revenue) as avg_revenue,
  COUNT(*) as transaction_count
FROM `project.dataset.sales_data` TABLESAMPLE SYSTEM (1 PERCENT)
WHERE transaction_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY avg_revenue DESC;

-- Sample 1% of data for dashboard prototyping
SELECT 
  product_category,
  customer_segment,
  AVG(revenue) as avg_revenue,
  COUNT(*) as transaction_count
FROM `project.dataset.sales_data` TABLESAMPLE SYSTEM (1 PERCENT)
WHERE transaction_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY avg_revenue DESC;

-- Sample 1% of data for dashboard prototyping
SELECT 
  product_category,
  customer_segment,
  AVG(revenue) as avg_revenue,
  COUNT(*) as transaction_count
FROM `project.dataset.sales_data` TABLESAMPLE SYSTEM (1 PERCENT)
WHERE transaction_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY avg_revenue DESC;

-- Sample 1% of data for dashboard prototyping
SELECT 
  product_category,
  customer_segment,
  AVG(revenue) as avg_revenue,
  COUNT(*) as transaction_count
FROM `project.dataset.sales_data` TABLESAMPLE SYSTEM (1 PERCENT)
WHERE transaction_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY avg_revenue DESC;

Alternatives: Creating dedicated development datasets with subset data provides predictable performance but requires manual data pipeline maintenance. Query result caching helps with repeated development queries but doesn't address the fundamental cost issue.

Implement Approximation Functions for Real-Time Dashboards

Executive dashboards displaying user counts, unique visitors, or distinct product views don't always need exact precision - especially when approximate results load in 2 seconds versus exact counts taking 30 seconds. BigQuery's approximation functions like APPROX_COUNT_DISTINCT deliver 99%+ accuracy while scanning dramatically less data.

Here's how approximation transforms dashboard performance for high-cardinality metrics:

-- Approximate dashboard metrics for real-time performance
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  APPROX_COUNT_DISTINCT(user_id) as unique_users,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(50)] as median_session,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(95)] as p95_session,
  COUNT(*) as total_events
FROM `project.dataset.user_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY 1 DESC;

-- Approximate dashboard metrics for real-time performance
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  APPROX_COUNT_DISTINCT(user_id) as unique_users,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(50)] as median_session,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(95)] as p95_session,
  COUNT(*) as total_events
FROM `project.dataset.user_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY 1 DESC;

-- Approximate dashboard metrics for real-time performance
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  APPROX_COUNT_DISTINCT(user_id) as unique_users,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(50)] as median_session,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(95)] as p95_session,
  COUNT(*) as total_events
FROM `project.dataset.user_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY 1 DESC;

-- Approximate dashboard metrics for real-time performance
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  APPROX_COUNT_DISTINCT(user_id) as unique_users,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(50)] as median_session,
  APPROX_QUANTILES(session_duration, 100)[OFFSET(95)] as p95_session,
  COUNT(*) as total_events
FROM `project.dataset.user_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY 1 DESC;

The key here is knowing which metrics require exact precision (revenue, transaction counts) versus which benefit from fast approximation (unique users, percentiles).

Alternatives: HyperLogLog sketches provide more control over approximation accuracy but require additional complexity. Streaming analytics can pre-compute real-time aggregations but adds infrastructure overhead. e6data's sub-second query execution often eliminates the need for approximation altogether, delivering exact results with the performance characteristics of approximate queries.

Optimize Dashboard Queries with Strategic Denormalization

Complex dashboard queries that join 5-8 tables to build executive KPI views often exceed acceptable latency thresholds, especially during peak business hours when slot contention is high. Strategic denormalization pre-joins frequently accessed dimensions, transforming multi-table dashboard queries into single-table scans that execute in seconds rather than minutes.

The transformation looks like this for a typical sales performance dashboard:

-- Create denormalized table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_dashboard_optimized`
CLUSTER BY region, product_category, customer_segment
AS SELECT 
  o.order_id,
  o.order_date,
  o.revenue,
  c.customer_segment,
  c.region,
  p.product_category,
  p.product_name,
  s.salesperson_name,
  s.sales_territory
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
JOIN `project.dataset.products` p ON o.product_id = p.product_id
JOIN `project.dataset.salespeople` s ON o.salesperson_id = s.salesperson_id;

-- Create denormalized table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_dashboard_optimized`
CLUSTER BY region, product_category, customer_segment
AS SELECT 
  o.order_id,
  o.order_date,
  o.revenue,
  c.customer_segment,
  c.region,
  p.product_category,
  p.product_name,
  s.salesperson_name,
  s.sales_territory
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
JOIN `project.dataset.products` p ON o.product_id = p.product_id
JOIN `project.dataset.salespeople` s ON o.salesperson_id = s.salesperson_id;

-- Create denormalized table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_dashboard_optimized`
CLUSTER BY region, product_category, customer_segment
AS SELECT 
  o.order_id,
  o.order_date,
  o.revenue,
  c.customer_segment,
  c.region,
  p.product_category,
  p.product_name,
  s.salesperson_name,
  s.sales_territory
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
JOIN `project.dataset.products` p ON o.product_id = p.product_id
JOIN `project.dataset.salespeople` s ON o.salesperson_id = s.salesperson_id;

-- Create denormalized table for dashboard performance
CREATE OR REPLACE TABLE `project.dataset.sales_dashboard_optimized`
CLUSTER BY region, product_category, customer_segment
AS SELECT 
  o.order_id,
  o.order_date,
  o.revenue,
  c.customer_segment,
  c.region,
  p.product_category,
  p.product_name,
  s.salesperson_name,
  s.sales_territory
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
JOIN `project.dataset.products` p ON o.product_id = p.product_id
JOIN `project.dataset.salespeople` s ON o.salesperson_id = s.salesperson_id;

Once you've set this up, your dashboard queries avoid expensive joins and execute consistently fast regardless of concurrent user load. The trade-off is increased storage costs, but you'll find significant query performance improvements for mission-critical dashboards.

Alternatives: Nested and repeated fields maintain relationships without full denormalization but require query logic changes. Views with optimized joins provide abstraction without storage duplication but don't eliminate join costs.

Ad-hoc Analytics Optimization Tactics

Control Projection - Query Only Required Columns

The most expensive way to query data is using SELECT *. When you use SELECT *, BigQuery performs a full scan of every column in the table, leading to unnecessary I/O and materialization costs. Projection optimization is your first line of defense against runaway query costs.

Here's how proper column selection transforms analytical query performance:

-- Inefficient: scans all columns
SELECT * FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
LIMIT 100;  -- LIMIT doesn't reduce data scanned!

-- Optimized: scan only required columns
SELECT 
  order_id,
  customer_id,
  revenue,
  order_date,
  product_category
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
ORDER BY revenue DESC
LIMIT 100;

-- Use SELECT * EXCEPT to exclude unnecessary columns
SELECT * EXCEPT(internal_notes, raw_metadata, debug_info)
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01';

-- Inefficient: scans all columns
SELECT * FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
LIMIT 100;  -- LIMIT doesn't reduce data scanned!

-- Optimized: scan only required columns
SELECT 
  order_id,
  customer_id,
  revenue,
  order_date,
  product_category
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
ORDER BY revenue DESC
LIMIT 100;

-- Use SELECT * EXCEPT to exclude unnecessary columns
SELECT * EXCEPT(internal_notes, raw_metadata, debug_info)
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01';

-- Inefficient: scans all columns
SELECT * FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
LIMIT 100;  -- LIMIT doesn't reduce data scanned!

-- Optimized: scan only required columns
SELECT 
  order_id,
  customer_id,
  revenue,
  order_date,
  product_category
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
ORDER BY revenue DESC
LIMIT 100;

-- Use SELECT * EXCEPT to exclude unnecessary columns
SELECT * EXCEPT(internal_notes, raw_metadata, debug_info)
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01';

-- Inefficient: scans all columns
SELECT * FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
LIMIT 100;  -- LIMIT doesn't reduce data scanned!

-- Optimized: scan only required columns
SELECT 
  order_id,
  customer_id,
  revenue,
  order_date,
  product_category
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
ORDER BY revenue DESC
LIMIT 100;

-- Use SELECT * EXCEPT to exclude unnecessary columns
SELECT * EXCEPT(internal_notes, raw_metadata, debug_info)
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01';

Alternatives: Table previews let you explore data structure without full scans. Views can encapsulate column selection logic for repeated use patterns.

Leverage Intelligent Query Pruning with WHERE Clause Optimization

Ad-hoc analytics queries often start broad and iteratively narrow down to specific insights. The difference between scanning 10TB versus 100GB comes down to WHERE clause placement and partition pruning effectiveness. When you structure your exploratory queries to leverage BigQuery's partition and cluster pruning early, you'll dramatically reduce both query costs and execution time.

Here's how strategic WHERE clause optimization transforms exploratory analytics:

-- Optimize for partition and cluster pruning
SELECT 
  customer_segment,
  product_category,
  SUM(revenue) as segment_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  AVG(order_value) as avg_order_value
FROM `project.dataset.sales_partitioned`
WHERE 
  -- Partition pruning first (most selective)
  transaction_date BETWEEN '2024-09-01' AND '2024-09-30'
  -- Cluster pruning second
  AND region IN ('US-WEST', 'US-EAST')
  AND product_category IN ('Electronics', 'Software')
  -- Business logic filters last
  AND order_value > 100
  AND customer_segment != 'test'
GROUP BY 1, 2
HAVING segment_revenue > 10000
ORDER BY segment_revenue DESC;

-- Use subqueries for complex filtering logic
WITH high_value_customers AS (
  SELECT customer_id
  FROM `project.dataset.customer_analytics`
  WHERE lifetime_value > 5000
    AND last_purchase_date >= '2024-06-01'
)
SELECT 
  p.product_name,
  SUM(s.revenue) as revenue_from_hvcs,
  COUNT(*) as hvc_purchases
FROM `project.dataset.sales_partitioned` s
JOIN high_value_customers h ON s.customer_id = h.customer_id
JOIN `project.dataset.products` p ON s.product_id = p.product_id
WHERE s.transaction_date >= '2024-09-01'
GROUP BY 1
ORDER BY 2 DESC;

-- Optimize for partition and cluster pruning
SELECT 
  customer_segment,
  product_category,
  SUM(revenue) as segment_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  AVG(order_value) as avg_order_value
FROM `project.dataset.sales_partitioned`
WHERE 
  -- Partition pruning first (most selective)
  transaction_date BETWEEN '2024-09-01' AND '2024-09-30'
  -- Cluster pruning second
  AND region IN ('US-WEST', 'US-EAST')
  AND product_category IN ('Electronics', 'Software')
  -- Business logic filters last
  AND order_value > 100
  AND customer_segment != 'test'
GROUP BY 1, 2
HAVING segment_revenue > 10000
ORDER BY segment_revenue DESC;

-- Use subqueries for complex filtering logic
WITH high_value_customers AS (
  SELECT customer_id
  FROM `project.dataset.customer_analytics`
  WHERE lifetime_value > 5000
    AND last_purchase_date >= '2024-06-01'
)
SELECT 
  p.product_name,
  SUM(s.revenue) as revenue_from_hvcs,
  COUNT(*) as hvc_purchases
FROM `project.dataset.sales_partitioned` s
JOIN high_value_customers h ON s.customer_id = h.customer_id
JOIN `project.dataset.products` p ON s.product_id = p.product_id
WHERE s.transaction_date >= '2024-09-01'
GROUP BY 1
ORDER BY 2 DESC;

-- Optimize for partition and cluster pruning
SELECT 
  customer_segment,
  product_category,
  SUM(revenue) as segment_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  AVG(order_value) as avg_order_value
FROM `project.dataset.sales_partitioned`
WHERE 
  -- Partition pruning first (most selective)
  transaction_date BETWEEN '2024-09-01' AND '2024-09-30'
  -- Cluster pruning second
  AND region IN ('US-WEST', 'US-EAST')
  AND product_category IN ('Electronics', 'Software')
  -- Business logic filters last
  AND order_value > 100
  AND customer_segment != 'test'
GROUP BY 1, 2
HAVING segment_revenue > 10000
ORDER BY segment_revenue DESC;

-- Use subqueries for complex filtering logic
WITH high_value_customers AS (
  SELECT customer_id
  FROM `project.dataset.customer_analytics`
  WHERE lifetime_value > 5000
    AND last_purchase_date >= '2024-06-01'
)
SELECT 
  p.product_name,
  SUM(s.revenue) as revenue_from_hvcs,
  COUNT(*) as hvc_purchases
FROM `project.dataset.sales_partitioned` s
JOIN high_value_customers h ON s.customer_id = h.customer_id
JOIN `project.dataset.products` p ON s.product_id = p.product_id
WHERE s.transaction_date >= '2024-09-01'
GROUP BY 1
ORDER BY 2 DESC;

-- Optimize for partition and cluster pruning
SELECT 
  customer_segment,
  product_category,
  SUM(revenue) as segment_revenue,
  COUNT(DISTINCT customer_id) as unique_customers,
  AVG(order_value) as avg_order_value
FROM `project.dataset.sales_partitioned`
WHERE 
  -- Partition pruning first (most selective)
  transaction_date BETWEEN '2024-09-01' AND '2024-09-30'
  -- Cluster pruning second
  AND region IN ('US-WEST', 'US-EAST')
  AND product_category IN ('Electronics', 'Software')
  -- Business logic filters last
  AND order_value > 100
  AND customer_segment != 'test'
GROUP BY 1, 2
HAVING segment_revenue > 10000
ORDER BY segment_revenue DESC;

-- Use subqueries for complex filtering logic
WITH high_value_customers AS (
  SELECT customer_id
  FROM `project.dataset.customer_analytics`
  WHERE lifetime_value > 5000
    AND last_purchase_date >= '2024-06-01'
)
SELECT 
  p.product_name,
  SUM(s.revenue) as revenue_from_hvcs,
  COUNT(*) as hvc_purchases
FROM `project.dataset.sales_partitioned` s
JOIN high_value_customers h ON s.customer_id = h.customer_id
JOIN `project.dataset.products` p ON s.product_id = p.product_id
WHERE s.transaction_date >= '2024-09-01'
GROUP BY 1
ORDER BY 2 DESC;

Alternatives: Query result caching helps with repeated exploratory patterns but doesn't address initial query costs. Table previews with LIMIT can validate query logic cheaply but may not represent full dataset patterns. For teams requiring unlimited data exploration without cost anxiety, e6data's predictable per-vCPU (cores consumed) pricing eliminates the guesswork in analytical query budgeting.

Optimize Complex Joins with Strategic Table Ordering

Multi-table analytical queries often involve joining fact tables with multiple dimension tables, and BigQuery's join performance depends heavily on table ordering and join strategies. When you understand BigQuery's distributed join algorithms, you can structure your analytical queries to minimize data shuffling and reduce execution time from minutes to seconds.

The transformation happens when you apply join optimization principles to typical analytical workloads:

-- Optimize join order for analytical performance
WITH filtered_sales AS (
  SELECT 
    order_id,
    customer_id,
    product_id,
    revenue,
    order_date
  FROM `project.dataset.sales`
  WHERE order_date BETWEEN '2024-09-01' AND '2024-09-30'
    AND revenue > 50  -- Pre-filter to reduce join volume
),
product_metrics AS (
  SELECT 
    product_id,
    product_category,
    product_name,
    cost_basis
  FROM `project.dataset.products`
  WHERE is_active = true
)
SELECT 
  p.product_category,
  c.customer_segment,
  c.region,
  SUM(s.revenue) as total_revenue,
  SUM(s.revenue - p.cost_basis) as gross_profit,
  COUNT(s.order_id) as order_count
FROM filtered_sales s
JOIN product_metrics p ON s.product_id = p.product_id  -- Join smaller filtered tables first
JOIN `project.dataset.customers` c ON s.customer_id = c.customer_id
GROUP BY 1, 2, 3
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

-- Use ARRAY_AGG for one-to-many relationships
SELECT 
  c.customer_id,
  c.customer_name,
  c.customer_segment,
  ARRAY_AGG(STRUCT(
    o.order_date,
    o.revenue,
    o.product_category
  ) ORDER BY o.order_date DESC LIMIT 10) as recent_orders,
  SUM(o.revenue) as total_customer_value
FROM `project.dataset.customers` c
JOIN `project.dataset.sales` o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY 1, 2, 3
HAVING total_customer_value > 5000;

-- Optimize join order for analytical performance
WITH filtered_sales AS (
  SELECT 
    order_id,
    customer_id,
    product_id,
    revenue,
    order_date
  FROM `project.dataset.sales`
  WHERE order_date BETWEEN '2024-09-01' AND '2024-09-30'
    AND revenue > 50  -- Pre-filter to reduce join volume
),
product_metrics AS (
  SELECT 
    product_id,
    product_category,
    product_name,
    cost_basis
  FROM `project.dataset.products`
  WHERE is_active = true
)
SELECT 
  p.product_category,
  c.customer_segment,
  c.region,
  SUM(s.revenue) as total_revenue,
  SUM(s.revenue - p.cost_basis) as gross_profit,
  COUNT(s.order_id) as order_count
FROM filtered_sales s
JOIN product_metrics p ON s.product_id = p.product_id  -- Join smaller filtered tables first
JOIN `project.dataset.customers` c ON s.customer_id = c.customer_id
GROUP BY 1, 2, 3
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

-- Use ARRAY_AGG for one-to-many relationships
SELECT 
  c.customer_id,
  c.customer_name,
  c.customer_segment,
  ARRAY_AGG(STRUCT(
    o.order_date,
    o.revenue,
    o.product_category
  ) ORDER BY o.order_date DESC LIMIT 10) as recent_orders,
  SUM(o.revenue) as total_customer_value
FROM `project.dataset.customers` c
JOIN `project.dataset.sales` o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY 1, 2, 3
HAVING total_customer_value > 5000;

-- Optimize join order for analytical performance
WITH filtered_sales AS (
  SELECT 
    order_id,
    customer_id,
    product_id,
    revenue,
    order_date
  FROM `project.dataset.sales`
  WHERE order_date BETWEEN '2024-09-01' AND '2024-09-30'
    AND revenue > 50  -- Pre-filter to reduce join volume
),
product_metrics AS (
  SELECT 
    product_id,
    product_category,
    product_name,
    cost_basis
  FROM `project.dataset.products`
  WHERE is_active = true
)
SELECT 
  p.product_category,
  c.customer_segment,
  c.region,
  SUM(s.revenue) as total_revenue,
  SUM(s.revenue - p.cost_basis) as gross_profit,
  COUNT(s.order_id) as order_count
FROM filtered_sales s
JOIN product_metrics p ON s.product_id = p.product_id  -- Join smaller filtered tables first
JOIN `project.dataset.customers` c ON s.customer_id = c.customer_id
GROUP BY 1, 2, 3
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

-- Use ARRAY_AGG for one-to-many relationships
SELECT 
  c.customer_id,
  c.customer_name,
  c.customer_segment,
  ARRAY_AGG(STRUCT(
    o.order_date,
    o.revenue,
    o.product_category
  ) ORDER BY o.order_date DESC LIMIT 10) as recent_orders,
  SUM(o.revenue) as total_customer_value
FROM `project.dataset.customers` c
JOIN `project.dataset.sales` o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY 1, 2, 3
HAVING total_customer_value > 5000;

-- Optimize join order for analytical performance
WITH filtered_sales AS (
  SELECT 
    order_id,
    customer_id,
    product_id,
    revenue,
    order_date
  FROM `project.dataset.sales`
  WHERE order_date BETWEEN '2024-09-01' AND '2024-09-30'
    AND revenue > 50  -- Pre-filter to reduce join volume
),
product_metrics AS (
  SELECT 
    product_id,
    product_category,
    product_name,
    cost_basis
  FROM `project.dataset.products`
  WHERE is_active = true
)
SELECT 
  p.product_category,
  c.customer_segment,
  c.region,
  SUM(s.revenue) as total_revenue,
  SUM(s.revenue - p.cost_basis) as gross_profit,
  COUNT(s.order_id) as order_count
FROM filtered_sales s
JOIN product_metrics p ON s.product_id = p.product_id  -- Join smaller filtered tables first
JOIN `project.dataset.customers` c ON s.customer_id = c.customer_id
GROUP BY 1, 2, 3
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

-- Use ARRAY_AGG for one-to-many relationships
SELECT 
  c.customer_id,
  c.customer_name,
  c.customer_segment,
  ARRAY_AGG(STRUCT(
    o.order_date,
    o.revenue,
    o.product_category
  ) ORDER BY o.order_date DESC LIMIT 10) as recent_orders,
  SUM(o.revenue) as total_customer_value
FROM `project.dataset.customers` c
JOIN `project.dataset.sales` o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY 1, 2, 3
HAVING total_customer_value > 5000;

Alternatives: Partitioned joins can improve performance when join keys align with partition columns. Broadcasting smaller tables happens automatically but can be optimized through table sizing.

Implement Window Functions for Advanced Analytics Efficiently

Advanced analytical queries - cohort analysis, time-series comparisons, running totals, rank calculations - rely heavily on window functions. The challenge in BigQuery is that poorly written window functions can consume massive compute resources, while optimized implementations deliver sophisticated analytics efficiently. Understanding partitioning and ordering strategies transforms expensive analytical queries into performant insights.

Here's how to structure window functions for optimal analytical performance:

-- Efficient cohort analysis with optimized window functions
WITH customer_cohorts AS (
  SELECT 
    customer_id,
    DATE_TRUNC(MIN(order_date), MONTH) as cohort_month,
    DATE_TRUNC(order_date, MONTH) as order_month,
    SUM(revenue) as monthly_revenue
  FROM `project.dataset.sales`
  WHERE order_date >= '2024-01-01'
  GROUP BY 1, 3
),
cohort_sizes AS (
  SELECT 
    cohort_month,
    COUNT(DISTINCT customer_id) as total_cohort_size
  FROM customer_cohorts
  WHERE cohort_month = order_month
  GROUP BY 1
),
cohort_analysis AS (
  SELECT 
    cc.cohort_month,
    cc.order_month,
    DATE_DIFF(cc.order_month, cc.cohort_month, MONTH) as month_number,
    COUNT(DISTINCT cc.customer_id) as active_customers,
    SUM(cc.monthly_revenue) as cohort_revenue,
    cs.total_cohort_size as cohort_size,
    SUM(cc.monthly_revenue) 
      OVER (PARTITION BY cc.cohort_month ORDER BY cc.order_month 
            ROWS UNBOUNDED PRECEDING) as cumulative_revenue
  FROM customer_cohorts cc
  JOIN cohort_sizes cs ON cc.cohort_month = cs.cohort_month
  GROUP BY 1, 2, 4, 6
)
SELECT 
  cohort_month,
  month_number,
  active_customers,
  cohort_size,
  ROUND(active_customers / cohort_size * 100, 2) as retention_rate,
  cumulative_revenue,
  cohort_revenue
FROM cohort_analysis
WHERE month_number <= 12
ORDER BY cohort_month, month_number;

-- Time-series analysis with lag comparisons
SELECT 
  DATE_TRUNC(order_date, WEEK) as week_start,
  SUM(revenue) as weekly_revenue,
  LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as prev_week_revenue,
  LAG(SUM(revenue), 52) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as yoy_revenue,
  ROUND(
    (SUM(revenue) - LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK))) / 
    LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) * 100, 2
  ) as wow_growth_pct
FROM `project.dataset.sales`
WHERE order_date >= '2023-01-01'
GROUP BY 1
ORDER BY 1;

-- Efficient cohort analysis with optimized window functions
WITH customer_cohorts AS (
  SELECT 
    customer_id,
    DATE_TRUNC(MIN(order_date), MONTH) as cohort_month,
    DATE_TRUNC(order_date, MONTH) as order_month,
    SUM(revenue) as monthly_revenue
  FROM `project.dataset.sales`
  WHERE order_date >= '2024-01-01'
  GROUP BY 1, 3
),
cohort_sizes AS (
  SELECT 
    cohort_month,
    COUNT(DISTINCT customer_id) as total_cohort_size
  FROM customer_cohorts
  WHERE cohort_month = order_month
  GROUP BY 1
),
cohort_analysis AS (
  SELECT 
    cc.cohort_month,
    cc.order_month,
    DATE_DIFF(cc.order_month, cc.cohort_month, MONTH) as month_number,
    COUNT(DISTINCT cc.customer_id) as active_customers,
    SUM(cc.monthly_revenue) as cohort_revenue,
    cs.total_cohort_size as cohort_size,
    SUM(cc.monthly_revenue) 
      OVER (PARTITION BY cc.cohort_month ORDER BY cc.order_month 
            ROWS UNBOUNDED PRECEDING) as cumulative_revenue
  FROM customer_cohorts cc
  JOIN cohort_sizes cs ON cc.cohort_month = cs.cohort_month
  GROUP BY 1, 2, 4, 6
)
SELECT 
  cohort_month,
  month_number,
  active_customers,
  cohort_size,
  ROUND(active_customers / cohort_size * 100, 2) as retention_rate,
  cumulative_revenue,
  cohort_revenue
FROM cohort_analysis
WHERE month_number <= 12
ORDER BY cohort_month, month_number;

-- Time-series analysis with lag comparisons
SELECT 
  DATE_TRUNC(order_date, WEEK) as week_start,
  SUM(revenue) as weekly_revenue,
  LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as prev_week_revenue,
  LAG(SUM(revenue), 52) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as yoy_revenue,
  ROUND(
    (SUM(revenue) - LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK))) / 
    LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) * 100, 2
  ) as wow_growth_pct
FROM `project.dataset.sales`
WHERE order_date >= '2023-01-01'
GROUP BY 1
ORDER BY 1;

-- Efficient cohort analysis with optimized window functions
WITH customer_cohorts AS (
  SELECT 
    customer_id,
    DATE_TRUNC(MIN(order_date), MONTH) as cohort_month,
    DATE_TRUNC(order_date, MONTH) as order_month,
    SUM(revenue) as monthly_revenue
  FROM `project.dataset.sales`
  WHERE order_date >= '2024-01-01'
  GROUP BY 1, 3
),
cohort_sizes AS (
  SELECT 
    cohort_month,
    COUNT(DISTINCT customer_id) as total_cohort_size
  FROM customer_cohorts
  WHERE cohort_month = order_month
  GROUP BY 1
),
cohort_analysis AS (
  SELECT 
    cc.cohort_month,
    cc.order_month,
    DATE_DIFF(cc.order_month, cc.cohort_month, MONTH) as month_number,
    COUNT(DISTINCT cc.customer_id) as active_customers,
    SUM(cc.monthly_revenue) as cohort_revenue,
    cs.total_cohort_size as cohort_size,
    SUM(cc.monthly_revenue) 
      OVER (PARTITION BY cc.cohort_month ORDER BY cc.order_month 
            ROWS UNBOUNDED PRECEDING) as cumulative_revenue
  FROM customer_cohorts cc
  JOIN cohort_sizes cs ON cc.cohort_month = cs.cohort_month
  GROUP BY 1, 2, 4, 6
)
SELECT 
  cohort_month,
  month_number,
  active_customers,
  cohort_size,
  ROUND(active_customers / cohort_size * 100, 2) as retention_rate,
  cumulative_revenue,
  cohort_revenue
FROM cohort_analysis
WHERE month_number <= 12
ORDER BY cohort_month, month_number;

-- Time-series analysis with lag comparisons
SELECT 
  DATE_TRUNC(order_date, WEEK) as week_start,
  SUM(revenue) as weekly_revenue,
  LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as prev_week_revenue,
  LAG(SUM(revenue), 52) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as yoy_revenue,
  ROUND(
    (SUM(revenue) - LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK))) / 
    LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) * 100, 2
  ) as wow_growth_pct
FROM `project.dataset.sales`
WHERE order_date >= '2023-01-01'
GROUP BY 1
ORDER BY 1;

-- Efficient cohort analysis with optimized window functions
WITH customer_cohorts AS (
  SELECT 
    customer_id,
    DATE_TRUNC(MIN(order_date), MONTH) as cohort_month,
    DATE_TRUNC(order_date, MONTH) as order_month,
    SUM(revenue) as monthly_revenue
  FROM `project.dataset.sales`
  WHERE order_date >= '2024-01-01'
  GROUP BY 1, 3
),
cohort_sizes AS (
  SELECT 
    cohort_month,
    COUNT(DISTINCT customer_id) as total_cohort_size
  FROM customer_cohorts
  WHERE cohort_month = order_month
  GROUP BY 1
),
cohort_analysis AS (
  SELECT 
    cc.cohort_month,
    cc.order_month,
    DATE_DIFF(cc.order_month, cc.cohort_month, MONTH) as month_number,
    COUNT(DISTINCT cc.customer_id) as active_customers,
    SUM(cc.monthly_revenue) as cohort_revenue,
    cs.total_cohort_size as cohort_size,
    SUM(cc.monthly_revenue) 
      OVER (PARTITION BY cc.cohort_month ORDER BY cc.order_month 
            ROWS UNBOUNDED PRECEDING) as cumulative_revenue
  FROM customer_cohorts cc
  JOIN cohort_sizes cs ON cc.cohort_month = cs.cohort_month
  GROUP BY 1, 2, 4, 6
)
SELECT 
  cohort_month,
  month_number,
  active_customers,
  cohort_size,
  ROUND(active_customers / cohort_size * 100, 2) as retention_rate,
  cumulative_revenue,
  cohort_revenue
FROM cohort_analysis
WHERE month_number <= 12
ORDER BY cohort_month, month_number;

-- Time-series analysis with lag comparisons
SELECT 
  DATE_TRUNC(order_date, WEEK) as week_start,
  SUM(revenue) as weekly_revenue,
  LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as prev_week_revenue,
  LAG(SUM(revenue), 52) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) as yoy_revenue,
  ROUND(
    (SUM(revenue) - LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK))) / 
    LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC(order_date, WEEK)) * 100, 2
  ) as wow_growth_pct
FROM `project.dataset.sales`
WHERE order_date >= '2023-01-01'
GROUP BY 1
ORDER BY 1;

When you align window partitions with your analytical dimensions (customer cohorts, product categories, time periods), BigQuery can process these functions much more efficiently.

Alternatives: Array aggregation functions can handle some analytical patterns without window functions. Self-joins provide similar functionality but typically perform worse than optimized window functions. For teams requiring real-time analytical computations with complex window operations, e6data delivers sub-second performance for even the most complex, high-cardinality analytical queries.

Use APPROXIMATE Functions for Large-Scale Data Exploration

Exploratory data analysis on billion-row datasets doesn't always require exact precision - especially during the discovery phase when you're identifying patterns, outliers, or data quality issues. BigQuery's approximation functions enable rapid data exploration by trading minimal accuracy for dramatic performance improvements, letting you iterate through analytical hypotheses quickly.

Here's how approximation functions accelerate large-scale data exploration:

-- Fast market segment analysis with approximation
SELECT 
  region,
  customer_segment,
  APPROX_COUNT_DISTINCT(customer_id) as approx_customers,
  APPROX_QUANTILES(order_value, 100)[OFFSET(50)] as median_order_value,
  APPROX_QUANTILES(order_value, 100)[OFFSET(95)] as p95_order_value,
  SUM(revenue) as exact_revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY exact_revenue DESC;

-- Fast market segment analysis with approximation
SELECT 
  region,
  customer_segment,
  APPROX_COUNT_DISTINCT(customer_id) as approx_customers,
  APPROX_QUANTILES(order_value, 100)[OFFSET(50)] as median_order_value,
  APPROX_QUANTILES(order_value, 100)[OFFSET(95)] as p95_order_value,
  SUM(revenue) as exact_revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY exact_revenue DESC;

-- Fast market segment analysis with approximation
SELECT 
  region,
  customer_segment,
  APPROX_COUNT_DISTINCT(customer_id) as approx_customers,
  APPROX_QUANTILES(order_value, 100)[OFFSET(50)] as median_order_value,
  APPROX_QUANTILES(order_value, 100)[OFFSET(95)] as p95_order_value,
  SUM(revenue) as exact_revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY exact_revenue DESC;

-- Fast market segment analysis with approximation
SELECT 
  region,
  customer_segment,
  APPROX_COUNT_DISTINCT(customer_id) as approx_customers,
  APPROX_QUANTILES(order_value, 100)[OFFSET(50)] as median_order_value,
  APPROX_QUANTILES(order_value, 100)[OFFSET(95)] as p95_order_value,
  SUM(revenue) as exact_revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY exact_revenue DESC;

Avoid SQL Anti-Patterns for Better Performance

BigQuery performance suffers when queries follow common anti-patterns that create unnecessary computational overhead. Understanding and avoiding these patterns is crucial for maintaining analytical query performance at scale.

Here are the key anti-patterns to avoid:

-- AVOID: Self-joins (use window functions instead)
-- Anti-pattern
SELECT 
  a.customer_id,
  a.order_date,
  a.revenue,
  b.prev_order_revenue
FROM `project.dataset.sales` a
JOIN `project.dataset.sales` b 
  ON a.customer_id = b.customer_id 
  AND a.order_date > b.order_date;

-- Better: Use window functions
SELECT 
  customer_id,
  order_date,
  revenue,
  LAG(revenue) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
  ) as prev_order_revenue
FROM `project.dataset.sales`;

-- AVOID: Cross joins (Cartesian products)
-- Use pre-aggregation or filtering to reduce cross join output
WITH top_products AS (
  SELECT product_id, product_name
  FROM `project.dataset.products`
  WHERE category = 'Electronics'
  LIMIT 10
),
top_customers AS (
  SELECT customer_id, customer_name
  FROM `project.dataset.customers`
  WHERE segment = 'Enterprise'
  LIMIT 50
)
SELECT p.product_name, c.customer_name
FROM top_products p
CROSS JOIN top_customers c;

-- AVOID: Self-joins (use window functions instead)
-- Anti-pattern
SELECT 
  a.customer_id,
  a.order_date,
  a.revenue,
  b.prev_order_revenue
FROM `project.dataset.sales` a
JOIN `project.dataset.sales` b 
  ON a.customer_id = b.customer_id 
  AND a.order_date > b.order_date;

-- Better: Use window functions
SELECT 
  customer_id,
  order_date,
  revenue,
  LAG(revenue) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
  ) as prev_order_revenue
FROM `project.dataset.sales`;

-- AVOID: Cross joins (Cartesian products)
-- Use pre-aggregation or filtering to reduce cross join output
WITH top_products AS (
  SELECT product_id, product_name
  FROM `project.dataset.products`
  WHERE category = 'Electronics'
  LIMIT 10
),
top_customers AS (
  SELECT customer_id, customer_name
  FROM `project.dataset.customers`
  WHERE segment = 'Enterprise'
  LIMIT 50
)
SELECT p.product_name, c.customer_name
FROM top_products p
CROSS JOIN top_customers c;

-- AVOID: Self-joins (use window functions instead)
-- Anti-pattern
SELECT 
  a.customer_id,
  a.order_date,
  a.revenue,
  b.prev_order_revenue
FROM `project.dataset.sales` a
JOIN `project.dataset.sales` b 
  ON a.customer_id = b.customer_id 
  AND a.order_date > b.order_date;

-- Better: Use window functions
SELECT 
  customer_id,
  order_date,
  revenue,
  LAG(revenue) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
  ) as prev_order_revenue
FROM `project.dataset.sales`;

-- AVOID: Cross joins (Cartesian products)
-- Use pre-aggregation or filtering to reduce cross join output
WITH top_products AS (
  SELECT product_id, product_name
  FROM `project.dataset.products`
  WHERE category = 'Electronics'
  LIMIT 10
),
top_customers AS (
  SELECT customer_id, customer_name
  FROM `project.dataset.customers`
  WHERE segment = 'Enterprise'
  LIMIT 50
)
SELECT p.product_name, c.customer_name
FROM top_products p
CROSS JOIN top_customers c;

-- AVOID: Self-joins (use window functions instead)
-- Anti-pattern
SELECT 
  a.customer_id,
  a.order_date,
  a.revenue,
  b.prev_order_revenue
FROM `project.dataset.sales` a
JOIN `project.dataset.sales` b 
  ON a.customer_id = b.customer_id 
  AND a.order_date > b.order_date;

-- Better: Use window functions
SELECT 
  customer_id,
  order_date,
  revenue,
  LAG(revenue) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
  ) as prev_order_revenue
FROM `project.dataset.sales`;

-- AVOID: Cross joins (Cartesian products)
-- Use pre-aggregation or filtering to reduce cross join output
WITH top_products AS (
  SELECT product_id, product_name
  FROM `project.dataset.products`
  WHERE category = 'Electronics'
  LIMIT 10
),
top_customers AS (
  SELECT customer_id, customer_name
  FROM `project.dataset.customers`
  WHERE segment = 'Enterprise'
  LIMIT 50
)
SELECT p.product_name, c.customer_name
FROM top_products p
CROSS JOIN top_customers c;

Additionally, avoid DML statements that update single rows - BigQuery is optimized for batch operations, not OLTP workloads.

Alternatives: Use batch DML operations for updates and inserts. Consider streaming inserts for real-time requirements.

ETL/Streaming Optimization Tactics

Optimize Large Sorts with LIMIT Clauses

When sorting very large result sets, BigQuery can encounter resource exhaustion because final sorting occurs on a single slot. The combination of ORDER BY with very large datasets often results in "Resources exceeded" errors.

Here's how to handle large-scale sorting efficiently:

-- AVOID: Sorting massive result sets without LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC;  -- Can overwhelm single slot

-- Better: Use LIMIT with ORDER BY
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000;

-- For pagination: Use OFFSET with LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000 OFFSET 5000;

-- For large analytical sorting: Filter first, then sort
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
  AND revenue > 1000
ORDER BY revenue DESC;

-- AVOID: Sorting massive result sets without LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC;  -- Can overwhelm single slot

-- Better: Use LIMIT with ORDER BY
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000;

-- For pagination: Use OFFSET with LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000 OFFSET 5000;

-- For large analytical sorting: Filter first, then sort
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
  AND revenue > 1000
ORDER BY revenue DESC;

-- AVOID: Sorting massive result sets without LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC;  -- Can overwhelm single slot

-- Better: Use LIMIT with ORDER BY
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000;

-- For pagination: Use OFFSET with LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000 OFFSET 5000;

-- For large analytical sorting: Filter first, then sort
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
  AND revenue > 1000
ORDER BY revenue DESC;

-- AVOID: Sorting massive result sets without LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC;  -- Can overwhelm single slot

-- Better: Use LIMIT with ORDER BY
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000;

-- For pagination: Use OFFSET with LIMIT
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
ORDER BY revenue DESC
LIMIT 1000 OFFSET 5000;

-- For large analytical sorting: Filter first, then sort
SELECT customer_id, order_date, revenue
FROM `project.dataset.sales`
WHERE order_date >= '2024-01-01'
  AND revenue > 1000
ORDER BY revenue DESC;

The key insight is to reduce the dataset size before sorting, rather than attempting to sort the entire table and then limiting results.

Alternatives: Window functions with RANK() can identify top records without full sorting. Materialized views can pre-sort frequently accessed data.

Optimize Batch Loading with Strategic Partitioning and Clustering

Large-scale ETL operations in BigQuery require careful attention to data organization strategy. When your daily ETL jobs process hundreds of gigabytes or terabytes, the difference between well-partitioned tables and heap tables can mean the difference between 15-minute loads and 3-hour operations. Strategic partitioning combined with clustering transforms both write performance and downstream query efficiency.

Here's how to structure high-volume ETL operations for optimal performance:

-- Create optimally partitioned destination table for ETL
CREATE OR REPLACE TABLE `project.dataset.sales_optimized`
(
  transaction_id STRING,
  customer_id STRING,
  product_id STRING,
  transaction_timestamp TIMESTAMP,
  revenue NUMERIC,
  region STRING,
  product_category STRING,
  customer_segment STRING
)
PARTITION BY DATE(transaction_timestamp)
CLUSTER BY region, product_category, customer_segment
OPTIONS (
  partition_expiration_days = 1095,  -- 3 years
  description = "Sales data optimized for analytical workloads"
);

-- Efficient batch insert with partition alignment
INSERT INTO `project.dataset.sales_optimized`
SELECT 
  transaction_id,
  customer_id,
  product_id,
  transaction_timestamp,
  CAST(revenue as NUMERIC) as revenue,
  UPPER(TRIM(region)) as region,
  COALESCE(product_category, 'Unknown') as product_category,
  CASE 
    WHEN customer_ltv > 5000 THEN 'Enterprise'
    WHEN customer_ltv > 1000 THEN 'Commercial'
    ELSE 'SMB'
  END as customer_segment
FROM `project.staging.daily_sales_raw`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1  -- Process yesterday's data
  AND transaction_id IS NOT NULL;

-- Bulk partition maintenance for ETL operations
DELETE FROM `project.dataset.sales_optimized`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1;  -- Clean before reload

-- Efficient MERGE operation for incremental loads
MERGE `project.dataset.sales_optimized` target
USING (
  SELECT 
    transaction_id,
    customer_id,
    product_id,
    transaction_timestamp,
    revenue,
    region,
    product_category,
    customer_segment
  FROM `project.staging.daily_sales_incremental`
  WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1
) source
ON target.transaction_id = source.transaction_id
WHEN MATCHED THEN UPDATE SET
  revenue = source.revenue,
  region = source.region,
  product_category = source.product_category,
  customer_segment = source.customer_segment
WHEN NOT MATCHED THEN INSERT (
  transaction_id, customer_id, product_id, transaction_timestamp,
  revenue, region, product_category, customer_segment
) VALUES (
  source.transaction_id, source.customer_id, source.product_id, 
  source.transaction_timestamp, source.revenue, source.region,
  source.product_category, source.customer_segment
);

-- Create optimally partitioned destination table for ETL
CREATE OR REPLACE TABLE `project.dataset.sales_optimized`
(
  transaction_id STRING,
  customer_id STRING,
  product_id STRING,
  transaction_timestamp TIMESTAMP,
  revenue NUMERIC,
  region STRING,
  product_category STRING,
  customer_segment STRING
)
PARTITION BY DATE(transaction_timestamp)
CLUSTER BY region, product_category, customer_segment
OPTIONS (
  partition_expiration_days = 1095,  -- 3 years
  description = "Sales data optimized for analytical workloads"
);

-- Efficient batch insert with partition alignment
INSERT INTO `project.dataset.sales_optimized`
SELECT 
  transaction_id,
  customer_id,
  product_id,
  transaction_timestamp,
  CAST(revenue as NUMERIC) as revenue,
  UPPER(TRIM(region)) as region,
  COALESCE(product_category, 'Unknown') as product_category,
  CASE 
    WHEN customer_ltv > 5000 THEN 'Enterprise'
    WHEN customer_ltv > 1000 THEN 'Commercial'
    ELSE 'SMB'
  END as customer_segment
FROM `project.staging.daily_sales_raw`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1  -- Process yesterday's data
  AND transaction_id IS NOT NULL;

-- Bulk partition maintenance for ETL operations
DELETE FROM `project.dataset.sales_optimized`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1;  -- Clean before reload

-- Efficient MERGE operation for incremental loads
MERGE `project.dataset.sales_optimized` target
USING (
  SELECT 
    transaction_id,
    customer_id,
    product_id,
    transaction_timestamp,
    revenue,
    region,
    product_category,
    customer_segment
  FROM `project.staging.daily_sales_incremental`
  WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1
) source
ON target.transaction_id = source.transaction_id
WHEN MATCHED THEN UPDATE SET
  revenue = source.revenue,
  region = source.region,
  product_category = source.product_category,
  customer_segment = source.customer_segment
WHEN NOT MATCHED THEN INSERT (
  transaction_id, customer_id, product_id, transaction_timestamp,
  revenue, region, product_category, customer_segment
) VALUES (
  source.transaction_id, source.customer_id, source.product_id, 
  source.transaction_timestamp, source.revenue, source.region,
  source.product_category, source.customer_segment
);

-- Create optimally partitioned destination table for ETL
CREATE OR REPLACE TABLE `project.dataset.sales_optimized`
(
  transaction_id STRING,
  customer_id STRING,
  product_id STRING,
  transaction_timestamp TIMESTAMP,
  revenue NUMERIC,
  region STRING,
  product_category STRING,
  customer_segment STRING
)
PARTITION BY DATE(transaction_timestamp)
CLUSTER BY region, product_category, customer_segment
OPTIONS (
  partition_expiration_days = 1095,  -- 3 years
  description = "Sales data optimized for analytical workloads"
);

-- Efficient batch insert with partition alignment
INSERT INTO `project.dataset.sales_optimized`
SELECT 
  transaction_id,
  customer_id,
  product_id,
  transaction_timestamp,
  CAST(revenue as NUMERIC) as revenue,
  UPPER(TRIM(region)) as region,
  COALESCE(product_category, 'Unknown') as product_category,
  CASE 
    WHEN customer_ltv > 5000 THEN 'Enterprise'
    WHEN customer_ltv > 1000 THEN 'Commercial'
    ELSE 'SMB'
  END as customer_segment
FROM `project.staging.daily_sales_raw`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1  -- Process yesterday's data
  AND transaction_id IS NOT NULL;

-- Bulk partition maintenance for ETL operations
DELETE FROM `project.dataset.sales_optimized`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1;  -- Clean before reload

-- Efficient MERGE operation for incremental loads
MERGE `project.dataset.sales_optimized` target
USING (
  SELECT 
    transaction_id,
    customer_id,
    product_id,
    transaction_timestamp,
    revenue,
    region,
    product_category,
    customer_segment
  FROM `project.staging.daily_sales_incremental`
  WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1
) source
ON target.transaction_id = source.transaction_id
WHEN MATCHED THEN UPDATE SET
  revenue = source.revenue,
  region = source.region,
  product_category = source.product_category,
  customer_segment = source.customer_segment
WHEN NOT MATCHED THEN INSERT (
  transaction_id, customer_id, product_id, transaction_timestamp,
  revenue, region, product_category, customer_segment
) VALUES (
  source.transaction_id, source.customer_id, source.product_id, 
  source.transaction_timestamp, source.revenue, source.region,
  source.product_category, source.customer_segment
);

-- Create optimally partitioned destination table for ETL
CREATE OR REPLACE TABLE `project.dataset.sales_optimized`
(
  transaction_id STRING,
  customer_id STRING,
  product_id STRING,
  transaction_timestamp TIMESTAMP,
  revenue NUMERIC,
  region STRING,
  product_category STRING,
  customer_segment STRING
)
PARTITION BY DATE(transaction_timestamp)
CLUSTER BY region, product_category, customer_segment
OPTIONS (
  partition_expiration_days = 1095,  -- 3 years
  description = "Sales data optimized for analytical workloads"
);

-- Efficient batch insert with partition alignment
INSERT INTO `project.dataset.sales_optimized`
SELECT 
  transaction_id,
  customer_id,
  product_id,
  transaction_timestamp,
  CAST(revenue as NUMERIC) as revenue,
  UPPER(TRIM(region)) as region,
  COALESCE(product_category, 'Unknown') as product_category,
  CASE 
    WHEN customer_ltv > 5000 THEN 'Enterprise'
    WHEN customer_ltv > 1000 THEN 'Commercial'
    ELSE 'SMB'
  END as customer_segment
FROM `project.staging.daily_sales_raw`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1  -- Process yesterday's data
  AND transaction_id IS NOT NULL;

-- Bulk partition maintenance for ETL operations
DELETE FROM `project.dataset.sales_optimized`
WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1;  -- Clean before reload

-- Efficient MERGE operation for incremental loads
MERGE `project.dataset.sales_optimized` target
USING (
  SELECT 
    transaction_id,
    customer_id,
    product_id,
    transaction_timestamp,
    revenue,
    region,
    product_category,
    customer_segment
  FROM `project.staging.daily_sales_incremental`
  WHERE DATE(transaction_timestamp) = CURRENT_DATE - 1
) source
ON target.transaction_id = source.transaction_id
WHEN MATCHED THEN UPDATE SET
  revenue = source.revenue,
  region = source.region,
  product_category = source.product_category,
  customer_segment = source.customer_segment
WHEN NOT MATCHED THEN INSERT (
  transaction_id, customer_id, product_id, transaction_timestamp,
  revenue, region, product_category, customer_segment
) VALUES (
  source.transaction_id, source.customer_id, source.product_id, 
  source.transaction_timestamp, source.revenue, source.region,
  source.product_category, source.customer_segment
);

Alternatives: Time-unit column partitioning provides finer granularity than daily partitioning for high-frequency data. Ingestion-time partitioning simplifies ETL logic but may not align with analytical access patterns.

Implement Streaming Inserts with Optimal Buffering Strategies

Real-time data pipelines feeding BigQuery require careful attention to streaming buffer management and insertion patterns. The challenge lies in balancing data freshness requirements with cost efficiency - streaming inserts cost more than batch loads, but poorly optimized streaming can result in buffer overflow, duplicate detection issues, and query performance degradation on recently streamed data.

Here's how to structure streaming operations for optimal performance and cost control:

-- Optimize streaming table structure for real-time ingestion
CREATE OR REPLACE TABLE `project.dataset.events_streaming`
(
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP,
  session_id STRING,
  _insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY event_type, user_id;

-- Query pattern optimized for streaming buffer considerations
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(*) as total_events
FROM `project.dataset.events_streaming`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY 1, 2
ORDER BY 1, 2;

-- Optimize streaming table structure for real-time ingestion
CREATE OR REPLACE TABLE `project.dataset.events_streaming`
(
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP,
  session_id STRING,
  _insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY event_type, user_id;

-- Query pattern optimized for streaming buffer considerations
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(*) as total_events
FROM `project.dataset.events_streaming`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY 1, 2
ORDER BY 1, 2;

-- Optimize streaming table structure for real-time ingestion
CREATE OR REPLACE TABLE `project.dataset.events_streaming`
(
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP,
  session_id STRING,
  _insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY event_type, user_id;

-- Query pattern optimized for streaming buffer considerations
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(*) as total_events
FROM `project.dataset.events_streaming`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY 1, 2
ORDER BY 1, 2;

-- Optimize streaming table structure for real-time ingestion
CREATE OR REPLACE TABLE `project.dataset.events_streaming`
(
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP,
  session_id STRING,
  _insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY event_type, user_id;

-- Query pattern optimized for streaming buffer considerations
SELECT 
  DATE(event_timestamp) as event_date,
  event_type,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(*) as total_events
FROM `project.dataset.events_streaming`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY 1, 2
ORDER BY 1, 2;

What makes this particularly effective is the separation of hot streaming data from cold analytical data, combined with proper buffer management. You'll find that this approach reduces streaming costs while maintaining query performance on both real-time and historical data.

Alternatives: Dataflow streaming can handle complex streaming transformations before BigQuery insertion but adds infrastructure complexity. Storage Write API provides higher throughput streaming but requires custom integration logic. For streaming workloads requiring guaranteed sub-second latency and unlimited concurrency, e6data processes streaming data with the same performance characteristics as batch operations.

Avoid Wildcard Table Queries and Date-Sharded Tables

BigQuery supports querying multiple tables using wildcard expressions, but performance suffers when wildcards are too broad or when using legacy date-sharded table patterns. Modern partitioning strategies dramatically outperform date-sharded tables.

Here's how to optimize multi-table querying:

-- AVOID: Broad wildcard that scans many unnecessary tables
SELECT *
FROM `project.dataset.sales_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131';

-- Better: Use granular prefixes for wildcards
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_202401*`
WHERE _TABLE_SUFFIX BETWEEN '01' AND '31';

-- Best: Use partitioned tables instead of date-sharded tables
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_partitioned`
WHERE DATE(order_timestamp) BETWEEN '2024-01-01' AND '2024-01-31';

-- AVOID: Broad wildcard that scans many unnecessary tables
SELECT *
FROM `project.dataset.sales_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131';

-- Better: Use granular prefixes for wildcards
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_202401*`
WHERE _TABLE_SUFFIX BETWEEN '01' AND '31';

-- Best: Use partitioned tables instead of date-sharded tables
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_partitioned`
WHERE DATE(order_timestamp) BETWEEN '2024-01-01' AND '2024-01-31';

-- AVOID: Broad wildcard that scans many unnecessary tables
SELECT *
FROM `project.dataset.sales_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131';

-- Better: Use granular prefixes for wildcards
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_202401*`
WHERE _TABLE_SUFFIX BETWEEN '01' AND '31';

-- Best: Use partitioned tables instead of date-sharded tables
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_partitioned`
WHERE DATE(order_timestamp) BETWEEN '2024-01-01' AND '2024-01-31';

-- AVOID: Broad wildcard that scans many unnecessary tables
SELECT *
FROM `project.dataset.sales_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131';

-- Better: Use granular prefixes for wildcards
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_202401*`
WHERE _TABLE_SUFFIX BETWEEN '01' AND '31';

-- Best: Use partitioned tables instead of date-sharded tables
SELECT 
  order_id,
  revenue,
  order_date
FROM `project.dataset.sales_partitioned`
WHERE DATE(order_timestamp) BETWEEN '2024-01-01' AND '2024-01-31';