ETL: Extract, Transform, Load
ETL (Extract, Transform, Load) is the historical paradigm for data pipelines. Emerging in the 1970s-1980s alongside the first on-premise data warehouses (Teradata, Oracle), it follows a simple logic: extract data from sources, transform it in an intermediate staging area, then load the clean result into the destination warehouse.
Transformation is at the heart of the process: cleansing, aggregation, joins, applying business rules, format conversion. Everything happens before the data lands in the Data Warehouse. The warehouse only receives clean, structured, ready-to-use data.
Traditional ETL architecture and tools
In a classic ETL architecture, a dedicated transformation server (often called an ETL server or staging area) handles all the work. Legacy tools like Informatica PowerCenter, IBM DataStage, Talend, and Microsoft SSIS encapsulate this logic in graphical interfaces with pre-built connectors and drag-and-drop transformation flows.
This approach requires significant upfront design effort: every transformation must be specified before data enters production. Readability is a genuine advantage — you know exactly what enters the warehouse — but rigidity becomes a real obstacle when analytical needs evolve rapidly. Schema changes in sources require manual updates across the entire pipeline.
When ETL still makes sense in 2026
ETL is not dead. Three scenarios still justify it. First, sensitive data masking: GDPR, HIPAA, and financial regulations often require that PII (Personally Identifiable Information) be pseudonymized or redacted before it ever lands in any storage layer — ETL's pre-load transformation is structurally suited to this. Second, legacy system integration: mainframe-based ERP systems often output fixed-width flat files or EBCDIC-encoded data that must be transformed before any modern system can consume it. Third, micro-batch streaming: tools like Apache NiFi or Informatica CDI handle near-real-time ETL with sub-minute latency for operational use cases where the warehouse is not the right destination.
The key insight is that ETL and ELT are not mutually exclusive. Many mature organizations run ETL for compliance-sensitive flows and ELT for analytical workloads — a hybrid approach that gets the best of both paradigms.
ETL was designed when storage was expensive and compute on the warehouse was limited and costly. Loading raw, untransformed data would have been wasteful — hence the systematic upstream transformation. In 1990, storing 1 GB of data cost around $10,000. In 2025, the same GB on S3 or GCS costs less than $0.02. This 500,000x cost reduction is the primary driver behind the shift to ELT.
ELT: Extract, Load, Transform
ELT (Extract, Load, Transform) reverses the order: extract data, load it directly into the warehouse or data lake (raw, untransformed), then transform it in-place using SQL or frameworks like dbt. This approach was born with the cloud and modern columnar data warehouses.
The fundamental shift is that the data warehouse itself becomes the transformation engine. BigQuery (Google), Snowflake, Amazon Redshift, and Databricks offer elastic compute power and near-free object storage. Transforming terabytes of data in SQL directly inside Snowflake is today faster and cheaper than doing it on an intermediate ETL server.
The structural advantages of ELT
Preserving raw data is the most underrated advantage of ELT. By loading source data without transformation, you create an immutable historical layer. If business rules change in six months, you can replay transformations without re-extracting sources. If a new analytical question arises that requires a field you discarded in an ETL pipeline, in ELT you simply write a new transformation against the raw data.
Flexibility is equally significant: Data Scientists and Analysts can access raw data for their own exploratory analyses without waiting for a central ETL pipeline to be reconfigured. This fundamentally changes the dynamic between data teams — from 'request and wait' to 'self-serve.'
The medallion architecture: Bronze, Silver, Gold
The medallion architecture (popularized by Databricks) organizes ELT into three layers. Bronze (raw): data is ingested as-is from sources, with no transformation. Schema-on-read, append-only. This is the immutable record of everything that happened. Silver (cleansed): Bronze data is cleaned, deduplicated, type-cast, and lightly enriched. Business keys are resolved, bad records are filtered. This layer is trusted and reusable across teams.
Gold (business-ready): aggregated, modeled, and curated datasets optimized for specific analytical use cases — dashboards, ML features, financial reports. Each Gold table typically serves one consumer or team. The medallion model maps directly to dbt layer conventions: staging models (Bronze → Silver), intermediate models, and mart models (Gold). It provides a clear contract between data producers and consumers at each layer.
Adoption of ELT-first architectures has clearly accelerated as cloud warehouses (Snowflake, BigQuery, Redshift) made transforming large data volumes far more affordable than with traditional ETL tools. The exact scale of this shift varies widely by industry and company size — precise adoption figures circulating in some analyst reports should be treated with caution and verified against the original source before being cited.
The 5 fundamental differences between ETL and ELT
Beyond the order of operations, ETL and ELT differ across five dimensions that directly impact your architecture choices, tooling decisions, and team organization.
1. Location of transformation
In an ETL pipeline, transformation occurs in a system external to the data warehouse — a dedicated server, Spark cluster, or virtual machine. The warehouse sees only the final result. In an ELT pipeline, transformation happens directly inside the data warehouse or data lake using SQL or an integrated compute engine. The warehouse is both storage and compute.
2. Raw data preservation
ETL does not preserve raw data in the warehouse — only transformed data is stored. If requirements change, you must re-extract from source. ELT first loads raw data into a Bronze layer, enabling you to replay transformations from scratch and answer analytical questions that were not anticipated at design time. This 'time travel' capability (available in Delta Lake and Iceberg) takes raw data preservation even further.
3. Speed to production
ETL requires specifying transformations before loading, which lengthens the development cycle. Schema changes in sources require manual updates to the transformation logic. With ELT and tools like dbt, a Data Analyst can write a new transformation in SQL and deploy it in hours rather than days, with built-in testing and documentation.
4. Cost structure
Traditional ETL solutions carry high licensing costs (Informatica, MicroStrategy) and require dedicated staging servers with fixed capacity. ELT leverages the elastic compute power of the cloud data warehouse — costs are directly tied to usage and decrease with columnar compression and object storage pricing. Snowflake's credit model and BigQuery's on-demand pricing make ELT cost-proportional to actual analytical work.
5. Privacy, security and compliance
ETL has a structural advantage for regulated data: sensitive fields can be masked, tokenized, or pseudonymized before they ever enter the warehouse. With ELT, raw data is loaded first in full — you must ensure the Bronze layer has strict RBAC controls, that column-level masking policies are applied (Snowflake Dynamic Data Masking, BigQuery column-level security), and that only Silver/Gold views are exposed to end users. For GDPR right-to-erasure requests, ELT architectures need a deletion propagation strategy across all layers.
CDC and streaming: beyond batch ingestion
Most ELT pipelines run in batch mode — hourly, daily, or weekly full or incremental loads. But many use cases require near-real-time data: fraud detection, live dashboards, inventory management, customer-facing recommendations. Change Data Capture (CDC) and streaming architectures address this gap.
Change Data Capture (CDC): capturing changes as they happen
CDC captures every INSERT, UPDATE, and DELETE from a source database as it happens, rather than periodically querying for changed rows. The most reliable CDC mechanism reads from the database write-ahead log (WAL) or transaction log — the same log the database uses for replication. This makes CDC non-intrusive (no polling queries that slow down the source) and complete (no changes are missed between polling intervals).
Debezium is the leading open-source CDC connector. It connects to PostgreSQL (WAL), MySQL (binlog), MongoDB (oplog), Oracle (LogMiner), SQL Server (CDC tables) and publishes every change as a structured event to a Kafka topic. The event includes the before and after state of the row, the operation type, and a timestamp — making it trivial to reconstruct the full history of any record.
Streaming ELT: Kafka, Kafka Connect and the lakehouse
A modern streaming ELT pipeline typically follows this pattern: source database → Debezium (CDC) → Kafka topic (raw events) → Kafka Connect sink connector → Bronze layer in Delta Lake or Iceberg → dbt Streaming or batch transformations → Silver/Gold layers. The Kafka layer acts as a durable buffer that decouples producers from consumers and enables replay.
Streaming Lakehouse (Databricks terminology) extends this to allow streaming queries directly on Delta Lake tables via Spark Structured Streaming. This gives you exactly-once semantics, schema enforcement, and time travel on streaming data — eliminating the traditional separation between a batch data warehouse and a real-time streaming system. Apache Flink is an alternative to Spark for stateful stream processing, offering lower latency for complex event processing.
Incremental loading strategies: watermark, CDC, and full refresh
Three strategies for loading new data. Full refresh: truncate and reload the entire table each run. Simple, reliable, but expensive on large tables. Watermark-based incremental: query rows where updated_at > last_run_timestamp. Fast and simple, but fails silently if the source does not have a reliable updated_at column (hard deletes are invisible). CDC-based incremental: capture every operation from the source log — the only approach that correctly handles hard deletes and schema changes without polling.
dbt incremental models support all three strategies via the incremental_strategy parameter (append, merge, delete+insert, insert_overwrite). The merge strategy on Snowflake or BigQuery replaces changed rows based on a unique key — functionally equivalent to CDC semantics at the transformation layer, without requiring a Kafka infrastructure.
The most common bug in incremental pipelines: relying on updated_at for change detection when the source application does not reliably update that column. Hard deletes (DELETE FROM orders WHERE id = 123) are completely invisible to watermark-based loading — the row disappears from the source but stays in your warehouse indefinitely. Always audit whether your source reliably maintains updated_at before relying on it for incremental loads. If not, CDC is the only correct approach.
Key tools of the modern data pipeline
The ELT ecosystem has structured itself around four categories of tools covering the entire pipeline: ingestion, orchestration, transformation, and observability.
Ingestion: Fivetran, Airbyte, Stitch
These tools handle extraction and loading (the EL in ELT, without the T). Fivetran is the market leader with pre-built, fully managed connectors for hundreds of sources (Salesforce, HubSpot, SQL databases, REST APIs, event platforms). Its value proposition: zero maintenance, automatic schema change handling, and SLA guarantees on data freshness. Airbyte is the open-source alternative with a growing connector catalog and the ability to deploy self-hosted. Both handle incremental replication, schema drift detection, and connection error retries automatically.
Transformation: dbt — the de facto ELT standard
dbt has become the standard for the ELT transformation layer. It lets you write transformations in pure SQL (or Jinja-templated SQL), document every model with YAML descriptions, test them (not_null, unique, accepted_values, referential integrity), and version everything in Git. dbt turns the data warehouse into an execution engine and produces automatic lineage for all transformations.
dbt's model layering maps directly to the medallion architecture: staging models (Bronze → Silver), intermediate models, and mart models (Gold). dbt Core is open-source and runs locally or in CI/CD. dbt Cloud adds a managed scheduler, a visual IDE, and team collaboration features. dbt contracts (v1.5+) allow declaring schema constraints that fail the build if violated — bringing software-style contracts to data pipelines.
Orchestration: Airflow, Prefect, Dagster
Orchestration coordinates pipeline execution in the right order at the right time with error handling and retry logic. Apache Airflow (created by Airbnb in 2014) is the open-source standard with DAGs (Directed Acyclic Graphs) defined in Python. Managed versions on GCP (Cloud Composer), AWS (MWAA), and Astronomer reduce operational overhead. Prefect and Dagster are more modern alternatives: better observability out of the box, native data dependency management (Dagster's asset-based model), and faster local development cycles. The trend in 2025 is toward asset-based orchestration (Dagster, Hamilton) over task-based orchestration (Airflow).
Distributed processing: Apache Spark
For transformations on very large volumes (terabytes to petabytes) or complex processing that exceeds native SQL capabilities (ML feature engineering, graph processing, NLP), Spark remains the standard. It integrates into ELT architectures as a heavy transformation layer on top of the data lake. PySpark is the most widely used Python API. Spark on Databricks (Delta Live Tables) or EMR (AWS) is the most common production configuration. Delta Live Tables (DLT) provides declarative pipeline definitions with built-in quality expectations — think dbt but for Spark-scale workloads.
A data pipeline frequently seen in production combines: Fivetran or Airbyte (ingestion) + Snowflake or BigQuery (storage and compute) + dbt Core (transformation) + Airflow or dbt Cloud (orchestration) + Monte Carlo or Elementary (data observability). This combination covers a large share of common analytical needs and comes up regularly in Data Engineering interviews, though the right tools always depend on each organization's context and data volume.
How to choose between ETL, ELT, and hybrid
The ETL vs ELT decision does not happen in the abstract — it depends on your existing infrastructure, data volume, regulatory requirements, team skills, and latency requirements.
When to prefer ETL
ETL remains the right choice in specific situations: on-premise infrastructure without a cloud data warehouse (ETL tools are optimized for this environment), strict regulatory requirements on personal data where masking must happen before loading (HIPAA, financial data subject to PCI-DSS, biometric data), integration with legacy mainframe systems that output formats requiring specialized parsing, and teams with deep expertise in classical ETL tools without capacity for short-term migration.
A practical signal: if your compliance team requires you to prove that PII never enters the warehouse in raw form, ETL gives you a cleaner audit trail. In ELT, you need to demonstrate that Bronze-layer access controls and masking policies achieve the same guarantee.
When to prefer ELT
ELT is the right choice for most greenfield projects in 2026: fully cloud stack (Snowflake, BigQuery, Databricks), need for analytical flexibility and self-serve access for Data Scientists, team proficient in SQL and dbt, significant volumes that benefit from the elastic compute power of the cloud warehouse, and desire to maintain raw data history for replay and schema evolution. If your team's primary skill is SQL and you want transformations in version control with automated testing, ELT with dbt is the natural fit.
The hybrid ETL+ELT approach: the practical reality
In practice, most organizations with complex needs combine both. An ETL pipeline handles sensitive data flows (anonymization and tokenization before loading), while the rest of the analytical workload adopts ELT. CDC streaming handles real-time ingestion for operational use cases, while batch ELT handles historical and analytical workloads. dbt incremental models bridge batch and near-real-time by merging CDC events into the Silver layer.
A decision framework: start with ELT by default. Add ETL for compliance-sensitive sources. Add CDC/streaming only when batch latency (hourly or daily) is genuinely insufficient for the business use case. Avoid premature streaming complexity — it multiplies operational overhead for marginal latency gains in most analytical contexts.
Consolidating ETL and ELT with spaced repetition
Data engineering concepts are dense and specific. Knowing how to define ETL and ELT in an interview is one thing; understanding the architectural implications — medallion layers, CDC patterns, incremental loading strategies, and tool trade-offs — is another. Spaced repetition (FSRS algorithm) is ideal for anchoring these distinctions in long-term memory.
Memia's 'ETL/ELT Pipelines and Orchestration' deck covers definitions, tools, architectural patterns, and common interview questions. Each card is formulated to test understanding rather than simple memorization.
The most frequently tested concepts in Data Engineer interviews: (1) ETL vs ELT — 5 differences with examples. (2) Medallion architecture — what goes in each layer and why. (3) CDC vs watermark incremental — when each is appropriate and what CDC misses. (4) dbt — what it does and does not do (it is not an ingestion tool). (5) Airflow vs Prefect vs Dagster — when to choose each. (6) Fivetran vs Airbyte — managed vs open-source trade-offs.
Explore the Data & AI cluster
Frequently asked questions about ETL and ELT
What is the main difference between ETL and ELT?
The difference is the timing and location of transformation. In ETL, data is transformed before being loaded into the warehouse (in an intermediate staging system). In ELT, raw data is loaded first into the warehouse, then transformed in-place using SQL or dbt. ETL keeps only transformed data; ELT preserves raw data for replay and re-transformation.
Why has ELT largely replaced ETL in modern architectures?
ELT prevailed thanks to the cloud: data warehouses like BigQuery, Snowflake, and Redshift offer elastic compute power and cheap columnar storage. Transforming in-place in the warehouse became faster and cheaper than maintaining an intermediate ETL server. The cost of storing raw data dropped by 500,000x since 1990, removing the original economic justification for upstream transformation.
What is the medallion architecture?
The medallion architecture organizes ELT into three layers: Bronze (raw, as-is from sources, immutable), Silver (cleansed, deduplicated, type-cast — trusted and reusable), and Gold (business-ready aggregations for specific use cases — dashboards, ML features, reports). It maps directly to dbt layer conventions: staging models, intermediate models, and mart models.
What is CDC and when should you use it instead of watermark-based incremental loading?
Change Data Capture (CDC) reads the database transaction log (WAL, binlog) to capture every INSERT, UPDATE, and DELETE as it happens. Use CDC when: (1) hard deletes must be propagated downstream, (2) the source table has no reliable updated_at column, (3) you need sub-minute latency. Watermark-based incremental (querying where updated_at > last_run) is simpler but silently misses hard deletes. Debezium + Kafka is the standard CDC stack.
Is dbt an ETL or ELT tool?
dbt is a pure ELT tool: it handles only the Transform (T) layer and executes it directly in your data warehouse using SQL. It does not handle extraction or loading — those steps are managed by dedicated ingestion tools like Fivetran or Airbyte. dbt's value is bringing software engineering practices (version control, testing, documentation, lineage) to SQL transformations.
Can Airflow orchestrate both ETL and ELT pipelines?
Yes. Apache Airflow is a generic workflow orchestrator that can execute any type of task: REST API calls to ETL tools, Spark jobs, SQL queries, dbt commands, Python scripts. It coordinates execution order and timing without imposing a paradigm. Modern alternatives like Prefect and Dagster offer better data dependency modeling (asset-based) and easier local development.
Is ETL more secure than ELT for sensitive data?
ETL has a structural compliance advantage: data can be masked, tokenized, or pseudonymized before it ever enters the warehouse — easier to audit for regulators. With ELT, raw data is loaded in full and you must apply masking at the storage layer (Snowflake Dynamic Data Masking, BigQuery column-level security) and ensure strict RBAC on the Bronze layer. Both approaches can achieve the same security posture; ETL simply makes the audit trail cleaner.
What is the 'staging area' in an ETL pipeline?
The staging area is a temporary intermediate storage space where data sits between extraction and transformation. It buffers incoming data, enables error recovery without re-extraction, and allows parallel transformation workloads. In ELT, the Bronze layer plays a similar role — but it is permanent, immutable, and queryable rather than transient.
Can you do ETL with Spark?
Yes. Spark is commonly used in ETL architectures for complex transformations on large volumes that exceed native SQL capabilities. Spark reads from multiple sources, transforms data via DataFrames or Spark SQL, and writes to a data warehouse or data lake. On Databricks, Delta Live Tables (DLT) provides declarative ETL/ELT pipelines with quality expectations — combining Spark's power with dbt-style contracts.
What is dbt incremental and how does it relate to ELT?
dbt incremental models run transformations only on new or changed rows rather than rebuilding the entire table each run. The incremental_strategy parameter controls the approach: append (new rows only), merge (upsert based on a unique key — handles updates and deletes), delete+insert, and insert_overwrite. The merge strategy on Snowflake or BigQuery approximates CDC semantics at the transformation layer without requiring a Kafka pipeline.