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
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
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.
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.