Cortex Code CLI can optimize your dbt project in one prompt.

Optimize Your dbt Project in One Prompt with Cortex Code CLI

Why this matters for builders

Cortex Code CLI is Snowflake’s terminal-based AI coding agent that now understands dbt projects. It lets you analyze run_results.json, identify slow models, suggest concrete performance fixes, and flag unused models using a single natural-language prompt.

The recent extension adds first-class support for dbt (and Apache Airflow), giving data engineers a secure, context-aware assistant that works directly in the terminal or IDE. Instead of manually digging through query profiles and lineage graphs, you get an AI pair-programmer that turns minutes of investigation into actionable recommendations—moving you from bottleneck detection to optimized models faster than ever.

When to use it

• Your dbt project has grown beyond 50 models and run times are creeping up
• You want to quickly audit an inherited project for performance and dead code
• You need to prepare for a cost review with the data platform team
• You’re onboarding a new team member and want them to understand the slowest parts immediately
• You run weekly “dbt health checks” and want to automate the analysis step

The full process

Define the goal

Start by writing a one-sentence success criterion.

Example goal:
“Reduce total dbt runtime by at least 25% in the next release by fixing the three slowest models and removing at least two unused models, validated against production run_results.json.”

Capture this in a README.md or OPTIMIZATION.md so every subsequent prompt references the same north star.

Shape the spec/prompt

Good prompts are specific, reference real artifacts, and ask for structured output.

Starter prompt template (copy-paste ready):

cortex code "Analyze the attached run_results.json from our dbt production run.
1. Identify the 5 slowest models by execution time.
2. For each slow model, suggest specific SQL or config improvements (materialization, incremental strategy, clustering keys, macro usage, etc.).
3. Scan the project and list any models that have not been executed in the last 30 days or have zero downstream references.
4. Output in markdown with sections: Slow Models, Recommended Fixes, Unused Models, Next Actions.
Use the dbt manifest.json and schema.yml files in the current directory as context."

Run this from the root of your dbt project where target/run_results.json, target/manifest.json, and your model folders are present.

Scaffold the workspace

Before invoking the CLI, prepare a clean environment:

1. Make sure you are on the latest Cortex Code CLI (check official docs for install).
2. Run a fresh dbt build or dbt run --store-failures in production-like settings to generate fresh run_results.json.
3. Create a new branch: git checkout -b dbt-optimization-cortex.
4. Add the optimization ticket to your tracking system with the goal defined above.

Implement with the assistant

Run the prompt above. Cortex Code CLI will:

• Parse the JSON run results
• Cross-reference with the manifest for model metadata and lineage
• Generate concrete suggestions

Review every suggestion before accepting. Common high-impact fixes it surfaces:

• Changing table to incremental with a proper is_incremental() macro
• Adding clustering keys on high-cardinality filter columns
• Replacing expensive Jinja loops with dbt_utils.star or generate_schema_name macros
• Converting repeated CTEs into materialized intermediate models

Example output snippet you might receive:

### Slow Models
- `fct_order_items` – 14m 22s (materialized as table)
  → Recommendation: Switch to incremental + merge strategy. Add clustering on `order_date`.
- `dim_customers` – 9m 41s
  → Recommendation: Add clustering key on `customer_id` and pre-aggregate `lifetime_value`.

Use the CLI iteratively. After reviewing the first response, follow up with:

cortex code "Implement the incremental version of fct_order_items. Keep the same tests and column names. Show me the diff."

Validate changes

Never ship AI-generated models without validation.

Checklist:

• Run dbt compile and fix any Jinja errors
• Execute dbt test --select tag:optimization (or the relevant subset)
• Compare run times before/after using dbt run --profile target on a staging environment
• Validate data correctness with a row-count and checksum check:

  -- validation.sql
  SELECT 
    'before' as version, count(*) as rows, sum(amount) as total 
  FROM analytics.fct_order_items_before
  UNION ALL
  SELECT 
    'after' as version, count(*) as rows, sum(amount) as total 
  FROM analytics.fct_order_items;
  

• Check for unused models: confirm with dbt ls --resource-type model --select +unused_model or by inspecting lineage in Snowflake.

If Cortex flags a model as unused, verify it really has no downstream BI dependencies before deleting.

Ship safely

1. Open a pull request with a clear title: “chore: dbt optimization round 1 – Cortex Code suggestions”
2. Include before/after runtime numbers in the PR description.
3. Tag the data platform and analytics engineering reviewers.
4. Merge only after at least one successful production-like run.
5. Monitor the next scheduled run in your orchestrator (dbt Cloud, Airflow, etc.) and set a 7-day alert to revisit if regressions appear.

Pitfalls and guardrails

### What if Cortex suggests a change that breaks business logic?
Always treat suggestions as starting points. Ask the CLI to explain the reasoning, then manually validate the logic. Run differential tests against a copy of production data.

### What if the run_results.json is from a partial run?
The analysis will be incomplete. Force a full dbt run or dbt build first. You can also prompt: “Only consider models that executed in this full run.”

### What if I don’t have Snowflake?
Cortex Code CLI is currently tied to Snowflake accounts for the heavy lifting, but the CLI itself runs locally. Check the official docs for the latest authentication and supported platforms.

### What if the model is slow because of source data volume, not SQL?
The CLI can detect this if you also give it source freshness or row-count stats. Extend your prompt: “Also analyze source row counts from manifest.json and flag models where the bottleneck is likely upstream data volume.”

What to do next

After the first optimization round:

• Add the best-performing prompt to your team’s dbt-healthcheck.md playbook
• Schedule a recurring “Cortex Optimization Friday” every two weeks
• Export the unused models list into your data catalog as “candidates for deprecation”
• Measure total dbt runtime before and after each round—track it in a simple dashboard
• Explore the new Airflow support for your DAGs once the dbt layer is healthy

Sources

• Snowflake extends Cortex Code CLI to dbt and Apache Airflow to streamline data engineering workflows | InfoWorld (https://www.infoworld.com/article/4136429/snowflake-extends-cortex-code-cli-to-dbt-and-airflow-to-streamline-data-engineering-workflows.html)
• Snowflake Cortex Code CLI adds dbt and Apache Airflow support for AI-powered data pipelines – The New Stack (https://thenewstack.io/snowflake-cortex-code-dbt-airflow/)
• Original announcement: https://bit.ly/4unsoP4
• Cortex Code CLI product page and documentation (check official Snowflake docs for latest installation and usage)

(Word count: 928)