ETL tools extract, transform, and load data, moving it from SaaS applications and databases to a place where you can do data processing and analytics, such as a data warehouse or data lake. ELT tools do the same thing in a different sequence (extract, load, and transform), storing raw data in the data warehouse and then doing data transformations there, which is possible thanks to efficient, scalable cloud-based workloads.
In both cases, ETL connectors are the components of an ETL or ELT tool that establish connections to data sources (both databases and applications), building data pipelines and enabling the magic of extraction and loading to happen.
You can find cloud-based ETL tools and open source ETL tools that support anywhere from half a dozen to hundreds of data connectors. When you're doing an ETL tools comparison, a large number of supported connectors can be a favorable sign, but it doesn’t tell you how well-suited any particular tool is for your organization’s use case. Here’s what you need to know about data connectors to make that decision.
What applications do you use?
Chances are, your business uses a lot of applications. According to BetterCloud, organizations use an average of 80 IT-sanctioned SaaS applications today. Bigger businesses use more than that. And we suspect that figure is low, because many organizations use SaaS applications that aren’t IT-sanctioned.
And that’s only SaaS applications. Many organizations run additional applications on local servers, which rely on databases. The reason you need an ETL tool in the first place is to move data out of all of these silos and into a place from which you can use it to surface insights. But which of the many available ETL platforms will do the best for you?
To answer that question, you need to know which applications your managers rely on most. And to answer that question, you need to know which applications you’re running.
Therefore, step one is to create a list of all the applications you use. Most organizations don’t have such a list at their fingertips, thanks to disorganized record-keeping and decentralized application purchases.
Remember the phrase “IT-sanctioned”? Ten years ago the IT department may have been in charge of application development and administration, but the popularity of SaaS applications makes it easy for other departments to pay for software as a service from their operating budgets. IT managers call it “shadow IT” to make it sound menacing lest they lose control, but now it’s standard practice. The downside is that no one in your organization may know exactly what software everyone is running.
Fortunately, there are SaaS spend management tools for monitoring and managing the purchasing and licensing of all the SaaS applications in use within a company. They’re worth using to start building your list, but you should also consult your department heads and other managers to learn what they can tell you about the software they’re using.
This consultation step is also useful as a general audit. Chances are you’ll find your company is paying for more than one SaaS tool that you’re no longer using. But we can leave that task for the IT department.
Along with discovering all of your SaaS tools, you should also use network management software that can scan your servers and your network and create an inventory of the applications you use in-house. When you do that, you’ll find databases like MySQL and PostgreSQL, which often serve as the back-end databases for home-grown applications. You may also be running them on cloud platforms. Those are the first data sources you should put on your list of potential connectors.
The most popular data sources
Your organization probably uses Salesforce, Facebook Ads, Google Ads, and Google Analytics. That’s an easy bet, because those are the top cloud applications that businesses use. We’ll give good odds that you’re running four or five from among Zendesk, HubSpot, Jira, MailChimp, Mixpanel, and Marketo too.
You probably use some combination of the following types of data sources:
- Customer relationship management (CRM)
- Enterprise resource planning (ERP)
- Marketing and advertising automation
- Customer service
- Event tracking
The most important ones are probably those you use for marketing and sales activities — revenue-generating applications, in other words. Put them alongside your databases on your initial list of must-have connectors.
Run that list by all of the managers in your organization who use analytics software. Your software inventory might have turned up analytics tools like Tableau, Google Data Studio, Microsoft Power BI, Looker, or other cloud or on-premises analytics tools. Be sure to ask anyone who uses them what data they’ve been analyzing. Departmental managers may have more applications they want you to add to your A-list.
The destination question
Once you’ve covered data sources, you have to figure out which cloud data warehouse you want to replicate your data to. If your organization already uses one, put support for that platform on your ETL platform evaluation checklist.
If not, read our enterprise data warehouse guide for some background and a discussion of three of the top alternatives. You should choose a data warehouse before you select the best ETL tool — and you should probably choose an analytics tool before you choose a data warehouse, but that’s a topic for another day.
Now that you have your list of data sources and destination in hand, you can vet ETL platforms based on whether they support the connectors and the data flows you need. Read about the top business considerations for choosing an ETL tool and the top technical considerations, then take advantage of free trial accounts to see how well different tools meet your needs.
Need more real-world examples? Read how businesses like New Relic, Oldcastle Infrastructure and Strava use connectors to power their modern data stack in our library of case studies.