Dynamic Pivot Queries in Snowflake: A Practical Example and Benefits

In this post, we will dive into a Snowflake query example that showcases the power of dynamic pivoting, a technique that can simplify and enhance data manipulation and reporting. We’ll walk through the query step-by-step, explore how the dynamic pivot works, and discuss its advantages. This example uses some of the sample data provided by Snowflake.

The Query: Breaking It Down

SET REGION_NAME = 'EUROPE';
WITH NATION_SALES AS(
SELECT C_ACCTBAL, C_MKTSEGMENT, N.N_NAME, R.R_NAME 
FROM CUSTOMER AS C
JOIN NATION AS N
ON C.C_NATIONKEY = N.N_NATIONKEY
JOIN REGION AS R
ON N.N_REGIONKEY = R.R_REGIONKEY
)
SELECT
    *
FROM
    (SELECT N_NAME, C_ACCTBAL, C_MKTSEGMENT 
     FROM NATION_SALES
     WHERE R_NAME = $REGION_NAME) AS N
PIVOT(
    SUM(C_ACCTBAL) 
    FOR N_NAME IN (SELECT DISTINCT N_NAME FROM NATION_SALES WHERE R_NAME = $REGION_NAME)
)
ORDER BY 1;

Step 1: Setting the Region

The first line uses a SET command to assign a value to a variable REGION_NAME. In this case, we are setting the region to ‘EUROPE’. This variable is later used to filter the results by the region we’re interested in.

SET REGION_NAME = 'EUROPE';

Step 2: Defining the Base Data with a CTE

The WITH clause defines a Common Table Expression (CTE) called NATION_SALES. In this step, the query joins the CUSTOMER, NATION, and REGION tables, selecting three main columns: the account balance (C_ACCTBAL), market segment (C_MKTSEGMENT), and the nation’s name (N_NAME), along with the region name (R_NAME).

WITH NATION_SALES AS (
    SELECT C_ACCTBAL, C_MKTSEGMENT, N.N_NAME, R.R_NAME 
    FROM CUSTOMER AS C
    JOIN NATION AS N
    ON C.C_NATIONKEY = N.N_NATIONKEY
    JOIN REGION AS R
    ON N.N_REGIONKEY = R.R_REGIONKEY
)

This CTE sets up the data we need to work with and ensures that we have all necessary fields filtered by region.

Step 3: Building the Dynamic Pivot

Now comes the key part of the query, the PIVOT.

SELECT
    *
FROM
    (SELECT N_NAME, C_ACCTBAL, C_MKTSEGMENT 
     FROM NATION_SALES
     WHERE R_NAME = $REGION_NAME) AS N
PIVOT(
    SUM(C_ACCTBAL) 
    FOR N_NAME IN (SELECT DISTINCT N_NAME FROM NATION_SALES WHERE R_NAME = $REGION_NAME)
)
ORDER BY 1;

Here, the PIVOT clause transforms rows into columns based on the distinct nation names (N_NAME). The query dynamically sums the C_ACCTBAL (account balance) for each distinct nation name found in the NATION_SALES dataset filtered by the chosen region.

The magic happens with the IN clause of the pivot, where we dynamically generate the list of nation names by selecting distinct values from the NATION_SALES table. This is what makes it a “dynamic” pivot – the column headers (nation names) are generated on the fly based on the actual data in the table, rather than being hard-coded.

Why Use a Dynamic Pivot?

1. Adaptability: You don’t need to know the exact set of column headers (nation names) beforehand. This is particularly useful when working with data that changes over time, such as adding or removing countries from a dataset.
2. Scalability: As new nations or categories are added to your data, the pivot automatically adjusts. You don’t need to rewrite your query to handle new columns.
3. Simplified Reporting: Dynamic pivots make it easier to create flexible and scalable reports. They eliminate the need for repetitive manual updates, allowing for more efficient data exploration.
4. Efficiency: Dynamic pivots can be more efficient in cases where manually specifying a list of columns would require multiple adjustments over time. Instead, the system adapts to data changes seamlessly.

Example Output

When we run the query with REGION_NAME = 'EUROPE', the results look like this:

In this case, the C_MKTSEGMENT remains a row header, and the account balances (C_ACCTBAL) for each nation are pivoted into columns. Each column corresponds to the account balance sums for a particular nation within the region specified.

Conclusion

Dynamic pivoting in Snowflake is a powerful technique for transforming data into a more useful and readable format. By generating pivot columns dynamically based on the data itself, you can handle changing datasets more flexibly and efficiently. This eliminates manual intervention and allows your queries to stay robust and adaptable, especially in environments where the underlying data may evolve over time.

Imagine pairing a dynamic pivot query with a very simple Streamlit app with its powerful data frame display. The possibilities of what insights you can make quickly are endless. In a future post, I’ll do just that – create a simple Streamlit in Snowflake app that dynamically displays the results of a dynamic query.

Follow me on LinkedIn and Medium for more content on Data Management and demos including Snowflake, Streamlit, and SQL Server.