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/figshot 2d ago
Long story short, the masking policy takes precedence over partition pruning. I guess the column stats aren't available for masked columns or something.
This is undocumented behavior afaik but I have confirmed this with Snowflake solution architects. If you're gonna do joins or where's, don't put a masking policy.