# Design an ETL platform: system design interview question

> Reliable data transfer across different systems: partial failures, incremental updates, and changes to source schemas.

Source: https://sysround.com/questions/design-etl-platform
Updated: 2026-11-05


Difficulty: hard. Levels: Senior, Staff+.

## How the interviewer opens it

"Design an ETL platform that extracts data from different sources, transforms it, and loads it into analytical storage. Users need to define and operate their data flows."

## What to clarify first

- Scale and traffic
- Sources
- Target capabilities
- Transformations and scope
- Schedule and latency
- Consistency and availability
- Durability and retention
- Initial copies and backfills
- Schema changes
- Source protection and tenant isolation

## Scope of a strong answer

Core requirements:

- Create and schedule data flows from supported sources to analytical tables.
- Apply SQL filters, projections, and type conversions.
- Transfer new, changed, and deleted records according to each source's capabilities.
- Recover failed work and handle changes to source schemas.

Usually out of scope:

- Build a new execution engine.
- Serve analytical queries.
- Execute arbitrary user code.
- Support joins or aggregates.
- Provide atomic updates across different sources.

## What interviewers probe

### Data completeness

Sources expose different update and deletion rules. The initial copy can overlap live changes, and source history has a limited lifetime.

### Partial failures and retries

The source, workers, platform metadata, and warehouse do not share one transaction. A retry can repeat work or overwrite newer data.

### Changes to source schemas

A source can change independently of a transformation or target table. Silent conversion can corrupt data while every run reports success.

## Common mistakes

- Uses one timestamp rule for every source.
- Treats a successful extract as a successful target update.
- Claims exactly-once behavior without a target commit protocol.
- Discards nonmatching updates instead of removing earlier matching target rows.
- Applies schema changes automatically without a compatibility policy.
- Builds a new execution engine instead of defining recovery and connector contracts.

## The bar by level

- **Senior:** Specifies snapshot boundaries, deletion transfer, filter transitions, and atomic target commits. Handles uncertain outcomes, backfills, source limits, and schema versions with concrete state transitions.
- **Staff+:** Defines connector guarantees and identifies unrecoverable history gaps. Specifies target fencing and rebuild publication. Sets tenant isolation rules and budgets recovery capacity against source limits.

Practice this question with an AI interviewer: https://sysround.com/practice/new?problem=etl-platform


---
SysRound (https://sysround.com) is an AI practice tool for system design interviews. It has a voice or typed AI interviewer and a shared whiteboard. At the end, you get feedback on each skill and a model answer. The AI feedback can be wrong.