Qualities of a Well-Built ETL Tool
Here is a guest blog by Hayden Beck –
Hayden has written content for some of the biggest logos in the Silicon Slopes (Utah) and works exclusively as a freelance writer producing great content. A graduate of the University of Utah, Hayden spends his free time enjoying every winter sport the Rocky Mountains have to offer!
So you’ve determined that your use case doesn’t merit the time and energy required to create a customSQL solution , and you’re ready to pick an ETL tool to help you get what you need out of your data. The problem is, you’re not sure how to tell if you’ve found a tool that will meet your needs.
That’s where this list comes in. We’ve put together a list of attributes you can use as a metric to determine if an ETL tool will be effective for your use case. Ideally, you’ll be able to find one that meets all of these qualifications
Good ETL tools can integrate into a wide variety of APIs, allowing it to pull or push through them. This is important because, in the end, you’re going to need your ETL tool to interact with a lot of different programs and APIs to make use of your data, and you’re going to be very frustrated if it doesn’t do some of that with ease. Not every tool plays well with others, and many lack integration with major APIs, meaning they can’t access your data where you need them too. A tool that effectively integrates with a wide range of external tools will meet your needs better than a tool that doesn’t.
Ease of Use
The whole reason you’re choosing an ETL tool is to avoid needing to deal directly with SQL. Why, then, would you choose a tool that requires a firm grasp of SQL to be able to use? The best ETL tools have an intuitive interface that simplifies as many functions as possible, making it easy to train non-developers to use. That way, you can free up your devs’ precious time, and still get the work done.
You don’t want to implement a tool, only to have to program SQL workarounds every time you need something done. Automation in an ETL tool is key—the more tasks you can automate, the faster and easier it is to get the work done. And let’s face it, when working with data and databases, there are a lot of tasks we’d like to automate; for instance:
- Edit columns
- Add constants
- Calculator functions
- Collapse columns
- Date operations
- “Group by” operations
- Rank and window
- Select columns
- Set column type
A good ETL tool will do all of this, and more, so find one that meets all your current and future needs.
There are three primary elements to how well any computerized tool functions: speed, accuracy, and stability. Accuracy is how good the machine is at minimizing computational errors that result from bugs and glitches. Stability is how consistently a tool can handle the workload without quitting and crashing. Speed is simply how fast the work gets done, since computers are used primarily because they think so much faster than humans. You want a tool that gives you the right answers, does it quickly, and doesn’t break every time you use it. This is all a product of how well a system is programmed, and how thoroughly it’s been tested.
The big advantage to commercial ETL tools is the amount of effort that’s already been put into them, meaning that a lot of that work should already be done for you. Most ETL tools are reliably accurate, so most of the variance you’ll see will be in speed and stability. The fastest tools can load 1TB or more of data in less than an hour, and the most stable have uptimes at 99.9% or above.
While it has no bearing on functionality, there’s a lot of value in how a given tool looks. An effective tool that nevertheless looks like an MS-DOS prompt will ultimately feel like something the Flintstones would use. Having a smooth finish increases a user’s appreciation for, and value estimation of, the tool. People like having nice, new things, and will be happier to use something that feels current, rather than something that seems older than they are.
Bonus: Comprehensive Solutions
If you’re looking for a tiebreaker to determine between two or more likely candidates that meet the standards above, consider whether the ETL tool is part of a larger suite of tools, like a BI package. Some commercial options are part of an all-in-one solution that can likely solve a wide range of needs in your organizations, rather than having to purchase multiple tools to get the same effect. Having all your tools in one cohesive product, like Domo’s ETL tools and Talend Open Studio, can be a huge benefit, especially since you won’t have to get separate tools to integrate with each other.