6 technical skills every data engineer should have

I only publish 1/4 of my deep-dive data engineering articles on Medium. Access the full repository here.

Intro

If I were to enter the data engineering field right now, I would feel extremely overwhelmed. Tons of tools, tons of skills, and I didn't even put AI-related stuff on the table.

I read somewhere that the most effective approach to learning in this era is to learn things that would not change. Looking back on my journey as a data engineer, I realized that, indeed, there are a few things like that.

In this article, I shared the six technical skills that I believe every data engineers should equip themself with. They won't be obsolete anytime soon.

Before we move on

For me, the most important aspect of learning something is having a solid feedback loop and getting someone to provide you with feedback: your friend, your senior colleague, or the internet community.

Asking Gemini or ChatGPT to act like someone who knows what you're doing (e.g., "imagine you're a data engineer with 20 years of experience, help me to give feedback on this transform SQL script") is not a bad option.

The key is to know whether what you're doing is on the right track.

Data modeling

Why?

You will soon realize a fact that every subsequent process — every pipeline, every query, every machine learning model — is built upon the structure defined by the data model.

If the data warehouse is a building, data modeling is the blueprint. Without it, we have no clue what to do next. We can survive several months of blindly loading and querying data; however, the nightmare soon comes:

• Maintenance Cost: Without a clear blueprint, we are left with a mess of SQL scripts and tables, making maintenance a costly and frustrating process
• Inefficient Processing: Queries against a poorly designed structure are usually slow and resource-intensive
• Data Integrity Problems: Without enforcing relationships and constraints, it's hard to ensure data integrity, which could render the information unreliable.
• Weird Insights: Without reliable data and standard ways to load and retrieve data, a high chance that analysts and data scientists create bad reports and ML models.
• No Trust: Business users then use these weird insights to make decisions, which can lead to costly mistakes. The process soon has one more step: check if the insight is valid.

Let's imagine a brighter scenario: if we have our nicely designed data modeling sitting there:

• A Common Language: With data modeling, we have a shared, unified view of the organization's data, facilitating clear communication between stakeholders.
• Data Quality and Integrity: Modeling constraints and relationships gives us a good starting point for ensuring data quality.

• Reduces Errors: A data analyst knows exactly how to query a piece of insight. A data engineer knows exactly the location where the data is going to be loaded. Every necessary transformation is performed beforehand, leaving the data nicely organized and ready to be served. A good data model limits errors as much as possible.

How to learn it?

The first thing we need to know is that data modeling is a process that moves from high-level business concepts to low-level technical implementation:

• Conceptual Data Model: This is the highest-level view, focused on capturing business requirements. It identifies the core business entities (e.g., Customers, Products, Orders) and the relationships between them. We don't care about the underlying technology at this phase. The conceptual model is used to align with stakeholders.
• Logical Data Model: This layer has more detail than the conceptual model. We add attributes for each entity (e.g., Customer has first_name, email, customer_id), identify primary keys, and data types (e.g., string, integer). We also don't care about the technology here. The logical model acts as a bridge between business concepts and the physical implementation.
• Physical Data Model: This is the concrete implementation blueprint for a specific database system. It translates the logical model into tables, columns, constraints, or optimization techniques, such as clustering or partitioning. We use the terminology and features of the chosen technology here (e.g., BigQuery, Snowflake, Databricks).

Next is exploring data modeling methodologies.

The Kimball Method (Dimensional Modeling) offers a bottom-up approach optimized for fast and understandable analytics. The method is well documented in The Data Warehouse Toolkit, which is the definitive guide to dimensional modeling.

Its core structure is the star schema, consisting of a central fact table containing quantitative measurements or events surrounded by dimension tables that provide descriptive context (e.g., customer details, product information, dates).

In contrast, the Inmon method's approach is top-down, advocating for the creation of a centralized, highly normalized (typically to the Third Normal Form, or 3NF) Enterprise Data Warehouse (EDW). This EDW serves as the single source of truth.

Departmental data marts are then built from this normalized core to meet specific analytical needs. This method is more complex and less agile, but excels at large-scale data integration and minimizing data redundancy.

