‘Reverse ETL’ Can Help Companies Operationalize Data Lakes
Enterprises building data lakes and warehouses have been turning to third-party vendors to bring in data. Building connectors to applications likes Salesforce is tedious and requires data engineering expertise. Plus, the SaaS platforms change up their data schemas and API details.
These data pipeline vendors extract data from the source systems and load the data into the warehouse. They will also clean up or sync the data between different systems, either before it gets to the warehouse, or after.
Today, more and more companies are opting for the latter.
With the first approach, transforming the data before you get to the warehouse, you lose traceability, said Goutham Belliappa, vice president of artificial intelligence engineering at Capgemini North America.
“When people want to understand the validity of a metric that was transformed in flight, there is no way to audit it and identify the progress of the data,” he said. But when the transformation happens afterwards, there is more control. “The moment can be recreated or alternative algorithms and formulas can be simulated with ease,” he said. “Further, business analysts can validate the data and the algorithm freely and have the ability to make tweaks or modifications.”
If the data transformation step happens before the data gets to the warehouse the technology is called ETL — extract, transform, load. If the transformation happens in the data warehouse itself, it’s called ELT — extract, load, transform.
Either way, the company ends up with a centralized place where data is stored.
“The loading and storage of raw data lets you ingest data once and then share it and distribute it for many uses without having to go back to the source applications,” said Doug Henschen, vice president and principal analyst at Constellation Research.
Now, enterprises can have a birds’ eye view of all of their data, can aggregate their operational or financial metrics, or get a 360-degree view of their customers.
One company that built just such a data warehouse is Zeplin, an online collaboration platform for UI designers and frontend developers with millions of users.
It uses a Snowflake warehouse to collect operational data, such as how end users interact with its products, and third-party data from SaaS platforms like Salesforce for customer relationship management and Intercom for customer support.
“We collect all this data through a traditional ETL solution and centralize it in a data warehouse,” said Jason Feng, senior growth marketing manager at Zeplin. “Now we have all this information in Snowflake about users are doing, what teams are doing. But the problem is that making the data useful to the rest of the company is hard.”
To get at the view, enterprises typically have data scientists run analytics using business intelligence platforms. The data scientists then produce reports. This is often a slow and time-consuming process and doesn’t put the data in the hands of the employees when they need it.
“For example, when someone converts from being a free user to a paid user, we want to notify the sales team,” said Feng.
To solve this problem, some enterprises are building new data pipelines, sending the reports back out to the systems their employees are actually using.
“But that’s difficult,” said Feng. “You need a data engineer to do it, you need to maintain it. Each time something changes, you need to have the data engineer make the updates.”
At companies where he previously worked, there were data engineers on staff to manually build those data pipelines, he said.
But Zeplin, with fewer than 100 employees, had no engineers to spare.
Instead, when Feng joined the company six months ago, he spearheaded an effort to deploy a third-party “Reverse ETL” tool.
Reverse ETL grabs information from the data warehouse and sends it out to Salesforce or other destinations, where it’s available to the sales team in real time, or close to real time.
