optimize-cortex-agent

name

optimize-cortex-agent

description

This goes through a workflow to guide AI assistants through optimizing Snowflake Cortex Agents for production readiness through systematic evaluation, improvement, and generalization. The process uses AI reasoning combined with human domain expertise to achieve significant accuracy improvements. Use this workflow when: - Preparing an agent for production deployment - Agent has known accuracy or performance issues - Need to validate agent behavior systematically - Want to ensure agent generalizes beyond test cases

Optimize Cortex Agent

Required Access & Tools

Snowflake Access:

• Connection to Snowflake account with Cortex Agents access
• Permissions to query agent schemas and run evaluations
• Ability to modify agent configurations (for updates)

Essential Scripts: (in scripts/ directory)

• get_agent_config.py - Extract agent configuration
• run_evaluation.py - Execute evaluation questions in batch from any table or SQL query
• create_or_alter_agent.py - Create or alter/modify agents
• test_agent.py - Test individual questions for debugging
• agent_events_explorer.py - Interactive Streamlit app to explore agent events, annotate responses, and create evaluation datasets
• fetch_events_from_event_table.py - Fetch events from AI Observability Event Table
• load_eval_data_from_json.py - Load evaluation dataset JSON into Snowflake table

Whenever running scripts, make sure to use uv.

Human Collaboration:

• Domain expert availability (2-3 sessions, 2-3 hours each)
• Expert should understand agent's domain and expected behavior

Tracking changes: Use the agent-system-of-record skill to track changes. Follow its organizational protocol for workspace structure, versioning, and file organization.

Progress Tracking (Required)

CRITICAL: Use the TodoWrite tool to track progress through this workflow. Update todo status as you complete each phase.

When starting this workflow, create todos for ALL phases upfront:

Phase 1: Agent Discovery & Workspace Setup [in_progress]
Phase 2: Evaluation Dataset Creation [pending]
Phase 3: Baseline Evaluation [pending]
Phase 4: Instruction Improvements [pending]
Phase 5: Overfitting Detection [pending]
Phase 6: Generalization & Validation [pending]

Rules for todo progression:

• Mark current phase as in_progress when starting it
• Mark phase as completed only when ALL deliverables are achieved
• Move to next phase by marking it in_progress
• Update todos IMMEDIATELY when phase status changes - do not batch updates

Phase transition triggers:

• Phase 1 → 2: Workspace created, agent config extracted, optimization log initialized
• Phase 2 → 3: Evaluation dataset ready (15-20 questions with expected answers)
• Phase 3 → 4: Baseline evaluation complete, failure patterns identified and categorized
• Phase 4 → 5: Instruction improvements applied, re-evaluation shows improvement
• Phase 5 → 6: Overfitting issues identified and prioritized
• Phase 6 → Done: Generalized instructions validated, deployment summary created

---

The Optimization Process

Phase 1: Agent Discovery, Workspace Setup & Configuration Extraction

Goal: Identify which agent to optimize, establish versioned workspace structure, and extract current configuration.

What You Should Do:

1. Discover the agent:

   Check if user already has agent in mind:

   Ask user:

   Do you already have a specific agent you want to optimize?
   
   If yes, please provide:
   - Agent name
   - Database and schema where it's located
   
   If no, I can query Snowflake to show available agents.
   

   If user doesn't have agent in mind, query Snowflake for available agents:

   -- Find agents in accessible schemas
   SELECT agent_name, agent_schema, created_on, comment
   FROM <DATABASE>.INFORMATION_SCHEMA.CORTEX_AGENTS;
   
   -- For each agent, get tool count
   SELECT agent_name, COUNT(*) as tool_count
   FROM <DATABASE>.INFORMATION_SCHEMA.CORTEX_AGENT_TOOLS
   GROUP BY agent_name;
   

   Present findings to user:

   • List agents by database.schema
   • Show tool count, last modified date, description
   • Ask which agent they want to optimize

2. Confirm workspace location and production status:

   • Fully qualified agent name (DATABASE.SCHEMA.AGENT)
   • Workspace directory (default: current dir + FQN with underscores)
   • Is this a production agent? If yes, ask user for the fully qualified clone name (DATABASE.SCHEMA.CLONE_AGENT_NAME) where they want the clone created.

3. Create workspace:

   AGENT_FQN="DATABASE.SCHEMA.AGENT"
   AGENT_DIR_NAME=$(echo "$AGENT_FQN" | tr '.' '_')
   WORKSPACE_DIR="${WORKSPACE_DIR:-$(pwd)/$AGENT_DIR_NAME}"
   mkdir -p 
   

Deliverables:

• Selected agent name
• Workspace directory structure established
• Production clone created at user-specified FQN (if applicable)
• Extracted configuration in version folder
• Understanding of agent's purpose and known issues
• Optimization log initialized

---

Phase 2: Evaluation Dataset Creation

Goal: Ensure you have 15-20 diverse evaluation questions with expected answers.

Step 0: Check Native Evaluation Availability & Permissions

Before choosing an evaluation approach, check if Native Snowflake Agent Evaluations (preview) is available and you have the required permissions.

LOAD: evaluate-cortex-agent skill and follow the Prerequisites section to:

1. Run SYSTEM$CREATE_EVALUATION_DATASET test to check availability (requires database/schema context)
2. Verify your role has required permissions (if available)

Summary of outcomes:

• "Unknown function" error → Preview NOT available. Use script-based evaluation only.
• "Object does not exist" or parameter error → Preview IS available. Both options available (if permissions are set up).

