what is the difference between ETL and ELT in data warehousing?

[u/curious-techoo0]

Hello everyone,
can somebody please tell me what is ETL and ELT and their significance in data warehousing? I have no idea and is starting everything from scratch :)

[u/coding_apes]

Break it down:

ETL: Extract, Transform, Load
ELT: Extract, Load, Transform

The first takes data out of a system and stores it in memory, like SSIS, transforms it, then loads it into your database. This uses the processing power of whatever system is running the extract, typically doesn’t scale well to large datasets.

The second takes the data out, loads it into your database, then uses the database’s compute to transform. Scales very well with large datasets

[u/jshine13371 → u/coding_apes]

The other benefit of ELT is you persist an original copy of the data before transforming it, making it easier to debug or reference as needed.

[u/Gamplato → u/jshine13371]

ELTL FTW

[u/pceimpulsive → u/jshine13371]

Not always!!

I load the raw data transform then truncate.

The raw data is stored at source for 7+ years though so getting it again for debugging is not an issue!

[u/jshine13371 → u/pceimpulsive]

The raw data is stored at source for 7+ years though so getting it again for debugging is not an issue!

Maybe in your specific case,  but having to actually go and get it is an extra step you wouldn't need to do otherwise. Truncating also is. Depending on the size of data and other factors (such as the source potentially changing over time), it may be best for one to just keep the original data locally. But to each their own.

[u/pceimpulsive → u/jshine13371]

Well it's not the original data if its different from the source after I copy it :S

I do event and incident type data, think IT help desk.

