Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

DATA Engineering Roadmap

A detailed, practical roadmap to become a job-ready Data Engineer — from foundations to production-grade pipelines.

Table of Contents

Overview

Data engineering is the discipline of building reliable systems that ingest, store, transform, serve, and monitor data.

A strong Data Engineer knows how to:

  • collect data from APIs, applications, databases, and files
  • design clean and scalable data models
  • create reliable ETL/ELT pipelines
  • schedule and monitor workflows
  • handle batch and streaming workloads
  • work with cloud platforms and modern data tools
  • ensure data quality, governance, and security
  • support analytics, machine learning, and operational systems

This roadmap is designed to help you progress from beginner to production-ready practitioner with a clear sequence of skills, tools, and projects.

Who This Roadmap Is For

This roadmap is ideal for:

  • beginners entering data engineering
  • data analysts moving toward engineering
  • software engineers transitioning into data platforms
  • BI developers who want stronger backend data skills
  • students preparing for internships or entry-level roles
  • professionals who want a structured path instead of random tutorials

What a Data Engineer Does

A Data Engineer typically works on:

  • Data ingestion — pulling data from databases, APIs, SaaS tools, logs, and streams
  • Data transformation — cleaning, joining, standardizing, validating, and modeling data
  • Data storage — operating data lakes, warehouses, and lakehouses
  • Pipeline orchestration — scheduling, dependency management, retries, and alerting
  • Data reliability — testing, monitoring, lineage, observability, and SLAs
  • Infrastructure — cloud services, containers, CI/CD, permissions, and deployment workflows
  • Collaboration — partnering with analysts, analytics engineers, ML engineers, and product teams

Core Skills You Need

A solid data engineering foundation combines multiple skill areas:

1. Programming

Focus on:

  • Python
  • shell basics
  • scripting and automation
  • debugging and logging
  • packages and virtual environments
  • clean code practices

2. SQL

This is non-negotiable.

You should master:

  • filtering, grouping, joins
  • window functions
  • CTEs
  • subqueries
  • aggregations
  • date handling
  • query optimization basics
  • schema design

3. Data Modeling

Learn how to structure data so it is useful and scalable:

  • normalization and denormalization
  • fact and dimension tables
  • star and snowflake schemas
  • slowly changing dimensions
  • surrogate keys
  • business grain definition

4. Distributed Processing

For large-scale systems, learn:

  • partitioning
  • parallelism
  • shuffling
  • fault tolerance
  • cluster computing concepts

5. Cloud and Infrastructure

Modern data engineering usually runs in the cloud:

  • object storage
  • IAM / permissions
  • compute services
  • containers
  • infrastructure basics
  • deployment pipelines

6. Reliability and Operations

Production systems require:

  • data quality checks
  • alerting
  • retry logic
  • idempotency
  • monitoring
  • incident handling
  • cost awareness

Roadmap Stages

Stage 0: Mindset and Setup

Before tools, build the right habits.

Learn

  • what ETL vs ELT means
  • difference between OLTP and OLAP
  • basics of files, tables, APIs, logs, events, and streams
  • why data pipelines fail in production
  • importance of reproducibility and observability

Set Up Your Environment

Install and configure:

  • Python
  • VS Code or PyCharm
  • Git and GitHub
  • Docker
  • a local SQL database such as PostgreSQL
  • Jupyter Notebook (optional)

Output

By the end of this stage, you should be able to:

  • write and run scripts locally
  • manage code with Git
  • use Docker at a basic level
  • connect Python to a database

Stage 1: Programming Fundamentals

Primary Language: Python

Python is widely used in data engineering for scripting, orchestration, API integrations, and Spark jobs.

Topics to Learn

  • variables, data types, functions, classes
  • loops and conditionals
  • list/dict/set comprehensions
  • file handling
  • exception handling
  • modules and packages
  • virtual environments
  • logging
  • type hints
  • unit testing basics

Libraries to Know Early

  • requests
  • pandas
  • pathlib
  • json
  • datetime
  • sqlalchemy
  • psycopg / database drivers

Practice Tasks

  • pull data from a public API
  • save JSON responses to files
  • transform raw data into tabular format
  • load transformed data into PostgreSQL
  • add logging and error handling

