As I discussed in my last posting, ELT or pushdown optimization can significantly improve data warehousing performance, while reducing costs. I also mentioned it’s important to implement a data integration platform that supports both traditional ETL (Extract-Transform-Load) and ELT (Extract-Load-Transform) methods, because different situations call for different methods.
Taking this thought a step further, metadata is the critical binding agent that should cut across all data integration approaches, be they ELT or ETL or some other combination such as ETLT. If the actual transformation logic and business rules are defined as metadata, the choice of where the processing actually occurs, be it the ETL server or the database/data warehouse, becomes a matter of configuration rather than of coding. A truly metadata-driven data integration platform enables you to design and reuse the same transformation rules, regardless of whether you choose ELT or ETL for data warehousing.
Take the example of a leading high-tech manufacturer, which recently presented a session on its ELT implementation at the Teradata Partners conference. In conjunction with its Teradata-based enterprise data warehouse, the company implemented the pushdown optimization capability within the Informatica data integration platform because their prior approach was not meeting the latency requirements of the users, and demands for scalability were continuing to increase. But in addition to performance enhancements, the manufacturer also took this approach because they needed the improved visibility and auditability enabled by a fully metadata-driven approach. All integration and transformation logic was captured as metadata in the Informatica platform, even if it was processed in the data warehouse. Moreover, Informatica’s metadata management capabilities enabled the manufacturer to quickly perform impact analysis and to automatically generate a comprehensive data lineage, including any processes that were pushed down, for auditability.
So if you are considering an ELT implementation, it’s important to focus not only on the performance improvements and cost savings, but also on how the metadata will be captured and managed.