Partition a 100B-row fact table: date partitions, clustering, Z-ordering, and avoiding small files.
system-designpartitioninglakehouseperformance
Start here
The problem in plain words
Imagine a library that has 100 billion index cards, all thrown into a single giant pile. Someone asks, "Show me the cards from yesterday." With no order at all, you must look at every card in the pile.
The fix is to sort the cards into drawers by date. A question about yesterday opens one drawer and ignores the rest. In a data table this is called partitioning, and skipping the drawers you do not need is called pruning.
But there is a trap on the other side. If you make a drawer for every minute, or for every customer, you end up with millions of tiny drawers, each holding a few cards. Finding the right drawer and opening it takes longer than reading the cards. The skill is picking drawers that are neither too big nor too small.
Words you will meet
Partition
A separate chunk of a table, usually a folder of files, defined by a column such as date.
Partition pruning
The engine skipping partitions that cannot match the filter, so it reads less data.
Cardinality
How many different values a column has. Date has few per day. Customer id has millions, so partitioning on it creates too many folders.
Clustering / Z-order
Arranging data inside files so rows with similar values are stored together, which lets the engine skip files for secondary filters.
Small files problem
Many tiny files make planning and reading slow. Each file has overhead.
Compaction
Merging small files into bigger ones.
Predicate / filter
The condition in a WHERE clause, such as order_date = yesterday.
Metadata
Information about the table's files and partitions. Too many partitions means too much metadata to manage.
A query for yesterday reads the whole table
Press Play to watch it move
Each arrow is one thing happening. Play it step by step, or read the list below.
1.The query only needs one day of data.
2.With no partitions, the engine cannot tell which files hold yesterday, so it reads all of them.
The numbers, as things you can picture
100 billion rowsSeveral years of history. The table is far too large to scan casually.
Date is almost always in the filterThat makes date a natural partition key.
Files of 100 MB to 1 GBA good target size. Much smaller means overhead, much larger means less parallelism.
What the interviewer wants
Choose a partitioning and clustering layout that matches how the table is queried, keeps files a sensible size, and explains which data a typical query will skip.
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.
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.
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.
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.