Skip to content

Future Optimizations — Snowflake#

This document tracks Snowflake features that are in preview or recently GA'd that will require new optimization paths or modifications to existing ones. Each section outlines what to build once the feature is generally available and stable.

Development Backlog#

Items below are validated as valuable but deferred from the initial release. Ordered roughly by expected impact.

Deeper warehouse signals#

  • Unhealthy join detection — use GET_QUERY_OPERATOR_STATS Join operator output size to identify exploding/cartesian joins
  • Write amplification metric — derive from DML row count vs table growth rate; signals incremental config tweaks
  • Exploding joins — flag queries where join output rows >> input rows (fanout indicator)

Expensive query root cause mapping#

  • Map expensive queries to a specific symptom (spillage? full scan? cartesian?) instead of null actionable output
  • Point to the relevant domain fix (clustering, materialization, warehouse resize) based on root cause

Multi-level downstream lineage for clustering key discovery#

  • Current: 1-level children only (direct parents in int_dbt__relations)
  • Future: recursive traversal for long DAGs where filter queries land 2+ hops downstream

Report card views#

  • ~~Top expensive builds (by credit cost per run)~~ — Shipped as vw_snowflake__top_expensive_queries
  • ~~Top queried models (by SELECT frequency)~~ — Shipped as vw_snowflake__top_queried_models
  • ~~Top spillage models (by GB spilled)~~ — Shipped as vw_snowflake__top_spillage_models
  • Storage growth trends (month-over-month by schema)

Storage optimization domain#

  • Unused/zombie tables (large, never queried)
  • Time-travel cost analysis (retention settings vs actual usage)
  • Transient vs permanent recommendations (rebuildable intermediates → transient)
  • Incremental growth auditing (hot vs cold data separation)

Action macros (run-operation convenience wrappers)#

  • resize_warehouse — ALTER WAREHOUSE with validation
  • apply_mcw — enable multi-cluster with recommended settings
  • upgrade_gen2 — enable Gen2 resource constraint

Version compatibility testing#

  • Done: the package runs on dbt v2 and on dbt v1 (dbt-core 1.10 and later), and passes the same test suites on both.

Package release prep#

  • CHANGELOG generation
  • Version bump strategy (semver)
  • CI pipeline (compile + test on multiple dbt versions)
  • README polish for public consumption

AI domain expansion#

  • Deeper token efficiency analysis (when AI usage is present)
  • Model substitution recommendations (cheaper model achieves same quality)
  • Agent session optimization (consolidation, timeout tuning)

dbt Wizard / Copilot usage tracking#

  • Pending platform export capability — no API or event stream exists today
  • See detailed section below for what integration would look like once available

dbt Wizard / Copilot Usage Tracking#

Status: Pending platform capability — no API or export path exists
Priority: Medium (valuable for AI cost optimization but blocked on platform)

Current state#

dbt Wizard (formerly dbt Copilot) usage is metered internally as "Copilot Actions" and visible only in the dbt platform billing UI (Account Settings → Billing → Copilot Actions tab). After July 13, 2026, Wizard usage is metered separately from Copilot.

What's NOT available: - No API endpoint exposing per-request Wizard usage - No webhook or event stream for Wizard actions - No query comment metadata indicating "generated by Wizard" - No token-level breakdown accessible programmatically - No export path to external monitoring tools (Splunk, Datadog, etc.)

Per internal confirmation (dbt Labs, July 2026): forwarding AI/audit events to external monitoring tools is acknowledged as a roadmap item but not shipped. The telemetry collected (token usage, model, latency, cost attribution) is stored in an internal dbt Labs data warehouse and not shared with customers programmatically.

What integration would look like (once available)#

If dbt exposes Wizard usage via API or event stream: - New staging model: stg_dbt__wizard_usage (via External Access Integration calling dbt platform Admin API) - Intermediate: int_dbt__wizard_usage_daily (per-user, per-action-type aggregation) - Fact: fct_dbt__wizard_optimization_recommendations (heavy users, action patterns, cost trends) - Gold: surfaced in ai_optimizations as a "dbt AI" sub-domain

Distinction from Snowflake AI tracking#

Signal Source Trackable today?
Cortex AI functions in dbt queries CORTEX_AI_FUNCTIONS_USAGE_HISTORY Yes
CoCo (Cortex Code) Snowsight/CLI/Desktop METERING_HISTORY (service_type) Yes (account-wide, platform insights mode)
Snowflake Intelligence SNOWFLAKE_INTELLIGENCE_USAGE_HISTORY Yes (account-wide, platform insights mode)
Cortex Agents METERING_HISTORY + CORTEX_AGENT_USAGE_HISTORY Yes
dbt Wizard / Copilot actions dbt platform billing UI only No — pending platform export capability

