Time Series Analytics with Pricing Data on Snowflake

Exploring several time series features using FactSet Tick Data, including TIME_SLICE, ASOF_JOIN, and RANGE BETWEEN for insights into trade data

Link to demo video Link to Quickstart

overview

This solution explores several time series features using FactSet Tick Data, including TIME_SLICE, ASOF_JOIN, and RANGE BETWEEN for insights into trade data.

  • Aggregating time-series data through downsampling reduces data size and storage needs, using functions like TIME_SLICE and DATE_TRUNC for efficiency.
  • ASOF JOIN simplifies joining time-series tables, matching trades with the closest previous quote, ideal for transaction-cost analysis in financial trading.
  • Windowed aggregate functions, such as moving averages using the RANGE BETWEEN window frame, allow trend analysis over time, accommodating data gaps for flexible rolling calculations.

Snowflake Features Used

Quickstart
Build this solution now Getting Started with Time Series Analytics with Pricing Data on Snowflake

Solution Architecture: Time Series Analytics with Pricing Data on Snowflake

This solution harnesses the power of Snowflake to extract valuable insights from high-frequency financial data. By utilizing FactSet Tick Data, available on the Snowflake Marketplace, we’ve constructed a robust data foundation. Synthetic closing prices, derived from the tick history, are stored in the closing_prices table within the raw schema.

Snowflake Notebooks serve as our interactive workspace for exploring time series data. Through the skillful application of advanced SQL functions like TIME_SLICE, ASOF JOIN, and RANGE BETWEEN, we demonstrate how to:

  • Calculate prevailing prices.
  • Track trading performance using TIME_SLICE
  • Analyze transaction costs using ASOF JOIN
  • Identify trends using RANGE-based sliding window
  • Measure volatility using LEAD & LAG

HANDS-ON TECHNICAL RESOURCES