ETL Pipeline Design & Orchestration Interview Questions (2026)

**Section 1 — The Context (The 'Why')** The primary challenge in choosing and justifying an architecture is alignment between technical complexity, operational cost, and business latency requirements. A naive monolithic pipeline fails when schema evolution hits, when a single component becomes a bottleneck, or when teams scale—causing coordination overhead and deployment risk. At scale, failure modes include cascade failures from tight coupling, data staleness exceeding SLAs, and cost explosion from over-provisioning. **Section 2 — The Diagram** ``` [Sources] --> [Bronze] --> [Silver] --> [Gold] | | | | v v v v [CDC/API] [Delta] [Merge] [Marts] ``` **Section 3 — Component Logic** The **Bronze layer** ingests raw data from CDC streams, APIs, and batch sources. It uses append-only Delta tables and serves as an immutable audit trail. We choose append because replay and debugging require exact reproduction of events—**idempotency** at downstream layers depends on deterministic ordering. The **Silver layer** applies schema enforcement, deduplication via merge-on-read, and type casting. **TTL policies** on Bronze enable cost control by moving cold data to cheaper storage after 90 days. The **Gold layer** produces business-level aggregates and star schemas. **Partitioning strategies** here use date and tenant keys to enable partition pruning. **Data skew mitigation** in Silver uses salting for high-cardinality keys during joins. **Section 4 — The Trade-offs (The 'Senior' part)** - **CAP Theorem**: We choose **AP** (Availability + Partition Tolerance) because for analytics pipelines, stale-by-minutes data is acceptable for dashboards and reporting, and we cannot afford downtime during partition rebalancing. Bronze append is eventually consistent; Silver/Gold MERGE uses Delta ACID for consistency within a single table. - **Cost vs. Performance**: S3 (~$0.023/GB) for the lake; Glue ($0.44/DPU-hr) for ETL; Athena ($5/TB) for ad-hoc. Partition pruning and columnar formats reduce scan cost ~10x. Glue wins for bursty <2hr jobs; EMR wins for sustained 8hr+ workloads, saving ~60%. - **Blast Radius**: If Bronze ingest fails: replay from source. Silver job fail: retry; merge is idempotent. Gold fail: downstream dashboards stale. Each layer is isolated. Self-heal: Airflow retries; Delta merge is deterministic. **Section 5 — Pro-Tip** **Pro-Move**: Explicitly discuss retention tiers (Bronze 90d hot, 1yr Glacier) and cost per tier. **Red Flag**: Describing architecture without 'why' or trade-offs. **Supplemental (Senior Context)**: In production, monitor partition skew, consumer lag, and merge duration. Use correlation IDs for traceability across pipeline stages. Schema evolution: prefer additive changes only; use Schema Registry for streaming to enforce compatibility. Consider data contract tests in CI to catch breaking changes early. Budget 10-20% overhead for replication, checkpoint storage, and DLQ. Data quality gates at each layer prevent bad data propagation. Right-size resources: profile before scaling; over-provisioning wastes budget. Document runbooks for common failures: broker restart, consumer rebalance, sink timeout. Establish SLOs per stage: ingest latency, transform duration, serve freshness. Review partition key choice: avoid high-cardinality keys that cause explosion; use composite keys (date, tenant) for balanced distribution. Test failure injection: kill executors, broker, sink to validate recovery. Optimize for the common case: most queries filter by date. Cold start mitigation: pre-warm connections, cache dimension lookups. Alert on lag exceeding 1hr, error rate above 1%. Cost optimization: lifecycle policies, spot instances, partition pruning. Lineage tracking enables impact analysis. Idempotency keys for replay. Backpressure handling prevents slow consumers from blocking producers. Fan-out patterns allow multiple consumers without re-processing. Exactly-once semantics require replayable source and idempotent sink. Data skew mitigation via salting for high-cardinality joins. Partitioning strategies must align with query patterns for pruning. CAP trade-off: AP for ingest and transform; CP for serve when BI needs accuracy. Blast radius bounded by partition and consumer group. Measure and iterate: latency percentiles, cost per record, error rate. Principal engineer tip: quantify before and after optimizations. Red flag: describing architecture without trade-offs. Glue versus EMR: Glue for bursty sub-2hr jobs; EMR for sustained 8hr+ saving 60%. MSK for Kafka; S3 for lake storage. Self-heal: orchestration retries; idempotent sinks ensure consistency. If primary fails, downstream goes stale but no data loss with replay. Design for operability: runbooks, dashboards, alerts. Avoid tight coupling between stages. Incremental processing reduces compute versus full refresh. Watermark-based deduplication enables idempotency. Partition evolution: add new partitions without rewriting. Retention policies balance cost and compliance. Test at scale: use production-size samples for validation. Always document trade-offs.