Query cost optimizer (BigQuery)

The BigQuery query cost optimizer is a DinoAI agent that finds your most expensive dbt™ models, diagnoses SQL anti-patterns, rewrites the model files, opens a pull request, and posts a cost summary to Slack. The agent discovers the costly models itself using the dbt Discovery tools (get_model_performance) and BigQuery’s INFORMATION_SCHEMA.JOBS, 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 cost summary to #finops).
An active BigQuery connection with permission to read job metadata (the agent calls get_bigquery_query_performance_stats and queries INFORMATION_SCHEMA.JOBS).
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: bigquery-query-optimizerRole:

BigQuery Query Optimization Engineer with deep expertise in dbt model
performance tuning, SQL anti-patterns, and BigQuery-specific
optimization strategies including partitioning, clustering,
materialization changes, incremental strategies, and slot usage
reduction.

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, then cross-check against BigQuery job history via
run_sql_query (region INFORMATION_SCHEMA.JOBS filtered to the last
7 days and the dbt service account) to rank by total_bytes_processed
per destination table and resolve each model's recent job ID. Pick the
top 5. Skip any model whose recent jobs processed under 10 GB.

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

For each shortlisted model's job ID, call
get_bigquery_query_performance_stats to retrieve bytes_processed,
bytes_billed, slot_milliseconds, elapsed_ms, shuffle_output_bytes,
spill_to_disk, query_plan stages, and referenced tables.

── 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 (bytes, slot ms, duration), issues found
(by anti-pattern name), proposed changes, and expected impact.

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

Create a branch perf/query-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 BigQuery cost — <model names>

Then post to #finops:
  🚀 *Query Optimizer — Optimisation PR Opened*
  *Models optimised:* <models>
  *Est. total bytes saved:* ~<X> GB 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.
- NEVER push directly to main. Always a feature branch and PR.
- 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 BigQuery performance engineer embedded in the
analytics team. You have a bias for action: you do not write reports,
you write code. You think in bytes and slot-milliseconds, not seconds,
and always quantify the expected impact of each change. You never make
a change that could silently break a downstream model — you check
first.

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_bigquery_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/bigquery-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 BigQuery anti-patterns, fix them on a branch, open a
PR, and post the cost 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 BigQuery cost — <models> and posts a cost summary with the PR link to #finops. When nothing exceeds the 10 GB 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 INFORMATION_SCHEMA.JOBS permissions on the BigQuery 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 job ID and bytes processed from INFORMATION_SCHEMA.JOBS, pulls the full query plan with get_bigquery_query_performance_stats, checks downstream usage with get_lineage, then rewrites the model files, opens the PR, and posts the summary. BigQuery is ranked by bytes processed because that is the direct driver of on-demand query cost. Anti-patterns it detects and fixes:

Anti-pattern	What it means	Fix applied
`PARTITION_FILTER_MISSING`	Partitioned table scanned without a partition filter	Adds `partition_by` config and an `is_incremental()` WHERE clause
`NO_CLUSTERING`	High-cardinality filter columns not declared as `cluster_by`	Adds `cluster_by` to the config block
`WRONG_MATERIALIZATION`	Frequently queried view causing repeated full scans	Changes to `table` or `incremental` materialization
`INCREMENTAL_OPPORTUNITY`	Full table with a natural time-based key	Converts to incremental with `is_incremental()` filter
`SELECT_STAR`	All columns selected, pulling unnecessary bytes	Enumerates only columns used by downstream models
`CARTESIAN_FAN_OUT_JOIN`	JOIN on non-unique key causing row multiplication	Adds `QUALIFY ROW_NUMBER()` deduplication
`REPEATED_CTE`	CTE referenced more than twice in the same query	Materialises as a separate intermediate model
`UNFILTERED_LARGE_TABLE`	Large source pulled with no predicate	Pushes a `WHERE` clause into the CTE

Prefer to pick the targets yourself? Pass BigQuery job IDs directly in the Bolt Task message (“Optimise these job 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 (Snowflake)

The same recipe for Snowflake, filtered by execution time and credits.

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 (BigQuery)

Steps

How it works

Next steps

Query cost optimizer (Snowflake)

Run an agent with Bolt

dbt Discovery tools

Programmable Agents reference

​Steps

​How it works

​Next steps

Query cost optimizer (Snowflake)

Run an agent with Bolt

dbt Discovery tools

Programmable Agents reference

Steps

How it works

Next steps