Extract, Transform and Load (ETL) is a data integration approach that was supposed to make data integration quicker, easier and reusable.
In practise, many of us may have found that ETL did not solve our data integration challenges – tools being highly inefficient and overly complex.
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 – 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 like Sunopsis capitalised by delivering so called ELT capabilities – reducing the need for handcoding.
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”
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.
New, high performance ETL solutions such as Syncsort Connect deliver on the original promise – easy to code extract, transform and load capabilities that take the load off the data warehouse.
In one case study, discussed on the eBook 5 tips to break through ELT roadblocks, the National Education Association identified savings in excess of USD100000 by replacing custom PL/SQL scripts with high performance ETL.
Contact us to learn more.
