ETL vs ELT Data Pipelines

ETL and ELT are data pipeline architectures that move data from source systems to target destinations (typically data warehouses or data lakes). The letters stand for Extract, Transform, and Load. The order of these operations defines the architecture:

In ETL, data is transformed in an intermediate staging area before being loaded into the target. In ELT, raw data is loaded directly into the target, and transformations are performed inside the target system.

The choice between ETL and ELT depends on factors like data volume, latency requirements, target system capabilities, and compliance needs. Historically, ETL was the standard because data warehouses had limited compute power — transforming data before loading reduced the load on the warehouse. Modern cloud data warehouses (e.g., Azure Synapse, Snowflake) and data lakes (e.g., Azure Data Lake Storage) have massive scalability, making ELT increasingly popular.

Extract: Data is read from source systems (databases, APIs, files). This step can use full or incremental extraction. For example, using SQL queries like SELECT * FROM Orders WHERE OrderDate > @LastRun.

Transform: Data is cleaned, filtered, aggregated, joined, and enriched in a staging environment. Transformations include:

Load: The transformed data is written to the target system. In a data warehouse, this typically involves INSERT or UPDATE operations. For dimension tables, you might use slowly changing dimension (SCD) patterns.

Key Characteristics:

Extract: Same as ETL — data is read from sources.

Load: Raw data is loaded directly into the target system, often into staging tables or a raw zone in a data lake. For example, in Azure Synapse, you might use COPY INTO to load data from Azure Blob Storage into a staging table.

Transform: Transformations are executed inside the target system using its compute engine (e.g., SQL queries, Spark jobs). The transformed results are often written to new tables or views.

Key Characteristics:

Azure Data Factory (ADF): A cloud-based ETL/ELT service. It uses pipelines containing activities. A typical ADF pipeline for ETL: 1. Copy activity (Extract) from source to staging 2. Data Flow activity (Transform) — runs on Azure Databricks or Spark 3. Copy activity (Load) to target

For ELT, you can use a Copy activity to load raw data into a staging table in Azure Synapse, then a stored procedure activity to run transformations.

Azure Synapse Analytics: Supports ELT natively with T-SQL. The COPY INTO command loads data from external sources into staging tables. Then you run CTAS (CREATE TABLE AS SELECT) or INSERT INTO SELECT to transform and load final tables.

PolyBase: In Azure Synapse, PolyBase enables loading data from Azure Blob Storage or Data Lake Store using external tables. It supports ELT patterns.

Azure Databricks: Often used for complex transformations in ELT pipelines. Data is loaded into Delta Lake tables (raw), then transformed using Spark SQL or DataFrame operations.

Default Values:

Azure Data Factory:

Azure Synapse:

COPY INTO dbo.Orders
FROM 'https://mystorage.blob.core.windows.net/data/orders.csv'
WITH (
    FILE_TYPE = 'CSV',
    FIRSTROW = 2
)

CREATE TABLE dbo.OrderSummary
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
FROM dbo.Orders
GROUP BY CustomerID;

Azure Databricks:

df = spark.read.format("csv").option("header", "true").load("path/to/raw")
df.write.format("delta").save("path/to/raw_table")

df_transformed = spark.sql("SELECT CustomerID, SUM(Amount) FROM raw_table GROUP BY CustomerID")
df_transformed.write.format("delta").save("path/to/summary")

A large retail chain uses ETL to consolidate sales data from thousands of stores into a central data warehouse for reporting. Each store sends daily CSV files to an Azure Blob Storage container. An ADF pipeline runs nightly: it extracts the CSV files, transforms them by cleaning currency formats, converting time zones, and aggregating sales by product and store, then loads the aggregated data into Azure Synapse. The staging environment is a set of Azure Databricks notebooks that run on a 20-node cluster. The transformation includes joining with product master data from an on-premises SQL Server (via Self-Hosted IR). The pipeline processes about 500 GB of raw data daily. A common problem is schema drift — if a store adds a new column, the transformation breaks. The solution is to use ADF's schema drift handling in Data Flows, which allows mapping columns dynamically. Misconfiguration of the staging cluster (e.g., too few nodes) causes timeouts and partial loads. The team sets up alerts for pipeline failures and uses data quality checks to reject malformed files.

A manufacturing company collects sensor data from factory equipment into Azure Event Hubs. Data is streamed into Azure Data Lake Storage Gen2 in raw JSON format. An ELT pipeline runs hourly: first, raw data is loaded into external tables in Azure Synapse using PolyBase. Then, T-SQL stored procedures transform the data — parsing JSON, calculating averages, and joining with equipment metadata. The target is a set of tables for dashboards in Power BI. The advantage of ELT is that raw data is always available for ad-hoc analysis (e.g., investigating a specific sensor failure). The challenge is storage costs: raw data grows at 2 TB per month, and transformed tables add another 500 GB. The team uses partitioning and file format optimization (Parquet with snappy compression) to reduce costs. Performance issues arise when too many concurrent transformation queries run — they scale Synapse DWUs dynamically using Azure Automation. A misconfiguration (e.g., forgetting to partition large tables) leads to full table scans and slow dashboards.

A financial services firm uses a hybrid approach: ETL for sensitive data (customer PII) and ELT for non-sensitive operational data. ADF orchestrates both. For PII data, transformations (masking, encryption) happen in a secure staging environment using Azure Databricks with managed identity access to Azure Key Vault. For operational logs, raw data is loaded into a separate zone in ADLS and transformed later using Azure Synapse. The pipeline runs every 15 minutes for near-real-time analytics. The main challenge is managing two different pipelines with different error handling and monitoring. The team uses ADF's metadata-driven approach to parameterize pipelines. A common mistake is using the same staging area for both ETL and ELT, causing data leakage — they keep strict separation using different containers and RBAC roles.