1

TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse?

So forgive me the broadness of the subject title, as it glosses over details, but I'm trying to understand how ETL and ELT differ in a definitive way. They seem very similar, to the point that you could describe either as ELTL. In both cases you start with raw data and end up with concrete transformed data in tables. It seems to be what happens in between is the difference, which can take many forms depending on the number/complexity of transformations from start to end state.

From what I know of ETL, the steps are as follows:

  1. Extract the data from source systems and load into raw tables
  2. Transform the data from raw tables into staging tables
  3. Load the data from the staging tables into your warehouse

In some definitions of ELT I've seen, the steps seem to be:

  1. Extract the data from source systems and load into raw tables
  2. Transform the data notionally from the raw tables by creating views that represent the staging tables
  3. Load the data from the staging views into your warehouse

But then I look at other articles about ELT, and it seems to be:

  1. Extract the data from source systems and load into raw tables
  2. Transform the data notionally from the raw tables by creating views that represent the staging tables
  3. Congratulations, no more hard duplication of data from raw to transformed state, everything is views! Users just fire off their query and views nested n-levels deep instantly* return data!

*Results may vary, depending on whether you have millions of dollars to throw at cloud processing and/or large-scale quantum computing becomes available

2
  • 1
    Personally, I like the ELT approach using a sophisticated RDBMS (I would use PostgreSQL - F/LOSS, or Oracle (or SQL Server - haven't for a long time)) and transform using tables rather than views. This is because a view can be created with a query - but if you want to run complex functions on top of your raw imported data (which is normally the case...) - then it's a whole different ball game - running queries on top of complex functions on top of more queries/functions - VIEWs just become too complex. You can always delete as you go so as not to take up too much space -sample first! Apr 29 at 12:29
  • @Vérace I agree that tables can be necessary, mainly due to quality of data coming in, and the requirement to cleanse before final state. Trying to do this on the fly through views / CTEs can result in some extremely expensive operations, compared to UPDATE/DELETE operations on temporary or permanent staging tables.
    – e_i_pi
    Apr 29 at 22:14
3

No.

What you describe are all variants of ELT.

The difference between ETL and ELT is in where you do the "T". The "traditional" ETL flow would implement the "T" (data transformation) outside the DBMS, using a specialized tool like DataStage, Informatica, Talend, etc. The data transformed to the target model would then be simply loaded into the destination DBMS tables.

The ELT flow differs in that it is the destination database engine that performs the "T"; you load raw data into staging tables in the target database, then use the DBMS tools to "massage" them into the final form and copy to the destination tables. It is an appropriate alternative when the source and target data type impedance is relatively low and the DBMS in question has the tools and capacity to do the job.

2
  • Right. So if the transformation is done by the database, it's ELT, if it's done by a different tool (say SSIS reading in from flat file and then running C# scripts to get to final relational form) then it's ETL. Is that a correct use case interpretation?
    – e_i_pi
    Apr 29 at 0:55
  • 1
    @e_i_pi I'd say your statement is accurate. My opinion is most things concerning definitions within the context of data warehousing are pretty generalized / broad / and not always concretely defined. Even the term data warehouse itself has evolved over time and can arguably mean different things these days, with new terms like data lake popping up, etc. Long story short, don't worry too much on getting a perfect definition of ETL vs ELT, but mustaccio's answer does a good job explaining the differences, generally.
    – J.D.
    Apr 29 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.