HOW TO: Calculate Cumulative Sum in Snowflake (2024)

In this article, we will learn how to calculate cumulative sums in Snowflake. A Cumulative Sum (CUSUM) is a running total that adds up values in sequence, to give you insights into data trends over time or other ordered dimensions. Imagine you’re tracking your daily steps. Sure the total steps count is interesting but wouldn’t it be cooler to see how your activity adds up over the day/time? That’s the magic of cumulative sum—it reveals trends by keeping a running tally. Snowflake makes calculating cumulative sums easy with its window functions, so you don’t have to write complex subqueries or self-joins, making the process faster and more streamlined.

What is Cumulative Sum?

Before we dive into the technical details of implementing Snowflake cumulative sums, let's take a moment to understand what exactly a cumulative sum is and why it's so valuable in data analysis.

A cumulative sum, also referred to as a running total or running sum, is a sequence of partial sums of a given sequence. In other words, it's the sum of a sequence  of numbers which is updated each time a new number is added to the sequence. Each number in the cumulative sum is the sum of the current number and all previous numbers in the original sequence.

Example:

If we have a sequence of numbers => [1, 2, 3, 4, 5]. The cumulative sum would be [1, 3, 6, 10, 15]. Here's how it's calculated:

• The first number remains the same: 1
• 1 + 2 = 3
• 3 + 3 = 6
• 6 + 4 = 10
• 10 + 5 = 15

In the context of data analysis, cumulative sums are extremely useful for tracking progress over time, understanding trends, and making comparisons. They can be applied to various metrics such as sales figures, user growth, inventory levels, or any other quantitative data that accumulates over time.

In Snowflake, we can use the Snowflake SUM() window function to calculate Snowflake cumulative sums. Snowflake window functions are a powerful feature that perform calculations across a set of table rows that are somehow related to the current row. They're called “window” functions because they operate on a window (set) of data. These functions can be used to solve problems such as calculating moving averages, ranking items, or in our case, computing cumulative sums.

Now that we have a clear understanding of what Snowflake cumulative sums are let's move on to its practical implementation.

How to Calculate Cumulative Sum in Snowflake?

Calculating Snowflake cumulative sums is a straightforward process once you understand the syntax and the steps involved. Let's walk through the process step-by-step, from setting up your Snowflake environment to writing the actual queries for Snowflake cumulative sum calculation.

Step 1—Log In to Snowflake

First login to your Snowflake account. Once logged in you can access the Snowflake web interface and run SQL and manage data.

Step 2—Create a Database and Schema

Before we can start working with data, we need to set up our database and schema. Here's how you can create a new database and schema:

CREATE DATABASE IF NOT EXISTS snowflake_cumulative_sum_demo;

USE DATABASE snowflake_cumulative_sum_demo;

CREATE SCHEMA IF NOT EXISTS snowflake_cumulative_sum_schema;

USE SCHEMA snowflake_cumulative_sum_schema;

Step 3—Create and Populate the Table

Now that we have our database and schema set up, create a table and populate it with sample data. For this example, we'll create an orders table:

CREATE OR REPLACE TABLE orders (
    order_id INTEGER,
    order_date DATE,
    product_category STRING,
    order_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, order_date, product_category, order_amount) VALUES
(1, '2024-07-01', 'Books', 30.00),
(2, '2024-07-02', 'Smartphone', 200.00),
(3, '2024-07-03', 'Books', 45.00),
(4, '2024-07-04', 'Clothing', 75.00),
(5, '2024-07-05', 'Smartphone', 300.00);

Now, let's verify the data to see whether the records have been successfully inserted.

SELECT * FROM orders;

Step 4—Calculate the Snowflake Cumulative Sum

Now we're ready to calculate Snowflake cumulative sums.

1) Using SUM with OVER Window Function (Calculating Total)

First, let's calculate the total sum of sales:

To calculate the total sum for each row across the entire dataset, use the SUM() function with the OVER() clause.

SELECT
    order_id,
    order_date,
    product_category,
    order_amount,
    SUM(order_amount) OVER () AS total_sum
FROM orders;

So, what does this query do? SUM(order_amount) OVER () calculates the sum of all order amounts across all rows. The empty parentheses in OVER () indicate that we're not specifying any partitioning or ordering, so the sum is calculated over the entire result set.

2) Using Snowflake SUM() with OVER(ORDER BY) (Calculating Snowflake Cumulative Sum/ Snowflake Running Total)

To calculate the running total from the start of the dataset up to the current row, use Snowflake SUM() with the OVER(ORDER BY) clause.

SELECT
    order_id,
    order_date,
    product_category,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_id) AS running_sum
FROM orders;

As you can see, in this query SUM(order_amount) OVER (ORDER BY order_id) calculates a Snowflake cumulative sum / Snowflake running total of order amounts, ordered by the order id. Each row's cumulative sum includes the current row's order amount plus all previous rows' order amounts.

