What is ETL? Data pipelines explained for beginners

ETL stands for extract, transform, load: a process that copies data from source systems, cleans and reshapes it by business rules, and loads it into one destination such as a data warehouse. A data pipeline is that flow set up to run repeatedly. ELT reverses the last two steps, loading raw data first and transforming it inside the destination.

What are the three stages of ETL?

Microsoft's Azure Architecture Center defines ETL as a data integration process that consolidates data from diverse sources into a unified data store. Google Cloud's description is similar: a way to combine data from multiple systems into a single database, data store, data warehouse or data lake. The three stages each have a job.

• Extract copies raw data from the sources into a staging area, a temporary holding place. AWS says extraction happens by update notification, by incremental extraction of changed records, or by full extraction.
• Transform applies business rules. Microsoft lists filtering, sorting, aggregating, joining, cleaning, deduplicating and validating, and AWS adds format conversion for character sets, units and dates.
• Load writes the result to the destination. AWS describes a full load, usually the first one, and incremental loads that carry only the difference since the last extract.

A data pipeline is the coordinated, usually automated version of these stages, moving data from sources to one or more data stores and reshaping it on the way.

What does a small ETL pipeline look like?

The hypothetical example below follows a small shop's order export through all three stages. The export has five rows: one order appears twice, one city is in capitals, one has a trailing space, one amount contains a comma and one amount is "n/a".

The Python script runs all three stages with the standard library and an in-memory SQLite database.

import csv, io, sqlite3
SOURCE = """order_id,customer,city,amount,order_date
1001,Asha,pune,"1,200",2026-11-01
1002,Bilal,Pune ,850,2026-11-01
1003,Chitra,Chennai,2400,2026-11-02
1003,Chitra,Chennai,2400,2026-11-02
1004,Dev,MUMBAI,n/a,2026-11-02
"""
rows = list(csv.DictReader(io.StringIO(SOURCE)))  # extract
good, rejects = {}, []
for r in rows:  # transform
    amount = r["amount"].replace(",", "")
    if not amount.isdigit():
        rejects.append((r["order_id"], "amount is not a number"))
    else:
        good[r["order_id"]] = (int(r["order_id"]), r["customer"], r["city"].strip().title(), int(amount), r["order_date"])
db = sqlite3.connect(":memory:")  # load
db.execute("CREATE TABLE fact_orders (order_id INTEGER, customer TEXT, city TEXT, amount INTEGER, order_date TEXT)")
db.executemany("INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?)", good.values())
print("extracted:", len(rows), "loaded:", len(good), "rejected:", len(rejects))
print(db.execute("SELECT * FROM fact_orders ORDER BY order_id").fetchall())
print(rejects)

extracted: 5 loaded: 3 rejected: 1
[(1001, 'Asha', 'Pune', 1200, '2026-11-01'), (1002, 'Bilal', 'Pune', 850, '2026-11-01'), (1003, 'Chitra', 'Chennai', 2400, '2026-11-02')]
[('1004', 'amount is not a number')]

Order 1003 was exported twice and loaded once. Order 1004 has no usable amount, so it goes to a reject list for review instead of silently disappearing. Setting rejected rows aside, not deleting them, is the same discipline covered in data cleaning techniques in Excel, SQL and Python.

What is the difference between ETL and ELT?

ETL transforms data before loading it, while ELT loads the raw data first and transforms it inside the destination. The Microsoft page says ELT differs from ETL solely in where the transformation takes place, and that in ELT the target data store's own processing capabilities replace a separate transformation engine. AWS says ELT has become more popular with cloud infrastructure, because the destination now has the processing power for transformations.

The same five rows loaded the ELT way go into a raw_orders table as untouched text, and the transformation runs as SQL inside the database.

CREATE TABLE fact_orders AS
SELECT CAST(order_id AS INTEGER) AS order_id, customer,
       UPPER(SUBSTR(TRIM(city), 1, 1)) || LOWER(SUBSTR(TRIM(city), 2)) AS city,
       CAST(REPLACE(amount, ',', '') AS INTEGER) AS amount, order_date
FROM (SELECT DISTINCT * FROM raw_orders) AS t
WHERE amount <> 'n/a';

This produced the same three fact_orders rows as the Python version. Order 1004 never reaches fact_orders but is still in raw_orders, which still holds all 5 raw rows. A daily revenue query on fact_orders, the kind a dashboard would run, returned two rows.

The query was run on SQLite 3.54.0. PostgreSQL has INITCAP for the city expression. The MySQL 8.4 manual says || means logical OR unless the PIPES_AS_CONCAT mode is enabled, so MySQL needs CONCAT() instead, and the text-cleaning line has to be adjusted per database.

What is the difference between batch and streaming pipelines?

Batch pipelines process a set of records on a schedule, while streaming pipelines process each event as it arrives. Microsoft says ETL and ELT operate on datasets in scheduled batches, whereas real-time streaming ingests events into a message broker or event hub and applies transformations while the data is in motion. It gives monitoring manufacturing equipment, detecting fraud in financial transactions and real-time dashboards as cases where low latency matters.