---

If Native Evaluation is NOT available:

Inform the user:

Native Snowflake Agent Evaluations is a preview feature not enabled on this account.

We'll use script-based evaluation (run_evaluation.py) which provides:
- LLM-as-judge evaluation using SNOWFLAKE.CORTEX.COMPLETE
- Results stored locally in JSON files
- Good for iteration and debugging
- Flexible SQL-based filtering

This approach is fully functional for the optimization workflow.

Skip the evaluation approach choice below and proceed with script-based evaluation.

---

If Native Evaluation IS available, choose your evaluation approach:

Approach	Best For	Dataset Format
Script-based (run_evaluation.py)	Quick iteration, local analysis	question, expected_answer columns
Native Snowflake (evaluate-cortex-agent skill)	Formal benchmarking, Snowsight tracking	INPUT_QUERY, GROUND_TRUTH (OBJECT) columns

Ask user:

Which evaluation approach would you like to use?

A) Script-based evaluation (run_evaluation.py)
   - Results stored locally in JSON files
   - Good for quick iteration and debugging
   - Flexible SQL-based filtering

B) Native Snowflake Agent Evaluations
   - Uses SYSTEM$EXECUTE_AI_OBSERVABILITY_RUN
   - Built-in metrics: correctness, tool_selection_accuracy, logical_consistency
   - Results visible in Snowsight Evaluations UI
   - Better for formal benchmarking and tracking over time

For detailed dataset creation guidance, LOAD: dataset-curation skill

What You Should Do:

1. Check if evaluation dataset already exists:

   Ask user:

   Do you already have an evaluation dataset for this agent? 
   
   For example:
   - A table with test questions and expected answers
   - A spreadsheet or document with evaluation cases
   - Production queries you've been tracking
   - Known failure cases you want to test
   
   If yes, where is it located?
   

2. If evaluation dataset exists:

   • Query the table/file to see existing questions
   • Count total questions available
   • Review coverage across agent capabilities
   • Validate expected answers are specific enough
   • Assess if additional questions are needed

   Present findings:

   I found your existing evaluation dataset:
   - Location: DATABASE.SCHEMA.agent_eval
   - Total Questions: 13
   - Tools covered: X, Y, Z
   - Question types: [list categories you observe]
   
   Coverage looks good/We should add questions for [gaps identified].
   Ready to proceed with baseline evaluation?
   

   Note: The evaluation source must have these columns:

   • question (required): The question text
   • expected_answer (required): Expected answer for comparison
   • Optional: tool_used, category, difficulty, author, date_added, or any other metadata

3. If no evaluation dataset exists, create one:

   You have two main approaches:

   • Option A: Create from production data (recommended if agent has been used)
   • Option B: Create from scratch (if agent is new or production data unavailable)

   Option A: Create Evaluation Dataset from Production Data

   If your agent has been running in production, you can use the Agent Events Explorer to:

   • Browse real production queries with natural language filters
   • Filter by question content, answer quality, or trace patterns
   • View traces in a scrollable, searchable format
   • Annotate responses with expected answers and feedback
   • Dataset auto-saves after each annotation to JSON file
   • Optionally export directly to Snowflake table

   Launch the Agent Events Explorer:

   # Start the interactive Streamlit app
   uv run streamlit run scripts/agent_events_explorer.py -- \
     --connection CONNECTION_NAME \
     --database DATABASE \
     --schema SCHEMA \
     --agent AGENT_NAME
   

Deliverables:

• 15-20 evaluation questions in database
• Each with specific, verifiable expected answer
• Good coverage across agent capabilities
• 25% of questions specifically test tool routing
• At least 1-2 routing questions per semantic model/tool
• Evaluation dataset location logged in optimization_log.md

---

Phase 3: Baseline Evaluation

Goal: Measure current agent accuracy and identify failure patterns.

Choose evaluation method based on Phase 2 availability check and decision:

• If Native Evaluation was NOT available in Phase 2 → Use Option A (Script-Based) only
• If Native Evaluation IS available → Use whichever option was chosen in Phase 2

⚠️ IMPORTANT: Pick ONE method and stick with it throughout the entire optimization workflow.

Once you choose an evaluation method (Option A or Option B), use ONLY that method for:

• Baseline evaluation (this phase)
• Re-evaluation after improvements (Phase 4)
• Final validation (Phase 6)

This ensures consistent metrics and valid before/after comparisons.

---

Option A: Script-Based Evaluation (run_evaluation.py)

Use this for quick iteration and local analysis.