Adaptive Compute (Preview — Enterprise+)#

Status: Public Preview (as of Snowflake Summit 2026)
Availability: Enterprise Edition, limited AWS regions (US West 2 Oregon, EU West 1 Ireland, AP Northeast 1 Tokyo)
Documentation: Adaptive Compute

What It Is#

Adaptive Compute is a new warehouse type (WAREHOUSE_TYPE = 'ADAPTIVE') that eliminates manual sizing. Instead of configuring warehouse size, multi-cluster settings, QAS, and auto-suspend/resume, the system allocates resources per-query automatically.

Key properties: - MAX_QUERY_PERFORMANCE_LEVEL — t-shirt size upper bound on per-query performance (XSMALL through X4LARGE, default XLARGE) - QUERY_THROUGHPUT_MULTIPLIER — controls concurrency budget (integer, default 2; 0 = unlimited)

How It Differs from Standard/Gen2#

Property Standard/Gen2 Adaptive
Size User-configured (XSMALL–X4LARGE) N/A — system decides per query
Multi-cluster User-configured N/A — automatic scaling
QAS Separately enabled/configured Built-in (no separate line item)
Auto-suspend/resume User-configured N/A — no suspend concept
Billing Per-hour credits (by warehouse size) Per-query credits (by resources consumed)
Detection warehouse_size in QUERY_HISTORY = standard sizes warehouse_size = 'ADAPTIVE'

Impact on Current Package#

Warehouse sizing recommendations (fct_snowflake__warehouse_config_recommendations): - Recommendations to "scale up," "scale down," "enable multi-cluster," or "enable Gen2" do not apply to adaptive warehouses - Current fix: filter adaptive warehouses from the sizing intermediate (warehouse_size != 'ADAPTIVE') - Future: produce a separate recommendation category for adaptive warehouses

Expensive query recommendations (fct_snowflake__expensive_query_recommendations): - Still relevant — expensive queries on adaptive warehouses still consume credits - QUERY_METERING_HISTORY provides per-query credits natively for adaptive warehouses (more precise than elapsed-time proration) - warehouse_size = 'ADAPTIVE' should be noted in output rather than displayed as a t-shirt size

Spillage recommendations (fct_snowflake__warehouse_performance_recommendations): - Fully relevant — spillage is a query-level phenomenon regardless of warehouse type - No changes needed

What to Build (Post-GA)#

New Mart: fct_snowflake__adaptive_warehouse_recommendations#

Purpose: Recommend tuning MAX_QUERY_PERFORMANCE_LEVEL and QUERY_THROUGHPUT_MULTIPLIER based on observed workload patterns.

Data sources: - QUERY_HISTORY (where warehouse_size = 'ADAPTIVE') - QUERY_METERING_HISTORY (per-query credits for adaptive warehouses) - WAREHOUSE_LOAD_HISTORY (queuing behavior for adaptive warehouses)

Recommendation logic:

Signal Detection Recommendation
Chronic queuing High queued_overload_time despite adaptive Increase QUERY_THROUGHPUT_MULTIPLIER
Underutilization All queries using far less than MAX_QUERY_PERFORMANCE_LEVEL Decrease MAX_QUERY_PERFORMANCE_LEVEL to reduce burst cost ceiling
Cost spikes without performance gain High per-query credits but no improvement in elapsed time Lower MAX_QUERY_PERFORMANCE_LEVEL — system is over-allocating
Conversion candidate Standard warehouse with chronic overload + Enterprise+ + supported region "Consider converting to Adaptive Compute"

Proposed intermediate: - int_snowflake__adaptive_warehouse_daily — daily stats per adaptive warehouse (queuing, per-query credit distribution, performance level utilization)

Proposed variables: 

vars:
  adaptive_warehouse_enabled: true
  adaptive_warehouse_lookback_days: 30
  adaptive_warehouse_queue_threshold_sec: 5

New Recommendation Tier in Sizing DAG#

When adaptive warehouses are GA and broadly available, add a tier to the existing fct_snowflake__warehouse_config_recommendations:

Recommendation: "Convert to Adaptive Compute"
Conditions:
  - Account is Enterprise Edition or higher
  - Warehouse has chronic overload (median_overload_sec > 5s for 14+ days)
  - Manual tuning hasn't resolved the issue (multiple size/MCW changes in history)
  - Region supports adaptive warehouses

Detection Query (for testing)#

-- Find adaptive warehouses in the account
SELECT DISTINCT warehouse_name
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE warehouse_size = 'ADAPTIVE'
  AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP());

-- Or via SHOW WAREHOUSES
SHOW WAREHOUSES;
-- Look for warehouse_type = 'ADAPTIVE' in results

Gen2 Default Behavior Change (BCR-2250)#

