[Feature]: Cortex Agent Definition Support with Dynamic Semantic View References

[mluizzi-whoop]

Problem Statement

SST currently creates only Semantic Views, but enterprises need a complete AI-powered data assistant story that includes Cortex Agents.

Snowflake Cortex Agents are the next evolution of AI-powered data interaction, combining:

• Cortex Analyst (via Semantic Views) for structured data queries
• Cortex Search for unstructured document retrieval
• Custom Tools for business-specific logic and external integrations
• Orchestration that routes user questions to the right tool

Currently, SST helps users define semantic models as code and deploy Semantic Views to Snowflake. However, to fully leverage Cortex Analyst capabilities, users must manually create Cortex Agents via:

• Snowsight UI (no version control, not reproducible)
• REST API (requires custom scripting outside SST)
• SQL DDL (requires manual SQL management)

This creates several pain points:

1. No single source of truth: Semantic Views are version-controlled in git, but Agent definitions are not
2. Manual reference management: Users must manually copy semantic view names into agent definitions
3. Deployment complexity: Multi-step process - deploy semantic views first, then manually configure agents
4. No validation: Agent tool references aren't validated against actual semantic views
5. Environment drift: Dev/staging/prod agents can diverge since they're managed separately

Use Case: A data team wants to deploy a complete "Business Intelligence Assistant" that can:

• Answer SQL questions using their Semantic Views (Cortex Analyst)
• Search company documentation (Cortex Search)
• Generate visualizations (data_to_chart tool)
• Apply custom business logic (UDF tools)

Today, they must manage semantic views in SST and agents separately. This is error-prone and doesn't scale.

Proposed Solution

Add Cortex Agent definition support to SST with dynamic resource references.

1. New YAML Schema: Agent Definitions

Create a new agents/ directory (configurable) for agent YAML files:

# snowflake_semantic_models/agents/business_assistant.yml

cortex_agents:
  - name: business_intelligence_assistant
    display_name: "Business Intelligence Assistant"
    description: "AI assistant for business analytics and reporting"
    avatar: "analytics-icon.png"
    color: "blue"
    
    # LLM Configuration
    models:
      orchestration: claude-4-sonnet  # or llama3.3-70b, etc.
    
    # Orchestration Budgets
    orchestration:
      budget:
        seconds: 30
        tokens: 16000
    
    # Instructions
    instructions:
      system: "You are a helpful data analyst assistant that helps with business questions."
      orchestration: "For revenue questions use Analyst; for policy questions use Search"
      response: "Respond in a friendly but concise manner. Always cite your data sources."
      sample_questions:
        - question: "What was our revenue last quarter?"
          answer: "I'll analyze the revenue data using our financial database."
        - question: "What is our refund policy?"
          answer: "Let me search our policy documentation."
    
    # Tools with dynamic references
    tools:
      - tool_spec:
          type: cortex_analyst_text_to_sql
          name: SalesAnalyst
          description: "Converts natural language to SQL for sales and revenue analysis"
        resources:
          semantic_view: "{{ semantic_view('sales_analytics') }}"  # Dynamic reference!
          
      - tool_spec:
          type: cortex_analyst_text_to_sql
          name: CustomerAnalyst
          description: "Analyzes customer behavior and lifetime value"
        resources:
          semantic_view: "{{ semantic_view('customer_360') }}"
          
      - tool_spec:
          type: cortex_search
          name: PolicySearch
          description: "Searches company policy and documentation"
        resources:
          service: "{{ cortex_search_service('policy_docs') }}"  # Dynamic reference!
          max_results: 5
          filter:
            "@eq":
              department: "{{ env('DEPARTMENT', 'All') }}"  # Environment variable support
          title_column: "doc_title"
          id_column: "doc_id"
          
      - tool_spec:
          type: data_to_chart
          name: ChartGenerator
          description: "Generates visualizations from query results"
          
      - tool_spec:
          type: function
          name: FormatCurrency
          description: "Formats numbers as currency with proper locale"
        resources:
          function: "{{ custom_tool('format_currency_udf') }}"  # UDF reference

2. New YAML Schema: Cortex Search Service Definitions

# snowflake_semantic_models/cortex_search/policy_docs.yml

cortex_search_services:
  - name: policy_docs
    description: "Search service for company policy documentation"
    
    # Source configuration
    source:
      database: "{{ env('DOCS_DATABASE', 'DOCUMENTATION') }}"
      schema: "{{ env('DOCS_SCHEMA', 'POLICIES') }}"
      table: policy_documents
      
    # Search configuration
    search_column: document_content  # Column to search
    attributes:                       # Columns returned with results
      - doc_title
      - doc_id
      - department
      - last_updated
      
    # Warehouse for indexing
    warehouse: "{{ env('CORTEX_WAREHOUSE', 'COMPUTE_WH') }}"
    
    # Refresh settings
    target_lag: "1 day"  # How often to refresh index
    
    # Optional: Filter to only index certain records
    filter_query: |
      SELECT doc_id, doc_title, document_content, department, last_updated
      FROM policy_documents
      WHERE is_active = TRUE
        AND visibility = 'Internal'

3. New YAML Schema: Custom Tool Definitions

# snowflake_semantic_models/custom_tools/format_currency.yml

custom_tools:
  - name: format_currency_udf
    description: "UDF that formats numbers as currency"
    type: function  # or procedure
    
    # Reference existing Snowflake function
    database: ANALYTICS
    schema: UTILS
    function_name: FORMAT_CURRENCY
    
    # Or define inline (SST creates it)
    # create_if_missing: true
    # definition:
    #   language: python
    #   runtime: 3.10
    #   handler: format_currency
    #   code: |
    #     def format_currency(value, currency='USD'):
    #         return f"${value:,.2f}" if currency == 'USD' else f"{value:,.2f} {currency}"

4. New Template Functions

Extend the existing template system with new functions:

Template	Purpose	Example
{{ semantic_view('name') }}	Reference a semantic view	{{ semantic_view('sales_analytics') }} → DB.SCHEMA.SALES_ANALYTICS
{{ cortex_search_service('name') }}	Reference a Cortex Search service	{{ cortex_search_service('policy_docs') }} → DB.SCHEMA.POLICY_DOCS
{{ custom_tool('name') }}	Reference a UDF/procedure	{{ custom_tool('format_currency') }} → DB.SCHEMA.FORMAT_CURRENCY
{{ env('VAR', 'default') }}	Environment variable with default	{{ env('TARGET_DB', 'ANALYTICS') }}

5. New CLI Commands

# Deploy everything (semantic views + search services + agents)
sst deploy --db ANALYTICS --schema SEMANTIC_LAYER

# Deploy only agents (after semantic views exist)
sst deploy-agents --db ANALYTICS --schema SEMANTIC_LAYER

# Validate agent definitions (check references resolve)
sst validate --include-agents

# Generate agent SQL without executing (dry run)
sst generate-agents --db ANALYTICS --schema SEMANTIC_LAYER --dry-run

# List configured agents
sst list-agents

6. Updated Deployment Flow

┌─────────────────────────────────────────────────────────────────────┐
│                        sst deploy (enhanced)                         │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  1. VALIDATE                                                         │
│     ├── Semantic Models (existing)                                   │
│     ├── Cortex Search Services (new)                                 │
│     └── Agent Definitions (new)                                      │
│         └── Verify all {{ semantic_view() }} refs exist             │
│         └── Verify all {{ cortex_search_service() }} refs exist     │
│         └── Verify all {{ custom_tool() }} refs exist               │
│                                                                      │
│  2. EXTRACT (existing)                                               │
│     └── Load metadata to SM_* tables                                 │
│                                                                      │
│  3. GENERATE SEMANTIC VIEWS (existing)                               │
│     └── CREATE OR REPLACE SEMANTIC VIEW ...                          │
│                                                                      │
│  4. GENERATE CORTEX SEARCH SERVICES (new)                            │
│     └── CREATE OR REPLACE CORTEX SEARCH SERVICE ...                  │
│                                                                      │
│  5. GENERATE AGENTS (new)                                            │
│     └── CREATE OR REPLACE AGENT ... FROM SPECIFICATION $$...$$       │
│                                                                      │
│  6. GRANT ACCESS (optional)                                          │
│     └── GRANT USAGE ON AGENT ... TO ROLE ...                         │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

7. Generated SQL Example

SST would generate SQL like this for the agent definition above:

CREATE OR REPLACE AGENT ANALYTICS.SEMANTIC_LAYER.BUSINESS_INTELLIGENCE_ASSISTANT
  COMMENT = 'AI assistant for business analytics and reporting'
  PROFILE = '{"display_name": "Business Intelligence Assistant", "avatar": "analytics-icon.png", "color": "blue"}'
FROM SPECIFICATION
$$
models:
  orchestration: claude-4-sonnet

