How to Filter Data in Snowflake

How do I filter data in Snowflake?

Use the WHERE and QUALIFY clauses to return only the rows that match specific conditions in Snowflake.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

When should I use the WHERE clause?

Use WHERE in the SELECT statement to filter rows before aggregation or ordering. It improves query speed by discarding irrelevant data early.

How do I structure multiple filter conditions?

Combine conditions with AND, OR, and parentheses for clear logic. AND narrows results; OR widens them. Parentheses ensure the intended precedence.

Why use QUALIFY instead of WHERE after window functions?

Window functions run after WHERE, so their results cannot be filtered with WHERE.QUALIFY lets you keep or drop rows based on window-function output.

Can I filter on aggregated results?

Yes—use HAVING with GROUP BY. HAVING runs after grouping, allowing filters on COUNT, SUM, AVG, and more.

What are best practices for performance?

Filter on highly selective columns, use comparison operators that allow pruning, and avoid casting columns inside the WHERE clause to maintain partition pruning.

Example: filter recent, high-value orders

SELECT * FROM Orders WHERE order_date >= CURRENT_DATE - 30 AND total_amount > 500;.

Why How to Filter Data in Snowflake is important

How to Filter Data in Snowflake Example Usage


-- Top-selling products last month, only in stock
SELECT p.id,
       p.name,
       SUM(oi.quantity)  AS units_sold
FROM   OrderItems oi
JOIN   Orders      o  ON o.id = oi.order_id
JOIN   Products    p  ON p.id = oi.product_id
WHERE  o.order_date BETWEEN DATE_TRUNC('month', CURRENT_DATE) - 1 AND DATE_TRUNC('month', CURRENT_DATE) - 1 + INTERVAL '1 month' - 1
  AND  p.stock > 0
GROUP BY p.id, p.name
HAVING SUM(oi.quantity) > 10
ORDER BY units_sold DESC;

How to Filter Data in Snowflake Syntax


SELECT column_list
FROM   table_name
WHERE  condition1 [AND|OR condition2 ...]
[GROUP BY column_list]
[HAVING aggregation_condition]
[QUALIFY window_condition]
[ORDER BY column_list]
[LIMIT n];

-- Example using ecommerce schema
SELECT c.name,
       o.order_date,
       o.total_amount
FROM   Customers c
JOIN   Orders o ON o.customer_id = c.id
WHERE  o.order_date >= '2024-01-01'
  AND  o.total_amount > 100;

-- Filter after a window function
SELECT o.id,
       o.total_amount,
       ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
FROM   Orders o
QUALIFY rn = 1;

Common Mistakes

Using WHERE to filter the result of ROW_NUMBER(). WHERE runs before window functions, so it ignores the generated row numbers. Fix by replacing WHERE with QUALIFY.
Placing functions around indexed columns in WHERE, e.g., WHERE TO_DATE(order_date) = '2024-01-01'. Casting prevents pruning. Compare raw columns instead or index-aligned expressions.