My small experience with ELT and reading makes it look like it is mostly for development. Because the data is not cleaned up like in ETL that makes me think the new application will need to adapt its queries to the old data structure alongside the new application's new data structure. And, then, eventually ETL takes place somewhere down the road, in the new database, overtime until you only have your new application, new schema, etc., the old data schema isn't used anymore. It may still be in some tables somewhere, but unused, archive only.

For something like a hospital, ELT doesn't feel right. I would think the hospital wants things cleaned up before the conversion.

Is ELT for going to a new application in a super critical life or death system?

Edit: This article was suggested, http://www.jamesserra.com/archive/2012/01/difference-between-etl-and-elt/

After reading this helpful article, one of the ELT cases for preferring ELT is:

The source database and the target database are the same

What does this mean? The same database engine or the same schema? New applications that replace 20-year-old systems don't usually have the same schema, so I do not understand.

  • 1
    ETL is just an acronym - it can be used in any way you need it to. It seems your question is far more broad and is concerned with how to shift between old and new platforms, whether that be all at once or slowly over time. This may be too big of a question to ask here. – LowlyDBA - John McCall Oct 12 '17 at 15:17

ELT is fine for a critical system. Your problem is predicated on this faulty assumption:

"Because the data is not cleaned up like in ETL..."

You can do most any cleanup with ELT as you do with ETL. You just generally do it in a staging table on the target system instead of in-flight between the source and target.

Edit: You asked why do they mention:

The source database and the target database are the same

I think that means "ELT is easier to do when the source and target database engines are the same, because you frequently have them talk directly".

For example, ELT over Oracle DB links (Oracle to Oracle) or SQL Server Linked Servers (SQL Server to SQL Server) is much easier than setting up heterogeneous links with something like GoldenGate.

Tools like Presto can make this easier though, as you'll see people do:

insert into sqlserver.table
select * from oracledb.table
| improve this answer | |
  • To add to CalZ, you are never loading directly in to the destination tables for mission critical info that needs to be cleaned. As long as you load to stage tables first then it's fine. Here's an interesting read of the differences: jamesserra.com/archive/2012/01/difference-between-etl-and-elt – indiri Oct 12 '17 at 15:30
  • How does the new application and ELT data live together? Usually the new application needs a different schema. Do applications use a data adapter until the ELT data "goes away" (it does for production doesn't it?) I guess something like Oracle's Golden Gate perhaps? – johnny Oct 12 '17 at 15:31
  • They will live in separate tables and, if you prefer, even if different schemas, until the data gets transformed. Once you are done with the transform step you can get rid of the staging tables. Think of them as temps. – indiri Oct 12 '17 at 15:31
  • So you need an adapter between the application and the old tables? – johnny Oct 12 '17 at 15:34
  • No. ELT just means you are moving it first and then still transforming. Once the data is in your database you still have to do the transform step and move that data in to your existing data structures. Your application would still talk to the existing tables it always did. The front end will never be "aware" of the stage tables. – indiri Oct 12 '17 at 15:40

As far as I can tell, two of the listed advantages of ELT over ETL are:

  • Minimized interaction with the source system; you're grabbing the data and getting out, not asking any transformation work to be done during the extraction step itself
  • Using native SQL code for the transformation step, rather than code being run via some third thing that stands between the source and destination databases (be that a perl/python script, a homegrown application, or an ETL tool like MS's SQL Server Integration Services).

Conceptually, it's assumed that you're still doing a final load to the production destination tables after the "T" in "ELT". So, it still starts by extracting data from a source, and ends with loading the transformed data into the final destination.

I think you're assuming that no final load happens after the transformation activity in the destination system, and I think that's an incorrect assumption. And, even if it is correct, the same thing can be done with an ETL tool; you're just redefining the destination.

| improve this answer | |

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