We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design Masterclass · Stream Batch Processing
Free preview: the first 2 parts of this 9-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Stream Batch Processing
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
Your application database is PostgreSQL. Your analytics warehouse is BigQuery. Your marketing team needs data in Salesforce. Your ML pipeline needs features in a . Data lives in many places, in many formats, and it needs to flow between them.
ETL. Extract, Transform, Load, is the process of pulling data from source systems, reshaping it (cleaning, aggregating, joining), and loading it into a destination. It is the plumbing of the data world: invisible when working, catastrophic when broken.
Every organization that uses data (which is every organization) runs ETL pipelines. Apache Airflow, dbt, Fivetran, Stitch, and custom scripts are all ETL tools. Understanding ETL patterns is essential for anyone building data-intensive applications.
ETL has three stages and the whole point of keeping them separate is that each one breaks differently.
Extract talks to the source system. The failure modes are network timeouts, rate limits, connection caps, and schema changes at the source. A Postgres extract holds a connection and runs a SELECT. A Stripe extract pages through a at 100 requests per second. A vendor SFTP extract downloads a CSV that might not be there yet. Each one needs its own retry logic because the failure signatures have nothing in common.
Transform is pure compute. It reads raw data, applies joins, type casts, deduplication, currency conversion, and writes cleaned output. The failure modes are bugs in the logic, null values in unexpected columns, and schema drift from the source. Transform should never call an external service. If it does, backfills become non-deterministic.
Load writes to the destination warehouse. The failure mode is contention. Two loaders writing the same partition at the same time will race and produce inconsistent row counts. The fix is a MERGE keyed on primary key plus partition date, with a concurrency limit of 1 per target table.
| Stage | Bottleneck | Retry strategy | Typical runtime |
|---|---|---|---|
| Extract | Source system I/O, rate limits | Per-source backoff, resume from watermark | 5 to 60 minutes |
| Transform | CPU and memory on compute cluster | Rerun from raw, no external dependencies | 2 to 30 minutes |
| Load | Warehouse write , locking | MERGE with , concurrency=1 | 1 to 10 minutes |
Fivetran, Airbyte, and Stitch handle the extract. Spark and dbt handle the transform. The loader is usually a bulk COPY or a dbt model materializing a table. Understanding which stage broke and why is 80 percent of operating a data pipeline.