BigQuery Patterns

Architecture overview

BigQuery is a serverless columnar warehouse. There are no clusters to manage - you submit SQL and Google allocates compute automatically. Storage and compute are decoupled. Pricing is either on-demand (per TB scanned) or capacity-based (reserved slots). All data is stored in Capacitor columnar format, replicated across zones.

Pricing models

Key insight: On-demand charges are based on bytes scanned, not rows returned. A SELECT * on a 500 GB table costs ~$3.13 even if the result is 10 rows. Partitioning and clustering directly reduce bytes scanned and therefore cost.

Partitioning

-- Partition by date column (most common)
CREATE TABLE `project.dataset.fct_events`
(
  event_id    STRING    NOT NULL,
  event_date  DATE      NOT NULL,
  user_id     STRING    NOT NULL,
  event_type  STRING    NOT NULL,
  properties  JSON
)
PARTITION BY event_date
OPTIONS (
  partition_expiration_days = 365,  -- auto-delete old partitions
  require_partition_filter = TRUE   -- prevent full scans
);

-- Partition by ingestion time (when source has no reliable date column)
CREATE TABLE `project.dataset.fct_raw_events`
(
  payload STRING
)
PARTITION BY _PARTITIONDATE;

-- Integer range partitioning
CREATE TABLE `project.dataset.fct_transactions`
(
  txn_id      INT64,
  customer_id INT64,
  amount      NUMERIC
)
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 10000000, 100000));

require_partition_filter = TRUE is a guardrail that prevents queries without a partition filter from running. Use it on all large fact tables.

Clustering

-- Cluster by up to 4 columns (order matters: most filtered first)
CREATE TABLE `project.dataset.fct_sales`
(
  sale_date    DATE      NOT NULL,
  region       STRING    NOT NULL,
  customer_id  STRING    NOT NULL,
  product_id   STRING    NOT NULL,
  amount       NUMERIC   NOT NULL
)
PARTITION BY sale_date
CLUSTER BY region, customer_id;

-- BigQuery auto-reclusters in the background at no cost
-- Unlike Snowflake, there is no manual recluster command

Clustering column selection: Choose columns that appear frequently in WHERE clauses, JOIN conditions, or GROUP BY. High-cardinality columns (like customer_id) benefit most.

Nested and repeated fields (STRUCT and ARRAY)

BigQuery natively supports nested data, which avoids joins.

-- Schema with nested and repeated fields
CREATE TABLE `project.dataset.orders_nested`
(
  order_id    STRING NOT NULL,
  order_date  DATE   NOT NULL,
  customer    STRUCT<
    id        STRING,
    name      STRING,
    segment   STRING
  >,
  items       ARRAY<STRUCT<
    product_id STRING,
    quantity   INT64,
    unit_price NUMERIC
  >>
);

-- Query nested fields with dot notation
SELECT
  order_id,
  customer.name,
  customer.segment
FROM `project.dataset.orders_nested`
WHERE order_date = '2026-03-14';

-- Unnest arrays to flatten
SELECT
  o.order_id,
  item.product_id,
  item.quantity * item.unit_price AS line_total
FROM `project.dataset.orders_nested` o,
  UNNEST(o.items) AS item;

Use nested/repeated fields when the child data is always accessed with the parent. This eliminates join costs and scans less data. Do not use when the child needs independent access patterns (keep it as a separate table).

Materialized views

CREATE MATERIALIZED VIEW `project.dataset.mv_daily_revenue`
AS
SELECT
  sale_date,
  region,
  SUM(amount)  AS total_revenue,
  COUNT(*)     AS txn_count
FROM `project.dataset.fct_sales`
GROUP BY sale_date, region;

-- BigQuery automatically uses the MV when it can answer a query
-- No need to query the MV directly (smart tuning / automatic rewriting)

-- Refresh is automatic and incremental
-- Force refresh if needed:
CALL BQ.REFRESH_MATERIALIZED_VIEW('project.dataset.mv_daily_revenue');

Limitations: MVs cannot use non-deterministic functions, OUTER JOINs, or window functions. They work best for simple aggregations on partitioned/clustered tables.