Goal

Build small scripts that behave like simple pipelines.

Stage 2: SQL and Data Modeling

SQL is the heart of analytics and warehouse work.

SQL Topics to Master

Fundamentals

  • SELECT
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • JOIN
  • UNION

Intermediate

  • window functions
  • CTEs
  • nested queries
  • case expressions
  • date/time functions
  • text manipulation
  • null handling

Advanced

  • execution plans
  • indexing concepts
  • query performance tuning
  • incremental logic
  • deduplication patterns
  • change data capture concepts

Data Modeling Topics

  • entities and relationships
  • business keys vs surrogate keys
  • dimensional modeling
  • medallion layering (bronze, silver, gold)
  • snapshots and historical tracking
  • schema evolution basics

Practice Tasks

  • design a mini e-commerce schema
  • write 25–50 SQL queries of increasing difficulty
  • create fact and dimension tables
  • build daily summary tables
  • solve window function problems

Goal

Be able to transform messy source data into analyst-ready models.

Stage 3: Databases and Storage Systems

You need to understand both transactional and analytical systems.

Learn the Difference

OLTP Systems

Used for application transactions:

  • PostgreSQL
  • MySQL
  • SQL Server

OLAP / Analytical Systems

Used for reporting and analytics:

  • data warehouses
  • columnar storage systems
  • lakehouse engines

Concepts to Learn

  • row vs column storage
  • partitions
  • indexes
  • replication basics
  • transactions and ACID
  • file formats: CSV, JSON, Parquet, Avro
  • compression and serialization
  • schema-on-write vs schema-on-read

Practice Tasks

  • compare CSV vs Parquet size and speed
  • load large files into a relational database
  • partition sample data by date
  • benchmark simple queries

Goal

Understand how storage design affects performance, cost, and maintainability.

Stage 4: Data Warehousing and Analytics Engineering

This is where raw data becomes trusted business data.

Learn

  • warehouse architecture
  • layered data models
  • transformation workflows
  • testing and documentation
  • incremental transformations
  • semantic consistency across teams
  • dbt fundamentals
  • source freshness checks
  • tests for unique/not-null/relationships
  • modular SQL models
  • documentation generation
  • staging → intermediate → marts structure

Practice Tasks

  • build a dbt project on top of PostgreSQL or a cloud warehouse
  • model raw order data into marts
  • add tests and documentation
  • create a dashboard-ready schema

Goal

Be able to take raw ingested data and produce reliable business-ready datasets.

Stage 5: Batch Data Processing

Most organizations still rely heavily on batch workloads.

Learn

  • scheduled ingestion
  • chunked processing
  • retries
  • backfills
  • idempotent loads
  • full refresh vs incremental loads
  • partition-based processing

Tools to Explore

  • Python-based ETL scripts
  • Spark / PySpark
  • SQL transformations in warehouses

Spark Topics

  • DataFrames
  • transformations vs actions
  • lazy execution
  • partitioning
  • joins and aggregations
  • reading/writing Parquet
  • Spark SQL
  • performance basics

Practice Tasks

  • process millions of rows with PySpark
  • clean and aggregate large event data
  • write partitioned Parquet outputs
  • compare local Python vs Spark for scale

Goal

Learn when simple scripts are enough and when distributed processing is necessary.

Stage 6: Workflow Orchestration

Pipelines are not just scripts. They need scheduling, dependency management, and monitoring.

Learn

  • DAG concepts
  • task dependencies
  • retries and backoff
  • scheduling intervals
  • parameterization
  • backfills
  • SLAs and alerts
  • secrets handling

Tool to Prioritize

  • Apache Airflow

Practice Tasks

  • create DAGs for ingest → transform → validate → publish
  • run daily and hourly schedules
  • add retry policies and failure alerts
  • parameterize DAGs for different environments

Goal

Move from “script runner” thinking to “production workflow system” thinking.

Stage 7: Streaming and Event-Driven Data

Many systems need near-real-time data movement.

Learn

  • events, topics, partitions, offsets
  • producers and consumers
  • delivery guarantees
  • stateful vs stateless processing
  • watermarking and late-arriving data
  • event time vs processing time

Tools to Explore

  • Apache Kafka
  • Kafka Connect concepts
  • Spark Structured Streaming
  • stream processing fundamentals

