Week 9 - SQL for Analytics

Welcome to Week 9! This week shifts focus from Python pipelines to the SQL layer. You will learn how to write analytical queries, validate ingested data with SQL, and understand the architecture of modern data warehouses. By the end of the week, you will deliver a fact and dimension view in Azure PostgreSQL.

Dataset

This week uses a curated subset of the NYC Taxi & Limousine Commission (TLC) Trip Record Data. The dataset includes:

Trips (fact data): ~57K rows of green taxi trips for January 2024 (pickup/dropoff times, locations, fares, payment types)
Zones (dimension data): 265 rows mapping location IDs to borough and zone names (taxi_zone_lookup.csv)

Trip data is available as monthly Parquet files at:

https://d37ci6vzurychx.cloudfront.net/trip-data/green*tripdata*YYYY-MM.parquet

A loading script will be provided to load a curated subset into your Azure PostgreSQL database. This same dataset continues into Week 10 (dbt) and Week 11 (dashboarding).

Learning goals

Write complex SQL queries using joins, CTEs, and aggregations to answer analytical questions
Build validation queries that detect duplicates, nulls, and row count mismatches in ingested data
Distinguish between OLAP (data warehouse) and OLTP (transactional database) workloads and choose the right tool
Describe the architecture of modern data warehouses such as Snowflake and Databricks
Apply data modeling concepts including raw/staging/mart layers, grain, keys, facts, and dimensions
Avoid common SQL performance pitfalls such as accidental cartesian joins
Deliver a fact view and a dimension view in Azure PostgreSQL

Chapters

SQL for Analytics
Joins, CTEs, and Aggregations
Data Validation Queries
OLAP, OLTP & Warehouses
Data Modeling Concepts
Building SQL Views
Practice
Assignment
Gotchas & Pitfalls

Week 9 - SQL for Analytics

Week 9 - SQL for Analytics

Dataset

Learning goals

Chapters

Supplementary