Wren Usage

Wren Engine CLI — Agent Workflow Guide

Version check

Silently fetch https://raw.githubusercontent.com/Canner/wren-engine/main/skills/versions.json. Compare the wren-usage key with this skill's version (from the frontmatter above). If the remote version is newer, notify the user before proceeding:

A newer version of the wren-usage skill is available.

Update with:

```

npx skills add Canner/wren-engine --skill wren-usage

```

The CLI auto-detects your installed agent. To target a specific one, add --agent <name> (e.g., claude-code, cursor, windsurf, cline).

Then continue with the workflow below regardless of update status.

---

Preflight — Verify environment and installation

Goal: Ensure the wren CLI is available before entering any workflow.

Step 1 — Check Python virtual environment

Run python -c "import sys; print(sys.prefix)" (or equivalent) to determine whether a virtual environment is active.

• If no venv is active, warn the user and ask whether to:
• Create one (e.g., python -m venv .venv && source .venv/bin/activate)
• Continue without a venv (not recommended — may pollute global packages)

Step 2 — Check if wren-engine is installed

Run wren --version. If the command is not found or errors:

1. Tell the user that the wren CLI is not installed. 2. Ask if you should help install it. 3. If the user agrees, determine the datasource extra to install:

Auto-detect from project: Check whether the current directory is inside a wren project (look for wren_project.yml up to the repository root). If found, read the active profile with cat ~/.wren/profiles.yml or look for a datasource hint in the project's profile configuration. Extract the datasource type from there.

Ask the user: If no project is detected or no datasource can be inferred, ask the user which database they plan to connect to. Valid extras: postgres, mysql, bigquery, snowflake, clickhouse, trino, mssql, databricks, redshift, spark, athena, oracle. DuckDB is included by default — no extra needed.

4. Install with the detected or chosen extra:

   # DuckDB (no extra needed)
   pip install "wren-engine"

   # Other datasources
   pip install "wren-engine[<datasource>]"

To also enable semantic memory, interactive prompts, and web UI (recommended):

   pip install "wren-engine[<datasource>,main]"
   # or for DuckDB:
   pip install "wren-engine[main]"

5. Verify: wren --version

If wren --version succeeds, proceed to the relevant workflow below.

---

The wren CLI queries databases through an MDL (Model Definition Language) semantic layer. You write SQL against model names, not raw tables. The engine translates to the target dialect.

Two things drive everything:

• Profile — database connection + datasource type, managed via wren profile (stored in ~/.wren/profiles.yml)
• Project — MDL model definitions in YAML, compiled to target/mdl.json via wren context build

The CLI reads the active profile for connection info and datasource. Use wren profile list to see which profile is active, wren profile switch <name> to change it. dry-plan also accepts --datasource / -d for transpile-only use without a profile.

For memory-specific decisions, see references/memory.md. For SQL syntax, CTE-based modeling, and error diagnosis, see references/wren-sql.md. For project structure, MDL field definitions, and CLI workflow details, see the documentation.

---

Workflow 1: Answering a data question

Step 1 — Gather context

If this is the first query in the conversation, also run:

wren context instructions

If it returns content, treat it as rules that override defaults — apply them to all subsequent queries in this session.

Step 2 — Recall past queries

wren memory recall -q "<question>" --limit 3

Use results as few-shot examples. Skip if empty.

Step 2.5 — Assess complexity (before writing SQL)

If the question involves any of the following, consider decomposing:

• Multiple metrics or aggregations (e.g., "churn rate AND expansion revenue")
• Multi-step calculations (e.g., "month-over-month growth rate")
• Comparisons across segments (e.g., "by plan tier, by region")
• Time-series analysis requiring baseline + change (e.g., "retention curve")

Decomposition strategy: 1. Identify the sub-questions (e.g., "total subscribers at start" + "subscribers who cancelled" → churn rate) 2. For each sub-question:

• wren memory recall -q "<sub-question>" — check if a similar pattern exists
• Write and execute a simple SQL
• Note the result

3. Combine sub-results to answer the original question

When NOT to decompose:

• Single-table aggregation with GROUP BY — just write the SQL
• Simple JOINs that the MDL relationships already define
• Questions where memory recall returns a near-exact match

This is a judgment call, not a rigid rule. If you're confident in a single query, go ahead. Decompose when the SQL would be hard to debug if it fails.

Step 3 — Write, verify, and execute SQL

For simple queries (single table or simple MDL-defined JOINs, straightforward aggregation): Execute directly:

wren --sql 'SELECT c_name, SUM(o_totalprice) FROM orders
JOIN customer ON orders.o_custkey = customer.c_custkey
GROUP BY 1 ORDER BY 2 DESC LIMIT 5'

For complex queries (non-trivial JOINs not covered by MDL relationships, subqueries, multi-step logic): Verify first with dry-plan:

wren dry-plan --sql 'SELECT ...'

Check the expanded SQL output:

