SQL
Advanced 16 min read SQL

Analytical Query Performance

Fast dashboards live in the query and schema, not the cache

Analytical query performance is governed by data volume, query shape and warehouse cost. Partitioning, clustering, pre-aggregation and filter pushdown make dashboards fast without piling on cache.

performancepartitioningclusteringpre-aggregationEXPLAINcachecost
Prerequisites: Time-Series SQL Query Performance
Before we start

What you will learn

  • Identify the top levers: scan size, partition pruning, clustering
  • Read EXPLAIN to locate the expensive stage
  • Design pre-aggregations and caches that stay correct
Introduction

What is this?

Analytical query performance means answering a dashboard in acceptable time at acceptable cost. The levers are structural: query only needed data (pruning), keep sortable keys clustered, pre-aggregate hot grains, and move filters into the engine. Caching masks problems; query and schema design cure them.

Motivation

Why do you need it?

  • A slow dashboard is a bad product; a costly query is a growing bill.
  • Most slowness is wasted scan — touching partitions or columns you don't need.
  • Structural fixes scale; cache only hides repeat queries.
  • Cost controls (warehouse credits) tie directly to query design.
Think about it

Mental model

Imagine

A library with a card index organized by shelf

Think of

Partitioning + clustering

You don't wander 40 aisles to find one book — the index plus shelf layout gets you there directly. Partitioning is the aisle signs; clustering is that every genre is logically on the same shelves.

Mechanics

How it works

  • Partitioning splits storage by a column (usually date), letting queries prune whole partitions via WHERE.
  • Clustering co-locates similar values (e.g., customer_id) so scans read fewer blocks.
  • Pre-aggregation stores hot grains (daily) so reports skip the raw scan.
  • Filter pushdown sends WHERE into the scan/partition pruning before any join or group.
  • EXPLAIN shows the plan's measured steps — scan size, join strategies, shuffles.
  • Caches (result, warehouse) serve repeated identical queries cheaply.

The levers that make a query cheaper.

WHERE on partition key
Clustered sort key
Pre-aggregated grain
Engine execution
Cache
Real world

In production

  • A warehouse tables partitioned by day and clustered by store; the daily store report prunes to one day.
  • A pre-aggregated daily-sales table feeds the exec dashboard so it never scans fact-truth rows.
  • A BI pipeline runs EXPLAIN on new queries in CI and fails if estimated scan exceeds budget.
Guidelines

Best practices

  • Partition by the dominant filter (usually date) and cluster by the next hot key.
  • Pre-aggregate what's consumed repeatedly; keep the raw truth for reconciliation.
  • Push filters and aggregations to the engine — never pull wide rows to the BI tool.
  • Monitor cost per report; a report that costs 10x its peers is a design smell.
Watch out

Common mistakes

  • SELECT * over a wide table then filtering in the BI tool.
  • Partitioning by a low-cardinality key that users don't filter on.
  • Pre-aggregating without an owner, so multiple overlapping aggregates drift.
  • Buying more cache instead of admitting the query is badly shaped.
Root cause analysis

Common issues

Error
Dashboard is fast warm but slow at 8am
Root cause

Cold cache + a wide scan the cluster can't prune

Fix

Partition/cluster or pre-aggregate; make the query cheap raw, not fast warm.

Error
Two dashboards scan the same 200GB daily
Root cause

Shared fact queried at raw grain by each

Fix

Provide the pre-aggregated grain both truly need.

When things break

Debugging tips

  • EXPLAIN first: a big scan before a small join tells you where to shave.
  • Check bytes/rows scanned versus returned — the gap is wasted work.
  • A fast dashboard after warm-up but slow cold means cache dependency, not query quality.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

The fastest structural fix for a slow date-filtered query is usually:

2

A dashboard that is fast only after being queried once is well-optimized.

Go deeper

Official documentation

BigQuery — partitioning & clustering
docs
Snowflake — clustering keys
docs