What Is ETL? How Extract, Transform and Load Works, and When ELT Wins in 2026

The daily export from the policy system lands at 2:15 am. By 9 am the regional heads are reading a dashboard built on it, and twice this month the numbers were wrong: once because the same 2,600 rows arrived twice, once because a blank amount column quietly turned the measure into text. Nobody on the two-person data team at that mid-size Pune insurer wrote a bad query. They just had no stage in the middle that checks.

That missing stage is what ETL is.

What Is ETL? How the Three Stages Actually Work

Extract, transform, load describes where the cleaning happens: in a compute layer between source and destination, under your control, before anything is written. The source is read, never changed. The destination only receives rows that already passed your rules.

Extract

You read from wherever the data lives: a Postgres replica, a Salesforce API, an SFTP drop of CSVs. Two decisions bite later. Full load or incremental, because pulling the whole table nightly is fine up to a few million rows and ruinous above that. And what happens when the source is down at 2:15 am: an extract that silently writes an empty file is worse than one that crashes, because the empty file gets loaded.

Transform

You type strings into numbers and dates, deduplicate, quarantine rows that fail checks, and mask anything you are not allowed to store. In a regulated pipeline this is the only place where masking still counts, because after the load the raw value is already on disk somewhere.

Load

Insert, upsert by key, or replace the partition. One rule worth memorising: make the load idempotent, so running the job twice produces the same table rather than double the rows. The most common incident in a young pipeline is a retry that appends instead of replacing.

ETL Pipeline Example: 52,600 Messy Rows Into One Clean Table

Here is an ETL pipeline example you can run right now. It needs Python 3.11 and nothing else, because the standard library ships csv, sqlite3 and hashlib. The input is that insurer's daily payments export: 52,600 rows with exact duplicates, blank amounts, junk amounts, and dates in two formats because someone changed a regional setting in 2019 and nobody noticed.

The transform function is the whole job.

import csv, sqlite3, hashlib, datetime

SALT = "insurer-2026"   # in real life, read this from a secret store

def extract(path):
    with open(path, newline="") as f:
        yield from csv.DictReader(f)

def parse_date(s):
    for fmt in ("%Y-%m-%d", "%d/%m/%Y"):
        try:
            return datetime.datetime.strptime(s, fmt).date().isoformat()
        except ValueError:
            continue
    return None

def transform(rows, stats):
    seen = set()
    for r in rows:
        stats["extracted"] += 1
        key = (r["policy_id"], r["paid_on"], r["amount_inr"])
        if key in seen:
            stats["dropped_duplicate"] += 1
            continue
        seen.add(key)
        try:
            amount = float(r["amount_inr"])
        except ValueError:
            stats["dropped_bad_amount"] += 1
            continue
        if amount <= 0:
            stats["dropped_bad_amount"] += 1
            continue
        paid_on = parse_date(r["paid_on"])
        if paid_on is None:
            stats["dropped_bad_date"] += 1
            continue
        stats["loaded"] += 1
        yield (
            r["policy_id"],
            hashlib.sha256((SALT + r["customer_email"]).encode()).hexdigest()[:32],
            paid_on,
            round(amount, 2),
            r["city"].strip().title(),
            r["status"].strip().upper(),
        )

The load drops and rebuilds the table, the simplest way to make a rerun safe. Fail halfway, run it again, and you get the same 47,381 rows rather than 94,762.

def load(records, db="warehouse.db"):
    con = sqlite3.connect(db)
    con.execute("DROP TABLE IF EXISTS fact_payment")
    con.execute("""CREATE TABLE fact_payment (
        policy_id TEXT, customer_key TEXT, paid_on TEXT,
        amount_inr REAL, city TEXT, status TEXT)""")
    con.executemany("INSERT INTO fact_payment VALUES (?,?,?,?,?,?)", records)
    con.commit()
    n = con.execute("SELECT count(*) FROM fact_payment").fetchone()[0]
    con.close()
    return n

