Data Pipeline Architecture Patterns

SLIDE 9: Section Intro, Data Pipeline Architecture Patterns

Section 2: Data Pipeline Architecture Patterns

Every data engineering interview question is, at its core, a pipeline design question. The interviewer gives you a source of data, a destination, and a set of requirements, and asks you to design the system that moves data from one to the other with appropriate transformations along the way. This section teaches the foundational patterns that govern how production data pipelines are designed, and gives you the vocabulary and reasoning framework to answer any pipeline design question with clarity.

Recognition Signal:

When the interviewer asks you to move data from point A to point B with transformations along the way, you are answering a pipeline architecture question. This includes prompts like “Design the data pipeline for X,” “How would you ingest data from Y into your warehouse,” or “Walk me through how data flows from the application database to the analytics dashboard.”

What We’ll Cover:

1. ETL vs ELT: the foundational architectural decision and why the industry shifted
2. Pipeline anatomy: the five stages every pipeline shares
3. Source types and ingestion patterns for each
4. Full load vs incremental load, and change detection strategies
5. Idempotency: why pipelines must be safely re-runnable
6. Backfill patterns for reprocessing historical data
7. Reliability patterns: retries, dead-letter queues, checkpointing, and alerting

Where This Appears in Real Systems:

SLIDE 10: ETL vs ELT: The Foundational Decision

The first architectural decision in any data pipeline is where transformation happens relative to loading. This decision shapes everything downstream: compute costs, schema flexibility, latency, and how easily you can adapt when business logic changes.

ETL: Extract, Transform, Load

Data is extracted from the source, transformed on a dedicated processing server (or staging area), and then loaded into the target system in its final form. The target system only ever sees clean, transformed data.

ELT: Extract, Load, Transform

Data is extracted from the source and loaded into the target system in its raw form. Transformations happen inside the target system, using the target’s own compute resources.

Comparison:

Interview Tip:

When asked “Would you use ETL or ELT?” never answer with just one. Explain the trade-off: “For this use case, I’d use ELT because we want to preserve raw data for reprocessing and the cloud warehouse can handle transformations at scale. If we had strict compliance requirements that demanded data be cleaned before storage, ETL would be more appropriate.” Trade-off reasoning is what separates senior-level answers from junior ones.

SLIDE 11: Why the Industry Shifted from ETL to ELT

For decades, ETL was the default pattern. The shift to ELT over the last 10-15 years was driven by three fundamental changes in infrastructure economics and architecture.

Change 1: Cloud Storage Became Extremely Cheap

In the on-premise era, storage was expensive, so teams transformed data before loading to minimize what they stored. Cloud object storage (S3, GCS, ADLS) changed this equation dramatically.

Storage Cost Comparison:
  On-premise SAN (2010):         $1.00 - $5.00 / GB / month
  AWS S3 Standard (2024):        $0.023 / GB / month
  AWS S3 Glacier (2024):         $0.004 / GB / month

  Cost to store 1 TB of raw data for 1 year:
    On-premise:   $12,000 - $60,000
    S3 Standard:  $276
    S3 Glacier:   $48

When storage is $0.023/GB/month, the correct strategy is to store everything raw and transform on demand. Throwing away raw data to save storage cost no longer makes economic sense.

Change 2: Separation of Storage and Compute

Cloud warehouses (Snowflake, BigQuery, Redshift Serverless) and lakehouse engines (Databricks, Trino) separate storage from compute. This means you can scale transformation independently from storage. You do not need a permanently running staging server for transformations. Instead, you spin up compute when transformations need to run and shut it down when they finish.

Change 3: Schema Evolution Became a Core Requirement

Business requirements change constantly. With ETL, changing the transformation logic means rebuilding the entire pipeline. With ELT, the raw data is always preserved in storage. When business logic changes, you simply write a new transformation query against the same raw data. No re-extraction needed.

Concrete Example:

A retail company discovers their revenue calculation has been excluding a discount type for the last 6 months.

• ETL recovery: If raw data was discarded after transformation, they need to re-extract 6 months of data from the source database (if it even still exists), re-transform, and re-load. This could take days and may be impossible if the source has purged old records.
• ELT recovery: Raw data is still in the data lake. Write a corrected transformation query, backfill the last 6 months of aggregated tables in a few hours. The source database is never touched.

