End-To-End ETL Pipeline Case Design

What's being tested

Interviewers are probing your ability to design a reliable, performant end-to-end ETL flow that meets functional SLAs while exposing clear tradeoffs (latency, cost, correctness). They want to see system decomposition, capacity math, failure modes and recovery strategies, and pragmatic operator/automation choices a Software Engineer would own. Expect questions that probe API contracts, batching vs streaming choices, backpressure and idempotency, and how you would prove correctness to stakeholders.

Core knowledge

Architecture decomposition: separate ingestion, buffering, transformation, storage, and serving layers; draw clear synchronous vs asynchronous boundaries and who owns retries and durability at each boundary.
Batch vs streaming tradeoff: batch suits high-throughput, relaxed-latency (<hours) workloads; streaming (micro-batching or event-driven) fits low-latency (seconds–minutes) needs; complexity and cost increase with lower latency.
Throughput and sizing math: compute bandwidth as events/sec × avg_event_size; compute CPU needs: processing_seconds = (events/sec × work_per_event)/cores; add 30–50% headroom. Example: 100k events/s × 1KB ≈ 100 MB/s.
Latency model: total latency ≈ ingestion_delay + queue_wait + transform_time + write_time; optimize the dominant term and quantify p50/p95/p99 goals. Use pipeline-stage SLAs to reason about bottlenecks.
Durability and guarantees: choose between at-most-once, at-least-once, and exactly-once semantics; prefer idempotency and deduplication tokens in the service layer rather than relying on complex exactly-once plumbing.
Idempotency patterns: use client-generated idempotency keys or dedupe via deterministic keys (hash of event), and store last-seen id/timestamp in a compact lookup (e.g., LRU cache + persistent store) to bound state size.
Backpressure and flow control: implement client-side throttling, windowed batching, and queue-length-based throttling; expose Retry-After or 429 and exponential backoff on upstream APIs.
Failure & retry design: separate transient vs permanent errors; retries with exponential backoff and jitter; sidecar dead-letter queue for poison messages and human review; automated backfills for transient gaps.
Schema evolution & contracts: treat schemas as part of API contract; use tolerant readers (ignore unknown fields), versioning, and explicit migration windows; validate and reject incompatible changes at ingestion.
Observability & SLOs: instrument p50/p95/p99 latencies, throughput, error rates, and pipeline lag; expose lineage/sequence IDs for reconciliation; alert on schema-change and DW-load failures.
Data correctness checks: implement row counts, checksums, and reconciliation jobs (e.g., compare counts per hour) with thresholds and automated rollback triggers.
Storage/format choices: pick columnar (Parquet/ORC) for analytics+cost efficiency; use newline-delimited JSON or Avro for event streams where schema registry is required; compress and partition by time key for efficient scans.
Operational automation: provide safe backfill workflow with idempotent transforms, staged rollouts, canary batches, and feature flags to disable expensive transforms.

Worked example

Design a pipeline to ingest 100k events/second from mobile clients into a near-real-time analytics store with <=5-minute freshness. First 30s: clarify SLAs (per-event vs eventual, allowed duplicates), peak vs sustained load, maximum event size, and retention/compliance constraints. Skeleton: (1) lightweight HTTP/gRPC ingestion tier that validates minimal schema and emits into a durable buffer (Kafka/cloud pubsub), (2) stream processor (micro-batch/beam/Flink) that enriches and applies business transforms, (3) sink writes to partitioned analytics storage (Parquet on S3 or BigQuery), (4) monitoring + dead-letter pipeline. Flag a key tradeoff: exactly-once semantics via two-phase commit adds latency and complexity; prefer at-least-once with deterministic idempotent transforms and downstream dedupe for predictable behavior. Close by stating next steps: capacity test with synthetic load, implement schema validations and reconciliations, and build automated backfill tooling for reprocessing historical windows.

A second angle

Consider a nightly batch ETL for regulatory reports where completeness and reproducibility matter more than latency. The same decomposition applies, but you can simplify ingestion (bulk file drops via S3), perform heavyweight joins and deterministic sorting in a single compute job, and rely on snapshotting for reproducibility. Emphasize reproducible transforms (record input file checksums, transform code versioning) and deterministic ordering so reruns produce identical outputs. Here, cost-efficiency and re-runability trump microsecond latencies, so materialized intermediate state and stronger transactional semantics are acceptable.

Common pitfalls

Pitfall: Designing for average load only.
Many engineers size components for average throughput and are surprised by traffic spikes; always provision or autoscale for p95/p99 peaks and include graceful degradation (sampling, prioritization) modes.

Pitfall: Treating deduplication as an afterthought.
Assuming the streaming system provides perfect exactly-once guarantees leads to expensive surprises; implement idempotent consumers and explicit dedupe keys as the invariant.

Pitfall: Overengineering correctness early.
Building distributed ACID guarantees across every stage adds months of complexity; prioritize pragmatic correctness (idempotency, reconciliations, DLQs) and iterate to stronger guarantees only when needed.

Connections

Interviewers may pivot to distributed systems topics (consensus, leader election, partition tolerance) or to observability (tracing, distributed logs) as follow-ups. They may also ask about adjacent infra owned by Data Engineers (schema registries, partitioning) to assess your cross-team integration approach.