BI Engine

BI Engine is an in-memory analysis service that accelerates queries from BI tools (Looker, Data Studio, Tableau). Reserve BI Engine capacity in the BigQuery console.

• Queries that fit in the BI Engine reservation are served from memory (~1 second)
• No code changes needed - it transparently accelerates compatible queries
• Best for dashboards with repetitive query patterns on a stable dataset

Scheduled queries

-- Schedule a daily aggregation (runs as a scheduled query in BigQuery UI or API)
-- In the BigQuery console: More > Schedule > set schedule

-- Or via bq CLI:
-- bq mk --transfer_config \
--   --project_id=myproject \
--   --data_source=scheduled_query \
--   --target_dataset=dataset \
--   --display_name='Daily Sales Agg' \
--   --schedule='every 24 hours' \
--   --params='{"query":"INSERT INTO dataset.daily_sales SELECT ..."}'

Federated queries

Query external data without loading it into BigQuery.

-- Query Cloud SQL (PostgreSQL) directly from BigQuery
SELECT * FROM EXTERNAL_QUERY(
  'projects/myproject/locations/us/connections/my-cloudsql',
  'SELECT id, email, created_at FROM users WHERE created_at > NOW() - INTERVAL 1 DAY'
);

-- External tables over Cloud Storage (Parquet, CSV, JSON)
CREATE EXTERNAL TABLE `project.dataset.ext_events`
  OPTIONS (
    format = 'PARQUET',
    uris = ['gs://my-bucket/events/*.parquet']
  );

Federated queries are slower than native tables. Use them for ad-hoc exploration or infrequent joins with external systems. For regular analytics, load data into BigQuery native tables.

Cost optimization checklist

1. Always partition fact tables by date and set require_partition_filter = TRUE
2. Cluster by top 2-4 filter/group columns
3. **Avoid SELECT ***; list only needed columns (columnar storage = less scan)
4. Use dry runs (bq query --dry_run) to estimate cost before executing
5. Set custom cost controls in project settings (max bytes billed per query)
6. Monitor with INFORMATION_SCHEMA.JOBS to find expensive queries
7. Consider editions pricing when monthly spend exceeds ~$2,000 on-demand
8. Use table expiration and partition expiration to auto-delete old data

SCD Patterns

Slowly Changing Dimensions (SCDs) handle the reality that dimension attributes change over time. The type you choose determines whether history is preserved and how it is queried. This reference covers all standard types with complete SQL implementations.

SCD Type 0 - Fixed attribute

The attribute never changes after initial load. Use for truly immutable data.

-- Examples: birth_date, original_signup_date, first_order_date
-- Implementation: simply never update these columns
-- In MERGE statements, exclude Type 0 columns from the WHEN MATCHED clause

SCD Type 1 - Overwrite

Replace the old value with the new value. No history is preserved.

-- Snowflake Type 1 MERGE
MERGE INTO dim_product AS target
USING staging_products AS source
  ON target.product_id = source.product_id
WHEN MATCHED
  AND (target.category != source.category
    OR target.brand    != source.brand)
THEN UPDATE SET
  target.category = source.category,
  target.brand    = source.brand,
  target.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (
  product_sk, product_id, product_name, category, brand, updated_at
) VALUES (
  dim_product_seq.NEXTVAL,
  source.product_id, source.product_name, source.category, source.brand,
  CURRENT_TIMESTAMP()
);

When to use: Corrections (fixing a typo), attributes where history is irrelevant (e.g., updating a product description), or when storage/complexity must be minimized.

Trade-off: Simple and fast but you cannot answer "what was the category last month?"

SCD Type 2 - Add new row

Create a new row for each change, preserving full history. This is the most common type for analytical warehouses.

Required columns

Snowflake implementation

-- Step 1: Expire changed rows
MERGE INTO dim_customer AS target
USING staging_customers AS source
  ON target.customer_id = source.customer_id
     AND target.is_current = TRUE
WHEN MATCHED
  AND (target.segment  != source.segment
    OR target.region   != source.region
    OR target.tier     != source.tier)
