Explore the evolution from ETL and ELT to the dynamic EtLT approach, highlighting the shift towards continuous data refinement in modern data operations.
While rapid innovation drives huge opportunities for data teams, many professionals are still fixated on traditional patterns of data warehousing and ETL, even while their organizations are migrating to the cloud and adopting cloud-native data services. But the capabilities of the clouds have eclipsed traditional data architectures, and have upset the roles of data acquisition ("Extract"), logical processing ("Transform"), and populating a schema ("Load").
Central to this transformation are two shifts. First, the unmatched scalability of cloud databases which maintains classic table structures while separating storage and compute. Secondly, the rise of data lakes that catalyzed the transition from ETL to ELT and paved the way for niche paradigms such as Reverse ETL and Zero-ETL. Still, these methods have been overshadowed by EtLT — the predominant approach reshaping today's data landscape.
In this article, we assess:
Understanding this evolution equips data teams with the tools to navigate the ever-shifting data space, amplifying their impact on business value. Let's take a closer look.
The native capabilities of the cloud providers have been joined by third-party services to offload that data into separate less costly systems that are optimized for analysis of that data. These services fall into two broad categories: those designed to serve as data warehouses and those designed like data lakes. Let's investigate these two traditionally contrasting modes of operation.
The heart of a data warehouse lies in its schema, capturing intricate details of business operations. This unchanging schema forms the foundation for all queries and business intelligence. Modern platforms like Redshift, Snowflake, and BigQuery have elevated the data warehouse model. By marrying the tried-and-true practices of traditional warehousing with modern scalability, these platforms eliminate the cumbersome tasks associated with on-prem management while enabling the execution of analysis, reporting, and ad-hoc inquiries.
Emerging in contrast to the structured world of warehousing, data lakes cater to the dynamic and diverse nature of modern internet-based applications. For example, unlike traditional platforms with set schemas, data lakes adapt to frequently changing data structures at points where the data is loaded, accessed, and used.
These fluid conditions require unstructured data environments that natively operate with constantly changing formats, data structures, and data semantics. Services like AWS Glue, Databricks, and Dataproc have powerful data lake capabilities, where code-heavy processes and agile workflows can transform data into many different forms.
This pattern requires new methodical, traceable, and repeatable methods like Directed Acyclic Graphs (DAGs) that give the business confidence in the results.
As discussed, data warehouses are structured repositories designed for predefined schemas that are optimized for analytical querying and reporting of data. The ETL pattern was developed specifically to load data into these rigorous schemas, with the following characteristics:
These requirements are typically met by ETL tools, like Informatica, that include their own transform engines to "do the work" of cleaning, normalizing, and integrating the data as it is loaded into the data warehouse schema. If you encounter a tool that claims to perform ETL but uses the data warehouse itself to do this work, you can consider it to be ELT — the transformation ("T") occurs after the data is loaded ("L").
Read More: What is ETL? (Extract, Transform, Load)
As discussed earlier, data lakes are highly flexible repositories that can store vast volumes of raw data with very little preprocessing. They can accommodate any type of data, from structured to semi-structured to unstructured, and do not need a predefined schema. The ELT pattern emerged as a more suitable approach to process data in data lakes, due to these reasons:
Read More: Learn More About ETL vs. ELT
For many years, data warehouses with ETL and data lakes with ELT have evolved in parallel worlds. But recently, ELT processing has become common in organizations that augment their legacy ETL tools with newer, cheaper ELT processes to populate their data warehouse schemas — laying the foundation for the EtLT methodology.
This change can play out over three phases. Can you tell which is in use at your organization today?
A common approach to harnessing the flexibility and low cost of data lakes is to replace a costly legacy ETL tool with a data lake, and program transformations inside the data lake. However, to reduce the impact on the business, a data warehouse remains in use. This results in a combination of the ETL and ELT patterns:
This combination of patterns is commonplace in many enterprises today. Unfortunately, it has become the source of complexity and inefficiency across data groups and sprawl across infrastructures.
The costs of cloud data warehouses have dropped sufficiently to where the maintenance of a separate data lake makes less economic sense. In addition, some cloud data warehouses like Snowflake are expanding their features to match the diverse and flexible data processing methodologies of data lakes.
As a result, transformation processing inside data warehouses is becoming more accessible, and maintaining a separate data lake is no longer needed. This is leading many enterprises to consolidate their data operations onto the newest generation of cloud-based data warehouses.
Meanwhile, some data lakes like Databricks are adding structures that match the ease of use of data warehouses, reducing the benefits of loading data into a separate data warehouse. In some organizations, these changes are causing a tug-of-war on whether to retire data warehouses altogether.
Recognizing this convergence of data lakes and data warehouses, several vendors are now positioning their data platforms as "lakehouses," all-in-one solutions capable of storing and processing any type of data.
The disintegration of the differences between the two patterns has led to a new balkanization of tools along the value chain instead:
As a result, distinct tooling for ETL and ELT is disappearing, and the concept of loading (the "L" in ETL and ELT) has diminished altogether. Instead, the growing scale of data operations has given rise to a new set of concerns for which the balkanized tools are ill-suited:
These processes can implement techniques familiar to traditional data warehouse ETL, including normalization, validation for nonrepudiation, referential integrity enforcement, and idempotency enforcement, but without the cost of monolithic ETL tools. This approach can be strengthened by the use of Directed Acyclic Graphs (DAG), and monitoring that supports timely developer interventions.
Read More: Reverse ETL to Fuel Future Actions with Data
While ETL has its roots in the rigidity of early data warehouses, ELT was born from the flexibility of data lakes. However, with the immense growth in data generation and the complexities involved in handling it, even ELT is seeing an evolution.
A new approach, EtLT —and even beyond, in its ongoing iterations of EtLTLTLT— is surfacing in the data landscape. At its core, EtLT encapsulates "Extract, transform, Load, Transform," though we've also seen it called "Extract, tweak, Load, Transform." But, this is more than just a quirky sequence of letters. It's indicative of a deeper shift where data, once prepared, isn't static but is continuously refined and republished in a data catalog to respond to diverse and evolving needs. This dynamic and ever-evolving nature of the EtLT approach, with its emphasis on continuous refinement, is a pivotal reason why the concept of the data mesh has gained such traction and popularity.
The EtLT approach stands out as the standard and prevailing demand in today's data space. However, to implement this approach effectively, one needs the right technology in place. As James Densmore highlights in the "Data Pipelines Pocket Reference 2021," data pipelines are integral for a successful EtLT deployment, to the extent that the efficient execution and management of the EtLT model are now synonymous with the concept of data pipelines.
While data pipelines have long been tools in the arsenal of data engineers, they are now re-emerging as the most adaptable and potent data processing architecture.
Enterprises have an opportunity to undergo a metamorphosis. By rethinking traditional ETL as a mainstay of data handling, their data practice can evolve to accommodate data pipelines, and power up their use of both data lakes and data warehouses to tackle the complexities of their modern data landscapes.
Read More: What Is Data Pipeline Automation?