Practice Tasks

  • stream click events into Kafka
  • consume messages with Python
  • aggregate events over time windows
  • send enriched records to storage or a database

Goal

Understand real-time patterns even if your first job is primarily batch-oriented.

Stage 8: Cloud Data Engineering

Most real-world roles expect cloud familiarity.

Pick One Cloud First

Choose one deeply before sampling the others:

  • AWS
  • GCP
  • Azure

Universal Cloud Concepts

  • object storage
  • managed databases
  • serverless compute
  • data warehouse services
  • secrets and IAM
  • networking basics
  • logging and monitoring
  • cost optimization

Typical Services by Category

Storage

  • S3 / GCS / Azure Blob Storage

Compute

  • EC2 / Compute Engine / Virtual Machines
  • Lambda / Cloud Functions / Azure Functions
  • container services

Warehousing

  • Redshift
  • BigQuery
  • Snowflake
  • Azure Synapse / Fabric-oriented ecosystems

Orchestration and Integration

  • Airflow-managed offerings
  • cloud-native schedulers
  • connectors and ingestion services

Practice Tasks

  • store raw files in object storage
  • run transformations in cloud compute or warehouse SQL
  • create an end-to-end ELT pipeline in one cloud
  • track costs and permissions

Goal

Be able to build cloud-native pipelines instead of only local demos.

Stage 9: Data Quality, Governance, and Security

This is what separates toy projects from trustworthy systems.

Data Quality Topics

  • null checks
  • uniqueness checks
  • referential integrity
  • freshness checks
  • volume anomaly checks
  • distribution checks
  • schema drift detection

Governance Topics

  • metadata
  • lineage
  • ownership
  • naming conventions
  • documentation
  • data classification
  • retention policies

Security Topics

  • principle of least privilege
  • secret management
  • encryption at rest and in transit
  • PII awareness
  • auditability

Practice Tasks

  • add validation steps to every pipeline
  • create data contracts for key tables
  • document lineage from raw to mart
  • separate dev/staging/prod access

Goal

Learn to build pipelines others can trust.

Stage 10: DevOps for Data Engineers

Data engineers need software engineering discipline.

Learn

  • Git workflows
  • branching strategy
  • pull requests
  • CI/CD basics
  • automated tests
  • Docker images
  • environment variables
  • IaC concepts (Terraform is a strong plus)

Practice Tasks

  • dockerize a pipeline project
  • add unit tests and SQL tests
  • configure GitHub Actions for linting/tests
  • deploy the same project to dev and prod configs

Goal

Make your data projects reproducible, reviewable, and deployable.

Stage 11: Production Readiness

At this stage, focus less on tools and more on system design.

Learn

  • scalability trade-offs
  • fault tolerance
  • observability
  • cost vs performance
  • schema evolution
  • reprocessing and replay strategies
  • SLA / SLO thinking
  • stakeholder communication

Questions You Should Be Able to Answer

  • What happens if a pipeline fails halfway through?
  • How do you backfill historical data safely?
  • How do you detect bad upstream data?
  • How do you avoid duplicates?
  • How do you debug slow jobs?
  • When should you use Spark instead of SQL?
  • When is streaming worth the complexity?

Goal

Think like an engineer responsible for business-critical data systems.

Project Roadmap

Projects matter more than endless tutorials.

Project 1: API to PostgreSQL Pipeline

Goal: Build a small ETL pipeline.

Stack: Python, requests, PostgreSQL, Docker

Features:

  • extract data from a public API
  • transform nested JSON into clean tables
  • load into PostgreSQL
  • add logs and retry handling

Project 2: Analytics Modeling Project

Goal: Create business-ready warehouse models.

Stack: SQL, dbt, PostgreSQL or cloud warehouse

Features:

  • staging models
  • marts
  • tests
  • documentation
  • incremental models

Project 3: Orchestrated ELT Pipeline

Goal: Run a full multi-step pipeline automatically.

Stack: Airflow, dbt, Docker

Features:

  • ingest raw data
  • transform with dbt
  • run data quality checks
  • publish final tables
  • add alerts

Project 4: Batch Big Data Pipeline

Goal: Process large datasets efficiently.

