A transformation that refreshes every few minutes can still be expensive if it repeatedly scans and recomputes an entire dataset. That is why incremental processing matters: it can limit work to changed data and the downstream groups affected by those changes. Snowflake dynamic tables automate much of the scheduling and refresh management, but the SQL definition determines whether incremental refresh is eligible and how much work a refresh actually performs.
Two September 2026 changes expand the set of designs architects can consider. Snowflake announced general availability for incremental refresh of selected non-deterministic aggregate functions on September 23, and its supported-query documentation now includes constant VALUES clauses in FROM for incremental refresh. These changes are useful, but they are not a blanket promise that complex aggregations will become cheap, exact or universally incremental.
What changed in September 2026?
Snowflake’s September 23 release added incremental-refresh support for APPROX_COUNT_DISTINCT, APPROX_PERCENTILE and APPROX_TOP_K in INCREMENTAL, AUTO and ADAPTIVE modes. The key constraint is placement: these functions are supported for incremental refresh in the SELECT clause, including aggregate and window-function uses. ANY_VALUE remains unsupported. Approximate functions produce estimates, not exact results; that distinction matters when a metric is used for invoices, compliance or contractual thresholds.
The current supported-query reference also lists VALUES in the FROM clause, with expressions restricted to constants or expressions evaluable as constants at compile time. This can make compact, inline mapping sets easier to incorporate in a dynamic table without automatically requiring a full-refresh design. Neither change removes the need to review the complete query: one unsupported operator elsewhere can still prevent incremental eligibility.

Official references: Snowflake’s September 23, 2026 release note and its supported-queries guide, reviewed for this article.
Why this matters to executives
Consider a product analytics team estimating distinct products purchased per customer, a service organization monitoring the 90th-percentile time to resolution, or a merchandising team tracking frequently purchased categories. These are legitimate approximate-aggregation use cases. Previously, a team might have maintained separate transformation jobs or accepted a full refresh for a particular definition. Expanded support creates another option: let a dynamic table maintain a reusable, incrementally refreshed result.
The business benefit is not the SQL feature itself. It is the possibility of meeting a freshness requirement with fewer moving parts and lower repeated computation. The financial case must be measured against actual warehouse credits, refresh frequency, change volume and the cost of downstream queries. A feature being eligible for incremental refresh does not mean it will be efficient on a heavily changing, high-cardinality source.
Understand the three refresh choices
INCREMENTAL requests incremental processing and is appropriate when the query is supported and incremental semantics are required. FULL recomputes the result and may be simpler for small tables or transformations that change almost entirely each cycle. AUTO lets Snowflake choose a refresh mode based on its eligibility and heuristics. ADAPTIVE follows incremental-query eligibility requirements and can adjust its strategy under its documented behavior.
Do not treat AUTO as a contractual guarantee of incremental processing. Inspect the selected mode and refresh history after creation, and reassess when SQL definitions or source characteristics change. A dynamic table’s TARGET_LAG expresses a freshness target rather than an absolute delivery guarantee; downstream dependencies and warehouse capacity still affect observed lag.
Practical example: approximate distinct products by customer
An illustrative order-line source contains CUSTOMER_ID, PRODUCT_ID and ORDER_TS. An analytics mart can maintain a customer-level estimate of product breadth:
CREATE OR REPLACE DYNAMIC TABLE ANALYTICS.CUSTOMER_PRODUCT_BREADTH
TARGET_LAG = ’15 minutes’
WAREHOUSE = ANALYTICS_WH
REFRESH_MODE = INCREMENTAL
AS
SELECT
CUSTOMER_ID,
APPROX_COUNT_DISTINCT(PRODUCT_ID) AS EST_DISTINCT_PRODUCTS,
COUNT(*) AS ORDER_LINES
FROM RAW.ORDER_LINES
GROUP BY CUSTOMER_ID;
This is an illustrative definition, not a tested deployment. Verify syntax and eligibility in your Snowflake account and assess data types, source changes, privileges and refresh behavior. The approximate result should be labeled clearly in dashboards. If a finance report needs an exact number of billable items, COUNT(DISTINCT PRODUCT_ID) and an explicit reconciliation strategy may be more appropriate than replacing it with an estimate.

