Partition Pruning: Reduce Data Scanned by 90%

Partition Pruning: Reduce Data Scanned by 90%

Proper use of partition pruning can dramatically reduce the amount of data Snowflake needs to scan, resulting in faster queries and lower costs.

What is Partition Pruning

Snowflake stores data in micro-partitions (50-500MB). Partition pruning skips irrelevant partitions based on query filters, scanning only necessary data.

How Pruning Works

-- GOOD: Enables pruning
SELECT * FROM orders
WHERE order_date = '2024-01-15';
-- Scans only partitions containing Jan 15 data

-- BAD: Prevents pruning
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;
-- Functions on filter columns prevent pruning
-- Scans ALL partitions

Pruning Best Practices

1. Avoid Functions on Filter Columns

-- BAD
WHERE YEAR(date_column) = 2024
WHERE UPPER(name_column) = 'JOHN'
WHERE date_column + INTERVAL '1 day' = '2024-01-16'

-- GOOD
WHERE date_column >= '2024-01-01' AND date_column < '2025-01-01'
WHERE name_column = 'JOHN' -- Store as uppercase if needed
WHERE date_column = '2024-01-15'

2. Use Clustering for Pruning

ALTER TABLE large_table
CLUSTER BY (date_column, category);

-- Queries filtering on these columns benefit from pruning

Verifying Partition Pruning

-- Check partitions scanned in query profile
SELECT
QUERY_ID,
PARTITIONS_SCANNED,
PARTITIONS_TOTAL,
(PARTITIONS_SCANNED / PARTITIONS_TOTAL * 100) as PERCENT_SCANNED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT LIKE '%my_large_table%'
ORDER BY START_TIME DESC
LIMIT 10;

Real-World Example

A query on 10TB table with WHERE YEAR(order_date) = 2024 scanned all 10,000 partitions (10TB), took 60 seconds, cost 8 credits.

After changing to WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01': Scanned 1,200 partitions (1.2TB, 88% reduction), took 7 seconds (8.5x faster), cost 1 credit (87% reduction).

Key Takeaways

• Partition pruning can reduce data scanned by 80-95%
• Avoid functions on filter columns
• Use direct comparisons for best pruning
• Clustering keys enhance pruning effectiveness
• Verify pruning efficiency in query profiles