Reverse ETL: Data where business users need it most
Modern organizations are unlocking the value in their data to gain a competitive advantage. But when data is scattered around among different systems, it’s difficult to gain insight into operations or use data for meaningful reporting. According to the 2020 Matillion/IDG Marketpulse survey, data professionals spend 45 percent of their time preparing data for analytics.
With ETL, or with cloud-native data integration that includes push-down ELT, companies can create a single view of truth by consolidating all of their data into a single location, typically a cloud data platform. . With this centralized, shared data source , analysts can create dashboards and reports that drive better business decisions through crystal-clear insights.
But modern ELT doesn’t stop at the cloud data platform or the analytics tool. What if front-line business workers can use that enriched, transformed data straight from Salesforce or their cloud storage as they’re interacting with customers or when they’re trying to make a sale?
That’s the premise of what’s becoming known as reverse ETL. Reverse ETL takes the data loaded into the cloud data warehouse and makes it more accessible and actionable to more business users by pushing this data back into the operational systems where the organization can use it for improving customer experiences, solving issues faster, or providing that next best offer right on the phone when the customer calls.
What is reverse ETL?
Traditional ETL: The beginning
Traditional ETL arose out of the need to consolidate data so it could be used for analytics. Using ETL, organizations could extract data from multiple systems, transform it, and then load it into an on premises data warehouse in a highly cleansed, conformed and governed format. Later this process moved to a cloud data platform such as Snowflake, Amazon Redshift, Google BigQuery, Microsoft Azure Synapse, or Delta Lake on Databricks. From there, this consolidated data could be used to run analytics and build reports that support better business decisions. Although it’s possible to code your own data pipeline solutions, it’s complicated and time-consuming. The average time spent on data preparation for a typical data analytics project is one week: and that’s with many traditional, on-premises ETL tools. Modern business simply can’t wait a week.
From ETL to ELT
ELT has evolved the ETL process into something faster, simpler, and more scalable. Cloud-native ELT is a more effective way to consolidate data by extracting it from source systems, loading it into the cloud data platforms, and then using the power of the cloud data platforms to do data transformation there. With the transformation happening INSIDE the cloud data warehouse, users can take full advantage of the power and performance of the cloud. But cloud ELT really requires a cloud-native ELT solution. The most likely destination for this transformed data is into cloud-native business intelligence tools, where analysts and engineers can do analytics and reporting, and increasingly into machine learning and artificial intelligence models, where data scientists can extract deeper, often predictive insights.
Reverse ETL: From anywhere to everywhere
Now we’re hearing a lot of buzz about something called reverse ETL. Essentially, it’s another kind of data pipeline that extends the value of the data in the cloud data platform to more people in the organization. With reverse ETL, transformed data goes from the cloud data warehouse back out to operational systems: applications like Salesforce, databases, cloud storage, and more systems that are used by marketing, sales, and support. During reverse ETL, the information extracted from your central data warehouse or data lake is transformed to adhere to the data model of the target systems. Reverse ETL solutions can also copy unstructured data from a data lake out to the target systems.
Reverse ETL is intended to enable “operational analytics.” It takes all the useful data from the cloud data platform and syncs it back to operations systems so that business teams can access and act on the same data that’s being used by the analysts, in real time. Operational analytics supports better day to day operations by giving business users the data they need in the applications they use every day.
Actually, reverse ETL is nothing new: for years, data engineers have been transforming data and moving it back into databases and applications. Commonly this was done from Operational Data Stores or (ODS). Today, in Cloud Data Platforms, the ODS and enterprise data warehouses are combined and allow the data engineers more flexibility. What’s changed is the level of labor required. Until recently, reverse ETL required a lot of coding, time, and effort. But in the cloud, with a cloud-native data integration tool, what used to take days or weeks now takes a fraction of the time.
Reverse ETL vs ETL: Syncing data back to operational systems
With ETL in place, analysts can use the single view of truth in the data warehouse to create reports and analyze data using BI tools. Reverse ETL extends the usefulness of this common data resource by syncing this data back out to operational systems.
Business users can act on the same data that the analysts have been using to create their reports. And within the applications they use every day, they often have their own robust analytics capabilities that they can tap into at any time, rather than having to rely on IT to build out a BI dashboard or report.
Essentially, reverse ETL is an extension of ETL. By enabling the flow of data from the data platform to the people who can actually use it, workers across the organization are empowered with insight that can help them make better decisions in the moment.
|Copies data from numerous source systems into one centralized location, typically a cloud data platform||Syncs data out of the cloud data platform to multiple operational systems|
|Supports BI and analytics used by IT and analysts||Supports operational analytics used by everyone, where they need it|
|Enables analysts to create dashboards and reports||Enables better decision making by business users when interacting with customers, selling, or offering support|
|Data is accessed in a central location by analysts||Data is accessed in multiple systems by numerous business users|
Reverse ETL in modern data platforms
As with ETL in general, when it comes to implementing reverse ETL, you have a choice between DIY and commercial solutions. You can build a solution out yourself. But If you plan to do so, you’ll likely need to give yourself plenty of time, since development never happens quickly. If you plan to use a commercial solution, you can expect a faster implementation.
You’ll find that implementing reverse ETL is similar to implementing ETL, and in fact, some of the existing ETL tools on the market already support reverse ETL. In fact, the pre-built integrations that come with a cloud-native ETL platform may support both input and output to sync data back to the original source.
In addition to supporting operational analytics, some of the other use cases for reverse ETL include:
Sales: Using reverse ETL to move customer data back to CRM systems like Salesforce can give sales staff greater insight into the customers and customer segments to target for their sales campaigns.
Marketing: Marketing teams can run more effective email campaigns through marketing automation platforms and chatbot applications like Intercom when they have access to better, more personalized data on customers and subscribers in order to more effectively anticipate their needs.
Customer support: What if your support people had access to product usage data that could help them provide better customer service? Or what if you could sync consolidated customer data into your support application, giving support information that could help them better prioritize tickets?
Data automation: If your business users are going to data teams to ask for spreadsheets or reports based on the consolidated data in your data warehouse, reverse ETL can help automate that process and relieve your data teams from having to manage those types of requests.
The bottom line in many of these use cases is that better access to useful data can enable better customer experiences. When front-line workers are armed with current, accurate data, they can deliver a more seamless and personalized customer experience.
Matillion and reverse ETL
Matillion ETL is a cloud-native data integration platform that allows you to perform ELT or reverse ETL with the help of your cloud data platform. Matillion provides an extensive list of pre-built connectors to on-premises and cloud databases, SaaS applications, documents, NoSQL sources, and more.
Matillion ETL has an intuitive graphical interface that makes the ETL and reverse ETL processes easier, but does not compromise on underlying complexity and sophistication. removes complexity from the ELT or reverse ETL process by providing an intuitive, graphical interface. And Matillion works with leading cloud data platforms including Snowflake, Amazon Redshift, Google BigQuery, Microsoft Azure Synapse, and Delta Lake on Databricks.
Matillion ETL is enterprise ready, helping data teams at some of the world’s largest organizations turbocharge cloud data ingestion and transformation workflows without sacrificing power, capabilities, or enterprise security.
See how reverse ETL can work for you
If you want to see how reverse ETL works using Matillion’s pre-built connectors, request a demo.
Matillion Adds AI Power to Pipelines with Amazon Bedrock
Data Productivity Cloud adds Amazon Bedrock to no-code generative ...Blog
Data Mesh vs. Data Fabric: Which Approach Is Right for Your Organization? Part 3
In our recent exploration, we've thoroughly analyzed two key ...eBooks
10 Best Practices for Maintaining Data Pipelines
Mastering Data Pipeline Maintenance: A Comprehensive GuideBeyond ...