We used to call these ETL tools (Extract, Transform and Load), then they realised that Extract, Load and Transform was better sequence, then they decided they needed branding that suggested something grander, so they now are called Data Integration Tools.
I worked for one of these companies for four years. I rewrote the main training course because it really was quite difficult to learn how to use this thing. But most of my time was spent as a consultant, which meant writing C programs, UNIX shell scripts and PL/SQL (sorry!) to fill the gaps and overcome the poor performance of the beast.
I then worked for several organisations that were using the “visual programming” variants of these tools. Every one of those companies was writing code in other languages to fill the gaps and overcome the performance problems of these tools. When you’ve spent that much money on something, it is hard to admit that you have made a mistake.
My unreserved recommendation now is: “Do Not Buy One Of These Products!”. There are much easier, more reliable and massively cheaper solutions.
The vendors of these tools claim many benefits which we would definitely want:
In my experience, these tools do not deliver the things that they promise, and they completely miss some of the things which are really important in ETL development. These things include support for analysis, design, testing and project management, and the very practical necessities of version control and release management.
You can find the 2024 Gartner Magic Quadrant for Data Integration Tools on the Informatica web site. It excludes Ab Initio (which is one of the leading tools) because Ab Initio is not prepared to be a Friend of Gartner. Gartner does not test these tools, they just read the sales literature and interview a few users. Now, is someone who has paid hundreds of thousands of pounds for one of these tools, and been given a trip to Las Vegas to talk about it, going to admit that it was a bad idea? Not in my experience.
IBM, Oracle and Informatica come out top of the Gartner Magic Quadrant. I have first hand experience of all three (as well as Ab Initio). None of them are great, but the IBM tools are dire. IBM bought an incompatible bunch of tools when the best ones had already been snapped up. This was DataStage, based on the Pick operating system and the Basic programming language. There aren’t many collections of computer technology worse than this.
In the sales pitch for all the ETL tools we see the visual programming tool: “look how easy it is to put these three sources onto the canvas, link the required fields to the target and the ETL job is written!”. Well that’s fine for a demo, but in the real world we have hundreds of fields and the nice little diagram in the demo turns into an unintelligible rats’ nest.
The kinds of jobs which ETL tools do are best described by programming languages - things that we type into a text editor. (On this topic, I am pleased to find myself in the company of Andy Hunt and Dave Thomas, authors of the wonderful book: The Pragmatic Programmer. They have a section called “The Power of Plain Text” (Chapter 3, Section 14). Every developer should read it.)
We prefer SQL for most of our work because it is a non-procedural language which is well designed, based on robust, mature theory, and allows set level operations which are efficiently carried out in the database server. We also use the database loaders which are the most efficient way to get large quantities of data into the database. Where necessary, we use procedural languages. Typically this is for manipulation of files (such as XML files or other files with multiple record types) or interaction with other services, such as file transfer or messaging systems. Systems built with the visual programming ETL tools invariably have large chunks of SQL and smaller chunks of other languages embedded in them. By the time you have a real-world ETL job with bits of SQL and other languages embedded in it, the visual representation of the job is completely useless.
The visual programming tools also encourage the developer to a lower level of programming, simulating sequential procedural steps rather than the non-procedural, set-level approach which is SQL. The trickiest areas of programming are the procedural parts: conditional, branching and looping. These are the areas where we find the most bugs. SQL avoids this completely.
It takes a long time to learn the leading ETL tools. As a result, developers with skills in these tools are in short supply, of very mixed ability, and command high salaries (or daily rates). These developers have a very high investment in the tool they specialise in. This makes them reluctant to consider alternative approaches. A more insidious effect is that they become focussed on the tool and not on the needs it is being put to. They often take design decisions which are disastrous for the database or for the people using the data they create. We have come across cases of appalling performance on a powerful data warehouse applicance, because of the insistence on dragging the data through the tool for a job that could be done in the database with efficiency levels orders of magnitude higher. We have also seen cases of extremely complex reports because of the use of data structures which were convenient for the ETL tool.
Using the leading ETL tools it is expensive and impractical to have separate development, test and production systems. It is extremely difficult to promote changes through the environments, especially when changes are being made only to a small part of the total system. Reverting to an earlier version, after finding a critical bug in a new production release for example, is practically impossible with these systems. In one medium-sized database system it took a whole day to extract the ETL processes from the ETL repository in the development environment, and more time to import that release into the test environment. Then we had to do the same from test to live. This process was a major deterrent for a safe release process.
Metadata Management would be a wonderful thing if it could be made to work. IBM provides a tool which requires so much work to create and maintain the metadata that people are less likely to use it than they are to use spreadsheets. (We don’t consider spreadsheets to be good tools for metadata management, but that is what is used in practice, if anything is.) We have experience of several person-years being used to set up metadata in the IBM tools, only to abandon any attempt to use it. The other products are not much better.
Data Quality is very important for many reasons, including maintaining credibility with the organisation’s clients. Where we have worked in successful Data Quality teams, it has been easy to set up the discovery and monitoring of data quality issues, a bigger challenge has been to get anyone to do anything about it. We always end up with data cleaning rules in data warehousing systems, but these are only defences against some of the impacts of poor data quality. The real problems, which occur in the source systems, are extraordinarily difficult to fix. The operational systems fix the problems they need to fix in order to operate acceptably. They generally have no motivation to fix problems which cause trouble for other operational systems or for the data warehouse.
I have never seen anything in the leading ETL tools which alleviates data quality problems and could not be done with simpler techniques in script-based ETL.
Data Profiling is a great idea. It allows us to identify data quality issues in source systems before they even become a problem. Unfortunately, if they have not become a problem yet then we have even less chance of getting the source systems to fix them. Data Profiling tools (Trillium, DataFlux, and those sold as part of the leading ETL tools) are expensive and generally misused. Data Profiling analysts tend to run the reports on a small sample of the data and present the reports without further analysis. The reports then get filed and ignored.
The IBM data profiler is limited to such small samples that its chances of finding any data quality issues are minuscule.
Master Data Management (MDM) would also be a good thing. In the UK, the Royal Mail Postcode Address File (PAF) is a great tool for cleaning addresses. This can be licensed without getting an MDM system. Other kinds of Master Data Management are very specific to the organisation that owns the data. The best implementation of an MDM is a data warehouse with strong data quality monitoring and customised data cleaning rules implemented in SQL.
Buying a Master Data Management tool would be a waste of money.
With the reluctance to act on data quality issues, which we see in most organisations, data governance does not even get started. Add to this the difficulty of doing metadata management properly and you might as well forget it. It is not worthwhile for most organisations to spend the money on these tools. Free or home-grown tools (which we have) are useful for developers and the best we can hope for is that some of the pressure applied by determined developers will eventually get some improvements made. Governance is a management issue, not a technical one. Applying expensive tools to data governance may increase your chance of a trip to Las Vegas but will not be a sensible investment for your organisation. (Why would you want to go to Las Vegas anyway? Do you want to lose your money and cheat on your spouse? All in the tackiest of en=vironments?)
This currently relies on people with expertise in particular subject areas, and it relies on those making changes to consult the relevant experts. Sadly, this does not happen in many cases. When it does, the advice of the expert is often ignored, because the project is well down the road to being implemented and no-one wants to admit that it is going down the wrong road. Again, we need to have the right management commitment, at the highest level, to even begin to improve our impact-of-change analysis. It certainly is not worth spending money on tools without a massive change in management priorities, plus some robust evidence that the tool will help.
We have a very lightweight, free, tool for grouping scripts into jobs and for running those jobs, with automatic logging, and control over interaction between jobs and between scripts within jobs.
Our scripts are organised into jobs and are all managed in our version control system. (Subversion and Git are free and the best version control systems available.)
We have a release process which lets us build releases at the job level and promote them through the environments. We can easily back out a release if we need to. We can see a complete history of changes which can be useful if a change does cause a regression error. As well as ETL code, we keep documentation in the Subversion repository, whether it is produced by us or by external partners. Another important tool for us is Trac (also free) which gives us a wiki, a ticketing system and tight links with the Subversion repository. There are conventions we follow which make it easy for new team members to learn to produce efficient, robust ETL processes. New team members must be SQL experts and have a good understanding of relational database design principles. Beyond that they can learn everything on the job without slowing down existing team members significantly.
My experience of the leading ETL tools tells me that if we were to spend hundreds of thousands of pounds on tools, the infrastructure to run them and the training required (or rare and expensive experienced developers) we would be worse off rather than better off. You may find the following relevant. They are informal views of some of the experience described here.