ETL tools: What you do and don't want

Choosing the right ETL tool can be a project. Read what our integration expert says to look for in ETL tools, and what to avoid.

What should I look for in extract, transform and load (ETL) tools? What should I avoid?
Ahh. ETL tools. Can't you just do it all with COBOL? (Insert laugh track here.)

Seriously, there are some core functions that most ETL tool vendors offer today. These include:

  • The ability to deal with multiple input data formats (such as flat files, SQL databases, SAS data sets, etc.)

    More on ETL tools

    Get the specifics with the definition of ETL

    Learn how Pfizer has replaced ETL with data virtualization tools

    Read about automated data integration tools vs. manual coding

    Support of a GUI interface that allows the "drag and drop" of data elements from source to destination
  • Built-in data profiling and analysis functionality allowing the examination of source data
  • Built-in data transformation functions (such as data type conversion, date reformatting, etc.)
  • Support for data quality/cleansing functionality (either built-in, or via interfaces to popular data quality tools like Group 1, DataFlux, or Business Objects)
  • Metadata support, allowing for the capture and identification of data transformation and business rules
  • Job scheduling functionality
  • Version control of the ETL maps
  • The ability to manipulate data either inside or outside the target database (aka: ETL versus ELT)

What you should avoid

My ETL guys suggest staying away from code-generating ETL tools, which are difficult to troubleshoot and de-bug. They also recommend steering clear of ETL tools that don't provide error tracking and logging functionality.

And a final thought: ETL isn't just for breakfast anymore. What I mean by this is that ETL isn't exclusive to data warehouse projects, but can solve a host of data movement and transformation problems. Best of luck with your tool selection!

For more information on ETL tools

This was last published in June 2007

Dig Deeper on Extract transform load tools



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation


Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

It's not surprising your ETL guys advise you to steer clear of code-generating ETL tools! They would all be out of a job! The ONLY time you ever need to debug code-generated ETL code is when some fool has played around with it, or tried to perform some tasks manually when they ought to have been handed over to the code-generator.

There is NOTHING wrong with code-generation tools - they deliver results faster, the code is error-free and optimised, and they are self-documenting.

I don't see a downside here at all.

ETL tools on the other hand have a much worse track record of data warehouse delivery. They are slow to deliver, require more resources and additional tools to get the job done.

It is quite well proven now that Data Warehouse Automation tools like BIReady, Kalido, WhereScape and others get the job done in a fraction of the time and cost of ETL tools.

Go tell that to your ETL guys!
Your readers may also find real user reviews for all the major ETL tools on IT Central Station, such as Adeptia, Pentaho or Oracle Data Integrator, to be helpful when making their selection. As an example, this ODI user writes in her review, "The tool has also made metadata management more efficient, and automated the process from relational source to Hyperion Essbase." You can read the rest of her review, as well as explore what others have to say about ODI and other ETL solutions, here: https://goo.gl/pqORk9