ETL vs ELT: Modern Data Integration

This page describes how to choose between Extract, Transform, and Load (ETL) and Extract, Load, then Transform (ELT) patterns for moving and preparing data in data engineering systems. The decision affects how data flows through processing pipelines, where transformations occur, and how errors are handled during ingestion.

When to Use ETL vs ELT

Choose ETL when data must be transformed before storage. Data moves from source systems into a processing layer, where transformations run, then loads into the target system. Transformations happen in the pipeline or staging area before reaching final storage.

Choose ELT when you can load raw data first and transform it later. Data flows directly from sources to a data warehouse or lake, then transformations execute on the destination platform. This allows for more flexible, scalable processing using compute resources of the target system.

ETL Pipeline Configuration

In an ETL setup, the transformation logic is typically defined in a configuration file or SQL script. For example, when using a command-line tool with a JSON config:

{
  "pipeline": {
    "name": "sales-reporting",
    "source": {
      "type": "database",
      "connection": "postgresql://user:pass@host/db"
    },
    "transform": {
      "script": "SELECT customer_id, SUM(amount) as total FROM sales GROUP BY customer_id"
    },
    "destination": {
      "type": "warehouse",
      "connection": "bigquery://project/dataset"
    }
  }
}

The tool reads this configuration and executes the SQL transformation during the pipeline run. If the transformation fails, it typically returns an error code like 1001 with a message such as:

ERROR: Failed to execute transformation script

This failure mode often happens when the transformation queries reference tables that don't exist in the staging environment, or when SQL syntax is invalid for the processing engine.

ELT Pipeline Configuration

In an ELT setup, raw data is loaded first without transformations. The configuration only defines source and destination systems, not the transformation logic:

pipeline:
  name: "customer-data-lake"
  source:
    type: "s3"
    bucket: "sales-raw-data"
    prefix: "transactions/"
  destination:
    type: "data-lake"
    path: "processed/customer-data/"

The system copies files from S3 to the data lake directly. Transformations are handled later, typically via SQL queries executed in the target warehouse using a compute engine like BigQuery or Redshift.

Transformation Execution Location

In ETL, transformations run in the pipeline's processing layer, often using a custom engine or a scripting language like Python or SQL. The transformation script may be embedded in the configuration file or stored as a separate file. For example:

-- transformation.sql
CREATE OR REPLACE TABLE transformed_sales AS
SELECT
  customer_id,
  SUM(amount) as total_spent,
  COUNT(*) as transaction_count
FROM raw_sales
WHERE date > '2023-01-01'
GROUP BY customer_id;

This script runs in the pipeline's compute environment. If the transformation uses a function not available in that environment, such as REGEXP_REPLACE, it may silently fail or return unexpected results.

In ELT, transformations run in the data warehouse or lake's compute engine. The same logic would be executed in BigQuery:

-- executed in BigQuery
CREATE OR REPLACE TABLE transformed_sales AS
SELECT
  customer_id,
  SUM(amount) as total_spent,
  COUNT(*) as transaction_count
FROM raw_sales
WHERE date > '2023-01-01'
GROUP BY customer_id;

If the warehouse does not support a specific SQL function, such as STRING_AGG, it will return an error like:

ERROR: Function STRING_AGG is not supported in this context

Data Flow and Error Handling

In ETL pipelines, if a transformation fails, the entire batch may be marked as failed. The system typically logs the error and allows for reprocessing of the failed batch. For example, a batch might fail with:

Batch 12345 failed during transformation step
Error: Division by zero in calculation

In ELT pipelines, data loading usually succeeds even if transformations fail later. The raw data is available in the destination system for debugging or reprocessing. If a transformation fails, it's often logged separately and can be retried independently.

Scheduling and Orchestration

ETL pipelines are typically scheduled to run at specific intervals. A cron job might trigger:

etl-pipeline --config /etc/etl/config.json --run-id 202310151400

If the pipeline fails, it usually exits with a non-zero code and writes logs to a specified location. The orchestration tool may retry failed runs or send alerts.

ELT pipelines often have simpler scheduling since data loading is typically atomic. The orchestration command might be:

elt-pipeline --config /etc/elt/config.yaml --timestamp 2023-10-15T14:00:00Z

The transformation step is often separate from the loading step and can be triggered independently.

Performance Considerations

In ETL, performance depends on the processing engine's ability to handle transformations. Large datasets may require partitioning or parallel processing. For example, a pipeline might fail with:

Memory allocation error: 8GB limit exceeded

This occurs when transformation results exceed memory limits in the processing environment.

In ELT, performance depends on the warehouse's compute capabilities. If a query takes longer than expected, it may timeout or be cancelled:

Query execution timeout after 300 seconds

Common Pitfalls

A common ETL pitfall is assuming that transformations can be easily moved to the destination system later. When a transformation uses proprietary functions or libraries not available in the target environment, it fails silently or produces incorrect results.

An ELT pitfall is loading data without validation. Raw data may contain unexpected formats or missing values. For example, a column might be expected to be numeric but contains string data:

ERROR: Cannot cast 'abc' to numeric type

Data Type Handling

In ETL, data types are often explicitly defined during the transformation step. If a source has a VARCHAR(50) and the target expects TEXT, the pipeline may convert or truncate values.

In ELT, data types are preserved as much as possible during loading. However, if the destination system doesn't support a specific type, it may fall back to a generic representation:

Warning: Column 'metadata' converted from JSON to STRING

Version Control and Deployment

ETL configurations are often version-controlled using Git. Changes to transformation logic or pipeline definitions are tracked through commits. For example:

git add pipeline-config.json
git commit -m "Update sales aggregation logic"

In ELT, the same applies to configuration files, but the transformation scripts are typically stored separately and deployed independently.

Monitoring and Alerting

ETL pipelines often include checks for data quality during the transformation phase. If a transformation produces no output, it may fail with:

ERROR: Transformation returned empty result set

ELT pipelines monitor for successful data loading and separate alerting for transformation failures. If a transformation job does not complete within expected time limits, it may trigger an alert.

Testing and Validation

In ETL, test cases validate the transformation logic directly. For example:

-- test script
SELECT COUNT(*) FROM transformed_sales WHERE customer_id IS NULL;

If this query returns a non-zero count, the pipeline fails with:

Test validation failed: 5 rows have NULL customer_id

In ELT, tests validate data quality in the warehouse after transformations. The same test might be run as:

-- warehouse test
SELECT COUNT(*) FROM transformed_sales WHERE customer_id IS NULL;

If this fails, it's logged separately and may not halt the entire pipeline.

This page was rewritten on 16 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.