SQL & Data Engineering for ML · 55 min · SQL · dbt · Airflow

Feature Engineering, Data Quality & Pipelines

Garbage in, garbage model. The pipeline that builds your features is part of the model.

Hiring signal: Owns the data layer: point-in-time correct features and quality checks

What you will learn

Assemble a model-ready feature table from raw event tables
Guarantee point-in-time correctness and prevent leakage
Write data-quality checks for nulls, duplicates, ranges, and drift
Distinguish batch from streaming pipelines and when each fits
Explain the role of dbt and Airflow in a production data stack

The Problem

You can write perfect SQL and still ship a broken model — because the table you trained on was assembled with a future leak, a silent duplicate, or a column that drifted last Tuesday. In production ML, the feature pipeline is not plumbing you can ignore; it is part of the model's correctness. Most ML take-homes and on-the-job incidents are data problems, not modeling problems.

This lesson connects SQL skills to the discipline around them: building a model-ready table, keeping it point-in-time correct, checking its quality, and understanding the pipelines that run it on a schedule.

The Concept

The Feature Table: Where ML Meets Data Engineering

The goal of feature engineering is a feature table: one row per (entity, as-of time), every column a feature known at that time, plus the label. This table is the contract between data engineering and ML — it's what the model trains on, and it must be identical to what the model sees at serving time.

user_id | as_of_date | spend_30d | purchases_30d | days_since_last | ... | label_churn_next_30d

Two properties make this table trustworthy:

Point-in-time correct: every feature uses only data available at as_of_date. If your feature is "spend in the last 7 days" and as_of_date is June 1, the window must end on June 1 — not June 3. Including future data is called data leakage, and it's the most common cause of models that look great offline but fail in production. The model learns from information it won't have at prediction time, so offline metrics are inflated and the model is overconfident.
Reproducible: re-running the pipeline on the same inputs yields the same table. This means no NOW() or CURRENT_DATE() in feature SQL — use the as_of_date parameter instead. Non-reproducible pipelines make debugging impossible because you can't recreate the exact data the model trained on.

If either property fails, your offline metrics lie — the model appears to perform well, but it's learning from information that won't exist at prediction time.

Your feature table has as_of_date = '2024-06-01'. One feature is spend_last_7d which sums purchases from May 25 to June 3. Is this point-in-time correct?

Point-in-time correctness means every feature value is computable using only data available at as_of_date. A 7-day window ending June 1 should sum May 26–June 1, not May 25–June 3. Including June 2-3 leaks future information, inflating offline metrics.

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.

Key terms in this lesson

Streaming