Partitioning defaults for event tables that don't lie

Good partitioning starts with an honest time boundary

For large event and fact tables, the partition contract should match the way the table will actually be queried once the system has real traffic, real reporting, and real operational drift around it. That usually means partitioning on a DATE column derived from the time boundary the workload actually filters by, not whichever timestamp arrived first at ingestion or whichever field happened to be easiest to reach in the raw payload. Then clustering follows the next fields that genuinely narrow common access paths.

That sounds unremarkable because it is. Good defaults usually are. The goal isn’t to show off a storage feature. The goal is to make the table tell the truth about how it expects to be consumed. That’s the same instinct behind decision boundaries, and it’s why append-heavy raw zones still line up cleanly with the landing shape in streaming-first ingestion. Once the time boundary is wrong, the rest of the table design is already working uphill.

Partitioning is a safety control before it’s a speed feature

Partitioning gets described as a performance optimization because that’s the easiest version to explain. For large tables, the more useful view is that it’s one of the cheapest safety controls in the warehouse. A query that has to name its time slice has a much smaller blast radius than one that can wander across the full table and bill accordingly.

Partitioning belongs in the same family as broader cost guardrails. A lot of warehouse cost spikes start here, not in some dramatic one-off query, but in a table whose access path was never made explicit enough to protect itself. If the partition boundary matches the workload, bad queries usually fail cheaply. If it doesn’t, even normal queries can stay expensive for much longer than they should.

Pruning usually fails in ordinary ways

Most partitioning failures are not subtle. The partition column gets wrapped in a function. The filter lands on DATE(event_at) instead of the actual partition column. A view hides the predicate just enough that pruning becomes unreliable. A BI tool rewrites the query into something that still looks valid but no longer lines up with the contract the table was built around.

That’s why the real standard is not “could this table be pruned in a clean example.” The real standard is whether pruning survives the actual query shapes the system produces.

create table raw.events (
  event_at timestamp,
  event_date date,
  user_id string,
  event_name string,
  payload json,
)
partition by event_date
cluster by event_name, user_id
options (
  require_partition_filter = true
);

-- Good: prunable
select count(*)
from raw.events
where event_date between '2026-03-01' and '2026-03-07';

-- Bad: likely non-prunable or worse than it looks
select count(*)
from raw.events
where date(event_at) between '2026-03-01' and '2026-03-07';

That difference matters more than it looks. Both queries describe the same business slice. Only one of them lines up cleanly with the table’s storage contract. Once query layers start drifting away from that contract, partitioning stays declared but stops doing much of the work it was supposed to do.

require_partition_filter is worth the friction on shared large tables

On small or narrowly used tables, require_partition_filter can be more annoying than useful. On large shared tables, that trade changes fast. If a missing time predicate can turn into a real scan bill, a little friction is cheaper than silent convenience.

The mistake is treating the option like a moral virtue. It isn’t. It’s a boundary control. Use it where the table is large enough, shared enough, and exposed enough that unbounded scans are an operational problem. Leave it off where the blast radius is low and the extra constraint would mostly create noise.

Used that way, require_partition_filter stays practical. It enforces the existence of a time boundary without pretending to solve every query discipline problem in the stack. That’s enough. Anything more ambitious usually turns into warehouse theater.

A good base table can still be wasted by the serving path

A sensible partition strategy does not protect a weak serving model. Chart-level filters can generate ugly predicates. Custom SQL can hit the wrong field. One reporting surface can turn a clean table into a noisy stream of slightly different live queries without ever technically doing anything invalid.

That’s where this topic meets dashboard behavior and the precompute ladder. Some reporting patterns are simply too repetitive, too loose, or too indirect to keep running live against the base table, even if that base table was designed correctly. Partitioning can reduce the cost of a bad serving path. It doesn’t turn that path into a good one.

Partitioning is part of the table contract

Choosing a partition column is a claim about the table. It says: this is the time boundary that matters here, and this is the boundary query shapes are expected to use. That makes partitioning a close cousin of non-enforced constraints. Both are useful when they describe reality. Both become expensive ceremony when they stay in the DDL but stop matching the workload.

That’s the part worth guarding. Not the syntax, not the checkbox, and not the vague idea that the table is “optimized.” The real value is that the storage contract and the query path still agree on what kind of time slice the table is built to serve.

The rule

Pick a partition column that matches real access paths. Protect it with query shapes that can actually prune. Add require_partition_filter when the table is large enough to justify the friction. And if pruning keeps failing anyway, don’t pretend the problem lives only in storage. The serving path probably stopped honoring the contract.