It's worth noting that we can also explicitly specify the range of rows to include in our cumulative sum calculation using the ROWS BETWEEN clause:

SELECT
    order_id,
    order_date,
    product_category,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum
FROM orders;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause explicitly states that we want to sum all rows from the beginning of the partition up to and including the current row.

Both will give the same result, but the difference is in the explicitness of window frame definition:

• First query uses the default window frame, which is equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Second query explicitly defines the window frame as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Both of ‘em queries calculate the Snowflake cumulative sum / Snowflake running sum of order_amount, the Second query is more explicit and can sometimes offer better performance due to its clear frame definition.

Step 5—Calculate the Snowflake Cumulative Sum Within Separate Groups (Optional)

Sometimes, you might want to calculate cumulative sums within specific groups or categories. This is where the PARTITION BY clause comes in handy. It allows you to divide the result set into partitions and perform the calculation within each partition.

Let's calculate cumulative sums for each product separately:

SELECT
    order_id,
    order_date,
    product_category,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY product_category ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS snowflake_cumulative_sum
FROM orders;

You see PARTITION BY product_category is splitting the result set into partitions, one for each product_category. The Snowflake cumulative sum is then calculated within each partition, ordered by order_date. So you can see how orders are accumulating over time for each product category, rather than across all products.

Snowflake Cumulative Sums: Tips & Tricks

Now that we've covered the basics of calculating cumulative sums in Snowflake, let's explore some tips and tricks to use this feature more effectively:

1) ORDER BY Clause in the OVER() Function

The ORDER BY clause within the OVER() clause is crucial for cumulative sum calculations. It determines the order in which the running total is calculated. Make sure you set this correctly based on your needs. For time-based cumulative sums, you'll typically want to order by a date or timestamp column.

2) Use PARTITION BY Clause for Grouping

As we saw earlier in this article, the PARTITION BY clause is powerful for calculating Snowflake cumulative sums within specific groups. This is useful when you want to track running totals for different categories, products, or any other grouping in your data.

3) Optimization Tips

Snowflake is built for performance but there are still things you can do to optimize your cumulative sum calculations:

• Use clustering keys: If you are calculating cumulative sums on large tables often, consider using Snowflake’s auto clustering on the columns you use in your PARTITION BY and ORDER BY clauses.
• Efficient partitioning: When using PARTITION BY, try to choose columns that will split your data into a reasonable number of partitions. Too many partitions can hurt performance.
• Query optimization: Use the Snowflake EXPLAIN plan to understand how your query is being executed and look for opportunities to optimize.

4) Handle Nulls

Null values can sometimes cause unexpected results in Snowflake cumulative sum calculations. You can handle this by using the Snowflake COALESCE() function.

5) Balanced Partitioning and Clustering

Aim for balanced partitions. If one partition is significantly larger, it can cause performance issues. Similarly, choose clustering keys wisely to ensure even data distribution.

6) Use CTEs for Complex Calculations

For complex cumulative sum calculations, use Snowflake CTEs. They make your queries more readable and maintainable

Further Reading

To understand Snowflake window functions and performance in Snowflake, here are some great resources:

• Snowflake Window functions
• Snowflake SUM
• What Are Window Functions and Their Role in Snowflake?

Conclusion

And that's it! Cumulative sums are super useful in data analysis. They help us keep track of progress, spot trends, and make fair comparisons over time. Snowflake makes it easy to calculate these cumulative sums with its strong window functions. You'll be surprised what you can do with them!

In this article, we have covered:

• What is a Cumulative Sum?
• How to Calculate Cumulative Sum in Snowflake?
• Tips & Tricks for Snowflake Cumulative Sums

…and so much more!

FAQs

What is Snowflake cumulative sum?

Snowflake cumulative sum, or Snowflake running total, or Snowflake running sum is the summation of values over a specified range of rows within a dataset. It is typically calculated using the Snowflake SUM() window function combined with the OVER() clause.

How do I calculate a cumulative sum in Snowflake?

Use Snowflake SUM() window function with the OVER() clause, specifying the column to order by.

What does the OVER() clause do in a cumulative sum calculation?

OVER() clause defines the window or range of rows for the cumulative sum calculation. It allows you to specify the order of rows and optionally partition the data.

Can I partition data when calculating a Snowflake cumulative sum?

Yes, you can use the PARTITION BY clause within the OVER() clause to calculate cumulative sums for different groups independently

Is there a difference between ROWS and RANGE in window frames?

Yes, ROWS specifies a physical offset in rows, while RANGE specifies a logical range based on the values of the order column. ROWS is generally preferred for cumulative sums

Can I use cumulative sums for different time periods like weeks or months?

Yes, you can use date functions to truncate dates to weeks or months and then partition by these truncated dates.

How do I optimize Snowflake cumulative sum calculations?

Optimize by using clustering keys on columns used in the ORDER BY and PARTITION BY clauses, and by avoiding unnecessary complex calculations within the window function.