AI Features

Rocky includes an AI layer that uses Claude to generate models, explain existing code, propagate schema changes, and create test assertions. Every AI-generated artifact passes through the compiler before it can be used — the compile-verify loop ensures correctness regardless of LLM output quality.

1. Setup

Rocky’s AI features require an Anthropic API key. Set it in your environment:

export ANTHROPIC_API_KEY="sk-ant-..."

Add this to your shell profile (~/.zshrc, ~/.bashrc) for persistence:

echo 'export ANTHROPIC_API_KEY="sk-ant-..."' >> ~/.zshrc
source ~/.zshrc

Rocky uses claude-sonnet-4-6 by default. No additional configuration is needed.

Token budget (optional)

Each AI command runs a compile-verify retry loop (up to 3 attempts). To bound the worst-case spend when the LLM produces runaway responses, Rocky enforces a cumulative output-token budget across retries. Set the budget in rocky.toml if the default of 4096 is not enough for your project:

[ai]
max_tokens = 8192

max_tokens doubles as the per-request cap on the Anthropic Messages API and as the cumulative output-token ceiling — when the running total of output_tokens across retry attempts exceeds this value, Rocky fail-stops with a TokenBudgetExceeded error instead of paying for another retry. See [ai] in the configuration reference.

Verify the setup

rocky ai "hello world model that selects 1 as id"

If the API key is missing or invalid, you will see:

Error: ANTHROPIC_API_KEY not set. Set it to use `rocky ai`.

2. Generate a Model from Intent

The rocky ai command generates a model from a natural language description and writes both the body file and a matching .toml sidecar to the models directory. The emitted sidecar carries the materialization strategy + target coordinates, so Rocky’s loader picks the generated model up on the next rocky apply without manual editing.

rocky ai "monthly revenue by product category from orders and products, completed orders only"

Output:

Generated model: monthly_category_revenue (rocky)
Attempts: 1
Wrote: models/monthly_category_revenue.rocky
Wrote: models/monthly_category_revenue.toml

The emitted sidecar defaults to [strategy] type = "full_refresh" and [target] = generated.ai.<model_name>. Override either with the flags below.

Choose the output format

By default, Rocky generates Rocky DSL. Use --format sql for standard SQL (still emits both a .sql body and a .toml sidecar):

rocky ai "monthly revenue by product category" --format sql

SELECT
    DATE_TRUNC('month', o.order_date) AS month,
    p.category,
    SUM(o.quantity * o.unit_price) AS total_revenue,
    COUNT(*) AS order_count
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY DATE_TRUNC('month', o.order_date), p.category

Pick a materialization + target

Pair --materialization with --watermark for incremental models, and --target to land the output in a real catalog/schema rather than the generated.ai.* default:

rocky ai "daily order facts from stg_orders" \
  --materialization incremental --watermark order_date \
  --target analytics.marts.fct_orders_daily

The emitted sidecar (models/fct_orders_daily.toml) then carries the parsed materialization, watermark, and target:

name = "fct_orders_daily"

[strategy]
type = "incremental"
timestamp_column = "order_date"

[target]
catalog = "analytics"
schema  = "marts"
table   = "fct_orders_daily"

Pass --overwrite to replace an existing body or sidecar at the destination — without it, the command fails loudly rather than silently clobber user-authored models. See rocky ai for the full flag table, including the v1 --materialization merge limitation.

JSON output

For programmatic use:

rocky ai "monthly revenue by category" -o json

{
  "version": "1.30.0",
  "command": "ai",
  "intent": "monthly revenue by category",
  "format": "rocky",
  "name": "monthly_category_revenue",
  "source": "from orders\njoin products on ...",
  "attempts": 1,
  "body_path": "models/monthly_category_revenue.rocky",
  "sidecar_path": "models/monthly_category_revenue.toml"
}

3. Schema-Grounded Prompts

Before sending your intent to the LLM, rocky ai compiles the project and injects the resulting typed schemas into the prompt. The LLM sees the real column names, types, and model graph — so generated code references columns that actually exist, with the types they actually have.

rocky ai "monthly revenue by category" --models models

--models <PATH> points at the directory to compile for grounding (default models). If the directory doesn’t exist or fails to compile, rocky ai degrades gracefully to unschema’d generation rather than failing outright — the compile-verify loop (next section) still guards the output.