I recommend starting with the Kimball method first, as I personally see it's easier to get started and practice. If I have to learn Kimball again, I will try to grasp its fundamentals from the book, including facts, dimensions, and the 4-step processes. If you don't want to spend much time on the theory, a reading guide is what you need.

After that, we need to practice:

• Select your favorite business domain.
• Begin by defining the business questions. For example, "What are the key performance metrics we need to track?" The answers (e.g., daily sales revenue, number of new subscribers) will form the basis of the fact tables.
• Then, ask "How do we want to slice and dice these metrics?" The answers (e.g., by customer geography, by product category, by time) will define the dimension tables.
• Construct a simple star schema.
• Check if your modeling could help you seamlessly answer your questions.
• Adjust and improve your modeling if needed.
• Get feedback and iterate.

Git

Why?

We rarely work alone. We rarely work with a single version of the data pipeline, a Python application, or a SQL script. Organizations need a way to version control their work and enable collaboration.

Git is the standard way to do this. It is an open-source distributed version control system developed by the Linux development community in 2005. If you are entirely new to Git, you may find it takes a considerable amount of time. (Why do we need to git clone while we can zip and download the repo?).

However, you will soon realize the version control capabilities of Git, saving you a significant amount of time on development and making it easier to share your work. No matter how good your code is, if you don't know Git, you will have trouble working with others.

