Skip to content
Knowledge Hub/Article
Article

What Are ETL and ELT? Data Warehouse vs Data Lake

We explain how ETL and ELT work, how a data warehouse differs from a data lake, the lakehouse, CDC, data quality and the handling of personal data.

Oğuzhan Gerçek··9 min read
What Are ETL and ELT? Data Warehouse vs Data Lake

Short answer: ETL (extract, transform, load) is a data integration process that extracts data from different sources, transforms it according to business rules and loads it into a single target, most often a data warehouse. ELT changes the order: data is loaded into the target in raw form, and the target system's own processing power does the transformation. A data warehouse is a database designed for analysis and reporting that holds historical data and applies the schema when data is written. A data lake stores any kind of data in raw form and applies the schema when it is read. The lakehouse approach proposes combining the two on a single low-cost storage layer.

What is ETL?

In Microsoft's definition, ETL is a data integration process that consolidates data from diverse sources into a unified data store. According to AWS, it moves data from the source system to the destination at periodic intervals, in three steps:

  1. Extract: The relevant data is pulled from the source database.
  2. Transform: The data is made fit for analytics: errors are cleaned, duplicates removed, units and date formats converted, and sources joined.
  3. Load: The transformed data is written to the target database.

According to Microsoft, the transformation often runs in staging tables that hold data temporarily, and the three phases can run in parallel to save time.

What is ELT? ETL vs ELT

According to Microsoft, ELT differs from ETL solely in where the transformation takes place: instead of a separate transformation engine, the target data store's own processing capabilities are used. Oracle's data warehousing guide adds that modern data warehouses are moving toward an ELT architecture, in which all or most transformation happens in the database that hosts the warehouse.

According to AWS's comparison, because ELT keeps raw data in the target, the same data can be transformed as many times as needed, and unstructured data such as images or documents can be loaded too. ETL is better suited to structured data and requires more definition up front. Personal data is one side of the trade-off: with ETL, sensitive fields can be encrypted before they reach the target, while with ELT the raw data lands in the target as it is.

What is a data warehouse?

According to Oracle, a data warehouse is a database designed to enable business intelligence: it is built for query and analysis rather than transaction processing, and it usually holds historical data derived from transaction data. Oracle cites the four characteristics set out by William Inmon: subject oriented, integrated, nonvolatile (data does not change once it enters the warehouse) and time variant.

In Oracle's example, a typical operation in an OLTP system, which runs day-to-day transactions, touches only a handful of records, while a typical warehouse query scans thousands or millions of rows. According to Microsoft, analytical (OLAP) systems are optimized for heavy reads and few writes, and they use a star or snowflake schema instead of traditional normalization. In a star schema, tables fall into two groups: fact tables, which store events and observations, and dimension tables, which describe business entities.

What is a data lake?

According to Microsoft, a data lake is a storage repository that holds large volumes of data in its native, raw format; because it scales cost effectively from terabytes to petabytes, it suits massive and diverse datasets. Data is stored untransformed and transformed only when it is needed, an approach known as schema-on-read.

According to the lakehouse paper published at CIDR 2021, cloud data lakes such as S3, ADLS and GCS started replacing HDFS from 2015 onwards, and the data usually sits in open file formats such as Apache Parquet and ORC. We cover that storage layer in our object storage article. The risk is clear too: according to Microsoft, without proper cataloging, lakes can devolve into data swamps in which valuable information is present but inaccessible or misunderstood.

Data warehouse vs data lake

AWS's comparison lists the differences:

  • Data: All data in a warehouse is structured or pre-modeled into tables; a lake can hold any data, whether structured, semi-structured or unstructured.
  • Schema: In a warehouse, the schema is designed before data is saved; in a lake, it is written at the time of analysis.
  • Data quality: Warehouse data is highly curated and serves as the central version of the truth; lake data may be raw.
  • Users: Lakes add data engineers and data architects to the warehouse's users.
  • Performance and cost: A warehouse is designed for the fastest query performance; a lake prioritizes volume and cost and is cheaper for large numbers of images and videos.
  • Use cases: Warehouses serve batch reporting, BI and visualization; lakes serve machine learning, exploratory analytics, streaming and big data.