orchestration:
  budget:
    seconds: 30
    tokens: 16000

instructions:
  system: "You are a helpful data analyst assistant that helps with business questions."
  orchestration: "For revenue questions use Analyst; for policy questions use Search"
  response: "Respond in a friendly but concise manner. Always cite your data sources."
  sample_questions:
    - question: "What was our revenue last quarter?"
      answer: "I'll analyze the revenue data using our financial database."
    - question: "What is our refund policy?"
      answer: "Let me search our policy documentation."

tools:
  - tool_spec:
      type: cortex_analyst_text_to_sql
      name: SalesAnalyst
      description: "Converts natural language to SQL for sales and revenue analysis"
  - tool_spec:
      type: cortex_analyst_text_to_sql
      name: CustomerAnalyst
      description: "Analyzes customer behavior and lifetime value"
  - tool_spec:
      type: cortex_search
      name: PolicySearch
      description: "Searches company policy and documentation"
  - tool_spec:
      type: data_to_chart
      name: ChartGenerator
      description: "Generates visualizations from query results"
  - tool_spec:
      type: function
      name: FormatCurrency
      description: "Formats numbers as currency with proper locale"

tool_resources:
  SalesAnalyst:
    semantic_view: "ANALYTICS.SEMANTIC_LAYER.SALES_ANALYTICS"
  CustomerAnalyst:
    semantic_view: "ANALYTICS.SEMANTIC_LAYER.CUSTOMER_360"
  PolicySearch:
    name: "ANALYTICS.SEMANTIC_LAYER.POLICY_DOCS"
    max_results: "5"
    filter:
      "@eq":
        department: "All"
    title_column: "doc_title"
    id_column: "doc_id"
  FormatCurrency:
    function: "ANALYTICS.UTILS.FORMAT_CURRENCY"
$$;

Alternatives Considered

1. Manual SQL management outside SST

   • Pros: Works today, no SST changes needed
   • Cons: No version control, no validation, manual reference management, doesn't scale

2. Separate tool for agent management

   • Pros: Simpler SST scope
   • Cons: Fragmented tooling, users must coordinate between tools, no unified validation

3. REST API wrapper only (no YAML)

   • Pros: Simpler implementation
   • Cons: Loses declarative YAML benefits, harder to version control

4. Only support agent definitions, not Cortex Search

   • Pros: Smaller scope
   • Cons: Incomplete story - agents need search services, users still manage separately

Recommendation: Full solution (Option 1 in Proposed Solution) provides the complete story and aligns with SST's "semantic layer as code" philosophy.

Priority

High - Would significantly improve workflow

This feature would transform SST from a "semantic view tool" to a "complete Cortex AI platform management tool."

Impact

Who benefits:

• All SST users wanting to leverage Cortex Agents
• Enterprise teams needing version-controlled AI assistant definitions
• DevOps/Platform teams managing AI infrastructure across environments
• Data teams wanting to deploy semantic views AND agents together
• Organizations with compliance requirements needing auditability for AI configurations

Estimated reach:

• Any organization using Cortex Analyst will eventually want Agents
• Snowflake is actively promoting Agents as the future of data interaction
• This positions SST as THE tool for Cortex AI infrastructure management

Technical Considerations

Architecture Extension Points

1. New Data Models (core/models/):

   • CortexAgent dataclass
   • CortexSearchService dataclass
   • CustomTool dataclass
   • AgentTool dataclass

2. New Parsers (core/parsing/parsers/):

   • agent_parser.py - Parse agent YAML files
   • cortex_search_parser.py - Parse search service YAML files
   • custom_tool_parser.py - Parse custom tool YAML files

3. Template Engine Extensions (core/parsing/template_engine/):

   • Add semantic_view() resolver
   • Add cortex_search_service() resolver
   • Add custom_tool() resolver
   • Add env() resolver for environment variables

4. New Builders (core/generation/):

   • agent_builder.py - Generate CREATE AGENT SQL
   • cortex_search_builder.py - Generate CREATE CORTEX SEARCH SERVICE SQL

5. New Services (services/):

   • deploy_agents.py - Orchestrate agent deployment
   • deploy_cortex_search.py - Orchestrate search service deployment

6. New Validators (core/validation/rules/):

   • agent_validation.py - Validate agent definitions
   • cortex_search_validation.py - Validate search service definitions
   • tool_reference_validation.py - Validate all dynamic references resolve

7. CLI Extensions (interfaces/cli/commands/):

   • Extend deploy.py with agent/search flags
   • Add agents.py command group

