How the two approaches differ by decision
| Dimension | ETL | ELT |
|---|---|---|
| Where transformation runs | In a separate processing layer before the warehouse. | Inside the target platform, usually as SQL models. |
| Best-fit workload | Constrained target platforms, mandatory cleansing before storage, existing processing infrastructure. | Platforms with strong compute and SQL tooling, and teams that value versioned transformation code. |
| Latency | Adds a processing hop before data is queryable. | Shorter path from source to query, bounded by warehouse compute. |
| Cost model | Processing compute plus duplicated storage; warehouse cost stays focused. | Warehouse compute absorbs transformation; storage holds raw and modelled data. |
| Skills required | Pipeline engineering in an external tool. | SQL modelling, testing and platform cost awareness. |
| Governance and lineage | Logic sits outside the warehouse, so lineage has to be maintained deliberately. | Logic is visible in the platform, where tests and model documentation live. |
| Schema change and replay | Replay depends on what the processing layer retains. | Raw retention makes replay straightforward and corrections auditable. |
| Limitations | Duplicate storage and another system to operate. | Raw sprawl and unreviewed SQL if ownership is unclear. |
| Migration implications | Moving to ELT means rebuilding transformations in the platform. | Moving to ETL means extracting logic back out and maintaining it elsewhere. |
What actually changes between ETL and ELT
In ETL, a separate processing layer validates and reshapes data before loading, so the warehouse only ever receives modelled records. In ELT, raw data lands first and transformation happens in the target platform, usually through SQL models. The practical differences are where compute is spent, how much raw history exists for replay, and how much of the logic sits inside the platform’s own tooling. In healthcare data integration, this boundary is also a purpose-and-permission question: identify what is allowed to land before deciding where transformations run.
When loading first is the better design
ELT fits platforms with strong compute and SQL tooling, teams that want reproducible transformation code, and estates that benefit from retaining raw data for replay and audit. It also shortens the path between source and answer. The costs are real: transformation compute is consumed by the warehouse, and unreviewed SQL sprawl becomes a governance problem quickly.
A typical ELT layout lands each source unchanged in a raw schema, builds incremental models that merge only new or changed rows, and keeps the business logic in version-controlled SQL with tests beside it. In dbt, an incremental model with a unique key turns a rerun into an update of the same rows, which is what makes replay safe.
- Raw landing tables that preserve source fidelity for replay
- Transformation expressed as versioned, tested models
- One owner per mart, not per table
When transforming first earns its place
ETL fits constrained target platforms, data that must be cleansed before it is ever stored together, and estates with existing processing infrastructure that is already operated well. It keeps the warehouse free of raw complexity. The trade-off is duplicate storage, another system to operate, and business logic that lives outside the platform where analysts cannot see it.
Masking or removing personal data before it is stored is the clearest case. If a field must never land in the analytical platform, transforming in flight is a control, not a style choice. The same applies to heavy parsing of binary or deeply nested formats that the target engine handles poorly.
Governance and lineage decide more than performance
A number has to be traceable to its source regardless of pattern. When logic is split across a processing layer, a warehouse and a spreadsheet, lineage stops being answerable. Whichever order you choose, agree where definitions live, how transformations are versioned, and which tests block a release.
Schema change and replay
Sources change. New columns appear, types widen, late records arrive, and a pipeline has to be replayable without manual repair. Loading raw data first makes replay straightforward and keeps correction history intact. Transforming before load can be efficient, but it often means a schema change requires a change in two places.
Illustrative example · hypothetical organisation
Worked example: one pipeline, both patterns
Take a hypothetical subscription business loading billing events and a customer table that contains national ID numbers.
Billing events land raw and are modelled in the warehouse. Invoices, credits and refunds stay replayable, and the revenue definition lives in reviewed SQL next to its tests. When a refund rule changes, the models are rebuilt from raw history with no new extract.
The customer table takes a different path. National ID numbers are tokenised before the data leaves the source environment, because no analyst needs them and storing them raw would widen the access problem. That step is ETL by necessity.
Recording both choices per data product keeps the pipeline explainable: anyone asking where a rule lives finds the answer in one place.
How to decide
Answer four questions: how much raw history is worth retaining, how capable the target platform’s compute and SQL tooling are, who maintains transformations, and how strictly lineage must be provable. Then pick one dominant pattern per data product instead of mixing both arbitrarily.
Follow a sensitive field through every copy.
Moving a transformation before or after load changes where its input is exposed. In this hypothetical customer pipeline, analysts need a stable join identifier but do not need the original contact email. The useful ETL-versus-ELT decision records where that email can exist, who can read it and how replay avoids creating an uncontrolled second path.
Scope and assumptions: Choose the field classification, permitted use, retention and identity controls with the responsible data owner. A token, a masked query result and deletion are different controls. The worksheet does not establish legal compliance or make a particular tokenization design suitable for every dataset.
| Dimension | Boundary decision | Evidence and acceptance condition |
|---|---|---|
| Source extract | Identify whether the original email is needed for this data product. If not, evaluate an upstream identifier or transformation before copying it into another system. | Record the required join behavior and the minimum source fields. Test missing values, repeated identities and changes to the contact detail with synthetic inputs. |
| Landing copy | If raw values must land, name the restricted location, permitted identities and retention decision. Do not assume the reporting view's permissions protect every raw copy. | Test access through actual loader, analyst and support identities. Inventory temporary files and extracts as well as warehouse tables. |
| Modelled view | Separate the stored representation from what a query displays. Snowflake Dynamic Data Masking applies policies at query time; it should not be described as removing the stored source value. | Verify the intended query result for each role and inspect the underlying copy's separate controls. A masked screenshot alone is insufficient evidence about storage or replay. |
| Failure evidence | Decide what quarantine rows, test-failure tables and diagnostic logs may retain. They can reproduce a sensitive value outside the main transformation path. | Cause a controlled validation failure with synthetic data. Inspect the resulting artifacts and their access and retention settings; keep production personal data out of shared debugging notes. |
| Replay path | Apply the same field boundary when reprocessing history. Record whether the historical input, mapping material or derived identifier is retained and who can use it. | Replay an approved synthetic fixture through the real path. Check that temporary outputs and restored copies do not silently bypass the normal access boundary. |
At narrow widths, scroll the worksheet to read every column. With the table focused, use the arrow keys to scroll.
Worked decision: a reporting view shows only a join identifier, but a failed-load export contains the original email in a broadly accessible location. The reporting control works while the complete data-flow requirement fails. Address that copy and repeat the failure-path check; choosing the ETL or ELT label alone does not resolve the exposure.
- Retain a field-level copy inventory with the transformation definition and consumer contract. State explicitly which controls affect stored values and which affect query-time visibility.
- Recheck the inventory when adding a destination, diagnostic tool or replay process. Preserving a useful raw history is a deliberate data-use decision, not an automatic entitlement of an ELT architecture.
Use the field boundary as an input to the data engineering source and recovery contract so that ingestion, transformation and operations share the same requirements.
Official references for this example:
Questions and trade-offs
Question
Is ELT always the modern choice?
No. It is the better fit when the platform has strong compute and SQL tooling and the team wants transformations under version control. It is a poor fit when the platform is constrained, data must be cleansed before it is stored with other records, or an existing processing layer is already operated well.
Question
Can we use both?
Yes, and most estates do at some level: heavy cleansing before load, then modelled transformation inside the platform. The risk is ambiguity about where a given rule lives. Decide the default pattern per data product, document exceptions, and keep one owner for each defined measure.
Question
Where does reverse ETL fit?
Reverse ETL moves modelled data out of the warehouse into operational tools such as a CRM or support system. It is a delivery step after transformation, so the same rules apply: one definition upstream, a named owner, and monitoring that tells you when the sync falls behind or writes stale values.
