💎🏭SQL Reloaded: QUALIFY in Microsoft Fabric (Before and After)

One recent addition to T-SQL is the QUALIFY clause, which filters rows returned by a query based on a search condition that can reference window (analytic) functions [1]. The QUALIFY clause allows filtering directly in the block in which the window function is defined. According to the documentation QUALIFY can be used directly after HAVING:

SELECT select_list
FROM   table_source
[ WHERE <search_condition> ]
[ GROUP BY group_by_specification ]
[ HAVING <search_condition> ]
[ QUALIFY <filter_condition> ]
[ ORDER BY <order_by_expression> [ , ...n ] ]
[ FOR JSON <options> ]

So, for example, if we consider a fictitious Sales Invoices table, to retrieve the customers who bought a product more than once, we had to apply the constraint outside the block in which the window function was defined:

-- product bought more than once by same customer (before)
SELECT *
FROM (-- calculating resultset
	SELECT customer_id
	, product_id
	, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
	FROM [Sales].[sales_invoices]
) DAT
WHERE total_quantity>1

Now, a constraint can be applied directly in the block with the window function:

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1

The window function's logic must be repeated if a second constraint is needed for checking whether the value is within a boundary and, upon case, the logic can become even more complex (e.g. multiple intervals are checked):

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1

  AND SUM(quantity) OVER(PARTITION BY invoice_id, product_id)<10

Notes:
1) Having fewer blocks of code improves in theory code's readability and hopefully maintainability when the code doesn't need to be replicated as in the last example. This approach should improve also performance, however for this further tests are necessary.
2) It's useful to include the same logic used in QUALIFY also in the main SELECT statement, at least for troubleshooting purposes. This makes the code a bit redundant and developers must make sure the code is synchronized when making changes. This is the advantage of the first version.
3) The syntax seems to be supported only in Fabric warehouse, respectively SQL Endpoints for warehouse, lakehouse and SQL database.
4) One should check also whether the syntax is supported by all the features. For example, it's not supported by UPDATE and DELETE statements in warehouse. The restrictions for SQL Endpoints should be considered as well.
5) Developers must consider also portability of code when it's the case.

Happy coding!

References:
[1] Microsoft Learn (2026) SELECT - QUALIFY clause (Transact-SQL) [link]