• Are the correct models and columns referenced?
• Do the JOINs match expected relationships?
• Are CTEs expanded correctly?

If the expanded SQL looks wrong, fix before executing. If it looks correct, proceed:

wren --sql 'SELECT ...'

SQL rules:

• Target MDL model names, not database tables
• Write dialect-neutral SQL — the engine translates

Step 4 — Store and continue

After successful execution, store the query by default:

wren memory store --nl "<user's original question>" --sql "<the SQL>"

Skip storing only when:

• The query failed or returned an error
• The user said the result is wrong
• The query is exploratory (SELECT * ... LIMIT N without analytical clauses)
• There is no natural language question — just raw SQL
• The user explicitly asked not to store

The CLI auto-detects exploratory queries — if you see no store hint after execution, the query was classified as exploratory.

---

Workflow 2: Error recovery

"table not found"

1. Verify model name: wren memory fetch -q "<name>" --type model --threshold 0 2. Check MDL exists: ls target/mdl.json (or wren context show) 3. Verify column: wren memory fetch -q "<column>" --model <name> --threshold 0

Connection error

1. Check active profile: wren profile debug 2. Verify datasource and connection fields are correct 3. Test: wren --sql "SELECT 1" 4. Valid datasource values: postgres, mysql, bigquery, snowflake, clickhouse, trino, mssql, databricks, redshift, spark, athena, oracle, duckdb 5. If no profile exists, create one: wren profile add --ui (or --interactive / --from-file)

SQL syntax / planning error (enhanced)

Layer 1: Identify the failure point

wren dry-plan --sql "<failed SQL>"

Layer 2A: MDL-level diagnosis (dry-plan failed)

The dry-plan error message tells you exactly what's wrong:

Key principle: Fix ONE issue at a time. Re-run dry-plan after each fix to see if new errors surface.

Layer 2B: DB-level diagnosis (dry-plan OK, execution failed)

The DB error + dry-plan output together pinpoint the issue:

1. Read the dry-plan expanded SQL — this is what actually runs on the DB 2. Compare with the DB error message:

For small models: If the error message is unclear, try simplifying the query to the smallest failing fragment. Execute subqueries independently to isolate which part fails.

For the CTE rewrite pipeline and additional error patterns, see references/wren-sql.md.

---

Workflow 3: Connecting a new data source

1. Add a profile: wren profile add --ui (or --interactive / --from-file) 2. Test connection: wren profile debug 3. Test query: wren --sql "SELECT 1" 4. Initialize project: wren context init 5. Build manifest: wren context build 6. Index: wren memory index 7. Verify: wren --sql "SELECT * FROM <model> LIMIT 5"

---

Workflow 4: After MDL changes

When model YAML files are updated, rebuild and re-index:

# 1. Validate changes
wren context validate

# 2. Rebuild manifest
wren context build

# 3. Re-index schema memory
wren memory index

# 4. Verify
wren --sql "SELECT * FROM <changed_model> LIMIT 1"

---

Command decision tree

Get data back           → wren --sql "..."
See translated SQL only → wren dry-plan --sql "..." (accepts -d <datasource> if no active profile)
Validate against DB     → wren dry-run --sql "..."
Schema context          → wren memory fetch -q "..."
Filter by type/model    → wren memory fetch -q "..." --type T --model M --threshold 0
Store confirmed query   → wren memory store --nl "..." --sql "..."
Few-shot examples       → wren memory recall -q "..."
Index stats             → wren memory status
Re-index after MDL change → wren memory index
Show project context    → wren context show
Rebuild manifest        → wren context build
Check profile           → wren profile debug
Switch profile          → wren profile switch <name>

---

Things to avoid

• Do not guess model or column names — check context first
• Do not store failed queries or queries the user said are wrong
• Do not skip storing successful queries with a clear NL question — default is to store
• Do not re-index before every query — once per MDL change
• Do not pass passwords via --connection-info if shell history is shared — use profiles (wren profile add) or --connection-file

Wren Memory — When to index, context, store, and recall

This reference covers the decision logic for each memory command. The main workflow is in the parent SKILL.md.

---

Schema context: fetch and describe

The hybrid strategy works like this:

• Below 30K characters (~8K tokens): returns the entire schema as structured plain text — the LLM sees complete model-to-column relationships, join paths, and primary keys
• Above 30K characters: returns embedding search results — only the most relevant fragments

CJK-heavy schemas switch to search sooner (~1.5 chars per token vs 4 for English), which is the safe direction.

Override with --threshold:

wren memory fetch -q "revenue" --threshold 50000   # raise for larger context windows

---

Indexing: wren memory index

When to index:

• After updating model YAML files and rebuilding (wren context build)
• When wren memory status shows schema_items: 0 rows
• When wren memory fetch returns stale results (references deleted models)

When NOT to index:

• Before every query — indexing is expensive, do it once per MDL change
• When only using describe or fetch with full strategy — those read the MDL directly

