The relationship of data to today’s modern enterprise is becoming more sophisticated. As data sources continue to increase and data formats continue to evolve, ETL and its place in modern data integration are also changing. What was once a simple three-step process, ETL has become more nuanced and is often even replaced by a new data integration strategy, ELT.
So, with so much changing regarding the role of ETL in data integration, it’s worth doing a deep-dive investigation. In the following sections, we’ll look at the role of ETL in today’s modern data integration strategies, how ETL and ELT stack up, and the future of ETL.
What is ETL?
ETL, which stands for extract, transform, and load, is a technique used to pull and clean up data from multiple sources and load that data into a database suited for data analysis, such as a data warehouse.
Before cloud-based data analytics warehouses such as AWS Redshift or Google BigQuery disrupted data integration and data analytics strategies, ETL was the standard strategy to move data from a relational database to a data warehouse. And although cloud-based data analytics warehouses have enabled organizations to move to an ELT model, ETL is still a very widely used strategy and an essential part of data warehousing.
What is Data Integration?
Data integration is the process of taking a variety of disparate data formats from a variety of data sources and transforming that data into a single, unified view. This seemingly simple process is the foundation for which organizations perform business intelligence operations and business analytics, so ensuring robust data integration processes is paramount to almost every other facet of an organization.
Significantly for this conversation, data integration and ETL go hand and hand in today’s modern business environment, as ETL is a crucial component of a larger data integration strategy.
The intention of this analysis is to uncover some of the nuances in today’s ETL strategies and how they fit in a great data integration strategy giving a better understanding of the relationship between ETL and data integration.
The Relationship Between ETL and Data Integration
ETL and its role in data integration has undergone profound changes over the past decade. With the rise of real-time streaming data, and organizations’ dependence on real-time data analytics and monitoring, the traditional approach to ETL simply wasn’t keeping pace with the needs of today’s businesses.
And although ETL has been an inseparable component of data integration and an essential part of data warehousing, it’s evolving.
If we look back to the state of data integration a decade ago, it would look something like this: data resided in a database, files, or data warehouse, and although relational databases were highly transactional, the data integration system was more “static” than it is today.
This was the hay-day for the simple three-step ETL process – “Extract, Transform, and Load”.
In this traditional ETL system, data was moved between source and target a few times a day and dependent on an architecture that required a heavy amount of IT expertise and many developer-hours to write the scripts or software to move the data. In turn, this created a bottleneck as to who could source data. If there was a new request to source data into a new database for a particular business case, it required developers and IT to facilitate the change.
With new technologies such as data lakes and flexible storage schemas coming online, the paradigm for traditional data warehousing shifted. Data lakes provided a fundamentally different approach to data storage. Data lakes primarily store raw unprocessed data and don’t require a well-known, predefined schema.
Building on this, the rise of cloud computing has radically transformed ETL’s place in today’s data integration strategy. Cloud-based data analytics warehouses such as Amazon Redshift, Google BigQuery, and Snowflake and the incredible computing power behind them have changed the way businesses will interact with data warehousing indefinitely.
The Five Steps of ETL Data Processing
Today, the shift from the traditional ETL approach to a model ETL approach has shifted the simple three-step ETL process into five more well-defined steps: Extract, Clean, Transform, Load, and Analyze. The reason for this is that the traditional three-step approach to ETL didn’t account for the transportation of data, the overlap in these three stages, and how new technologies such as ELT and the rise of cloud data warehousing are influencing how ETL operates. Let’s look at the five steps of ETL and how this stacks up against the shift to ELT.
Extract – During data extraction, source data is pulled from a source system and moved to a staging area, making the data available to subsequent stages in the ETL process. Often this process included extracting data from a variety of sources and varying data types with the intention to make the data available for cleaning and transforming. Common sources of data are SQL or NoSQL databases, files, CRMs or ERPS, and other business data sources.
Clean – Once the data has been sourced and moved to a staging area, the data will undergo the cleaning stage, also known as data cleansing or data scrubbing. This stage takes on several forms, depending on the makeup of the data sources; however, typical stages of the cleaning process include filtering, deduplicating, and authenticating the data.
Transform – The transform stage is one of the most critical stages of the ETL process. Here, a series of data processing operations are performed on the data such as data translations or rearchitecting the scheme of how data is delivered to create consistency across all input data. Other common procedures in the transform stage include: sorting, applying validation rules across the entire dataset, converting currencies, concatenating text strings, and other similar procedures.
Load – The load stage is the final ETL stage before analysis accounting for the transformed data being moved from the staging area into the data warehouse. Here, data is loaded into the data warehouse in an automated manner and can receive periodic updates to data in the data warehouse. Once properly loaded, the data is ready for data analysis.
Analyze – Once the data has been extracted, transformed, and ingested into the data warehouse it’s ready for data analysis. Typically Data warehouses will follow an online analytical processing OLAP approach to analysis. This analysis strategy is designed to perform multidimensional analysis on massive datasets providing fast, accurate and efficient analysis.
The Shifting Paradigm of ETL to ELT
With cloud-native data warehousing solutions such as AWS Redshift or Google BigQuery coming online, everything has changed for ETL.
What’s so different about these cloud-native data warehouses? They offer incredible computing power. So much so that they can handle the transformation stage of a data integration strategy removing the need to transform data in a staging zone.
This approach is known as ELT or Extract, Load, Transform. While answering the question of ETL or ETL, deserves its own thorough analysis, there are a few important takeaways. With ELT, organizations can mitigate the costly overhead of transforming data in-house – a process that can require substantial personnel and technology resources. This equates to cost savings while being able to delegate the responsibility of transforming data to the cloud-native data warehouse.
Types of ETL: Batch, Streaming, and Reverse ETL
There are several types of ETL implementations that organizations can leverage to manage their data pipelines. With the rise of modern data warehousing and data analytics, it’s important to understand each of these in some detail to better understand which approach might be best for your data warehousing needs.
Batch ETL – Batch ETL is an ETL strategy where source data that is to be transformed and loaded into a data warehouse is gathered into batches and moved to the transformation stage. Here, batches are moved to the transformation landing zone on a particular schedule or triggered once a certain amount of data fills the batch.
Streaming ETL – Streaming ETL is another common approach to ETL where data is processed as soon as it arrives at the storage layer. In this ETL strategy, stream processing occurs continuously recording events near instantaneously. This approach to ETL detects changes made in real-time making it a great choice for processing website interactions, Internet of Things, edge computing, and real-time payment processing.
Reverse ETL – Reverse ETL is exactly what you might think – sourcing data from a data warehouse and storing it in a different data structure, like a transactional database for instance. The purpose of this ETL approach is to take data from a comprehensive data warehouse – i.e., a source of truth – and move the data to a “system of action” like a CRM or transactional database to use for other business processes.
The reason reverse ETL is of such a high value for today’s organizations is that it uncovers the opportunity for operational analytics. To build on this concept, moving data from a data warehouse to a system of action allows today’s SaaS platforms to operate more efficiently, or provide more comprehensive data for any number of business use cases.
Examples and Use Cases of ETL
Since ETL has been the standard practice for translating data between data sources and targets since the mid 70’s there are, of course, many use cases for ETL. Let’s take a look at three common use cases for ETL today and how it supports the needs of today’s data-centric organizations.
Synchronizing Data from Several Sources – As organizations grow, they’ll often be tasked with synchronizing data from several sources. Take, for example, an organization that operates in several geographic locations. Often, organizations want to keep data consistent between locations or create one location as a single source of truth. Here, ETL can be used as one of the first processes in this data migration strategy where data is sourced, cleaned, migrated, and loaded.
Migrating to the Cloud – In the same vein as synchronizing data between locations, organizations may want to leverage the cloud as a data warehousing target. Now, based on earlier discussions, ELT can be a solution for doing transformation processes within the data warehouse, however, ETL is still a very valid technology in the cloud conversation. Here, organizations will use existing ETL technology to source data, transform and clear the data locally then migrate and load the data in the cloud-native target.
Automation – For organizations looking to bolster their automation efforts, ETL can help here as well. What was once a very manual process with hand-coded data pipelines, ETL and the greater data pipeline architecture can now be automated in a simple and intuitive fashion via smart data pipelines.
The series of steps that move data from one system to another can be orchestrated via an easy-to-use drag and drop platform. Then, once this data pipeline is formalized, it can be left to run on its own in an automated fashion.
ETL and your Data Integration Strategy
If you’re interested in implementing a modernized ETL strategy, an ELT strategy, or improving your existing data integration approach, we can help.
Instead of investing major resources into rearchitecting your organization, consider building a smart data pipeline that orchestrates all of the traditional operations of an ETL offered in an intuitive drag and drop style interface.