Intelligent Enterprise

Better Insight for Business Decisions

RSS
Webcasts
Whitepapers
Subscribe
Home

May 31, 2003

Is Hand-Coded ETL the Way to Go?

Absolutely yes, or absolutely no, depending...

by Gary Nissen
edited by Ralph Kimball

Tool-Based VS. Hand-Coded ETL

Tool-Based ETL

Simpler, faster, cheaper development. The tool cost will make up for itself in projects that are large enough or sophisticated enough.
Many ETL tools have integrated metadata repositories that can synchronize metadata from source systems, target databases, and other BI tools.
Most ETL tools enforce a consistent metadata-driven methodology that all developers must follow.
Data flow and data lineage documentation often can be generated automatically from a metadata repository.
ETL tools have connectors prebuilt for many source and target systems,which is advantageous if you're working with many sources or targets.
Most ETL tools deliver good performance even for very large data sets. Consider a tool if your ETL data volume is very large or if it will be in a couple of years.
Some ETL tools help you conduct change impact analysis further down the road.

Hand-Coded ETL

Automated unit testing tools are available in a hand-coded system, but not with a tool-based approach. For example, the JUnit library (www.junit.org) is a highly regarded and well-supported tool for unit testing Java programs. There are similar packages for other languages. You can also use a scripting language, such as Tcl or Python, to set up test data, run an ETL process, and verify the results. Automating the testing process through one of these methods will significantly improve the productivity of your QA staff and the quality of your deliverables.
Object-oriented programming techniques help you make all your transformations consistent for error reporting, validation, and metadata updates. You may not have this degree of control with the parameter-setting and snippet-programming style of ETL tools.
You can more directly manage metadata in hand-coded systems. Actually, metadata-driven hand-coded systems are much more flexible than any alternative.
A brief requirements analysis of an ETL system quickly points you toward file-based processing, not database-stored procedures. File-based processes are more direct. They're simply coded, easily tested, and well understood.
A tool-based approach will limit you to the tool vendor's abilities and their unique scripting language. But you can develop a hand-coded system in a common and well-known language. (In fairness, all the ETL tools allow "escapes" to standard programming languages in isolated modules.)
Hand-coded ETL provides unlimited flexibility, if that is indeed what you need. You can literally do anything you want. In many instances, a unique approach or a different language can provide a big advantage.