A second mechanism runs after generation: rocky ai’s ValidationContext typechecks the candidate SQL against the live project graph. If the LLM references a model or column that doesn’t exist, the diagnostic flows back into the compile-verify loop as retry feedback instead of reaching your files.

Rocky’s typechecker is lenient on unresolved columns today, so schema grounding in the prompt is the primary mechanism preventing hallucinated columns; project-aware validation catches hallucinated model references in the project graph.

4. The Compile-Verify Loop

This is Rocky’s key safety mechanism for AI-generated code. Every generated model is compiled before it is shown to you:

Intent
  |
  v
LLM generates code
  |
  v
Compiler type-checks ──> Pass? ──> Output to user
  |
  | Fail
  v
Error feedback sent to LLM
  |
  v
LLM retries with error context
  |
  v
Compiler type-checks again (up to 3 attempts)

The compiler catches:

• Syntax errors: Invalid SQL or Rocky DSL syntax
• Type mismatches: Column used as wrong type (e.g., string compared to integer)
• Missing references: Column or table does not exist in the project
• Invalid functions: Unrecognized SQL functions or wrong argument counts

If all attempts fail, Rocky reports the best attempt and the remaining errors. No AI-generated code reaches your warehouse without passing the type checker.

Why this matters

LLMs occasionally generate plausible-looking SQL that is semantically wrong — a column name that does not exist, a JOIN on mismatched types, or an aggregation without a GROUP BY. The compile-verify loop catches these errors before you see the output.

5. Add Intent to Existing Models

Intent is a natural language description stored in the model’s TOML config. It serves two purposes:

1. Documentation: Explains what the model does in business terms
2. AI context: Powers ai-sync and ai-test with understanding of the model’s purpose

Write intent manually

Add an intent field to any model’s TOML config:

name = "fct_daily_revenue"
intent = """
Calculate daily revenue by product category.
Join orders with products, filter to completed orders only.
Revenue is quantity * unit_price after discounts.
Grain: one row per date per category.
"""
depends_on = ["stg_orders", "dim_products"]

[strategy]
type = "incremental"
timestamp_column = "order_date"

[target]
catalog = "analytics"
schema = "warehouse"
table = "fct_daily_revenue"

Generate intent with AI

Instead of writing intent manually, let Rocky analyze your SQL and generate it:

# Explain a single model
rocky ai-explain fct_daily_revenue --save

Saved intent for fct_daily_revenue: Calculate daily revenue aggregated by product
category. Joins staging orders with product dimension on product_id. Filters to
completed orders only. Revenue computed as quantity * unit_price * (1 - discount).
Grain: one row per order_date per category.

Bulk-generate intent for all models

rocky ai-explain --all --save --models models

This processes every model that does not already have an intent field. Models that already have intent are skipped. The --save flag writes the generated description directly to each model’s .toml sidecar.

Review without saving

Omit --save to preview the generated intent without modifying files:

rocky ai-explain --all --models models

fct_daily_revenue: Calculate daily revenue aggregated by product category...
dim_customers: Customer dimension with lifetime value and segment classification...
stg_orders: Stage raw Shopify orders selecting order_id, customer, date, amount...

Review and refine the descriptions before saving. The AI-generated intent is a starting point — you know your business domain better than the LLM.

6. Schema Change Sync

When upstream schemas change (columns renamed, types changed, new columns added), rocky ai-sync proposes updates to downstream models based on their stored intent.

Preview proposals (dry run)

rocky ai-sync --models models

Model: fct_daily_revenue (intent: "Calculate daily revenue by product category...")
- unit_price -> unit_price_local
  revenue = quantity * unit_price * (1 - discount)
  revenue = quantity * unit_price_local * (1 - discount)

Run with --apply to update models.

Apply proposals

rocky ai-sync --models models --apply

Updated: models/fct_daily_revenue.sql
Updated: models/fct_monthly_summary.sql

Filter to a specific model

rocky ai-sync --models models --model fct_daily_revenue

Only process models with intent

rocky ai-sync --models models --with-intent

Models without intent are skipped because the AI has no context to make intelligent update proposals.

How sync works