Key Takeaway:

ELT is the default pattern for modern cloud-native data pipelines. ETL still makes sense in specific cases: when raw data contains sensitive information that must be redacted before storage, when transformation must happen at the edge before network transfer, or when the target system cannot perform transformations efficiently.

SLIDE 12: Pipeline Anatomy: The Five Stages

In Section 1, we introduced the five-stage mental model (Ingest, Store, Transform, Serve, Monitor). This slide goes deeper into each stage, focusing on the design decisions and trade-offs that interviewers expect you to reason about.

Stage 1: Ingest

Move data from source systems into your pipeline. This is where you decide how data enters the system.

Stage 2: Store

Land data in durable, organized storage. This is where you decide the storage layer and data organization.

Stage 3: Transform

Clean, validate, enrich, aggregate, and reshape data for downstream consumption.

Stage 4: Serve

Make processed data available to its consumers.

Stage 5: Monitor

Track pipeline health, data quality, and SLA compliance. This stage spans all other stages.

SLIDE 13: Source Types and Ingestion Patterns

Data pipelines ingest from a variety of source types, and each source has a natural ingestion pattern that fits best. Choosing the wrong pattern for a given source type leads to problems: missed data, excessive load on the source, or unnecessary complexity.

Source Types and Their Natural Ingestion Patterns:

Ingestion Pattern Decision Guide:

Interview Tip:

When the interviewer specifies a source type, immediately name the appropriate ingestion pattern and explain why. “The product catalog is in PostgreSQL, so I’d use log-based CDC via Debezium to capture all changes, including deletes, with zero impact on application query performance. If CDC is not feasible due to infrastructure constraints, I’d fall back to incremental polling with a WHERE updated_at > last_run clause, but I’d flag that this approach misses hard deletes.” This shows you understand the pattern hierarchy and the trade-offs between approaches.

SLIDE 14: Full Load vs Incremental Load

One of the most common decisions in pipeline design is whether to extract all data every run (full load) or only the data that changed since the last run (incremental load). This decision has significant implications for cost, performance, and complexity.

Full Load:

Extract the entire dataset from the source on every pipeline run. Replace the target with a complete copy.

Incremental Load:

Extract only the rows that changed (new, updated, or deleted) since the last successful run. Merge changes into the existing target.

Comparison:

Worked Example: The Cost of Full Load at Scale

Consider a table with 500 million rows, each 1KB. Total table size: 500GB.

Full Load (daily):
  500GB extracted per run × 30 days = 15 TB/month transferred
  Source DB: full table scan every day (impacts production queries)
  Processing: 500GB processed daily even if only 0.1% changed
  Monthly compute cost: ~$2,000 (Spark cluster for 500GB daily)

Incremental Load (daily, 0.1% change rate):
  500MB extracted per run × 30 days = 15 GB/month transferred
  Source DB: index scan on updated_at column (minimal impact)
  Processing: 500MB processed daily
  Monthly compute cost: ~$50 (Spark cluster for 500MB daily)

  Cost reduction: ~97%
  Source DB load reduction: ~99%

At 500 million rows, full load is a non-starter for daily processing. Incremental load is the only viable approach.

Interview Tip:

Always start with the question: “What is the volume of this dataset and how frequently does it change?” If the dataset is small (dimension tables, configuration data, reference data), full load is perfectly fine and avoids unnecessary complexity. If the dataset is large and the change rate is low (common in production), incremental load is the clear choice. Show the interviewer you can size the problem before selecting the approach.

SLIDE 14B: Incremental Load: Change Detection Strategies

If you choose incremental load, you need a mechanism to detect which rows changed since the last run. There are four primary strategies, each with different completeness, complexity, and performance characteristics.

Strategy 1: Timestamp-Based

SELECT * FROM orders
WHERE updated_at > '2024-01-14 00:00:00'
  AND updated_at <= '2024-01-15 00:00:00'

Use an updated_at column maintained by the application. Extract rows where the timestamp is newer than the last successful run.

