This example is inspired by a customer who distinguished their Snowflake tables into two groups:

1. tables that only updated during nightly ETL processes
2. tables that also updated during the day

For the second group they made sure the Snowflake table comment for those tables included the text intraday=true.

This allowed database clients, including Excel models, to implement faster and cheaper queries by refreshing all queries — one time — at the start of the day, and subsequently only running queries that hit intraday=true tables.

The code snippet below shows how to do this using optimal(xl)'s VBA API:

Public Sub RefreshIntradayData()

    Dim query           As OxlQuery
    Dim allQueries      As OxlQueryList
    Dim intradayQueries As OxlQueryList

    Set allQueries = New OxlQueryList
    Set intradayQueries = New OxlQueryList

    allQueries.LoadAllInWorkbook ThisWorkbook

    For Each query In allQueries.AsEnumerable()

        If query.TableCommentsAll.ContainsCaseInsensitive("intraday=true") Then
            intradayQueries.Add query
        End If
    Next

    OxlSnowflake.RunParallel intradayQueries
End Sub

In the code samples that accompany our embedded API, we improve this macro by ensuring that at least one full refresh takes place, each new day, before we restrict the refresh to intraday=true tables. We also enhance it to add perpetual refreshes every 30 seconds; publish a 'dashboard' sheet as a website for teammates to view; and save/upsert some of the Excel model's computations to a Snowflake table.