How ETL pipelines move data at scale

The Problem Statement

Interviewer: "Your company has 15 different data sources: a PostgreSQL transactional database, three third-party APIs, an event stream from Kafka, and several CSV file drops from partners. The analytics team needs all of this data unified in a warehouse, updated every hour. How do you build the pipeline that moves and transforms this data?"

This question tests four things: your understanding of data extraction patterns (CDC, polling, file ingestion), your knowledge of transformation strategies (cleaning, deduplication, enrichment), your ability to choose between batch and streaming architectures, and whether you can design for failure recovery and idempotency.

Most candidates describe a vague "pull data, clean it, load it" flow. Strong candidates talk about exactly how extraction works for each source type, how they guarantee exactly-once semantics, and what happens when a pipeline run fails halfway through.

Clarifying the Scenario

You: "Before I design this, I want to understand the constraints."

You: "When you say updated every hour, is that a hard SLA? Could some data sources have near-real-time requirements while others are fine with daily batches?"

Interviewer: "The transactional database and event stream should be near-real-time. The CSV file drops and API pulls can be hourly."

You: "Got it. And what is the target warehouse? Something like BigQuery, Snowflake, or Redshift?"

Interviewer: "Snowflake."

You: "One more: how large is the transactional database? Are we talking gigabytes or terabytes of daily change volume?"

Interviewer: "About 50GB of changes per day across all tables."

You: "OK. I will structure my answer in four parts. First, how I extract data from each source type. Second, how I transform and clean it. Third, how I load it into Snowflake efficiently. And fourth, how I orchestrate and monitor the whole thing."

My Approach

I break ETL pipelines into four layers:

1. Extract: Getting data out of source systems without killing their performance. CDC for databases, API polling for third-party services, file watchers for CSV drops, and direct consumption for event streams.
2. Transform: Cleaning, deduplicating, enriching, and denormalizing raw data into analytics-ready schemas. This is where most of the complexity lives.
3. Load: Getting transformed data into the warehouse efficiently using bulk inserts, upserts, and slowly changing dimension patterns.
4. Orchestrate: Scheduling, dependency management, retries, monitoring, and alerting across all pipeline stages.

The mental model I use: ETL is like a factory assembly line. Raw materials (source data) arrive at different loading docks (extractors). Workers on the line clean, reshape, and combine parts (transformers). The finished products roll off to the warehouse shelves (loaders). The factory manager (orchestrator) makes sure each station starts when its inputs are ready and sounds the alarm when something jams.

The Architecture

Here is how the pipeline flows:

1. Each source type has a dedicated extractor tuned for its characteristics. The PostgreSQL CDC reads the write-ahead log, so it captures every insert, update, and delete without querying the production database. The API poller uses incremental cursors to fetch only new records. Kafka consumers track offsets for exactly-once processing.

2. All extractors write raw data to a staging layer in Parquet format on S3. This is the "land it first, transform it later" philosophy. Raw data is partitioned by date and source, making it easy to reprocess or backfill.

3. The transform layer reads raw partitions, applies cleaning and enrichment logic, and writes analytics-ready tables. Data quality checks run between transform and load to catch problems before they pollute the warehouse.

4. Airflow orchestrates everything: scheduling extraction jobs, triggering transforms when raw data arrives, managing dependencies between tables, and alerting on failures.

Extraction: Getting Data Out Without Breaking Things

This is the first hard problem. Each source type requires a fundamentally different extraction strategy, and getting extraction wrong means everything downstream is garbage.

Change Data Capture for databases

For the PostgreSQL transactional database, I use Debezium connected to the write-ahead log (WAL). This is the gold standard for database extraction.

Why CDC over querying the database directly? Three reasons:

1. Zero query load on production. Debezium reads the replication stream, the same mechanism PostgreSQL uses for replicas. The production database does not execute a single extra query.
2. Captures deletes. A SELECT * WHERE updated_at > last_run query misses deleted rows entirely. CDC captures DELETE events.
3. Captures intermediate states. If a row is updated three times between polls, a query-based approach only sees the final state. CDC captures all three changes.