THEN UPDATE SET
  target.expiry_date = CURRENT_DATE - 1,
  target.is_current  = FALSE;

-- Step 2: Insert new current rows for changed and new records
INSERT INTO dim_customer (
  customer_sk, customer_id, name, segment, region, tier,
  effective_date, expiry_date, is_current
)
SELECT
  dim_customer_seq.NEXTVAL,
  s.customer_id, s.name, s.segment, s.region, s.tier,
  CURRENT_DATE, '9999-12-31', TRUE
FROM staging_customers s
WHERE NOT EXISTS (
  SELECT 1 FROM dim_customer d
  WHERE d.customer_id = s.customer_id
    AND d.is_current = TRUE
    AND d.segment = s.segment
    AND d.region  = s.region
    AND d.tier    = s.tier
);

BigQuery implementation

-- Step 1: Expire changed rows
MERGE `project.dataset.dim_customer` AS target
USING `project.dataset.staging_customers` AS source
  ON target.customer_id = source.customer_id
     AND target.is_current = TRUE
WHEN MATCHED
  AND (target.segment != source.segment
    OR target.region  != source.region
    OR target.tier    != source.tier)
THEN UPDATE SET
  expiry_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),
  is_current  = FALSE;

-- Step 2: Insert new current rows
INSERT INTO `project.dataset.dim_customer` (
  customer_sk, customer_id, name, segment, region, tier,
  effective_date, expiry_date, is_current
)
SELECT
  GENERATE_UUID(),
  s.customer_id, s.name, s.segment, s.region, s.tier,
  CURRENT_DATE(), DATE '9999-12-31', TRUE
FROM `project.dataset.staging_customers` s
WHERE NOT EXISTS (
  SELECT 1 FROM `project.dataset.dim_customer` d
  WHERE d.customer_id = s.customer_id
    AND d.is_current = TRUE
    AND d.segment = s.segment
    AND d.region  = s.region
    AND d.tier    = s.tier
);

Querying Type 2 dimensions

-- Get current state of all customers
SELECT * FROM dim_customer WHERE is_current = TRUE;

-- Point-in-time lookup: what segment was customer C123 in on 2025-06-15?
SELECT segment
FROM dim_customer
WHERE customer_id = 'C123'
  AND effective_date <= '2025-06-15'
  AND expiry_date   >= '2025-06-15';

-- Join fact to dimension for historical accuracy
SELECT f.sale_date, d.segment, SUM(f.net_amount)
FROM fct_sales f
JOIN dim_customer d
  ON f.customer_sk = d.customer_sk  -- surrogate key join: gets the version active at sale time
GROUP BY f.sale_date, d.segment;

SCD Type 3 - Previous value column

Store the current and one previous value as separate columns. Limited history.

CREATE TABLE dim_customer_type3 (
  customer_sk       INT         PRIMARY KEY,
  customer_id       VARCHAR(50) NOT NULL,
  name              VARCHAR(200),
  current_segment   VARCHAR(50),
  previous_segment  VARCHAR(50),
  segment_change_date DATE
);

-- Update: shift current to previous
UPDATE dim_customer_type3
SET previous_segment  = current_segment,
    current_segment   = 'Enterprise',
    segment_change_date = CURRENT_DATE
WHERE customer_id = 'C123';

When to use: Rarely. Only when you need exactly one prior value and never more. Most analytical needs are better served by Type 2.

SCD Type 6 - Hybrid (1 + 2 + 3)

Combines Type 2 history rows with a Type 1 overwrite column for the current value. This gives you both full history and easy access to the current state on every row.

CREATE TABLE dim_customer_type6 (
  customer_sk        INT         PRIMARY KEY,
  customer_id        VARCHAR(50) NOT NULL,
  name               VARCHAR(200),
  -- Type 2 historical value
  historical_segment VARCHAR(50),
  -- Type 1 current value (same on ALL rows for this customer)
  current_segment    VARCHAR(50),
  -- Type 2 tracking
  effective_date     DATE        NOT NULL,
  expiry_date        DATE        NOT NULL DEFAULT '9999-12-31',
  is_current         BOOLEAN     NOT NULL DEFAULT TRUE
);

