A Reverse ETL Primer

In our data-first economy, ETL (extract, transform, load) has long been the mainstay for ingesting data to a centralized data warehouse. However, data teams have recently started to use ETL to send data from a warehouse into other third-party systems. This process is known as “Reverse ETL” and can help organizations improve data and operational efficiency. This post provides your data teams with a quick overview about using reverse ETL and its benefits.

ETL/ELT vs. Reverse ETL – What’s the Difference

ELT and ELT Overview

Before we talk about Reverse ETL, let’s first review the traditional ETL process. ETL has been the dominant data warehouse integration methodology for over 30 years. The technology is mature, and the process is well understood. ETL extracts raw data from sources, transforms that data on a secondary processing server, and then loads the result into a target database, data lake or data warehouse. The data warehouse is consumed in a read-only mode, and the data contained within is used downstream for online analytical processing, business analysis and intelligence.

Figure 1. Traditional ETL Process

Additionally, the emergence of cloud data warehouses saw the ELT (extract, load, transform) approach increase in popularity and supplant ETL in many performance-critical situations. This is because ELT does not require data transformation to complete before the warehouse-loading process can begin and consequently can load data more quickly. Data transformations in ELT are often performed inside the data warehouse via the execution of pushdown SQL or python scripts.

Figure 2. The ELT Process

Nevertheless, both ETL and ELT achieve the same goal of transferring data from business applications (e.g., Adobe Marketo Engage, Oracle Netsuite, Salesforce, Zendesk etc.), databases (e.g., Microsoft SQL Server, MySQL, Oracle, etc.) and third-party systems into data warehouse targets for downstream analysis.

The Reverse ETL Difference

Reverse ETL is the flipside of ETL/ELT. Data is extracted from the data warehouse, transformed to meet the formatting requirements of the third-party system, and then loaded into the third-party system for further action.

Figure 3. The Reverse ETL Process

In certain situations, the warehouse data can also be transformed inside of the warehouse before it’s extracted and loaded into the target systems, as the diagram below illustrates.

Figure 4. Reverse ETL With Transformation In Situ

Why Would You Move Data Out of the Warehouse?

I’ll admit it seems counterintuitive, especially after the time and effort you’ve expended to implement infrastructure that reliably loads your enterprise data into the data warehouse. So why on earth would you want to move it anywhere else? The answer lies in the changing use of the modern data warehouse.

As mentioned earlier, data warehouses have been historically used to analyze information that inform long-term strategy, but the business frequently needs to make strategic decisions in real-time. In addition, data warehouses are increasingly becoming the central repository for unconventional data, such as customer experience information or product usage data.

Consequently, a disconnect arises between traditional business intelligence tools, the data warehouse and the operational systems. Reconciling the analytical data can impede insights and result in a slower response to daily business challenges. However, using Reverse ETL to complete the loop of data integration from the warehouse into systems of record enables business teams to act on the same data that has been powering their business analytics. Reverse ETL enriches third-party systems with insightful data, eliminates manual errors and operationalizes data flow throughout an organization. The ability to make data actionable is at the core of Active Intelligence.

Reverse ETL and Active Intelligence

Let’s look at a real-life example. A manufacturing company uses Snowflake as its data warehouse, Salesforce as its CRM and Qlik as its data integration and analytics platform. The operations team builds an analytics application for company executives to perform customer lifetime value (LTV) analysis as part of strategic account planning. However, with Qlik’s Active Intelligence Platform and by using Reverse ETL, it’s now possible to the push that insight into Salesforce for account managers to use and action.

Thankfully, Qlik makes this whole loop relatively easy. Our Data Integration solution creates data pipelines that continually load Snowflake with enterprise data. Then our Data Analytics solution creates in-the-moment analysis to model and calculate LTV. Finally, an update can be triggered via Qlik Application Automation, to push that intelligence into the Salesforce application, where it’s available for Salesforce users to consume and action.

Why Is Reverse ETL Making Nose in 2022?

There are many reasons why technology is adopted, but none so powerful as the product market fit, i.e., the right product, solving the right problems, at the right time. And, I believe this is the situation with the current state of the data market and Reverse ETL.

Conclusion

The flow of data from the warehouse into operational systems is no longer a pipe dream, but rather an integral component of a company’s analytics strategy, with data teams leading the charge. Companies wishing to modernize their analytical and data processes, explore new paradigms, and realize the potential benefits of Reverse ETL, as well as push their companies deeper into a state of active intelligence, are flush with business opportunities. Departmental or even enterprise-level efficiencies could be achieved; operational logjams and stopgaps could be replaced with more than just workarounds but rather fully functional and integrated technologies; and flatfooted responses to old data succeeded by fleetfooted ones to in-the-moment business intelligence.

Do you think Reverse ETL is an innovation or just an extension of the traditional ETL/ELT metaphor? I see it as a force multiplier that can allow organizations to continue to evolve to meet and respond effectively to each business moment.

What's all the rage around Reverse #ETL and how can it make a difference in your business? @Qlik's @cbearman give's us a primer on why it can give businesses an edge in achieving #ActiveIntelligence with their #data.