Now in Public Preview, Snowflake’s RANGE based window frame will accelerate your time-series analytics

Addressing Time-Series Analytics Challenges

At Snowflake, we’re committed to helping customers derive meaningful insights from data with simplicity and speed. That’s why we are thrilled to announce the public preview of our enhanced RANGE based window frame, that will help accelerate time-series analytics.

For customers across industries, the ability to perform time-series analytics is crucial. This often involves calculating rolling statistics that remain robust despite gaps or non-uniform time-series data. For example, a trading firm might need to calculate the rolling average stock price five seconds before every transaction. Similarly, an IoT company might want to identify the moving maximum temperature reading from a thermostat every few minutes for anomaly detection. These scenarios can often be complicated by missing data, such as public holidays affecting stock trading or sensor malfunctions impacting thermostat readings. Regardless of the reason for these data gaps, analytics must account for them to produce accurate results.

Our Solution

We recognize the significance and prevalence of these use cases. So, over the past few months, we have invested in solving them by extending our support for RANGE BETWEEN sliding window frames to INTERVAL and unsigned numeric constants to define explicit offsets.

Deep dive into Snowflake’s extended RANGE based frame support

Consider the illustration above, where data is stored for each day, but it is missing for “Day 4.” The goal is to calculate the moving average over a 3-day window from “Day 1” to “Day 7”.

This can now be easily accomplished using Snowflake’s support for INTERVAL in the RANGE based window frame, as demonstrated in the pseudo-SQL below. With this functionality, customers can avoid the cumbersome preprocessing of their data to eliminate gaps. Instead, they can focus on the crucial task of extracting valuable insights from their data.

To apply this to some real-world use cases:

What’s next

The feature is now available in all Snowflake accounts. To learn more details, such as the list of window functions supported, you can read the Snowflake documentation and also try out some of the examples. Feel free to reach out to us in case of any questions or to share any feedback.