-- When segment changes:
-- 1. Expire the current row (Type 2)
UPDATE dim_customer_type6
SET expiry_date = CURRENT_DATE - 1,
    is_current  = FALSE,
    current_segment = 'Enterprise'  -- Type 1: update on ALL rows
WHERE customer_id = 'C123' AND is_current = TRUE;

-- 2. Insert new current row (Type 2)
INSERT INTO dim_customer_type6 VALUES (
  NEXTVAL, 'C123', 'Acme Corp',
  'Enterprise',   -- historical_segment = the new value
  'Enterprise',   -- current_segment = the new value
  CURRENT_DATE, '9999-12-31', TRUE
);

-- 3. Backfill current_segment on all historical rows (Type 1 overwrite)
UPDATE dim_customer_type6
SET current_segment = 'Enterprise'
WHERE customer_id = 'C123';

Querying Type 6

-- "What segment is the customer in NOW?" (use current_segment on any row)
SELECT DISTINCT customer_id, current_segment
FROM dim_customer_type6
WHERE customer_id = 'C123';

-- "What segment were they in when this sale happened?" (use historical_segment)
SELECT f.sale_date, d.historical_segment, SUM(f.net_amount)
FROM fct_sales f
JOIN dim_customer_type6 d ON f.customer_sk = d.customer_sk
GROUP BY f.sale_date, d.historical_segment;

When to use: When analysts frequently need both "current state" and "historical state" queries and you want to avoid a self-join to the current row.

Choosing the right SCD type

Default recommendation: Start with Type 2 for any attribute where there is even a possibility of needing historical analysis. The storage cost is minimal in cloud warehouses. Migrating from Type 1 to Type 2 retroactively is painful because the history is already lost.

Migration: Type 1 to Type 2

When you realize you need history after starting with Type 1:

-- Add tracking columns
ALTER TABLE dim_customer ADD COLUMN effective_date DATE DEFAULT '1900-01-01';
ALTER TABLE dim_customer ADD COLUMN expiry_date DATE DEFAULT '9999-12-31';
ALTER TABLE dim_customer ADD COLUMN is_current BOOLEAN DEFAULT TRUE;

-- Set effective_date to the best available timestamp
UPDATE dim_customer
SET effective_date = COALESCE(last_modified_at, created_at, '1900-01-01');

-- From this point forward, use Type 2 MERGE logic
-- Note: all history before this migration is lost - you only have the current state

This migration preserves only the current snapshot. Any changes that occurred before the migration are permanently lost. This is why defaulting to Type 2 from the start is strongly recommended for analytics-critical attributes.

Snowflake Patterns

Architecture overview

Snowflake separates storage, compute, and services into three independent layers. Storage is managed in a columnar format on cloud object storage. Compute is provided by virtual warehouses (clusters of nodes) that can be resized, suspended, and resumed independently. The services layer handles authentication, metadata, query parsing, and optimization.

This separation means you can scale compute without affecting storage costs and vice versa.

Virtual warehouses

Sizing guidelines

Multi-cluster warehouses

-- Auto-scale for concurrent query load
ALTER WAREHOUSE analytics_wh SET
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

Best practice: Separate warehouses for ETL (etl_wh) and analytics (analytics_wh) to prevent loading jobs from starving interactive queries.

Clustering keys

Snowflake micro-partitions store data in 50-500 MB compressed blocks. Clustering keys control how data is organized within these partitions.

-- Cluster by the most common filter columns (left to right = most selective)
ALTER TABLE fct_sales CLUSTER BY (date_sk, region);

-- Check clustering quality
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_sales');
-- average_depth close to 1 = well clustered
-- average_depth > 5 = needs reclustering

-- Snowflake automatically reclusters in the background (costs credits)
-- Monitor with:
SELECT * FROM TABLE(INFORMATION_SCHEMA.AUTOMATIC_CLUSTERING_HISTORY(
  TABLE_NAME => 'fct_sales',
  DATE_RANGE_START => DATEADD(DAY, -7, CURRENT_DATE())
));

