Once upon a time I had a series about some cool stuff in Snowflake: features in their SQL dialect that were not available in the T-SQL dialect at the time (and some still aren’t). I followed up with some posts about interesting Fabric features (some of them are more “ironic” in nature as they describe extremely basic features who were originally missing in Fabric, like folders).
Recently I started working with Databricks on a project, and I mainly have to use the serverless SQL endpoint. Like Snowflake, Databricks also has a pretty amazing SQL dialect. In this blog post, I’m going to talk about one of my favorite new additions to the world of window functions: QUALIFY.
We all know the WHERE clause (which filters rows returned by the FROM clause) and the HAVING clause (which filters rows after they were aggregated by GROUP BY). QUALIFY is similar to those two, in the sense that this clause filters rows after they’ve been returned by the SELECT clause (which is where window functions are executed). As a small refresher, here’s the logical execution order of a SQL statement:
The QUALIFY clause is situated right after 5. What advantages do we get from QUALIFY? Previously, when we needed to remove duplicates for example, we wrote SQL like this (using the ROW_NUMBER function):
WITH cte_dupes AS
(
SELECT
SK_Customer
,CustomerID
,CustomerName
,ValidFrom
,ValidTo
,rid = ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ValidFrom DESC)
FROM dbo.DimCustomer
)
FROM cte_dupes
WHERE rid = 1;
With QUALIFY, we can do this directly in one single statement:
SELECT
SK_Customer
,CustomerID
,CustomerName
,ValidFrom
,ValidTo
,rid = ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ValidFrom DESC)
FROM dbo.DimCustomer
QUALIFY rid = 1;
More compact code, hooray! The good news is that QUALIFY isn’t Databricks exclusive, you can find it in Snowflake and since not too long ago, also in the Fabric Warehouse. Not in Azure SQL DB or in SQL Server though, but hopefully Microsoft will ship it to those products as well.
You can find the slides and SQL scripts for my session "Spice up your SQL…
The slides for my session "The €100 data warehouse on the Azure data platform" can…
A good week ago I hosted the monthly T-SQL Tuesday blog party. I invited the…
It's the second tuesday of the month, which means T-SQL Tuesday time! This month's topic…
It's time for T-SQL Tuesday again! And we're almost to number 200! T-SQL Tuesday is…
A while ago we suddenly had an error while trying to deploy one Fabric workspace…