Now in Public Preview, Snowflake’s ASOF JOIN functionality will accelerate your Time Series Analytics

At Snowflake, we’re committed to helping customers derive meaningful insights from their data with simplicity and speed. That’s why we’re excited to announce the public preview of ASOF JOIN, a purpose-built SQL feature to accelerate time series analytics.

If you analyze time series data using SQL, you often have to join records across time series tables where timestamps don’t exactly match. This usually requires writing complex and cumbersome queries to link and analyze this kind of data. Across industries, in different scenarios, this type of JOIN is common. For instance, financial data analysts need it to associate intraday option trades with prevailing market prices for auditing purposes. Data engineers want it to construct pipelines for linking ad-campaign activity with website analytics, for attributing advertisement conversions. Engineers want to use it to join machine learning features point-in-time data for a feature store and similarly data scientists want this JOIN to develop dashboards for linking remote sensor data with equipment maintenance logs, for enabling failure analysis and maintenance prediction.

We recognize the importance of these kinds of use cases. So, over the past few months, we have invested in solving these use cases by developing native support for ASOF JOIN. This new SQL capability is not only easy to use but also more performant than current workarounds being used by customers.

Deep dive into Snowflake’s ASOF JOIN feature

ASOF JOIN is a type of join that pairs a record from two tables based on their proximity (usually based on temporal proximity) and used when the timestamps don’t exactly match. For each row on the left side of the join, the operation finds the closest matching value from the right side. Below is the syntax and pictorial representation of the ASOF JOIN.

SELECT …
FROM left_table ASOF JOIN right_table
MATCH_CONDITION… // proximity definition (closest preceding / following)
[ ON…] // Optional key for joining

To apply this to some real world use cases,

What are customers saying?

The simplicity and performance of the feature drew positive feedback from customers across industries who participated in the private preview. For instance, a large, energy sector customer was extremely pleased that their ASOF JOIN query ran ~99% faster than the alternative they were running, and that the use of ASOF JOIN resulted in substantially lower query costs. Similarly, a feature store partner was pleased that they consistently saw performance improvements in their relevant Machine Learning workloads upon using the feature.

In addition, Snowflake is building a native Feature Store solution that allows ML teams to create, store, manage, and retrieve features from a single source of truth. The ASOF JOIN feature provided significant performance gains to Snowflake’s Feature Store product. Previously, it was possible to achieve up to 7 joins in 200 seconds. With ASOF JOIN, it is now possible to join 100 feature groups in less than 20 seconds.

What’s next?

The feature is now available to all of you to use in your Snowflake accounts. To learn more, you can read the Snowflake documentation and feel free to reach out to us in case of questions or to share any feedback.