Blog Post

Dazzling Stuff in Databricks (SQL) – Qualify

,

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.

The post Dazzling Stuff in Databricks (SQL) – Qualify first appeared on Under the kover of business intelligence.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating