Data Integration and Management

There are two approaches to consider for data integration: ELT and ETL (see the blog on Don't Push ELT Around for some of the benefits of the ELT approach. There are also some interesting benefits beyond simply the performance benefits resulting from a localized transformation.

For example, ELT also provides the best possible SQL code generated on any given machine. Since this SQL is localized for the environment where they run, they can be optimized, streamlined, and maintained in their native environments. The ELT approach will generate native SQL for the underlying databases and optimize the code for these databases. There is no translation from pseudo-code into SQL. No generic SQL is required.

On the contrary, the E-T-L approach generates SQL code which is a translation of proprietary code into SQL. This approach will have many limitations: not all transformation functions can be properly translated (the SQL logic would be so different that the entire workflow would have to be re-designed); because the transformations are translated, the resulting code tends to be “all or nothing”. For instance, an ELT can generate code on any system, source and or target, to take advantage of the specifics of the different technologies available. A typical ETL engine will only allow for one of the systems to be used.
The next challenge will be the level of control on the generated code. Because ETL tools will translate from their internal code into SQL, they cannot easily allow for the customization of the generated code (they have to give you the option of going back to a transformation in the engine: doing so with customized SQL code becomes problematic). With an ELT logic, the developers have total control over the SQL code that is generated, and can modify it as needed from within their graphical interfaces to make sure that the generated code is the best possible code for any given platform.