Warehouse Performance Tuning

Why Warehouse Performance Tuning matters for Analytics Engineers

Performance tuning turns slow, expensive queries into fast, predictable pipelines and dashboards. As an Analytics Engineer, you model data for analysts and BI tools. Good tuning reduces compute costs, speeds up development, and keeps dashboards responsive under load.

• Ship incremental models that finish reliably within SLAs.
• Keep BI dashboards snappy for dozens or hundreds of concurrent users.
• Control spend by reducing scans, shuffles, and unnecessary reprocessing.

What you will be able to do

• Pick the right partitioning and clustering strategy for large tables.
• Choose materializations that balance freshness, speed, and cost.
• Reduce scan and shuffle costs with selective filters and better join design.
• Build incremental models that prune work and scale to billions of rows.
• Design stable join keys and manage wide tables for BI workloads.
• Leverage caching and result reuse safely.
• Monitor runtime and cost trends and act on regressions.
• Prepare data structures that support high BI concurrency.

Who this is for

• Analytics Engineers building dbt or SQL pipelines for BI and analytics.
• Data/BI Engineers maintaining warehouses and dashboards.
• Analysts who own heavy queries and want to improve speed and cost.

Prerequisites

• Comfortable with SQL SELECT, JOIN, GROUP BY, and basic window functions.
• Basic understanding of star schemas and dimensional modeling.
• Familiarity with your warehouse's concepts (partitions, stages, query history).
• Optional: basic dbt experience for materializations and incremental models.

Learning path

1. Start with partitions and clustering. Choose keys based on time and query patterns. Validate with query history.
2. Materializations. When to use views vs tables vs incremental; set freshness expectations.
3. Scan and shuffle control. Filter early, avoid SELECT *, reduce skew, simplify joins.
4. Incremental and pruning. Process only changed partitions; validate MERGE logic and idempotency.
5. Join keys and wide tables. Conform datatypes, consider surrogate keys, pre-aggregate for BI.
6. Caching and reuse. Persist hot intermediate results, leverage result-cache safely.
7. Monitoring. Track query time, bytes scanned, and costs; alert on regressions.
8. BI concurrency. Build aggregate tables; separate ETL and BI compute where possible.

Worked examples

1) Partition and cluster a large events table

Goal: Serve queries like "last 7 days, filter by user_id, event_name" with minimal scan.

-- Example (BigQuery-style DDL)
CREATE TABLE analytics.events_partitioned
PARTITION BY DATE(event_ts)
CLUSTER BY user_id, event_name AS
SELECT * FROM raw.events;

-- Query pattern that benefits
SELECT user_id, COUNT(*) AS cnt
FROM analytics.events_partitioned
WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND event_name = 'purchase'
GROUP BY user_id;

Tip: Choose a low-to-medium cardinality cluster key that appears in filters or joins (e.g., user_id, account_id).

2) Use dbt incremental materialization with pruning

Goal: Only process new partitions and upserts for changed rows.

-- dbt model: models/fct_orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='sync_all_columns',
    partition_by={'field': 'order_date', 'data_type': 'date'},
    cluster_by=['customer_id']
) }}

WITH src AS (
  SELECT * FROM {{ ref('stg_orders') }}
  {% if is_incremental() %}
    WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
  {% endif %}
)

SELECT * FROM src;

This prunes work during incremental runs to recent partitions while still allowing backfills when needed.

3) Reduce scan and shuffle by filtering early and selecting narrow columns

-- Before (scans entire table, joins before filtering, selects *)
SELECT *
FROM fact_events e
JOIN dim_users u ON e.user_id = u.user_id
WHERE DATE(e.event_ts) = CURRENT_DATE();

-- After (filter first, select only needed columns)
WITH filtered AS (
  SELECT user_id, event_ts
  FROM fact_events
  WHERE DATE(event_ts) = CURRENT_DATE()
)
SELECT f.user_id, u.region
FROM filtered f
JOIN dim_users u USING (user_id);

Benefits: Less data scanned, fewer columns shuffled, faster joins.

4) Stabilize joins with clean keys and pre-aggregation