Status: Pending (rolling out by region)
Impact: New organizations after June/July 2025 default to Gen2 for standard warehouses
Documentation: Gen2 Standard Warehouses

Impact on Current Package#

The "Enable Gen2" recommendation in fct_snowflake__warehouse_config_recommendations remains valid for: - Existing Gen1 warehouses that haven't been converted - Accounts in organizations created before the default changed

No code changes needed — the recommendation correctly identifies DML-heavy warehouses that would benefit from Gen2's optimized DML execution path. As adoption of Gen2 increases organically, this recommendation will fire less often.

Detection#

Gen2 can be detected via SHOW WAREHOUSES (the resource_constraint column shows STANDARD_GEN_2). This is NOT currently reflected in ACCOUNT_USAGE views — only in SHOW WAREHOUSES output. A future enhancement could query SHOW WAREHOUSES and exclude already-Gen2 warehouses from the recommendation.

Interactive Warehouses#

Status: Generally Available
Documentation: Interactive Warehouses

Relevance to This Package#

Interactive warehouses are designed for low-latency, high-concurrency dashboard and application workloads. They use a different billing model and are not a cost optimization target in the same way — they're a specialized product for a specific use case.

No optimization path planned unless customer demand emerges. If added, it would focus on: - Identifying workloads that are paying standard warehouse rates but have interactive-warehouse characteristics (many short queries, low latency requirements) - Recommending conversion to interactive when the workload profile matches

Recommendation Interface + Cross-Domain Prioritization#

Status: Shipped (GA)
Scope: Package-wide design — implemented as int_snowflake__all_recommendations + gold layer views

Implementation Summary#

The cross-domain prioritization layer is now implemented:

  • int_snowflake__all_recommendations (table materialization) — unions all domain facts with normalized fields, computes per-entity priority_tier via row_number() window function
  • 10 gold views — dashboard-ready surfaces for humans and agents (see gold_design.md)
  • Confidence-based incremental recommendations — confidence_score, assumptions, blocking_signals fields
  • Per-entity relative priority — hierarchy_rank + backlog_status ordering (see optimization_priorities.md)

All domain-specific recommendation logic remains in the domain marts. The gold layer normalizes and prioritizes across domains.

Remaining backlog#

Item Status Notes
vw_snowflake__recommendations_by_environment Planned Filter by target_name (prod/dev/staging), has_nonprod_only flag
Resolution detection (v2) Planned Requires incremental facts — detect when a signal disappears because fix was applied vs data changed
Temporal promotion Planned Requires v2 — promote deferred warehouse config after observation window
Agent auto-apply workflow Planned config_change items auto-proposed as PRs; sql_refactor items investigated and drafted

The following items from the original design are now shipped: - Cross-domain prioritization layer (int_snowflake__all_recommendations) - Gold layer views (top_recommendations, optimization_backlog, model_optimizations, warehouse_optimizations, cross_domain_insights, cost_savings_summary, top_expensive_queries, top_spillage_models, top_queried_models, user_level_cost_attribution) - Effort classification (config_change, actionable_review, sql_refactor, investigation) - Agent-consumable structured fields (node_id, signal_id, dbt_model_config, snowflake_ddl, effort_category, action_type) - Confidence-based incremental recommendations (confidence_score, assumptions, blocking_signals) - Per-entity relative priority system (priority_tier via hierarchy_rank + window function)

Clustering Health Analysis (for already-clustered tables)#

Status: Planned
Priority: High
Scope: New model within the clustering optimization domain
Prerequisites: Existing fct_snowflake__table_clustering_candidates (V3 pruning-based scoring)

Why This Matters#

The current clustering DAG answers "which tables should be clustered?" But it does not answer "are already-clustered tables healthy?" — which is equally important.

A table with is_already_clustered = true may still show poor pruning (high scan_ratio_pct) because: - The clustering key is misaligned with actual query filter patterns - High DML volume has degraded clustering depth faster than auto-reclustering can maintain it - Auto-reclustering is suspended (intentionally or accidentally) - The table exceeds the auto-reclustering throughput budget

Proposed Model#

fct_snowflake__clustering_health

  • Grain: one row per clustered table per snapshot_date
  • Only evaluates tables where is_already_clustered = true
  • Joins to fct_snowflake__table_clustering_candidates to correlate health with pruning behavior

Data Sources#

1. AUTOMATIC_CLUSTERING_HISTORY (ACCOUNT_USAGE view)#

Standard ACCOUNT_USAGE view. Provides: - credits_used — reclustering compute cost per table per time window - num_bytes_reclustered — volume of data reorganized - num_rows_reclustered — rows touched by reclustering - Latency: up to 3 hours. Retention: 1 year.

Proposed derived columns: - reclustering_credits_30d - is_actively_reclustering (boolean) - avg_daily_reclustering_credits