Stack: PySpark, Parquet, object storage

Features:

  • read large event logs
  • clean and aggregate data
  • partition outputs by date
  • benchmark performance changes

Project 5: Streaming Pipeline

Goal: Demonstrate real-time thinking.

Stack: Kafka, Python consumers, Spark Structured Streaming or similar

Features:

  • ingest event stream
  • aggregate in windows
  • store results downstream
  • monitor lag/failures

Project 6: Cloud-End-to-End Data Platform Demo

Goal: Show production-style architecture.

Stack: cloud storage + cloud warehouse + orchestration + IaC

Features:

  • raw, refined, curated layers
  • automated scheduling
  • cost-aware storage choices
  • permissions and environment separation

Beginner to Advanced Tool Stack

Below is a practical progression, not a mandatory list.

Foundation Tools

  • Python
  • SQL
  • PostgreSQL
  • Git/GitHub
  • Docker

Early Data Engineering Tools

  • pandas
  • SQLAlchemy
  • dbt
  • Airflow

Scale Tools

  • PySpark
  • Kafka
  • Parquet / Avro
  • object storage

Cloud / Platform Tools

  • AWS / GCP / Azure
  • warehouse platform
  • Terraform
  • CI/CD pipelines

Reliability Tools

  • data tests
  • alerting systems
  • logging / metrics / dashboards
  • lineage and catalog tools

Portfolio Checklist

Your GitHub portfolio should show more than notebooks.

Include

  • clear README files
  • architecture diagrams
  • setup instructions
  • sample datasets or generation scripts
  • tests
  • screenshots of DAGs or dashboards
  • documentation of trade-offs
  • failure handling notes
  • deployment steps

Recruiter-Friendly Project Questions Your Repo Should Answer

  • What problem does this solve?
  • What tools are used and why?
  • How is data ingested, transformed, and stored?
  • How is quality validated?
  • How is the workflow scheduled?
  • How would this scale in production?

Interview Preparation

Technical Topics

  • SQL joins, windows, deduplication
  • Python data structures and scripting
  • ETL vs ELT
  • dimensional modeling
  • Airflow concepts
  • Spark basics
  • partitioning and file formats
  • Kafka fundamentals
  • warehousing concepts
  • cloud basics
  • testing and observability

Scenario Questions

Prepare for questions like:

  • design a daily sales pipeline
  • handle duplicate events in ingestion
  • backfill 2 years of historical data
  • optimize a slow transformation job
  • model orders, customers, and products for analytics
  • move from batch to near-real-time reporting

Behavioral Topics

  • debugging production issues
  • communicating with analysts/stakeholders
  • prioritizing reliability vs speed
  • documenting assumptions and trade-offs

Common Mistakes to Avoid

  • learning too many tools at once
  • skipping SQL depth
  • building only notebooks instead of pipelines
  • ignoring testing and logging
  • using Spark before understanding basic data workflows
  • copying tutorials without changing anything
  • focusing on certificates over projects
  • neglecting cloud cost and permissions
  • avoiding documentation

Learn by Official Documentation

Practice Platforms

  • SQL practice websites
  • LeetCode SQL sections
  • Kaggle datasets
  • public APIs
  • cloud free tiers / sandbox projects

Books / Concepts to Study

  • data modeling
  • distributed systems basics
  • database internals basics
  • analytics engineering principles
  • software engineering best practices

How to Use This Roadmap

Use this roadmap in cycles:

  1. Learn a concept
  2. Apply it in a small exercise
  3. Use it in a project
  4. Document what you learned
  5. Refactor and improve

Do not wait until you “know enough” before building.

Build early. Break things. Fix them. Document them. Repeat.

Final Advice

The fastest way to become a Data Engineer is not to memorize every tool. It is to consistently build systems that move data from raw → reliable → usable.

A strong path looks like this:

  1. Master Python + SQL
  2. Learn databases + modeling
  3. Build ETL/ELT pipelines
  4. Add dbt + Airflow
  5. Learn Spark + cloud
  6. Understand Kafka + streaming
  7. Practice testing, monitoring, and production design
  8. Build a portfolio that proves you can do the work

If you stay consistent and project-focused, this roadmap can take you from beginner to job-ready with real confidence.