What are the arguments in favor of using ELT process over ETL?

Question

I realized that my company uses an ELT (extract-load-transform) process instead of using an ETL (extract-transform-load) process.
What are the differences in the two approaches and in which situations would one be "better" than the other? It would be great if you could provide some examples.

Answer 1

lots of Discussions about ETL vs ELT out there.

The main difference between ETL vs ELT is where the Processing happens ETL processing of data happens in the ETL tool (usually record-at-a-time and in memory) ELT processing of data happens in the database engine

Data is same and end results of data can be achieved in both methods.

it very much depends on you and your environment If you have a strong Database engine and good hardware and you can do heavy processing on it, ELT is good for you, If you have a busy datawarehouse engine and you need to free it from processing go for ETL.

notice that having an ETL tool gives you both the options, like ETL(T), you can do the Transformation in the ETL tool and you can do transformation in the Database engine as well

but ELT you only have the option of transformation in the database engine, but you should know that Databases are better at set based operations than record-at-a-time ETL tools.

similar question asked on SO but supporting ETL and also a nice Article comparing ETL vs ELT, but favoring ELT

Answer 2

It's almost a matter of semantics. A lot of hot air gets released in discussions about this but I'm not really convinced that there is any real philosophical depth to a distinction between the two.

At some level you can view ETL as transforming data in a client-side tool before finally loading it, with ELT implying that the data is transferred to some sort of staging area with relatively little change to the format. 'Transformation' takes place afterwards.

These are very fluffy definitions and could be applied to a wide variety of technical architectures, and there are many possible designs that either term could be used to describe.

I'm very strongly in favour of an architecture where all the transformation and business logic can be built into a more or less homogeneous code base, and I've done a lot of systems where the transformation logic was quite complex. This tended to just use the ETL tool to land the data and then all of the transformation was done in stored procedures. Arguably this could be described as ETL or ELT with the difference merely being one of semantics.

Some tools are very database centric, however (Oracle Data Integrator, for example, is often referred to as an ELT tool). If you subscribe to this view, then 'Extract' and 'Load' are happening before the data is transformed as they are being landed into a staging area and then crunched by SQL or PL/SQL code (which may be generated by the tool or hand written). Several people I've talked to seem to regard the principal merit of ODI as that it's not OWB.

If you use a client-side tool such as Informatica Powercentre or MS SQL Server Integration Services then the tool can do extensive transformation to the data client-side. Some ETL tools, such as Ascential Datastage and Ab Initio are designed to do a lot of work with flat files and in-memory data structures for speed. In this sort of architecture the transformation has already been done before it's loaded. Perhaps this type of architecture could be definitely classified as 'ETL', although I've seen many tool-centric projects where all the real work is done by a bunch of stored procedure code.

There are advantages to various tools and architectural approaches, but one can't make a blanket statement about the merits of 'ETL' vs. 'ELT' approaches because the terms are so broad that the difference is almost meaningless. Some tools and architectures may have specific advantages - for example, Ab Initio's heavy use of flat files gives it a significant performance advantage on large data volumes.

In practice, making the distinction between 'ETL' and 'ELT' is pretty meaningless without going into a much deeper discussion of the system requirements, platform and technical architecture.

Answer 3

It's also a matter of money. Where data volumes are high as you point out, flat-file based solutions like Ab Initio and DataStage Parallel Extender are indeed faster, but can be mid-to-high six-figure propositions. IRI CoSort is very ETL-centric (per their ELT comparison), and the only affordable way I've seen to address the transformation volume with file-system speed, apart from a complex Hadoop implementation. I also think throwing hardware at the problem generally (which ELT appliances and in-memory DBs also do), doesn't scale as well cost-wise either.