ETL Patterns in SQL: Implementing Extract, Transform, Load in Practice

1. What ETL patterns actually solve

An ETL pattern describes a repeatable structure for extracting data from a source system, transforming it into a target format and loading it into a target system. The instinctive move for many developers is to implement these three steps in application code: read rows from the source, transform them in Python or PHP, then write them back one at a time. That works for small data volumes, but it scales poorly once millions of rows per run need processing, because every row causes a round trip to the database.

The more robust approach shifts as much logic as possible into SQL itself. Extract becomes a filtered SELECT query, transform becomes a combination of CASE, COALESCE, window functions and joins, load becomes a single MERGE or INSERT statement. This ETL pattern uses the database's set based processing instead of working around it row by row. The following sections show what extract, transform and load actually look like as SQL patterns, how incremental loads work and how to build data quality checks directly into the pipeline.

2. Extract phase: pulling source data cleanly

The extract phase of an ETL pattern determines the runtime of the entire pipeline. A common mistake is exporting the full source table on every run, even though only a fraction of rows changed since the last run. Instead, one filters on a watermark column, usually an updated_at timestamp or a monotonically increasing ID, and reads only rows that arrived since the last successful run. This ETL pattern often reduces the volume of data transferred by more than ninety percent.

It is important to persist the watermark itself in a control table rather than compute it in application code. That keeps the extract logic idempotent and traceable, even when a job fails repeatedly and gets restarted. With distributed source systems, a small time buffer is added on top to absorb clock skew between the application server and the database, otherwise rows written exactly at extract time can be lost.

-- Incremental extract using a persisted watermark
-- Read control table for last successful extract timestamp
SELECT last_extracted_at
FROM etl_control
WHERE source_table = 'orders';

-- Extract only rows changed since the watermark,
-- with a small safety buffer for clock skew
SELECT order_id, customer_id, status, total_amount, updated_at
FROM orders
WHERE updated_at > (
  SELECT last_extracted_at - INTERVAL '5 minutes'
  FROM etl_control
  WHERE source_table = 'orders'
)
ORDER BY updated_at ASC;

-- After a successful load, advance the watermark
UPDATE etl_control
SET last_extracted_at = (SELECT MAX(updated_at) FROM staging_orders)
WHERE source_table = 'orders';

3. Transform phase: transformation as a set operation

The transform phase of an ETL pattern is almost always worth doing as a SQL query rather than a loop in application code. CASE expressions encode business rules, COALESCE replaces missing values with defaults, and window functions compute running totals or rankings without hand rolled aggregation logic. The decisive advantage: the database optimizes these operations itself and parallelizes them across multiple cores, while an application loop always stays sequential.

Another important ETL pattern in the transform phase is separating staging and target schema. Raw data first lands unchanged in a staging table, and the transformation happens in a second SQL step from staging to target. This separation makes it possible to debug faulty transformations without re-running the extraction, and it makes every step of the pipeline individually traceable and testable.

-- Transform staged orders into the target schema
INSERT INTO fact_orders (order_id, customer_id, status_code, revenue_eur, order_date)
SELECT
  s.order_id,
  s.customer_id,
  CASE
    WHEN s.status = 'completed' THEN 'C'
    WHEN s.status = 'cancelled' THEN 'X'
    ELSE 'P'
  END AS status_code,
  COALESCE(s.total_amount, 0) * COALESCE(fx.rate_to_eur, 1) AS revenue_eur,
  CAST(s.updated_at AS DATE) AS order_date
FROM staging_orders s
LEFT JOIN fx_rates fx ON fx.currency = s.currency
  AND fx.rate_date = CAST(s.updated_at AS DATE);

4. Load phase: filling target tables efficiently

The load phase is the step where many hand built ETL solutions become inefficient. Individual INSERT statements per row incur their own transaction overhead on every call and are orders of magnitude slower than batch operations for large data volumes. The production ready ETL pattern is a single MERGE statement that combines INSERT and UPDATE in one call while processing the entire batch size within a single transaction.

MERGE (emulated in MySQL as INSERT ... ON DUPLICATE KEY UPDATE) compares source data against target data by key: if the row already exists it gets updated, otherwise it gets inserted. This ETL pattern makes the load phase idempotent by design, because rerunning it with the same data does not create duplicates, it simply writes the same state again.

-- PostgreSQL: MERGE combines insert and update in a single statement
MERGE INTO fact_orders AS target
USING staging_orders_transformed AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
  UPDATE SET
    status_code = source.status_code,
    revenue_eur = source.revenue_eur
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, status_code, revenue_eur, order_date)
  VALUES (source.order_id, source.customer_id, source.status_code,
          source.revenue_eur, source.order_date);

-- MySQL equivalent: upsert via ON DUPLICATE KEY UPDATE
INSERT INTO fact_orders (order_id, customer_id, status_code, revenue_eur, order_date)
VALUES (1001, 42, 'C', 129.90, '2026-07-30')
ON DUPLICATE KEY UPDATE
  status_code = VALUES(status_code),
  revenue_eur = VALUES(revenue_eur);

