Demystifying Data Engineering: A Comprehensive Overview

From databases, data lakes, data warehouses, ETL, ELT, batch processing vs streaming, data visualization up to workflow management, all these concepts will have no secrets for you.
data engineering
data pipelines
data warehouse
batch processing
real-time processing


In the modern world, data is the new oil 🔥 It powers businesses, drives decision-making, and fuels innovation. As such, understanding how to manage, process, and leverage data is crucial. This is where data engineering comes in 🏃‍♂️💨

Data engineering is the backbone of any data-driven organization. It involves the design and construction of systems for collecting, storing, processing, and analyzing data 🔬 In this article, we aim to demystify the field of data engineering, breaking down its key concepts and tools in a concise and digestible manner.

Whether you’re a seasoned professional looking to brush up on your knowledge, or a newcomer eager to learn, this article is for you. Let’s dive in ! 🏊‍♂️

But before that, sit back, relax, and enjoy these images of futuristic industries, brought to life by the power of AI. These visuals are not actual representations of these technologies, but rather, they’re imaginative metaphors of what the future of data engineer could look like. Enjoy the journey !

Data Modeling

Data modeling is a critical first step in the data engineering process. It’s all about creating a conceptual representation of data objects, the relationships between different data objects, and the rules they abide by.

There are several key concepts in data modeling that you should be aware of:

  • Normalization 📚: This is a database design technique used to minimize data redundancy and avoid data anomalies. The process involves dividing a database into two or more tables and defining relationships between the tables. The main goal of normalization is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
  • Denormalization 🚀: This is the process of improving read performance of a database at the expense of losing some write performance by adding redundant copies of data. It’s essentially the opposite of normalization. Denormalization is used when the database is mostly read-intensive and can benefit from faster query performance.
  • Star schema 🌟: This is the simplest style of data mart schema in a data warehouse. The star schema consists of one or more fact tables referencing any number of dimension tables. The star schema gets its name from the physical model’s resemblance to a star shape with a fact table at its center and the dimension tables surrounding it representing the star’s points.
  • Snowflake schema ❄️: This is a more complex database schema. It’s an extension of the star schema where each point of the star explodes into more points. In a snowflake schema, dimension tables are normalized, splitting data into additional tables, which can lead to fewer data redundancy and improved query performance. However, this comes at the cost of more complex queries and reduced query performance.

In essence, data modeling is a balancing act between optimizing data retrieval and maintaining data integrity and accuracy. Whether you choose normalization or denormalization, star schema or snowflake schema, will depend on your specific use case and the trade-offs you’re willing to make 🤹‍♀️

Databases, Data Warehouses, and Data Lakes

Before we delve into the specifics, it’s important to understand the fundamental building blocks of any data engineering architecture - Databases, Data Warehouses, and Data Lakes. These are the storage systems where your data will reside and each serves a unique purpose 🏗️

Databases are primarily designed for transactional processing (OLTP) and are optimized for maintaining, adding, deleting, or updating data in real-time. They are typically used for front-end applications where speed and efficiency are crucial. Think of databases as your grocery list, where you keep track of what you need to buy, add new items, and cross off what you’ve purchased. 🛒

Data Warehouses, on the other hand, are designed for Online Analytical Processing (OLAP). They are optimized for reading operations and are typically used for data analysis and reporting. Data warehouses store historical data from various sources, making them ideal for identifying trends and gaining insights over time. Imagine a data warehouse as a library, where you can find books (data) on different topics from different times 📚

Data Lakes are designed to store a vast amount of raw data in its native format until it’s needed. Unlike data warehouses, which store data in a structured and processed format, data lakes store unprocessed data, offering more flexibility. You can think of a data lake as a real lake filled with different kinds of submarine creatures. You don’t know what kinds of fish you’ll catch until you throw in your line 🎣

Each play a crucial role in the data engineering landscape. Understanding their differences and uses is key to building effective data pipelines.

If you’d like to have a deeper understanding about the key differences between databases and warehouses, feel free to read my Cloud SQL vs BigQuery: Choosing The Right Tool For Your Data Needs article.

Data Extraction, Transformation, and Loading (ETL)

Moving on to the next key concept in data engineering - ETL. Standing for Extract, Transform, Load, and it’s a process that involves:

  • Extracting data from different source systems. This could be databases, CRM systems, files, or any other data storage system. The goal here is to retrieve all the necessary data for further processing 📦
  • Transforming the extracted data. This could involve cleaning the data, handling missing or incomplete data, validating it, or converting it into a suitable format. The aim is to prepare the data for loading into the target system. 🛠️
  • Loading the transformed data into the target system, which could be a data warehouse or a data lake. This is where the data will be stored and used for analysis 🚚