1. Run evaluation using run_evaluation.py:

   The run_evaluation.py script supports flexible evaluation sources:

   Option A: Use a table or view

   uv run python scripts/run_evaluation.py \
       --agent-name AGENT_NAME --database DATABASE --schema SCHEMA \
       --eval-source database.schema.eval_table \
       --output-dir "$VER_DIR/evals/eval_baseline" \
       --connection CONNECTION_NAME
   

   Option B: Use a SQL query to filter questions

   # Example: Test only first 5 questions
   uv run python scripts/run_evaluation.py \
       --agent-name AGENT_NAME --database DATABASE --schema SCHEMA \
       --eval-source "SELECT * FROM database.schema.eval_table LIMIT 5" \
       --output-dir "$VER_DIR/evals/eval_baseline" \
       --connection CONNECTION_NAME
   
   # Example: Test specific category
   uv run python scripts/run_evaluation.py \
       --agent-name AGENT_NAME --database DATABASE --schema SCHEMA \
       --eval-source "SELECT question, expected_answer FROM eval_table WHERE category = 'finance'" \
       --output-dir "$VER_DIR/evals/eval_baseline" \
       --connection CONNECTION_NAME
   

   Script behavior:

   • Automatically detects if input is table name or SQL query
   • Requires question and expected_answer columns
   • Includes any optional metadata columns (tool_used, category, etc.)
   • Creates individual response files (q01_response.json, q02_response.json, etc.)
   • Generates evaluation_summary.json with all results

   Tip: For large evaluation sets (>20 questions), consider running in batches:

   # Test critical questions first
   uv run python scripts/run_evaluation.py --agent-name AGENT_NAME --database DB --schema SCHEMA \
       --eval-source "SELECT * FROM eval_table WHERE difficulty = 'critical'" \
       --output-dir "$VER_DIR/evals/eval_critical" \
       --connection CONNECTION_NAME
   
   # Then test remaining questions
   uv run python scripts/run_evaluation.py --agent-name AGENT_NAME --database DB --schema SCHEMA \
       --eval-source  \
       --output-dir  \
       --connection CONNECTION_NAME
   

Skip to step 3 below (Analyze each failure deeply).

---

Option B: Native Snowflake Agent Evaluations

Note: This option requires the Native Agent Evaluations preview to be enabled on your account. If the availability check in Phase 2 showed "Unknown function" error, skip this option and use Option A instead.

Use this for formal benchmarking with built-in metrics and Snowsight tracking.

LOAD: evaluate-cortex-agent skill for detailed workflow.

---

⚠️ CRITICAL: DO NOT MIX EVALUATION METHODS

If you chose Native Snowflake Agent Evaluations (Option B), you MUST:

1. Use ONLY the native evaluation approach throughout the optimization
2. Wait for native evaluation runs to complete before proceeding
3. Query results from GET_AI_OBSERVABILITY_EVENTS - DO NOT fall back to run_evaluation.py

Native evaluations may take several minutes to complete. Be patient and poll for results:

-- Check if evaluation run has completed
SELECT COUNT(*) as record_count
FROM TABLE(SNOWFLAKE.LOCAL.GET_AI_OBSERVABILITY_EVENTS(
    '<DATABASE>', '<SCHEMA>', '<AGENT_NAME>', 'CORTEX AGENT'))
WHERE RECORD_ATTRIBUTES:"snow.ai.observability.run.name" = '<RUN_NAME>'
AND RECORD_ATTRIBUTES:"ai.observability.span_type" = 'eval_root';

If record_count is 0 or less than expected, wait 30-60 seconds and try again. The evaluation is running asynchronously.

Do NOT:

• Switch to run_evaluation.py "while waiting" for native results
• Run both evaluation methods in parallel on the same questions
• Mix results from different evaluation methods in your analysis

Mixing methods creates confusion about which metrics to trust and makes comparison impossible.

---

Quick summary:

1. Ensure dataset is in native format:

   If you created dataset with question/expected_answer columns, convert it:

   CREATE OR REPLACE TABLE EVAL_NATIVE_FORMAT AS
   SELECT 
       question AS INPUT_QUERY,
       OBJECT_CONSTRUCT('ground_truth_output', expected_answer) AS GROUND_TRUTH
   FROM existing_eval_table;
   

2. Register evaluation dataset:

   CALL SYSTEM$CREATE_EVALUATION_DATASET(
       'Cortex Agent',
       '<DATABASE>.<SCHEMA>.EVAL_NATIVE_FORMAT',
       '<AGENT_NAME>_baseline_dataset',
       OBJECT_CONSTRUCT('query_text', 'INPUT_QUERY', 'expected_tools', 'GROUND_TRUTH')
   );
   

3. Run baseline evaluation:

   CALL SYSTEM$EXECUTE_AI_OBSERVABILITY_RUN(
       OBJECT_CONSTRUCT('object_name', '<DB>.<SCHEMA>.<AGENT>', 'object_type', 'CORTEX AGENT', 'object_version', ''),
       OBJECT_CONSTRUCT('run_name', 'baseline_v1', 'label', 'baseline', 'description', 'Initial baseline before optimization'),
       OBJECT_CONSTRUCT('type', 'dataset', 'dataset_name', '<AGENT_NAME>_baseline_dataset', 'dataset_version', 'SYSTEM_AI_OBS_CORTEX_AGENT_DATASET_VERSION_DO_NOT_DELETE'),
       ARRAY_CONSTRUCT(, , ),
       ARRAY_CONSTRUCT(, )
   );
   

Continue to step 3 below.

---

3. Analyze each failure deeply: For each failed question, determine:

   • What did the agent return?
   • What should it have returned?
   • Which tool did it call (if any)?
   • What tool type was used? (Check response JSON for tool_use.type)
   • Why did it fail?

   ⚠️ CRITICAL DECISION POINT: If the failure involves a cortex_analyst_text_to_sql tool AND has SQL issues:

   • Check the generated SQL in the tool_result
   • If SQL is incorrect (wrong filters, missing columns, incorrect date logic):
     • STOP analyzing this failure at agent level
     • Mark for semantic view optimization
     • Continue analyzing other failures

