ETL engineer system design interview questions

As asked

Design a system that ingests data from 50 different sources (REST APIs, Postgres databases, S3 files, Kafka topics) into a central Snowflake warehouse on a mix of real-time, hourly, and daily schedules. Cover the ingestion layer, orchestration, schema management, and observability.

Sample answer outline

Strong answers describe a layered architecture: a connector layer (Fivetran, Airbyte, or custom connectors) for lift-and-shift sources, Kafka Connect for change-data-capture, and custom Airflow/Dagster DAGs for complex REST sources. Data lands in a raw schema with append-only tables and ETL promotes to a staging then production schema. Schema changes are versioned in dbt. Observability uses dbt tests plus a freshness check dashboard. Candidates should discuss the make-vs-buy decision for off-the-shelf connectors.

Expect these follow-ups

• How do you handle a source that goes down for 12 hours? Does your system auto-recover or require manual intervention?

As asked

You need to backfill five years of transaction data into a new warehouse model while the daily pipeline keeps running. Walk me through the plan.

Sample answer outline

Separate the historical backfill from the incremental daily path, but make them use the same transformation logic where possible. Chunk by a natural partition such as transaction_date, run in bounded parallelism, and record per-partition status so failed chunks can be retried. Keep the target isolated until validation passes, then cut over views or downstream consumers. Compare row counts, sums, null rates, and sampled record-level values against the old source of truth. The common mistake is treating the backfill as a one-off script with no resume, validation, or cutover plan.

Expect these follow-ups

• How do you handle late-arriving records during the backfill window?
• What validation would convince finance to trust the new model?
• How do you avoid overwhelming the source database?

As asked

A customer needs your product connected to three internal systems: one REST API, one nightly CSV export, and one database replica they will not let you query directly. How would you design the integration?

Sample answer outline

The answer should normalise around contracts, ownership, and failure recovery rather than just moving data. For the REST API, define pagination, rate limits, retries, and idempotency. For CSV, use a landing bucket, schema validation, checksums, and clear handling for late or malformed files. For the restricted database, negotiate a supported export, change-data-capture feed, or customer-hosted connector rather than unsafe direct access. Strong candidates discuss audit logs, secrets handling, backfills, reconciliation, and how to make the integration observable for both teams.

Expect these follow-ups

• How do you handle a breaking schema change from the customer?
• What would you put in the integration runbook?
• Where do you draw the line between product work and customer-specific glue?

As asked

A product manager wants near-real-time dashboards with data no older than 5 minutes, but the transformation logic joins 4 tables and runs a complex aggregation. Walk me through how you decide between a true streaming approach, micro-batching with Spark Structured Streaming, and a 5-minute scheduled Airflow DAG.

Sample answer outline

A 5-minute Airflow DAG is simplest but adds up to 5 minutes of scheduling overhead on top of run time, so it might not meet the SLA. Spark Structured Streaming handles stateful joins across multiple sources but adds operational complexity. The key questions are: Can all 4 source tables be joined from a single stream or do some require lookups from a store? Is the aggregation stateful across events? Candidates should evaluate Flink or Kafka Streams if joins are complex, and always question whether 5 minutes is a true business requirement.

Expect these follow-ups

• What is the operational cost difference between running Spark Structured Streaming 24/7 versus a 5-minute Airflow DAG?

As asked

Your data team is growing to 15 engineers, all working on a single dbt project with 200 models, frequent merge conflicts, and slow CI runs. Design a project structure and workflow that scales to this team size.

Sample answer outline

Strong answers recommend a multi-project dbt approach or a monorepo with clear ownership boundaries (staging, mart, and reporting layers with team-specific subdirectories). Partial parsing and dbt-project.yml path exclusions speed up CI. Slim CI (dbt build --select state:modified+) only runs changed models and their dependents. Contracts enforce interfaces between teams. Candidates should also discuss model tags, documentation standards, and automated lineage visualization.

Expect these follow-ups

• How do you prevent one team's model from breaking another team's dependent model in the same project?

As asked

Design a data lake with Bronze, Silver, and Gold layers using Delta Lake on S3. Explain what transformations happen at each layer, how you enforce schema, and how you handle late-arriving data and deletes (GDPR).

Sample answer outline

Bronze is raw append-only landing; Silver is validated, deduplicated, and conformed data with schema enforcement via Delta constraints; Gold is business-ready aggregates. Delta Lake provides ACID transactions so late arrivals can be merged into Silver using MERGE INTO with a time window. GDPR deletes use Delta's DELETE statement and VACUUM to remove the Parquet files. Candidates should discuss the tradeoff between time travel retention and storage cost and how to partition tables to minimize VACUUM scope.

Expect these follow-ups

• How does Z-ordering in Delta Lake differ from traditional partitioning, and when would you use it?

As asked

Currently your ETL jobs run on fixed schedules, but they often run when no new data has arrived and miss data that arrives 10 minutes after the scheduled run. Design an event-driven system where pipelines trigger only when new source data is available.

Sample answer outline