ETL is a crucial part of data engineering because it allows data to be moved from various sources, processed, and loaded into a central location where it can be analyzed and used to generate insights. It’s like a data assembly line, where raw data enters, gets processed, and comes out as useful, actionable information 🏭

The ELT strategy, which stands for Extract Load Transform, provides a higher degree of flexibility compared to the conventional ETL process. The key advantage lies in the sequence of operations - by loading the raw data into the target system prior to its transformation, you gain the freedom to manipulate and utilize the data based on your evolving requirements. This becomes especially beneficial in the context of big data, where the sheer volume of information can render transformations before loading somewhat unfeasible.

Whether you choose ETL or ELT will depend on your specific needs, the nature of your data, and the capabilities of your data storage and processing systems. The key is to understand the strengths and limitations of each approach and choose the one that best fits your project. ⚖️

Data Processing: Batch vs. Streaming

In data engineering, processing data can be done in two primary ways: batch processing and real-time processing.

Batch Processing is like baking a large batch of cookies 🍪 You gather all the ingredients (data), mix them together (process), and then bake them (output). This method is efficient when dealing with large volumes of data that don’t require immediate action. It’s often used for tasks that are CPU-intensive and require a significant amount of data, like training machine learning models or generating reports.

On the other hand, Real-Time Processing, also called Streaming, is like having a conversation 🗣️ Each sentence you utter (data) is immediately processed and responded to (output). This method is used when you need to take immediate action based on the data, like fraud detection or real-time analytics. It’s all about speed and low latency.

Choosing between batch and real-time processing depends on your specific use case and the nature of your data. It’s important to understand the trade-offs and choose the method that best suits your needs.

Data Quality and Data Governance

Data Quality and Data Governance are two crucial aspects of data engineering that ensure the reliability and usability of data.

Data Quality refers to the condition of a set of values of qualitative or quantitative variables. It’s about the accuracy, consistency, and completeness of data. Poor data quality can lead to inaccurate decision-making and can negatively impact the overall efficiency of an organization 📉

Data Governance, on the other hand, is the overall management of the availability, usability, integrity, and security of data used in an enterprise. It’s a set of processes that ensures that important data assets are formally managed throughout the enterprise 🏢

In essence, Data Quality is about “doing things right,” while Data Governance is about “doing the right things.” Both are essential for any organization that wants to make informed decisions based on their data. 🎯

There are several strategies and tools available to maintain data quality and implement data governance. These range from data cleaning techniques to comprehensive data governance frameworks. The choice depends on the specific needs and scale of your organization. 🛠️

Remember, garbage in, garbage out. Ensuring high data quality and implementing robust data governance is not just a good-to-have, it’s a must-have in today’s data-driven world 🌐

Visualizing Data: Reporting and Data Visualization Tools

Data is only as good as the insights it can provide, and that’s where data visualization comes into play. Data visualization is the graphical representation of information and data. It uses visual elements like charts, graphs, and maps to provide an accessible way to see and understand trends, outliers, and patterns in data. 🎨

There are numerous data visualization tools available today, each with its own strengths and weaknesses. Here are a few popular ones:

  • PowerBI: a business analytics tool suite provided by Microsoft. It allows you to create interactive visualizations, reports, and dashboards with self-service business intelligence capabilities. PowerBI is highly integrated with other Microsoft products which makes it a go-to choice for organizations heavily using Microsoft services 💼
  • Tableau: Tableau is known for its robust and flexible data visualization capabilities. It allows you to create a wide range of different visualizations to interactively present data and provide insights. Tableau supports a wide variety of data sources, from spreadsheets to SQL databases and cloud services, making it a versatile choice for data visualization. However, it can be more complex and require more training to use effectively compared to some other tools 🖼️
  • Looker: a modern data platform that provides a web-based interface for exploring and visualizing your data. It allows you to create and share custom data models and visualizations, and it’s known for its strong collaboration features. Looker is highly customizable and offers powerful data exploration features, but it might be overkill for simple visualization needs 👀
  • Metabase: an open-source data visualization tool that is known for its user-friendly interface. It allows users to create, save and share custom visualizations and dashboards without needing any coding skills. Metabase is a great choice for smaller teams or projects with a tight budget, but it may lack some of the advanced features provided by premium tools 📚

