Data Engineer roadmap

Python is the primary programming language for data engineering, used to write custom scripts that fetch information from web services and clean up messy files. It works by running instructions line by line, letting you automate repetitive tasks like downloading daily CSV reports from a partner's server. Think of it as a reliable digital assistant that loops through hundreds of files, checks their contents, and gets them ready for storage.

Learning Python matters because you will spend a lot of time writing glue code to connect different systems together. Without it, you cannot build custom data extractors or handle complex data cleaning tasks that standard tools simply cannot manage.

• Write functions to reuse your code safely
• Use packages to save development time
• Read and write local files easily
• Connect to web APIs for data

Advanced SQL goes beyond basic lookups, allowing you to slice, dice and combine complex tables efficiently. It works by using window functions and common table expressions to perform multi-step calculations inside the database itself, rather than pulling all the records into your laptop. Imagine you need to find the second-highest sale for every store in a massive retail chain; advanced SQL lets the database engine do the heavy lifting in seconds.

This matters because databases are built to handle huge volumes of data quickly. Writing efficient queries keeps your pipelines fast, reduces cloud computing costs, and ensures analysts get their reports on time without crashing the system.

• Use JOINs to combine multiple tables
• Write CTEs to break up queries
• Apply window functions for running totals
• Optimise slow queries for better speed

Linux is the operating system running almost all cloud servers, and Git is the system that tracks changes to your code. The Linux shell lets you navigate folders and run commands via text, while cron jobs schedule your scripts to run automatically every night. Think of it as setting an alarm clock for your computer to fetch yesterday's sales figures while you sleep, with Git keeping a safe history of every script you wrote.

These tools matter because your data pipelines will run on remote servers, not your personal computer. Knowing your way around the command line and version control keeps your projects organised and ensures your pipelines keep running smoothly.

• Navigate directories using the terminal
• Schedule tasks using cron safely
• Track code changes using Git
• Collaborate with others via repositories

Data modelling is the process of organising how tables relate to one another in a database. Normalised tables prevent duplicate entries when users sign up on a website, while star schemas arrange data into facts and dimensions for business reporting. Imagine a web shop where customer details are stored once, but their purchases link to that customer ID alongside product categories for easy weekly sales summaries.

Good data modelling matters because messy data leads to slow, confusing reports. A clear structure helps business teams find answers quickly and ensures your pipelines do not break when source systems change.

• Normalise tables for transactional web apps
• Build star schemas for fast analytics
• Define primary and foreign keys
• Separate facts from dimensions clearly

A data warehouse is a large database optimised specifically for running heavy analytics and business intelligence queries. It works by storing data in columns rather than rows, meaning the system only reads the specific columns needed for a report instead of scanning entire files. Think of it like a massive digital filing cabinet where pulling a list of all invoice totals takes seconds because the numbers are stacked neatly together.

Warehouses matter because standard databases struggle when millions of rows are queried at once. Knowing how to load and query a modern warehouse is a core skill for any professional data engineer.

• Use columnar storage for faster scans
• Load structured data from multiple sources
• Scale compute resources up and down
• Control access for different business teams

Data lakes are low-cost storage repositories that hold vast amounts of raw information in its original format. File formats like Parquet compress this data and organise it into columns, making it much cheaper to store and faster to read later. Imagine dropping every receipt from every shop into a giant warehouse box, but sorting them into neat, compressed bundles so you can still search them easily.

This matters because storing raw data cheaply gives your organisation flexibility. You can keep historical records for years without breaking your budget, and process them later when new business questions arise.

• Store unstructured and raw data cheaply
• Use Parquet for efficient compression
• Partition files by date for speed
• Separate storage costs from compute power

ETL and ELT describe the journey data takes from its source to your warehouse. ETL extracts the data, transforms it on a separate server, and loads the clean result; ELT loads the raw data straight into the warehouse first and transforms it using SQL afterwards. Think of it like cooking: ETL is chopping vegetables before bringing them to the kitchen, while ELT is dumping all the ingredients onto the counter and prepping them right there on the workbench.

Understanding both approaches matters because cloud warehouses have made ELT very popular due to their immense processing power, but some sensitive data still requires traditional ETL transformation.

