SQL Foundations

Probably the most important skill of any data engineer, at any level, whether they are closer to the business or more technical, is SQL—the language of data. You can descriptively explain what you want from your data much more precisely than natural language through LLM workflows. That's why it will always be a core skill. For example, in the English language, you won't specify the partitions or the exact date range (including or excluding the current month). There are many questions that you need to define in your WHERE statement or in the SELECT, which you would miss otherwise.

To get started with SQL until you master it, you can follow this roadmap below:

• Start with understanding SQL.
• Database design principles, from relational database basics to key concepts for beginners. Learn DDL (ALTER, CREATE), DML (INSERT, UPDATE, DELETE), and relational theory by Edgar F. Codd, who invented the theoretical basis for relational databases.
• Advanced SQL queries, such as Window functions for performing advanced aggregations without additional subqueries within the current query. Or, CTEs are a powerful syntax that allows for better readability, creating aliases for sub-queries, and even recursion is possible.
• ACID properties and transactions within databases such as Postgres, MySQL, and DuckDB.
• Learn the differences between OLTP vs. OLAP with a beginner's guide. Also, check out an explainer of What is OLAP?
• dbt core and SQLMesh: frameworks to encapsulate SQL into a structure that can be versioned, tested, and run in order, including well-documented lineage as a web page.

Version Control

If you use SQL, very quickly you'll want to work with coworkers and want to version it so as not to lose essential changes or to roll back added bugs.

Therefore, you need version control. This short chapter gives you some starting points for the most common one.

• What is version control - a visual guide to version control.
• The tool, Git fundamentals.
• GitHub/GitLab Collaboration: Learn about platforms like GitHub and GitLab for hosting Git repositories and for sharing and collaborating with others. Main features include Pull Requests and Issues for communicating your changes in a structured way.
• Learn the different git workflows. Also, check out git worktree. Although it's a bit advanced, it's good to know it's there, especially if you need to work on different branches simultaneously without constantly stashing or committing your unfinished changes before switching to another branch.

There are many more helpful topics, such as GitHub Actions/Pipelines for CI/CD or basic automation (uploading documents to a website, checking grammar automatically before publishing, etc.). However, for the first week, let's keep it simple and move on to the next chapter: Linux and scripting.

Environment Setup, Linux Fundamentals & Basic Scripting

Set up your development environment and master essential Linux skills for data engineering. This depends on your operating system of choice, too, but most data engineering tasks are typically run on servers. In almost all cases, they are executed on Unix-based systems. That's why Linux fundamentals are key to elevating your data engineering skills.

Below are the resources and roadmap to learn about these topics:

• Bash scripting essentials, starting with the basics of bash scripting, including variables, commands, inputs/outputs, and debugging. Alternatively, use this course with an interactive command line in the browser: Linux command line basics (Paid).
• Package managers (Apt, yum, Homebrew, Wget): How to Use Package Managers in Linux? (APT and YUM) and Homebrew for macOS
• SSH and remote connections: Connecting to a remote server and fixing a DAG or updating a script on the fly.
• Development environment setup: Simple yet powerful dev setups: MacOS setup with pyenv, docker, uv, VSCode, Linux (Omakub, Omarchy) and Windows Setup for data scientist.
• Cron jobs and scheduling: Basic automation scripts without the need for a heavy tool.

Congratulations, this wraps up week one. If you have watched, experimented, and taken notes, you now possess the fundamentals of data engineering and, frankly, any engineering or technical job. Give yourself some time to ponder and review, and then proceed to week two below.

Data Modeling & Warehousing

To avoid creating independent SQL queries and persistent data tables without connected data sets, we need to model our data with a more holistic approach.

This is where the concepts of so-called data modeling and the long-standing term data warehousing originate. The sole purpose of these is to organize data optimized for consumption, whereas data in Postgres and other operational databases is optimized for storage.

This chapter will teach you and point you to key knowledge to prepare you to model enterprise workloads.

