'Just to Be Safe' Cost You Partition Pruning

Partition pruning is defeated almost entirely — the engine ends up scanning close to the whole table instead of the last 7 days.

Partition/micro-partition pruning works by comparing a filter directly against per-partition min/max metadata on the raw stored column. The moment that column is wrapped in a function — DATE(event_date) — most query engines' pruning logic can no longer match the filter to that stored metadata: pruning is done syntactically, against the literal column reference the optimizer sees, not against a fully evaluated, simplified version of the expression. It doesn't matter that DATE() on a DATE column changes nothing semantically — the optimizer isn't proving that; it's just failing to recognize the wrapped expression as prunable at all, so it falls back to evaluating the predicate per-row (or per-partition) across the full scan range.

Scale that against a 2-billion-row table spanning years of history: an unwrapped filter on the clustering key prunes down to roughly the ~7 daily partitions that matter. The wrapped version can force the engine to evaluate the predicate against most or all of the table's partitions instead, since none of them can be ruled out by metadata alone — often a 100x-plus difference in bytes scanned and compute cost for a query that "looks identical" from the result set it returns.

This generalizes far beyond DATE(): any function, cast, or arithmetic applied directly to a partitioned or indexed column in a WHERE clause — CAST(col AS ...), LOWER(col), col + 1, TRUNC(col) — risks the same silent pruning loss, in both warehouses and OLTP databases. The fix is to always filter on the raw column against a comparably-typed literal or bound (CURRENT_DATE - 7, not a transformed column), and if a derived value is genuinely needed for filtering, materialize it as its own persisted, clustered column rather than computing it inline in the predicate.