ETL vs ELT

What it is

ETL (Extract-Transform-Load) is a data integration pattern in which data is first extracted from source systems, transformed into the target schema and format in a dedicated processing engine (outside the destination), and then loaded into the destination data store. ETL was the dominant pattern when destination systems (Oracle data warehouses, Teradata) had expensive storage and limited compute — it was cheaper to do heavy transformation work externally and load only clean, refined data.

ELT (Extract-Load-Transform) reverses the order: raw data is extracted from sources and loaded directly into the destination (typically a cloud data warehouse or data lake with abundant cheap storage), and transformations are performed inside the destination using its native SQL engine or compute cluster. Tools like dbt (data build tool) operationalize ELT by defining transformation logic as SQL models that run inside Snowflake, BigQuery, or Redshift. The shift to ELT was enabled by massively parallel, scalable cloud warehouses that make in-warehouse transformation fast and cost-effective.

Why it exists

ETL emerged in the 1990s when data warehouses were expensive on-premise systems with limited storage. Pre-transforming data before loading reduced storage costs and query complexity. An ETL pipeline used dedicated transformation servers (IBM DataStage, Informatica PowerCenter, SSIS) that moved, joined, aggregated, and cleansed data before staging it in the warehouse. This model required significant upfront schema design (star/snowflake schema) because the shape of the data was fixed at load time.

ELT emerged with cloud data platforms that decouple storage from compute and scale both independently. When storage is near-free (S3, GCS) and compute scales elastically (Snowflake virtual warehouses, BigQuery slots), the cost rationale for external transformation disappears. Loading raw data first preserves optionality: you can always re-run transformations as requirements change, without re-extracting from sources. Reverse ETL — pushing transformed warehouse data back to operational systems (CRMs, marketing tools) — is a further evolution that makes the warehouse a hub rather than a terminus.

When to use

• Use ETL when: the destination database has limited storage or compute (on-premise warehouse, managed RDBMS) and raw data volume would be prohibitively large or expensive.
• Use ETL when: transformation requires logic that SQL cannot express efficiently — ML feature engineering, complex deduplication algorithms, or integration with external APIs during transformation.
• Use ETL when: the source data contains sensitive PII that must be masked, encrypted, or filtered before ever reaching the destination for compliance reasons.
• Use ELT when: you have a cloud data warehouse (Snowflake, BigQuery, Redshift, Databricks) with abundant SQL compute available.
• Use ELT when: data science and analytics teams need access to raw, unfiltered source data for exploration and ad-hoc analysis alongside curated models.
• Use ELT with dbt when: you want transformation logic version-controlled in SQL, reviewed in pull requests, and tested with data quality assertions.

When not to use

• Don't use ETL with a purpose-built transformation tool when your destination is a cloud warehouse and dbt + native SQL can handle the transformation — the complexity isn't justified.
• Don't load completely raw, unparsed data into ELT pipelines without at least structural validation — loading corrupt data into the warehouse then discovering it in dbt models delays detection and remediation.
• Don't use ETL pipelines for streaming/real-time scenarios where batch-oriented ETL tools introduce unacceptable latency — use stream processing (Kafka Streams, Flink) instead.
• Don't build monolithic ETL jobs that combine extraction, complex transformation, and loading in a single opaque script — testability and observability suffer significantly.

Typical architecture

  ETL Pattern:
  ┌──────────┐   Extract   ┌──────────────┐  Transform  ┌──────────────┐  Load  ┌──────────┐
  │  Source  │────────────▶│  Staging     │────────────▶│  Transform   │───────▶│  Target  │
  │  Systems │             │  Area /      │             │  Engine      │        │  DWH     │
  │  (DB,API)│             │  Temp Files  │             │  (Spark,     │        │          │
  └──────────┘             └──────────────┘             │  Informatica)│        └──────────┘
                                                        └──────────────┘

  ELT Pattern (modern cloud):
  ┌──────────┐   Extract   ┌──────────────────────────────────────────────────┐
  │  Source  │────────────▶│           Cloud Data Warehouse / Lake            │
  │  Systems │    Load     │  ┌─────────┐  ┌──────────────┐  ┌────────────┐  │
  │  (Fivetran│            │  │  Raw    │  │  Staging     │  │  Marts /   │  │
  │  Airbyte)│             │  │  Layer  │─▶│  Models (dbt)│─▶│  Reporting │  │
  └──────────┘             │  └─────────┘  └──────────────┘  └────────────┘  │
                           │              Transform happens inside DWH via SQL │
                           └──────────────────────────────────────────────────┘
          