if __name__ == "__main__":
    stats = dict(extracted=0, dropped_duplicate=0, dropped_bad_amount=0,
                 dropped_bad_date=0, loaded=0)
    n = load(transform(extract("payments_raw.csv"), stats))
    for k, v in stats.items():
        print(f"{k:22} {v:>7,}")
    print(f"{'rows in warehouse':22} {n:>7,}")

It runs in 0.75 seconds and prints:

$ python3 etl.py
extracted               52,600
dropped_duplicate        2,600
dropped_bad_amount       2,619
dropped_bad_date             0
rows in warehouse       47,381

Note the third counter. Zero rows failed on dates, because parse_date handles both formats: the chaos everyone worried about was a five-line fix. The damage came from duplicates and blank amounts, which nobody had mentioned. That is normal. You learn what your data actually does by counting what you throw away, which is why those stats counters exist.

Hand-rolled Python is right for one job and wrong for forty. Also read: dbt Tutorial for Beginners 2026, the standard next step once your transforms deserve version control and tests.

ETL vs ELT: The Comparison That Actually Decides It

ETL vs ELT is not a debate about which is modern. It is a question about where raw data is allowed to sit. ETL transforms before the load, so the destination never sees the mess. ELT lands everything raw and transforms in place with SQL, which became the default once storage got cheap enough to keep data you had not cleaned yet. Here is the same job as ELT: the raw file goes into a table, and one SQL statement does what the Python did.

CREATE TABLE fact_payment AS
WITH typed AS (
    SELECT
        policy_id,
        CASE
            WHEN paid_on LIKE '____-__-__' THEN paid_on
            ELSE substr(paid_on,7,4)||'-'||substr(paid_on,4,2)||'-'||substr(paid_on,1,2)
        END                          AS paid_on,
        CAST(amount_inr AS REAL)     AS amount_inr,
        upper(substr(city,1,1))||lower(substr(city,2)) AS city,
        upper(status)                AS status
    FROM raw_payment
    WHERE amount_inr <> '' AND CAST(amount_inr AS REAL) > 0
)
SELECT DISTINCT policy_id, paid_on, amount_inr, city, status
FROM typed;

That produces 47,381 rows. Exactly the number the Python produced. The output tables are identical, so if both give the same answer, what did you actually choose between?

What is sitting in the lake afterwards. The ELT run leaves raw_payment holding 8,960 distinct customer emails in the clear, for as long as you keep the raw layer, which is usually forever. The ETL run leaves a 32 character SHA-256 key and no email column at all. The ELT database was also 51 percent larger, 5.74 MB against 3.80 MB, because you stored the mess as well as the answer.

My recommendation, and it is not a fence-sit: build ELT by default, carve out ETL only for the fields that are legally awkward. Mask or drop identifiers during extraction, land everything else raw, do the rest in SQL where it can be reviewed in a pull request. Practitioner writing through 2026 is close to unanimous that ELT is the general-case default, and the reason is reusability rather than speed. Raw data you kept can serve a dashboard this quarter and a retrieval corpus next quarter. Raw data you transformed away serves nothing.

The trade-off is a bigger storage bill and a governance problem. Somebody owns retention on that raw layer, or you become the team explaining why a five-year-old table still holds customer emails.

ETL Tools in 2026 and What They Really Cost

The script above is a teaching artefact. Production needs retries, alerting, backfills and a schedule, which is the part hand-rolled Python gets wrong. These are the ETL tools worth knowing, with the cost detail vendor pages bury.

Version facts worth having straight, because tutorials go stale fast. Airflow 3 shipped in April 2025 and the current line is 3.3.x, with 3.3.2 published on 17 September 2026; Astronomer's State of Apache Airflow 2026 report, published in January, put Airflow 3 adoption at 26 percent of surveyed users at that point, so an advert asking for Airflow 2 is still normal. dbt Core's stable v1 line reached 1.12.3 on 20 August 2026, with a Rust-based dbt Core 2.0 in alpha since June. DuckDB shipped 1.5.0, codenamed Variegata, in March 2026. Our Apache Airflow tutorial walks the first DAG end to end.