API polling with incremental cursors

For third-party APIs, I cannot use CDC. I have to poll. The key is making this incremental rather than full-refresh.

I store a cursor (usually a timestamp or an auto-incrementing ID) from the last successful pull. Each poll fetches only records newer than the cursor. After a successful batch, I persist the new cursor.

The tricky part is rate limiting. If the API allows 100 requests per minute and I need to paginate through 50 pages, that takes 30 seconds. I build rate-limit awareness into the poller: it reads the X-RateLimit-Remaining header and backs off before hitting the limit.

File drop ingestion

For CSV file drops from partners, I use S3 event notifications. When a new file lands in the S3 landing zone, an event triggers a Lambda (or an Airflow sensor) that validates the schema, converts to Parquet, and moves the file to the raw layer. Invalid files are quarantined to a dead-letter zone with an alert.

Transformation: Where the Real Complexity Lives

Raw data is messy. The transform layer is where I clean it, deduplicate it, enrich it, and reshape it into analytics-ready tables.

I prefer the ELT pattern (Extract-Load-Transform) over traditional ETL for most modern warehouses. Load the raw data into a staging area first, then transform it using SQL inside the warehouse (using dbt) or with Spark for heavier processing.

Step 1: Deduplication

CDC produces one event per change, so if a row is updated five times, I have five events. I need to keep only the latest state per primary key.

The pattern is simple: group by primary key, order by event timestamp descending, take the first row. In Spark:

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, col

window = Window.partitionBy("order_id").orderBy(col("event_ts").desc())
deduped = (raw_df
    .withColumn("rn", row_number().over(window))
    .filter("rn = 1")
    .drop("rn"))

For late-arriving data (events that arrive out of order), I keep a lookback window. Instead of processing only the latest partition, I reprocess the last 3 hours to handle events that arrived late.

Step 2: Cleaning

Cleaning is boring but critical. I handle:

• Null values: Replace with defaults or flag as missing. Never silently drop rows with nulls.
• Type casting: Partner CSVs send everything as strings. Cast to proper types with explicit error handling.
• Date normalization: Sources send dates in different formats and timezones. Normalize everything to UTC ISO-8601.
• String trimming: Remove leading/trailing whitespace, normalize unicode characters.

Step 3: Enrichment

Enrichment joins raw data with reference data to add context:

• Join orders with customer profiles to get customer segments
• Look up geographic data from IP addresses
• Convert currencies using daily exchange rate tables
• Add fiscal calendar columns (quarter, fiscal year)

I always do enrichment as a separate step from cleaning, so I can rerun enrichment independently when reference data updates.

Step 4: Denormalization

Analytics queries perform terribly on normalized schemas. I build a star schema with pre-joined fact tables and slowly changing dimension tables.

For the dimension tables (like dim_customers), I use SCD Type 2 to preserve history:

Data quality checks

I run data quality checks between transform and load. This is the quality gate that prevents bad data from reaching the warehouse.

I use Great Expectations (or a similar framework) to define expectations:

• Row count checks: Did the transform produce a reasonable number of rows? If orders dropped by 90%, something is wrong.
• Null percentage thresholds: The customer_id column should be less than 0.1% null. If it is 15%, the join failed.
• Value range validation: Order amounts should be between $0.01 and $1,000,000. Negative amounts mean a parsing error.
• Referential integrity: Every customer_id in the fact table should exist in the dimension table.

If any check fails, the pipeline halts and alerts the on-call engineer. The failed batch is quarantined, and the warehouse continues serving the previous good data.

Loading: Getting Data Into the Warehouse

The load step is simpler than extraction or transformation, but there are performance traps.

Bulk loading vs row-by-row

