Extract, Transform, Load (ETL) is a fundamental concept when managing data from disparate systems. It’s what FME was built on and the foundation for most data integration technologies. But with iterations of this acronym popping up everywhere—ETL, ELT, ETLT, and more—how do you know which workflow you need?
Let’s look at the difference between ETL, Reverse ETL, and a newer concept, Zero-ETL.
What is ETL (Extract, Transform, Load)?
ETL is the traditional workflow used in data integration processes. It involves extracting data from various sources, transforming it (e.g. cleaning, quality assurance), and loading it into a destination system, usually a data store.
For example, data can be extracted from sensors, transformed and validated, and loaded into a centralized data warehouse for analysis.
What is Reverse ETL?
As the name implies, Reverse ETL works in the opposite direction. It involves extracting data from a centralized data store, transforming it to fit operational systems, and loading it back into various business applications where it can be acted upon directly.
For example, a telecommunications company could extract subscriber usage data and service preferences from a central analytics platform and update customer service and billing systems to ensure tailored service offerings and accurate billing.
What is Zero-ETL?
Zero-ETL aims to bypass traditional ETL pipelines and establish a direct connection between the data source and destination. The key here is the ability to generate real-time insights and connect to data streams. Zero-ETL simplifies the data architecture and improves the ability to generate actionable insights because data is available immediately.
For example, a utilities company could connect real-time data streams with infrastructure databases, allowing teams to instantly detect and respond to service disruptions.
How to create ETL, Reverse ETL, and Zero-ETL workflows with FME
ETL and Reverse ETL workflows can be built using FME Form’s visual interface. Workflows can be run on demand or automated using FME Flow.
Zero-ETL requires automation to ensure data is continuously synchronized. For this, deploy the workspace in FME Flow, and create an Automation to trigger the workflow as new data arrives (e.g. webhooks).
Data flows can be unidirectional, i.e. bringing data from system A to system B, or bidirectional/multidirectional, i.e. keeping data synchronized between various systems. For example, a new submission from ArcGIS Survey123 can trigger a workflow in FME Flow, which integrates the data with Cityworks—a unidirectional data flow. On the other hand, a multidirectional data flow could mean keeping ArcGIS Online, Cityworks, and Salesforce synchronized by triggering an Automation whenever any of these three systems are updated.
See demos in our webinar, The Zero-ETL Approach: Enhancing Data Agility and Insight.
How to handle high-velocity data streams in ETL workflows
An ETL workflow takes time to run, and sometimes, data streams come in faster than the workflow can handle—think customer orders, transactions, Internet of Things (IoT) data/sensors, any scenario where the volume of data coming in is very high.
Zero-ETL is perfect for cases like this. Reduce data volumes by reading only what you need, and use FME Flow Streams to listen for the data continuously with low latency.
Coming soon in FME: Data Virtualization
We’re excited to enable FME users to create OpenAPI-compliant APIs on top of all FME data types. This will allow you to deliver real-time, Zero ETL data via a web API. Stay tuned!
Case Study: City of Henderson’s digital plan submission portal
The City of Henderson created a self-serve web app using FME. Users can upload a digital CAD plan, validate it, and have it automatically integrated into the municipality’s GIS.
Case Study: City of Naperville’s automated work order system
The City of Naperville automates service requests and work orders using FME by keeping systems synchronized across departments.
Learn More
Choosing the right ETL approach can maximize the value you derive from your data and impact the efficiency of your data management strategy. For a deeper dive into these concepts and to see demos, watch our webinar, The Zero-ETL Approach: Enhancing Data Agility and Insight.
