Turbocharge Time Series Insights with Snowflake’s ASOF JOIN

AUDIENCE: Technical

LEVEL: Basic

There is an abundance of Time series data. It plays a crucial role in understanding how systems, behaviors, and processes evolve over time. Industries like Internet of Things (IoT), financial services, and manufacturing leverage this data to inform business and operational decisions. However, when it comes to analyzing time series data, challenges often arise, particularly when joining multiple datasets.

For instance, consider a developer in an IoT company creating an application for predictive maintenance. They may need to analyze equipment health time series data alongside historical maintenance records to identify patterns. Similarly, a financial data analyst might want to associate periodic (e.g. hourly) option trade data with market price data for auditing purposes. The common hurdle in both cases is that timestamps in different time series tables often don’t match precisely, leading to complex and cumbersome queries.

Snowflake, in the quest to empower customers with simple yet powerful data insights, has launched ASOF JOIN. ASOF JOIN a purpose-built, high-performance feature designed to streamline time series analytics. In this article we will discuss what ASOF JOIN is and how it can transform your analytics workflows.

What is ASOF JOIN?

ASOF JOIN is a specialized join type that aligns records from two tables based on their temporal proximity. For every row in the left table, ASOF JOIN identifies the closest corresponding value from the right table, even if their timestamps don’t exactly match.

“ASOF JOIN’s ability to quickly align your time series data will be a game-changer in your analytics workflows.”

SQL Syntax

The SQL syntax for ASOF JOIN is straightforward:

How ASOF JOIN Works

CHALLENGE: Traditionally, without native ASOF JOIN support, customers had to resort to complex workarounds involving multiple subqueries, window functions, and range joins.

NEGATIVE OUTCOME: These solutions often resulted in lengthy queries that were challenging to maintain and yielded suboptimal performance.

SOLUTION: ASOF JOIN simplifies this process with a clear syntax that specifies the desired outcome, allowing the query engine to handle the heavy lifting. Snowflake optimizes the ASOF JOIN operation through a series of steps that align rows based on join keys and timestamp expressions, applying partition-aware sorting and efficiently searching for the closest preceding or following values.

Practical Application

Imagine a financial analyst tasked with finding the closest stock quote prior to each stock trade for audit or regulatory purposes.

Here’s how they would implement ASOF JOIN:

Performance Gains

To illustrate the benefits of ASOF JOIN, Snowflake conducted performance tests using sample datasets consisting of 39 million stock quotes and 2.2 million trades. The results showed that ASOF JOIN queries executed 12 times faster than traditional workarounds. When we scaled the data size tenfold, ASOF JOIN performed 16 times faster than the alternative approach.

By leveraging Snowflake’s native ASOF JOIN, you can concentrate on building straightforward analytic queries that seamlessly align time series datasets, eliminating the need for complicated workarounds.

Conclusion

The general availability of Snowflake’s ASOF JOIN marks a significant advancement in time series analytics. With its intuitive syntax and remarkable performance enhancements, ASOF JOIN empowers enterprises to derive valuable insights from their time series data effortlessly.

As your organization continue to harness the power of time series data, ASOF JOIN will stand out as a vital tool for driving informed business decisions.

Snowflake #SQL #SQLJOIN #ASOFJOIN #datamanagement #datawarehouse #datacloud #dataanalysis

Tarik Dwiek Saqib M. Sridhar Ramaswamy