[DI] ETL versus ELT

When does ETL win?

• Ordered transformations not well suited to set processing.
• Integration of third party software tools best managed by Informatica outside of the RDBMS (e.g., name and address standardization utilities).
• Maximize in-memory execution for multiple step transformations that do not require access to large volumes of historical or lookup data (note: caching plays a role).
• Streaming data loads using message-based feeds with "real-time" data acquisition.

When does ELT win?

• Leverage of high performance DW platform for execution reduces capacity requirements on ETL servers - this is especially useful when peak requirements for data integration are in a different window than peak requirements for data warehouse analytics.
• Significantly reduce data retrieval overhead for transformations that require access to historical data or large cardinality lookup data already in the data warehouse.
• Batch or mini-batch loads with reasonably large data sets, especially with pre-existing indices that may be leveraged for processing.
• Optimize performance for large scale operations that are well suited for set operations such as complex joins and large cardinality aggregations.

[DI] A Taxonomy of Data Integration Techniques

 

 There are three main approaches:

1.ETL Approach: (1) Extract from the source systems, (2) Transform inside the Informatica engine on integration engine servers, and (3) Load into target tables in the data warehouse.

2.ELT Approach: (1) Extract from the source systems, (2) Load into staging tables inside the data warehouse RDBMS servers, and (3) Transform inside the RDBMS engine using generated SQL with a final insert into the target tables in the data warehouse.

3.Hybrid ETLT Approach: (1) Extract from the source systems, (2) Transform inside the Informatica engine on integration engine servers, (3) Load into staging tables in the data warehouse, and (4) apply further Transformations inside the RDBMS engine using generated SQL with a final insert into the target tables in the data warehouse.