• Extract data from various source systems
• Transform data to fit business needs
• Load final tables into the warehouse
• Choose between ETL and ELT wisely

Orchestration tools coordinate the order, timing and dependencies of your data pipeline tasks. They work by running a directed acyclic graph, ensuring that task B only starts after task A has finished successfully, and sending an alert if something fails. Imagine an automated factory assembly line where the paint robot only turns on once the metal chassis has been fully bolted together.

Orchestration matters because pipelines rely on each other. If a source file is late, your workflow should wait or pause gracefully rather than crash and ruin morning reports for the executive team.

• Schedule pipelines to run automatically
• Manage task dependencies and ordering
• Handle retries when network errors happen
• Monitor pipeline health and failures

Data build tool, or dbt, lets data engineers transform data inside the warehouse using modular SQL statements. It works by turning your SQL queries into professional tables or views, automatically generating documentation and testing data integrity along the way. Think of it as a version control system specifically for your business logic, ensuring that your financial metrics are calculated the exact same way every single time.

dbt matters because managing raw SQL scripts across a growing company quickly becomes chaotic. It brings software engineering best practices like testing and reviews directly into data transformation work.

• Write modular SQL select statements
• Automatically test data constraints
• Generate visual lineage documentation
• Track transformation history with Git

Data quality checks are automated tests that run inside your pipelines to catch errors before users see them. They work by checking rules such as ensuring key columns are never empty, values fall within expected ranges, or row counts match yesterday's totals. Imagine a supermarket scale that refuses to print a price sticker if a bag of apples somehow registers as weighing negative kilograms.

These tests matter because bad data destroys trust. Catching a broken upstream feed or a missing column early saves your team from awkward conversations with business leaders who spotted errors in their dashboards.

• Test for null values in primary keys
• Check that row counts look normal
• Set up alerts for pipeline failures
• Prevent bad data from reaching dashboards

Apache Spark is a distributed computing engine designed to process datasets that are far too large to fit on a single computer. It works by splitting your data into tiny chunks and processing them simultaneously across dozens or hundreds of separate machines. Think of it like hiring a massive team of helpers to sort through a giant warehouse of paperwork, rather than trying to read every single page by yourself.

Spark matters because modern companies collect petabytes of logs, clickstreams and transaction histories. Knowing how to write distributed code is essential when single-server databases run out of memory.

• Process petabyte-scale data efficiently
• Distribute workloads across clusters
• Use Resilient Distributed Datasets (RDDs)
• Optimise joins for big data jobs

Streaming data platforms ingest and process information in real time as events happen, rather than waiting to process them in batch overnight. They work by maintaining continuous logs of event messages that subscriber applications read instantly. Imagine a live stock ticker updating share prices the exact millisecond a trade occurs, rather than waiting until the end of the trading day.

Streaming matters because many modern applications need instant insights, such as detecting fraudulent credit card transactions, monitoring server health, or updating user feeds on social media apps immediately.

• Ingest real-time event messages
• Process records as they arrive
• Handle high-throughput data streams
• Maintain low latency for alerts

Cloud data services are managed platforms provided by companies like Amazon, Microsoft or Google that host your storage, compute and pipelines. They work by letting you rent powerful servers and specialized data tools on demand, saving your company from buying physical hardware. Think of it like renting a fully equipped commercial kitchen for a day instead of building a restaurant from scratch.

Cloud platforms matter because almost every modern engineering team builds their infrastructure in the cloud. Understanding how these managed services fit together is vital for deploying reliable data systems.

• Deploy pipelines on managed infrastructure
• Scale compute and storage independently
• Manage cloud security and access
• Monitor spending and resource usage

Docker is a tool that packages your pipeline code alongside all its required libraries into a lightweight container. It works by creating an isolated mini-environment so your script runs identically on your laptop, a test server, or a cloud cluster. Think of it like shipping fragile electronics inside a custom-fitted protective case so they work perfectly no matter which delivery truck carries them.

Containers matter because the classic excuse 'it worked on my machine' causes endless delays. Docker ensures that your data pipelines run reliably anywhere without dependency conflicts.

• Package code and libraries together
• Run applications in isolated containers
• Ensure consistency across environments
• Build images using a Dockerfile