Data Warehousing
Intermediate 14 min read Data Warehousing

Warehouse Performance

The cheapest byte is the one you never scan

Warehouse performance is decided by scan reduction: partition pruning, clustering/sort order, columnar projection, and caching. Concurrency costs slots and credits, so the tuning loop is: measure scanned bytes, then re-model.

pruningclusteringprojectioncachingslotsconcurrency
Prerequisites: Snowflake Platform BigQuery
Before we start

What you will learn

  • Rank the levers: partition, cluster/sort, projection, caching
  • Read EXPLAIN to see pruned vs scanned ranges
  • Tune concurrency alongside cost
Introduction

What is this?

Columnar warehouses make the number of bytes scanned the dominating factor in both time and cost. Performance engineering is mostly about reducing those bytes through partitioning by time, clustering/sorting by other predicate columns, projecting only needed columns, and exploiting caching — then scaling compute for the unavoidable scans.

Motivation

Why do you need it?

  • The same 5 levers apply across Snowflake, BigQuery, Redshift, and even DuckDB locally.
  • Most 'slow warehouse queries' are 20% engine and 80% schema/query shape.
  • Concurrency is a slot/credit budget — your performance knob is modeling, not just compute.
Think about it

Mental model

Imagine

Choosing the right page of a catalog from the index

Think of

Partition + cluster pruning

The catalog's index (metadata) tells you the page (partition) and the order of listings (cluster) lets you skip sections; reading fewer pages makes both speed and cost better.

Mechanics

How it works

  • Partition pruning: metadata excludes whole partitions when a time/natural-key filter applies.
  • Clustering/sort orders rows so block-level pruning trims ranges even within a partition.
  • Projection: columnar stores scan only referenced columns — SELECT * defeats projection.
  • Caching: identical results within the cache window skip re-scan; cache fills per query pattern.
  • Concurrency: active queries share slots; contention shows as queued slots, not 'bigger queries needed'.

The scan-reduction lever stack.

Query predicate
Partition
Cluster/Sort
Column projection
Result
Hands-on

Examples

Before and after model-aware queries

Before and after model-aware queries
-- BAD: scans everything
SELECT * FROM fact_event WHERE provider = 'clickstream';
 
-- BETTER: partition pruning + projection
SELECT user_id, count(*) FROM fact_event
WHERE day = '2026-01-05' AND provider = 'clickstream'
GROUP BY user_id;
 
-- Explain shows the pruned ranges:
EXPLAIN SELECT ... -- look for partition pruning steps / estimated bytes

Same answer, tiny fraction of the bytes — the whole reduction stack was exercised.

Real world

In production

  • A funnel dashboard always filters day + country, so the team partitions by day and clusters by country.
  • A cost review flags any model whose 'bytes scanned / total table' ratio exceeds 5% on hot queries.
  • An analyst habit of 'SELECT * LIMIT 1000' is replaced by explicit column lists to stop full-block scans.
Guidelines

Best practices

  • Design filters around the partition column (usually a date) in every hot query.
  • Cluster/sort by the next highest-cardinality predicate that shows up after the partition key.
  • Ban SELECT * in BI and notebook layers; projection is free but only if you ask.
  • Watch cache hit ratio; a pattern that never hits cache is paying for re-scan every time.
Watch out

Common mistakes

  • Filtering on a derived expression (DATE_TRUNC(day, ts)) that defeats partition pruning.
  • Partitioning or clustering on low-cardinality attributes, then wondering why nothing prunes.
  • Scaling compute (more slots) before fixing a 100x scan-reduction opportunity in the model.
When things break

Debugging tips

  • Check the 'bytes scanned' metric in the query profile; a big number is the smoking gun for pruning failure.
  • Inspect EXPLAIN for a full-table scan reference even when a date predicate is present.
  • Profile an identical filtered query; if it returns to full cost, the cache expired or the filter changed.
Interviews

Interview checkpoint

Practice

Hands-on lab

Prove Scan Reduction Locally
35 min · intermediate
In progress
Objectives
  • Compare full-scan vs pruned query cost in DuckDB
  • Observe the effect of partitioning/clustering on scanned rows
  • Measure projection savings with EXPLAIN

Prove Scan Reduction Locally

35 min · intermediate

0/6 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which habit most reduces warehouse query cost?

2

Filtering on DATE_TRUNC(day, ts) defeats partition pruning when the table is partitioned by ts (a day).

Go deeper

Official documentation

Snowflake performance guide
docs
BigQuery optimize cost & performance
docs
Redshift tuning tables
docs