Moving from ETL to ELT?

Question

I would like to know if it's possible for an organization to change its data integration process from ETL to ELT in order to reduce loading time? and what if the DWH Infrastructure doesn't match?

https://www.softwareadvice.com/resources/etl-vs-elt-for-your-data-warehouse/ — Channa, May 24 '20 at 16:08

score 0 · Answer 1 · answered Jul 27 '16 at 16:27

0

I'd say it depends on your target DWH platform and its performance capability along with the ability to perform the transforms internal. I have done this in the past with a Teradata DWH where we decided to let the Teradata system do the heavy lifting since the tool we were using just couldn't perform the transforms as well and we could write SQL that would be much more efficient.

Also, are you using an ETL tool? Is there optimization potential with it?

answered Jul 27 '16 at 16:27

nr99

46
3

Actually, the organisation with which i work within my traineeship uses SSIS as an ETL tool. And the objective right now is to Come up with a solution for real-time data integration into the DWH from the different sources (ERP). I suggested CDC with SSIS to manage the real-time issue. And i was wondering how can i combine it with ELT for optimisation reasons. But the DWH's actual structure doesn't allow this major switch from ETL to ELT or host all those transormation processes. That's why i want to know what kind of "adjustments" should be introduced to the DWH in order to achieve this? – B. Mar Jul 29 '16 at 09:45

score 0 · Answer 2 · answered Sep 21 '16 at 11:45

We have implemented a similar strategy to yourselves, using CDC as the data capture tool and then SSIS (a home grown version actually) to transport the data via a job. In our case, SSIS portion simply dumps the data into a staging DB, off the main client production data server (separation of resources) periodically and then Stored Procedures in the staging DB take over to complete the somewhat complex transform operation. In this way we can output to multiple targets, as we have both DW and DM targets to provide the data to, depending on the source. This allows for both flexibility, and removing the heavy lifting from the Extraction side, together with a much easier maintenance schedule for changes.

score 0 · Answer 3 · answered Sep 19 '17 at 12:15

Switching between ETL and ELT can depend on many factors: business need, data warehouse used, ETL/ELT tools in use and what's available, data sources, and support/admin staff capabilities. Since ELT is still maturing, there are not the breadth of tools available to do the work. If your business can handle the possibility of shouldering more of the transformation work, or if you're lucky to find a tool that does what you need; then ELT is a reasonable switch.

Depending on the size and structure of your data, loading time may decrease. However, your ETL based data warehouse may not be structured to support ELT. And because ELT does the transformations at query time, if the data is loaded into a schema structure that is not optimized for queries, that could be your bottleneck. A respository with the wrong structure likely creates more problems than switching to ELT solves.

So to answer your final question: it all depends on how the data warehouse infrastructure and schema structure do not match. You need the processing power to handle ad hoc queries with transformations. You need a DWH structure that supports loading basically all your data -- and is query friendly.

Moving from ETL to ELT?

3 Answers3