4. Discover failure patterns (don't use predefined categories): Analyze the failures and group them by what actually went wrong.

   Example analysis:

   I've analyzed the 9 failures and found 4 distinct patterns:
   
   1. **Percentage vs. Absolute Values (3 failures: Q4, Q9, Q11)**
      - Questions asked for "percentage" or "proportion"
      - Agent returned absolute numbers instead of percentages
      - Example: Q4 asked "what percent of credits" but got "1.2M credits"
      - Root cause: Instructions don't specify to compute percentage
   
   2. **Wrong Tool Selection (2 failures: Q7, Q12)**
      - Agent routed to incorrect semantic model
      - Example: Q7 asked about "Streamlit Open Source" but used "Streamlit in Snowflake" tool
      - Example: Q12 asked about "ML platform" (specific product) but queried all AI/ML products
      - Root cause: No guidance on tool disambiguation or product scope definitions
   
   3. **Time Period Interpretation (2 failures: Q1, Q10)**
      - Agent misinterpreted relative time periods
      - Example: "first half of 2025" wasn't translated to Jan 1 - June 30
      - Root cause: No explicit date range definitions
   
   4. **Missing Data Validation (2 failures: Q5, Q13)**
      - Agent didn't check if date range covered full period
      - Returned partial data without warning
      - Root cause: No mandatory validation checks
   
   Would you like me to generate improvement suggestions for each pattern?
   

   Note: Tool routing issues are common failure patterns. Look for:

   • Agent using wrong semantic model/tool for the question
   • Agent not asking for clarification when multiple tools could apply
   • Agent not coordinating across multiple tools when needed
   • Agent confusing similar product names (e.g., "Streamlit in Snowflake" vs "Streamlit Open Source")

5. Categorize failures by fix location:

After discovering failure patterns, separate them into two categories:

Category A: Agent-level fixes (orchestration instructions)

• Tool routing issues (wrong tool selected)
• Response formatting issues
• Missing clarification questions
• Incorrect interpretation of user intent
• Multi-tool coordination issues

Category B: Semantic view fixes (YAML model)

• Incorrect SQL generation from Cortex Analyst
• Missing columns or tables in semantic view
• Wrong join relationships
• Incorrect filters or date logic in generated SQL
• VQR (Verified Query Repository) misleading the SQL generation

Present to user:

Failure Analysis:
- Total Failures: 9
- Agent-level fixes needed: 5 failures (Q2, Q5, Q8, Q11, Q13)
- Semantic view fixes needed: 4 failures (Q1, Q4, Q7, Q10)

For the 4 semantic view issues:
- Q1: Date interpretation ("last week" = calendar week vs rolling 7 days)
- Q4: Missing column for percentage calculation
- Q7: Wrong table joined in semantic view
- Q10: VQR pattern misleading SQL generation

Would you like to:
A) Fix agent-level issues first, then semantic views
B) Fix semantic views first, then agent-level issues  
C) Fix both in parallel (recommended if different people own them)

6. Present findings to user:
   • Show accuracy metrics
   • Explain each failure pattern with examples
   • Show categorization: Agent-level vs Semantic view fixes
   • Confirm your categorization makes sense
   • Ask which patterns to prioritize and whether to handle semantic view fixes now or later

Deliverables:

• Baseline accuracy measurement (e.g., 31%)
• Categorized failure analysis
• Failures separated into Agent-level vs Semantic view fixes
• Understanding of what needs to improve

---

Phase 4: Instruction Improvements

Goal: Systematically improve orchestration instructions and/or semantic views based on failure patterns.

What You Should Do:

0. Determine if semantic view optimization is needed:

If Phase 3 identified semantic view failures (Category B):

Option 1: Semantic views and agent owned by same team (or you have access)

For each semantic view with failures:

1. Extract semantic view name from agent tool configuration:

   # Get agent config if not already extracted
   jq '.agent_spec' agent_config.json | jq '.tools[] | select(.type=="cortex_analyst_text_to_sql")'
   

2. LOAD: semantic-view-optimization skill in DEBUG mode

3. Provide to the semantic-view-optimization skill:

   • Semantic view name (e.g., DATABASE.SCHEMA.SEMANTIC_VIEW_NAME)
   • Failing question(s) from evaluation
   • Expected behavior vs actual SQL generated

4. Follow semantic-view-optimization DEBUG workflow:

   • Download semantic view YAML
   • Generate and analyze SQL with Cortex Analyst
   • Identify root cause (missing columns, wrong relationships, bad VQRs, etc.)
   • Apply targeted fixes to semantic view YAML
   • Validate with SQL comparison

5. Re-run evaluation on semantic view questions to confirm fixes:

   # Test just the semantic view fixes
   uv run python scripts/run_evaluation.py \
       --agent-name AGENT_NAME --database DATABASE --schema SCHEMA \
       --eval-source "SELECT * FROM eval_table WHERE question_id IN (1, 4, 7, 10)" \
       --output-dir "$VER_DIR/evals/eval_semantic_view_retry" \
       --connection CONNECTION_NAME
   

6. Update optimization_log.md with semantic view changes

Option 2: Semantic views owned by different team

Create handoff document:

# Semantic View Fixes Needed for [AGENT_NAME]

## Semantic View: DATABASE.SCHEMA.SEMANTIC_VIEW_1