2. SYSTEM$CLUSTERING_INFORMATION (system function — real-time)#

Per-table function call (not a queryable view). Provides: - average_depth (lower = better, 1 = perfectly clustered) - average_overlaps - total_partition_count vs total_constant_partition_count - clustering_errors (e.g., "Clustering service has been disabled")

Requires per-table iteration — macro-only or post-hook path. Cannot be a standard SQL model.

Proposed derived columns: - avg_clustering_depth - constant_partition_pct - clustering_health (good/moderate/degraded based on depth thresholds)

Recommendations This Model Would Produce#

Scenario Recommendation
High depth + high scan_ratio + active reclustering Key misaligned with query patterns — review key choice
High depth + high scan_ratio + NOT reclustering Auto-clustering may be suspended — investigate
Low depth + high scan_ratio Key maintained but doesn't match filter columns — consider different key
Low depth + low scan_ratio Healthy — clustering working as intended
High reclustering credits + low query volume Over-clustered — cost may exceed benefit

Implementation Path#

  1. Start with AUTOMATIC_CLUSTERING_HISTORY in the DAG (standard staging + intermediate + fact)
  2. Add SYSTEM$CLUSTERING_DEPTH to the macro path for interactive checks
  3. Evaluate whether a post-hook or stored procedure is needed to bring depth data into the DAG

References#

Storage Optimization Path#

Status: Planned
Priority: Medium
Scope: New Snowflake optimization domain

Why This Matters#

The current package focuses primarily on compute and query-performance optimization. A future optimization path should also target storage efficiency and storage-related spend, especially for mature dbt projects that accumulate large historical tables, low-value retained data, and expensive storage patterns over time.

This would broaden the package from compute optimization into a more complete Snowflake cost optimization surface.

Potential Recommendation Areas#

Stale or low-value tables#

Identify large relations that: - have not been queried recently - are not downstream dependencies of actively used models - continue to incur storage cost without meaningful analytical value

Potential actions: - archive - drop after retention review - move to cheaper long-term storage patterns outside the active analytics environment

Time Travel / Fail-safe heavy storage overhead#

Highlight objects where retained history materially increases storage cost relative to active table value.

Potential actions: - reduce retention where safe and governance permits - move transient/ephemeral workloads to more appropriate table types

Transient vs permanent storage fit#

Identify dbt-managed tables where the current durability choice may not align with the workload.

Potential actions: - recommend transient materialization for rebuildable intermediate data - preserve permanent tables for curated / business-critical marts

Large tables with low read value#

Identify relations that are large but rarely queried.

Potential actions: - archive - compress usage windows - re-evaluate whether the model should continue to be materialized

Historical accumulation from incremental models#

Identify incrementals where storage growth is outpacing usage value.

Potential actions: - partition or prune old data - add retention windows - split hot vs cold data - archive closed periods

Proposed Future Models#

Possible Snowflake marts/intermediates: - int_snowflake__storage_inventory_daily - int_snowflake__table_storage_retention_signals - fct_snowflake__storage_optimization_recommendations

Design Notes#

Storage recommendations should follow the same long-term interface as other optimization domains: - recommendation_state - recommendation_confidence - priority_score - estimated_impact - recommended_next_action - recommendation_reason

This makes storage optimization easy to fold into the broader cross-domain prioritization layer once implemented.

Environment Metadata Expansion#

Status: Future enhancement
Current state: The package captures dbt_cloud_environment_id from query comments and groups by it, but cannot resolve the human-readable environment name or type (dev/staging/prod) without external data.

Potential approaches#

  1. User-provided mapping var — A dbt_environment_labels var where users manually map env_ids to names: 

    vars:
  dbt_environment_labels:
    '70437463665306': {name: 'Production', type: 'prod'}
    '70437463672596': {name: 'CI/PR Checks', type: 'dev'}

  2. dbt platform Admin API integration — A dbt run-operation populate_environment_map macro that calls GET /api/v2/accounts/{id}/environments/{env_id}/ via Snowflake External Access Integration and writes results to a seed table. Requires: External Access Integration (ACCOUNTADMIN), dbt platform service token as Snowflake secret, network rule for cloud.getdbt.com.

  3. dbt-artifacts-style on-run-end hook — Capture env_var('DBT_CLOUD_ENVIRONMENT_TYPE', '') and env_var('DBT_CLOUD_ENVIRONMENT_NAME', '') at the package's own runtime and store in a metadata table. Limited: only captures the env where the package itself runs.

Limitations#

  • Cross-account architectures: QUERY_HISTORY is account-scoped. Multi-account setups must deploy the package per account.
  • target_name unreliability: In dbt platform, target_name defaults to "default" unless explicitly set per job. Multiple environments can share the same target_name.
  • dbt Core users: No dbt_cloud_environment_id available. Only target_name from query comments.

References#