1. Rocky compiles the project and builds the semantic graph
2. For each model with intent, it identifies upstream schema changes (renamed columns, type changes, new columns)
3. The LLM receives the model’s SQL, its intent, and the upstream changes
4. The LLM proposes a minimal diff that preserves the model’s intent while adapting to the schema change
5. The proposed code is compiled to verify correctness
6. With --apply, the updated SQL is written back to the file

Example scenario

Suppose an upstream model stg_orders renames unit_price to unit_price_local as part of a currency normalization effort:

1. rocky compile fails — downstream models reference unit_price which no longer exists

2. rocky ai-sync --models models detects the rename and proposes updates:

   --- models/fct_daily_revenue.sql
   +++ models/fct_daily_revenue.sql
   @@ -4,7 +4,7 @@
    SELECT
        o.order_date,
        p.category,
   -    SUM(o.quantity * o.unit_price * (1 - o.discount)) as revenue,
   +    SUM(o.quantity * o.unit_price_local * (1 - o.discount)) as revenue,
        COUNT(*) as order_count
    FROM stg_orders o

3. Review the diff and apply with --apply

4. rocky compile passes again

7. Generate Test Assertions

rocky ai-test generates test assertions based on each model’s SQL logic and intent:

rocky ai-test fct_daily_revenue

Tests for fct_daily_revenue:
  - grain_uniqueness: No duplicate rows per date and category
  - revenue_positive: Revenue should be non-negative for completed orders
  - no_future_dates: Order dates should not be in the future

Save tests to disk

rocky ai-test fct_daily_revenue --save

Saved 3 tests for fct_daily_revenue

Tests are saved to the tests/ directory as SQL files that return 0 rows on success:

-- tests/fct_daily_revenue/grain_uniqueness.sql
-- No duplicate rows per date and category
SELECT order_date, category, COUNT(*) as n
FROM fct_daily_revenue
GROUP BY order_date, category
HAVING n > 1

Generate tests for all models

rocky ai-test --all --save --models models

Run the generated tests

Tests run with rocky test (DuckDB) and are included in rocky ci:

rocky test --models models

Testing 12 models...

  All 12 models passed

  Result: 12 passed, 0 failed

8. Intent in the IDE

When using the VS Code extension, models with intent get enhanced IDE features:

• Hover: Shows the intent description above the column list when hovering over a model name
• Document Symbols: Intent appears as the first child of the model in the Outline panel
• Diagnostics: The compiler warns when intent mentions columns that do not exist in the model’s output schema

9. Best Practices for Intent Descriptions

Good intent descriptions make ai-sync and ai-test significantly more effective. Here are guidelines:

State the grain

Specify what one row represents. This is the most important piece of context:

Grain: one row per customer per month

Name key columns and their business meaning

Do not just list column names — explain what they mean:

customer_lifetime_value is the total revenue from all completed orders for this customer

Describe filters and their purpose

Explain why data is filtered, not just what the filter is:

Filter to completed orders only (exclude cancelled, pending, refunded) because
revenue should only count fulfilled transactions

Explain aggregation logic precisely

Be specific about how calculated columns are computed:

Revenue is quantity * unit_price * (1 - discount_pct), aggregated per day per category

Mention source models being joined

Join stg_orders with dim_products on product_id to get category information

Good example

intent = """
Calculate daily revenue by product category.
Join stg_orders with dim_products on product_id.
Filter to completed orders only (exclude cancelled, pending).
Revenue = quantity * unit_price * (1 - discount_pct).
Grain: one row per order_date per product_category.
"""

Bad example

intent = "Revenue model."

This is too vague for ai-sync to make intelligent update proposals or for ai-test to generate meaningful assertions.

10. AI Commands Reference

Command	Description
rocky ai "<intent>"	Generate a model from natural language
rocky ai "<intent>" --format sql	Generate as standard SQL instead of Rocky DSL
rocky ai-explain <model>	Generate intent for a single model
rocky ai-explain --all --save	Generate and save intent for all models without intent
rocky ai-sync	Preview schema change proposals
rocky ai-sync --apply	Apply proposed schema changes
rocky ai-sync --model <name>	Sync a specific model
rocky ai-sync --with-intent	Only process models that have intent
rocky ai-test <model>	Generate tests for a single model
rocky ai-test --all --save	Generate and save tests for all models