Roadmap to be a data engineer

A field guide from zero: the fundamentals, a step-by-step learning route, hands-on projects to build, and a series of short lessons where each idea is explained through something that actually goes wrong at a real company.

The fundamentals

Data engineering is the discipline of moving data from where it is created to where it can be trusted and used, reliably, repeatably and at scale. These are the ideas a data engineer works with every day.

§1 Source systems

Data starts in systems the engineer usually does not own, and everything downstream inherits their quirks.

  • Transactional databases (Postgres, MySQL)
  • Event streams and logs (Kafka, pub/sub)
  • Third-party APIs with rate limits and pagination
  • Flat files (CSV, JSON, Parquet) and SaaS tools
  • Change data capture: reading a database’s log

Most data incidents trace back to a source that changed silently.

§2 Storage

Product databases store data by row. Analytical systems store it by column, so summing a billion values reads one column only.

  • Warehouse: schema on write, fast SQL (BigQuery, Snowflake)
  • Lake: schema on read, cheap raw files (S3, GCS)
  • Lakehouse: table formats like Iceberg or Delta on lake files

Warehouse buys structure and speed, a lake buys flexibility, a lakehouse tries to stop choosing.

§3 Ingestion

Getting data from the source into storage.

  • ETL: transform before loading, only clean data lands
  • ELT: load raw, transform inside the warehouse (the modern default)
  • Batch: on a schedule, cheaper and simpler
  • Streaming: within seconds, for fraud or live features

Use batch until the business genuinely needs fresher data than your schedule allows.

§4 Transformation and modelling

Cleaning types, removing duplicates, handling nulls, joining sources and shaping the result for analysts. SQL does most of the work; dbt turns it into tested, versioned software.

  • Normalised (3NF): each fact stored once, for systems that write
  • Star schema: a fact table surrounded by dimensions, for systems that read

§5 Pipelines and orchestration

Dozens of dependent steps run in order every day. The model is a DAG; the tools are Airflow, Dagster or Prefect.

  • Idempotency: re-running gives the same result
  • Backfills: reprocess history safely
  • Retries and alerting with useful context
  • Incremental loads: process only what is new

§6 Data quality and observability

A pipeline that runs is not a pipeline that is right.

  • Tests: not null, unique, sane ranges, row counts
  • Observability: freshness, volume and schema of every table
  • Lineage: which sources feed which dashboards

Accuracy, completeness and timeliness. A wrong number is worse than a late one.

§7 Serving

All of this exists to be used, and each audience wants a different shape.

  • BI and analytics (Looker, Tableau, Power BI, Metabase)
  • Machine learning: history to train on, fresh features to serve
  • Data products inside the app itself
  • Reverse ETL back into CRM and ad tools

§8 The undercurrents

Concerns that cut across every stage, and often where the real risk and cost live.

  • Governance: ownership, definitions, catalogues
  • Security and privacy: access control, GDPR in practice
  • DataOps: Git, CI/CD, separate environments
  • Cost: cloud warehouses bill by bytes scanned

§9 The core toolbox

Depth beats breadth. SQL and one warehouse take you further than a shallow tour of ten tools.

  • SQL: joins, aggregations, window functions, CTEs
  • Python for ingestion and automation
  • One cloud warehouse, hands-on
  • An orchestrator plus dbt
  • Git, the command line, Docker, basic CI/CD
  • Object storage, IAM and Parquet

§10 Words you will hear in a standup

OLTP vs OLAP
Small transactions vs large analytical scans
Idempotent
Safe to re-run without double counting
Partitioning
Splitting a table, usually by date, so queries read less
CDC
Streaming a database’s changes from its log
Data contract
The schema and guarantees a source promises
DAG
The dependency graph of a pipeline
Medallion
Bronze, silver, gold: raw to cleaned to business-ready

The learning route, from zero