On cost, the question is always Fabric. The Microsoft Learn Data Factory pricing overview meters pipeline activity as capacity unit consumption drawn from the same pool as Spark, SQL and Power BI refreshes, and pay-as-you-go pages checked in September 2026 list capacity at roughly 0.18 US dollars per capacity unit hour. Do the arithmetic first: 2 capacity units across 730 hours is about 263 dollars a month, an F8 four times that, an F64 near 8,410 dollars. Capacity is charged whether or not your pipeline runs, so a nightly job on an oversized SKU is money burned on an idle machine. To try it free, Microsoft Learn documents a 60 day Fabric trial with 64 capacity units and up to 1 TB of OneLake storage. That is the setup used in 360DT's live Microsoft Fabric Data Engineer course, where ingestion and transformation map onto the DP-700 objectives.

What Is ETL Used For? Where You Meet It in Real Work

Three places, and they hire differently.

Analytics and BI

Someone has to produce the table the dashboard sits on. At that Pune insurer it is one nightly job feeding a claims dashboard, and the title is usually analyst rather than engineer. SQL carries you further than Python here, which is the ground a live data analyst program running Excel, SQL, Python and Power BI covers.

Platform data engineering

Dozens of pipelines, a lakehouse, an orchestrator and an on-call rota. The Microsoft DP-700 skills measured document, updated 20 April 2026, splits the exam into three domains weighted at 30 to 35 percent each, and the middle one is ingest and transform data, which Microsoft writes as ELT rather than ETL. Read that as a signal about which pattern to default to. Cloud-side the same work sits behind Azure architecture and DevOps skills or the AWS Solutions Architect and DevOps track, depending on whose cloud your employer bought.

Feeding AI systems

A retrieval corpus is an ETL output: extract documents, chunk and embed them, load vectors. Masking matters more here, because an embedded customer email is far harder to delete than a column. That work needs RAG and agent engineering skills plus the MLOps discipline to keep pipelines reproducible.

What Usually Goes Wrong in an ETL Process

Every one of these has cost somebody a weekend. Keep the fix column.

And the one nobody warns you about. You will fix a dirty source by adding a condition to the transform, it will work, and six months later that function has forty conditions and nobody remembers which upstream bug each one patched. Write the reason in the code, or better, push the fix back to the source team. When the SQL itself starts creaking, 10 SQL mistakes that make queries slow is the next layer.

How to Learn ETL Properly

Four weeks of honest effort beats four months of tutorials. Week one, SQL only: joins, group by, window functions, on a dataset you did not choose. Week two, rebuild the pipeline on this page against your own messy CSV and add a quarantine table. Week three, put it behind an orchestrator so it schedules and retries. Week four, move it to a cloud platform and watch what it costs. To do that with a cohort reviewing your pipeline, the Fabric track below is the closest fit, and the free webinars let you check the teaching style first.

If you are deciding what to do this week: generate a messy CSV, run the 40 lines above against it, read the counters. The moment you can explain why 5,219 rows did not make it, you understand ETL better than most people who list it on a CV. Then rebuild the same job on the platform your target employers actually use. If that is Fabric, the live Fabric data engineering course does this with review at each step, and a free demo class tells you in one evening whether the format suits you.

Related guides

• What Is Apache Iceberg in 2026? The table format that makes an ELT raw layer behave like a real database.
• PL-300 vs DP-700 in 2026 Reporting side or pipeline side, decided before you pay an exam fee.
• Data Engineer Roadmap 2026 The full sequence around ETL, from SQL to a portfolio an interviewer will read.
• Data Engineer Salary in India 2026 What pipeline work is advertised at, by experience and company type.
• Data Engineer Jobs in Hyderabad 2026 Which tools employers in one large market are naming in job posts.

Frequently asked questions

About this guide. 360 Digital Transformation is an Authorized Training Partner of Anthropic and Microsoft. Other certification bodies, vendors and employers named here are not affiliated with us. Tools and versions change quickly; commands and figures cited were checked on 25 September 2026.