# Design a cloud data warehouse like Snowflake: system design interview question

> Analytical SQL over shared data: durable loads, parallel queries, and separate compute capacity for concurrent workloads.

Source: https://sysround.com/questions/design-cloud-data-warehouse-like-snowflake
Updated: 2026-11-05


Difficulty: hard. Levels: Senior, Staff+.

## How the interviewer opens it

"Design a cloud data warehouse where customers load large structured datasets and run analytical SQL. Different teams need to use the service at the same time."

## What to clarify first

- Data scale and retention
- Data loads and retries
- Request mix
- Query shape
- Latency
- Concurrent workloads
- Consistency and availability
- Durability and failures
- Compute lifecycle
- Scope limits

## Scope of a strong answer

Core requirements:

- Load structured data in batches.
- Run analytical SQL with filters, joins, and aggregates.
- Give concurrent workloads separate compute capacity.
- Resize or suspend compute without data loss.

Usually out of scope:

- Row updates and row transactions.
- Continuous data streams.
- Implement a complete SQL compiler.
- Operate across multiple regions.

## What interviewers probe

### Query results during data loads

Queries read many files while loads change table contents. Partial loads must remain invisible. Retries must follow the client's load identity.

### Concurrent workloads

Reports can exhaust memory and shared data access. Separate compute workers alone cannot protect dashboard latency.

### Large analytical queries

Scans, joins, and data skew require different execution plans. Worker failures can repeat work and corrupt results if the engine accepts duplicate output.

### Compute capacity changes

Worker removal destroys caches and intermediate data. The service must preserve durable data and define what happens to active queries.

## Common mistakes

- Tie durable table data to worker disks.
- Treat separate queues as complete workload isolation.
- Store every file reference in one catalog row.
- Expose files before the complete load commits.
- Repeat tasks without controlling duplicate output.
- Ignore the data bandwidth that the latency targets require.

## The bar by level

- **Senior:** Defines snapshot capture, retry-safe load commits, and worker failure behavior. Explains join plans, memory limits, admission control, and safe pool suspension.
- **Staff+:** Quantifies shared bandwidth and catalog limits. Defines isolation guarantees under overload. Covers concurrent commits, snapshot cleanup, capacity transitions, and cost trade-offs.

Practice this question with an AI interviewer: https://sysround.com/practice/new?problem=cloud-data-warehouse


---
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.