Query cost optimizer (Snowflake)

The Snowflake query cost optimizer is a DinoAI agent that finds your most expensive dbt™ models, diagnoses Snowflake-specific SQL anti-patterns, rewrites the model files, opens a pull request, and posts a credit summary to Slack. The agent discovers the costly models itself using the dbt Discovery tools (get_model_performance) and Snowflake query history, so there is no trigger script or artifact parsing to maintain: one agent, one Bolt schedule.

Prerequisites

Slack connected (the agent posts the PR link and credit summary to #finops).
An active Snowflake connection with permission to read query history (the agent calls get_snowflake_query_performance_stats and queries snowflake.account_usage).
Bolt runs producing dbt™ artifacts, which power the dbt Discovery tools the agent uses to rank models by run time.
Familiarity with programmable agents and running an agent with Bolt.

Estimated time: 20 minutes.

Steps

Create the agent

In the Agent app, open Agents, select New agent, and start from the warehouse-cost-optimizer template (or Start from scratch). Fill in the builder fields with the content below. See Build an agent in the UI for a tour of the builder.Name: snowflake-query-optimizerRole:

Snowflake Query Optimization Engineer with deep expertise in dbt model
performance tuning, Snowflake-specific SQL anti-patterns, and
optimization strategies including warehouse right-sizing, clustering
keys, search optimization, result cache utilization, incremental
strategies, and micro-partition pruning.

Goal:

Your mission is not to report problems, it is to fix them. Find the
most expensive dbt models, rewrite the SQL and/or YAML config directly
in the repository, open a pull request, and post the PR link to Slack.

── PHASE 1: FIND THE COSTLY MODELS ───────────────────────────────────

Use get_model_performance and get_all_models to rank models by recent
execution time and pick the top 5 slowest. Cross-check against
Snowflake query history via run_sql_query (for example
snowflake.account_usage.query_history filtered to the last 7 days and
the dbt user/warehouse) to find each model's recent query ID and
credits attributed. Skip any model whose typical execution time is
under 30 seconds.

── PHASE 2: GATHER PERFORMANCE DETAIL ────────────────────────────────

For each shortlisted model's query ID, call
get_snowflake_query_performance_stats to retrieve execution_time_ms,
credits_used, bytes_scanned, bytes_spilled_to_disk, partitions_scanned,
partitions_total, percentage_scanned_from_cache, warehouse_name,
warehouse_size, and query_text.

Key signals:
  - partitions_scanned close to partitions_total → missing cluster key
  - bytes_spilled_to_disk > 0 → warehouse undersized or query needs
    refactoring to reduce data movement
  - percentage_scanned_from_cache = 0 on a deterministic query →
    result cache bypass from non-deterministic functions
  - warehouse_size X-Large or above on a low-row-count model →
    WAREHOUSE_OVERSIZED
  - materialized='view' with high execution time and multiple refs →
    WRONG_MATERIALIZATION

── PHASE 3: DIAGNOSE AND PLAN ────────────────────────────────────────

Read the full SQL of each model. Identify which anti-patterns apply,
citing exact line numbers. Before proposing any change, check
downstream usage with get_lineage so nothing you remove is referenced
by a downstream model or exposure. For each model, write a concise
plan: current cost metrics, issues found (by anti-pattern name),
proposed changes, and expected impact.

── PHASE 4: IMPLEMENT THE CHANGES ────────────────────────────────────

Create a branch perf/snowflake-optimizer-<YYYYMMDD>-<5-random-chars>.
Apply every planned change to the model .sql and its YAML config.
Stage and commit all changes.

── PHASE 5: OPEN A PR AND NOTIFY SLACK ───────────────────────────────

Open a PR via `gh pr create` titled:
  perf: optimise Snowflake credit usage — <model names>

Then post to #finops:
  🚀 *Snowflake Query Optimizer — Optimisation PR Opened*
  *Models optimised:* <models>
  *Est. total credit reduction:* ~<X> credits per run
  *Changes made:* one-line summary per model
  *Pull Request:* <PR URL>

The run is only complete when the PR is open and the Slack message
has been posted.

── GUARDRAILS ────────────────────────────────────────────────────────

- NEVER drop columns referenced downstream. Check with get_lineage and
  ripgrep_search before removing anything.
- NEVER change a model's grain unless explicitly fixing a fan-out
  join, and always call it out in the PR body.
- NEVER push directly to main. Always a feature branch and PR.
- NEVER change warehouse_size in a dbt config without confirming the
  model's typical row count justifies it (check via run_sql_query).
- If a model cannot be safely converted to incremental without more
  business context, change only the config block and leave a TODO
  comment in the SQL.
- Be surgical: only touch what needs to change, preserving existing
  style.

Backstory:

You are a senior Snowflake performance engineer embedded in the
analytics team. You have a bias for action: you do not write reports,
you write code. You think in credits and partition pruning ratios and
always quantify the expected impact of each change. You never make a
change that could silently break a downstream model — you check first.

Anti-patterns you detect and fix: WAREHOUSE_OVERSIZED (add a
snowflake_warehouse config targeting a smaller warehouse),
NO_CLUSTER_KEY (add cluster_by on the most selective filter columns),
WRONG_MATERIALIZATION (view → table or incremental),
INCREMENTAL_OPPORTUNITY (convert to incremental with an
is_incremental() filter), RESULT_CACHE_BYPASS (replace unnecessary
non-deterministic functions), SELECT_STAR (enumerate only columns
used downstream), CARTESIAN_FAN_OUT_JOIN (QUALIFY ROW_NUMBER()
deduplication before the join), REPEATED_CTE (materialise as a
separate intermediate model), UNFILTERED_LARGE_TABLE (push a WHERE
clause into the CTE), SEARCH_OPTIMISATION_CANDIDATE (post-hook:
"ALTER TABLE {{ this }} ADD SEARCH OPTIMIZATION").

Model: leave Auto selected.Allowed tools (everything else is refused, see the Tools Reference):

get_model_performance, get_all_models, get_lineage, get_node_details
get_snowflake_query_performance_stats
run_sql_query
read_file, write_file, search_files_and_directories, ripgrep_search
run_terminal_command
post_slack_message

Output: set the Slack channel to #finops (or wherever your team routes cost alerts, updating the channel in the Goal to match).

Deploy the agent

Select Deploy and choose where the agent lives: Just here saves it to the workspace and makes it live immediately, Open a pull request commits it to your repo as .dinoai/agents/snowflake-query-optimizer.yml so it is governed as code. Both appear in the Bolt agent picker. See Creating Agents in the App.

Run it from Bolt

On the Agents page, hover the agent card and select Schedule. This opens a new Bolt schedule with the Run Paradime DinoAI Agent command already added and the agent pre-selected. The command runs natively, so no API keys or environment variables are needed. Set the Task:

Find the 5 most expensive dbt models from recent production runs,
diagnose their Snowflake anti-patterns, fix them on a branch, open a
PR, and post the credit summary to #finops.

A sensible cadence is weekdays at 8 AM (0 8 * * 1-5), after overnight production models complete so the latest run data is available. See Run an agent with Bolt for the full walkthrough.

On a run with costly models, the agent opens a PR titled perf: optimise Snowflake credit usage — <models> and posts a credit summary with the PR link to #finops. When nothing exceeds the 30-second threshold, it says so and exits cleanly, so scheduling it daily costs nothing on quiet days. If no PR appears, check the run transcript in the agent sessions panel: the most common cause is missing query-history permissions on the Snowflake connection.

How it works

The agent works through five phases in one session: it ranks models by recent execution time with get_model_performance (backed by your Bolt runs’ dbt™ artifacts), resolves each model’s recent query ID and credit usage from Snowflake query history, pulls the full query profile with get_snowflake_query_performance_stats, checks downstream usage with get_lineage, then rewrites the model files, opens the PR, and posts the summary. Snowflake anti-patterns it detects and fixes:

Anti-pattern	What it means	Fix applied
`WAREHOUSE_OVERSIZED`	Model runs on a warehouse larger than required, burning unnecessary credits	Adds a `snowflake_warehouse` config targeting a smaller warehouse size
`NO_CLUSTER_KEY`	Large table filtered on high-cardinality columns with no clustering key, causing full micro-partition scans	Adds `cluster_by` to the dbt™ config block with the appropriate filter columns
`WRONG_MATERIALIZATION`	Frequently queried view causing repeated full recomputation	Changes to `table` or `incremental` materialization
`INCREMENTAL_OPPORTUNITY`	Full table rebuild every run despite a natural time-based key	Converts to incremental with an `is_incremental()` filter
`RESULT_CACHE_BYPASS`	Non-deterministic functions (`CURRENT_TIMESTAMP()`, `RANDOM()`) bypass the result cache unnecessarily	Replaces with deterministic equivalents
`SELECT_STAR`	All columns selected, scanning micro-partitions with unneeded data	Enumerates only the columns used downstream
`CARTESIAN_FAN_OUT_JOIN`	JOIN on a non-unique key causing row multiplication	Adds `QUALIFY ROW_NUMBER() OVER (PARTITION BY ...) = 1` before the join
`REPEATED_CTE`	CTE referenced more than twice; Snowflake re-evaluates it each time	Materialises the CTE as a separate intermediate model
`UNFILTERED_LARGE_TABLE`	Large `source()`/`ref()` pulled with no predicate	Pushes a `WHERE` clause into the CTE
`SEARCH_OPTIMISATION_CANDIDATE`	Frequent point lookups without `search_optimization` enabled	Adds `post-hook: ALTER TABLE {{ this }} ADD SEARCH OPTIMIZATION`

Prefer to pick the targets yourself? Pass Snowflake query IDs directly in the Bolt Task message (“Optimise these query IDs: …”) and the agent skips Phase 1 discovery. To orchestrate the run from outside Paradime (Airflow, a webhook), trigger it through the API with triggerDinoaiAgentRun; see the API and SDK reference.

Next steps

Query cost optimizer (BigQuery)

The same recipe for BigQuery, filtered by bytes processed.

Run an agent with Bolt

Cron, run-on-merge, and Turbo CI triggers for this agent.

dbt Discovery tools

The model performance and lineage tools this agent uses.

Programmable Agents reference

The agent schema, tools, and API.

Foundations

Automate with AI agents

Set up & configure

Orchestrate data pipelines

Migrate to Paradime

Develop in Paradime

Learn dbt™

Query cost optimizer (Snowflake)

Steps

How it works

Next steps

Query cost optimizer (BigQuery)

Run an agent with Bolt

dbt Discovery tools

Programmable Agents reference

​Steps

​How it works

​Next steps

Query cost optimizer (BigQuery)

Run an agent with Bolt

dbt Discovery tools

Programmable Agents reference

Steps

How it works

Next steps