Pros and cons

Implementation notes

For modern ELT pipelines, use Fivetran or Airbyte for the Extract and Load phase — these managed connectors handle incremental extraction, schema drift, and retry logic for hundreds of sources. Let the EL tool load raw data into a dedicated raw schema in your warehouse. Then use dbt to build transformation layers: staging models that rename columns and cast types; intermediate models for joins and business logic; and mart models for reporting-ready aggregates. Apply dbt tests (not_null, unique, accepted_values, relationships) to catch data quality issues at each layer.

For ETL with Spark, structure pipelines as distinct, restartable stages: extraction to a landing zone in S3/GCS, transformation in Spark (running on EMR, Dataproc, or Databricks), and load via JDBC or warehouse-native bulk load (COPY INTO for Snowflake, bq load for BigQuery). Use checkpoint directories in Spark Structured Streaming for stateful operations. Implement idempotent loads using MERGE statements rather than INSERT so that reruns don't duplicate data. Use Apache Airflow or Dagster to orchestrate multi-step pipelines with dependency management, SLA monitoring, and retry policies.

Common failure modes

• Schema drift breaks EL jobs: Source system adds or renames a column; the connector fails or silently loads null values into the raw table; downstream dbt models produce incorrect aggregates.
• Runaway warehouse costs: A poorly written dbt model full-scans a multi-terabyte raw table on every run; cost spikes go unnoticed until the monthly cloud bill arrives.
• No data quality gates in ETL: Transformation pipeline loads invalid records (negative prices, future birthdates) into the warehouse; downstream reports show incorrect figures for weeks.
• Big-bang load without incremental: ETL job re-loads the entire source table on every run instead of incrementally; performance degrades as source data grows, eventually timing out.
• Reverse ETL double-write: Operational data pushed from the warehouse back to a CRM via reverse ETL overwrites changes that users made directly in the CRM, causing data loss and confusion.

Decision checklist

• Is your destination a cloud data warehouse that supports scalable SQL transformation (Snowflake, BigQuery, Redshift)?
• Do you need raw data preserved for re-transformation and exploration, or only cleaned, shaped data?
• Does transformation logic require capabilities beyond SQL (ML, external API calls, complex algorithms)?
• Are there PII or data residency constraints that prohibit loading raw data into the destination?
• Do you have data analysts who can own SQL transformation logic using dbt?
• Have you planned incremental loading strategies so pipelines don't re-process entire datasets on each run?

Example use cases

• SaaS analytics platform (ELT): A B2B SaaS company uses Fivetran to load Salesforce, HubSpot, and Postgres data into Snowflake, then runs dbt models to build a unified customer 360 view for finance and sales operations dashboards.
• Healthcare data integration (ETL): A hospital network uses an Informatica ETL pipeline to extract HL7 messages from EMR systems, de-identify patient records (masking SSN, DOB), and load compliant aggregate data into an Oracle data warehouse for population health analytics.
• Reverse ETL for personalization: An e-commerce platform uses Census or Hightouch to sync customer lifetime value scores calculated in BigQuery back to Salesforce for sales rep prioritization and to Braze for personalized email segmentation.

Related patterns

• Change Data Capture — CDC provides low-latency incremental extraction for ELT pipelines, replacing batch polling.
• Batch Integration — ETL is a form of batch integration; understand the tradeoffs with streaming approaches.
• Data Warehouse — The destination for most ETL and ELT pipelines.
• Data Lakehouse — Modern architecture that often uses ELT with Delta Lake or Iceberg as the transformation layer.

Further reading

• dbt Documentation — The standard ELT transformation tool
• What is ELT? — Fivetran
• Spark Structured Streaming Guide — for ETL with Spark