What is ETL (extract, transform, load)?

ETL data pipelines provide the foundation for data analytics and machine learning workstreams. Through a series of business rules, ETL cleanses and organizes data to address specific business intelligence needs, such as monthly reporting. When used in conjunction with data streaming technology, it is known as streaming ETL and it can enable real-time data processing and immediate insights.

ETL data pipelines provide the foundation for data analytics and machine learning workstreams. Through a series of business rules, ETL cleanses and organizes data to address specific business intelligence needs, such as monthly reporting. When used together with data streaming technology, ETL can also be applied to continuously arriving event streams—an approach known as streaming ETL.

While there are newer data integration options available to enterprises today, ETL remains a popular choice because it is compatible with legacy systems, can improve data quality and can enable the masking of sensitive data while it’s still in transit.  

ETL tools are available through open source and commercial solutions. They are also often integrated into database and cloud platforms. Key capabilities for ETL solutions include comprehensive automation, visual interfaces and the flexibility to switch between ETL and other data integration patterns.

ETL helps enterprises to gather information from disparate sources and transform it into trusted data that can be used for analytics and artificial intelligence in different environments, from on-premises servers to cloud platforms.

Its relevance to today’s technology landscape notwithstanding, ETL actually predates modern AI workloads and cloud computing. It was introduced in the 1970s as a process for integrating and loading data for computation and analysis.

ETL eventually became the primary method to process data for data warehousing projects. In the 1990s and 2000s, as the era of big data arrived, organizations were deploying ETL to integrate large volumes of data pulled from new sources, such as social media and Internet of Things (IoT) devices and sensors.

As cloud infrastructure became a staple of business in the 2000s, modern ETL software and tools enabled data migration and integration into cloud data platforms and warehouses including Amazon Web Services (AWS), Microsoft Azure and Snowflake. Also, such platforms allowed ETL solutions to deliver insights quickly and at scale, driving competitive advantages for the enterprises that adopted them.

The latest evolution in ETL is its deployment for streaming data integration, delivering up-to-the-second insights culled from continuous data feeds.

Discussions on ETL often include mention of a data integration process with a similar name: ELT, or extract, load, transform.

Terminology overlaps aside, the two processes are notably different. An obvious distinction lies in the order of operations. In ELT, data transformation happens at the end of the data integration process—not the middle. The most significant differences between the two processes include:

The easiest way to understand how ETL works is to understand what happens in each of the three steps—extract, transform and load—of the process.

The ETL process begins with the identification of specific data points that are the targets of extraction along with any potential “keys” that can be used to integrate data across disparate source systems. The source of input data is often tracked by using metadata.

During data extraction, raw data is copied or exported from data sources to a staging area. Data management teams can extract data from various sources, which can be structured or unstructured. Data sources include, but are not limited to:

• Relational databases or NoSQL databases
• CRM and ERP systems
• Internet of Things (IoT) devices and sensor data
• Microservices and APIs
• JSON and XML files
• Flat-file databases
• Email
• Web pages

In the staging area, the raw data undergoes data processing. Here, the data is transformed and consolidated for its intended analytical use case according to specific business rules. This phase of the transformation process can include:

• Data cleaning, filtering, aggregating and deduplicating.
• Data validation and authentication.
• Performing calculations, translations or summarizations based on the raw data. This process can include changing row and column headers for consistency, converting currencies or other units of measurement, editing text strings and more.
• Conducting audits to ensure data quality and compliance and computing metrics.
• Removing, encrypting or protecting data governed by industry or governmental regulators.
• Formatting the data into tables or joined tables to match the schema of the target data warehouse.

In this last step, the transformed data is moved from the staging area into a target data warehouse. In traditional ETL, this process involves an initial loading of all data, followed by periodic loading of incremental data changes and, less often, full refreshes to erase and replace data in the warehouse. In modern, streaming ETL, data loading happens continuously.