### Question 1 (Q1): "How many users in the last week?"
- **Current SQL:**
  ```sql
  WHERE ds >= DATE_TRUNC('WEEK', CURRENT_DATE - INTERVAL '1 WEEK')

• Expected SQL:

  WHERE ds >= CURRENT_DATE - INTERVAL '7 DAYS'
  

• Root Cause: "last week" interpreted as previous calendar week, not last 7 days
• Fix: Update VQR or custom instructions for date interpretation

[Repeat for each semantic view issue]

Pause optimization until semantic view fixes are deployed, then resume with re-evaluation.

**Then proceed to Step 1** for agent-level fixes.

1. **For each AGENT-LEVEL failure pattern, draft instruction improvements:**

   **Example for "Percentage Calculation" pattern:**

For the "Percentage vs. Absolute Values" pattern affecting Q4, Q9, and Q11, I suggest adding this section to orchestration instructions:

---

Proportion/Percentage Calculations

When calculating "proportion of X spent on Y" or "percentage":

For "what percent" questions:

• Return the percentage value as primary answer
• Include context: "67.4% (1.2M out of 1.8M total credits)"

For "highest proportion" questions:

• Query both numerator and denominator separately
• Calculate: (numerator / NULLIF(denominator, 0)) * 100
• Sort by percentage DESC, not absolute values
• Return top results: "1. Acme: 89.3% (500K/560K), 2. Beta: 76.2%"

Validation:

• Verify denominator is non-zero
• If percentage >100%, re-examine query (numerator shouldn't exceed denominator)

---

This would handle both "what percent" and "highest proportion" question types. Would this fix the failures? Any adjustments needed?

**Example for "Tool Routing" pattern:**

For the "Wrong Tool Selection" pattern affecting Q7 and Q12, I suggest adding this section to orchestration instructions:

---

Tool Selection and Routing

Before answering a question, identify which semantic model/tool has the required data.

Product disambiguation:

• "Streamlit in Snowflake" vs "Streamlit Open Source" are DIFFERENT products
• "ML Platform" or "Snowflake ML" refers to specific product, NOT all AI/ML
• "AI/ML" includes: Cortex ML, Cortex LLM, Snowflake ML, ML Packages, Copilot
• "Notebooks" is separate from AI/ML categories

Tool selection rules:

• For product-specific questions → use product_categories or product-specific tool
• For geographic breakdowns → use product_geo tool
• For feature usage (SIS, Notebooks execution) → use feature_usage tool
• For organizational metadata → use product_owners tool

When multiple tools could apply:

• Ask for clarification: "To answer accurately, I need to know: [specific question]"
• Example: "For 'ML usage', do you mean Snowflake ML specifically or all AI/ML products?"

Tool coordination:

• Some questions require querying multiple tools
• Example: "Compare Notebooks vs Streamlit" needs product_categories AND feature_usage tools
• Query each tool separately, then combine results in your answer

---

This adds explicit tool routing rules based on product types and question patterns. Would this prevent the Q7 and Q12 routing failures?

2. **Iterate with user on each improvement:**
- Present draft instruction section
- Explain reasoning and what it fixes
- Ask for refinement or approval
- Show how it would handle the failed question
- Adjust based on feedback

3. **Create complete updated instructions:**
- Combine original instructions with all improvements
- Organize logically (critical rules first)
- Show before/after comparison
- Present to user for review

4. **Create new version folder and save updated instructions:**
```bash
# Create new version for this update
VERSION="v$(date +%Y%m%d-%H%M)"
VER_DIR="$WORKSPACE_DIR/versions/$VERSION"
mkdir -p "$VER_DIR/evals"

# Save updated instructions to version folder
# (copy from previous version and edit, or create new)
cp "$PREVIOUS_VER_DIR/instructions_orchestration.txt" "$VER_DIR/instructions_orchestration.txt"
# Then edit the file with improvements

5. Update the agent:

   uv run python scripts/create_or_alter_agent.py alter \
       --agent-name AGENT_NAME \
       --instructions "$VER_DIR/instructions_orchestration.txt" \
       --database DATABASE --schema SCHEMA --connection CONNECTION_NAME
   

6. Re-evaluate (use same method as Phase 3):

   ⚠️ REMINDER: Use the SAME evaluation method you used in Phase 3. Do not switch methods.

   Option A: Script-based:

   uv run python scripts/run_evaluation.py \
       --agent-name AGENT_NAME --database DATABASE --schema SCHEMA \
       --eval-source database.schema.eval_table \
       --output-dir "$VER_DIR/evals/eval_after_update" \
       --connection CONNECTION_NAME
   

   Option B: Native Snowflake:

   CALL SYSTEM$EXECUTE_AI_OBSERVABILITY_RUN(
       OBJECT_CONSTRUCT('object_name', '<DB>.<SCHEMA>.<AGENT>', 'object_type', 'CORTEX AGENT', 'object_version', ''),
       OBJECT_CONSTRUCT('run_name', 'after_improvements_v1', 'label', 'improved', 'description', 'After instruction improvements'),
       OBJECT_CONSTRUCT('type', 'dataset', 'dataset_name', '<AGENT_NAME>_baseline_dataset', 'dataset_version', 'SYSTEM_AI_OBS_CORTEX_AGENT_DATASET_VERSION_DO_NOT_DELETE'),
       ARRAY_CONSTRUCT('answer_correctness', 'tool_selection_accuracy', 'logical_consistency'),
       ARRAY_CONSTRUCT('INGESTION', 'COMPUTE_METRICS')
   );
   

   If using Native Snowflake evaluation: Wait for results before proceeding. Poll until you see results for your run_name. Do NOT fall back to script-based evaluation while waiting.

Deliverables:

• Updated orchestration instructions (for agent-level fixes)
• Updated semantic view YAMLs (for semantic view fixes, if applicable)
• Improved accuracy (target: >70%)
• Comparison showing improvements, distinguishing agent vs semantic view fixes
• Any regressions identified

---

Phase 5: Overfitting Detection

Goal: Identify instruction patterns that are too specific to evaluation questions.

What You Should Do:

