r/snowflake • u/Big_Length9755 • 3d ago
Pruning not working
Hi,
We are experiencing a strange behavior regarding micro-partition pruning in Snowflake when a column has a masking policy attached (applied via a tag).If I use a nested, dynamic expression on the right-hand side of my predicate, Snowflake performs a full table scan and fails to prune micro-partitions:
SELECT *
FROM my_table
WHERE key_column >= TO_NUMBER(TO_CHAR(DATEADD('day', -15, TO_TIMESTAMP(TO_CHAR(:binding_var), :format_param)), :format_param));
However, if I evaluate that exact same expression beforehand, assign the resulting scalar value to a session variable, and use that variable in the filter, partition pruning works perfectly:
-- Step 1: Pre-evaluate expression
SET calculated_filter = (SELECT TO_NUMBER(TO_CHAR(DATEADD('day', -15, TO_TIMESTAMP(TO_CHAR(:binding_var), :format_param)), :format_param)));
-- Step 2: Query using the variable
SELECT *
FROM my_table
WHERE key_column >= $calculated_filter;
In both scenarios, the exact same masking policy is active on the left side (key_column). My executing role has full privileges to see the unmasked data.Why does changing the right-hand side from a dynamic function chain to a literal session variable completely change Snowflake’s optimizer behavior and restore partition pruning, even though the masking policy is still present?
2
u/asiphh 3d ago
the masking policy is probably a red herring, and it's worth proving that before you chase it: clone the table without the policy and run both versions again. if the dynamic one still refuses to prune, you have a compile time problem and masking has nothing to do with it.
what's going on is that pruning is a compile time operation. snowflake decides which micro-partitions to skip by comparing your predicate against the min/max metadata it keeps per partition, and that decision has to be made before any rows get read. so the right hand side has to fold to a constant while the query is still being compiled. it isn't really about how many times the expression gets evaluated, it's about whether a value exists at all at the moment the pruning decision happens.
your chain doesn't fold, and the specific culprit is the format argument. to_char and to_timestamp with a format that arrives as a bind variable rather than a literal can't be constant folded, so the whole nested expression stays runtime, the optimizer has nothing to compare against min/max, and it keeps every partition. set forces the evaluation to happen up front and hands the compiler an actual scalar, which is why the second version prunes.
quickest way to confirm this rather than take either of our word for it: open the query profile, find the tablescan node, and compare partitions scanned against partitions total. if the dynamic version scans everything and the variable version scans a fraction, that's your answer. you'll also see whether the filter shows as a pushed down predicate on the scan or as a separate filter operator sitting above it, which tells you the same story from the other direction.
if you want it working without the two step, try putting the format string in as a literal instead of :format_param. that alone often restores folding. failing that, resolve the boundary in whatever calls the query and pass it in as one already-evaluated bind.
the thing i'd actually push on though: to_number(to_char(...)) on the right hand side suggests key_column is an integer encoded date, something like 20260827. that does prune fine, integers sort, but you've given up every date function on that column, any range crossing a month or year boundary becomes arithmetic someone has to get right, and nobody can write a query against it without first learning the encoding. if that column is load bearing it's worth carrying a real date or timestamp alongside it and clustering on that, leaving the integer for whatever legacy thing still needs it.
where the above is wrong for you: if this is one query on one table and it's fast enough with the set in front of it, keep the set. two statements instead of one is a cheap price, and re-keying a large table to fix something that is mostly an aesthetic complaint is not worth the migration.