Roughly six to nine months at 8 to 10 hours a week. Each stop ends with something you can show, so you always know when to move on.

  1. SQL, properly

    Weeks 1 to 6

    SELECT, filtering, GROUP BY, every kind of JOIN, CTEs, window functions (ROW_NUMBER, LAG, running totals), and reading a query plan. Practise in DuckDB or Postgres on a real public dataset.

    You are ready to move on when you can write a “top 3 products per month with month-over-month change” query without looking anything up.

  2. Python and the command line

    Weeks 5 to 10

    Functions, files, JSON, calling an API with requests, pagination and retries, pandas for small data, virtual environments, Git and GitHub, basic shell.

    Ready when you have a script in a public repo that pulls from an API and writes clean files to disk.

  3. Data modelling and a cloud warehouse

    Weeks 10 to 16

    OLTP vs OLAP, star schemas, facts and dimensions, grain, slowly changing dimensions. Pick one warehouse (BigQuery has a free tier) and learn loading, partitioning, clustering and what a query costs.

    Ready when you can draw a star schema for an online shop and explain the grain of every table.

  4. Transformation with dbt

    Weeks 16 to 20

    Models, sources, refs, tests, documentation, incremental models, snapshots, and staging → intermediate → mart layering.

    Ready when your dbt project builds from scratch and has tests that fail when you break the data on purpose.

  5. Orchestration and Docker

    Weeks 20 to 26

    DAGs in Airflow or Dagster, schedules vs sensors, retries, idempotent tasks, backfills, and running everything locally in Docker Compose.

    Ready when you can delete a day of data and backfill it with one command, getting identical numbers.

  6. Quality, files and the lakehouse

    Weeks 26 to 32

    Data quality tests and freshness checks, Parquet and compression, object storage, Iceberg or Delta, and the basics of Spark for when data outgrows one machine.

    Ready when you can explain why a Parquet file reads faster than the same CSV, and prove it.

  7. Streaming and CDC

    Weeks 32 to 36

    Kafka or Redpanda basics, topics and consumers, at-least-once delivery, deduplication, and change data capture with Debezium.

    Ready when changes in a Postgres table appear in your warehouse within a minute.

  8. Portfolio and job search

    Ongoing from week 20

    Three finished projects on GitHub with clear READMEs and architecture diagrams, a short write-up of one design decision per project, and practice explaining trade-offs out loud.

    Ready when a stranger can clone a repo and run it by following the README.

Hands-on projects

Build these in order. Each one reuses the previous one, so by the end you have a small but complete data platform.

Project 1: an ELT pipeline into a warehouse

Python, an open API (weather, public transport or exchange rates), DuckDB or BigQuery

  1. Write a Python script that pulls the last 30 days from the API with pagination and retries.
  2. Land the raw responses untouched as JSON files, one folder per day.
  3. Load them into a raw table, then write SQL that turns them into one clean, typed table.
  4. Make the load idempotent: running the same day twice must not duplicate rows.
  5. Add three checks: no nulls in the key, no duplicate keys, and the row count is within an expected range.

Show it off with a README, a one-line diagram, and a chart built from the clean table.

Project 2: a batch platform with Airflow and dbt

Docker Compose, Postgres as the source, Airflow or Dagster, dbt, a warehouse, Metabase

  1. Seed a Postgres “shop” database with customers, products and orders, plus a script that adds new orders every few minutes.
  2. Extract new and changed rows incrementally on a schedule into the warehouse’s raw layer.
  3. Build a dbt project with staging, a star schema (fact_orders, dim_customer, dim_product, dim_date) and a revenue mart.
  4. Track customer address changes as a slowly changing dimension using a dbt snapshot.
  5. Orchestrate extract → dbt run → dbt test as one DAG, and stop publishing if a test fails.
  6. Build a dashboard on the mart and backfill one past month to prove the numbers do not change.

Show it off with the DAG screenshot, the dbt lineage graph, and the dashboard.

Project 3: a small streaming pipeline with CDC

Postgres, Debezium, Kafka or Redpanda, a Python consumer, the warehouse from Project 2

  1. Turn on change data capture for the orders table so every insert, update and delete becomes an event.
  2. Write a consumer that deduplicates events and writes them to the warehouse every minute.
  3. Build a “last 15 minutes” live tile next to the nightly batch numbers.
  4. Kill the consumer for ten minutes, restart it, and confirm no events were lost or doubled.
  5. Write down the end-to-end latency and what it costs compared with the batch version.

Show it off with a short screen recording of a row changing in Postgres and appearing on the dashboard.

Project 4: make it production-grade

GitHub Actions, dbt tests, Parquet, object storage, your existing projects

  1. Add CI that runs dbt build and tests on every pull request.
  2. Export the history to Parquet in object storage, partitioned by date, and compare size and query speed with CSV.
  3. Add freshness checks and an alert to Slack or email.
  4. Mask personal data columns and write down who can see what.
  5. Delete one customer completely (a GDPR request) and prove they are gone from every table.

Show it off with a one-page write-up of what would break first at 100× the data, and why.

The lessons

Short lessons, each built around one real-life scene at an online second-hand marketplace: something breaks, someone asks why, and the lesson walks through the mechanism with charts and a hands-on exercise. Read them in order; later lessons build on earlier ones.