Use S3 event notifications or SNS to trigger a Lambda or Airflow DAG run when a new file lands. For database sources, use a watermark table that the source system updates on each write, polled by a lightweight sensor task. For Kafka sources, Airflow ExternalTaskSensors or native Kafka triggers in Dagster can react to new messages. Candidates should discuss the idempotency requirement: triggering twice on the same data should not produce duplicates.

Expect these follow-ups

• How do you prevent a burst of 50 files landing simultaneously from triggering 50 parallel pipeline runs?

As asked

Your company needs both sub-minute latency dashboards for operations and historically accurate reports for finance that include late-arriving data up to 5 days old. Design an architecture that serves both use cases without maintaining two completely separate pipelines.

Sample answer outline

Lambda architecture splits into a speed layer (Kafka plus Spark Streaming or Flink writing to a serving store like Druid or ClickHouse for fast reads) and a batch layer (Airflow orchestrating Spark or dbt jobs writing to Snowflake for accurate historical data). A serving layer merges results. Candidates should compare this with the Kappa architecture (streaming only, using compacted Kafka topics as the replayable source of truth) and discuss why Kappa is often simpler for most teams.

Expect these follow-ups

• What are the operational challenges of keeping the speed layer and batch layer in sync, and how do you handle reprocessing if the batch layer finds errors?

As asked

You have an ETL pipeline reading from Kafka at 100k events per second and writing to Postgres. During a downstream Postgres slowdown, events pile up in memory and the consumer crashes with OOM. Design a solution that handles backpressure without losing data.

Sample answer outline

Stop consuming from Kafka when in-flight write buffers exceed a threshold and let Kafka retention absorb the lag (Kafka-native backpressure). The consumer tracks the committed offset separately from the produced offset. Use bounded write queues with blocking behavior to propagate back-pressure from the DB layer to the consumer thread. Alternatively, buffer to a disk-backed queue (local file or S3) as a circuit breaker. Candidates should discuss Kafka's retention period as a buffer and the risk of consumer lag exceeding retention.

Expect these follow-ups

• How do you alert on consumer lag in Kafka, and what threshold triggers a page?

As asked

You are building an ETL system for a SaaS product where each customer's data must be isolated, some customers need hourly refreshes while others need daily, and a large customer should not be able to starve smaller ones. How do you architect this?

Sample answer outline

Strong answers describe separate pipelines per tenant sharing a common DAG template with tenant_id as a parameter (Airflow DAG factory pattern), or a single parameterized DAG with per-tenant schedule configs stored in a control table. Compute isolation can use separate Airflow queues with worker limits per tenant tier. Data isolation uses per-tenant schemas or databases in the warehouse with RLS policies. A priority queue ensures SLA-bound customers preempt batch jobs.

Expect these follow-ups

• How do you onboard a new tenant without deploying new code?

As asked

A business-critical pipeline loads financial data used for end-of-day reporting. Design a disaster recovery plan that achieves an RPO of 1 hour and an RTO of 30 minutes if the primary Airflow environment fails.

Sample answer outline

A warm standby Airflow instance in a second region with DAG definitions synced via Git and a shared metadata database replica satisfies the RTO. RPO requires that pipeline state (last successful run, watermarks) is stored durably, not only in the primary Airflow DB. Candidates should discuss DNS failover or a load balancer health check to redirect traffic, and how to test failover quarterly without impacting production. They should also note that the warehouse itself (Snowflake, BigQuery) typically has its own regional replication.

Expect these follow-ups

• How do you ensure the standby system's DAGs are always in sync with production without manual intervention?

As asked

Your team runs 300 dbt models daily. The CFO asks you to reduce the Snowflake bill by 40% without degrading any user-facing SLAs. Design a scheduling and compute architecture that achieves this.

Sample answer outline

Group models by SLA tier: tier-1 must finish by 7 AM, tier-2 by 10 AM, tier-3 by end of day. Run tier-1 on the largest warehouse at 5 AM, then suspend and switch to a smaller warehouse for tier-2 and tier-3. Replace full table rebuilds with incremental models. Use dbt's defer feature in development so engineers test only changed models against production, not full rebuilds. Also audit result cache hit rate to avoid re-running identical queries.

Expect these follow-ups

• How do you handle a tier-3 model that a new team member accidentally makes a dependency for a tier-1 model?

As asked

Your company processes EU customer data on a Snowflake instance in us-east-1 due to legacy reasons, violating GDPR data residency. Design a migration plan to move EU data to an EU region instance while keeping pipelines running and minimizing downtime.

Sample answer outline

Strong answers describe a phased approach: first set up Snowflake database replication to an EU instance, then route new EU writes to the EU instance while the historical backfill completes, then cut over ETL pipelines to read from and write to the EU instance. During cutover, dual-write to both regions for a verification period. All pipeline connection strings are parameterized in a secrets manager so the cutover requires only a config change, not a code deploy. Candidates should also discuss audit logging to prove data residency compliance.

Expect these follow-ups

• How do you verify that no EU data remains in the US instance after the migration?