Practical example: an inline classification map
Small constant maps are sometimes maintained outside SQL solely because a transformation could not incrementally refresh with an inline VALUES source. The supported-query documentation now permits VALUES in FROM for incremental refresh, subject to constant-expression rules:
CREATE OR REPLACE DYNAMIC TABLE ANALYTICS.ORDERS_CLASSIFIED
TARGET_LAG = ’30 minutes’
WAREHOUSE = ANALYTICS_WH
REFRESH_MODE = INCREMENTAL
AS
SELECT O.ORDER_ID, O.STATUS_CODE, M.STATUS_GROUP
FROM RAW.ORDERS O
LEFT JOIN (
SELECT COLUMN1 AS STATUS_CODE, COLUMN2 AS STATUS_GROUP
FROM VALUES (‘N’,’Open’), (‘P’,’Open’), (‘C’,’Closed’)
) M ON O.STATUS_CODE = M.STATUS_CODE;
Treat this as a design illustration that requires account-level validation. The mapping is embedded in the definition: changing a code means changing the SQL definition and reviewing refresh implications. For frequently changing or business-owned mappings, a governed reference table remains preferable. A convenient VALUES clause is not a replacement for mapping stewardship.

A grouped aggregate does not necessarily update only one output row for each incoming row. Changes to historical transactions can require recomputing an entire customer, product or time-period group. When the source is constantly reloaded, group keys are highly skewed or most groups change each cycle, the incremental advantage can narrow.
Snowflake’s 2026 performance documentation also describes an optimization for certain top-level GROUP BY shapes that can reuse stored aggregate values. This is an optimization opportunity, not a universal promise for every aggregation. Test representative data, including deletes, updates, late-arriving events and corrections. For a daily dashboard, consider whether a narrow daily summary followed by a separate serving layer offers clearer operational behavior than one giant dynamic table.

How to decide whether to migrate an existing pipeline
Start with a dependency inventory. Identify the source tables, scheduled tasks, dbt models, dashboard extracts, APIs and any consumer that depends on exact metric values. Classify the workload by freshness, change volume, correctness requirements and operational ownership. A 15-minute customer-activity estimate and a month-end invoice ledger should not share an unexamined approximation policy.
Next, benchmark the current approach against a candidate dynamic table. Capture initial materialization cost separately from steady-state refresh cost. Compare total credits over an equivalent observation period, median and high-percentile refresh duration, actual lag, failure rate and query-serving performance. Run both outputs in parallel and investigate differences before switching consumers. Finally, define a rollback that preserves the old model and its dependencies until the new pipeline has passed acceptance checks.
A practical evaluation scorecard
For each candidate transformation, record:
- business purpose and consumer;
- exact or approximate metric policy;
- source change rate and historical correction frequency;
- desired and observed freshness;
- selected refresh mode;
- warehouse credits per day;
- refresh failures and retry behavior;
- reconciliation results;
- owner and rollback plan.
An illustrative acceptance rule might require all critical reconciliations to pass, observed lag to remain inside an agreed service threshold during a representative peak period, and daily compute cost to stay below an internally approved ceiling. Those thresholds are examples to define with stakeholders, not universal Snowflake benchmarks.