Never insert rows one at a time into a warehouse. Snowflake, BigQuery, and Redshift are columnar stores optimized for bulk operations. A COPY command loading a Parquet file runs 100x faster than individual INSERT statements.

The pattern: write transformed data to S3 as Parquet files, then use COPY INTO to load into Snowflake:

COPY INTO fact_orders
FROM @my_s3_stage/transformed/orders/
FILE_FORMAT = (TYPE = 'PARQUET')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Upsert pattern with MERGE

For incremental loads where rows might be new or updated, I use the SQL MERGE statement:

MERGE INTO fact_orders AS target
USING staging_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET
  status = source.status,
  amount = source.amount,
  updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT
  (order_id, customer_id, status, amount, created_at, updated_at)
VALUES
  (source.order_id, source.customer_id, source.status,
   source.amount, source.created_at, source.updated_at);

This is idempotent: running the same MERGE twice with the same data produces the same result. That is critical for retry safety.

Idempotent processing

Every stage of the pipeline must be idempotent: running it twice with the same input produces the same output. This is non-negotiable for retry safety and backfill operations.

How I guarantee idempotency at each stage:

• Extract: Use checkpointing (offsets for Kafka, LSN for CDC, cursor positions for APIs). Restarting from the last checkpoint replays the same data.
• Transform: Process by partition (e.g., date hour). A re-run overwrites the output partition entirely rather than appending.
• Load: Use MERGE (upsert) instead of INSERT. Or use partition-swap: drop and reload an entire partition atomically.

Orchestration: Airflow and DAG Management

The orchestrator is the central nervous system of the pipeline. I use Airflow because it handles dependency management, scheduling, retries, and monitoring in one tool.

DAG design

Each pipeline is an Airflow DAG. The DAG defines which tasks run in which order, what depends on what, and what happens when something fails.

Key principles:

1. Fan out at extraction, fan in at aggregation. Extract jobs run in parallel because they are independent. Aggregation waits for all upstream loads to complete.
2. Quality checks are blocking tasks. If the quality check fails, the downstream load task never runs.
3. Retries with exponential backoff. API extractions retry 3 times with 5-minute, 15-minute, 45-minute delays. Database extractions retry once (if the WAL read failed, retrying immediately usually works).

Backfill strategy

When I deploy a new transformation or fix a bug, I need to reprocess historical data. This is a backfill.