While ETL is traditionally a batch-oriented process, distributed event streaming platforms such as Apache Kafka extends ETL patterns from batch processing to continuously arriving event streams. Such streaming ETL can enable real-time data processing.

Kafka-compatible streaming processors like Kafka Streams, ksqlDB and Apache Flink enable data engineers to build ETL pipelines that ingest streams of events from source systems, transform the data while it is in motion and send it to analytics engines and downstream applications—all with low latency, unlike batch processing-based integration.

Such streaming ETL has other advantages over traditional ETL. Two key ones are flexibility and scalability. While traditional ETL pipelines are written with transformation logic designed for specific use cases and destinations, data streamed into Kafka from different sources can be transformed for varying use cases and consumers.

In other words, streaming ETL doesn’t require the tight coupling typical of traditional ETL, allowing for reusable data streams and reducing the time and labor required to reconfigure pipelines.

Such advantages notwithstanding, enterprises can still opt for traditional ETL over streaming ETL when complex transformations are necessary or when time is not of the essence. In such cases, traditional ETL and batch processing can be more cost-efficient than streaming ETL.

Beyond traditional ETL, streaming ETL and ELT, there are other approaches that are also used to facilitate data integration workflows. These methods include:

CDC identifies and captures only the source data that has changed and moves that data to the target system. CDC can be used to reduce the resources required during the ETL “extract” step. It can also be used independently to move data into a data lake or other repository in real-time.

Data replication copies changes in data sources in real time or in batches to a central database. Unlike ETL, it generally preserves data with limited transformation. Data replication is often listed as a data integration method. In fact, it is most often used to create backups for disaster recovery, and can also be used for database migration and other purposes.

Data virtualization uses a software abstraction layer to create a unified, integrated view of data—without physically copying, transforming or loading the source data to a target system. Data virtualization enables an organization to create virtual data warehouses, data lakes and data marts from the same source data. This approach might reduce the expense and complexity of building and managing separate platforms for each.

Stream data integration is an umbrella term for the different integration methods that allow the continuous consumption and loading of data streams in real time, including streaming ETL, modern ELT and change data capture.

Zero-ETL is a fast data integration approach in which consumers bypass traditional data pipeline steps to query data in its original form. In zero-ETL, transformation is deferred while data is shared through point-to-point connections between source and target systems. Such sharing can take place through data replication or federated querying, which entails querying data across multiple sources without requiring data movement.

There are data integration approaches that can be considered derivative of ETL but include some notable differences.

• ETLT, or extract, transform, load and transform, is ETL with an additional transformation step at the end. While some data transformation takes place in the staging area, as with traditional ETL, additional transformation takes place at a destination system such as a data warehouse. Typically the first transformation step entails basic changes while the latter transformation step includes more comprehensive changes.
• Reverse ETL changes the flow of traditional ETL. Instead of data being extracted from data sources, already transformed data from data warehouses is sent to an SaaS application, operational system or other downstream consumer. It is also known as operational analytics.
• TETL, or transform, extract, transform and load, is ETL with an additional transformation step at the beginning: transformation first takes place at the data source before being extracted. Then, it’s transformed again as necessary before being loaded into the target system. Enterprises might choose to use TETL when the source system can handle transformations, helping to reduce downstream processing needs.

Traditional and streaming ETL provides multiple benefits to enterprises managing growing data estates.

• Data quality improvement: ETL improves data quality during the transformation phase through methods such as data cleansing and deduplication, ensuring that data that arrives at a target data store is reliable and ready for use.
• Compliance support: ETL is often useful for businesses in heavily regulated industries such as financial services and healthcare because compliance and security measures, such as encryption, can take place before data is delivered to its destination.
• Legacy system compatibility: Organizations that use legacy systems, including on-premises servers with fewer capabilities than cloud-based environments, can deploy traditional ETL to ensure data is transformed as necessary before being loaded into target destinations.
• Managing cloud storage needs: Data transformation techniques such as filtering and aggregation can reduce the volume of data loaded into cloud data platforms, helping to reduce cloud storage requirement and costs.
• Support for AI workflows: Streaming ETL enables continuous delivery of high-quality data to AI and machine learning workflows. Supplying fresh, reliable information to AI models can reduce drift and improve prediction accuracy, helping enterprises successfully embed AI into everyday operations.

