Welcome to your second lesson. In our previous session, we mapped out the data engineering landscape, identifying the key stages a pipeline executes: ingestion, storage, transformation, and serving. We established that data moves from a source to a consumer, but the exact sequence of operations is a critical architectural choice.
Today, we will focus on that choice by contrasting the two dominant paradigms for data integration: ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform). Understanding the fundamental differences, historical context, and modern trade-offs between these two patterns is essential for designing an effective data pipeline. This lesson will directly inform the architecture we build for your project, especially given your choice to use tools like dbt.
A Tale of Two Architectures
At the heart of the ETL vs. ELT debate is a simple but profound question: When and where should you apply your transformation logic? Do you clean and structure your data before it enters your primary data storage, or do you load the raw data first and transform it inside the target system?
To start, let's get a high-level overview of both approaches. The following video offers a clear, concise introduction to ETL and ELT.
ETL vs ELT | Modern Data Architectures
The video "ETL vs ELT | Modern Data Architectures" from Kahan Data Solutions sets the stage by framing these as the two primary strategies for moving and transforming data.
Please watch the introductory part of the video, from the beginning to the start of the ETL section. Notice how it frames both as achieving the same goal—moving and transforming data—but through different strategies.
The sequence of these three simple words—Extract, Transform, Load—defines the architecture of your pipeline. Let's examine each pattern in detail.
The Traditional Approach: ETL (Extract, Transform, Load)
ETL is the classic, long-standing pattern for data integration. For decades, it was the standard way to populate data warehouses.
The process is exactly as the name implies:
- Extract: Data is pulled from one or more source systems (databases, applications, files).
- Transform: The data is moved to a separate, intermediate processing server or "staging area." Here, it undergoes all necessary transformations: cleaning, validation, renaming columns, joining with other data, and aggregation. Your SQL skills would be heavily used at this stage, but within a dedicated transformation engine, not the final data warehouse.
- Load: The fully transformed, cleaned, and structured data is loaded into the target data warehouse, ready for analysis.
The IBM Technology channel offers a fantastic analogy for this process.
ETL vs ELT: Powering Data Pipelines for AI & Analytics
This video, "ETL vs ELT: Powering Data Pipelines for AI & Analytics," uses a water filtration analogy to explain the concept.
Watch the section explaining the ETL model, from the introduction of ETL to the beginning of the ELT section. The key idea is that you are cleaning the water before it ever reaches the city's reservoir. Pay attention to the use cases mentioned, particularly regarding sensitive data and cost control.
The core principle of ETL is that the data warehouse only ever receives clean, pre-processed data. This was essential in an era where data warehouses had limited computational power and storage was expensive. You didn't want to waste precious warehouse resources on cleaning data or storing raw, messy versions of it.
This approach is particularly strong when:
- Compliance is critical: You can ensure sensitive information like Personally Identifiable Information (PII) is removed or masked before it's loaded into the main analytical system.
- Transformations are complex and CPU-intensive: A dedicated transformation engine can be optimized for these tasks without impacting the performance of the data warehouse, which is busy serving queries to analysts.
- Data sources are well-understood and targets are rigid: ETL is excellent for building highly structured, predictable reports and dashboards where the output format is known in advance.
The Modern Approach: ELT (Extract, Load, Transform)
The rise of powerful, scalable cloud data warehouses like Snowflake, BigQuery, and Redshift flipped the traditional model on its head, giving birth to ELT.
The process here is:
- Extract: Data is pulled from source systems, just like in ETL.
- Load: The raw, untouched data is loaded directly into the target data warehouse or a data lake. There is no intermediate transformation step.
- Transform: All transformations happen inside the data warehouse, leveraging its massive parallel processing power. You use SQL queries to clean, join, and model the raw data into analytics-ready tables.
Let's return to the water analogy to see how this differs.
ETL vs ELT: Powering Data Pipelines for AI & Analytics
The same IBM video now explains the ELT model.
Watch the section covering ELT, from the start of the ELT explanation to the comparison section. Here, the raw water is piped directly into the reservoir, and the filtration happens inside the reservoir itself.
This shift was made possible by two key technological changes:
- Cheap, scalable storage: Cloud storage makes it economically viable to store vast amounts of raw data.
- Powerful cloud data warehouses: These modern systems are designed to handle transformations on huge datasets efficiently. Their ability to separate storage from compute means you can scale up compute resources for a big transformation job and then scale them down, paying only for what you use.
This is the paradigm where dbt, a tool you've chosen for your project, operates. dbt doesn't extract or load data; it is a dedicated tool for managing the "T" (transform) step within an ELT workflow, allowing you to build, test, and run complex SQL transformations on data that's already in your warehouse.
The following image provides a great visual summary of the two workflows.
ETL vs. ELT: A Direct Comparison
Now that we understand the two approaches, let's put them side-by-side. The following article from dbt Labs provides an excellent breakdown.
ETL vs ELT: What's the difference and why it matters | dbt Labs
This article from the creators of dbt explains the differences and makes a clear case for the modern ELT approach.
Please read the following sections of the article: Start with the introduction to grasp the core distinction. Briefly review the definitions and diagrams for ETL and ELT. Pay close attention to the section ETL vs ELT: a side-by-side comparison. This table-like structure clearly outlines the trade-offs. Read the section Making the case for ELT to understand its primary benefits. Finally, read How dbt fits into the ELT workflow. This connects the concept directly to the tool you will be using.
As the article highlights, the main advantages of ELT include:
- Speed & Flexibility: Raw data is available in the warehouse almost immediately. You don't have to define all your transformations upfront. If a new analytical question arises, you can work with the raw data directly without having to re-ingest it.
- Leveraging Cloud Power: It takes full advantage of the scalability and power of modern cloud data warehouses.
- "Schema-on-read": With ETL, you must define your data structure (schema) before you load it ("schema-on-write"). With ELT, you load raw data and then decide how to structure it when you query it ("schema-on-read"), which is far more flexible.
- Data Democratization: By having all the raw data available, you empower different teams (data science, analytics, marketing) to build their own transformations and views suited to their specific needs.
Choosing the Right Path for Your Project
Given your project goals—powering both a structured analytics dashboard and a more exploratory NLP service—and your chosen tech stack, the ELT architecture is the clear choice.
Here's why:
- Tooling: Your choice of dbt is the definitive sign. dbt is the premier tool for the "transform" step in an ELT world. It is designed to run on top of data that is already loaded into your PostgreSQL database.
- Flexibility for Multiple Use Cases: Loading raw data first gives you maximum flexibility. You can create one set of dbt models to produce highly structured, clean tables for your dashboard. Simultaneously, your NLP task might benefit from having access to the less-structured, raw text data, which will be readily available in your database.
- SQL Proficiency: Your advanced SQL skills are perfectly suited for an ELT workflow. You'll use SQL within dbt to build all the transformation logic, turning raw data into valuable insights.
Our pipeline will follow this modern pattern. We will focus on building robust ingestion scripts that load raw data into PostgreSQL (the E and L), and then we will use dbt to manage the complex transformations (the T).
Key Takeaways
- The fundamental difference between ETL and ELT is the timing and location of the transformation step.
- ETL (Extract, Transform, Load) is the traditional model, where data is cleaned in a separate staging area before being loaded into a data warehouse. It's rigid but good for compliance and legacy systems.
- ELT (Extract, Load, Transform) is the modern, cloud-native model, where raw data is loaded into a warehouse first, and then transformed inside it using the warehouse's own compute power.
- ELT offers greater flexibility, speed, and scalability, making it the standard for most modern data analytics and data science workloads.
- Your project will use an ELT architecture, with Python scripts for extraction/loading and dbt for in-database transformation.
Next time
We have now decided on the sequence of our data operations (ELT). The next question is about the timing. Should we process data in large, scheduled chunks or as a continuous flow? In the next lesson, we will compare batch and streaming processing to understand the trade-offs in latency and throughput, another critical decision for our pipeline design.