1. Analyze instructions for evaluation-specific patterns:

   Read through the updated instructions carefully and reason about:

   • Specific years, dates, or time periods that came from eval questions
   • Specific company/account names used as examples
   • Hardcoded numeric thresholds that only work for eval data scale
   • Fixed result counts (e.g., "show top 10") without context
   • Absolute value ranges specific to eval data
   • Any other patterns that seem too specific

2. For each issue found, explain the problem:

   Example analysis:

   I found 4 critical overfitting issues:
   
   1. **Line 107: "First half of 2025" = Jan 1 - June 30, 2025**
      
      Why problematic: This hard-codes year 2025 from your evaluation questions.
      
      Production risk: In 2026, if a user asks "first half", the agent won't 
      know it means Jan-Jun 2026. The instruction is year-specific.
      
      Generalization needed: Define "first half" as Jan 1 - June 30 of 
      *specified year*, not a fixed 2025 date.
   
   2. **Line 117: "AMD might match AMDOCS, AMD Holdings, etc."**
      
      Why problematic: "AMD" is a specific company name from Q7 in your eval set.
      
      Production risk: Users might search for other short names (IBM, HP, SAP, 
      GE) and won't have this warning. The pattern is about *short names in 
      general*, not specifically AMD.
      
      Generalization needed: Explain that short names may match unrelated 
      companies, give AMD as ONE example among others.
   
   3. **Line 145: "Daily credits typically: SIS (10-1000), Total (10K-1M+)"**
      
      Why problematic: These absolute ranges came from your evaluation data scale.
      
      Production risk: A very large enterprise customer might have 10M daily 
      credits (10x the range), or a small trial account might have 5 credits. 
      The ranges don't adapt to different customer sizes.
      
      Generalization needed: Use relative comparisons (e.g., "SIS credits are 
      typically 100-1000x smaller than total Snowflake credits") instead of 
      absolute ranges.
   
   4. **Line 122: "Filter out accounts with <1000 total credits"**
      
      Why problematic: The 1000 threshold came from your eval data distribution.
      
      Production risk: For different question types or data scales, 1000 might 
      filter out important results or not filter enough noise.
      
      Generalization needed: Make filtering context-dependent based on question 
      type and data scale.
   
   I also noticed Line 124 says "Show top 10-20 results" - this should vary 
   based on whether the question asks for "which account has highest X" (show 
   3-5) vs "accounts with high X" (show 10-20).
   
   Should we generalize these patterns?
   

3. Prioritize issues:

   • Critical: Will definitely cause production failures
   • Medium: Might cause issues in some scenarios

Deliverables:

• Overfitting analysis identifying eval-specific patterns
• Prioritized list of generalizations needed
• User confirmation on issues to address

---

Phase 6: Generalization & Validation

Goal: Create production-ready instructions that work beyond evaluation cases.

What You Should Do:

1. For each overfitting issue, create generalized version:

   Example generalization:

   Issue #1: Time Period Definitions
   
   BEFORE (lines 107-109):
   ├─ "First half of 2025" = Jan 1 - June 30, 2025
   ├─ "Last couple of months ending mid-October" = Aug 1 - Oct 15
   └─ Always specify exact date ranges in your tool queries
   
   AFTER:
   **For time-based questions:**
   ├─ Standard period definitions:
      - "First half" or "H1" = January 1 - June 30 of specified year
      - "Second half" or "H2" = July 1 - December 31 of specified year
      - "Q1" = Jan-Mar, "Q2" = Apr-Jun, "Q3" = Jul-Sep, "Q4" = Oct-Dec
      - "Last/past [N] months" = N months before reference date
      - "Last/past [N] days/weeks" = N days/weeks before reference date
   ├─ Interpret relative terms based on context and current date
   ├─ When time period is ambiguous, ask for clarification on exact date range
   └─ Always specify exact date ranges in your tool queries
   
   What this fixes:
   - Works for any year (not just 2025)
   - Handles all standard period types (Q1-Q4, H1-H2)  
   - Supports relative time references
   - Covers edge case: ambiguous periods
   
   Your eval question "first half of 2025" still works correctly, but now 
   so do these production variations:
   - "first half of 2026"
   - "H1 2024"
   - "Q3 of last year"
   - "past 3 months"
   

2. Show complete generalized instructions:

   • Present full updated instruction file
   • Highlight all changes from previous version
   • Explain how each change improves generalization
   • Get user approval

3. Create new version folder and save generalized instructions:

   # Create new version for generalization
   VERSION="v$(date +%Y%m%d-%H%M)"
   VER_DIR="$WORKSPACE_DIR/versions/$VERSION"
   mkdir -p "$VER_DIR/evals"
   
   # Save generalized instructions to version folder
   # (copy from previous version and edit, or create new)
   cp "$PREVIOUS_VER_DIR/instructions_orchestration.txt" "$VER_DIR/instructions_orchestration.txt"
   # Then edit the file with generalizations
   

Deliverables:

• Production-ready agent with generalized instructions
• 100% (or near-100%) evaluation accuracy
• Deployment summary documenting journey
• No critical overfitting issues remaining

---

Best Practices for AI Assistants

Using run_evaluation.py Effectively

The run_evaluation.py script is flexible and supports many use cases beyond basic table evaluation:

Filtering Strategies:

# By category
"SELECT * FROM eval_table WHERE category IN ('finance', 'sales')"

# By difficulty
"SELECT * FROM eval_table WHERE difficulty = 'hard'"

# By date added (recent questions only)
"SELECT * FROM eval_table WHERE date_added >= '2025-01-01'"

# By specific questions
"SELECT * FROM eval_table WHERE question_id IN (3, 7, 11, 15)"

# By tool coverage
"SELECT * FROM eval_table WHERE tool_used = 'Product_Categories'"

# Test routing for each semantic model
"SELECT * FROM eval_table WHERE category = 'tool_routing'"

# Random sample for quick validation
"SELECT * FROM eval_table ORDER BY RANDOM() LIMIT 10"

Progressive Testing Approach:

1. Start with small subset (5-10 questions) to validate agent works
2. Run full evaluation once agent shows promise
3. Re-test only failed questions after improvements
4. Run final comprehensive evaluation before deployment

Performance Optimization:

• For >20 questions, consider batching by category or tool
• Use LIMIT during development/debugging
• Run full evaluation only when needed (baseline, after major changes, final)
• Filter to failed questions for faster iteration

Example workflow (using versioned structure):

# Set version directory for this phase
VER_DIR="$WORKSPACE_DIR/versions/v$(date +%Y%m%d-%H%M)"

# 1. Quick smoke test (5 questions)
uv run python scripts/run_evaluation.py --agent-name AGENT --database DB --schema SCHEMA \
    --eval-source "SELECT * FROM eval_table LIMIT 5" --output-dir "$VER_DIR/evals/eval_smoke" --connection CONNECTION_NAME

# 2. If smoke test passes, run full baseline
uv run python scripts/run_evaluation.py --agent-name AGENT --database DB --schema SCHEMA \
    --eval-source eval_table --output-dir "$VER_DIR/evals/eval_baseline" --connection CONNECTION_NAME

# 3. After improvements, re-test only failures (in new version folder)
NEW_VER_DIR="$WORKSPACE_DIR/versions/v$(date +%Y%m%d-%H%M)"
uv run python scripts/run_evaluation.py --agent-name AGENT --database DB --schema SCHEMA \
    --eval-source "SELECT * FROM eval_table WHERE question_id IN (3, 6, 10)" \
    --output-dir "$NEW_VER_DIR/evals/eval_retry" --connection CONNECTION_NAME

# 4. Final validation with full set
uv run python scripts/run_evaluation.py --agent-name AGENT --database DB --schema SCHEMA \
    --eval-source eval_table --output-dir "$NEW_VER_DIR/evals/eval_final" --connection CONNECTION_NAME

Analyzing Failures

❌ Don't: Use predefined failure categories

"This is a tool selection error" [forcing into predetermined bucket]

✅ Do: Discover actual patterns

"Looking at these 3 failures, they all asked for percentages but got absolute 
numbers. The common issue is the agent isn't computing the division. This is 
a calculation pattern issue, not covered by current instructions."

"Q7 and Q12 both routed to the wrong semantic model. Q7 used 'Streamlit in 
Snowflake' tool when the question asked about 'Streamlit Open Source'. Q12 
queried all AI/ML products when the question specifically asked for 'ML Platform' 
only. This is a tool routing and product disambiguation issue."

Generating Improvements

❌ Don't: Provide generic template instructions

"Add: When user asks for X, do Y"

✅ Do: Create specific, actionable instructions

"Add this section:

## Proportion/Percentage Calculations

**For 'what percent' questions:**
- Calculate: (numerator / NULLIF(denominator, 0)) * 100
- Return: '67.4% (1.2M out of 1.8M total credits)'

**For 'highest proportion' questions:**
- Sort by percentage DESC (not absolute values)
- Return top N with percentages and raw numbers

This handles Q4 (what percent of credits...) and Q9 (highest proportion of spend...)"

Detecting Overfitting

❌ Don't: Just pattern-match for years/names

"Found '2025' in instructions - that's overfitting"

✅ Do: Reason about production impact

"Line 107 says 'First half of 2025' = Jan 1 - June 30, 2025. 

This is problematic because in production:
- Users will ask about 2026, 2027, etc.
- The instruction only defines 2025 specifically
- Next year, 'first half' won't be interpreted correctly

We should generalize to: 'First half' = Jan 1 - June 30 of *specified year*"

Communicating with User

Be specific about what you're doing:

✅ "I'm going to query Snowflake for all agents in TEMP.NVYTLA schema"
✅ "Let me analyze Q4 - it asked for percentage but got absolute number"
✅ "I found 3 questions with the same pattern - percentage calculation issues"

Ask for validation at key decision points:

✅ "Does this failure categorization make sense to you?"
✅ "Would this instruction fix the issue?"
✅ "Should we prioritize fixing percentages or account matching first?"

Show your reasoning:

✅ "This pattern is overfit because [explanation + production risk]"
✅ "I categorized these together because [common root cause]"
✅ "This generalization handles [list of scenarios]"

---

Measuring Success

Track these metrics throughout optimization:

Metric	Baseline	Target	How to Measure
Accuracy	Measure first	>80%	Evaluation results
Instruction Size	Measure first	<20KB	Character count
Overfitting Issues	N/A	0 critical	Your analysis
Regressions	0	0	Evaluation comparison

Success Criteria:

• ✅ >80% accuracy on evaluation set
• ✅ Zero critical overfitting issues
• ✅ Zero regressions from baseline to final
• ✅ Domain expert approval for production deployment

---

Common Pitfalls to Avoid

1. Accepting First Draft

❌ User approves first improvement suggestion → immediately update agent
✅ User approves → iterate on wording → show how it handles failed question → then update

2. Writing Eval-Specific Instructions

❌ "For 'first half of 2025' questions, use Jan 1 - June 30, 2025"
✅ Catch this yourself: "Wait, this is too specific to 2025. Let me generalize..."

3. Forcing Predefined Categories

❌ "Categorizing into: Tool Selection, Metric Confusion, Calculation, Data Quality"
✅ "Analyzing patterns... I see 3 distinct issues: percentage calculations, 
    account pattern matching, and time period interpretation"

4. Skipping Overfitting Check

❌ 77% accuracy → "Great improvement! Ready for production"
✅ 77% accuracy → "Good improvement! Let me check for overfitting before we deploy..."

5. Not Explaining Reasoning

❌ "This is overfit" [no explanation]
✅ "This is overfit because it hard-codes 2025. In production, users will ask 
    about other years and the agent won't know how to handle them."

---

Example: Full Phase 4 Interaction

Here's what a complete Phase 4 conversation looks like:

You: Let me analyze the failures from baseline evaluation.

I found 3 distinct patterns:

1. Percentage Calculation Issues (Q4, Q9, Q11)
2. Account Name Ambiguity (Q7, Q12)  
3. Time Period Misinterpretation (Q1, Q10)

Let's start with #1. All three questions asked for percentages or proportions,
but the agent returned absolute numbers instead.

Looking at Q4: "what percent of snowflake credits were used by accounts with 
Databricks usage?"

Expected: "67.42%" 
Actual: "1.2M credits"

The agent queried the right data but didn't compute the percentage.

I suggest adding this section to orchestration instructions:

---
## Proportion/Percentage Calculations

When calculating "proportion of X spent on Y" or "percentage":

**For "what percent" questions:**
- Query both numerator and denominator
- Calculate: (numerator / NULLIF(denominator, 0)) * 100
- Return: "67.4% (1.2M out of 1.8M total credits)"

**For "highest proportion" questions:**
- Sort by percentage DESC, not absolute values
- Return top entities: "1. Acme: 89.3% (500K/560K), 2. Beta: 76.2%"

**Validation:**
- If percentage >100%, re-examine query
---

Does this make sense? Any adjustments needed?

User: Yes, but what if denominator is zero?

You: Good catch! The NULLIF handles division by zero, but we should also explain
what to do if that happens. Let me add:

- If denominator is zero, return "Cannot calculate percentage (no baseline data)"

Should I also add a sanity check for very small denominators?

User: Yes, warn if denominator seems unusually low.

You: I'll add:

- If denominator <1000 credits, note: "Warning: Small baseline (only X credits)"

Here's the updated version: [shows complete section]

Does this fully address the percentage calculation failures?

User: Perfect. What about pattern #2?

You: For the account name ambiguity pattern, Q7 asked about "AMD" but the agent
returned results for "AMDOCS" because it used pattern matching...

[continues through all patterns]

---

Output Files Reference

Throughout optimization, following the agent-system-of-record protocol, you'll create these files:

<WORKSPACE_DIR>/
├── optimization_log.md                         # Running log (update continuously)
├── versions/
│   ├── vYYYYMMDD-HHMM-baseline/                # Baseline version (Phase 1-3)
│   │   ├── agent_config.json                   # Original config
│   │   ├── instructions_orchestration.txt     # Original instructions
│   │   ├── tools_summary.txt                   # Tool inventory
│   │   └── evals/
│   │       └── eval_baseline/
│   │           ├── q01_response.json ... q13_response.json
│   │           ├── evaluation_summary.json
│   │           └── analysis_notes.txt          # Your analysis
│   │
│   ├── vYYYYMMDD-HHMM-updated/                 # Updated version (Phase 4)
│   │   ├── instructions_orchestration.txt     # Updated instructions
│   │   ├── change_manifest.md                 # What changed
│   │   └── evals/
│   │       ├── eval_after_update/
│   │       │   ├── q01_response.json ... q13_response.json
│   │       │   ├── evaluation_summary.json
│   │       │   └── comparison_vs_baseline.txt
│   │       └── eval_failed_retry/              # Optional: retry failed questions
│   │
│   └── vYYYYMMDD-HHMM-generalized/             # Generalized version (Phase 6)
│       ├── instructions_orchestration.txt     # Generalized instructions
│       ├── change_manifest.md                 # Generalization changes
│       └── evals/
│           ├── eval_generalized/
│           │   ├── q01_response.json ... q13_response.json
│           │   ├── evaluation_summary.json
│           │   └── three_way_comparison.txt  # Baseline → Updated → Generalized
│           ├── eval_full/                      # Optional: full validation
│           ├── eval_edge/                      # Optional: edge cases only
│           └── eval_production_sample/         # Optional: production queries
│
└── DEPLOYMENT_SUMMARY.md                       # Final summary (optional, at workspace root)

---

When to Escalate to User

Ask user for input when:

• Ambiguous requirements: "Should we prioritize accuracy or speed?"
• Domain knowledge needed: "What should the expected answer be for this question?"
• Trade-off decisions: "This instruction improves Q4 but might affect Q7. Which is more important?"
• Validation needed: "Does this failure categorization make sense?"
• Approval required: "Ready to update the agent with these changes?"

Don't ask user for:

• Technical execution: Just run the scripts
• Obvious issues: "I found an overfitting issue" (explain it, don't ask if it's an issue)
• Analysis: Do the analysis yourself, then present findings

---

This workflow is designed for AI assistants to guide humans through agent optimization using reasoning and collaboration, not rigid automation. Adapt as needed for your specific agent.