How the interviewer opens it
“Design an engine that runs SQL queries across large datasets in several storage systems. Multiple users submit queries at the same time.”
The prompt is vague on purpose. The first minutes are for you. Reduce the prompt to the core features and agree on the numbers before you draw.
What to clarify first
- SQL support and read:write mix
- Storage systems
- Query workload
- Tenants and bursts
- Worker resources
- Throughput and latency
- Overload and cancellation
- Consistency and availability
- Durability and recovery
- Scope limits
The interviewer has the answers. In a SysRound round, Alex gives these answers, as a real interviewer does.
Scope of a strong answer
Core requirements
- Run read-only SQL across object storage and relational sources
- Execute joins and aggregates across workers
- Limit query memory and temporary disk use
- Share worker capacity across concurrent queries
Usually out of scope
- SQL writes and distributed transactions
- Data ingestion and storage system internals
- Full SQL compatibility and user-defined functions
- One consistent snapshot across all storage systems
What interviewers ask about
- Plans across storage systems
- Sources expose different statistics, SQL behavior, and throughput limits. A poor plan can transfer far more data than the result requires.
- Memory and uneven work
- Hash operators retain state while exchanges consume buffers. One frequent key can overload one worker even when the cluster has free capacity.
- Work after a failure
- A failed task can leave partial data in several consumers. A retry can count rows twice or read a different source snapshot.
- Overload and shared capacity
- Each query can fit alone but exceed shared capacity during a burst. Memory, source limits, and temporary disk need separate controls.
Common mistakes
- Treats the engine as a request router and omits distributed joins and aggregates.
- Chooses join distribution from row counts alone and ignores decoded memory size.
- Assumes more partitions remove every form of skew.
- Adds spill without disk quotas or enough memory for continued progress.
- Retries tasks without removing partial downstream results.
- Starts every query immediately and ignores shared source limits.
The bar by level
- Senior (L5/E5)
- Uses workload estimates to choose join distribution, memory grants, spill, and tenant limits. Handles skew and partial task output. Preserves source snapshots during retries. Separates cancellation acceptance from remote task termination.
- Staff (L6+/E6+)
- Defines correctness and resource rules across stages, attempts, and connectors. Explains progress under memory pressure and safe recovery boundaries. Chooses guarantees that source limits and worker isolation permit. Compares simple query restarts with more complex stage recovery.