Strategy 2: Sequence-Based

SELECT * FROM events
WHERE event_id > 58293847

Use an auto-incrementing ID or sequence number. Extract rows with IDs higher than the last processed ID.

Strategy 3: CDC-Based (Log-Based)

Read the database transaction log to capture every change as a structured event.

Strategy 4: Hash-Based

Compute a hash of each row and compare with previously stored hashes to detect changes.

Comparison:

Decision Guide:

Common Misconception:

“Timestamp-based incremental extraction captures all changes.” It does not. If a row is deleted, the deleted row no longer exists in the source table, so no WHERE updated_at > X query will ever return it. If the application performs a bulk import that does not update the updated_at column, those rows will also be missed. CDC is the only strategy that captures every operation by reading the database’s own transaction log.

SLIDE 15: Idempotency in Data Pipelines

Pipelines fail. Networks drop. Spark executors crash mid-job. Airflow retries tasks that timed out but actually succeeded. In every one of these scenarios, a pipeline stage may run more than once for the same input. If the pipeline is not idempotent, reruns produce incorrect results: duplicate rows, inflated metrics, broken dashboards.

Definition:

A pipeline operation is idempotent if running it once produces the same result as running it multiple times with the same input. The output after the first successful run and the output after the third retry are identical.

Why Idempotency Matters:

Scenario: Daily aggregation pipeline for revenue reporting.

Pipeline runs at 2:00 AM. Spark job fails at 2:45 AM due to executor OOM.
Airflow retries at 3:00 AM. Spark job completes successfully at 3:30 AM.
Airflow retries again at 4:00 AM (timeout triggered before success was confirmed).

If the write operation is INSERT (non-idempotent):
  Run 1 (partial): 500K rows inserted before failure
  Run 2 (success): 1M rows inserted
  Run 3 (duplicate): 1M rows inserted again
  Result: 2.5M rows in the target. Revenue is 2.5x the actual value.

If the write operation is UPSERT (idempotent):
  Run 1 (partial): 500K rows upserted before failure
  Run 2 (success): 1M rows upserted (500K update existing, 500K insert new)
  Run 3 (duplicate): 1M rows upserted (all 1M update existing, no new rows)
  Result: 1M rows in the target. Revenue is correct.

Three Idempotent Write Patterns:

Pattern 1: Upsert (INSERT ON CONFLICT UPDATE)

INSERT INTO daily_revenue (date, category, total_revenue, order_count)
VALUES ('2024-01-15', 'Electronics', 1250000.00, 8420)
ON CONFLICT (date, category)
DO UPDATE SET
  total_revenue = EXCLUDED.total_revenue,
  order_count = EXCLUDED.order_count;

If the row exists (same date + category), update it. If it doesn’t exist, insert it. Running this three times produces the same result as running it once.

Pattern 2: Partition Overwrite

df.write \
  .mode("overwrite") \
  .partitionBy("date") \
  .parquet("s3://data-lake/daily_revenue/")

Overwrite the entire partition for the date being processed. If the partition for 2024-01-15 already exists from a previous run, it is replaced entirely. The result is always the same regardless of how many times the job runs.

Pattern 3: Staging Table Swap

CREATE TABLE daily_revenue_staging AS
SELECT date, category, SUM(amount) as total_revenue, COUNT(*) as order_count
FROM raw_orders
WHERE date = '2024-01-15'
GROUP BY date, category;

BEGIN TRANSACTION;
  DELETE FROM daily_revenue WHERE date = '2024-01-15';
  INSERT INTO daily_revenue SELECT * FROM daily_revenue_staging;
COMMIT;

DROP TABLE daily_revenue_staging;

Write results to a staging table, then atomically swap the relevant data in the target table within a single transaction. The staging table is rebuilt from scratch on every run, so reruns always produce correct output.

Key Insight:

Idempotency is not a nice-to-have. It is a fundamental requirement for any production pipeline. Every write operation in your pipeline should be idempotent by default. In interviews, mentioning idempotency proactively (before the interviewer asks “what happens if this fails?”) is one of the strongest signals of production experience.

SLIDE 15B: Idempotency: Wrong vs Right

