ETL (extract, transform, load) has been around in the software world since the late 1970s, but its recent adoption has skyrocketed as data analytics become increasingly important to businesses of all types and sizes. Today, the ETL process is an essential part of data analytics workflows—but it only moves data from periphery business apps into a central data warehouse, not the other way around.
Reverse ETL does the opposite: it takes all the valuable data modeled in your central data warehouse and sends it out into your sales, marketing, and operations tools to be used in real time.
Read on to learn more about reverse ETL, how it works, some common misconceptions, and the tools you can use to perform it.
What is reverse ETL?
Reverse ETL is the process of moving data from your central data warehouse out to your operational systems, cloud apps, and other software solutions. Usually, ETL goes the other way—extracting data from periphery sources, transforming that data into the proper schema and format, and then loading that data into your data warehouse.
Reverse ETL has become a crucial tactic for data analytics teams to bring all the data orchestration work they’ve done into operational play.
You can use reverse ETL to move useful customer data into your SaaS tools, such as Salesforce, Marketo, Google Ads, Facebook Ads, and HubSpot, as well as your chosen digital experience platform (DXP), customer data platform (CDP), or content management system (CMS).
Reverse ETL is a crucial piece of the modern data stack that many companies are missing.
The difference between ETL, ELT, and reverse ETL
It’s worth a quick breakdown here because there is a similar process called ELT (extract, load, transform) that data teams also use. Popular transformation tools like dbt use ELT to transform data after it’s loaded to your data warehouse. So why can’t you just extract, load, and transform from your data warehouse to Salesforce?
Because Reverse ETL process is not as technically simple as extracting your data from the warehouse and loading it into another target database to be transformed. The extraction step in Reverse ETL needs reverse SQL to function and ELT tools aren’t designed for that.
Reverse ETL also moves critical data in a different direction — from your warehouse into live SaaS apps, marketing campaigns, marketing tools like Hubspot, and CRMs like Salesforce.
And it’s important to know that with that change in direction comes a different set of challenges. For example, Hubspot and Salesforce aren’t as advanced as your cloud data warehouse is when it comes to dataset changes. If you update your Salesforce instance with a reverse ETL and something doesn’t work, it’s not as simple as when you make a mistake with ELT.
That’s not to say Reverse ETL can’t be easy ands risks can’t be mitigated with the right purpose-built solutions. It’s just crucial to know so that you approach this data process as something different from traditional ETL or ELT processes.
Here are some quick definitions of each process to get them straight.
- ETL (extract, transform, load): The main data process that’s been around since the 1970’s that moves data from periphery and external sources into a central data warehouse
- ELT (extract, load, transform): A newer process that loads data into a data warehouse first and then performs the data transformation via data tools like dbt
- Reverse ETL: Uses reverse SQL to move data from your central data warehouse out to your business applications so the data is actionable
Now that those are defined, let’s take a look at some common misconceptions about reverse ETL.
Common misconceptions about Reverse ETL
Reverse ETL is a fairly new process that data teams are just starting to adopt and understand. It also sounds very similar to ETL and ELT even though those are technically different in critical ways. That’s lead to some common misconceptions about Reverse ETL and here are a few of the most important ones that come up regularly.
1. Reverse ETL only moves data
A common misconception about reverse ETL is that it is only used for syncing data between two systems—moving data from your cloud data warehouse to the database of your business apps.
But that’s not the only use for this process. Reverse ETL can also be used to trigger campaign actions, personalized experiences, and workflows in SaaS tools like HubSpot.
For example, when a data job finishes running in the warehouse, it can sync to an app to trigger email sends to specific audiences. That’s far more dynamic than just moving data to a different place.
2. Reverse ETL is just an ETL process in a different direction
Reverse ETL requires reverse SQL. It is far more complex than just moving data from point A to Point B.
3. All modern data tools can perform reverse ETL
Other data analytics tools we love like Stitch and Fivetran are largely used for data visualization and dashboards—they are ELT tools. But they don’t perform reverse ETL. You need to build or buy a specific reverse ETL solution to get this functionality.
Now that we’ve looked at reverse ETL and what is and isn’t, let’s look at some use cases to determine when you’d want to use it.
When to use reverse ETL
Modern data infrastructures are typically missing the key piece where data is activated for business use. Reverse ETL comes into play whenever you want your business applications to be able to use the data stored in your data warehouse.
For instance, you might store customer data in the Snowflake Data Cloud or on an SQL server, which makes that data incompatible with Salesforce. You can use reverse ETL to move your customer data into Salesforce so it will trigger campaign events and workflows.
Think about anywhere you can put your data to work by automating business processes that would otherwise be manual and clunky. Use reverse ETL to deliver customer data to your marketing and sales apps so your teams can focus on doing their jobs instead of juggling CSVs. Following are a few specific use cases for reverse ETL.
Use cases for reverse ETL
There are many ways to use reverse ETL—from triggering HubSpot marketing workflows to sending Slack notifications. One of the most popular ways to use reverse ETL today is for personalizing customer experiences. This common strategic goal is data-heavy, requires real-time and trustworthy data sets, and depends on effective data integration and automation.
Personalizing customer experiences
Imagine your data warehouse stores your customer data, like customer lifetime value or order history, and you have a customer experience platform like Adobe Experience Cloud that delivers personalized content. You likely have a data pipeline going from Adobe to your data warehouse where your data science models live. But without reverse ETL connecting your data to Adobe, your experience manager can’t factor in something like lifetime value to send a special content offer to customers in a certain set who perform a specified trigger action.
Reverse ETL can also be used for much simpler tasks where automation is just as valuable. Here is a list of some common requests your data analytics team might get:
- Marketing teams want to sync a list of loyal customers to Facebook Ads for a new campaign.
- Sales teams want a list of new email newsletter subscribers to import as leads into their CRM system.
- Customer solutions team wants a Slack feed of customers whose returns need to be processed.
- Product team wants a lifetime customer value score fed into their customer experience platform.
- Business intelligence wants a way to distribute data that replaces the slow process of educating people on how to read and interpret BI reports.
Bringing together data sets
The other most common use for reverse ETL is to bring together data from dozens, or even hundreds, of siloed systems. For example, one of your cloud applications might need data from Salesforce that’s stored in your data warehouse. There is already an ELT process moving the data from Salesforce to your data warehouse, but there’s no integration from the data warehouse to a client-facing web app where people want to access the Salesforce data.
A reverse ETL process can move that Salesforce data out to your web app and make it available to customers.
You can likely imagine a long list of scenarios where you need to connect data from one app to another. While you may already have other data pipelines moving data into your warehouse or data lake, reverse ETL is the missing process that data science teams can use to get the most out of their data.
Factors to consider for building or buying reverse ETL tools
To build or to buy? This is an age-old question in software development. There are always pros and cons when buying a solution versus building one in-house. These days, most companies don’t have the time or spare engineering resources to build something. And with reverse ETL, there are only rare cases where it actually makes sense to build your own tool.
Some of the biggest complexities to solve for are APIs. Every business app or tool has a unique API that’s constantly being changed and updated. Because of this you would need to build a unique integration for every tool in your data infrastructure, and then you would have to continually manage and monitor each of those integrations for changes—because even a small API change could break your reverse ETL process.
You may still have a case for building your own reverse ETL solution. Sometimes there’s just no way around it. But if you have the choice, it’s best to buy a purpose-built solution that will quickly activate your data and put your data models to work for your business.
What to look for in a reverse ETL tool
When you’re evaluating reverse ETL tools, you want to look for a platform that’s as easy to use as popular ELT tools like Stitch and Fivetran. You want to be able to schedule reverse ETL data syncs from your warehouse just as easily as you can ingest data to your warehouse.
Here’s a short list of the features you want to consider when choosing your reverse ETL tool:
- Easy-to-use interface for technical and non-technical users
- Scalable pricing with clear pricing model
- Integrations with your business apps for specific use cases
- Version control and sync flexibility
- Security and compliance standards for data
There’s a lot more that goes into evaluating a reverse ETL platform, but these give you good guidelines to start with.
Shipyard integrates directly with reverse ETL platform favorites Hightouch, Census, and Rudderstack. You can use this combination to quickly build reverse ETL data pipelines that sync your data warehouse with your business apps. Plus, if these tools don't have an existing integration to power your Reverse ETL, you can easily build a custom Blueprint directly in Shipyard.
Get started with reverse ETL
Shipyard’s data automation tools and integrations work with your existing data stack or modernize your legacy systems. These tools can fill in the missing parts of your reverse ETL puzzle, and it’s easy to quickly find out how and where they will work best for you.
Sign up to demo the Shipyard app with our free Developer plan—no credit card required. Start to build data workflows in 10 minutes or less, automate them, and see if Shipyard fits your business needs.