Data engineer roadmap
Every stage in order, from SQL through storage engines, batch and streaming pipelines and the warehouse, to feature stores and model serving. It is the broadest of the three paths and covers each layer data passes through.
Core and recommended topics. Optional ones stay hidden until you have some real work behind you.
- Core: needed for most jobs of this kind
- Recommended: worth learning early
- Optional: learn it when your work needs it
Stage 1
Data foundations and SQL
Most data systems are built on relational ideas, including many that describe themselves as non-relational. Before Spark or feature stores, learn SQL well and understand how a database turns a query into a plan of scans, joins and aggregations.
Core: SQL fundamentals
SELECT, WHERE, GROUP BY and HAVING. A query does not run in the order it is written: FROM and JOIN are evaluated first and SELECT near the end, which explains a lot of confusing errors.
Outside linkPostgreSQL: SQL language SimulatorSQL query execution Outside linkMode SQL tutorial Outside linkuse the index, Luke
Core: Joins
Inner, left and cross joins, and how the database runs them. Nested loops suit small inputs, hash joins suit large unsorted ones, and merge joins need sorted input. The planner chooses, and you should know why it chose.
SimulatorSQL joins Outside linkPostgreSQL: join methods CodexDatabases Outside linkMode: SQL joins
Recommended: Window functions
ROW_NUMBER, LAG, running totals and ranks within partitions. They replace many nested subqueries with a few readable lines, and much analytical SQL relies on them.
Outside linkPostgreSQL: window functions Outside linkMode: window functions Outside linkPostgreSQL: window function reference
Recommended: Reading query plans
EXPLAIN ANALYZEshows how a query ran: a sequential scan over 40 million rows where you expected an index, or row estimates ten times off. It is one of the most useful skills in data work.SimulatorQuery execution Outside linkPostgreSQL: using EXPLAIN CodexPerformance
Stage 2
Relational databases and modelling
A query can only be as fast as its schema and indexes allow. This stage covers the database engine itself: how an index turns a two-second scan into a lookup of a few milliseconds, what ACID guarantees, and how to model data so the database does the work.
Core: Database indexing
B-tree indexes for ranges, hash indexes for equality, and partial and covering indexes for specific queries. Every index slows down writes, so index the queries you run rather than ones you might.
GuideHow database indexing works SimulatorB-tree CodexDatabases Outside linkuse the index, Luke Outside linkPostgreSQL: indexes
Core: Transactions and ACID
Atomicity and durability are hard to guarantee when machines fail. Isolation is the subtle part: PostgreSQL, Oracle and SQL Server default to Read Committed and MySQL to Repeatable Read, not Serializable, and the anomalies those levels allow cause bugs that are hard to reproduce.
GuideHow transactions work SimulatorACID SimulatorIsolation levels Outside linkPostgreSQL: transaction isolation
Core: Normalisation and schema design
Third normal form stores each fact in one place, which keeps transactional writes consistent. Then you denormalise deliberately where reads need it. Doing that on purpose is fine; doing it by accident causes inconsistent data.
CodexDatabases Outside linkPostgreSQL: data definition Outside linkDesigning Data-Intensive Applications
Recommended: The write-ahead log
Record each change in a sequential log first and apply it to the data files later, so durability does not need a disk sync for every row. PostgreSQL crash recovery, Kafka and LSM-tree stores all use the same idea.
GuideHow the WAL works SimulatorWAL recovery Outside linkPostgreSQL: WAL
Recommended: Isolation levels
Dirty reads, non-repeatable reads, phantoms and write skew. Each isolation level allows some of these in exchange for throughput. Interviewers ask what can go wrong at Read Committed; in production the question is why two transfers from the same account both succeeded.
SimulatorIsolation levels Outside linkPostgreSQL: transaction isolation Outside linkDesigning Data-Intensive Applications
Stage 3
NoSQL databases
NoSQL databases still have schemas; the application enforces them instead of the database. Each family makes a trade: Redis keeps data in memory for speed, Cassandra gives up joins for write throughput across many nodes, and document stores allow flexible record shapes. Choose by how the data will be read and written.
Core: Key-value stores and Redis
Redis is an in-memory store of keys and data structures, reached over the network. It is used for caches, rate limiters, leaderboards and sessions. Memory is limited and persistence is optional, so choose the eviction and persistence settings deliberately.
GuideHow Redis works SimulatorRedis operations GuideRedis persistence SimulatorDistributed cache Outside linkRedis documentation
Core: Wide-column stores and Cassandra
You design each table around one query, choosing the partition key first, and there are no joins. In return, writes scale across nodes and the database keeps working if a data centre goes offline. Consistency is tunable per query.
GuideCassandra replication SimulatorRead/write quorum SimulatorConsistent hashing Outside linkCassandra documentation
Recommended: Quorums and tunable consistency
Choose how many replicas must answer a read (R) and acknowledge a write (W). When R + W is greater than the number of replicas N, a read sees the latest write. Stronger settings cost latency; weaker ones may return stale data.
SimulatorRead/write quorum SimulatorCAP theorem GuideCassandra replication PaperDynamo paper
Recommended: Document and search stores
MongoDB and Elasticsearch store each record with its own structure, so changing the shape does not need a migration. That suits data whose shape changes often, but skipping modelling can mean rewriting joins in application code.
CodexDatabases Outside linkMongoDB data modeling Outside linkDesigning Data-Intensive Applications
Stage 4
Storage engines and file formats
Every database has a storage engine that favours either writes or reads, and every data lake has a file format that stores data by row or by column. These two choices explain more about performance than most query tuning.
Core: LSM trees and B-trees
B-trees update pages in place, which favours reads but rewrites a lot on each write. LSM trees append to an in-memory table and merge files later, which favours writes and uses Bloom filters to keep reads fast. Many write-heavy databases use LSM trees.
SimulatorLSM tree SimulatorStorage engine GuideHow the WAL works Outside linkDesigning Data-Intensive Applications
Core: Columnar formats: Parquet and ORC
Data is stored by column, so selecting three columns from a 200-column table reads only those three. With run-length and dictionary encoding, analytical queries become much cheaper. Parquet is the usual default for a data lake.
Outside linkApache Parquet documentation SimulatorStorage engine Outside linkSpark: data sources CodexPerformance
Recommended: Bloom filters
A small probabilistic structure that can say a key is definitely absent, using a few bits per key. In an LSM tree it stops a read from checking every file on disk for a key that was never written.
Recommended: Compression and encoding
Snappy for speed, Zstandard for a better ratio, and dictionary encoding for columns with few distinct values. In columnar files similar values sit together, so the encoding has a large effect on storage cost.
Outside linkApache Parquet: encodings Outside linkSpark: data sources CodexPerformance
Stage 5
Batch processing
When a dataset no longer fits on one machine, you describe the transformations and a cluster runs them in parallel. Spark is the usual choice. Most slow Spark jobs come down to how the data is partitioned across executors.
Core: Spark and the DAG
Transformations do nothing until an action needs their result. Spark builds a graph of the work, splits it into stages at each shuffle, and runs one task per partition. The stage boundaries are where the cost is.
Outside linkSpark: programming guide Outside linkSpark SQL guide CodexDistributed systems Outside linkLearning Spark (free, Databricks)
Core: Partitioning and the shuffle
A shuffle moves data across the network so rows with the same key end up together, and it is where many jobs slow down or fail. A skewed key can send most rows to one executor. Repartition, add a salt to the key, or broadcast the smaller side.
SimulatorDatabase sharding Outside linkSpark: performance tuning CodexPerformance
Core: Joins in Spark
If one table is small enough to fit in memory, broadcast it to every executor and avoid the shuffle. Otherwise Spark sorts and merges across the cluster. Spotting the broadcast case is often the biggest single speed-up.
Outside linkSpark: performance tuning SimulatorSQL joins Outside linkSpark SQL guide
Recommended: MapReduce and lineage
Map on each machine, shuffle by key, reduce. Spark replaced MapReduce, but kept its main ideas, including recovering lost data by recomputing it from its lineage instead of replicating it. Google's 2004 paper is still worth reading.
PaperMapReduce paper (Google) Outside linkSpark: programming guide Outside linkDesigning Data-Intensive Applications
Stage 6
Streaming
Batch jobs report on what already happened; streaming jobs process events as they arrive. That makes things harder, because events can arrive late, out of order or more than once, and a window has to close before all its data is in. Kafka usually stores the events and Flink or a similar engine processes them.
Core: Kafka as a log
Kafka is a durable, partitioned log that consumers can replay. Producers append, each consumer tracks its own position, and the data stays for the retention period. Many architectures use it as the record other systems read from.
GuideHow Kafka works GuideKafka storage internals SimulatorLSM tree Outside linkKafka documentation
Core: Partitions, offsets and consumer groups
Order is guaranteed only within a partition, and the partition count limits how many consumers in a group can read in parallel. Rebalances pause consumption briefly, so choose partition counts and keys with both in mind.
GuideKafka storage internals GuideHow Kafka works GuideMessage queues Outside linkKafka: consumer docs
Core: Delivery semantics
At most once, at least once, or exactly once. Kafka offers exactly-once processing within Kafka using transactions, but once side effects reach other systems you are back to at-least-once delivery with idempotent writes and deduplication on a key.
GuideMessage queues Outside linkKafka: delivery semantics GuideHow Kafka works
Recommended: Stream processing and windows
Flink runs stateful computations over unbounded streams, with tumbling, sliding and session windows and fault-tolerant state. The hard question is when to close a window if more data might still arrive.
Outside linkApache Flink documentation Outside linkFlink: concepts Outside linkKafka Streams docs
Stage 7
Pipelines and orchestration
A pipeline is a set of dependent tasks run on a schedule, and it has to recover from partial failures and rerun safely. Airflow and similar tools schedule and retry the tasks, and dbt handles the SQL transformations in the warehouse.
Core: Orchestration with Airflow
A DAG of tasks, a scheduler, retries and backfills. The orchestrator should trigger the systems that move data rather than move it itself, and record whether they succeeded. Keep tasks idempotent so backfills are safe.
Outside linkAirflow documentation Outside linkAirflow: core concepts Outside linkAirflow: best practices
Core: Transformations with dbt
SQL models with version control, tests and a dependency graph dbt works out for you. Raw data is loaded first and transformed inside the warehouse (ELT). The lineage and documentation it produces are a large part of why analytics teams adopted it.
Outside linkdbt documentation Outside linkdbt: best practices Outside linkdbt: building models
Core: Idempotency and backfills
Every task should be safe to run twice and safe to run for a past date. Overwrite whole partitions rather than appending rows you cannot remove. Rerunning a pipeline should be routine.
Outside linkAirflow: best practices Outside linkdbt: incremental models CodexSystem design
Recommended: Scheduling and dependencies
Cron runs jobs at fixed times but knows nothing about dependencies between them. Orchestrators run a task when the tasks it depends on have finished.
Outside linkAirflow: core concepts Outside linkAirflow documentation CodexSystem design
Stage 8
Data warehouses and the lakehouse
A data warehouse stores data for analysis by people and dashboards. It is columnar, separates storage from compute, and is modelled for analytics rather than transactions. A lakehouse keeps data in cheap object storage and adds warehouse features through an open table format.
Core: Cloud warehouses
Snowflake and BigQuery separate storage from compute, so each scales on its own and you pay for queries rather than idle servers. A query without a partition filter can scan the whole table, and the bill reflects it.
Outside linkSnowflake documentation Outside linkBigQuery documentation Outside linkBigQuery: best practices
Core: Dimensional modelling
A star schema has one fact table of events surrounded by dimension tables that describe them. Slowly changing dimensions are the hard part: whether to overwrite an old value or keep its history changes the model.
CodexDatabases Outside linkdbt: dimensional modeling Outside linkKimball: dimensional modeling techniques
Recommended: Lakehouse table formats
Iceberg, Delta Lake and Hudi add ACID transactions, schema changes and time travel to Parquet files in object storage, so you get atomic commits on S3 without a separate warehouse.
Outside linkApache Iceberg documentation Outside linkDelta Lake documentation Outside linkApache Parquet documentation
Recommended: Partitioning and clustering
Partition by the column queries filter on, usually a date, so the engine skips most of the data. Cluster by the columns queries group or filter on next. Done well, a query reads gigabytes instead of terabytes.
SimulatorDatabase sharding Outside linkBigQuery: partitioning Outside linkSnowflake: micro-partitions
Stage 9
Data quality, governance and observability
A pipeline that succeeds while writing wrong data is worse than one that fails. This stage covers tests on the data itself, contracts between producers and consumers, lineage to see what a bad table affects, and monitoring that catches silent changes.
Core: Testing data
Checks on the data rather than the code: row counts in an expected range, no nulls in a key, foreign keys that match. dbt tests and Great Expectations fail the pipeline when a check fails.
Outside linkdbt: tests Outside linkGreat Expectations docs Outside linkdbt: best practices
Recommended: Data contracts
A table's schema is an interface between the team that produces it and everyone who reads it. A contract makes that explicit, so a breaking change fails CI before it breaks dashboards. Someone also has to own it.
CodexSystem design Outside linkdbt: model contracts Outside linkDesigning Data-Intensive Applications
Recommended: Lineage and data catalogs
When a number is wrong, the first question is what feeds it. Lineage records which tables depend on which, so you can trace a problem upstream to its source and downstream to everything it affects.
Outside linkdbt: lineage Outside linkOpenLineage docs Outside linkAirflow documentation
Recommended: Freshness and data observability
A dashboard can be two days out of date without anyone noticing. Track freshness, row volume and value distributions, and alert on unusual changes as well as on failed jobs.
Outside linkdbt: source freshness Outside linkGreat Expectations docs CodexSystem design
Stage 10
Distributed data
Most systems in this roadmap spread data across machines, and the same few ideas decide whether that works: partitioning keys so load spreads evenly, replicating so one dead node does not take the service down, and getting nodes to agree on what is true.
Core: Sharding and partition keys
Split the data and route each request by key. A poorly chosen key puts most of the load on one shard while the others sit idle, and changing the key on a live system is a project of its own.
SimulatorDatabase sharding CodexDistributed systems Outside linkDesigning Data-Intensive Applications
Core: Consistent hashing
With modulo sharding, adding a node moves almost every key. Consistent hashing places keys and nodes on a ring, so only the keys next to the new node move. Cassandra, DynamoDB and many distributed caches use it.
SimulatorConsistent hashing GuideCassandra replication CodexDistributed systems
Recommended: Replication strategies
Single-leader replication is simple but followers lag. Multi-leader and leaderless replication improve availability but need conflict resolution. Choose based on whether reads can be stale, and measure replication lag.
GuideCassandra replication SimulatorRead/write quorum Outside linkDesigning Data-Intensive Applications
Recommended: Generating IDs across machines
Auto-increment IDs stop working once data is sharded. Snowflake-style IDs combine a timestamp, a machine ID and a sequence number in 64 bits, so each node creates unique, roughly time-ordered IDs without coordination.
GuideDistributed IDs GuideSnowflake IDs CodexDistributed systems
Stage 11
Machine learning fundamentals
You do not need to derive backpropagation to ship a model, but you do need to understand what it does well enough to debug it in production. This stage covers supervised and unsupervised learning, what training optimises, why a model that scores well offline can still fail, and how text becomes vectors.
Core: How training works
Feed in examples, measure the error with a loss function, adjust the parameters to reduce it, and repeat. Gradient descent does the adjusting in most models. A model fits whatever is in the examples, noise included.
Outside linkscikit-learn: ML basics Outside linkscikit-learn user guide Outside linkGoogle: ML crash course
Core: Supervised and unsupervised learning
Supervised learning predicts a known label; unsupervised learning finds structure in unlabelled data. Classification, regression, clustering and dimensionality reduction. Much of the value in industry comes from supervised learning on tabular data.
Outside linkscikit-learn: supervised learning Outside linkscikit-learn: unsupervised learning Outside linkGoogle: ML crash course
Core: Overfitting, bias and variance
A model that does well on training data and badly on new data has memorised its examples. Too simple a model underfits; too flexible a model overfits. Regularisation and a held-out test set help you tell which is happening.
Outside linkscikit-learn: cross-validation Outside linkscikit-learn: model evaluation Outside linkGoogle: ML crash course
Recommended: Embeddings
Words, users or items represented as points in a high-dimensional space, where nearby points are similar. Search, recommendations and retrieval-augmented generation all use them, often with cosine similarity.
SimulatorVector embedding Outside linkscikit-learn: feature extraction SimulatorVector embeddings
Recommended: Evaluation metrics
Accuracy misleads on imbalanced data: a model that always predicts "not fraud" can score 99%. Precision, recall, F1, AUC and the confusion matrix show what the model gets right and wrong. Choose the metric that reflects the cost of each kind of mistake.
Outside linkscikit-learn: model evaluation Outside linkscikit-learn: classification metrics Outside linkGoogle: ML crash course
Stage 12
Feature engineering and feature stores
Better features usually improve a model more than a more complex architecture does. The hard part is serving exactly the same feature values to training, which reads history in batch, and to inference, which needs them in milliseconds. Feature stores exist to keep the two consistent.
Core: Feature engineering
Encoding categories, scaling numbers, and building aggregates and combinations that make patterns easier for a model to find. Careful feature work often improves results more than a larger model.
Outside linkscikit-learn: preprocessing Outside linkscikit-learn: feature extraction Outside linkGoogle: ML crash course
Core: Training-serving skew
A model is trained on a feature computed in batch and served the same feature computed differently online. The values differ slightly and accuracy drops without any error. Compute each feature once and read it from both places.
Outside linkFeast: feature store concepts Outside linkGoogle: ML crash course CodexSystem design
Core: Online and offline feature stores
The offline store holds historical feature values in the warehouse for training. The online store is a fast key-value store that serves current values to inference. A feature store keeps the two consistent.
GuideHow Redis works Outside linkFeast documentation SimulatorDistributed cache
Recommended: Point-in-time correctness
Training examples must use the feature values that were known at the time of each label. Joining a Tuesday label to a feature computed on Thursday leaks future information into training. Point-in-time joins prevent this.
Outside linkFeast: point-in-time joins SimulatorSQL joins Outside linkFeast: feature store concepts
Stage 13
Training and experiments
Training a model once in a notebook is easy. Training it again next month and getting the same result, with the same data, seed and settings recorded, is what makes it engineering. This stage covers the frameworks and the record-keeping around them.
Core: Training frameworks
scikit-learn for classical models on tabular data, PyTorch or TensorFlow for neural networks. Start simple: gradient-boosted trees often beat neural networks on tabular data and train in seconds.
Outside linkPyTorch documentation Outside linkscikit-learn user guide Outside linkTensorFlow guide
Core: Experiment tracking
Record each run's parameters, metrics and artifacts, so you can say which model is in production and how it was trained. MLflow and Weights & Biases are common choices.
Outside linkMLflow: tracking Outside linkMLflow documentation Outside linkWeights & Biases docs
Recommended: Hyperparameter tuning
Grid search tries every combination, random search often does as well for less effort, and Bayesian methods suit expensive training runs. Use cross-validation so the chosen settings generalise rather than fitting the validation set.
Outside linkscikit-learn: hyperparameter tuning Outside linkscikit-learn: cross-validation Outside linkMLflow documentation
Recommended: Reproducibility
Pin the data version, random seed, library versions and environment. A result you cannot reproduce cannot be trusted. Tools such as DVC version datasets the way Git versions code.
Outside linkMLflow: projects Outside linkDVC documentation CodexSystem design
Stage 14
Model serving and MLOps
A trained model does nothing until it answers requests within a latency budget. After that, the data it sees changes over time and its predictions get worse, so you need to notice and retrain. MLOps covers serving, monitoring and retraining.
Core: Model serving
Batch inference scores data offline; online inference answers requests in real time behind a load balancer within a latency budget. It has the same scaling, caching and tail-latency problems as any backend service.
CodexSystem design Outside linkMLflow: model serving Outside linkTensorFlow Serving
Core: Model registry and deployment
A registry versions models and tracks which is in staging or production. Release a new model with shadow traffic or a canary, so a bad one affects few users, and treat each model as a release.
Outside linkMLflow: model registry Outside linkMLflow: models CodexSystem design
Core: Monitoring and drift
A model does not crash when it degrades; it becomes less accurate as incoming data drifts away from the training data. Monitor prediction distributions and feature statistics as well as latency.
Outside linkGoogle: ML production monitoring Outside linkMLflow documentation CodexSystem design
Recommended: Retraining pipelines
Models go stale as the world changes. Run retraining in the same orchestrator as the data pipelines, with tests and approval steps, so it happens on a schedule rather than in a rush after accuracy drops.
Outside linkAirflow documentation Outside linkMLflow: pipelines CodexSystem design
After this roadmap
- SimulatorsLSM trees, SQL joins, isolation levels, sharding and quorums, as models that run in the browser.
- Databases in the CodexLonger study pages on indexes, transactions, storage engines and query planning.
- How it worksGuides to Kafka, the write-ahead log, database indexing and transactions.