Extract, Transform and Load (ETL) is a data integration approach that was supposed to make data integration quicker, easier and reusable.

In practice, many of us may have found that ETL did not solve our data integration challenges – tools being highly inefficient and overly complex.

The challenges of ETL

I remember working on a project some years back to create a single view of the citizen by combining hundreds of millions of records from various sources into a single target.

A “leading” ETL tool was selected to do the data integration.

After more than 9 months of development, the actual processes to integrate the data were still incomplete and ran for days. In the end, we replaced all of the ETL development with PL/SQL – it took us two days and the resultant processes ran in hours.

The rise of ELT

This kind of problem was not unique to our project. Unable to keep up with growing demands for information, ETL developers were increasingly compelled to push transformations down to the data warehouse – in many cases reverting back to hand coding.

Vendors capitalised by delivering so-called ELT capabilities – reducing the need for hand-coding.

To quote searchdatamanagement – what is ELT:

“One of the main attractions of ELT is its reduction in load times relative to the ETL model. Taking advantage of the processing capability built into a data warehousing infrastructure reduces the time that data spends in transit”

searchdatamanagement

ELT carries its own cost

A hidden cost of ELT is the proliferation of costly database staging areas to do transformations. The cost is not just physical – the complexity of managing these staging areas means that the IT department must invest in more database administrators to manage the nightly loads, create indexes, drop temporary tables and so on.

And, of course, we need to employ SQL programmers to maintain the (often) thousands of lines of poorly documented and poorly understood SQL code.

In effect, ELT simply centralises the SQL code that must be maintained – it does not replace it.

Increasing business demands for effective intelligence (in many cases near real-time) mean that ELT approaches are unsustainable.

Delivering high-performance ETL

New, high-performance ETL solutions such as Precisely Connect deliver on the original promise – easy-to-code extract, transform and load capabilities that take the load off the data warehouse.

Contact us to learn more.