SQL, Data Modeling and Data Warehousing

Structure complex queries so they stay readable, correct, and easy to debug.

• Common table expressions and when to chain them
• Join types, join fan-out, and duplicate row pitfalls
• Semi-joins and anti-joins with EXISTS and NOT EXISTS
• Set operations: UNION, INTERSECT, and EXCEPT
• Conditional aggregation with CASE and FILTER
• Handling NULLs correctly in comparisons and aggregates

Hands-on lab: Rewrite a nested, hard-to-read reporting query as a chain of CTEs, then find and fix a join that silently doubles revenue.

Use window functions to answer ranking, trend, and sessionization questions without self-joins.

• PARTITION BY, ORDER BY, and frame clauses (ROWS versus RANGE)
• ROW_NUMBER, RANK, and DENSE_RANK for top-N and deduplication
• LAG and LEAD for period-over-period change
• Running totals and moving averages
• NTILE and percentile calculations
• Gaps-and-islands patterns for streaks and sessions

Hands-on lab: From a raw events table, compute daily active users, a 7-day moving average, and each user's first and most recent action using window functions.

Diagnose why a query is slow and apply the right fix for the engine you are on.

• Reading EXPLAIN and EXPLAIN ANALYZE output
• B-tree indexes, composite indexes, and covering indexes
• Sargable predicates and avoiding functions on indexed columns
• Join algorithms: nested loop, hash, and merge
• Table statistics and why stale statistics mislead the planner
• Partitioning and pruning basics

Hands-on lab: Take a slow query on a multi-million-row Postgres table, capture its plan, apply an index and a rewrite, and compare plans and timings before and after.

Turn business processes into fact and dimension tables that are simple to query and hard to misuse.

• Business process, grain, dimensions, and facts: the four design steps
• Transaction, periodic snapshot, and accumulating snapshot fact tables
• Surrogate keys versus natural keys
• Conformed dimensions and the bus matrix
• Slowly changing dimensions: Type 1, Type 2, and Type 3
• Star versus snowflake schema and when normalizing dimensions helps

Hands-on lab: Design a star schema for an order-to-delivery process on paper, then create the tables in Postgres and load a Type 2 customer dimension from sample change data.

Understand how columnar, distributed warehouses differ from row-store databases, and how that changes design and cost.

• Row stores versus columnar storage and compression
• Separation of storage and compute in Snowflake and BigQuery
• Redshift distribution styles and sort keys
• Partitioning and clustering in BigQuery and Snowflake
• Cost models: per-query scan, warehouse time, and provisioned nodes
• When Postgres is enough and when to move to a dedicated warehouse

Hands-on lab: Load the same dataset into Postgres and a cloud warehouse trial or free tier, run an identical query on both, and write a short comparison of plans, scanned bytes, and likely cost drivers.

Manage warehouse transformations as versioned, modular, documented code.

• dbt project structure: sources, staging, intermediate, and mart layers
• Models, ref(), and the dependency graph
• Materializations: view, table, and incremental
• Jinja basics and reusable macros
• Seeds and snapshots for reference data and history
• Generating documentation and lineage graphs

Hands-on lab: Build a dbt project that stages raw tables, produces a fact table and two dimensions, and renders the generated documentation site.

Catch bad data before stakeholders do, and ship models with a repeatable workflow.

• Generic dbt tests: unique, not_null, accepted_values, and relationships
• Singular tests for custom business rules
• Source freshness checks
• Severity levels and deciding what should fail a run
• Using the dbt-utils and dbt-expectations packages
• Running dbt in CI with Git pull requests

Hands-on lab: Add a full test suite to your dbt project, introduce bad rows into the source data on purpose, and confirm each failure is caught with a clear message.