Teradata QUALIFY in Snowflake, Databricks and BigQuery
QUALIFY started as a Teradata extension, and all three targets support it now. Older Teradata shorthand inside it, such as RANK(score DESC) without OVER, must be rewritten.
Teradata syntax: SELECT ... QUALIFY window_condition · Teradata docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | QUALIFY <predicate> | Evaluated after window functions. | Snowflake docs |
| Databricks | Same name | QUALIFY boolean_expression | Evaluated after window functions. | Databricks docs |
| BigQuery | Same name | QUALIFY bool_expression | A window function must appear in the QUALIFY clause or the SELECT list. | BigQuery docs |
Behavior differences to check
- Teradata accepts legacy ordered-analytic syntax such as QUALIFY RANK(amount DESC) <= 3 and CSUM. Rewrite these as RANK() OVER (ORDER BY amount DESC) and SUM(...) OVER (...).
Examples
Top 3 products per category
SELECT category, product, sales FROM product_sales QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;SELECT category, product, sales FROM product_sales QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;SELECT category, product, sales FROM product_sales QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;SELECT category, product, sales FROM product_sales QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;