We recently stumbled across an interesting blog post (http://trianzblog.com/wordpress/?p=203) by Subhankar Das of Trianz that summarized fairly comprehensively the ongoing debate about data integration: do you Extract, Transform and Load data from one data repository to another, or do you Extract, Load and then Transform.
The question is of obvious interest to us because core to the back end of SMI Suite is an ETL utility that takes the data from the Service Manager database, then does a lot of translation to make the nasty array fields, BLOBs and CLOBs readabale, then loads that data into the relational SMI database.
The difference really boils down to where the translation takes place. Under an ELT model, the translation happens once the data has already been loaded into the target repository. And, as Subhankar points out, new technology is making this an increasingly viable option, and is gaining traction in the marketplace.
Check out Subhankar’s full piece for an excellent breakdown of the pros and cons of each approach.
For Westbury it’s an ongoing discussion that will, no doubt, shape our future development initiatives and give us some serious food for thought.
Tom
Whether you choose ETL or ELT is really a matter of operating requirements.
For example, you’d select ETL when you want to transform “before” you upload and store the transformed data in its target location. You’d select ELT if you want a quick download and storage, with not time to transform during the download and with the intent to transform at a later time. You might also select ELT if you need a local copy of the original data (for example in a Data Warehouse) for historical and/or audit purposes.