In an Iceberg table PARTITIONED BY (days(event_ts)), which WHERE clause actually prunes partitions?

## The short rule Filter the column named inside the transform. If the table was created with `PARTITIONED BY (days(event_ts))`, then predicates on `event_ts` are what prune. Nothing else in the query needs to change, and there is no second column to add. ## Why that works When a writer creates a data file it computes `day(event_ts)` for the rows in that file and stores the resulting value in the file's manifest entry. At plan time Iceberg takes each predicate on `event_ts` and projects it through the same transform to produce a predicate on that stored value — an inclusive projection, meaning it may keep a file that turns out to contain no matching rows, but it will never drop a file that does. Manifest entries that fail the projected predicate are discarded without opening the data file, and the surviving files are handed to the engine, which applies the original predicate row by row. ## Walking three predicates ```sql -- 1. prunes to one day's files WHERE event_ts >= TIMESTAMP '2026-05-01 00:00:00' AND event_ts < TIMESTAMP '2026-05-02 00:00:00' -- 2. does not resolve: there is no such column WHERE event_ts_day = DATE '2026-05-01' -- 3. resolves, but the planner sees a function, not the column WHERE date(event_ts) = DATE '2026-05-01' ``` The first is the canonical form and is what a well-written query looks like against any Iceberg table, partitioned or not. The second is the mistake made by someone carrying Hive habits over: the generated partition field name appears in file paths and in metadata tables, but it is not part of the table schema, so the query fails at analysis. The third is subtler and is the one that costs money in production — the query returns correct results, but because the planner is given an expression over the column rather than the column itself, the projection through the transform does not happen and the scan falls back to per-file bounds. On a table whose files are tightly bounded by time it may still skip a lot; on one whose files each span months it skips almost nothing. A fourth case is worth knowing: `WHERE region = 'eu'` on a table partitioned only by day cannot prune partitions at all. It can still drop files whose recorded lower and upper bounds for `region` exclude `'eu'`, which is effective only if the data happens to be clustered on that column. ## Time zones If the column is a timestamp with time zone, Iceberg stores it as an instant and the transform is computed in UTC. A partition therefore covers a UTC day. Filtering with local-midnight boundaries is still correct, but the range straddles two partitions, so the scan touches both. This surprises people who expect a business-local day to map to exactly one partition, and it is worth saying out loud when the interviewer asks about a query that reads twice as many files as expected. ## Confirming it rather than believing it Do not assert pruning; measure it. Run `EXPLAIN` on the query and look at the scan node, or read the number of files and bytes the scan actually planned from the engine's metrics. The comparison that convinces an interviewer is running the same query with and without the timestamp predicate and showing the file count drop by the expected factor. If it does not drop, the usual causes are, in order: the predicate is wrapped in a function, the predicate compares against a value the engine could not resolve at plan time, or the column being filtered is simply not a partition source. ## Habits that generalise Write predicates as half-open ranges on the raw column (`>= start AND < end`) rather than as equality on a derived value. Avoid casting or formatting the partition source column inside the WHERE clause. And when a table is handed to you, look at its partition spec before writing the query, so you know which column carries the pruning — with hidden partitioning the query does not reveal it, which is exactly the tradeoff the format makes.