Data Engineer Roadmap: SQL, Pipelines, Spark and Cloud

What a data engineer does

Data engineers build and maintain the systems that collect, move, clean and store data so analysts and data scientists can use it. They design pipelines, manage data warehouses and lakes, and make sure data is accurate, on time and affordable to process. If data is the fuel, data engineers build the fuel lines.

Titles you will see: Data Engineer, Analytics Engineer, ETL Developer, Big Data Engineer, Cloud Data Engineer.

Stage 1: foundations (weeks 1–8)

• Programming: Python is the standard. Learn functions, file handling, APIs, error handling and testing (Python guide).
• SQL, deeply: joins, window functions, CTEs, query plans and indexing. This is the core skill; see SQL for interviews.
• Linux and command line, Git (guide).
• Core CS: data structures, OS and networking basics (core subjects).

Stage 2: data modelling and warehousing (weeks 8–14)

• OLTP vs OLAP, normalised vs dimensional design
• Star and snowflake schemas, facts and dimensions, slowly changing dimensions
• Data warehouses: how columnar storage works; try BigQuery, Snowflake, Redshift or a free option like DuckDB and PostgreSQL
• dbt for SQL-based transformations, testing and documentation, increasingly common in analytics engineering

Stage 3: batch pipelines (weeks 12–20)

• ETL vs ELT, incremental loads, idempotency, backfills
• Orchestration with Apache Airflow (or Prefect or Dagster): DAGs, scheduling, retries, alerts
• File formats: CSV, JSON, Parquet, Avro; partitioning and compression
• Ingesting from APIs, databases and files; handling schema changes
• Data quality checks: row counts, nulls, uniqueness, freshness

Stage 4: big data and streaming (weeks 18–28)

• Apache Spark with PySpark: DataFrames, transformations vs actions, joins, partitions, shuffles, performance tuning
• Kafka basics for streaming: topics, partitions, consumers, delivery guarantees
• Lakehouse ideas: Delta Lake or Apache Iceberg
• When you do not need big data tools: many problems fit comfortably in a single well-tuned database

Stage 5: cloud and engineering practice (weeks 24–34)

Choose one cloud (AWS, Azure or GCP) and learn its data services: object storage, a managed warehouse, a serverless compute option and a workflow service. Also learn:

• Docker and basic Kubernetes concepts
• CI/CD for data code and infrastructure as code (Terraform)
• Monitoring and cost control: storage tiers, query costs, cleaning up unused resources

The DevOps and cloud roadmap covers these.

Projects to build

1. End-to-end batch pipeline: pull data from a public API daily, store raw files, transform with SQL or Spark, load a warehouse table and schedule it with Airflow.
2. A dbt project with staging and mart models, tests and documentation, and a dashboard on top.
3. A streaming demo: events into Kafka, processed and written to a table, with a simple live metric.
4. A data-quality framework that checks a dataset and sends an alert when a check fails.

Document each with an architecture diagram, how to run it and what would change at 100x data volume.

Interview topics

• SQL problems (often hard ones with window functions)
• Python data-manipulation questions
• Data modelling: design a schema for an e-commerce or ride-sharing company
• Pipeline design: "How would you ingest 10 million events a day and make them queryable?"
• Spark concepts: partitions, shuffle, skew, caching
• Cloud and cost-awareness questions

Next steps

Revise DSA basics and the system design primer. Then look at fresher and experienced data engineering openings. If you want more modelling work, compare with the data scientist and ML roadmap.