The interview setup
Design a partitioning strategy for a large fact table (100B rows). Discuss partition by date, clustering by frequent filters, Z-ordering, and avoiding over-partitioning (small files).
Partitioning is how you skip data. Over-partitioning is how you create millions of tiny files and slow everything.
Background concepts from first principles
Partition prune
If almost every query has WHERE order_date = ..., storing files in date directories (or engine partitions) lets the query open a tiny subset.
Clustering / Z-order
Secondary filters like customer_id or product_id should not always be partitions. Clustering colocates related values inside partitions so more data can be skipped.
Small files problem
Too many partitions × frequent writes = tiny files. List operations and open costs dominate. Compaction is mandatory for streaming or high-frequency writes.
High-cardinality partition anti-pattern
Partition by user_id at 100B scale casually and you create an operational nightmare of partitions and tiny files.
The expanded problem
- Choose partition keys aligned to common filters
- Add clustering/Z-order for secondary filters
- Prevent tiny-file explosion
- Explain prune behavior for typical queries
- Include compaction jobs
fact_orders/ order_date=2026-09-01/ *.parquet (~large files) order_date=2026-09-02/ ...
What good looks like
- Ask top query filters first
- Date (or month) partitions
- Cluster secondary columns
- Compaction plan
- Reject user_id partitions unless very special case
Clarifying questions
- Top query filters?
- Write pattern: streaming append vs daily batch?
- Retention and time travel needs?
- Engine (Iceberg/Delta/Hive/BigQuery)?
Out of scope
- Inventing a new file format
- Exact vendor SQL dialect trivia as the core answer
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Interview framing details
You are expected to teach while you design. Start from a concrete failure, define the terms, then put the algorithm or layout on the board. Keep the scope tight: one grain, one SLA, one publication method.
Ask only the clarifying questions that would change your diagram. Write assumptions when the interviewer asks you to decide. Call out out-of-scope work so you do not burn the clock on tooling trivia.
A strong close restates the critical path, the failure mode you fear most, and what you would ship in week one.
Interview framing details
You are expected to teach while you design. Start from a concrete failure, define the terms, then put the algorithm or layout on the board. Keep the scope tight: one grain, one SLA, one publication method.
Ask only the clarifying questions that would change your diagram. Write assumptions when the interviewer asks you to decide. Call out out-of-scope work so you do not burn the clock on tooling trivia.
A strong close restates the critical path, the failure mode you fear most, and what you would ship in week one.