The Data Engineer Roadmap

SELECT, JOIN, GROUP BY with HAVING, window functions, CTEs, recursive CTEs, NULL handling, conditional aggregation with FILTER, the difference between COUNT(col) and COUNT(*). You're done when you can write a window-function query that handles ties correctly without thinking about it, and when you can articulate why an INNER JOIN can drop rows.

• ▸Don't read about SQL. Write SQL against a Postgres database and let the wrong answers tell you what you don't know.
• ▸Ship: one analysis project that touches a real dataset and ends with a query you can defend out loud.

Pandas for groupby/merge/pivot, the standard library for file parsing (CSV, JSON, gzipped logs, fields-of-fields), enough OOP to write a class with three methods and not embarrass yourself, and the kind of error handling that distinguishes a script from a job. Skip LeetCode-style algorithms; they don't show up in the rounds you care about.

• ▸If pandas feels slow, you're holding it wrong. Learn vectorization before you learn Polars.
• ▸Ship: a script that ingests a messy CSV, validates it, and writes to a warehouse table.

Star schema, snowflake, the difference, when to denormalize on purpose. Slowly changing dimensions Type 1 versus Type 2 versus Type 6, and which one a real product actually needs. Grain. Always grain. State the grain before you draw the table. The single biggest separator between mid-level and senior data engineers is whether dimensional thinking is automatic or effortful.

• ▸Read Kimball's Data Warehouse Toolkit. There is no shortcut. The book is forty years old and still right.
• ▸Ship: a five-table dimensional model for a product you understand (your gym, a side project, a hobby) with the grain stated for every fact.

Pick one of Snowflake, BigQuery, or Postgres. Learn it past surface depth: query planning, partitioning, clustering, materialized views, the dialect quirks that change which queries are cheap. Going wide across all three is what bootcamps teach. Going deep on one is what gets you hired and lets you contribute on day one.

• ▸BigQuery if you're targeting Google or analytics-heavy startups. Snowflake for most mid-market. Postgres for working knowledge that translates everywhere.

Airflow conceptually, because it's the default. Backfills, retries, idempotency, the difference between a pipeline that works and one that's safe to re-run. Late-arriving data. Schema drift. The phrase 'exactly-once' and why it's almost always actually 'at-least-once with deduplication.' This is the content of the system design round.

• ▸Dagster and Prefect are real. Airflow is what you'll interview on. Learn both eventually; learn Airflow first.
• ▸Ship: a DAG that ingests something on a schedule, handles a deliberate failure, and recovers without manual intervention.

By now you know the material. What you don't have yet is the speed and the calm. Time-box SQL problems at 20 minutes, Python at 30, modeling at 45, design at 60. Do at least three full mock loops out loud before any onsite. The gap between knowing the answer and saying the answer is closed only by reps.

• ▸Use the round-by-round guides on this site for company-specific patterns.
• ▸Ship: an offer. The point of the roadmap is the offer, not the roadmap.