SQL & Data Engineering for ML · 55 min · DuckDB · PostgreSQL · dbdiagram.io
Joins & Relational Modeling
Features live across tables. The join is where correctness is won or lost.
Hiring signal: Can combine tables without fan-out bugs or double counting
What you will learn
- Choose the right join type (INNER, LEFT, RIGHT, FULL)
- Reason about join keys and cardinality to avoid fan-out
- Use CTEs and subqueries to structure complex queries
- Explain normalization vs denormalization tradeoffs for ML
- Read and design a star schema (fact + dimension tables)
The Problem
A single-table query is rare in the real world. Your label is in one table, user attributes in another, and the events you want to aggregate in a third. Combining them is the join — and it's the single most common place ML feature pipelines go silently wrong: a one-to-many join quietly multiplies rows, your SUM doubles, and your model trains on inflated numbers nobody noticed.
This lesson is about joining tables correctly and modeling data so the joins stay safe.
The Concept
Why Joins Are Where ML Pipelines Break
A join matches rows from two tables on a shared key. The concept is simple — but the type of join you choose is a semantic decision that determines which rows survive. This is where ML feature pipelines go silently wrong: a one-to-many join quietly multiplies rows, your SUM doubles, and your model trains on inflated numbers that nobody noticed. Or an INNER JOIN silently drops users with no purchases, and your churn model never sees the users who already churned — the exact ones it needs to learn from.
The rule: always ask "what happens to rows with no match?" before writing a join. The answer determines the join type.
A INNER JOIN B keep only rows that match in BOTH
A LEFT JOIN B keep ALL of A; B columns NULL where no match
A RIGHT JOIN B keep ALL of B; A columns NULL where no match
A FULL JOIN B keep all rows from both sides
"All users and their purchase totals (including users who never purchased)" demands a LEFT join — an INNER join would silently drop the non-purchasers and bias your dataset.
You need a report of ALL users and their purchase counts, including users who never bought anything. Which join do you use?
LEFT JOIN keeps every row from the left table (users) and fills NULL for the right table's columns when there's no match. INNER JOIN would silently drop users with zero purchases, biasing your report.
Unlock the full lesson
You've read the first 2 sections. The rest of this lesson covers Build It, Use It, Ship It, Evaluation, Exercises, Key Terms, Common Pitfalls, Interview Framing — plus a hands-on lab, quiz, and project artifact.
Create a free account to unlock Phase 0 and Phase 1 of every course — no credit card.
Browse all courses · View pricing · DeVenture Academy