Part one: the foundations

How data is stored, moved, modelled and kept honest.

  1. 01The Monday Morning QueryOLTP vs OLAPAn analytics query slows down checkout. Why warehouses store data by column.
  2. 02The Rows We Threw AwayETL vs ELTWhy keeping the raw data lets you fix yesterday’s logic tomorrow.
  3. 03Ghost ListingsBatch vs streamingHow fresh data really needs to be, and what each minute of freshness costs.
  4. 04The Payout That Ran TwiceIdempotencyA retry pays sellers twice. Safe re-runs and backfills.
  5. 05Five Answers, One QuestionStar schemaFive analysts, five numbers. Grain, fan-out and dimensional modelling.
  6. 06The Flatline WeekendData qualityA dashboard goes flat and nobody notices. Tests, freshness and alert fatigue.
  7. 07The Green BoardOrchestrationEvery task succeeded and the data was still wrong. DAGs and check gates.
  8. 08The Clause Nobody WroteData contractsA source changes meaning without changing shape. Schema drift and contracts.
  9. 09The Report That Changed Its MindSCD Type 2Last June’s report changes overnight. Keeping history in dimensions.
  10. 10The Bill Nobody ReadCost and partitioningOne dashboard tile was three quarters of the warehouse bill.

Part two: building reliable pipelines

Layering, change capture, privacy, CI and the formats underneath.

  1. 11Cleaned Eight TimesMedallionEight reports clean the same data eight ways. Bronze, silver and gold.
  2. 12Between Two PollsCDCWhat a polling ingest silently misses, and why reading the log fixes it.
  3. 13Return To SenderReverse ETLA customer list synced to a CRM drifts further from the truth every day.
  4. 14The Deletion That Came BackGDPR erasureA deleted customer reappears after the next full refresh.
  5. 15All Checks Passeddbt and CIA one-line refactor inflates revenue and CI stays green.
  6. 16Smaller and SlowerFile formatsGzipped CSV saves storage and makes every query slower. Parquet and codecs.
  7. 17Both Copies Were ThereLakehouseA delete job makes a total go up. Why table formats like Iceberg exist.
  8. 18One Task Left RunningSkew and shuffleOne big seller turns a nine-minute job into four hours.
  9. 19Five People Called Anna MeierEntity resolutionDuplicate customers make retention look a third worse than it is.
  10. 20Three ThermometersSemantic layerThree teams, three correct GMV numbers. Defining a metric once.

Part three: the platform at scale

Lineage, incremental loads, access, caching, reliability targets and shared capacity.

  1. 21Nobody Referenced That ColumnLineageA careful column change breaks a report six steps away.
  2. 22The Mark the Tide LeftIncremental loadsA watermark quietly drops rows from long transactions.
  3. 23Every Door Was LockedPII maskingFour people can see names, yet ninety-six can identify a customer.
  4. 24Six Copies, One QuestionBI cachingFour people, three numbers for the same week, all correct.
  5. 25The Schedule of ExclusionsSLOsJobs at 99.6% uptime while one answer in eight is wrong.
  6. 26The Late FinalLate dataThe same query gives a different number on Monday and Friday.
  7. 27The Busy HourWorkload managementA query takes 8 seconds at 08:41 and 4 minutes at 10:06.

Part four: running it as a business

Ownership, time, recovery and proving the platform works.

  1. 28Who Owns This Table?OwnershipA seven-hour fix takes twenty days because nobody owns the table.
  2. 29Tomorrow’s AlmanacPoint-in-timeA model looks brilliant in testing because it can see the future.
  3. 30The Second CopyDisaster recoveryThe first real rebuild test, and the seven-day limit nobody chose.
  4. 31The Taxing MasterTesting the tests391 tests, and a €825m bug none of them catch.
  5. 32Register of TitleBitemporalAn auditor asks to reproduce a board figure, and nobody can.
  6. 33The Exercise CalendarGame daysWhy one big recovery drill a year proves almost nothing.

Part five: change and accountability

Moving platforms safely, and proving every question was allowed to be asked.

  1. 34The Same Sentence, TwiceWarehouse migrationEvery model translates cleanly, and the numbers still change. What a migration really costs.
  2. 35Who Is Allowed To Ask?Purpose and consentEvery query was authorised, and none recorded why. Purpose checked at query time.

New lessons are added regularly. Start with the fundamentals, follow the route, build the projects, and read one lesson whenever you finish a stop on the route.

These lessons were written with the help of AI (Claude) and reviewed and edited by me.

Back to top