Stage 2 of 8 · 9 lessons · ~5 weeks

Analytical SQL and data modelling

Write the analytical SQL pipelines depend on, then design warehouse tables that answer business questions: facts, dimensions, grain, history and layers.

Start: Analytical SQL: CTEs and aggregation

Lessons· one lesson for each topic, in this order

Do this

Solve 20 analytical SQL problems, including at least 8 with window functions.
Write a deduplication query that keeps the latest row per key with a deterministic tie-breaker.
Write the grain of your main fact table in one sentence.
Draw a star schema for your dataset with a date dimension and at least two others.
Implement one Type 2 dimension with valid_from, valid_to and a current flag.
Organise your tables into bronze, silver and gold schemas.

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

Before moving on: A star schema design for your dataset: a written grain statement, a diagram of one fact table and at least three dimensions (one of them Type 2), and SQL that builds them in DuckDB or PostgreSQL with a deduplicated silver layer.

Tauseef's advice

Modelling is the skill that separates a data engineer from someone who moves files around. Always write the grain in one sentence before you create a fact table. If the SQL and databases roadmap is new to you, do its querying and joins stages first; this stage builds on them rather than repeating them.