← Blog
A lavender funnel with a cream rim and orange hanging tab on a pale background.
Published on
2026-11-16

ETL vs ELT: where should data transformations run?

ETL transforms data before loading it into an analytical destination. ELT loads the extracted data first and transforms it inside that destination. The choice affects where processing happens, which raw fields enter the system, and how you rerun the work when a rule changes.

Start by naming the destination and the people who can access it. Then decide which data should cross that boundary. This guide compares the two paths using the same seven synthetic records and a tested Python and DuckDB example. It demonstrates processing order and validation behavior, without claiming that either approach is always faster or cheaper.

ETL vs ELT at a glance

ETL means extract, transform, load. ELT means extract, load, transform. The difference is the position of transformation relative to loading into the target system. The AWS comparison of ETL and ELT describes that sequence. For a real workload, the target's processing capabilities and your data-access requirements determine whether the sequence is useful.

DecisionETLELT
Where transformation runsBefore data enters the chosen destinationInside the destination after loading
What reaches the destinationThe selected and transformed outputThe extracted input covered by the loading contract
Where a bad value is handledIn the preprocessing stageIn destination-side validation or transformation
What a changed rule needsAccess to retained input or a new extractionUsable retained raw data in the destination, or a new extraction
Where capacity is neededProcessing workers plus the destinationDestination processing capacity as well as storage
What the acronym does not establishSecurity, speed, cost, or reliable recoverySecurity, speed, cost, or reliable recovery

Name the destination before drawing the pipeline

A pipeline can have several storage stages. Copying a source file to an object-storage bucket does not, by itself, tell you whether the overall process is ETL or ELT. If a CPU job transforms that file before loading an analytical database, the database still receives transformed data. Describe the sequence relative to the system your readers are evaluating.

Our example uses two separate in-memory DuckDB databases as the destinations. In the ETL path, Python validates the input, converts accepted amounts to decimals, and removes the contact field before insertion. In the ELT path, the database receives all seven records and all four source fields, then SQL produces the accepted table.

The final accepted tables contain the same values. Their raw-data exposure is different. In ELT, excluding a field from a reporting table does not remove it from the raw table. In ETL, excluding a field from the destination does not remove it from the original source or the preprocessing system. Those locations still need deliberate access and retention rules.

Run the same dataset through both paths

The source contract has four string-or-null fields: order ID, category, amount text, and a synthetic contact address. Accepted order IDs use ord- followed by three ASCII digits; categories are exactly books or tools. Amounts are nonnegative USD values from 0.00 to 9999.99, written with two decimal places. The example deliberately rejects refunds, unexpected categories, and malformed identifiers. Those are example business rules, not general properties of ETL.

Use Python 3.11 or later and install the pinned dependency in your project environment:

python -m pip install duckdb==2.0.0

Save the following as compare_pipelines.py. Both destinations exist only in memory and close at the end of each call. The code opens no existing database and makes no network requests.

import re
from decimal import Decimal

import duckdb

ID_PATTERN = r"ord-[0-9]{3}"
AMOUNT_PATTERN = r"(0|[1-9][0-9]{0,3})\.[0-9]{2}"
ROWS = [
    ("ord-001", "books", "12.50", "a@example.invalid"),
    ("ord-002", "books", "7.50", "b@example.invalid"),
    ("ord-003", "tools", "0.00", "c@example.invalid"),
    ("ord-004", "tools", "oops", "d@example.invalid"),
    ("ord-005", "tools", "-3.00", "e@example.invalid"),
    ("bad-id", "books", "2.00", "f@example.invalid"),
    ("ord-007", "other", "1.00", "g@example.invalid"),
]


def transform(row):
    order_id, category, amount, contact = row
    if order_id is None or not re.fullmatch(ID_PATTERN, order_id):
        return None, "invalid_id"
    if category not in ("books", "tools"):
        return None, "invalid_category"
    if amount is None or not re.fullmatch(AMOUNT_PATTERN, amount):
        return None, "invalid_amount"
    return (order_id, category, Decimal(amount)), None


def compare(rows):
    # This demonstration accepts four source fields, each a string or None.
    if any(len(row) != 4 or any(v is not None and not isinstance(v, str)
                               for v in row) for row in rows):
        raise ValueError("Expected four string-or-None fields per row")
    accepted, rejected = [], []
    for row in rows:
        cleaned, error = transform(row)
        if error:
            rejected.append((row[0], error))
        else:
            accepted.append(cleaned)

    # Separate in-memory destinations. No existing database is opened.
    etl = duckdb.connect(":memory:")
    elt = duckdb.connect(":memory:")
    try:
        etl.execute("CREATE TABLE accepted(order_id VARCHAR, category VARCHAR, amount DECIMAL(6,2))")
        if accepted:
            etl.executemany("INSERT INTO accepted VALUES (?, ?, ?)", accepted)

        elt.execute("CREATE TABLE raw(order_id VARCHAR, category VARCHAR, amount_text VARCHAR, contact VARCHAR)")
        if rows:
            elt.executemany("INSERT INTO raw VALUES (?, ?, ?, ?)", rows)
        elt.execute("""
            CREATE TABLE checked AS
            SELECT *, CASE
                WHEN NOT COALESCE(regexp_full_match(order_id, ?), FALSE)
                    THEN 'invalid_id'
                WHEN category IS NULL OR category NOT IN ('books', 'tools')
                    THEN 'invalid_category'
                WHEN NOT COALESCE(regexp_full_match(amount_text, ?), FALSE)
                    THEN 'invalid_amount'
                ELSE NULL
            END AS error
            FROM raw
        """, [ID_PATTERN, AMOUNT_PATTERN])
        elt.execute("""
            CREATE TABLE accepted AS
            SELECT order_id, category, TRY_CAST(amount_text AS DECIMAL(6,2)) AS amount
            FROM checked WHERE error IS NULL
        """)

        def snapshot(connection):
            return {
                "rows": connection.execute("SELECT * FROM accepted ORDER BY order_id").fetchall(),
                "columns": connection.execute("DESCRIBE accepted").fetchall(),
                "totals": connection.execute("SELECT category, SUM(amount) FROM accepted GROUP BY category ORDER BY category").fetchall(),
                "tables": connection.execute("SHOW TABLES").fetchall(),
            }

        return {
            "etl": snapshot(etl), "elt": snapshot(elt),
            "etl_rejected": rejected,
            "elt_rejected": elt.execute("SELECT order_id, error FROM checked WHERE error IS NOT NULL ORDER BY order_id").fetchall(),
            "elt_raw": elt.execute("SELECT * FROM raw ORDER BY order_id").fetchall(),
        }
    finally:
        etl.close()
        elt.close()


if __name__ == "__main__":
    result = compare(ROWS)
    assert result["etl"]["rows"] == result["elt"]["rows"]
    assert sorted(result["etl_rejected"]) == result["elt_rejected"]
    print("Accepted rows in each destination:", len(result["etl"]["rows"]))
    print("Rejected rows in each path:", len(result["etl_rejected"]))
    print("ELT raw rows retained during the run:", len(result["elt_raw"]))
    for category, total in result["etl"]["totals"]:
        print(f"{category}: {total:.2f} USD")

Run the comparison:

python compare_pipelines.py

The fixture produces:

Accepted rows in each destination: 3
Rejected rows in each path: 4
ELT raw rows retained during the run: 7
books: 20.00 USD
tools: 0.00 USD

The amount check comes before conversion. That prevents the example from silently accepting a value with extra decimal places by rounding it. DuckDB documents full-string pattern matching, TRY_CAST returning NULL when conversion fails, and fixed-point DECIMAL types. The script combines these operations with an explicit acceptance policy.

Read the result without overstating the test

Both paths accept three records, reject four, and produce category totals of USD 20.00 for books and USD 0.00 for tools. The ETL destination contains only the accepted table. The ELT destination also contains the raw and checked tables, including the synthetic contact fields, until the connection closes.

All 10 local tests passed with CPython 3.12.14 and DuckDB 2.0.0 on Windows. They check the exact accepted rows, rejection reasons, destination schemas, retained raw fields, totals, amount boundaries, missing values, empty input, the source-field contract, and repeated execution of the fixture. Additional cases reject values such as NaN, scientific notation, extra decimal places, and a category with unexpected capitalization.

This is a correctness example using small, local datasets. Both paths run in one Python process, so it does not compare separate server tiers, warehouse performance, access-control enforcement, or cloud costs. Repeating the fixture creates fresh databases; it does not prove that appending the same batch to a persistent production table is safe.

For a practical import that validates a daily file before writing to PostgreSQL, continue with our Python ETL pipeline guide. Keep the choice of transformation location separate from the mechanics of a particular loader.

Choose by the constraint that matters

Choose ETL when the destination should receive a narrow, validated result. For example, a reporting database may need monthly totals but have no reason to receive customer contact fields. Preprocessing can select the necessary columns and reject invalid values before loading. The worker then becomes part of your operating responsibility: it needs enough capacity, controlled access to source data, and a clear failure policy.

Choose ELT when the destination can process the data and retaining raw inputs serves a defined purpose. An analytics team may need to revise grouping rules or build several views over the same retained batch. That flexibility depends on usable raw data, appropriate processing capacity, and permissions that distinguish raw inputs from approved reporting outputs.

Some workloads combine the two. You might remove an unnecessary field before loading, then build reporting models inside the destination. Document that actual sequence. A label should not hide which fields are retained or where a transformation runs.

Neither approach requires a particular file format, and neither automatically makes a pipeline real time. Extraction frequency, ingestion behavior, scheduling, and processing capacity determine how soon the consumer receives usable data.

Plan replay, failure handling, and cost together

Define what happens to an invalid row. The example records an identifier and reason; a real pipeline may quarantine the source record in a restricted location, reject the complete batch, or accept a documented subset. Make rejected counts visible so a successful job exit does not conceal missing data.

Before rerunning a persistent load, decide how the destination recognizes a batch or record it has already received. Replacing a snapshot, merging by a stable business key, and appending a new event have different consequences. Record the input version and transformation version, then test interrupted execution and recovery against the chosen behavior. Neither ETL nor ELT supplies those guarantees through its name.

Compare costs using the same input volume, delivery deadline, retention period, and accepted output. Include extraction work, transformation compute, temporary and retained storage, data transfer, monitoring, and the time spent maintaining the pipeline. Moving transformations into a destination may change where processing is billed; it does not make the work disappear.

Measure the time until usable data is available, not just the time until raw input has loaded. An early landing time is useful only if the later validation and transformation also meet the consumer's deadline.

Map the workload to Hivenet

Compute with Hivenet provides CPU infrastructure for customer-operated jobs and services. For this decision, bring the source size, processing rules, run frequency, memory needs, and target system. Use those details to size the worker or analytical workload you intend to operate.

Hivenet Object Storage can hold pipeline inputs, retained datasets, and generated artifacts through S3-compatible tools. Define storage and processing as separate responsibilities. This example does not establish a managed ETL service or an object store that performs your database transformations.

A useful first workload review starts with one representative batch, an explicit destination, and a measurable acceptance rule. Once those are clear, the compute and storage choices become easier to test.

Your next workload belongs on Hivenet.

Pick one AI, compute, or storage workload and see the difference for yourself. Spin it up in minutes, or let our team map your fastest path to production.