What are the pros/cons of custom development vs. an ETL tool?

    Requires Free Membership to View

    Login

The question of custom code versus the use an ETL tool is one that we are faced with each time we need to create a data warehouse. Each has its advantages and disadvantages, but for me it comes down to how serious is an organization about the care and feeding of the warehouse.

Creating custom code is a nice solution, it provides organizations with the ability to code exactly what and how they would like their transformation programs to be structured. Let's summarize both side of the custom-code option:

Advantages:

  • Low cost (usually have language/compiler in-house e.g.: C++, PL/SQL, Java)
  • Code is built just for your needs
  • Optimization of programs
  • Build what you want, when you want.
  • Large knowledgebase of programmers
Disadvantages:
  • Difficult to manage and maintain programs
  • Changes to warehouse could have large impact to many programs
  • No centralized repository of code
  • Limited metadata capabilities
  • Longer development cycle
  • Debugging is more difficult
  • Limited auditing capabilities
Using an ETL tool is the other side of the loading equation. An ETL tool usually provides a nice interface for users to create and manage transformations, let's look at the advantages and disadvnatges of these tools:

Advantages:

  • Nice visual interface to create and maintain programs
  • Centralized storage of programs
  • Version control of programs
  • Customization of transforms is relatively simple
  • Usually provides good metadata support
  • Quick deployment of transformations
  • Debugging built into most products
  • Transform scheduling
  • Transform auditing
Disadvantages:
  • High cost
  • Limited user knowledge of most products
  • Optimization sometimes is limited due to generic programming methods
The bottom line is can you afford not to get an ETL tool at some point in your data warehouse lifecycle? I think not.

For More Information

This was first published in July 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top