This slide shows concrete examples of non-idempotent operations and their idempotent alternatives. In interviews, interviewers often present a pipeline sketch and ask “what happens if this step runs twice?” Being able to spot the non-idempotent operation and fix it is a core competency.

Non-Idempotent Patterns and Their Fixes:

The Golden Rule:

If you cannot answer the question “what happens if this step runs twice?” with “the result is the same as running it once,” the step is not idempotent and will cause data quality issues in production.

Interview Tip:

When presenting a pipeline design, explicitly state the idempotent write pattern for each stage. “The aggregation output uses partition overwrite, so reruns replace the previous output for that date. The warehouse load uses upsert on the composite key of (date, category, region), so retries do not create duplicates.” This single sentence demonstrates more production awareness than five minutes of talking about Spark configurations.

SLIDE 16: Backfill Patterns

Backfilling is the process of reprocessing historical data. It happens more often than most engineers expect, and pipelines that are not designed for backfill from day one create enormous operational pain when the need arises.

When Backfill Is Needed:

Pattern 1: Partition-Based Backfill

The most common and recommended approach. Each pipeline run processes a specific date partition independently. Backfilling means rerunning the pipeline for specific historical dates.

def process_daily_revenue(execution_date):
    raw_data = read_partition(f"s3://lake/raw/orders/date={execution_date}")
    aggregated = raw_data.groupBy("category").agg(sum("amount").alias("revenue"))
    aggregated.write.mode("overwrite").partitionBy("date").parquet(
        f"s3://lake/gold/daily_revenue/"
    )

# Normal daily run:
process_daily_revenue("2024-01-15")

# Backfill last 30 days:
for date in date_range("2023-12-16", "2024-01-15"):
    process_daily_revenue(date)

Each date partition is independent. Reprocessing one date does not affect any other date. This is why partition overwrite (not append) is critical: it makes each partition run idempotent.

Pattern 2: Full Recompute

Rebuild the entire output dataset from raw data. Expensive but guarantees correctness.

Use when:
  - The transformation logic changed fundamentally (not just a bug fix)
  - A new output table is being built for the first time
  - The raw data is small enough that full recompute is feasible

Avoid when:
  - The dataset is very large and only a small portion needs correction
  - The recompute would take days and block the pipeline from processing new data

Design Principles for Backfill-Ready Pipelines:

1. Preserve raw data. This is the single most important principle. If raw data is not preserved, backfilling requires re-extraction from the source, which may be impossible if the source has purged old records.
2. Parameterize by execution date. Every pipeline should accept an execution date parameter. The pipeline processes exactly one date’s worth of data, not “whatever is new since the last run.” This makes backfilling a simple loop over dates.
3. Use partition overwrite, not append. Partition overwrite is idempotent. Append creates duplicates on rerun. This is the single most common backfill-related mistake.
4. Separate extraction from transformation. If extraction and transformation are in the same job, backfilling means re-extracting from the source (expensive, may load the source database). If raw data is already in the lake, backfilling only reruns transformations against stored raw data.

Interview Tip:

When presenting a pipeline design, mention backfill proactively: “The pipeline is parameterized by date and uses partition overwrite, so backfilling any date range is as simple as rerunning the DAG for those dates. Raw data is preserved in the Bronze layer, so backfills never touch the source database.” This signals operational maturity that most candidates do not demonstrate.

SLIDE 17: Data Pipeline Reliability Patterns

Production pipelines process millions of records across distributed systems with many potential failure points: network timeouts, source system outages, corrupt records, disk failures, and out-of-memory errors. Reliability engineering ensures the pipeline recovers gracefully from these failures without human intervention for common cases, and alerts humans quickly for cases that require investigation.

Pattern 1: Retry with Exponential Backoff

When a transient failure occurs (network timeout, temporary rate limit, brief source outage), retry the operation with increasing delays between attempts.

retry_delays = [30, 60, 120, 300, 600]  # seconds: 30s, 1m, 2m, 5m, 10m