Implementation plan: four focused stages
Stage 1, establish a baseline
Record the existing transformation SQL, downstream dependencies, warehouse usage, source-change patterns and business definitions. Identify metrics that tolerate approximation.
Stage 2, prototype on a representative dataset
Create an isolated dynamic table with an explicit refresh mode. Test supported SQL constructs and observe initial versus subsequent refreshes. Include inserts, updates, deletes and late corrections.
Stage 3, validate business and operations
Compare row counts, exact reference calculations, approximate-error tolerance, refresh history, lag and credits. Confirm role permissions, deployment process, incident alerts and ownership.
Stage 4, release gradually
Route a limited dashboard or data product to the new model, monitor it through a full business cycle, then expand adoption. Retain the previous pipeline until rollback criteria are no longer needed.
Mistakes to avoid
Do not assume APPROX_COUNT_DISTINCT is interchangeable with COUNT(DISTINCT). Do not put supported approximate aggregates in WHERE or HAVING and assume incremental eligibility. Do not confuse a successful refresh with accurate business results. Do not assume a low TARGET_LAG means a guaranteed low latency. Do not compare costs using only one unusually quiet hour. Do not bury an operationally managed mapping in VALUES simply because it is syntactically convenient.
Most importantly, do not move all transformation logic into one large dynamic table merely to simplify orchestration. Smaller, well-defined layers can make testing, lineage, failure isolation and cost attribution easier.
Where Actiknow fits
Teams evaluating this change often need to connect warehouse behavior with the business definitions used in dashboards. Actiknow’s business intelligence consulting and data-modeling services cover reporting architecture, integration of multiple data sources and ongoing BI maintenance. The right engagement begins with an inventory of metrics, dependencies and refresh requirements rather than a promise that a particular Snowflake feature will reduce costs.
Frequently asked questions
1. Are all aggregate functions now incrementally supported?
No. Snowflake documents support by construct and placement. Check the current supported-query matrix for the full definition.
2. Are approximate distinct counts exact?
No. APPROX_COUNT_DISTINCT estimates cardinality; use an exact approach when the business requires exactness.
3. Can APPROX_PERCENTILE be used in an incremental dynamic table?
Snowflake lists it as supported in the SELECT clause, subject to the full query meeting incremental requirements.
4. Does ANY_VALUE work with this new support?
No. Snowflake’s September 23 note specifically states that ANY_VALUE remains unsupported.
5. Can VALUES replace a reference dimension?
It can express small constant mappings, but a managed reference table is usually better for frequently changing business rules.
6. Does AUTO always choose incremental refresh?
No. Inspect the selected refresh mode and actual behavior rather than assuming it.
7. Does incremental refresh always save money?
No. Savings depend on data churn, grouping patterns, warehouse configuration and frequency. Benchmark them.
8. Can I use these functions in HAVING?
Not for incremental refresh under the SELECT-only rule for the listed non-deterministic aggregates.
9. How should I test accuracy?
Compare approximate outputs against exact reference queries over representative periods and agree acceptable error with metric owners.
10. What should operations monitor?
Refresh failures, actual lag, credit consumption, upstream data changes and business-level reconciliation exceptions.
11. Should existing streams and tasks be retired immediately?
No. Run parallel validation and retain a tested rollback path until the replacement is accepted.
12. What is the first step for a CIO or data leader?
Identify one expensive, frequently refreshed aggregation with a clear owner and measurable success criteria.
Conclusion and next step
The September 2026 additions make Snowflake dynamic tables more flexible for certain approximate aggregations and compact constant mappings. They do not remove the need for deliberate metric definitions, SQL eligibility checks, performance measurement and operational controls. The best candidate is not necessarily the most complicated query; it is the one where a simpler refresh architecture can demonstrably improve freshness, reliability or cost without compromising trust in the numbers.
If your team is reassessing warehouse transformations and downstream reporting, explore Actiknow’s BI consulting approach or contact Actiknow to discuss a scoped assessment of data models, refresh performance and reporting requirements.
Sources and verification
Snowflake release note, September 23, 2026: https://docs.snowflake.com/en/release-notes/2026/other/2026-09-23-nondeterministic-agg-incremental-dynamic-tables
Snowflake supported queries: https://docs.snowflake.com/en/user-guide/dynamic-tables/supported-queries
Snowflake 2026 performance improvements: https://docs.snowflake.com/en/release-notes/performance-improvements-2026

