Data engineer
Advanced SQL, data modelling, ETL/ELT pipelines, cloud warehouses, data quality and infrastructure: building the foundations that analytics and AI stand on.
Sign in to track how many of the 34 roadmap tasks you have done.
For programmers and analysts who want to build data infrastructure: pipelines, warehouses and models that analytics, reporting and AI draw from. The roadmap leads from relational design, advanced SQL and data modelling through ETL/ELT, cloud warehouses and streaming to data quality, infrastructure as code and governance. By the end you will design and operate a data platform whose output can be trusted and which survives your holiday.
Steps
Step 1: Relational design and ERD
Entities, relationships and cardinality in an ERD, primary and foreign keys, breaking an M:N relationship down through a junction table, and normalisation to 3NF. Without a sound relational design every warehouse and pipeline is built on sand: duplicates, inconsistent data and queries nobody can write. You are done when, from a verbal brief, you draw an ERD, turn it into tables with keys and justify why each one is in 3NF.
Tasks
Resources
Step 2: Advanced SQL
Window functions, CTEs, recursive queries, EXPLAIN and indexes. A data engineer must not only write SQL but also understand why a query is slow and how the database executes it. In a modern warehouse most transformations are SQL in the end. You are done when you write a query with window functions over millions of rows, read its execution plan and speed it up with a suitable index or a rewrite.
Tasks
Resources
Step 3: Data modelling
external resourcesThe star schema with facts and dimensions, slowly changing dimensions, normalisation vs denormalisation and warehouse layers (L0 raw / L1 cleaned / L2 business, elsewhere called bronze, silver, gold). A good model means an analyst writes a query in a minute and the result is consistent for everyone. You are done when, from an operational database, you design a star schema for one business area and justify every dimension.
Resources
Step 4: ETL/ELT and orchestration
external resourcesData has to be moved from sources into the warehouse, transformed and refreshed regularly: reliably, idempotently and with the possibility of backfilling. An orchestrator (Airflow, Dagster) manages dependencies and scheduling, and dbt standardises transformations in SQL. You are done when you have a pipeline that downloads data from an API daily, stores it raw, transforms it with dbt and alerts you on failure.
Resources
Step 5: Cloud data warehouse: BigQuery, Snowflake
external resourcesModern warehouses separate storage from compute, scale to terabytes and charge by usage. This changes how you design tables (in BigQuery partitions and clustering, in Snowflake clustering keys) and how you write queries (BigQuery bills by the volume of data scanned, Snowflake by the credits of the running warehouse). You are done when you load a dataset into BigQuery or Snowflake, design a table so that a query reads only the data it needs, and can estimate its cost before you run it.
Resources
Step 6: Big data processing: Spark, Parquet, DuckDB
optionalexternal resourcesWhen data does not fit into the memory of one machine, distributed processing (Spark) and columnar formats (Parquet) come in; on the other hand, DuckDB shows that many "big data" tasks can be handled by a single laptop. You are done when you process the same dataset in pandas, DuckDB and Spark and can say when each is worth it.
Resources
Step 7: Streaming basics
optionalexternal resourcesSome data cannot wait for the nightly run: events from an application, sensors, payments. Kafka and stream processing bring their own terms: topic, partition, offset, exactly-once, late events. You are done when you build a simple producer and consumer on Kafka and explain what happens to an event when the consumer crashes in the middle of processing.
Resources
Step 8: Data quality and testing
A pipeline that runs is not yet a pipeline that produces correct data. Schema, uniqueness and referential integrity tests, checks of distributions and outliers, freshness monitoring, and descriptive statistics as a profiling tool. You are done when every model in the warehouse has tests, a change in the distribution of values triggers an alarm, and you can say within an hour where a faulty value came from.
Topics on the portal
Resources
Step 9: Infrastructure as code and CI/CD
external resourcesThe warehouse, the pipelines and the permissions should all be defined in code, versioned and deployed automatically, otherwise they are irreproducible and dependent on one person. Terraform, containers, GitHub Actions and dev/stage/prod environments. You are done when a change to a dbt model goes through a pull request, CI runs the tests and deployment to production happens without manual clicking.
Resources
Step 10: Governance and security
Who may see which data, where it comes from (lineage), how long it is retained and how personal data is anonymised under GDPR decide whether a company can use the warehouse at all. You are done when you have a catalogue with table owners, role-based access control, sensitive columns marked and masked, and you can answer a request for erasure of personal data.
Tasks
Resources