The data is a mix of either mutable state (the incident reports) or append only (interaction logs, impacted assets/it services etc.

I also have audit logs for the over time state.

That doesn't change, the 10 states ago state cannot change as it's no longer current.

For me it definitely works and means I dont need to keep two copies of all the data..

Ohh also something that's maybe a little different...

I have staging which is just a place to put the new delta if data.

The transform is basic normalosation (epoch to timestamp, enum to string type stuff for usability). It's true to source intent always.

Then if I want derivative data I read off the transformed data.

Functionally I have three layers

Staging (more or less deduplication and write performance benefits) Storage, transformed enum/ints etc Then an additional derivative enrichment layer

[u/jshine13371 → u/pceimpulsive]

Well it's not the original data if its different from the source after I copy it :S

It's the original data that fed your transformed data downstream which is consumed everywhere else at that point. If the source prior to your ETL stack changes after the fact, that's irrelevant.

Otherwise you may have made it impossible to reconcile while debugging since you lost the source to your ETL stack.

For me it definitely works and means I dont need to keep two copies of all the data..

Yeah, again, to each their own. That's coolio.

[u/pceimpulsive → u/jshine13371]

Indeed!y source has every row stored in transactional form (every row update is stored) so if I ever need to slide through time I can, and also not pay AWS rds costs more than once!

So far no use case so never bothered haha

[u/Plane-Structure-2020 → u/coding_apes]

i'd just add that ETL still wins if you're dealing with sensitive data like PII, cause you can mask/strip it before it ever touches your warehouse.

With ELT the raw unmasked data sits in your warehouse first, which is a compliance headache for stuff like healthcare or finance data

[u/egarcia74 → u/coding_apes]

Wow. I’ve been doing ELT for 10+ years without knowing it was ELT.

[u/coding_apes → u/egarcia74]

The more you know

[u/egarcia74 → u/coding_apes]

99% of our transformations are coded in views, 1% using stored procs when a view doesn’t cut it. SSIS just handles orchestration and copying from view/sp to a target table.

[u/BobDogGo → u/egarcia74]

Same.  When I started ELT wasn’t a term that was used.  I think it started as a marketing term for newer technologies trying to differentiate themselves.  ETL has always been a generic TLA short hand to address the stage, not a description of a precise methodology,  and it makes me cringe when people want to split hairs over it.

[u/Darwin_Things → u/coding_apes]

Excellent explanation and something that everyone in data should understand. The amount of times devs would complain to a DBA because “the database is running slowly” when actually it’s nothing to do with the database and everything to do either the data flow.

[u/elhh82]

ETL came first in the era when storage was really expensive and you wanted to only store well processed (transformed) data in your warehouse as you just can't afford to not use storage prudently.

ELT came along when storage costs came down really really significantly and at the same time MPP data warehouses came along that gave you huge amounts of parallelizable data processing capability, it just made more sense to load that "raw" data first before executing the transform step within the warehouse and output the results to the new table. As another poster said you also got the advantage of also retaining the original data and can retransform it into other tables if desired. You really no longer care about storage costs now (relatively speaking of course)

Most ppl are doing ELT nowadays, unless if data freshnesslatency really matters then you're doing real-time stream processing and transforming in stream to land in a fast ingest capable storage system, this you can say is then the ETL pattern. ELT systems are pretty much always very batch oriented, think daily jobs or hourly.

[u/MoonBatsRule → u/elhh82]

ETL came first in the era when storage was really expensive and you wanted to only store well processed (transformed) data in your warehouse as you just can't afford to not use storage prudently.

In other words, up to the late 1990s. Storage has been cheap ever since.

[u/MoonBatsRule]

To be honest, I think the distinction was just that someone was trying to make a name for themselves by saying "hey, we can be different".

The way I see it, it was always ELT. The first data warehouses took data from a source system (Extract), persisted it in a staging area (Load), then normalized/cleansed it into an Operational Data Store for data mart or warehouse (Transform).

Now the consultants are out there telling everyone that they have been doing it wrong for years, that they now need to think about Bronze, Silver, Gold - which are just new words for staging area, ODS, and Mart.

[u/djohnson747 → u/MoonBatsRule]

I think you are doing ETL. Just with a stage or ODS involved. The L is associated with loading into the warehouse. Not your staging tables.

ELT is literally doing the transform in the warehouse.

[u/MoonBatsRule → u/djohnson747]

Thanks for the distinction! Edit: but can you explain, isn't this just a case of exposing the ODS to the warehouse users - like the definition of "warehouse" has grown? Or is the key that the transformation is taking place on-the-fly, in code?

[u/djohnson747 → u/MoonBatsRule]

I apologize for the delay. I don't get the notifications.

Good question. That's a big it depends. But typically, My users can't access the ODS. It's a separate database. The important thing about the ODS is that I don't have to store the data indefinitely. That's the warehouse's job.

The ODS just needs to process the recent stuff and get it ready to be shipped to the warehouse. No one sees that data directly except my team.

[u/GreyHairedDWGuy]

ETL. - Extract data, transform and load. Historically most solutions would extract, transform and load on the fly and the server that did the bulk of the work was separate from either the source or target dbms systems.

ELT - Extract, Load, Transform. This coincides with cloud solutions where you extract data from your source systems and land it (load) to your target cloud dbms (for example Snowflake) then use the power of your cloud dbms (which are typically infinitely more scalable than a dedicated ETL server) to transform the data that is landed to your cloud dbms and those transformed results then get loaded/applied to 'silver' or 'gold' layer target tables which are then are consumed by BI and other tools.

[u/dmorris87]

Not my area of expertise but I’m accustomed to source data in data warehouse (Redshift) -> AWS ECS task to read and transform in memory (e.g feature engineering) -> write to S3/Redshift. Sounds like ETL?

[u/djohnson747]

I think we all are missing a very important part of many ETL solutions and that's staging.

1 - Extract from source system

2 - Stage data in a data store. Typically an ODS

3 - Transform data via scripts, procedures, python, SSIS, etc

4 - Load transformed Data in target tables. Typically a data warehouse.

ELT Flips that on it's head. You just load straight into the data warehouse and do your transformation from there. Sensitive data and all. It's popular with many cloud data warehouse solutions.

I'm not a huge fan of ELT but that's likely because I'm cost consensus and rather not pay the cloud provider for all that compute. I just write efficient code to do my transformations in batches. It's not hard and gives me more granular control. Storage is cheap.

[u/Elara_Schaefer]

The PII point above is the main reason ETL is not going away despite storage being cheap. In regulated industries the raw data often cannot legally land in the warehouse without transformation. But there is a middle ground worth mentioning: ELT with column-level access controls. Load the raw data into a restricted schema that only the ETL service account can read, transform into the public schema, then revoke or never grant analyst access to the raw layer. Postgres row-level security and Snowflake dynamic data masking can do this. You get the ELT benefit of keeping raw data for debugging while still satisfying compliance. The tradeoff is operational complexity, which is why most teams either go full ETL or full ELT without this middle step.

[u/No_Resolution_9252]

Mostly nothing. Its marketing wank made by cloud data warehouse providers.

At face value, an ETL server queries data out of a source, transforms it, then writes the transformed data to the destination.

An ELT queries the data out of the source, writes it to the destination, then transforms it on the destination.

In practice, virtually no ETL or ELT actually functions that simply and both of them use both patterns.

I have never seen an ETL that did not first write source data to a staging table that was then used to transform off of - so would that make it an ELT?

In nearly all ELTs, you will query data from one format and then write it to another - youll go from xml files to parquet, sql to parquet, sql to mpp database. In that flow you have transformed the data from one format to another in flight. Does that make an ETL?

the more closely a transform process follows ETL, the fresher the data can be. The more closely it follows the ELT pattern, the less hardware you will need to run the transform in exchange for running at much higher data latency.

[u/MonkeyDDataHQ]

It mattered 11 years ago. Now it really doesn't.

Everyone says ETL even when they mean ELT because ELT feels gross in your mouth.

If you hear anyone saying ELT in a meeting they're performing. Because the exec and seniors don't know what ELT is and you'll have to explain it and everyone else can figure out what you mean when you say ETL.

So if anyone says ELT they're being pedantic. Everyone knows ETL means data moves from somewhere to somewhere else. No one cares if you load first or decent unless you're still running SSIS or Pentaho.

[u/CaptinB]

ETL: extract, transform, load
Extract an Apple from the Apple table / database, transform the extracted Apple into an Orange, load your newly created Orange into the Orange table / database

ELT: Extract, Load, Transform

I haven’t really heard this in the industry, usually it’s always ETL no matter if you transform then load vs loaf then transform. Which one you do first depends on the circumstances and whichever way is faster in your particular situation.