As a data engineer, you’ve likely been in a design review where someone says at the very end, “We should just do ELT for everything!”. You are a data engineer, and you’ve probably been in a design review where you heard someone say at the end, “We should just do ELT for everything!”. Or you’ve inherited a package in an old 10-year-old version of SSIS that you would have to pick apart row-by-row in a painful manner, and the business is wondering why the “modern” warehouse team can’t simply replace it overnight. The industry has morphed ETL, ELT and now Reverse ETL into tribal identities: pick one, fight about it in slack threads, ship. But that’s backwards. The pattern is not the personality, it is a means to an engineering end.
The reality, though, is that ETL, ELT, and Reverse ETL are not mutually exclusive approaches — they are three different kinds of pipelines that are tackling three different problems and most production data pipelines require all three to be running concurrently. Don’t identify a winner. It’s to align the pattern with the limitations of the workload: data sensitivity, complexity of transformation, latency requirements, and the most cost-efficient place to execute the workload.
The Problem Deep Dive
All three patterns use the same three verbs—extract, load, transform—but in different orders: that’s why it’s a bit of a muddle.
ETL (Extract, Transform, Load): ETL is the process of moving data into a staging area, where it is transformed in some way outside of the target system before it is loaded into the clean data in the target system. This was the standard practice for decades as target systems or data warehouses, particularly transactional databases, would be costly to compute on and required to be buffered from raw and dirty data. This pattern has become the backbone of careers such as those of tools like SSIS, Informatica, and Talend.
ELT (Extract, Load, Transform) first loads raw data into the destination, and then transforms it-while-stored using compute functions on the destination. It really only became dominant when cloud warehouses (Snowflake, BigQuery, Redshift, Azure Synapse) separated storage from compute and started to offer cheap ways to store raw data and transform it later, incrementally, using tools like dbt.
Reverse ETL moves the already transformed, already modeled data out of the warehouse and into operational systems for business teams to take action: Salesforce, HubSpot, Braze (an ad platform). It’s more of a third point on the same spectrum; it’s the vehicle for the other two, which is a problem that neither ETL nor ELT were intended to solve—getting warehouse truth into the tools where other things and humans are happening.
The sticking point is that teams find themselves using these as “short cuts” rather than as decisions based on the workload:
· Failure of compliance due to ELT by-default. A healthcare team loads raw PII into a warehouse without masking or tokenization as that is the way modern data stacks work.A healthcare or fintech team places the raw PII in a warehouse without masking or tokenization first. Now, without the mask, SSNs or PHI are stored in raw schemas that are accessible to half the analytics org and the cost of the compliance retrofit outweighs the transform step. This is the same for ETL’s pre-load transformation; scrub before, don’t scrub after it lands.
· Runaway warehouse costs due to misusing ELT. A team pushes a full nightly extract of a 500-million-row transactional table into Snowflake, and uses dbt models to re-scan the data on every run, rather than incremental models. The warehouse bill expands due to the compute moving from a dedicated ETL server to the meterized cloud credits, without any one looking at the meter.
· Operational data that has been stale due to the lack of Reverse ETL. A customer lifetime value model is created in the marketing team’s warehouse, but not piped back to the CRM. The warehouse model has no way to get back out of Salesforce, so the Sales reps still see the raw purchase counts. The insight is there, but it isn’t there where the person needs it at decision time.
· ETL (Row-by-row) instead of ELT (Set-based). Some classic SSIS or legacy on-prem pipelines where they were changing records one by one inside the pipeline, and the same transformation can be written as a simple set-based SQL statement in the target warehouse and run in a fraction of the time.
All these are the correct tools for the wrong jobs — but not bad tools!
The Solution: A Decision Framework
Ask 4 questions about each workload, instead of “which pattern do we standardize on?”. In reality, all three patterns are implemented together on various pipelines on most platforms.
1. Is the data required to be scrubbed, masked, or filtered before it reaches any place it can be queried? If yes — PII, PHI, cardholder data, anything under GDPR/HIPAA/PCI scope — transform before load. This remains ETL’s main reason to be, regardless of the ELT fashions. Mask/tokenize in the extraction layer (Azure Data Factory data flows, or a simple Python/SQL Server SSIS step), meaning that any raw sensitive values never reach the raw schema of the warehouse. For those on the Microsoft stack, this is the typical best case scenario for maintaining ADF or SSIS in the mix in an otherwise ELT-focused Azure Synapse or Fabric pipeline, instead of removing it from the mix because dbt is cool.
2. Does the transformation require a lot of steps and iterations and needs to be versioned, tested, re-run by analysts? If yes, use ELT. Bring in raw (or lightly scrubbed) data to the warehouse and leave the work of in-place modeling to dbt, stored procedures or Synapse/Databricks notebooks. You have version-controlled transformation logic, built-in automated testing, lineage graphs, and can rebuild history without re-extracting from a fragile source system when someone discovers a bug in the transformation.
The most important decision when creating a model that causes cost blowouts is whether to run it incrementally (only the new or changed rows since the last run) or not. The key for teams transitioning from SQL Server/SSIS to a dbt-style ELT mindset is to get this concept in their heads early: a transformation is no longer “a step in a pipeline,” it’s a “materialized view” that needs to be refreshed, and it’s that refresh strategy where most of the warehouse bill lives or dies.
3. What is the latency requirement and is it variable with each hop? Batch analytics (nightly board reporting) does not require ELT’s load then transform lag. Extracting and initial transforming typically occur within a streaming layer (Kafka/Event Hubs + stream processing) before anything enters a warehouse, or in the context of ETL, transform early.
4. Is there a need for this insight to act upon outside of the warehouse for a human or downstream system(s)? Whether the data pipeline was ETL or ELT, without Reverse ETL the value of the data is lost once it’s returned. Tools such as Census, Hightouch, or a scheduled Azure Function or Logic App that read from a warehouse view and write to an API fill in the gap. Model it once, sync it wherever it needs to act it out. What this typically involves in practice is creating a single, well-governed model in the warehouse, e.g. a “customer health” or “customer segment” view, which categorizes each customer as “at-risk”, “high-value” or “standard” according to recency and lifetime spend, and then letting a Reverse ETL tool match that segment field with a custom field in the CRM, following a set schedule. Never again will any engineer have to create a CSV to Salesforce, and a sales rep’s view of the segment is always up to date with the warehouse model that powers it.
In reality: streaming or batch extraction with PII scrubbed at the source (ETL) → raw but safe data deposited in the warehouse (ELT) → curated marts synced back to operational tools (Reverse ETL). Three patterns, one pipeline, for each one of them it is really good at doing.
Proof: What This Looks Like in Practice
The initial design on a retail insight app I was working on was ELT only – raw order and customer data was coming straight from Salesforce and the transactional database into Snowflake and all masking and transformation was being done downstream in dbt. Until a compliance audit brought up the fact that raw customer PII was accessible to any analytics use case with access to the warehouse, which also accessed some fields that never saw use in any analytics use case.
But it wasn’t about giving up on ELT. It was putting in a thin ETL step at extraction: an Azure Data Factory data flow that was stripping out PII fields before putting data into the raw schema, and everything else flowed directly through to dbt for modeling. Access to detokenized values was restricted to a few service accounts.
The measurable outcome: compliance gap was closed without any action on the 40+ dbt models that were already deployed, as the business logic that needed to be changed didn’t need to be moved. Compute cost was not impacted — the masking step did not cost anything in the warehouse compute, it did on the extraction layer. The team then integrated an hour-by-hour (via Hightouch) Reverse ETL sync to move the customer health segment view from the marketing platform into the CRM – removing a weekly manual CSV export performed by a marketing analyst every Monday. None of these three required giving up the other changes, and each pattern was used precisely where the compromises were warranted.
The Close
ETL, ELT, and Reverse ETL are not competing architectures for your loyalty, but three tools to address three different questions: what must be cleaned before it is put into the warehouse, what is less expensive to transform when compute resides there, and what must come out of the warehouse to have an impact? The ones who are burned are those who choose one pattern and apply all of their workloads through it.
The next time you are thinking of setting up a pipeline, don’t ask “are we an ETL shop or an ELT shop?”. For each workload: Does it need scrubbing before it lands? Is the transform complex enough to benefit from version control and incremental materialization? Does it need to be materialized at extraction time (latency) given that output needs to walk back out of the warehouse to do any good? When answering the four questions truthfully for each data flow, the correct pattern — typically multiple patterns — emerges spontaneously.