When to cluster: Tables > 1 TB with predictable filter patterns. Do not cluster small tables - the overhead exceeds the benefit.

Time travel and fail-safe

-- Query data as it existed 30 minutes ago
SELECT * FROM fct_sales AT(OFFSET => -1800);

-- Query data as it existed at a specific timestamp
SELECT * FROM fct_sales AT(TIMESTAMP => '2026-03-13 10:00:00'::TIMESTAMP);

-- Restore a dropped table
UNDROP TABLE fct_sales;

-- Set retention period (default 1 day; Enterprise edition: up to 90 days)
ALTER TABLE fct_sales SET DATA_RETENTION_TIME_IN_DAYS = 30;

Time travel storage is billed separately. Set retention to the minimum you need for operational recovery. Fail-safe adds 7 days of additional protection beyond the retention period but is not user-accessible - Snowflake support must restore it.

Zero-copy cloning

-- Clone an entire database for development (instant, no storage cost until divergence)
CREATE DATABASE dev_warehouse CLONE prod_warehouse;

-- Clone a single table
CREATE TABLE fct_sales_test CLONE fct_sales;

-- Clone at a point in time (combines with time travel)
CREATE TABLE fct_sales_snapshot CLONE fct_sales
  AT(TIMESTAMP => '2026-03-13 08:00:00'::TIMESTAMP);

Clones share the underlying micro-partitions. Storage cost only accrues when data in the clone diverges from the source. Use clones for safe testing of schema changes and ETL.

Streams and tasks (CDC pipeline)

Streams capture change data on tables. Tasks schedule SQL execution.

-- Create a stream to track changes on the staging table
CREATE STREAM stg_customers_stream ON TABLE staging_customers;

-- Create a task to process changes every 5 minutes
CREATE TASK scd2_customer_task
  WAREHOUSE = etl_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('stg_customers_stream')
AS
  MERGE INTO dim_customer AS target
  USING stg_customers_stream AS source
    ON target.customer_id = source.customer_id
       AND target.is_current = TRUE
  WHEN MATCHED
    AND (target.segment != source.segment
      OR target.region  != source.region)
  THEN UPDATE SET
    expiry_date = CURRENT_DATE - 1,
    is_current  = FALSE
  WHEN NOT MATCHED THEN INSERT (
    customer_sk, customer_id, name, segment, region,
    effective_date, expiry_date, is_current
  ) VALUES (
    dim_customer_seq.NEXTVAL,
    source.customer_id, source.name, source.segment, source.region,
    CURRENT_DATE, '9999-12-31', TRUE
  );

-- Resume the task (tasks are created in suspended state)
ALTER TASK scd2_customer_task RESUME;

Stages and data loading

-- External stage pointing to S3
CREATE STAGE s3_raw_data
  URL = 's3://my-bucket/raw/'
  STORAGE_INTEGRATION = my_s3_integration
  FILE_FORMAT = (TYPE = 'PARQUET');

-- List files in stage
LIST @s3_raw_data;

-- COPY INTO for bulk loading (most efficient method)
COPY INTO staging_customers
FROM @s3_raw_data/customers/
  FILE_FORMAT = (TYPE = 'PARQUET')
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- Snowpipe for continuous loading (serverless, event-driven)
CREATE PIPE customer_pipe
  AUTO_INGEST = TRUE
AS
COPY INTO staging_customers
FROM @s3_raw_data/customers/
  FILE_FORMAT = (TYPE = 'PARQUET');

Cost optimization checklist

1. Auto-suspend all warehouses at 60 seconds (or 300 for heavy workloads)
2. Use resource monitors to set credit quotas per warehouse per month
3. Cluster only large tables (> 1 TB) with clear filter patterns
4. Minimize time travel retention to what you actually need
5. Use transient tables for staging/temp data (no fail-safe, lower storage cost)
6. Monitor with ACCOUNT_USAGE views: WAREHOUSE_METERING_HISTORY, STORAGE_USAGE, QUERY_HISTORY