You asked for one day. It read five years.

The default

A filter does not stop the reading. It happens after.

Here is the bit that surprises people. You write this:

select count(*)
from events
where event_date = '2026-06-15'

read all 5 years
check each row
keep the ones that match
throw away 99.9%

So the engine pulled every row off storage, checked each one, and kept almost none of them. The answer is correct. The work was not.

Both techniques in this case are the same idea wearing different clothes: arrange the data so the engine can tell, before opening something, that it cannot possibly contain what you asked for.

And this matters twice over. Where you are billed for every byte scanned, reading less is cheaper straight away. Where you rent a cluster by the hour, reading less is faster — which is the same saving, arriving by a different route.

The first move

Split the table into separate piles.

Partitioning means physically splitting one table into chunks, based on the value of one column. Almost always a date.

One chunk holds everything from 13 June. Another holds 14 June. And so on, back five years.

Now your filter has something to work with. The engine looks at where event_date = '2026-06-15', sees that a single chunk is the only one able to hold matching rows, opens it, and leaves the rest alone.

The name for skipping whole chunks like this is partition pruning. It is the single biggest win available in this case, and it usually costs you one line when you create the table.

You met a version of this in Case 01, where blocks got skipped because of the notes kept beside them. This is the same instinct, one level up: skip entire folders because of what they are named.

What that showed

The wrong column is worse than no column.

Go back and split by customer_id, then run three of the four queries. Watch what happens.

That is the trap, and it catches people who have just learned the word. Partitioning is not a thing you do to a table to make it faster. It is a bet on which question gets asked most, and a wrong bet costs you more than not betting.

The second move

Sort the rows inside each pile.

You cannot split the table by customer. But customer is exactly what half your queries filter on. So what do you do?

You keep the rows for each customer sitting next to each other inside the chunk, instead of scattered through it at random.

c_8821  c_104   c_55012
c_377   c_8821  c_9
c_55012 c_41    c_8821

c_9     c_41    c_55
c_104   c_377   c_8821
c_8821  c_8821  c_55012

People call this clustering, and depending on the system you will also hear sorting, bucketing, or a Z-order. The names differ. The instinct — put things that get asked for together, together — does not.

Note the difference in what gets skipped. Partitioning skips whole chunks without opening them. Clustering opens the chunk, then skips most of what is inside. The first is a bigger saving. The second is the one that still works when you cannot split by that column.

Together

One picks the pile. The other finds the row.

They are not alternatives. They work at different scales, and the useful thing is to use both.

Go back to the widget and try it: event_date for the split, customer_id for the sort, then ask about one customer on one day. You will see why that pairing turns up everywhere. It happens to answer the two questions people ask most.

It is also why clustering alone is rarely the answer. Without partitioning, a clustered table still has to open every chunk before it can skip anything inside them.

Deciding

Two questions, in this order.

Three things people say

And the one that costs money.

If you get asked

“This query is slow. What would you do?”

Resist saying “add an index”. In a warehouse it is usually the wrong answer, and it is the first thing most candidates say.

Start by asking what it filters on. You cannot answer this without knowing. Saying so is a better opening than any technique.

Then name the mechanism, not just the word. That you would split the table by whichever column nearly every query filters on — a date, nine times out of ten — so whole chunks get ruled out before anything is opened. Saying partition pruning and then explaining it in plain words lands better than saying it alone.

Then the second layer. Cluster by the high-cardinality column that queries filter or join on, so blocks inside the chunk get skipped too.

Then show you know the failure. That splitting by something like customer id makes millions of tiny chunks, helps one query shape and nothing else, and can be slower overall. Volunteering the trap is what separates this from a memorised answer.

Three questions

Did it land?