Understanding cost for dynamic tables¶
This topic provides an overview of the compute and storage costs associated with dynamic tables. For general information about Snowflake costs, see Understanding overall cost.
Compute cost¶
There are two compute costs associated with dynamic tables: virtual warehouses and Cloud Services compute.
Dynamic tables require virtual warehouses to refresh - that is, run queries against base objects when they are initialized and refreshed, including both scheduled and manual refreshes. These operations use compute resources, which consume credits.
Dynamic tables also require Cloud Services compute to identify changes in underlying base objects and whether the virtual warehouse needs to be invoked. If no changes are identified, virtual warehouse credits aren’t consumed since there’s no new data to refresh. Note that there may be instances where changes in base objects are filtered out in the dynamic table query. In such scenarios, virtual warehouse credits are consumed because the dynamic table undergoes a refresh to determine whether the changes are applicable.
If the associated virtual warehouse is suspended and no changes in base objects are identified, the suspended virtual warehouse doesn’t get invoked and no credits are consumed. Conversely, if changes are identified, the virtual warehouse is automatically resumed to process the updates.
Dynamic table refreshes are driven by the configured target lag. Dynamic table pipelines with lower target lag refresh more often and therefore incur higher compute costs.
You can use the Refresh History tab in Snowsight to check if virtual warehouse credits were consumed:
In the navigation menu, select Monitoring » Dynamic Tables.
Select your dynamic table and go to the Refresh History tab. Check the Warehouse used only checkbox to view refreshes that used the warehouse to update.
Tip
To get a clear understanding of costs related to your dynamic table pipelines, Snowflake recommends testing dynamic tables using dedicated warehouses so that the virtual warehouse consumption attributed to dynamic tables can be isolated. You can move your dynamic tables to a shared warehouse after you establish a cost baseline.
Storage cost¶
Dynamic tables require storage to store the materialized results. Similar to regular tables, you may incur additional storage cost for Time Travel, fail-safe storage, and cloning feature.
This section discusses the following storage considerations for dynamic tables:
For detailed information about how this storage incurs cost, see Understanding storage cost and Data Storage Considerations.
Time Travel and fail-safe storage¶
Snowflake Time Travel enables you to access and query historical versions of dynamic tables at specific points in time, which can help provide insights into historical trends, changes, and anomalies in your data. Note that frequent refreshes can increase buildup of Time Travel data, which adds to your overall storage usage. For more information, see Understanding & Using Time Travel.
Fail-safe features help protect your dynamic tables from data loss or corruption. Based on the configured fail-safe period, additional storage charges might apply.
Replication of dynamic tables¶
Dynamic tables support cross-account, cross-region replication, which enables you to copy data from a primary database to a secondary database for either disaster recovery or data sharing. It can serve as either a failover preparation strategy for disaster recovery or as a means of sharing data across deployments for read-only purposes. Using replication with dynamic tables is subject to replication costs. For more information, see Replication and dynamic tables.
Suspended dynamic tables¶
Suspended dynamic tables don’t incur additional costs beyond standard storage fees and don’t consume compute resources. If there are ongoing maintenance tasks or scheduled jobs that interact with the suspended table, compute resources might be consumed.
Transient dynamic tables¶
Snowflake supports transient dynamic tables, similar to regular tables, that persist until explicitly dropped, and are available to all users with the appropriate privileges without a fail-safe period. Transient dynamic tables are best used for transitory data that doesn’t need the same level of data protection and recovery provided by permanent tables, helping save on storage charges for fail-safe storage.
Additional storage for incremental refresh operations¶
For incremental refresh operations, dynamic tables maintain an additional internal metadata column for identifying each row within the table. Internal row identifiers consume a constant amount of storage per row and increase storage cost linearly to the number of rows in the table (independent of the number of columns). For tables with very few columns, the increase in storage compared to an equivalent CTAS table can be significant, or even dominant. In wider dynamic tables, this effect is less pronounced.
Refresh schedule cost¶
The schedule at which a dynamic table refreshes, whether full or incremental, has an effect on its overall cost. This section discusses the factors to consider when deciding on a refresh schedule (with the assumption that every refresh is non-empty):
Note
Refreshes are relatively inexpensive if the sources haven’t changed. For more information, see Compute cost (in this topic).
Full refresh schedule¶
The cost of full refresh typically depends on the data scanned and the refresh frequency. To save on costs, you can refresh your dynamic tables only when needed - for example, suspend your dynamic tables outside of business hours. For precise timing control, use the ALTER DYNAMIC TABLE command to set downstream target lag for your dynamic tables and use tasks to automate custom schedules.
Incremental refresh schedule¶
The cost of incremental refresh is typically proportional to the volume of changes in the source objects, plus some fixed overhead.
If the overhead is low, you can set a high refresh frequency without much downside. This means you can
refresh often for best results. For instance, a simple SELECT ... FROM ... WHERE dynamic table
only processes changed rows between refreshes, which has minimal overhead and can run frequently at low
added cost.
If the overhead is high, you must balance the credit consumption of high refresh frequency and the business benefits of freshness. For example, in a dynamic table with a join, the changes in one table must be joined with the other table. No matter how small the set of changes, this join usually involves a minimum cost to execute. If this overhead is significant, it can accumulate as the refresh frequency increases.