Non-deterministic aggregate functions in Snowflake Dynamic Tables

Use cases

Imagine you maintain a dynamic table that calculates approximate volume metrics and you need the refresh to run incrementally without full reprocessing. You can now use approximate aggregate functions directly in Dynamic Tables using INCREMENTAL, AUTO, or ADAPTIVE refresh modes.

• Build Dynamic Tables that rely on approximate counts and percentiles.
• Process large datasets more efficiently while keeping incremental updates.

How to use

CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = '1 hour'
  WAREHOUSE = my_warehouse
AS
  SELECT
    category,
    APPROX_COUNT_DISTINCT(user_id) AS approximate_users,
    APPROX_PERCENTILE(amount, 0.95) AS p95_amount
  FROM my_base_table
  GROUP BY category;

What happens in this code:

1. Creates a Dynamic Table named my_dynamic_table.
2. Sets the refresh lag target to one hour.
3. Uses APPROX_COUNT_DISTINCT and APPROX_PERCENTILE functions within the SELECT clause alongside a GROUP BY clause.

Limitations and requirements

• Generally Available (GA).
• Supported in INCREMENTAL, AUTO, and ADAPTIVE refresh modes.
• Functions must be used only in the SELECT clause for incremental refresh.
• Supported functions include APPROX_COUNT_DISTINCT, APPROX_PERCENTILE, and APPROX_TOP_K.
• ANY_VALUE remains unsupported for incremental refresh.