Each of these tools has its own strengths and weaknesses, and the best choice depends on your specific needs and circumstances. It’s important to consider factors like the size and complexity of your data, the specific types of visualizations you want to create, the level of customization you need, and your budget when choosing a data visualization tool 🎯

Workflow Management

Workflow management tools play a crucial role in data engineering by automating and coordinating tasks. These tools allow data engineers to define workflows, monitor their status, and ensure that they complete successfully. They also handle dependencies between tasks and provide mechanisms for error handling and retrying failed tasks.

Think of workflow management tools, or orchestrators, as the conductors of an orchestra. Without a conductor, each musician (or task, in the context of data engineering) might play their part well, but the overall performance could lack coordination and harmony 🎼

Similarly, an orchestrator in data engineering ensures that each task in a workflow is executed at the right time, in the right order, thus minimizing the amount of manual steps 🚶‍♂️

Let’s take a look at some popular workflow management tools:

  • Dagster: Dagster is an open-source data orchestrator. It lets you define pipelines in terms of the data flow between reusable, logical components, then test locally and run anywhere. With a unified view of pipelines and assets, Dagster enables visibility into data dependencies and allows you to catch issues before production 🎢
  • Airflow: Apache Airflow is a platform to programmatically author, schedule and monitor workflows. Airflow allows users to launch multi-step pipelines using a simple Python script, and it includes numerous integrations with popular data tools. However, it can be complex to set up and manage. 🌬️
  • Jenkins: Jenkins is an open-source automation server. It provides hundreds of plugins to support building, deploying, and automating any project. Jenkins is more of a general-purpose automation tool and is often used for CI/CD pipelines, but it can also be used for data workflows. However, it may not provide as many data-specific features as other tools 🛠️

It’s important to consider factors like the tool’s ease of use, scalability, community support, and integration capabilities when making your choice 🤔

The Role of CI/CD

Continuous Integration (CI) and Continuous Deployment (CD) is a crucial part of modern data engineering. It’s like a well-oiled assembly line in a factory.

Its role is to function so that each piece of code is integrated, tested, and deployed in a streamlined and automated manner. This ensures that the codebase is always in a deployable state and reduces the time and effort required to release new features or fixes.

Popular CI/CD tools include GitHub Actions, Jenkins, and CircleCI.

Infrastructure as Code (IaC)

Infrastructure as Code (IaC) is the practice of managing and provisioning computing infrastructure through machine-readable definition files, rather than manual hardware configuration or interactive configuration tools. It’s like building a city using blueprints and automated machinery, rather than constructing each building by hand 🏗️

This method applies software engineering principles to achieve reproducibility in deploying cloud components. In essence, we utilize code for deploying these components rather than relying on User Interface (UI) interactions.

This approach ensures consistency, reduces errors, and makes the process of setting up and maintaining infrastructure more efficient. Terraform, Ansible, and Pulumi are some of the popular IaC tools.

On-Premise vs Cloud Solutions

Choosing between Cloud and On-Premise solutions is like choosing between renting and buying a house 🏡

Renting (cloud) offers flexibility and saves you from maintenance hassles, but you have less control. Buying (on-premise) gives you full control and ownership, but comes with maintenance responsibilities and higher upfront costs 📈

The choice depends on your specific needs and resources. Cloud solutions are provided by vendors like AWS, Google Cloud, and Azure, while on-premise solutions require setting up and maintaining your own hardware and software 🔧

Generally, Cloud solutions are suitable for businesses of all sizes, but they are especially beneficial for small to mid-sized companies. On the other hand, On-Premise solutions are an option for companies willing to invest in engineering roles to reduce cloud costs over the long term 📉


In the ever-evolving data world, understanding the fundamentals of data engineering is crucial 🔎

From data modeling to choosing between cloud and on-premise solutions, each aspect plays a significant role in how we handle and derive insights from data. As we’ve seen, there are numerous tools and methodologies at our disposal 🛠️

The key is to choose what best fits your organization’s needs and goals. Remember, the journey of data engineering is one of continuous learning and adaptation. So, keep exploring, keep learning, and keep engineering your data for success ! 👨‍💻

Want to read about an industrial application of Data Engineering ? Jump right to my next article: Building a Modern ETL Pipeline: Challenges, Solutions, and Insights.

Stay in touch

I hope you enjoyed this article as much as I enjoyed writing it !
If so, feel free to support my work by interacting with my content on LinkedIn 👀
You can also subscribe to be notified of the latest articles I publish 😌