How to Architect Snowflake Near-Real-Time Dashboards (Dec 2023)

As a Snowflake Sales Engineer who has also been a Financial Services Big Data Engineer, I’m often asked how I would implement near-real-time Snowflake dashboards.

The Question:

When we have changing big data, how can the business gets answers with the best performance to price?

The Challenge:

Getting snappy dashboards on non-changing data no matter how big is super easy — barely an inconvenience — because you can always denormalize to one optimized table. But as that data is changing, you also want to have the historical and fresh data be snappy and have a high performance to price ratio so you can increase your ROI.

Near-Real-Time Snowflake Dashboards

Sample Use Cases:

1. Financial Services: As we receive trades, cash, PnL, etc., how can our traders make decisions faster than any other system?
2. Airlines: As Flights / Pilots / Flight Crews / Staffing needs change, how can we inform stakeholders and systems ASAP?
3. Supply Chain: As Stock Keeping Units (SKUs) are shipped from source to warehouse to customer and the dozens of places in between, how can SKUs be tracked?

Architecture

Ingestion

1. Willing to pay possibly more for lower latency: Snowpipe Streaming (5–9 seconds), or
2. Want to pay less for higher latency: Copy Into (Batch, per-second billing after the 1st minute) or Snowpipe (Serverless, auto-ingest, per-second billing).

Transformations & Optimizations

1. Dynamic Tables: Automatically rebuild optimized, denormalized objects based on a query.
2. Lambda Architecture: Optimize a static historical table joined with a less-optimized but smaller table with changing data. They’re both joined by a view so the user doesn’t need to know that detail but gets the high performance for big data and the updated changes.
3. Search Optimization Service: For selective point lookup queries on tables. Imagine serverless, online, non-clustered indexes across multiple datatypes.

Presentation Layer Options

1. Activity Schema: One denormalized, optimized table.
2. Excelerator: An open-source Excel Add-In against Snowflake.
3. Sigma Computing: Think SAAS Excel on-steroids that can run circles around Excel’s 1 millon row limitation and other non-cloud-native limitations.

Conclusion

Having a Snowflake Single Version of Truth to handle your changing big data can drive significant business value with a high performance to price ratio. You can test out these architectural patterns and then scale up and down, in and out easily as your changing big data needs change. You can trial Snowflake to see for yourself.

If cost continues to be a concern, you can use Snowflake Edition arbitrage. For example, let’s say you are using the Business Critical edition in production but want to test this out on Standard or Enterprise edition with dummy data. You can easily do this by creating an account on your own with that lower edition.