The modernization of ETL through data streaming technologies helped mitigate some of its traditional challenges by increasing its speed and flexibility. However, there remain important considerations organizations should consider.

• Upfront preparation: ETL requires a more complex tech stack, including servers for staging and transformation, in comparison to ELT. It also requires the identification of business rules and creation of transformation logic before integration begins.
• Infrastructure and labor costs: While ETL can help enterprises reduce the costs of cloud compute, they tend to spend more upfront on infrastructure and pipeline design in comparison to ELT.
• Schema management: Unexpected changes in the structure of source data—such as the renaming of columns or alterations to data types—can cause pipeline failures. Schema management tools such as schema registries can automate schema validation to prevent downstream issues.

The long history of traditional ETL and the advent of streaming ETL means that the process supports a wide variety of use cases across different industries.

• Handling sensitive data: During the transformation phase of ETL, personally identifiable information (PII) can be masked before it’s delivered to a target destination.
• Regular reporting: Batch ETL is a cost-efficient method for aggregating data for reports that are produced at scheduled intervals, such as sales reports and regulatory compliance reports.
• Fraud detection: Financial institutions can use streaming ETL to detect suspicious patterns and instantly flag anomalies indicating potential fraud.
• Predictive maintenance: The analysis of IoT data—ingested, transformed and delivered through ETL—can help organizations assess maintenance and repair needs to prevent equipment downtime.

In the past, organizations’ data engineers wrote their own ETL code to create custom pipelines. Today, there are various ETL tools and services that significantly reduce the labor and time it takes to build an ETL pipeline. Enterprises often take advantage of open source software, commercial solutions and tools integrated into database and cloud service platforms for this purpose.

Capabilities of leading ETL solutions include:

• Comprehensive automation: Automating the entire data flow, from data sources to the target data warehouse, saves data engineers from the tedious tasks of moving and formatting data—for faster results and more efficient operations.
• A visual, drag-and-drop interface: This function can be used for specifying rules and data flows.
• Support for complex data management: This capability includes assistance with complex calculations, data integrations and string manipulations.
• Security and compliance: The best ETL tools encrypt data, enforce data governance rules and might follow guidelines to comply with industry or government regulations, such as HIPAA and GDPR.
• Integration style flexibility: The option to toggle between ETL and other patterns, such as ELT and TETL, give enterprises the flexibility to choose the best integration style for each workload.
• Real-time streaming data integration: Modern ETL solutions include real-time data streaming and processing capabilities that have become integral to enterprise AI.

ETL remains a foundational data integration process, even as enterprises consider newer data integration patterns. Batch-based ETL is a reliable and cost-efficient method for many workloads while streaming ETL helps organizations achieve real-time insights for time-sensitive decision-making.

While traditional ETL predates modern cloud infrastructure, ETL today is commonly used to extract and transform data from various sources and load it into cloud-based repositories, including cloud data warehouses. Cloud services providers often offer ETL capabilities, including cloud-based staging areas. The scalability and compute power of cloud platforms can enhance the performance of ETL processes, resulting in faster processing and insights.

A staging area is a temporary storage location between data sources and a target destination that is used to temporarily store raw data. It acts as a loading dock where raw data is cleaned, inspected and transformed before moving to a target destination.

Initial, incremental and full loads describe different ways data is loaded during ETL. Initial load is the first-time extraction and loading of data from a data source into a target destination. It includes the entire data set and is done once, at the beginning of a project.

Incremental load is the process of loading only data that is new, changed or deleted since the last load. And full load is the process of transferring all the data in a dataset to a target system, often to replace existing records in that system.