5. Incremental loads vs. full loads

A full load reloads the complete dataset on every run, which is simple to implement but increasingly expensive as tables grow. An incremental load, the central ETL pattern for growing data volumes, processes only rows that changed since the last run. The choice between the two depends on the size of the source table and its change rate: for small reference tables with rare changes, a full load is often simpler and more robust than the added complexity of a watermark.

For large fact tables with millions of rows per day, incremental load is practically the only viable option. It is important to account for deleted rows in the source system here: a plain updated_at filter does not detect hard deletes. That requires either soft deletes in the source system (a deleted_at flag instead of physical deletion) or a periodic reconciliation of the full key set between source and target.

6. Idempotency: making pipelines repeatable

Idempotency is the property of an ETL pattern that produces the same result every time it runs with the same input data, no matter how many times it is executed. This is not an academic nicety, it is a hard requirement for production pipelines because jobs regularly fail and get restarted. A non idempotent pipeline design produces duplicate rows or inconsistent totals on a retry, an idempotent design produces exactly the same final state.

The combination of MERGE based upserts, transactional processing of entire batches and a control table for the last successful run makes an ETL pattern idempotent. Every batch is processed in a single transaction: either all rows of the batch commit together with the watermark update, or none do. An aborted job therefore never leaves behind a half processed state that would corrupt the next run.

-- Idempotent batch: staging, transform, load and watermark
-- update all commit together, or none at all
BEGIN;

TRUNCATE TABLE staging_orders_batch;

INSERT INTO staging_orders_batch
SELECT * FROM staging_orders
WHERE batch_id = 20260730;

MERGE INTO fact_orders AS target
USING staging_orders_batch AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET revenue_eur = source.total_amount
WHEN NOT MATCHED THEN INSERT (order_id, revenue_eur)
  VALUES (source.order_id, source.total_amount);

UPDATE etl_control
SET last_extracted_at = NOW(), last_batch_id = 20260730
WHERE source_table = 'orders';

COMMIT;

7. Data quality checks directly in SQL

Faulty data that enters a data warehouse unnoticed costs a multiple of the time an early check would have taken. A proven ETL pattern builds data quality checks directly between transform and load: row count comparisons between source and staging, null checks on required fields and range checks on plausible values. If a check fails, the pipeline aborts in a controlled way instead of silently passing bad data along.

These checks can be expressed entirely in SQL and do not need a separate tool to run. A simple count query compares row numbers before and after transformation, an aggregation checks whether total revenue falls within an expected range. This ETL pattern turns data quality into a fixed, automated part of every run instead of a manual spot check.

-- Row count parity check between staging and target
SELECT
  (SELECT COUNT(*) FROM staging_orders_batch) AS staged_count,
  (SELECT COUNT(*) FROM fact_orders WHERE order_date = CURRENT_DATE) AS loaded_count;

-- Null check on mandatory columns
SELECT COUNT(*) AS bad_rows
FROM staging_orders_batch
WHERE customer_id IS NULL OR total_amount IS NULL;

-- Plausibility check: revenue must stay within an expected range
SELECT SUM(revenue_eur) AS daily_revenue
FROM fact_orders
WHERE order_date = CURRENT_DATE
HAVING SUM(revenue_eur) NOT BETWEEN 1000 AND 500000;

8. Orchestration and scheduling of ETL jobs

A single ETL pattern rarely solves the entire problem, usually several extract transform load steps need to run in the right order and with dependencies on each other. Orchestration tools such as Airflow, Dagster or simple cron jobs running SQL scripts take care of scheduling, while the actual data processing stays in SQL. It is important that every step is individually repeatable, so a failure in step three does not automatically rerun steps one and two.

A simple but robust ETL pattern for orchestration is a control table holding a status per batch: pending, running, success, failed. Every orchestration run first checks the status of the previous run before starting a new one, which prevents parallel, competing executions of the same job. This status table simultaneously acts as the central point for monitoring and alerting on failed runs.

9. ETL patterns compared

Choosing the right ETL pattern depends heavily on data volume, latency requirements and the complexity of the transformation. The table below compares the common approaches.

ELT is gaining importance in cloud data warehouses because compute for transformations is cheaper and more elastic there than on a separate ETL server. The ETL pattern stays conceptually the same, only the order of transform and load flips: raw data lands unchanged in the target system first, transformation follows afterward as a SQL view or a materialized table in the same system.

10. Summary

The most important ETL patterns in SQL reduce complex data processing to a handful of clearly bounded set operations. Extract reads only changed rows via a watermark, transform expresses business rules as CASE, COALESCE and window functions, load writes idempotently into the target table via MERGE. Data quality checks between transform and load prevent faulty data from being processed further unnoticed.

The biggest lever is keeping as much logic as possible inside the database instead of pushing it into application code. A SQL based ETL pattern is easier to test, easier to optimize and automatically benefits from every improvement to the database optimizer, without touching a single line of application code.

11. FAQ: ETL Patterns in SQL