Preventing weird things from happening on production, tracing bugs (why the previous version didn't see this bug), building CI/CD pipelines, isolating your work from teammates, etc

Tons of benefits and practical usage.

How to learn it?

Among the skills listed in this article, I think Git will be the easiest to learn. A toy project on GitHub, having Git installed on your laptop, and practicing commands.

The key is to understand its fundamentals before practicing.

Git stores data as snapshots, providing powerful branching by leveraging pointers.

A commit is a snapshot of the entire project at a specific point in time. It's a complete picture of every file and folder in the repository, exactly as they were at the time when the commit was made. A commit will have pointers pointing to its parent commit.

A Git branch is simply a movable pointer to a commit.

For more details, you can check my 15-minute guide on learning Git here: Git for Data Engineers.

SQL

Why?

If you work in the data field, whether you're a data engineer, a data analyst, or a data scientist, you "speak" SQL. The language was designed in the 1970s to manipulate and retrieve data from relational databases. Since then, it has gained increasing adoption worldwide as the primary interface for working with these databases.

The evolution of the OLAP database and the rise of transformation tools, such as DBT or SQLMesh, make SQL an attractive choice for data transformation, which was mainly handled by procedural languages like Java or Python. Some cloud data warehouses, such as BigQuery, even allow users to utilize SQL for machine learning.

Data engineers use SQL for many things.

How to learn it?

First, learn the syntax.

Start simple, working with one table: SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, SUM, COUNT, LIMIT… to retrieve data, and INSERT, UPDATE, DELETE to modify data.

Then, we move on to working with more than one table. Learn the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Next, we make it a bit harder when moving to WINDOW FUNCTION. Like GROUP BY, it performs calculations on groups of rows, but there are differences:

• GROUP BY collapses rows. It takes multiple rows and aggregates them into a single summary row. For example, SELECT country, SUM(sales) FROM products GROUP BY country will return one row for each category, showing the total sales for that category.

• Window functions operate on a "window" of rows but do not collapse them. They return a value for each row based on the defined window. For example,
• SELECT country, SUM(sales) OVER (PARTITION BY country) AS category_total_sales FROM products will return all the original product rows. However, it adds a new column (category_total_sales) to each row, showing the total sales for the category to which the product belongs.

Then, we come to CTEs (Common Table Expressions). They are essential for breaking down complex queries into logical, readable steps, which improves maintainability.

However, learning syntax alone is not enough. Data engineers must know what happens under the hood, as we not only write SQL queries but also write optimized ones. Understanding the execution order is crucial. A complete query will be executed like this.

1. FROM / JOIN: The database first identifies the required tables and performs any joins to create the complete dataset.
2. WHERE: It then filters this dataset, skipping rows that do not meet the conditions. (e.g., X > 3)
3. GROUP BY: The remaining rows are grouped based on the values in one or more columns, preparing them for aggregation (e.g., SUM, COUNT, AVG…)
4. HAVING: After the rows are grouped, this clause filters out records that do not meet the aggregate conditions (e.g., SUM(X) > 3)
5. SELECT: The engine processes the SELECT list to determine which columns, expressions, and aggregated values to include in the final result. Window functions are also processed here.
6. DISTINCT: If specified, duplicate rows are removed from the result set.
7. ORDER BY: The final result set is sorted according to the specified columns or expressions.
8. LIMIT / OFFSET: The query limits the output to a specific number of rows.

Note: Some databases, such as BigQuery, also support a very useful clause called QUALIFY, which filters records based on the value of the window function.

Python

Why?

As I mentioned, data engineers utilize SQL for various purposes, but not for everything. Python can make up for that.

You see repetitive tasks and want to automate them. Python can do it.

You face complex transformations that are difficult to express in SQL. Python with PySpark, Pandas, or Polars can help.

Data comes from many systems. Python can help pull them via the REST API.

You need to orchestrate many data pipeline steps. Python can help with tools like Airflow or Dagster.

Or, you want to build a data application. Python can help with Streamlit or a backend framework like FastAPI.

Learning Python is a must.

How to learn it?

Learning Python is quite easy compared to other languages, as its syntax is much simpler. There are numerous resources available to help us learn how to write a function, an if clause, or a class in Python.

However, learning syntax is never enough. Again, we rarely work alone. Writing code is not hard, but writing readable, maintainable, and extendable code needs time. Many of us start the journey by self-learning Python; thus, writing messy code is understandable.

Over time, we need to care for others who work with us. No matter how good your Python program performs, if your colleagues don't understand what you're doing or find it extremely challenging to extend your work, your shiny code is useless.

Pay attention to writing organized code as soon as possible. Learn design patterns (Python-general or data-pipeline-specific), coding principles like SOLID, or read Clean Code.

This might be boring at first, as it won't give you the same feeling as your Python program runs for the first time. But when you add a piece of code without crashing the application, see your colleagues inherit your work seamlessly without pain, or someone takes your work as a reference on how they organize their code, the feeling is even more satisfying.

OLAP system

Why?

Oooh, this is my favorite one.

A data warehouse is a logical entity that consolidates data from multiple sources to serve analytics demand. However, the warehouse needs a physical database to store and expose the data. In the past, organizations also used the database that backed their application for this purpose.

Over time, more and more companies have realized they need to extract insights from data to gain business advantages. Database researchers saw an opportunity. Transactional databases (OLTP) cannot serve analytics workloads well because they were not designed to do so.

The boom of OLAP systems began. BigQuery, Databricks, Snowflake, Redshift, or Clickhouse. They were designed to handle TBs or even PBs of queries with the most advanced optimization techniques.

Nowadays, an OLAP database is the most important component of the data infrastructure. Most of the data-related tasks occur here: ingesting data, transforming data, serving data, or enforcing data governance.

How to learn it?

The key to learning any OLAP system is understanding two things: how it processes data and how it stores the data. The good news is that these systems share some commonalities:

Overall: Most of the OLAP systems have a share-nothing architecture; compute and storage layers are separate to achieve high scalability.

Processing: Due to the high data volume, data is usually processed by multiple workers. These systems also employ techniques such as vectorized execution and/or code generation to enhance performance.

Storage:

• Data in the OLAP system is stored in column or hybrid format.

• The data also has rich metadata to help the query engine skip as much data as possible when processing a query.

• To implement version control and support workload isolation ('I' in ACID), these systems don't allow overwriting data, as written data is immutable, and changes result in writing new files.

• To achieve scalability and cost efficiency, most systems use object storage (or storage with shared object storage characteristics).

Keep these things in mind, and you can explore any OLAP systems you want. For a specific solution, the best approach is to try it. Most cloud data warehouses offer a trial period for their service.

Try it, apply their recommended best practices, see how it helps with data governance, understand its pricing model, and how it can integrate with other systems.

The rise of the lakehouse paradigm means that OLAP systems are no longer the exclusive domain of vendors. A query engine (Spark, Trino) + object storage (GCS, S3) + table format (Delta Lake, Iceberg), and you have your own OLAP systems. The observation above (Overall, Processing, Storage) remains applicable here.

Compared to the cloud data warehouse, managing these systems on your own will take more effort, but in return, you have more control over your OLAP system.

Orchestration

When we're working on a pet project, a single SQL script or a PySpark application is enough. However, things get complicated in production where your team has many dbt models and Python scripts that need to be run.

More importantly, they need to be run in order. A task to load data into a staging table must complete successfully before a transformation task can begin, which in turn must finish before a final reporting table is updated.

Managing this complexity manually is not a solution. Tools, such as Apache Airflow and Dagster, come to the rescue. In addition to the OLAP system, a data orchestration tool is an indispensable part of the data infrastructure.

It automates the scheduling, execution, monitoring, and management of whole data workflows. These workflows are typically represented as Directed Acyclic Graphs (DAGs), where each node in the graph is a task and the directed edges represent dependencies between tasks.

The necessity for these tools arises from key requirements of production-grade data systems:

• Dependency Management: Orchestrators ensure that a downstream task only runs after all its upstream dependencies have completed. Exactly what we want when we expect something like pulling an API from two sources, then joining them together.
• Scheduling: They provide sophisticated scheduling capabilities beyond simple time-based triggers, allowing for cron-like schedules, event-based triggers (e.g., run when a file arrives in S3), or data-aware schedules (e.g., run when an upstream data asset is available).
• Error Handling and Retries: Everything could fail; network issues, API servers down, or SQL syntax errors are common. These systems provide built-in mechanisms to retry failed tasks and perform data backfilling automatically.
• Monitoring and Visibility: Orchestration tools also offer user interfaces that provide a complete, visual overview of all data pipelines. They allow engineers to monitor the status of DAG runs, inspect logs for individual tasks, and receive alerts on failures, providing critical visibility into the health of the data platform.

How to learn it?

Keep these in mind

Task-Based vs. Asset-Based Orchestration: You will see these two approaches in most orchestration tools. The first approach states, "Run this job, then that job" (Airflow was designed with this approach), while the latter asks, "Keep these assets (tables, models…) up-to-date?" (Dagster was designed with this approach).

Idempotency and Backfilling: These are two critical concepts for a reliable data pipeline.

• Idempotency means that running a task multiple times with the same input produces the same result (e.g., f(x) = x * 1). An idempotent pipeline can be safely retried after a failure without creating duplicate data or other side effects. A common pattern for achieving idempotency is to design jobs that overwrite a specific data partition(e.g., a specific day's data) rather than appending to it.

• Backfilling is the process of running a pipeline for historical periods to reprocess data, perhaps to fix a bug in the original logic or incorporate late-arriving data. Idempotent design is a prerequisite for safe and easy backfilling. Orchestration tools provide the mechanisms to trigger and manage these backfill runs across specific date ranges.

For a specific tool:

• Find out how your credentials, such as API tokens or cloud service accounts, are managed.
• Understand how these tools isolate tasks.
• Dependencies: Dagster supports each data pipeline having its own set of Python dependencies, while Airflow manages dependencies at the global level.
• Resource isolation: Each task in Dagster runs in a dedicated Kubernetes pod. Initially, Airflow was designed to have a set of Celery workers, and tasks can run on the same worker. Later, Airflow also supports running tasks as a Kubernetes pod.
• Understanding these factors will make it easier for you to maintain and monitor a production-grade environment.
• What other abstractions besides the provided ones can I extend the functionality of the tool with? (e.g., Airflow has Hook and Operator concepts to allow users to customize)
• These tools already have a lot of support for familiar data sources and destinations. However, it's not comprehensive. Learning how to extend the support for your desired data source/destination, following the tool's best practices, is crucial.

The next step is to run this tool on your laptop and write some DAGs. Apache Airflow is a great place to start thanks to its massive community and a vast number of integrations.

Outro

Thank you for reading to the end.

In this article, I outlined six technical skills that I think every data engineer should prioritize acquiring. I shared each skill, why it matters, and my experience in learning and using it effectively, based on my observations and experience.

Of course, there are other things we need to learn, such as data governance, cloud infrastructure, Docker, and bash scripting. However, I believe acquiring data modeling, Git, SQL, Python, OLAP systems, and Orchestration will give us a solid foundation for our careers.

And don't forget the feedback loop. It's the decisive factor in how fast you master a skill.

Now, see you next time.