You're building a new data solution for your business, and you need an ELT tool to make storing and analyzing data more manageable. You've read a ton of articles and talked to a bunch of folks about what they use. And now you’re staring at a long list of possible options.
How do you go from a list of tools down to one winner—especially when making this decision is only one small part of your job and you can't afford to spend a lot of time mucking around with it?
In this article, I'll walk you through the differences between legacy ETL and modern ELT tools and how to decide which ELT tool will best fit your needs, putting you on a path to spreading data goodness throughout your company.
What is an ELT tool and why do you need one?
Before we dive in, a quick recap. Let's say your company is using Salesforce, Shopify, Google Analytics, and an online accounting package. You've also got a database around your product(s) and your support system. You want to be able to pull data from all of these systems so you can get a 360° picture of what's going on with your company.
You know that to effectively analyze that data (and not just become a spreadsheet wrangler) you need to:
If you’re into DIY ETL, you could painstakingly:
...Or you could use a tool that handles the heavy lifting involved in doing this work.
Traditionally, these tools would extract (E) the data and put them in temporary tables, transform (T) the data in the temporary tables, then load (L) the data into wherever you are going to store it; that's why they’re called ETL tools.
Now that computers are far more powerful and storage is ever-cheaper, the industry is trending towards tools that skip the time-consuming step of using temporary tables and instead extract (E) the data, load (L) it directly into a data warehouse, and then perform the transformation (T). This approach is known as ELT.
Regardless of what these tools are called, how do you decide between them?
ELT tool providers like to give you an insanely long laundry list of all the reasons why they're awesome. But really, there are only six factors you need to worry about:
ELT tools tend to be pretty powerful, but many ELT tools look like they were designed by data engineers for data engineers. So one factor you want to consider is how easy it is to set up a new ETL process or to tweak an existing one.
As you are assessing an ELT tool's ease-of-use, be sure to have several user scenarios in mind that cover the main issues raised by your data sources. Drag-and-drop is great, but it can get pretty messy if it's not designed to accommodate, for example, joining together the pieces of a complex data model.
When you’re scrambling to get a new data solution up and running, it's easy to forget just how critical maintenance will be to your success. So be sure to ask questions such as:
No matter how easy-to-use a tool is, at some point you're going to need help. And the fact that the tool you chose had a marginally better feature set isn't going to matter to your users if they can't get critical work done because your data’s failing to process and you don't have the support you need.
Some questions you should ask:
Using an ELT tool to design an integration with a data source is a lot less work than writing a bunch of code to do it. You know what's even less work? Having somebody else take care of integrating most of your data sources.
These days, some ELT tools include custom integrations for commonly used data sources. If an ELT tool has custom integrations for most of the data sources you use, the amount of time it'll take to get your data warehouse up and running can drop dramatically. Given the right integrations, instead of taking weeks or months to integrate your data sources, a big chunk of your data could be ready to go within a matter of hours or even minutes.
In a lot of cases, a single ELT tool won’t have native integrations to all your data sources. That’s no big deal, so long as the tool can:
Finally, if you aren't planning on storing your data in a standard data warehouse—e.g., if your data storage uses noSQL—make sure that whatever ELT tool you choose integrates seamlessly both with your data source and your storage.
Cost is often a top factor when choosing between tools. Some questions you'll need to consider:
You may find that it's impossible to do an apples to apples comparison of the costs of different ELT tools. That's okay. When you’re doing your first cut of potential candidates, all you need is a very rough guess of how much they will cost.
And when you are making your final choice? If you aren't reasonably sure about the difference in cost, it might be worth reaching out to folks you know to find out if anyone uses it and what their experience has been. You might also want to do a quick proof of concept to get a better sense of how the pricing model works in your particular case.
Finally, you need to spend at least some time answering the question, can I expect the tool will be around two years from now? If you're talking about, say, Microsoft's ELT tools, you can be pretty sure they aren't going away tomorrow. But if the tool is brought to you by a new startup with a tiny customer base, you might want to think twice.
How do you vet a company's stability? Do a little research to see how long they've been around, how big your customer base is, what kind of funding they have, etc. You don't need to spend a lot of time on it—a very rough guess is all you need to prudently reduce the odds that you will get burned.
What about open source ELT tools? On the one hand, open source tools can be tricky: they don’t have a company or a large group of contributors dedicated to building and maintaining them, so there's always the danger they’ll stop being updated. On the other hand, if an open source tool's backers or community starts to crumble, you've got time to move to another platform; if the latest and greatest online ELT tool startup goes belly up, your ELT infrastructure could disappear overnight.
And now for a shameless plug: if you're trying to decide between ELT tools, you should take a serious look at Panoply. Here's why:
Sound too good to be true? Try it for yourself—for free, without entering your credit card information. You can have your ELT pipeline set up and be syncing data in minutes...all without a single line of code.