Filtering selects rows that satisfy conditions during query processing. In Snowflake, apply filters as early as practical, before joins, aggregation, sorting, or other complex operations, to reduce the data the query must process. section 8.6.1
Use filtering to improve query behavior and preserve correct results:
- Limit a large result set before grouping or sorting when the use case permits; fewer rows can reduce execution time and local-storage spilling. section 9.2.3
- Filter historical data by its validity timeline. Select current rows with a future end timestamp, or select rows whose validity interval contains a specified date. section 12.4.2
- Use the query profile to verify whether filtering reduces execution time and bytes spilled, rather than assuming that every filter improves performance. section 8.6.1 section 9.2.3
Filter stores before aggregation
A bakery sales query restricts stores to those less than 1,000 kilometers away, then groups sales by store, distance, and product. On an extra-small warehouse, execution time falls from 22 to 16 seconds and local spilling falls from 36.86 MB to 18.53 MB. section 9.2.3
Listing 9.2 Amended query from listing 9.1 with filtered data
use warehouse BAKERY_WH_XSMALL;
select
store_id,
distance_km,
product_id,
sum(sales_quantity) as total_quantity
from RETAILER_SALES
where store_id in (
select store_id
from (
select store_id,
count(distinct product_id) as product_cnt
from RETAILER_SALES
where distance_km < 1000 #1
group by store_id
having product_cnt > 100
)
)
group by store_id, distance_km, product_id
order by distance_km;
Inspect the executable SQL that applies the distance filter inside the historical sales aggregation. section 9.2.3
