Andy on Enterprise Software

Appliances and ETL

November 19, 2010

I attended some interesting customer sessions at the Netezza user group in London yesterday, following some other good customer case studies at the Teradata conference in the rather sunnier climes of San Diego. Once common thread that came out from some sessions was the way that the use of appliances changes the way in which companies treat ETL processing. Traditionally a lot of work has gone into taking the various source systems for the warehouse. defining rules as to how this data into be converted into a common format, then using an ETL tool (like Informatica or Ab Initio etc) to carry out this pre-processing before presenting a neatly formatted file in consistent form to be loaded into a warehouse.

When you have many terabytes of data then this pre-processing in itself can become a bottleneck. Several of the customers I listened to at these conferences had found it more efficient to move from ETL to ELT. In other words they load essentially raw source data (possibly with some data quality checking only) into a staging area in the warehouse appliance, and then write SQL to carry out the transformations within the appliance before loading up into production warehouse tables. This allows them to take advantage of the power of the MPP boxes they have purchase for the warehouse, which are typically more efficient and powerful than using regular servers that their ETL tools run on. This does not usually eliminate the need for the ETL tool (though one customer did explain how they had switched off some ETL licences) but means that much more processing is carried out in the data warehouse itself.

Back in my Kalido days we found it useful to take this ELT approach too, but for different reasons. It was cleaner to do the transformations based on business rules stored in the Kalido business model, rather than having the transformations buried away in ETL scripts, meaning more transparent rules and so lower support effort. However I had not appreciated that the sheer horsepower available in data warehouse appliances suits ELT for pure performance reasons. Have others found the same experience on their projects? If so then post a comment here.