wren memory index

---

Storing queries: wren memory store

Store by default when a query executes successfully and there is a clear natural language question. The default is to store, not to wait for explicit confirmation.

Store (default):

• Query executed successfully, user confirmed the result is correct
• Query executed successfully, user continued with a follow-up (implicit confirmation)
• Query executed successfully, user said nothing but the question had a clear NL description

Do NOT store when:

• The query failed or returned an error
• The user said the result is wrong or asked to fix it
• The query is exploratory / throwaway (SELECT * FROM orders LIMIT 5) — the CLI auto-detects these
• There is no natural language question — just raw SQL
• The user explicitly asked not to store it

wren memory store \
  --nl "top 5 customers by revenue last quarter" \
  --sql "SELECT c_name, SUM(o_totalprice) AS revenue ..." \
  --datasource postgres

The --nl value should be the user's original question, not a paraphrase.

---

Recalling queries: wren memory recall

When to recall:

• Before writing SQL for a new question, especially complex ones
• When the user asks something similar to a past question

wren memory recall -q "monthly revenue by category" --limit 3

Use results as few-shot examples: adapt the SQL pattern to the current question.

---

Full lifecycle example

Session start:
  1. wren memory status → if schema_items is 0: wren memory index

User asks a question:
  2. wren memory recall -q "<question>" --limit 3
  3. wren memory fetch -q "<question>"
  4. Write SQL using recalled examples + schema context
  5. wren --sql "..."

After execution:
  6. Show results to user
  7. Store by default → wren memory store --nl "..." --sql "..."
     User says wrong → fix SQL, do NOT store
     Query failed → do NOT store
     Exploratory query → do NOT store (CLI auto-detects)

---

Housekeeping

wren memory status              # path, table names, row counts
wren memory reset --force       # drop everything, start fresh

All memory commands accept --path DIR to override the default storage directory (<project>/.wren/memory/, falling back to ~/.wren/memory/ outside a project).

Wren SQL — How CTE-Based Modeling Works

Wren Engine rewrites your SQL by injecting CTEs (Common Table Expressions) that expand each MDL model into its underlying database query. Understanding this mechanism helps you diagnose errors and write correct SQL.

---

The rewrite pipeline

Your SQL (target dialect, e.g. Postgres)
  → parse & qualify all column references (sqlglot)
  → identify which models and columns are referenced
  → per model: wren-core expands the model definition → CTE
  → inject model CTEs into your query
  → output final SQL in target dialect

Example: Given an MDL with model orders backed by table public.orders with columns o_orderkey, o_custkey, o_totalprice:

-- You write:
SELECT o_custkey, SUM(o_totalprice) FROM orders GROUP BY 1

-- Engine produces (via dry-plan):
WITH "orders" AS (
  SELECT "public"."orders"."o_orderkey",
         "public"."orders"."o_custkey",
         "public"."orders"."o_totalprice"
  FROM "public"."orders"
)
SELECT o_custkey, SUM(o_totalprice) FROM orders GROUP BY 1

The CTE named "orders" shadows the model name, so the rest of your SQL runs against the CTE as if it were a table.

---

What the rewriter handles

---

SQL rules for writing queries

1. Use model names, not database table names — write FROM orders, not FROM public.orders 2. Write dialect-neutral SQL — the engine translates to the target database dialect 3. Column names must match the MDL — use the names defined in mdl.json, not the underlying database column names 4. Hidden columns are excluded — columns with "isHidden": true are not available in SELECT * 5. Relationship columns are excluded — relationship fields don't appear as selectable columns; use JOINs instead

---

Diagnosing errors with dry-plan

dry-plan shows the expanded SQL without executing it. This separates MDL-level issues from database-level issues.

Step 1 — Run dry-plan

wren dry-plan --sql "SELECT o_custkey, SUM(o_totalprice) FROM orders GROUP BY 1"

Step 2 — Interpret the result

Step 3 — Compare dry-plan output with DB error

When execution fails but dry-plan succeeds, compare them side by side:

# Get the expanded SQL
wren dry-plan --sql "SELECT ..." 2>&1

# Run against DB and capture the error
wren --sql "SELECT ..." 2>&1

Common patterns:

• Type mismatch: The CTE exposes the raw column type; a function may not accept it in the target dialect
• Missing table: The underlying table referenced in the model definition doesn't exist in the database
• Permission denied: The DB user lacks access to the underlying tables
• Syntax difference: Rare — usually means a sqlglot dialect translation gap

---

Fallback behavior

If the rewriter detects no model references in your SQL (e.g. SELECT 1 or queries against raw database tables), it falls back to passing the entire query through wren-core's transform_sql() directly. This means:

• Queries that don't reference any MDL model still work
• The fallback path does NOT use CTE injection — it transforms the whole query at once
• If you expect model expansion but get none, check that your FROM clause uses model names from the MDL