def extract_with_retry(source_url, max_retries=5):
    for attempt in range(max_retries):
        try:
            return fetch_data(source_url)
        except TransientError as e:
            if attempt == max_retries - 1:
                raise  # All retries exhausted, fail the task
            delay = retry_delays[attempt]
            log.warning(f"Attempt{attempt + 1} failed:{e}. Retrying in{delay}s.")
            time.sleep(delay)

In Airflow, this is configured per task:

extract_task = PythonOperator(
    task_id="extract_orders",
    python_callable=extract_orders,
    retries=3,
    retry_delay=timedelta(minutes=5),
    retry_exponential_backoff=True,
    max_retry_delay=timedelta(minutes=30),
)

Pattern 2: Dead-Letter Queue for Poison Records

When individual records fail validation or processing, route them to a separate location (dead-letter table or queue) rather than failing the entire pipeline or silently dropping them.

def process_events(events):
    good_records = []
    dead_letter_records = []

    for event in events:
        try:
            validated = validate_schema(event)
            enriched = enrich(validated)
            good_records.append(enriched)
        except ValidationError as e:
            dead_letter_records.append({
                "original_event": event,
                "error": str(e),
                "timestamp": datetime.utcnow(),
                "pipeline_run_id": current_run_id
            })

    write_to_target(good_records)
    write_to_dead_letter(dead_letter_records)

    dead_letter_rate = len(dead_letter_records) / len(events)
    if dead_letter_rate > 0.01:  # More than 1% failure
        alert("Dead letter rate exceeded threshold", dead_letter_rate)

The pipeline continues processing valid records. Bad records are quarantined for investigation. An alert fires if the dead-letter rate exceeds a threshold, indicating a systemic problem rather than isolated bad records.

Pattern 3: Checkpointing for Resumable Pipelines

For long-running jobs that process large volumes, save progress periodically so that a failure does not require restarting from the beginning.

def process_large_dataset(input_path, checkpoint_path):
    last_checkpoint = load_checkpoint(checkpoint_path)
    partitions = list_partitions(input_path)

    for partition in partitions:
        if partition <= last_checkpoint:
            continue  # Skip already processed partitions

        data = read_partition(partition)
        result = transform(data)
        write_output(result)
        save_checkpoint(checkpoint_path, partition)

If the job fails after processing 70 of 100 partitions, the retry starts at partition 71 instead of partition 1.

Pattern 4: Alerting on SLA Violations

Monitor pipeline execution against defined SLAs and alert when the pipeline is at risk of missing its deadline.

Key Insight:

These four patterns work together. Retries handle transient failures automatically. Dead-letter queues isolate bad records without blocking the pipeline. Checkpointing prevents wasted computation on retry. Alerting ensures humans are notified when automated recovery is not sufficient. A well-designed pipeline uses all four, and mentioning them together in an interview demonstrates a mature understanding of production reliability.

SLIDE 18: Worked Example: Design a Pipeline for 10M Daily User Events

This worked example walks through a complete pipeline design using the patterns from this section. It demonstrates how to structure an interview answer by applying the five-stage mental model and making explicit trade-off decisions at each stage.

Requirements:

• Mobile application with 5 million daily active users
• Each user generates an average of 2 events per day (screen views, button clicks, feature usage)
• Total volume: 10 million events per day, each approximately 500 bytes
• Consumers: (1) real-time user activity feed visible in a dashboard within 30 seconds, (2) daily aggregated engagement metrics for product analytics

Step 1: Size the Problem

Daily volume:     10M events × 500 bytes = 5 GB/day
Monthly volume:   5 GB × 30 = 150 GB/month
Annual volume:    5 GB × 365 = 1.825 TB/year
Peak throughput:  Assume 4x peak-to-average ratio
                  10M events / 86,400 seconds ≈ 116 events/second average
                  116 × 4 = ~460 events/second peak

Storage cost (S3): 150 GB/month × $0.023/GB = $3.45/month (negligible)
Annual storage:    1.825 TB × $0.023/GB × 12 ≈ $504/year for raw data

At 5 GB/day and 460 events/second peak, this is a moderate-scale pipeline. No exotic infrastructure needed.

Step 2: Ingest

The source is a mobile application generating events. Events are sent to a backend API, which publishes them to Kafka.

