GCP BigQuery — Serverless Data Warehouse and Analytics Guide

Introduction

Why This Matters

Google BigQuery is a fully managed, serverless data warehouse that can analyze petabytes of data in seconds using standard SQL. Unlike traditional databases, BigQuery separates storage from compute and charges per bytes scanned rather than for idle infrastructure. It is the foundation of most GCP data stacks, integrating natively with Pub/Sub, Dataflow, Looker, and Cloud Storage for end-to-end analytics pipelines.

BigQuery CLI Basics

# Install and authenticate
gcloud components install bq
gcloud auth application-default login
 
# List datasets
bq ls --project_id=my-project
 
# Create dataset
bq mk \
  --dataset \
  --location=US \
  --description="Analytics events" \
  my-project:analytics
 
# List tables in dataset
bq ls my-project:analytics
 
# Show table schema
bq show --schema --format=prettyjson my-project:analytics.events
 
# Run a query
bq query \
  --use_legacy_sql=false \
  --project_id=my-project \
  'SELECT COUNT(*) as total FROM `my-project.analytics.events`'
 
# Estimate bytes scanned before running (no execution)
bq query \
  --use_legacy_sql=false \
  --dry_run \
  'SELECT * FROM `my-project.analytics.events`'

SQL Queries and Patterns

-- Session analysis with window functions
SELECT
  user_id,
  session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  TIMESTAMP_DIFF(MAX(event_time), MIN(event_time), SECOND) AS duration_seconds,
  COUNT(*) AS event_count
FROM `my-project.analytics.events`
WHERE DATE(event_time) = CURRENT_DATE()
GROUP BY user_id, session_id
ORDER BY duration_seconds DESC
LIMIT 100;
 
-- Funnel analysis
WITH steps AS (
  SELECT
    user_id,
    COUNTIF(event_name = 'page_view') > 0 AS step_1,
    COUNTIF(event_name = 'add_to_cart') > 0 AS step_2,
    COUNTIF(event_name = 'checkout_start') > 0 AS step_3,
    COUNTIF(event_name = 'purchase') > 0 AS step_4
  FROM `my-project.analytics.events`
  WHERE DATE(event_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
  GROUP BY user_id
)
SELECT
  COUNTIF(step_1) AS visitors,
  COUNTIF(step_2) AS added_to_cart,
  COUNTIF(step_3) AS started_checkout,
  COUNTIF(step_4) AS purchased,
  ROUND(COUNTIF(step_4) / COUNTIF(step_1) * 100, 2) AS conversion_rate
FROM steps;
 
-- Using UNNEST for array columns
SELECT
  user_id,
  product.name,
  product.quantity
FROM `my-project.orders.daily`
CROSS JOIN UNNEST(products) AS product
WHERE DATE(_PARTITIONTIME) = CURRENT_DATE();

Loading Data

# Load CSV from local file
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --autodetect \
  my-project:analytics.users \
  ./users.csv
 
# Load JSON from Cloud Storage
bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --autodetect \
  my-project:analytics.events \
  gs://my-bucket/events/2024/01/*.json
 
# Stream insert via API (real-time ingestion)
bq insert my-project:analytics.events events.json
 
# Create table from query result
bq query \
  --use_legacy_sql=false \
  --destination_table=my-project:analytics.daily_summary \
  --replace \
  'SELECT DATE(event_time) as date, COUNT(*) as events
   FROM `my-project.analytics.events`
   GROUP BY 1'

Partitioning and Clustering

-- Create partitioned and clustered table
-- Partitioning: prunes entire partitions (reduces bytes scanned dramatically)
-- Clustering: sorts data within partitions for efficient range queries
CREATE TABLE `my-project.analytics.events`
(
  event_id STRING NOT NULL,
  user_id STRING,
  event_name STRING,
  event_time TIMESTAMP,
  properties JSON
)
PARTITION BY DATE(event_time)
CLUSTER BY user_id, event_name
OPTIONS (
  partition_expiration_days = 365,
  require_partition_filter = true
);
 
-- Always filter on partition column in WHERE clause
-- Good: scans only relevant date partitions
SELECT * FROM `my-project.analytics.events`
WHERE DATE(event_time) = '2024-01-15'
AND user_id = 'user-123';
 
-- Bad: full table scan (expensive)
-- SELECT * FROM `my-project.analytics.events`
-- WHERE user_id = 'user-123'

Cost Optimization

# BigQuery pricing:
# - On-demand: $6.25 per TB scanned (first 1 TB/month free)
# - Flat-rate: Fixed monthly slots for predictable cost
 
# Check bytes processed by a query (dry run)
bq query --dry_run --use_legacy_sql=false \
  'SELECT * FROM `my-project.analytics.events` WHERE DATE(event_time) = CURRENT_DATE()'
 
# Save query results to table to avoid re-scanning
bq query \
  --use_legacy_sql=false \
  --destination_table=my-project:analytics.cached_result \
  --use_cache=true \
  'SELECT ...'
 
# Use materialized views for frequently queried aggregations

-- Materialized view (auto-refreshed, query routing)
CREATE MATERIALIZED VIEW `my-project.analytics.daily_events_mv`
AS
SELECT
  DATE(event_time) AS date,
  event_name,
  COUNT(*) AS count
FROM `my-project.analytics.events`
GROUP BY 1, 2;

Scheduled Queries and Data Pipelines

# Create scheduled query
bq mk \
  --transfer_config \
  --project_id=my-project \
  --data_source=scheduled_query \
  --display_name='Daily aggregation' \
  --schedule='every 24 hours' \
  --params='{
    "query": "INSERT INTO analytics.daily_summary SELECT DATE(event_time), COUNT(*) FROM analytics.events WHERE DATE(event_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) GROUP BY 1",
    "destination_table_name_template": "daily_summary",
    "write_disposition": "WRITE_APPEND"
  }'

