💎🏭SQL Reloaded: QUALIFY in Microsoft Fabric (Before/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 clause:

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 via QUALIFY followed by one or more constraints:

-- 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

Given the order in which the constraints are processed, QUALIFY allows to reduce redundancy by referencing a new column defined in the SELECT statement (update 19-Oct-2026):

-- 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 total_quantity>1

Hopefully this is available on all supported platforms. Otherwise, the logic must be repeated if a second constraint is needed for checking whether the value is within a boundary. 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

Imagine how complex can become a query like the following (update 19-Oct-2026):

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

    OR (total_quantity>21 AND total_quantity)<30)

    OR (total_quantity>41 AND total_quantity)<50)

    OR (total_quantity>61 AND total_quantity)<70)  

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 further tests are needed to validate this. 
2) It's useful to include the same logic used in QUALIFY also in the main SELECT statement, at least for troubleshooting purposes. 
3) The syntax seems to be supported only in Fabric warehouse, respectively SQL Endpoints for warehouse, lakehouse and SQL database.4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16).
4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16). (update 19-Oct-2026)
5) 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.
6) Developers must consider also portability of code when it's the case.
7) According to Copilot, QUALIFY is supported in Teradata (originator of the feature), Snowflake, Google BigQuery, Amazon Redshift, Databricks SQL, DuckDB and ClickHouse. (update 19-Oct-2026)
8) Of course, the window functions can be mixed to address different business questions, though it's maybe a good idea to move the DENSE_RANK in SELECT and reference the new column within QUALIFY (update 19-Oct-2026):

-- first/last customer by product incl. total
SELECT product_id
, SUM(quantity) OVER(PARTITION BY product_id) quantity_total
, FIRST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date ASC) first_status
, LAST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date DESC) last_status
FROM [LH_CRM_Data].ERP.[sales_invoices_partitioned4]
QUALIFY DENSE_RANK() OVER(PARTITION BY product_id ORDER BY invoice_date)=1

Happy coding!

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

🏭𝐌𝐢𝐜𝐫𝐨𝐬𝐨𝐟𝐭 𝐅𝐚𝐛𝐫𝐢𝐜: 𝐃𝐚𝐭𝐚 𝐖𝐚𝐫𝐞𝐡𝐨𝐮𝐬𝐞 (𝐓𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐎𝐯𝐞𝐫𝐯𝐢𝐞𝐰)

 Introduction (Some Background)

Typically, PowerPoint slides and similar content must be broken in small pieces and structured in such a way that the audience can digest the information presented. Thus, a presentation ends up spreading over multiple slides that must be structured in such a way that it facilitates also digestion, retention, and whatever further aspects are targeted. Conversely, on social networks the average author/publisher has only a small chance of capturing an audience's attention, given that the competition for readers' attention increases exponentially in the long sequence of posts. So, what information would we show for this purpose? 

When I want to learn something, typically I need a few pieces of information/knowledge that would allow me to anchor and integrate the text into existing knowledge. It usually starts with high-level definitions of the main concepts that reflect the various aspects that would help me associate and differentiate the respective concepts from similar concepts or knowledge. The volume of such information depends on many aspects - the complexity of the concepts and the contexts they belong to, how many similar concepts or metaphors are available, the gaps available, analogies and differentiators, etc.

A high-level list of the main capabilities for the respective concept(s) would further allow a (wider) surface to anchor the various information of interest. No matter how much text can be included, we shouldn't forget that a (well-chosen) picture is (often) worth a thousand words. Therefore, a well-chosen image that depicts a high-level representation of the concepts/ideas presented, the architecture or even a metaphor can have a considerable impact on the readers.  

Ideally the image should serve as a sketch or sample of the finished product. Then, even if from the used representation one can guess what are the ingredients used, the difference between a good dish and a fiasco often resides in details. Like in preparing a dish, one needs to know what ingredients are needed and how they must be prepared and used together, for the maximum effect. An experienced cook needs at least the list of ingredients and some general information on how the dish differentiates from other dishes. The more inexperienced the cook, the more information needs to be provided and somewhere a line must be drawn, otherwise a simple recipe becomes the cookbook itself. Therefore, like in any cookbook, I must assume that some basic knowledge is available!

So, as part of the learning/teaching process, I started a series of slides on Microsoft Fabric that should help me narrow down the recipes for delivering great products. The information comes from training material, various presentations delivered by Microsoft or third-parties, the various books and other technical material I read over the years. 

Let's start with the basics - the (data) warehouse!

   📣Technical Overview 

    

Conclusion

The slide barely scratches the surface, especially if we consider it from a broader context - what the average professional must know. Even if the gap between the presented information and a business case, for example, or anything similar capable of convincing the audience is considerable, the slide should serve as a starting point, if not a foundation on which something durable can be built. 

Enjoy the ride and feel free to like it, download it, respectively share it further!