How to Evaluate an ETL Tool?

by Katherine VasilegaNovember 29, 2010

With different technology requirements and business user expectations across organizations, a number of parameters exist to help you make a choice.

(Featured image credit)

Table of Contents

Out of the box vs. custom

As companies seek for software to fit their data integration needs, the first question they need to decide upon is whether to build a custom or to obtain an out-of-the-box ETL tool. The best answer to this question is simple: it depends on your needs. In any case, both approaches have to be carefully examined, so here are some advantages and disadvantages of each method.

The primary reason to obtain an out-of-the-box ETL tool is that it helps companies to minimize the time and cost required for developing custom code. Many companies are confident that ETL tools from vendors are more effective at delivering projects on time and within budget. There are also other advantages—such as a wide range of rich built-in features, the ability to be reused for future projects, the simplicity of ETL maintenance, etc.

On the other hand, the time spent on evaluating, selecting, adopting, and learning the product can be tremendous. Training costs and support fees, as well as not fitting 100% to your business needs are also among the drawbacks. On top of that, a proprietary data integration tool may cost up to $500,000 on annually (including licensing and maintenance), according to Gartner and Bloor Research.

Three-year TCO for the top data integration systems (image credit)

Some organizations believe that custom ETL tools save time and money on support and maintenance of a data warehouse, because hand-coded software is 100% geared to specific business needs. More flexibility for complex data sources and transformation mappings are also among the advantages of in-house projects. Meanwhile, the time needed for development from scratch may take months or even years—depending on the size and complexity of your information infrastructure. Besides, these products don’t provide all the rich functionality of proprietary ETL software packages and can hardly be reused for other projects without additional customization.

Each of the approaches may have its own particular implementations across industries and vary a lot depending on your requirements. Just keep in mind that the goal of an efficient ETL tool is to integrate data quickly, practically, and cost-effectively.

At the same time, an open-source ETL tool can be something in between, combining the pros (and cons, of course) of the two models.

Implementation approaches to customer data integration (image credit)

Criteria for vendor selection

Still, before making a decision about purchasing commercial software or downloading an open-source ETL tool, you need to research the market. What are the features you should look for in the ideal ETL software?

Here is a list of parameters to help you decide if a certain ETL tool suits your technology requirements:

Supported operating systems and runtimes, desktop/SaaS, server/client
Available connectors for data sources needed (ERP, CRM, databases, etc.)
Data formats (CSV, XML, unstructured data, etc.)
Task scheduling
Logging, error handling, alerts

These are some of the core integration features that one should pay attention to:

Available data transformation functions/operations
Support for handling data quality
Data cleansing or enhancement
Metadata support, business rules implementation
Batch mode / real-time
Synchronization (if available): one-way or bidirectional

Many ETL tools have either strong transformation or strong cleansing capabilities. If your data can potentially be corrupted, make sure your product has strong data quality capabilities.

The high-level role of ETL in data warehousing (image credit: IBM)

On the dawn of data integration, ETL tools were supposed to be used by IT people. Today, these products are managed by business users, too. At the same time, the increasing complexity of data sets requires ETL vendors to implement sophisticated features. So, what is the right combination of functionality and ease of use?

The following functionality is required for ETL tools to meet business user expectations:

A visual interface that enables to drag and drop data elements (such as source/target connectors)
An intuitive GUI for building data maps and setting transformation rules withour coding
Reusable artifacts (such as prebuilt mappings)
Comprehensive user manuals and demos

It is also helpful to obtain customer references from each ETL vendor or search community forums to get the reviews from end users who needed the same functionality as your organization requires. With this approach, you may also find out whether these tools are difficult to troubleshoot and maintain—or not.

Finally, remember that you should define your master data requirements and set data integration goals before selecting an ETL tool.

P.S. For an open-source ETL tool that can be either customized or used out of the box, feel free to explore Apatar.