The convergence of ETL and ELT: The future of unified data management

The History of ETL and ELT

At the end of the 20th century, it was relatively - even prohibitively - expensive to buy database processing power. On-premises databases had high licensing costs due to a combination of limited choice, the lack of open-source options, and the absence of cloud alternatives that we can take for granted nowadays.

So, to manage large amounts of data efficiently and cost-effectively, organizations often choose to move data outside their databases and process it externally before eventually loading it back in. This was the era of Extract, Transform, Load (ETL). Data acquisition meant extracting from source systems, transforming en route into a more consumable format in a separate processing environment, and, last of all, loading into the target database for querying and analytics.

With the advent of Cloud Data Platforms (CDPs), the paradigm began to shift. Today’s CDPs are optimized for high performance in both storage and processing and are almost infinitely scalable. This allows for a more efficient model: Extract, Load, Transform (ELT). In the ELT paradigm, raw data is first loaded into the CDP, and transformations are performed directly on the platform. As such, the data never needs to leave the CDP, making the process more efficient and streamlined, and more tightly governed.

Cloud Data Platforms: Cloud Data Warehouses and Data Lakes

Cloud Data Warehouses (CDWs) available today - like Snowflake, BigQuery, and Redshift - have revolutionized the way we handle data. They excel at processing large amounts of data quickly, making it easier to perform complicated tasks internally and entirely within the platform. This has significantly reduced the need for external data processing, and for software tools designed for that purpose. Moreover, each cloud service provider typically also provides its own efficient methods for moving data in and out, tailored to work seamlessly within their own platform.

Data management can also be performed using a data lake architecture, which is especially good at handling constantly changing data structures. Because these platforms are flexible and can handle complex tasks, data lakes are perfect for customizing data right when you need it, making it fit perfectly for each specific analysis. By taking this approach, you only spend resources when you're ready to analyze, which brings more efficiency overall.

The architectural convergence of Cloud Data Warehouses and Data Lakes

The distinction between CDWs and data lakes is becoming ever less well-defined. Data Lakes have evolved by adopting structured characteristics that are generally linked with Data Warehouses. These characteristics include enforcing predefined data structures, supporting transactions that ensure data integrity, and allowing users to run queries using SQL. The result has been cloud data platforms such as Databricks.

At the same time, data warehouses have adopted some aspects of the more flexible approach of Data Lakes. This means they are becoming better at dealing with data that does not have a predefined structure. Such data can now be read and processed in a more versatile manner.

This blending of technologies has given rise to what is now known as the "lakehouse" architecture. Essentially, lakehouses combine the adaptable storage capabilities of data lakes with the organized, structured nature of data warehouses.

One key advantage is that they keep the storage and computing tasks entirely separate from one another.

Additionally, lakehouses carefully manage metadata and track data lineage, which helps to preserve the integrity and clarity of the data. As a result, they provide a cohesive platform that meets a wide variety of data processing requirements.

Modern Data Architecture and new AI workloads

In this unified landscape, the distinction between ETL and ELT has become less relevant.

At the start of every data transformation and integration architecture, raw data - which is often structured or semi-structured - always has to be transformed syntactically (although not semantically) to make it more orderly and comprehensible.

Once this foundational work is done, the data can be continuously modified and integrated by professionals such as SQL developers, data analysts, and engineers specializing in artificial intelligence. Their goal is to further transform and integrate the data (sometimes semantically), to ensure that it not only meets the requirements of the business but also supports the functions of AI applications.

This architecture means it's no longer a problem if the data is always in a state of flux, constantly being refined, repurposed, and republished to adapt to changing needs.

New AI workloads

Most recently, generative AI has introduced three significant new workloads into this unified architecture:

1. Enriched Analytics: Generative AI, such as sentiment analysis, can provide deep insights into user emotions and opinions, enabling more informed decision-making.

2. Business Optimization: Automating repetitive tasks using generative AI can enhance operational efficiency, reduce errors, and lead to streamlined workflows.

3. Interactive Generative AI: Advanced language models empower chatbots to engage in dynamic, context-aware conversations, offering personalized assistance.

Incorporating Generative AI into a Modern Data Platform

New approaches for managing the tasks associated with generative artificial intelligence are becoming available to Cloud Data Platform users. Here are some of the ways this is being accomplished:

• SaaS-like approaches
  • Allowing users to utilize Large Language Models (LLMs) via SQL functions or User-Defined Functions (UDFs) without needing to involve themselves in the direct management of the models
  • Accessing LLMs through external services, where they are provided as part of a Software-as-a-Service (SaaS) offering. This is typically engineered using Application Programming Interfaces (APIs) and Software Development Kits (SDKs).
• PaaS-like approaches
  • Handling LLMs outside the primary system, typically for tasks that require highly specific solutions or custom configurations.
  • Incorporating the management of LLMs directly within the Cloud Data Platform which results in more seamless and unified integration, with the overhead of managing the LLM

Conclusion

As we have seen, advances in technology have meant the traditional "Load" stage in both ETL and ELT has been fading in importance. As a consequence, the categorization of data processing tools as either "ETL" or "ELT" is becoming more redundant.

For buyers and users of data integration platforms, this emphasizes the need for a single, comprehensive, and unified platform. The future of data management lies in a platform that seamlessly supports both ETL and ELT processes and is capable of erasing the decreasing distinctions between CDWs and data lakes.

Ultimately, a single, cohesive platform empowers data architects and engineers to manage new, diverse, dynamic workloads with unparalleled efficiency and flexibility.

Matillion is just such a platform, designed specifically for data teams. It enables them to build and manage data processing—for analytics and AI—quickly, efficiently, and scalably. Sign up for a free trial using your own data in your own cloud infrastructure.