Common Mistakes

Querying without WHERE filters on partitioned tables — always filter on the partition column to avoid full table scans
Using SELECT * in production queries — select only needed columns; BigQuery is columnar and scans entire columns
Not setting require_partition_filter = true on large tables — protects against accidental costly full scans
Using streaming inserts for large batch loads — use batch loading (GCS → BigQuery) for throughput and cost efficiency
Not monitoring slot utilization for flat-rate customers — under-utilized flat-rate slots represent wasted spend

Best Practices

Partition large tables by date and cluster by commonly filtered columns (user_id, event_name)
Use INFORMATION_SCHEMA.JOBS to audit query costs and identify expensive queries by user or job
Cache query results — identical queries within 24 hours serve from cache at no cost
Use BigQuery ML for in-database machine learning — avoids exporting data to external ML systems
Export cold data to Cloud Storage with BigQuery external tables for lowest cost long-term storage
Use column-level security and row-level access policies to enforce data governance

Key Takeaways

BigQuery uses columnar storage and distributed compute — queries that select fewer columns and scan fewer rows cost less
Partitioning divides tables by date (or integer range) — queries with partition filters skip irrelevant data entirely
Clustering sorts data within partitions by specified columns — accelerates range queries and equality filters
On-demand pricing charges $6.25 per TB scanned — --dry_run estimates cost before executing
Materialized views pre-aggregate query results and are automatically refreshed when base tables update
Streaming inserts provide sub-second data availability but cost more than batch loading from Cloud Storage
BigQuery supports standard SQL including window functions, UNNEST for arrays, and JSON functions
The first 1 TB of query data processed per month is free — ideal for development and small-scale analytics

Introduction#

Why This Matters#

BigQuery CLI Basics#

SQL Queries and Patterns#

Loading Data#

Partitioning and Clustering#

Cost Optimization#

Scheduled Queries and Data Pipelines#

Common Mistakes#

Best Practices#

Key Takeaways#

Related reading