ELT, ETL, and Apache Airflow: The Preferred Way to Build Data Pipelines in 2026 With Apache Airflow
1. Apache Airflow
Apache Airflow is like a robotic conductor for computer programs that need to work together in a specific order. Imagine you are baking a cake: you must mix the batter before you can put it in the oven, and you must let it cool before you can add frosting. Airflow makes sure each step happens at the right time and in the right sequence, so nothing gets mixed up.
The specific order in which Airflow ensures programs run is commonly referred to as Directed Acyclic Graphs (DAGs). This acyclic property ensures that processes flow in a single direction, preventing infinite loops and enabling topological ordering for sequential execution.
In Data Engineering, Apache Airflow acts as an orchestrator, not a compute engine. Apache Airflow does not hold or process data itself; it only triggers tasks/ tells a program "run now". For instance, you want your data moved from a MySQL database to a warehouse every day at 2 pm; we could use a cron job or a simple Python script, but these do not work well when your program fails(and a bunch of other reasons); Airflow is always the best bet in any orchestration work.
However, it's important to understand that Apache Airflow does not process data; its job is to schedule and sequence. So the transformed data never "lives inside Airflow." It lives wherever the transform task's code writes it. This is an important concept that influences your choice between an ETL and ELT pipeline, especially if using Apache Airflow as an orchestrator.
2. ETL Pipelines
In an ETL pipeline, data is moved from a source system to a target system whilst getting reshaped along the way. A simple example is illustrated below.
In this example pipeline by Apache Airflow), data moves from the database, undergoes transformation, and gets pushed to a warehouse. In the real world, there's more to an ETL pipeline than the simple extract → transform → load. Now look at the sample pipeline below.
Now we have extra stops along the way. The staging and validation stages, these are key steps in building industry ETL pipelines
Ⅰ.Staging
In real-world systems, data is not directly pulled, stored in memory, and then directly pushed to a warehouse. Think of it like cooking from a recipe using ingredients from a grocery store.
- MySQL is the grocery store; the source of raw ingredients.
- Extract is you going shopping once, bringing everything home, and putting it in your fridge.
- Staging is that fridge; ingredients are home now, safe, not going anywhere, ready whenever you want to cook.
- Validation(raw) is opening the bag and checking nothing's spoiled before you start cooking with it.
- Transform is the actual cooking: chopping, mixing, seasoning; using what's in your fridge, not by running back to the store mid-recipe.
- Validation(staging) is confirming the prepared meal isn't burnt and is ready for serving.
- Load is plating the finished meal and putting it on the table (the warehouse).
When you accidentally burn your food, you do not go all the way back to the grocery store; you check the fridge for more of the same ingredients and try again. Staging gives you a private working copy so mistakes and retries don't mean going back to the live, shared source every single time. This protects you from various failure scenarios:
- When your transform script crashes halfway through, without staging, you have to re-read the data from the source again to retry. But with staging, your file is already sitting safely on disk; you just re-run your transform step against that file.
- Say you find a bug within your transformation logic later; without staging, the database is your only source of truth, and it might have changed since then. You cannot perfectly recreate what the data looked like during your last transformation. With staging, you have the exact snapshot ready to be redone.
- Whenever you're actively debugging transform logic and need to run it repeatedly, without staging, every iteration means querying the live source again; this means competing for resources with whatever else depends on that system. With staging, you hit the source once, then iterate locally, as many times as you need, for free.
This is exactly what companies do at scale for staging, often under the name medallion architecture (bronze/silver/gold):
- s3://your-bucket/bronze/ ← raw, untouched, exactly as extracted
- s3://your-bucket/silver/ ← cleaned, deduplicated, feature-engineered
- s3://your-bucket/gold/ ← business-ready, what dashboards and models query
Staging exists so a bug, a crash, or a retry in your transformation logic never means "go bother the live production database again"; it means "just re-read the file that's already sitting safely on disk."
Ⅱ.Validation
Staging helps you not lose data, but it does nothing to catch bad data; that's why validation is needed.
Validate raw: This stage answers various questions: did we get roughly the number of rows we expected? Are the required columns present at all? Did the source return anything, or did it silently fail and hand us an empty file? This stage catches problems with data extraction before they get baked into anything downstream.
Validate staging: After transformation, does the schema match what the next step expects? Did a join (like an external data enrichment step) produce unmatched or duplicated rows? Did the target variable's distribution shift in a way that suggests something broke, rather than the data genuinely changing? This step catches problems with your own transformation logic before a warehouse or a model trusts the result.
Staging guarantees you don't lose your data; validation guarantees you don't quietly trust bad data.
3. ELT Pipelines
In an ELT pipeline, data is moved from the source systems and loaded directly into a cloud data warehouse. The data is then transformed inside that destination warehouse using the warehouse's own compute.
Unlike an ETL pipeline where staging and validation take place outside the warehouse, these steps take place inside the warehouse. As in our provided example pipeline(orchestrated by Apache Airflow):
- Data is moved from a MySQL database directly into the Snowflake warehouse in a raw schema table(raw.applications); no changes are made to this table.
- Then the Staging step, where a dbt model, which is a .sql file containing SQL queries, is run to create a staging table/data transformation(staging.applications) inside the warehouse using the warehouse's compute.
- Then the Validation step, where dbt, with built-in "tests" (YAML rules like not_null, unique, accepted_values), checks the staging table right after it's built and stops the pipeline if a rule is broken.
- Finally the Marts layer(another sql query built from the staging table) does the final joins and grouping producing the polished, ready-to-use tables your models or dashboards actually query
What To Choose In 2026
Choosing between ETL & ELT in 2026 boils down to the kind of transformation you need, and where you're willing to pay for the computer power to do it.
Choose ELT when:
- Your transformations are things SQL is naturally good at- joining tables, grouping, summing, filtering. Most everyday business logic fits here.
- You're using a modern cloud warehouse (Snowflake, BigQuery, Databricks)- these are built to be fast and cheap at exactly this kind of work.
- You want to keep the untouched raw data sitting in the warehouse too, so you can always go back and reprocess it differently later without re-pulling from the source.
- Your team is stronger in SQL than in Python/Spark; dbt (the standard ELT tool) is just SQL files, easy to pick up and easy for more people on a team to contribute to.
- You want to cut down costs in case the extra staging storage for ETL causes additional costs and warehouse costs are cheaper
Choose ETL when:
- The transformation needs something SQL genuinely can't do: calling an external API, running a machine learning model, or complex Python-only logic (regex cleaning, custom feature engineering).
- You want to control exactly what gets sent to the warehouse; useful if there's sensitive data you need to strip or mask before it ever lands there, rather than after.
- You're not using a modern cloud warehouse, or your warehouse's compute is expensive/limited, so you'd rather do the heavy lifting somewhere cheaper first.
Airflow's role is the same either way; it's just the conductor telling tasks when to run, whether those tasks are Python scripts (ETL) or dbt run commands (ELT). Choosing between them isn't really an Airflow decision at all; it's a decision about where your transformation logic naturally belongs.