We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningIntermediate · Data Replication & Distribution
Extract, Transform, Load: a pipeline that extracts data from sources, transforms it into the desired format, and loads it into a destination like a data warehouse.
In short
ETL (Extract, Transform, Load) is a data pipeline pattern that pulls data out of source systems, reshapes and cleans it into a target format, and writes it into a destination such as a data warehouse. The defining feature is that transformation happens before the data lands, so what arrives in the warehouse is already cleaned, joined, and ready to query.
In short
ETL (Extract, Transform, Load) is a data pipeline pattern that pulls data out of source systems, reshapes and cleans it into a target format, and writes it into a destination such as a data warehouse. The defining feature is that transformation happens before the data lands, so what arrives in the warehouse is already cleaned, joined, and ready to query.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers ETL across a run of lessons, not one page. These 4 alone are about 85 minutes of step-by-step reading, every one with a quiz.
Extract, Transform, Load, moving and reshaping data between systems.
Convert data from one format, structure, or representation to another, the glue between incompatible systems.
Fix, standardize, and repair dirty data, turning messy real-world inputs into reliable records.
Moving data between systems, formats, or schemas safely and completely, with validation, rollback, and zero data loss.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with ETLpay once,
yours for life
ETL stands for the three steps it runs in order. Extract reads raw records from sources like a Postgres production database, a Salesforce API, application log files, or a stream. Transform applies business rules: it cleans bad values, converts data types, deduplicates rows, joins tables, aggregates daily totals, and maps source columns to the warehouse schema. Load writes the finished result into the destination, usually a data warehouse such as Snowflake, BigQuery, or Redshift.
The point of ETL is to move data out of systems built for handling one transaction at a time and into a system built for analytics across millions of rows. A production database is tuned for fast single-row writes and reads. A warehouse is tuned for scanning huge tables to answer questions like revenue by region last quarter. ETL is the bridge between the two.
The word that matters most is the middle one. In classic ETL, transformation runs on a separate processing layer before the data ever reaches the warehouse, so only clean, structured data is loaded.
A typical ETL job runs on a schedule, often nightly or hourly, orchestrated by a scheduler like Apache Airflow or Dagster. The extract step connects to each source and pulls either a full snapshot or, more commonly, only the rows that changed since the last run. That incremental pull uses a watermark column such as updated_at, or change data capture that reads the database write-ahead log so the source is never scanned in full.
The transform step is where most of the engineering lives. The pipeline parses raw records, drops malformed rows or routes them to a dead-letter location, standardizes formats like phone numbers and timestamps, looks up reference data, and computes derived fields. Tools range from hand-written Python or Spark jobs to managed services. Heavy transforms on big data sets typically run on a distributed engine like Apache Spark so the work splits across many machines.
The load step writes results in bulk. Warehouses are far faster loading a million rows in one batch than inserting them one at a time, so ETL almost always stages data in object storage like S3 first, then issues a bulk COPY into the warehouse. Loads are usually idempotent: each run can re-load the same partition safely using an upsert or a delete-then-insert by date, so a failed and retried job does not create duplicates.
Use ETL when the destination cannot store raw data cheaply, when you must mask or drop sensitive fields before they land for compliance reasons, or when the transformation logic is expensive and you want it computed once on a controlled cluster rather than inside the warehouse. It is the long-standing pattern for moving operational data into reporting systems.
The main alternative is ELT, which flips the last two steps: load raw data first, then transform inside the warehouse using SQL. Modern cloud warehouses are so fast and cheap that ELT has become the default for many teams, because raw data stays available for re-processing and analysts can rebuild transformations without re-extracting. ETL still wins when source-side compute is needed, when raw storage is a liability, or when the warehouse should not see unfiltered data.
The trade-offs of ETL are latency and rigidity. Batch ETL means analytics lag reality by minutes to a day. And because transforms run before load, fixing a bug in transformation logic means re-running the whole pipeline from extract, since the raw data was never kept.
Picture an e-commerce company. Orders live in a Postgres database, customer support tickets live in Zendesk, and ad spend lives in the Google Ads API. The finance team wants one dashboard showing profit per customer.
An ETL pipeline runs at 2 AM. It extracts yesterday's orders using the updated_at watermark, pulls new tickets from the Zendesk API, and pulls spend from Google Ads. The transform step converts every currency to USD, joins orders to customers, sums ad spend per customer, subtracts refunds flagged in support tickets, and produces one clean fact table keyed by customer and date. The load step bulk-writes that table into BigQuery.
By 7 AM the dashboard shows clean profit numbers without anyone touching the raw, messy source systems. The production database never gets hammered by analyst queries, and the warehouse only ever holds the curated result.
Apache Airflow
The most widely used open-source orchestrator for scheduling and monitoring ETL jobs as directed graphs of tasks.
Apache Spark
Runs the transform step at scale, splitting joins and aggregations across a cluster for terabyte-sized data sets.
AWS Glue
Amazon's managed ETL service that auto-generates Spark code and catalogs source schemas for loading into Redshift or S3.
Informatica PowerCenter
The classic enterprise ETL tool used by banks and large companies to move data into on-premise warehouses for decades.
In ETL the data is transformed before it lands, so the warehouse only stores clean data. In ELT raw data is loaded first and transformed afterward using SQL inside the warehouse. ELT keeps the raw data around for re-processing and is the common default with cloud warehouses like Snowflake and BigQuery.
ETL is one specific kind of data pipeline. A data pipeline is any system that moves data from one place to another, which can include streaming, replication, or simple copies. ETL is the pattern that specifically extracts, transforms, then loads, usually for analytics.
Classic ETL runs in scheduled batches, often hourly or nightly. Streaming ETL exists and processes records continuously using tools like Kafka and Spark Structured Streaming, but most reporting pipelines are still batch because the destination dashboards do not need second-level freshness.
Production databases are tuned for fast single-row transactions, and large analytical scans would slow them down and risk the customer-facing app. ETL copies data into a warehouse built for big scans, isolating heavy analytics from live traffic and letting you join data from many different sources in one place.
Orchestration is commonly handled by Airflow or Dagster. Transformation runs on Spark, dbt, or plain Python. Managed services like AWS Glue, Google Cloud Dataflow, and Fivetran handle extract and load. Destinations are usually Snowflake, BigQuery, or Redshift.
Data Transformation
Convert data from one format, structure, or representation to another, the glue between incompatible systems
Data Cleansing
Fix, standardize, and repair dirty data, turning messy real-world inputs into reliable records
Data Migration
Moving data between systems, formats, or schemas safely and completely, with validation, rollback, and zero data loss
ETL vs ELT: What a Model Loses When the Raw Rows Are Thrown Away
ETL vs ELT for machine learning, measured on a real shop's 1,067,371 invoice lines. A nightly job kept one row per customer per day in the warehouse and nothing else. Adding features rebuilt from the raw lines moved AUC from 0.712 to 0.725 for who orders again and from 0.728 to 0.736, a gap that could be luck, for who cancels; on a question chosen after the results, one the daily table never recorded, it moved AUC by 0.055 (0.059 with one added feature). Keeping every line cost $0.00218 a month. Then where transforms run, bronze, silver and gold, dbt models pinned to a date, lineage, and when to transform first.
ETL Pipeline
Orchestrating multi-step data transformations. Airflow, dbt, and pipeline design patterns
Data Lake
A centralized repository that stores raw data at any scale in its native format. Unlike a data warehouse, data doesn't need to be structured or cleaned before loading.
Batch Processing
Processing large volumes of data in scheduled chunks rather than in real time. Think nightly reports, ETL jobs, and data warehouse loads.
Data Lineage
Tracking data from its origin through every transformation and system it passes through. Answers 'where did this number come from?' for audits and debugging.
Backfill
Retroactively populating a new data store, index, or column with historical data. Typically done as a batch job when adding a new feature that needs past data.
Database Partitioning
Dividing a large table into smaller, more manageable pieces while keeping them in the same database. Sharding is partitioning across servers.
Read Replica
A copy of your database that handles read queries, reducing load on the primary database. Writes still go to the primary and replicate out.
This page explains the idea. The "ETL" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the ETL lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about ETL in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