8. Config Extensions (shared/config.py):

   • Add agents_dir config
   • Add cortex_search_dir config
   • Add agent-specific settings

Database Objects Created

Object Type	Naming Convention	Example
Semantic View	{name}	SALES_ANALYTICS
Cortex Search Service	{name}	POLICY_DOCS
Agent	{name}	BUSINESS_INTELLIGENCE_ASSISTANT

Dependency Order

Deployment must follow this order:

1. Semantic Views (agents reference these)
2. Cortex Search Services (agents reference these)
3. Custom Tools verification (must exist before agent creation)
4. Agents (reference all of the above)

Backward Compatibility

• All existing SST functionality unchanged
• New agent features are opt-in (only if agents/ directory exists)
• Existing sst deploy command works as before
• New --include-agents flag enables agent deployment

Example Usage

Basic Agent Deployment

# Create agent definition
mkdir -p snowflake_semantic_models/agents

# Create YAML file (as shown above)
vim snowflake_semantic_models/agents/business_assistant.yml

# Validate everything
sst validate --include-agents

# Deploy semantic views + agents
sst deploy --db ANALYTICS --schema SEMANTIC_LAYER --include-agents

Output

[1/5] Validating semantic models... PASSED (0 errors, 2 warnings)
[2/5] Validating agent definitions... PASSED
      ✓ business_intelligence_assistant
        ✓ semantic_view('sales_analytics') → ANALYTICS.SEMANTIC_LAYER.SALES_ANALYTICS
        ✓ semantic_view('customer_360') → ANALYTICS.SEMANTIC_LAYER.CUSTOMER_360
        ✓ cortex_search_service('policy_docs') → ANALYTICS.SEMANTIC_LAYER.POLICY_DOCS

[3/5] Extracting metadata to Snowflake...
      Loaded 1,234 rows from 8 models

[4/5] Generating semantic views...
      [CREATED] SALES_ANALYTICS (3 tables, 0.8s)
      [CREATED] CUSTOMER_360 (4 tables, 1.2s)

[5/5] Generating Cortex Search services...
      [CREATED] POLICY_DOCS (1.5s)

[6/6] Generating agents...
      [CREATED] BUSINESS_INTELLIGENCE_ASSISTANT
        Tools: SalesAnalyst, CustomerAnalyst, PolicySearch, ChartGenerator

================================================================================
DEPLOYMENT SUMMARY
================================================================================
Status: SUCCESS

Semantic Views: 2 created
Cortex Search Services: 1 created
Agents: 1 created

Total Time: 12.3s
================================================================================

Additional Context

Snowflake Documentation References

• Cortex Agents Overview
• Configure and Interact with Agents
• Agent REST API
• CREATE AGENT SQL Syntax

Implementation Phases

Phase 1: Foundation (MVP)

• Agent YAML schema and parsing
• {{ semantic_view() }} template function
• Agent SQL generation (CREATE AGENT)
• Basic validation
• sst deploy --include-agents flag

Phase 2: Cortex Search Integration

• Cortex Search YAML schema
• {{ cortex_search_service() }} template function
• Search service SQL generation
• Integration with agent deployment

Phase 3: Custom Tools

• Custom tool YAML schema
• {{ custom_tool() }} template function
• Tool existence validation
• Optional tool creation (UDFs)

Phase 4: Advanced Features

• {{ env() }} for environment variables
• Role-based access configuration
• Agent versioning support
• Multi-environment agent promotion

Related Issues/PRs

• Built on existing Cortex Search Manager (infrastructure/snowflake/cortex_search_manager.py)
• Extends existing template engine (core/parsing/template_engine/)
• Similar pattern to semantic view generation (core/generation/semantic_view_builder.py)

Pre-submission Checklist

• I have searched existing issues to avoid duplicates
• I have described a clear problem and solution
• I have considered alternatives and workarounds

---

Summary

This feature request proposes extending SST to support Cortex Agent definitions as code, enabling:

1. Version-controlled agent configurations alongside semantic models
2. Dynamic references to semantic views, search services, and custom tools
3. Unified deployment of the complete Cortex AI stack
4. Validation that all agent tool references resolve correctly
5. Reproducible agent deployments across dev/staging/prod

This transforms SST from a "semantic view tool" into a comprehensive Cortex AI platform management solution, aligning with Snowflake's vision for AI-powered data interaction.

[mluizzi-whoop]

No longer needed at WHOOP