• Data modeling is a significant one, and somewhat underappreciated these days. However, with the rise of AI and automation, it hasn't been more critical to learn.
  • Dimensional modeling with a star schema.
  • Snowflake schema vs star schema: Understanding when to use normalized vs denormalized dimension tables.
  • Data normalization: 1NF, 2NF, 3NF principles for reducing data redundancy
  • Fact tables vs dimension tables: Understanding measures, metrics, and descriptive attributes.
  • Granularity is a key concept to understand, so your facts will not suffer from too low detail that is slow, or too high-level detail that loses crucial information when drilling down in a dashboard.
• Data warehouse design methodologies:
  • Kimball methodology: Bottom-up, business process-focused approach.
  • Inmon methodology: Top-down, enterprise data model approach.
  • Data Vault 2.0: An approach with hubs, links, and satellites for agility and scalability.
• Advanced modeling concepts:
  • Slowly changing dimensions: Handling changes in dimension data over time.
  • Bridge tables and many-to-many relationships: Managing complex relationships in dimensional models.

Python for Data Engineering & Workflow Orchestration

After SQL, Python is the next most important language to learn. While it's beneficial to have deep knowledge about SQL, and you only need preliminary Linux skills to get around a server and run some commands from the command line, Python is the utility language of data. It's the glue code that connects everything you can't achieve with SQL, most notably working with external systems and orchestrating your data workflows with Python libraries and frameworks.

Orchestration and other more modern tools help you automate and organize, as well as version your data tasks and pipelines.

• Starting with a Python general introduction.
• DataFrame and data manipulation with Pandas, Polars and DuckDB. Navigating the Dataframe Landscape and DuckDB vs Pandas vs Polars for Python Developers, Video Format
• Python libraries for Data validation with Pydantic or Data Testing with pytest.
• Utilitarian Python knowledge. Connecting to any API quickly with FastAPI.
• Workflow orchestration is almost as important as the Python language itself. Apache Airflow is the biggest name. You learn about task dependencies and scheduling, as well as how orchestration and integration of data tools and stacks work through workflow management. Also, check out related DAG design patterns for guidance on designing pipelines that are easy to maintain and separate business logic from technical logic in an organized and conventional manner.

Cloud Platforms Introduction

Getting to know major cloud platform providers can save you a significant amount of time and enhance your employability because you know how to work around permissions, the services provided, and how to automate specific tasks. Ensure you select the right provider based on your location and primary use, or the company you prefer to work for.

• Introduction to AWS, Azure, or Google Cloud. Vital is permission management, such as security and IAM basics, on all platforms.
• Dedicated data services: MotherDuck, BigQuery, Fabric, hosted Airflow (Azure & AWS).
• Object Storage or blob storage setup on all platforms.

Depending on where your resume positions you, you'll do different work. But some sort of analytics through business intelligence (BI) is always involved. Visualizing your data and showing it in a way that makes sense immediately is hard; that's where BI tools and data visualization come into play.

• Introduction to BI tools and using notebooks. Others are Jupyter Notebooks, Hex, DeepNote, and many more. Check Jupyter notebooks for analytics, which is a super helpful toolkit for data analysis and iteration.
• Metrics and KPI design with metrics layers and semantics.
• Data visualization best practices. Tools like color management and a high-level grammar of interactive graphics help understand data presentation. Hichert SUCCESS Rules is another great option, although it is only available in German. Check also Data Visualization with Hex/Preset and DuckDB/MotherDuck.
• Self-service analytics enables business people to serve themselves.

This concludes Week Two. You're ready to tackle the advanced topics in Week three.

Stream Processing & Event-Driven Data

Event-driven approaches or integrating your data as a stream, end-to-end from source to your analytics, is sometimes a must and business-critical, especially for ad-tech or sports, where you need live results that are as up-to-date as possible.

Understanding stream processing fundamentals is especially beneficial for validating users' requests for real-time data insights, as they will often ask for it, but it's not always necessary.

• Event-driven architecture and design practices: How do they differ from batch loads? Key players in this category are Apache Kafka and Flink.
• Real-time analytics patterns: Change Data Capture (CDC) and the difference in propagating that stream compared to batch. See Postgres change data capture possibilities.

