System design interview

Partitioning Strategy for Large Tables

MediumLocked55 min read

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
WHERE dateno orderQueryorders from yesterdayOne giant heap100 billion rowsScans everythingslow and costly
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.The query only needs one day of data.
  2. 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.

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.

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.