A practical operating framework for data engineering leaders who need faster, more predictable refreshes without paying for unnecessary compute.
A dashboard can be technically available and still be operationally unreliable. The report opens, but its data is late. A scheduled transformation finishes, but only after the finance team has already made a decision. A warehouse is resized to make the delay disappear, and the bill rises without anyone knowing whether the underlying query was inefficient. These are not merely SQL tuning problems. They are decisions about freshness, architecture, cost and accountability.
Snowflake Dynamic Tables Insights, generally available since September 18, 2026, gives teams a more focused starting point. It examines dynamic table SQL definitions and refresh execution characteristics, then surfaces recommendations when it detects patterns that may increase latency or compute cost. The feature is useful, but the recommendation itself is not a business case. Teams still need to determine whether a fix improves the service level their users actually need.
The executive question is not “Can this refresh run faster?”
The better question is: “Which refresh problems threaten a business outcome, and what is the least expensive safe change that fixes them?” A five-minute improvement is valuable when it prevents a missed operational cutoff. It is less valuable when the dashboard is only reviewed each morning. Conversely, a low-cost pipeline can be a bad bargain if a critical metric is routinely stale.
Start with three measurable outcomes: data freshness against a defined business requirement; reliability, including failed or skipped refreshes; and total refresh-related cost. Every optimization should improve at least one without creating unacceptable regressions in the others. The objective is a defensible service level, not the shortest possible TARGET_LAG.
What Dynamic Tables Insights actually detects
A Snowflake dynamic table materializes the result of a SQL query and refreshes it as source data changes. Snowflake manages the refresh scheduling and dependency graph. Dynamic Tables Insights adds proactive diagnostics to this mechanism by inspecting both query structure and runtime conditions. It is accessible from the Insights filter on the Snowsight Dynamic Tables list and from an individual table’s Insights panel.
The current documented insight categories include an AUTO refresh mode that resolved to FULL; high change volume in base tables or joins; expensive order-dependent window functions; grouping and QUALIFY patterns that block efficient incremental maintenance; warehouse memory spilling; and an Iceberg base-table version condition. Each code describes a potential cause, not a universal instruction to change production DDL.
Snowflake also provides an Analyze with CoCo action that can examine dependencies, explain the detected pattern and generate proposed CREATE OR ALTER DYNAMIC TABLE statements. Treat this as a reviewable engineering suggestion. Generated DDL should go through the same testing, approvals and rollback planning as any other production change.
For the exact supported codes and interface behavior, consult Snowflake’s Dynamic Tables Insights reference.
Six patterns worth investigating first
1. AUTO quietly selected a full refresh
The AUTO_RESOLVED_TO_FULL_REFRESH insight means Snowflake selected FULL when the table was created. AUTO does not continually reassess its choice on every refresh. If the table is large and changes are small, repeated full processing may be costly. Inspect REFRESH_MODE_REASON and the query operators before deciding whether incremental processing is feasible. If FULL is appropriate, document the choice rather than labeling it a defect. If incremental is appropriate, redesign and validate the definition, then plan the supported mode change carefully.
2. Too many source rows change between refreshes
HIGH_BASE_TABLE_CHANGES and CHANGED_BASE_TABLES_UNDER_JOIN indicate that incremental maintenance may be processing a large amount of change, particularly around joins. The root cause can be upstream: truncate-and-reload ingestion, broad updates, late corrections, or a dimension table replaced wholesale. Investigate change patterns before changing the warehouse. A faster warehouse cannot make a needlessly destructive upstream load pattern efficient.
Snowflake’s ADAPTIVE mode normally uses incremental processing but can reinitialize when its heuristics determine that incremental work would be more expensive. It is worth evaluating for eligible workloads with occasional bulk changes. Reinitialization has downstream implications, so measure it rather than assuming it is free.
3. The SQL shape prevents efficient incremental work
Insights such as TOP_LEVEL_AGGREGATE_NOT_TOP_LEVEL, TOP_LEVEL_AGGREGATE_EXPRESSIONS_NOT_PERSISTED, QUALIFY_RANK_NOT_TOP_LEVEL and QUALIFY_RANK_KEYS_NOT_PERSISTED point to query structure. An aggregate nested inside a subquery or a deduplication filter buried below the outer SELECT may deny the engine a faster incremental path. In some cases, exposing grouping or ordering keys in the materialized output helps. But changing a SELECT list may alter downstream contracts, so schema compatibility and consumer impact must be checked.
A practical technique is to separate a complex transformation into testable stages, making the incremental-friendly operation visible where Snowflake can optimize it. This can simplify debugging, but more stages also introduce dependencies and maintenance overhead. Measure end-to-end pipeline cost, not just the performance of one table.
4. Window functions recalculate more than expected
EXPENSIVE_ORDER_DEPENDENT_WINDOW_FUNCTION flags calculations whose result depends on neighboring rows in an ordered partition. A new transaction can affect a running total or rank across many existing records. Ask whether the business needs the exact window result at every refresh, whether a narrower partition is possible, and whether a scheduled full refresh is more predictable. A rewrite that changes tie-breaking or historical results is not an acceptable optimization.
5. The warehouse spills because memory is insufficient
WAREHOUSE_TOO_SMALL is a specific signal: refresh execution spilled to local or remote storage. Increasing warehouse size can reduce elapsed time and sometimes total credits if the refresh finishes sufficiently faster. It can also increase cost. Test the smallest meaningful step-up, compare warehouse credits per successful refresh, and check whether the workload could instead be simplified or reduced. A recommendation to resize is not proof that the largest warehouse is the best option.
6. Target lag is more aggressive than the business needs
A target lag is a freshness target, not a guaranteed completion time or a fixed cron schedule. Short targets can increase refresh frequency and metadata checks. If sales operations need a fifteen-minute view, a one-minute target may buy little value while creating more work. Set explicit business-facing targets on consumer tables, and consider TARGET_LAG = DOWNSTREAM for intermediate tables where appropriate. Remember that a DOWNSTREAM table with no downstream consumer does not automatically refresh.
A repeatable diagnosis workflow
Step 1: Identify the tables that matter
Create an inventory of dynamic tables with owners, consuming reports or APIs, business criticality, target lag, observed lag, refresh mode, warehouse and upstream dependencies. Start with the final tables used by finance, operations or customer-facing applications. A low-priority staging table with a warning may be less urgent than a customer metrics table that misses a daily cutoff.
For organizations formalizing reporting ownership, Actiknow’s business intelligence consulting and data modeling services are relevant to connecting pipeline design with the metrics and reports business teams depend on.
Step 2: Collect the insight, not just its label
Use Snowsight’s Insights filter to review affected tables. Capture the code, detection context, suggested action, affected SQL definition and dependencies. Snowflake also documents a queryable recommendations field in INFORMATION_SCHEMA.DYNAMIC_TABLES(), allowing an account-wide review. The query below is illustrative and should be run with the appropriate monitoring privileges.
SELECT recommendations:recommendations, *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES())
WHERE ARRAY_SIZE(recommendations:recommendations) > 0;
Do not treat the count of insights as a quality score. Some warnings are intentional design choices; others can affect several downstream tables. Prioritize by impact and confidence, not volume.
Step 3: Establish a baseline from refresh history
For each candidate, capture at least seven representative days if available, including peak and quiet periods. Track successful refreshes, failures, p50 and p95 duration, actual lag, number of refreshes, reinitializations, and workload change volume. The INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY() table function provides recent per-refresh details, while the Account Usage history view supports longer analysis. Snowflake documents different retention windows for these surfaces, so select the source that matches your analysis period.
SELECT name, state, refresh_action, refresh_start_time, refresh_end_time,
DATEDIFF(‘second’, refresh_start_time, refresh_end_time) AS duration_sec
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
NAME_PREFIX => ‘MY_DB.MY_SCHEMA’))
ORDER BY refresh_start_time DESC LIMIT 100;
The purpose of this baseline is to distinguish occasional slow refreshes from a persistent design problem. A table that runs slowly only after a weekly bulk update needs a different solution from one that is slow on every incremental cycle.
Step 4: Attribute cost with care
Dynamic tables can incur warehouse compute, Cloud Services activity and storage charges. Warehouse credit consumption alone is not always attributable to a single table when multiple workloads share the warehouse. Use refresh and query history to connect executions to their resource use, and use dedicated warehouses or appropriate attribution methods where isolation is important. The number of rows processed is a useful diagnostic, but it is not a dollar estimate.
Avoid claiming savings by comparing only query duration. A two-times-faster refresh on a four-times-larger warehouse may consume more credits. Compare total credits across a representative workload, plus the resulting freshness and reliability. Include any cost from reinitializations, storage changes and operational work.
Step 5: Test one hypothesis at a time
For each insight, write a short hypothesis: “Moving the deduplication QUALIFY to the outermost SELECT will reduce incremental work while preserving the same business rows.” Define the expected mechanism, change scope, acceptance tests and rollback. Use a nonproduction environment or an isolated parallel table where feasible. Validate row counts, primary business keys, null behavior, aggregates, late-arriving data and downstream schemas before considering production deployment.
If Snowflake CoCo proposes DDL, review it as a candidate implementation, not a completed change. Dynamic table alterations or recreation may cause reinitialization or affect downstream objects. Check supported transitions and the likely refresh action before rollout. A faster refresh with different totals is a failed experiment.
Step 6: Measure and decide
Compare the baseline and candidate over comparable data volumes and update patterns. Record refresh duration distribution, lag compliance, credit use, failed refreshes and any new warnings. Promote only when the measured improvement is meaningful and the correctness checks pass. Keep a decision log explaining why an insight was fixed, deferred or accepted.
Illustrative example: a revenue reporting pipeline
Consider a hypothetical pipeline that ingests orders and adjustments, joins a frequently rewritten customer dimension, deduplicates late records and publishes a revenue summary. The final dashboard is needed by 8:00 a.m. daily. The team initially sets every intermediate table to a five-minute lag and assigns a larger warehouse whenever refreshes slow down.
Dynamic Tables Insights might reveal high changes under a join, a nested aggregation and a warehouse spill. The right sequence is not necessarily to fix all three at once. First confirm that the customer dimension really must be reloaded wholesale. Then test whether restructuring the aggregate improves incremental maintenance. Only after those changes should the team retest warehouse sizing. Finally, consider whether the revenue dashboard needs five-minute freshness at all, or whether a longer lag still meets the morning reporting cutoff.
This is an illustrative design scenario, not an Actiknow client case study. Its value is the order of investigation: remove avoidable work, preserve correct data, then buy more compute only when justified.
How to rank recommendations for a leadership review
A simple prioritization score can be more useful than a long technical backlog. Rate each proposed fix on four dimensions: business impact (does it threaten a report or operational deadline?), cost exposure (how often and how expensively does it run?), confidence (is the root cause supported by history?), and change risk (could it affect correctness or downstream consumers?).
Priority A: A critical table regularly misses its agreed freshness window or has recurring refresh failures, with a credible fix and manageable validation plan. Priority B: A high-cost table has a persistent insight and measurable savings potential but no immediate business outage. Priority C: A warning with limited evidence, an intentional full-refresh design, or low usage. This is a proposed management framework, not a Snowflake-generated score.
A useful one-page decision record includes the table name, owner, affected business process, insight code, seven-day baseline, proposed change, estimated engineering effort, testing approach, rollback method and measured post-change result. That makes cost optimization auditable and helps finance understand why engineering time is being spent.
The five metrics that belong on a dynamic-table health dashboard
- Freshness compliance: percentage of observed periods within the agreed lag target, measured at the consumer-facing table. A single successful refresh does not prove that data remained fresh throughout the day.
- Refresh reliability: successful, failed and skipped refresh counts by table and dependency chain. Track repeated failures separately from isolated transient events.
- p50 and p95 refresh duration: median and tail performance reveal different problems. Tail latency is particularly relevant when a morning deadline cannot be missed.
- Credits per useful outcome: refresh-related credit consumption normalized by successful refresh, business update cycle or another stable workload unit. State attribution assumptions explicitly.
- Active recommendations and time to resolution: count high-impact unresolved insights and track whether a fix was tested, accepted or intentionally deferred. Avoid using raw warning count as a team performance metric.
If leadership needs a single view across freshness, pipeline health and business reporting, a thoughtfully designed monitoring layer matters as much as the underlying Snowflake diagnostics. Actiknow’s data visualization services cover the dashboard design and reporting side of that problem.
What not to do
Do not blindly change every table to INCREMENTAL. Some SQL constructs and high-churn workloads make FULL the appropriate mode. Do not assume AUTO will revisit its original choice after the data changes. Do not set every table to the shortest target lag. Do not treat a larger warehouse as a permanent fix for inefficient upstream loading. Do not optimize a single table without considering the dependency graph. And do not ship AI-generated DDL without correctness tests and deployment controls.
A further trap is measuring only the first refresh after a change. Initialization and reinitialization can have very different costs from steady-state incremental refreshes. Evaluate both, and confirm that the system remains stable under late-arriving records, bulk corrections and peak ingestion.
A practical 30-day rollout plan
Week 1: Inventory and business requirements
Map the critical dynamic tables and their downstream consumers. Agree on freshness windows, especially around financial closes, operational shifts and customer-facing SLAs. Identify the owners of each table and establish a baseline of lag, duration, failures and compute consumption.
Week 2: Insights triage
Review Snowsight recommendations and classify them by structural SQL issue, source-change behavior, refresh mode or resource constraint. Select two or three high-value candidates. Document the hypothesis, expected benefit, test data and dependencies for each.
Week 3: Controlled experiments
Test SQL restructuring, mode selection, source-load changes or warehouse sizing separately. Compare equivalent periods, preserve sample results and run data-quality assertions. Where a change requires recreation or reinitialization, include that cost and impact in the evaluation.
Week 4: Deploy and operationalize
Promote validated changes through normal release controls. Create a recurring review of high-impact insights, refresh failures, lag compliance and cost. Assign a named owner for exceptions and define the conditions that trigger rollback. Record which recommendations were not adopted and why.
Frequently asked questions
Is Snowflake Dynamic Tables Insights generally available?
Yes. Snowflake’s September 13–18, 2026 release notes list Dynamic Tables Insights as generally available, with the GA entry added on September 18, 2026.
Does Dynamic Tables Insights automatically optimize my SQL?
No. It identifies issues and recommends actions. Analyze with CoCo can explain a pattern and generate DDL, but the engineering team should validate correctness, cost, dependencies and deployment impact before applying it.
Can I query recommendations rather than opening every table?
Yes. Snowflake documents the recommendations field returned by INFORMATION_SCHEMA.DYNAMIC_TABLES(), including an example that filters to tables with nonempty recommendations.
Should we switch every dynamic table to ADAPTIVE refresh?
No. ADAPTIVE is designed for eligible incrementalizable workloads and can reinitialize when Snowflake judges that to be more efficient. It is not a substitute for validating query support, upstream changes and downstream effects. FULL may still be the correct choice for certain workloads.
Does a smaller target lag guarantee fresher dashboards?
No. TARGET_LAG is a scheduling freshness target, not a guarantee. Slow queries, upstream delays and dependency chains can prevent the pipeline from meeting it. Measure actual freshness at the final consumer.
Will a bigger warehouse always reduce costs?
No. A larger warehouse may reduce spilling and finish faster, but its per-second credit rate is higher. Compare total credits and latency across equivalent runs before making a permanent change.
What is the difference between an insight and a refresh failure?
A refresh failure is an execution outcome. An insight is a diagnostic recommendation about structure or runtime behavior. A table can refresh successfully while still being unnecessarily expensive or slow.
How should we calculate the return on optimization work?
Compare before-and-after credits and engineering effort, but include the business value of improved freshness and fewer missed reporting deadlines. Use representative periods, not a single benchmark, and do not count projected savings as realized savings.
Do we need a separate monitoring dashboard?
Not necessarily. Snowsight and Snowflake monitoring views may be sufficient for a small team. A consolidated operational dashboard becomes useful when many tables, business owners or reporting commitments must be monitored together.
What is the safest first step for a production environment?
Inventory critical tables and establish a baseline before changing anything. Review the active insights, choose one low-risk candidate and validate its output in a controlled environment. Avoid bundling multiple untested changes into a single release.
The bottom line
Dynamic Tables Insights makes performance problems easier to discover. It does not remove the need to understand what the business needs, why the pipeline is doing expensive work and how a change affects downstream data. The strongest operating model pairs Snowflake’s diagnostics with explicit freshness commitments, reliable cost attribution, tested engineering changes and clear ownership.
Talk to Actiknow
If your BI reports depend on complex data pipelines and you want to connect refresh reliability, data modeling and executive reporting into one improvement plan, contact Actiknow to discuss the architecture and measurement requirements. We can start with the business outcomes and the evidence your team already has, rather than proposing changes before the problem is understood.
Sources and further reading
Snowflake release notes: Dynamic Tables Insights GA, September 18, 2026.
Snowflake: Dynamic Tables Insights reference and recommended fixes.
Snowflake: Monitor dynamic tables and review refresh history.
Snowflake: Understanding costs for dynamic tables.
Snowflake: Refresh modes and ADAPTIVE behavior.