Data Quality & Testing

Implementing robust data quality frameworks and testing strategies is crucial for maintaining a stable data platform. Most often, it's quick to set up a data platform, or a stack to extract analytics from your data, but doing it stably and with high data quality is an entirely different job. The tools in this chapter will help you with that.

• Great Expectations and other data quality frameworks.
• Unit testing for data pipelines: How to test your data and pipelines in an automated fashion.
• Data lineage and governance: How to get the lineage of your data flow.
• A Beginner’s Guide for Observability. Be sure to learn about Data Contracts, a concept for defining data interfaces between data and business teams.
• Metadata Management: Data discovery with data catalogs, ratings of datasets to know which ones are actively used and of good quality. Check also the Schema registry management to handle metadata.

Cost Optimization & Resource Management

Most of the time, especially if you use cloud solutions, the price to pay for these services is relatively high. Therefore, stopping the creation of a heavy temp table on an hourly basis can save a significant amount of costs. Consequently, it's crucial to debug heavy SQL queries or wasted orchestration tasks, including orphaned ones that aren't connected to any upstream datasets or that aren't in use.

Stacks that don't run in the cloud are optimized differently. Here, you don't pay for cloud services, but to run your own. That's why you optimize for team members and tasks. As data engineering tasks are elaborate, spending time on the right tasks can save a lot of money, too.

In the past, it was referred to as performance tuning. At that time, we were optimizing for speed, which remains the case today. Similarly, if you maximize performance, you also improve cost efficiency at the same time, as it runs for shorter periods. Over time, this can result in significant savings.

• Cloud cost monitoring and optimization: Tools to monitor the cost and usage of data engineering tasks.
• Performance Tuning: Indexing, partitioning strategies, and caching mechanisms are important components, as is query optimization for better efficiency and lower cost.
• Storage tiering and lifecycle management

Infrastructure as Code & DevOps

Infrastructure management and deploying new software in an automated fashion typically occurs through Infrastructure as Code (IaC) using Kubernetes or a similar platform. That's why it's good to have preliminary knowledge about these tools and when to use them.

• Docker containerization is a good start; here's a beginner's guide.
• Kubernetes and Terraform basics.
• Monitoring and logging explained.
• Advanced CI/CD for deploying entire data stacks and data platforms.

That's it. This is a three-week roadmap with numerous courses and links to help you learn data engineering. Let's take a break and dive into the final part, observing what we've learned throughout these three weeks.

What's Next?

All of it will help you build your portfolio and land your dream data engineering role. Each week builds upon the previous, creating a comprehensive learning experience that mirrors real-world data engineering challenges.

Throughout the entire process, it's beneficial to build your online portfolio, where you showcase your data engineering learnings, Git projects, website, and links to hackathons you participated in, among other things that demonstrate your motivation. Above all, sharing is also fun; people will reach out to you after reading your content, especially if they learn from it too.

Remember to take your time learning new concepts. If you give yourself time to digest, you learn more easily, you'll be able to recall specific terms better, and it's easier to connect the knowledge—this is how our brains learn.

Consistency is key. Dedicate 1-2 hours daily for a couple of weeks, and you'll be amazed at what compounding and consistent learning can achieve.

I hope you enjoyed this write-up. If so, you may also find the essential toolkit article for data engineers, available in Part 1 and Part 2, or check an End-To-End Data Engineering Project with Python and DuckDB.

If you want more? Check out the Mastering Essentials resources by MotherDuck, or follow their YouTube channel for additional resources. If you like DuckDB and need a cost-efficient data warehouse or data engine, check out MotherDuck for free.

Further in-depth content can be found and learned through bootcamps, events, and courses. Please don't give up; it's a lot to take in when you start. Begin with the fundamentals as guided in this roadmap, and also follow your interests. It's better to learn something that might not be suitable right now, but because you are passionate about it, learning comes much more easily. And over time, that knowledge may be put to use at a crucial moment later on.