ETL Pipelines: Batch Processing, Transformation and Data Loading

ETL (Extract, Transform, Load) is a data integration pattern that pulls data from multiple source systems (databases, APIs, files), transforms it into a clean, consistent format (deduplication, type casting, business logic), and loads it into a destination like a data warehouse. ETL pipelines are the backbone of business intelligence and analytics, enabling organizations to consolidate data from dozens of operational systems into a single queryable store. Tools like Apache Airflow, dbt, Informatica, and AWS Glue orchestrate these pipelines at scale.

Why This Matters

ETL matters because organizations store data across dozens of systems — CRM, billing, product databases, third-party APIs — and business decisions require unified views across all of them. A CEO dashboard showing revenue by region requires joining data from Salesforce, Stripe, and an internal database. Without ETL, analysts write fragile scripts that break silently. Proper ETL pipelines handle schema evolution, data quality validation, incremental loading, error handling, and idempotency. Understanding ETL is essential for any engineer working with analytics, reporting, or data-driven features.

The Building Blocks

• Extract: Pull data from source systems using full snapshots or incremental extraction (change timestamps, CDC). Handle API rate limits, pagination, schema changes, and connection failures.
• Transform: Clean and reshape data — deduplicate records, cast types, apply business rules, join reference data, handle null values, and conform to the target schema.
• Load: Write transformed data to the destination — typically a data warehouse or data lake. Use upserts for idempotency, partitioning for performance, and validation checks post-load.
• Orchestration: Tools like Apache Airflow and Dagster define DAGs (directed acyclic graphs) of pipeline tasks with dependencies, schedules, retries, and alerting.
• Data Quality: Validate data at each stage — row counts, schema conformance, null checks, range validations — and halt the pipeline if quality gates fail to prevent bad data from reaching production.

Under the Hood

An ETL pipeline starts with extraction, which reads data from source systems. Full extraction pulls all records each run — simple but slow and wasteful for large tables. Incremental extraction tracks which records changed since the last run, using updated_at timestamps, database sequence numbers, or change data capture (CDC) streams. CDC-based extraction via tools like Debezium is the most efficient, capturing row-level changes from the database's transaction log with minimal source system impact.

The transform stage applies business logic. Raw data is cleaned (trim whitespace, standardize date formats, handle nulls), validated (check referential integrity, enforce business rules), and reshaped (denormalize for analytics, compute derived columns, aggregate). Modern ETL often uses a staging area — data is loaded raw into a staging table, then transformed via SQL (the "ELT" pattern), leveraging the warehouse's compute engine rather than an external processing framework.

The load stage writes data to the target. Bulk inserts are fastest but can leave partial data on failure. Staging tables with atomic swaps (load into a temp table, then rename) provide atomicity. Upsert logic (INSERT ON CONFLICT UPDATE) handles idempotency for incremental loads. The orchestrator (Airflow, Dagster) manages the DAG of tasks, retries failed steps, sends alerts, and tracks data lineage. Idempotency is critical — every pipeline step must be safe to re-run without duplicating data, typically achieved through merge/upsert semantics and checkpoint tracking.

How Companies Actually Do This

Airbnb Built Apache Airflow (now an Apache top-level project) to orchestrate thousands of ETL pipelines that transform booking, pricing, and host data into analytics tables powering their data-driven pricing and search ranking models.

Spotify Runs massive ETL pipelines to transform streaming event logs (billions per day) into analytics tables that power Wrapped, artist dashboards, and royalty calculations — using Luigi (their open-source orchestrator) and Google Cloud Dataflow.

Stripe ETL pipelines consolidate payment data across millions of merchants into analytics tables that power Stripe Radar (fraud detection), revenue dashboards, and regulatory reporting across dozens of countries.

Common Pitfalls