Why Kafka? Two consumers need the same data (real-time dashboard and daily batch). Kafka’s consumer group model allows multiple independent consumers to read the same stream. Partitioning by user_id ensures all events for a given user go to the same partition, preserving per-user ordering.

Why 12 partitions? At 460 events/second peak, a single Kafka partition can easily handle this throughput (a single partition handles 10K+ messages/second). Twelve partitions provide room for future growth and allow up to 12 parallel consumers.

Step 3: Store

Raw events land in a data lake (S3) in Parquet format, partitioned by date and hour.

s3://data-lake/bronze/user_events/
  date=2024-01-15/
    hour=00/
      part-00000.parquet
      part-00001.parquet
    hour=01/
      ...

Why Parquet? Columnar format compresses well (500 bytes/event raw, roughly 100-150 bytes after Parquet compression). Analytics queries that select specific columns (event_type, user_id, timestamp) benefit from columnar reads.

Why partition by date and hour? Date enables partition pruning for daily batch jobs. Hour enables finer-grained reprocessing if a specific hour has a data quality issue.

Step 4: Transform (Two Paths)

Path A: Real-Time (for the 30-second dashboard)

A stream processing consumer reads from the Kafka topic, extracts the fields needed for the activity feed (user_id, event_type, timestamp), and writes to Redis with a TTL of 24 hours. The dashboard reads from Redis.

Path B: Daily Batch (for product analytics)

A daily Spark job reads the previous day’s raw events from S3, aggregates by event_type, user_segment, and date, and writes the results to the Gold layer. The Gold layer is loaded into Snowflake for analyst queries.

daily_metrics = spark.read.parquet(f"s3://data-lake/bronze/user_events/date={execution_date}") \
    .groupBy("event_type", "user_segment") \
    .agg(
        count("*").alias("event_count"),
        countDistinct("user_id").alias("unique_users")
    )

daily_metrics.write.mode("overwrite").partitionBy("date").parquet(
    "s3://data-lake/gold/daily_engagement/"
)

Step 5: Monitor

Architecture Summary:

Why Hybrid (Streaming + Batch)?

The dashboard needs 30-second freshness, which requires streaming. The analytics metrics need daily aggregation, which is naturally batch. Using streaming for both would work but would add unnecessary complexity and cost for the daily aggregation. Using batch for both would violate the 30-second freshness requirement. Hybrid gives each consumer the processing model that matches its latency requirement.

SLIDE 19: Interview Practice and Common Mistakes

Common Interview Mistakes in Pipeline Architecture:

Practice Questions:

Use these questions to practice applying the patterns from this section:

1. “Design a pipeline that ingests product catalog changes from a PostgreSQL database into a search index (Elasticsearch). Changes must reflect within 60 seconds.” (Key patterns: CDC, streaming ingestion, idempotent writes)
2. “A third-party vendor sends daily CSV files to an SFTP server with 2M rows of transaction data. Design the ingestion pipeline.” (Key patterns: file-based ingestion, full vs incremental, schema validation, dead-letter for bad rows)
3. “Your daily revenue pipeline is producing inflated numbers after a weekend deployment. What do you investigate, and how do you fix it?” (Key patterns: idempotency failure, backfill, monitoring)

SLIDE 20: Section Cheat Sheet

Concepts Covered:

Common Interview Mistakes:

Key Numbers to Remember:

Data Volume Quick Math:
  1M events/day × 1KB each  = 1 GB/day = 365 GB/year
  10M events/day × 500B each = 5 GB/day = 1.8 TB/year
  1B events/day × 1KB each   = 1 TB/day = 365 TB/year

Storage Costs (S3):
  S3 Standard:  $0.023/GB/month = $23/TB/month
  S3 Glacier:   $0.004/GB/month = $4/TB/month

Throughput Conversions:
  1M events/day    ≈ 12 events/second
  10M events/day   ≈ 116 events/second
  100M events/day  ≈ 1,157 events/second
  1B events/day    ≈ 11,574 events/second

Kafka Benchmarks:
  Single partition:  10K+ messages/second
  Single broker:     100K+ messages/second
  3-broker cluster:  300K+ messages/second

How This Connects to Other Sections: