How to Choose the Right ETL Tool for Your Business

For many companies, data comes from many places. Most companies utilize various cloud applications to run different parts of their business. A standard cloud application stack may include: a MySQL or PostgreSQL database for application data, a Salesforce for CRM, Hubspot or Marketo for Marketing Automation and Zendesk for Customer Support data. With that, consolidating all this data into useful insights has become the new challenge.

How do we solve this new challenge? By consolidating data into a single repository. It starts with getting all that data into one single data warehouse that allows for easy organization, transformation and analyzing. While your business is actively adding to its growing list of cloud applications and tools, it’s essential to start utilizing the data that lives inside those systems in a more useful way. Remember, data is only useful when it’s used.

What is an ETL?

Before you can sync your Salesforce data with a data warehouse, you need to realize one thing: you need an ETL tool.

ETL is short for extract, transform and load. An ETL tool is a data pipeline that will extract data from a source (like Salesforce), transform it into a workable state and load it into a data warehouse. What you need to know about an ETL tool is that it enables your organization to perform powerful analyses on all your data.

The second realization is to choose what type of ETL tool you’ll need that fits your business.

You have two options:

1. Build your own ETL process
2. Buy an ETL tool

This article is meant to help you choose an ETL tool, so we’re not going to get into building your own ETL tool. Engineering a data pipeline for all your data coming from applications like Salesforce, Zendesk, Hubspot, etc into a data warehouse is not an easy task. And with your business continuing to expand its use of tools, you’ll likely be stuck in an eternal loop of building new pipelines.

Luckily, there are a number of ETL tools that have already conquered this task and built powerful solutions, so that you don’t have to do that impossible work. So now the question is, which one do I choose?

We’ve evaluated many ETL tools and can offer a bit insight to help you choose the right one for you.

When is the right time?

At Chartio, we believe in data democracy, and we do what we preach, so all of us from Sales to Engineering have access to all the data. Our primary source of data was our application database that housed all our product data. This was great, but the data we were getting was too one dimensional. There was no additional context or story, and each department (Sales, Marketing, Engineering, Success, Finance) was desperately trying to find new ways to get the data from the tools they were using every day. We were running reports separately in Salesforce and then combining them manually with our application data.

This wasn’t just a Salesforce issue, we were running the same manual reports across all our tools. Soon enough, analysis became harder and less fun. Our energy was spent trying to figure out how to get the data instead of actually using the data. There had to a better way and that’s when we knew we had to find a way to consolidate all of this data.

What types of ETL tools are there?

There lots and lots of different ones. We never thought about building our own since we knew that the engineering cost was not worth it. Plus, we were adding on so many new tools that keeping up would be near impossible.

Our ETL tool search was also driven by our Sales and Marketing teams. Those two teams were the most determined to get the data they needed and had the most amount of applications to consolidate. While our Sales and Marketing teams are very capable in their roles, they had no prior experience in data warehouse creation or building an ETL process. We then focused our search on finding a tool that could get us up and running quickly and without too many technical roadblocks.

For us, it was important to narrow down the search to the type of ETL tool could empower our business teams to quickly find value and get the insights they needed. This meant finding an ETL tool that has streamlined workflows that can quickly move data from one source to another. There are tools that not only let you sync/replicate data but also allow for transformations and data enrichment. These type of ETL tools often have more complex workflows and support more technical data sources like APIs, log files, or other unstructured sources. Since our focus was more with popular cloud applications, choosing an ETL tool that offered connections to those specific types of applications, with a focus on being more business user oriented, was the way to go.

Will I need a Finance Guru? A Data Wizard? An Engineer?

An important part of choosing the right tool for you is budget. With the right amount of money, you can buy a tool that has all the features and functions you can dream of. But of course, it’s not that easy.

ETL tools usually calculate price based on rows of data, so evaluate how much data you want and how much this will cost you. Figuring out budget and cost was one of the key factors that helped us determine which tool was right for us.

The other very important part was understanding where the data was going, because most ETL tools do not provide a data warehouse solution. Here are a few questions to answer while evaluating an ETL tool:

• Do I have a spare database lying around?
• What is this data warehouse you speak of?
• Will I need an Engineer for this?
• Can I do this myself?

Initially, we didn’t factor in the cost of a data warehouse to store our consolidated data. Luckily, we spoke with our Engineering team and they were able to provision the data warehouse cost into their budget, which both technical and business teams benefited from. When then had an Engineer help us set up a new data warehouse that would ultimately house all incoming data from our ETL tool.

If you don’t have a spare database lying around, or don’t want to have to manage a data warehouse yourself, there are solutions that can provide this service for you. If you’re looking for a fully managed data warehouse solution, Panoply.io, is a great tool that lets you spin up an Amazon Redshift in minutes without needing any database experience. Additionally, you can also use Google BigQuery as a data warehousing service. 

Deciding Factors

Our deciding factors came down to:

1. A tool that is both quick and easy for data replication from one source to another (ex: Salesforce to our data warehouse)

2. A cost efficient tool that’s within budget

3. Finding a tool that does not require heavy set-up/maintenance help from our engineers

4. *Ability to connect to the sources** we use and could potentially want (Think of it as an investment into your data journey)

These were the initial deciding factors for us as we went into evaluation mode. It’s a short list but we didn’t really know what we were getting into just yet.

Choices

There are plenty of ETL tools out on the current market. While this list is not complete, we recommend evaluating Astronomer.io, Blendo, Fivetran, Segment, Stitch Data, Panoply.io, Treasure Data or Xplenty. Again, this list is not comprehensive and not all the options, but a great starting point for your ETL tool search. 

As we began our evaluation, one thing became clear: Not every ETL tool is the same. As we evaluated these tools, we became more aware of additional factors that helped in choosing the right tool for us. So that original list we had, we added a couple of more things.

5. Finding an ETL tool that ensures data quality: The data has to be trustworthy and reliable. This means that rows of data have to be complete, schema mappings have to be accurate and usable and consistency is key. The syncing and replication process can’t break unexpectedly without any notice or warnings and random rows of data can’t be missing. After all, this is the data that will drive our Sales, Marketing and other business decisions. People needed to be able to rely on this data like their life depended on it.

6. Finding an ETL tool that ensures that all the data is available: Different tools may pull different data, tables or columns. For example, one tool might give you data on a daily basis versus weekly basis or show current data versus historical data. This mattered as we tested out each tool because using one tool meant we might not be getting the data we needed to accurately build out our reports and workflows. Realizing this, we prioritized building out core reports that utilized several data sources and tested them against each tool to see which tool can give us the most complete picture.

7. Finding an ETL tool that ensures that the data is usable: Another important factor was how the data was structured in our data warehouse. How the schema, tables and columns were named and structured determined how easy it was to use the tool. Some tools might choose to abbreviate certain column names and other tools might have complicated names that are hard to understand. This was also one of the biggest factors in distinguishing which tool was most usable for our Sales and Marketing team as they knew the applications they were using, but when it came to translating that into a relational database format, that was another challenge.

8. Finding an ETL tool that has responsive support: If there’s something weird, And it don’t look good, Who you gonna call? Support Team! (Ghostbusters!) Many people in your organization will rely on this data to do their jobs and it will occasionally break from time to time, and when that happens, you’ll want to know right away. Responsiveness of their support team became a very important factor when we had questions regarding about the tool itself, errors or setup.

There were other things like user management, product roadmap and the tool itself that also factored into our decisions but didn’t rank as high.

How does this help you?

The ultimate advice in choosing an ETL tool: know your business and evaluate thoroughly. We eventually went with a few ETL tools, rather than choosing just a single option, because this best suited our needs as a business.

Our search for an ETL tool was driven by our Sales and Marketing teams and they knew the applications they needed to connect with to get valuable business insights. This was key to making sure our ETL tool would be the right fit. Making sure the tool would provide reliable and trustworthy data was another key component in choosing the right tool. It wasn’t until we went through the evaluation process ourselves and tested rigorously that we found out other important deciding factors in choosing the right ETL tools for us.

And with all that data now at your fingertips, you can focus on the fun stuff like building awesome charts and dashboards that helps drive key business decisions and unlock valuable insights.