1. Not making pipelines idempotent — if a pipeline fails mid-load and you re-run it, non-idempotent inserts create duplicate records; always use upserts or staging tables with atomic swaps
2. Ignoring schema evolution — when a source system adds or removes columns, rigid ETL pipelines break; design for schema changes with flexible extraction and explicit column mapping
3. Building one giant pipeline instead of modular stages — a monolithic extract-transform-load script is impossible to debug, test, or partially re-run; separate each stage into independently testable and retriable tasks

Interview Questions Worth Practicing

1. How would you design an ETL pipeline that consolidates data from 10 different source systems into a single data warehouse with daily freshness?
2. Explain the difference between ETL and ELT. When would you choose one over the other?
3. How do you handle pipeline failures and ensure data quality when an ETL job processes 100 million records?

The Tradeoffs

• Freshness vs Complexity: Batch ETL runs hourly or daily with simple orchestration; real-time streaming provides fresher data but requires more complex infrastructure and error handling
• ETL vs ELT: ETL transforms before loading (reduces storage, ensures clean data), while ELT loads raw data first (simpler extraction, leverages warehouse compute, retains raw data for reprocessing)
• Full vs Incremental Extraction: Full extraction is simple and correct but slow and wasteful for large tables; incremental extraction is efficient but requires change tracking and handles deletes poorly

How to Explain This in an Interview

Here is how I would explain ETL Pipelines in a system design interview:

Start by defining the three stages: Extract pulls data from source systems (databases, APIs, files), Transform cleans and reshapes it (deduplication, type casting, business rules), and Load writes it to a destination like a data warehouse. Emphasize the distinction between ETL and ELT: in ETL, you transform externally before loading; in ELT, you load raw data into the warehouse and transform it there using SQL — ELT is increasingly popular because modern warehouses like Snowflake and BigQuery have powerful compute engines. Discuss orchestration: tools like Apache Airflow define DAGs of tasks with dependencies, schedules, and retries. Highlight idempotency as the most critical design principle — every step must be safe to re-run without duplicating data, achieved through upserts, staging tables, and checkpoint tracking. For data quality, mention validation gates that halt the pipeline if row counts, null rates, or schema checks fail.

Related Topics

• Data Pipelines
• Data Warehouses
• Data Lakes
• Database Scaling

Original DosiBridge notes for ETL Pipelines: Batch Processing, Transformation and Data Loading

This section is written specifically for this page, not copied from a general system-design checklist. The useful way to study ETL Pipelines: Batch Processing, Transformation and Data Loading is to connect the concept to a production service where growth exposes the first weak assumption in the original design. That framing changes the design discussion from definitions to operating choices.

What makes this topic distinct

• Query Shape: For ETL Pipelines: Batch Processing, Transformation and Data Loading, decide the query shape before choosing tools; otherwise the design looks clean but fails when etl changes.
• Write Amplification: For ETL Pipelines: Batch Processing, Transformation and Data Loading, decide the write amplification before choosing tools; otherwise the design looks clean but fails when pipelines changes.
• Replication Lag: For ETL Pipelines: Batch Processing, Transformation and Data Loading, decide the replication lag before choosing tools; otherwise the design looks clean but fails when etl changes.
• Migration Safety: For ETL Pipelines: Batch Processing, Transformation and Data Loading, decide the migration safety before choosing tools; otherwise the design looks clean but fails when pipelines changes.
• Data Ownership: For ETL Pipelines: Batch Processing, Transformation and Data Loading, decide the data ownership before choosing tools; otherwise the design looks clean but fails when etl changes.

Production review question

If a team adopts ETL Pipelines: Batch Processing, Transformation and Data Loading, what observable signal proves it helped instead of only making the architecture diagram more fashionable?

Practical metric to track

Track etl-pipelines health as a named dashboard or review item: combine one user-facing metric, one saturation metric, and one correctness metric. For ETL Pipelines: Batch Processing, Transformation and Data Loading, this keeps the discussion grounded in behavior that can be measured after deployment instead of generic advice that sounds good in an interview but never gets verified.