-- Normalize types and trim keys
WITH d AS (
  SELECT CAST(user_id AS STRING) AS user_id, region FROM dim_users
),
 f AS (
  SELECT CAST(user_id AS STRING) AS user_id, DATE(event_ts) AS d, COUNT(*) AS cnt
  FROM fact_events
  WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY 1,2
)
SELECT f.d, d.region, SUM(f.cnt) AS events
FROM f
JOIN d USING (user_id)
GROUP BY 1,2;

By pre-aggregating the fact table and matching datatypes, the join is smaller and avoids skew.

5) BI aggregate table for concurrency

-- Create a daily aggregate for dashboards
CREATE OR REPLACE TABLE mart.sales_daily AS
SELECT order_date, product_id, region_id,
       SUM(net_revenue) AS revenue,
       COUNTIF(status = 'completed') AS completed_orders
FROM fct_orders
GROUP BY 1,2,3;

-- Dashboard queries hit mart.sales_daily instead of raw orders

When many users run the same dashboard, pre-aggregates reduce contention and query time.

6) Monitor query runtime and bytes scanned

-- Example (BigQuery): top costly jobs last 7 days
SELECT user_email, total_bytes_processed, total_slot_ms, statement_type, query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY total_bytes_processed DESC
LIMIT 20;

-- Example (Snowflake): long-running queries
SELECT user_name, total_elapsed_time, rows_scanned, query_text
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  DATEADD('day', -7, CURRENT_TIMESTAMP()), CURRENT_TIMESTAMP()))
ORDER BY total_elapsed_time DESC
LIMIT 20;

Drills and exercises

• Pick a 500M+ row table. Add/verify partitioning on a time column and one clustering key.
• Convert one heavy view to an incremental materialized model with pruning.
• Rewrite a dashboard query to select only needed columns and filter before joins.
• Create a daily aggregate table used by at least one dashboard card.
• Add a query history check that flags a 30%+ runtime increase week-over-week.

Common mistakes and debugging tips

• High-cardinality clustering. Too many distinct values reduces clustering benefit. Tip: Choose 1–3 medium-cardinality keys used in filters/joins.
• SELECT * in BI queries. This increases scans and shuffles. Tip: Project only used columns.
• Missing partition filters. Queries scan full tables. Tip: Always include partition predicates.
• Type-mismatched join keys. Causes implicit casts and prevents partition pruning. Tip: Conform types in staging models.
• Overusing result cache. Cached answers can hide regressions or serve stale results. Tip: Cache for exploratory analytics; persist aggregates for production dashboards.
• Incremental bugs. Incorrect unique_key or predicates skip updates. Tip: Test merges on a small backfill window and validate row counts vs source.
• Single giant model powering all dashboards. Tip: Split into subject-area aggregates (daily, weekly) and expose narrow, denormalized tables for BI.

Mini project: Speed up a slow revenue dashboard

1. Identify the dashboard's heaviest query from query history. Capture runtime and scanned bytes as a baseline.
2. Create a partitioned, clustered fact_orders table aligned to order_date and customer_id.
3. Build an incremental dbt model with unique_key=order_id and pruning to the last 7 days.
4. Add a mart.sales_daily aggregate table (order_date, region_id, product_id) with revenue and order counts.
5. Point the dashboard to mart.sales_daily. Remove SELECT * and unused joins.
6. Measure again. Aim for 50%+ less runtime and scan. Document changes and results in a short readme.

Optimizing for BI concurrency

• Precompute aggregates for common date grains (daily/weekly/monthly).
• Separate ETL and BI compute where supported to avoid resource contention.
• Favor narrow, denormalized query surfaces with predictable filters.
• Warm caches before peak hours by running lightweight priming queries on hot aggregates.
• Avoid cross-joins and skewed joins; pre-aggregate and standardize keys.

Next steps

• Work through each subskill below and apply changes to a real model.
• Complete the skill exam to check your understanding. Anyone can take it; saved progress requires login.
• Plan a 2-week optimization sprint: baseline, tune, and document results with before/after metrics.