Stage 2 · Lesson 3 of 9

SQL performance at scale

In a warehouse, cost and speed depend mainly on how much data a query reads. Most warehouses use : each column is stored separately. So they read only the columns you ask for, and SELECT * on a wide table is expensive.

They can also skip whole chunks of data using partitions and or sort keys. But they only skip when you filter on those columns directly. Filter early.

Add things up before joining when you can. Do not wrap in functions inside a WHERE clause, because that stops the skipping. Before and after a change, read the or the bytes scanned figure your warehouse shows.

On BigQuery or Athena you pay per byte scanned. On warehouses with fixed capacity, you pay in time and capacity.

Common traptesting on a small sample and discovering the real cost only when a scans terabytes every hour.

A question to check yourself, an AI practice session and 2 cheat sheet entries come with the full roadmap. Unlock

Resources for this stage

Start with

CourseFree
SQL Tutorial for Data Analysis (formerly Mode)
ThoughtSpot · Beginner
Its advanced section covers window functions, performance tuning and pivoting on realistic data

Practise

LeetCode SQL 50
Fifty graded problems; the window function and aggregation ones match what pipeline SQL needs
Select Star SQL
An interactive book on one real dataset that ends with window functions and self joins

Keep handy

Kimball Dimensional Modeling Techniques
The original definitions of grain, conformed dimensions and SCD types, straight from the source
roadmap.sh: SQL
Check which SQL topics you still lack before moving on

Also worth your time

Data Warehouse Design: Schemas, Steps, and Best Practices (opens in a new tab)Read
Star and snowflake schemas, facts and dimensions, and the steps of a warehouse design in one read