The approach: parameterize every task by a logical date (Airflow's execution_date). To backfill, I run the DAG for past dates using Airflow's backfill command:

airflow dags backfill etl_pipeline \
  --start-date 2026-01-01 \
  --end-date 2026-04-01 \
  --reset-dagruns

Because every task is idempotent and partitioned by date, backfilling overwrites old partitions with freshly computed data. Running jobs are not affected because they process different date partitions.

The Tricky Parts

1. Schema evolution breaks everything. A source database adds a column, renames a field, or changes a type. If the transform logic is not flexible, the entire pipeline fails. I handle this by using schema registries (for Kafka/Avro sources) and dbt's source freshness checks that alert when schemas drift.

2. Late-arriving data is inevitable. Events arrive out of order due to network delays, partner file delays, or batch processing windows. I never assume that processing today's partition means all of today's data has arrived. I reprocess a lookback window (typically 3 hours for streaming sources, 2 days for batch sources).

3. The "it works on my machine" problem with Spark. A transformation that works on a 1GB sample fails at 500GB because of data skew. One customer has 10 million orders while the average has 50. The join explodes on that partition. I monitor for skew and use salting techniques to redistribute hot keys.

4. Orchestrator as a single point of failure. If Airflow goes down, all pipelines stop. I run Airflow in HA mode with a PostgreSQL metadata database, multiple workers, and a heartbeat monitor that pages the on-call if the scheduler stops.

5. Time zone hell. Source systems use different timezones. The PostgreSQL database stores in UTC, the API returns Pacific time, and the CSV files use whatever the partner's local time is. I normalize to UTC at extraction time, before any transformation logic runs. Every downstream consumer works in UTC.

What Most People Get Wrong

How I Would Communicate This in an Interview

Here is how I would actually say this:

"I would build this ETL pipeline in four layers. For extraction, I would use Debezium CDC for the PostgreSQL database because it reads the write-ahead log without putting any query load on production. For the APIs, I would use an incremental polling approach with stored cursors and rate-limit awareness. For Kafka, I would consume with exactly-once semantics using offset tracking. And for file drops, I would use S3 event notifications to trigger processing as files arrive.

All raw data lands in S3 in Parquet format, partitioned by source and date. This gives me a queryable data lake as a safety net before any transformation.

For transformation, I would use dbt for SQL-based transforms inside Snowflake and Spark for heavier processing like deduplication across large datasets. I would run data quality checks using Great Expectations between transform and load, blocking bad data from reaching the warehouse.

For loading, I would use MERGE statements for upserts and SCD Type 2 for slowly changing dimensions like customer profiles. Everything is idempotent, so retries and backfills are safe.

Airflow orchestrates the whole thing: scheduling, dependencies, retries with exponential backoff, and alerting to Slack and PagerDuty on failures."

Interview Cheat Sheet

• "How do you extract from a database?" leads to CDC with Debezium reading the WAL, zero query load on production, captures deletes and intermediate states.
• "What about API sources?" leads to incremental polling with stored cursors, rate limiting awareness, and pagination handling.
• "How do you handle data quality?" leads to Great Expectations with blocking checks between transform and load, null thresholds, row count validation, referential integrity.
• "What if the pipeline fails halfway?" leads to idempotent processing, partition-based reprocessing, and MERGE for safe retries.
• "Batch or streaming?" leads to "both": streaming CDC for the database and Kafka events, batch hourly polls for APIs and file drops, converging into the same raw layer.
• "How do you handle schema changes?" leads to schema registries for Avro sources, dbt source freshness checks, and alerting on schema drift before it breaks transforms.
• "What about backfill?" leads to date-parameterized tasks, Airflow backfill command, concurrency limits to avoid overwhelming sources.
• "ETL or ELT?" leads to ELT for modern cloud warehouses (load raw, transform inside the warehouse with dbt), traditional ETL only when the warehouse cannot handle the compute.
• "How do you orchestrate?" leads to Airflow DAGs with dependency management, fan-out at extraction, fan-in at aggregation, retry policies per task type.
• "How do you monitor?" leads to DAG execution time trends, data freshness SLAs, row count anomaly detection, and PagerDuty alerts for failed quality checks.

Test Your Understanding

Quick Recap

1. Use CDC (Debezium) for database extraction to avoid querying production and to capture deletes and intermediate states.
2. Land raw data in a staging layer (S3 in Parquet) before any transformation, giving you a safety net and backfill source.
3. Transform in stages: deduplicate, clean, enrich, denormalize, with each stage independently rerunnable.
4. Run automated data quality checks between transform and load that halt the pipeline on failure.
5. Use MERGE statements and SCD Type 2 patterns for idempotent, history-preserving loads.
6. Orchestrate with Airflow DAGs, parameterizing every task by date for safe backfill.
7. Design every stage to be idempotent: running the same stage twice with the same input produces the same output.
8. Monitor data freshness SLAs, row count anomalies, and extraction lag as first-class production metrics.

Related Concepts

• Kafka and event streaming connect directly to the extraction layer, serving as both a transport for CDC events and a data source in its own right.
• Data warehouse design (star schema, SCD) is the target schema design that the transform layer builds toward.
• Airflow and workflow orchestration is the control plane that schedules, retries, and monitors every stage of the pipeline.
• Idempotency patterns apply to every stage and become critical when retries and backfills are part of the normal operating procedure.
• Schema evolution and registry prevents the most common pipeline-breaking failure: upstream schema changes that silently corrupt downstream data.