Google Cloud notes that streaming pipelines have emerged alongside batch pipelines, and some enterprises run both. A daily sales report fits a batch pipeline. An alert that must fire within moments of a suspicious transaction needs a streaming one.

What is the difference between a data warehouse and a data lake?

A data warehouse stores structured, preprocessed data for analytics and business intelligence, while a data lake stores raw data of any kind to be processed later. AWS adds a third term: a data mart is a data warehouse serving one business unit, such as finance, marketing or sales.

AWS says most large organisations use a combination of lakes, warehouses and marts, typically ingesting all data into a lake and then loading warehouses and marts for particular uses. For an analyst, the practical point is that a warehouse table is usually already cleaned, while a lake file may not be.

Which tools sit in the data pipeline category?

Pipeline tools split into those that move data, those that transform it and those that schedule or process it. The table below names five of them with what each does and its status on 7 November 2026.

Fivetran's documentation argues that ELT has replaced fragile ETL pipelines. That is a vendor's view, and Microsoft's guidance above keeps ETL as the right choice in some cases.

Do data analysts need to know ETL?

Data analysts need to understand ETL and ELT because every number in a report has passed through one, and many analysts write the transformation step in SQL. The dbt documentation says anyone on the data team familiar with SQL can contribute to production-grade data pipelines, and that analysts write select statements while dbt creates the models. Airflow's description, authoring, scheduling and monitoring workflows, marks the part that is orchestration rather than analysis.

Understanding is the minimum: knowing where a table came from, how often it refreshes, which rows were rejected and where the raw copy lives. Building and operating ingestion and scheduling is a separate skill set, and not every analyst role includes it. Posts on SQL joins and MongoDB vs MySQL cover the SQL and database knowledge that carries over.

What are the limits and risks of ETL?

ETL and ELT each trade something. AWS says ETL needs more definition at the start: analytics must be involved early to define target types, structures and relationships. Microsoft says ELT only works well when the target system is powerful enough to transform the data efficiently. Raw data loaded without checks can carry duplicates and errors into a lake, as AWS notes.

Two further limits apply to the examples here. The pipeline has five rows, so it says nothing about speed, scheduling or failure handling at scale. And the tools change: dbt now has two major versions and a renamed repository, and the status column above is a snapshot from 7 November 2026.

How does ETL fit into a data analytics programme?

ETL sits behind the data an analyst receives, and a working knowledge of it helps in reading and trusting that data. The Certification Program in Data Analytics with AI and Gen AI is an online programme from E&ICT Academy, IIT Roorkee, run with Masai as the implementation partner, with live sessions and an optional campus immersion. It covers SQL, Python, Google Sheets, prompt engineering, LLM tools and agentic workflows with LangChain and CrewAI, and a capstone project, and its topics include data ecosystems and the information lifecycle.

Frequently asked questions

What is the difference between ETL and ELT?

ETL transforms data before loading it into the destination, while ELT loads raw data first and transforms it inside the destination. Microsoft's guidance is that the difference is solely where the transformation runs. ELT suits modern warehouses with elastic compute and a need to keep raw data. ETL suits constrained targets and curated staging audits.

Do data analysts need to know ETL?

Analysts need to understand it, and many write the transformation step in SQL. Knowing where data came from, how often it refreshes and which rows were rejected prevents wrong conclusions. Building and running ingestion and scheduling is a separate specialism that not every analyst role includes.

What is the difference between a data warehouse and a data lake?

A data warehouse holds structured, preprocessed data with a schema designed before loading, mainly for analytics and business intelligence. A data lake holds raw data of any kind, including logs and clickstreams, with the schema applied when the data is analysed. Many large organisations use both, plus data marts for single business units.

What is a data pipeline?

A data pipeline is an automated sequence that moves data from sources to a destination and reshapes it along the way. Microsoft describes gathering data from multiple sources in various formats, moving it to one or more data stores, and cleaning or transforming it before loading. ETL and ELT are two common pipeline designs.

Is ETL still relevant now that ELT is common?

Yes. AWS says ELT has grown with cloud infrastructure, but Microsoft's guidance still names cases for ETL: offloading heavy transformations from a constrained target, using specialised transformation engines, and meeting compliance needs for curated staging audits. Many pipelines mix both, and the choice depends on the destination.

What is the difference between batch and streaming pipelines?

Batch pipelines process a set of records on a schedule, for example nightly. Streaming pipelines process each event as it arrives, which matters for uses such as fraud detection and live dashboards. Microsoft says ETL and ELT operate on scheduled batches, while real-time streaming processes data as it arrives.

Next step

Rerun the Python example above with an extra row of your own, such as a missing city, and decide whether it should be loaded, corrected or rejected. The Certification Program in Data Analytics with AI and Gen AI covers SQL, Python and Google Sheets alongside a capstone project. For the next layer of database choices, see the post on MongoDB vs MySQL.