According to the lakehouse paper, the dominant pattern in the industry uses both: data is first ETLed into lakes and then ELTed again into warehouses.

What is a lakehouse?

The CIDR 2021 paper, written by researchers from Databricks, UC Berkeley and Stanford, argues that the data warehouse architecture as we know it will be replaced by the lakehouse. In the authors' view, the two-tier setup creates complexity, delays and new failure modes, and warehouse data is stale compared with the lake, with new data frequently taking days to load. The paper defines a lakehouse as a data management system based on low-cost, directly accessible storage that also provides analytical DBMS features such as ACID transactions, data versioning, auditing, indexing, caching and query optimization.

Batch and streaming: CDC and Kafka

Classic ETL runs in batches; as Oracle puts it, a warehouse is updated by an ETL process run nightly or weekly. According to Google Cloud, streaming ETL pipelines that handle continuous streams of data in real time are now emerging alongside batch pipelines.

One way to capture changes at the source is CDC (change data capture). According to Microsoft, CDC in SQL Server reads insert, update and delete changes from the transaction log and makes them available in an easily consumed relational format; in the documentation's example, an ETL application loads those changes incrementally into a data warehouse. Apache Kafka is an event streaming platform that publishes, durably stores and processes streams of events, and a PostgreSQL connector in Kafka Connect can capture every change to a set of tables.

Data quality, validation and personal data

Data quality belongs in the transformation step: Microsoft lists cleaning, deduplicating and validating data among transformation operations. Rules can also be written as tests: dbt ships with four generic tests (unique, not_null, accepted_values, relationships); each test selects the rows that break the rule and passes if it returns zero rows. In migration projects, AWS DMS can compare each source row with its target counterpart and report mismatches, at the cost of extra resources and time. We handle data migration projects in our migration & integration engineering service.

For personal data, SQL Server's dynamic data masking shows masked values to nonprivileged users without changing the data, but according to Microsoft, a user with ad hoc query permissions can still get at the actual values. NIST SP 800-188 (September 2023) also notes that not all tools that merely mask personal information are sufficient for de-identification. We cover how the KVKK (Türkiye's personal data protection law) defines anonymization in our KVKK article, and data preparation for AI projects in our data infrastructure article.

ETL tools

The tools below are examples only:

  • Apache Airflow: According to its documentation, an open-source platform for orchestrating batch workflows; workflows are defined entirely in Python.
  • dbt: Transforms raw warehouse data into models using SQL select statements; the transformation runs directly in the cloud data platform.
  • Azure Data Factory: According to Microsoft, a managed cloud service for hybrid ETL, ELT and data integration projects; its next generation lives in Microsoft Fabric.
  • AWS Glue: According to AWS, a serverless data integration service for discovering, preparing, moving and integrating data from multiple sources.

We cover the databases the data comes from in our SQL and NoSQL articles. We handle connecting source systems in our system integration service, and the warehouse and lake platform in our database platform engineering service.

Frequently asked questions

What does ETL mean? Extract, transform, load: a process that pulls data from sources, transforms it and loads it, most often into a data warehouse.

What is ELT, and how is it different from ETL? In ELT, data is loaded into the target in raw form and transformed inside the target system. In ETL, the transformation happens before loading, in a separate engine.

What is a data warehouse? A database designed for analysis and reporting that stores historical data from different sources in a consistent form. The schema is applied when data is written.

What is a data lake? A repository that stores structured and unstructured data in raw form at large scale. The schema is applied when data is read.

What is the difference between a data warehouse and a data lake? A warehouse is built for fast reporting on cleaned, structured data. A lake holds any raw data at low cost and is used for machine learning.

Sources