Snowflake Semantic ViewBest Practices Guide

The Semantic Layer for Cortex Analyst & Snowflake Intelligence

A Semantic View is a YAML-based metadata layer that sits between your raw Snowflake tables and Cortex Analyst — Snowflake's natural-language-to-SQL engine that powers Snowflake Intelligence.

How They Work Together

Semantic views fix the mismatch between how business users describe data ("revenue by region") and how it's actually stored in database schemas (`TBL_SALES.AMT_TOTAL`). They define the business meaning of your data by providing:

• Business context — Descriptions that explain what each table and column represents in business terms
• Relationships — How tables connect to each other through joins
• Metrics — Pre-defined calculations like Net Revenue = SUM(gross_revenue * (1 - discount))
• Filters — Common data slices like "active customers" or "last quarter"
• Examples — Sample question-answer pairs (Verified Queries) that guide the AI

Cortex Analyst uses these semantic views to generate accurate SQL from natural language questions. Instead of guessing column names and join paths, it understands your business concepts and generates reliable queries.

Snowflake Intelligence brings it all together: a Cortex Agent orchestrates multiple tools (Cortex Analyst for structured data, Cortex Search for documents, custom tools) to deliver end-to-end natural-language analytics inside Snowflake.

Key Benefits & Added Value

Why YAML, Not SQL DDL?

Semantic views use YAML because it supports features that SQL DDL cannot express:

• Verified Queries (VQRs) — example question+SQL pairs for few-shot learning
• Sample values — representative data values for filter generation
• Custom instructions — natural language guidance for the LLM
• Rich descriptions — business context for every column and table
• Metrics — pre-defined aggregation logic (SUM, COUNT, AVG)
• Named filters — reusable WHERE clause patterns

YAML format also enables version control via Git CI/CD pipelines, making it easy to track changes and collaborate across teams.

What Is the semantic-view Skill?

The semantic-view skill is a bundled skill available in both Cortex Code environments:

• Cortex Code in Snowsight — AI assistant integrated directly into the Snowflake web UI
• Cortex Code CLI — Command-line AI assistant for local development workflows

It is the single entry point for all semantic view operations — creation, optimization, auditing, debugging, VQR generation, and more. Every best practice described in this guide is implemented as a workflow inside this skill.

Getting Started with Cortex Code

How to Invoke the Skill

Simply describe what you want in natural language. The skill triggers automatically on keywords like create, optimize, debug, audit, improve, suggest VQRs, suggest metrics, or import Tableau.

# Create a new semantic view
"Create a semantic view for our sales tables in ANALYTICS_DB.SEMANTIC_MODELS"

# Optimize an existing one
"Optimize the semantic view PROD_DB.MODELS.SALES_SV"

# Debug SQL generation issues
"Debug why 'top 10 customers by revenue' generates wrong SQL on MY_DB.SCHEMA.MY_VIEW"

# Run a best practices audit
"Audit ANALYTICS_DB.MODELS.FINANCE_SV for best practices"

# Generate VQR suggestions from usage history
"Suggest VQRs for PROD_DB.MODELS.SALES_SV"

# Suggest metrics and filters from query history
"Suggest metrics and filters for PROD_DB.MODELS.SALES_SV"

# Import a Tableau workbook
"Import the Tableau workbook at @MY_STAGE/dashboard.twbx as a semantic view"

Skill Workflow Routing

After initialization, the skill automatically routes to the right workflow based on your intent:

Prerequisites

• Cortex Code CLI installed and connected to Snowflake
• Fully qualified semantic view name for existing views: DATABASE.SCHEMA.VIEW_NAME
• Python managed via uv — the skill uses uv run python for all scripts
• Dependencies: tomli, urllib3, requests, pyyaml, snowflake-connector-python (auto-managed by uv)

Skill Capabilities at a Glance

Complete YAML Skeleton

Every semantic view follows this structure. Elements are categorized as Physical (mapped to real columns) or Computed (added logic).

name: my_analytics_model
description: "Sales analytics for the North America region"

# ---- Tables ----
tables:
  - name: SALES                  # Logical name (for humans)
    description: "Daily sales transactions"
    base_table:
      database: PROD_DB
      schema: ANALYTICS
      table: TBL_SALES           # Physical table name

    # Physical columns (CANNOT add new ones)
    dimensions:                     # Physical categorical
      - name: REGION
        description: "Sales region (NA, EMEA, APAC)"
        expr: region
        data_type: TEXT
        sample_values: ["NA", "EMEA", "APAC"]

    time_dimensions:                # Physical date/time
      - name: SALE_DATE
        description: "Date the transaction occurred"
        expr: sale_date
        data_type: DATE

    facts:                          # Physical numeric
      - name: REVENUE
        description: "Transaction revenue in USD"
        expr: revenue_usd
        data_type: NUMBER

    # Computed elements (CAN add new ones)
    metrics:                        # Computed aggregations
      - name: TOTAL_REVENUE
        description: "Sum of all revenue"
        expr: SUM(revenue_usd)
        access_modifier: public_access

    filters:                        # Computed WHERE clauses
      - name: LAST_30_DAYS
        description: "Records from the last 30 days"
        expr: sale_date >= DATEADD(day, -30, CURRENT_DATE())

# ---- Relationships ----
relationships:                      # Computed joins
  - name: sales_to_customers
    left_table: SALES
    right_table: CUSTOMERS
    relationship_columns:
      - left_column: CUSTOMER_ID
        right_column: CUSTOMER_ID
    relationship_type: many_to_one
    join_type: left_outer

# ---- Verified Queries ----
verified_queries:
  - name: vqr_0
    question: "What is the total revenue by region?"
    sql: |
      SELECT region, SUM(revenue_usd) AS total_revenue
      FROM prod_db.analytics.tbl_sales
      GROUP BY region ORDER BY total_revenue DESC

# ---- Custom Instructions ----
module_custom_instructions:         # Computed LLM guidance
  sql_generation: |
    Use table aliases for readability.
  question_categorization: |
    Treat trend questions as UNAMBIGUOUS_SQL.

Physical vs Computed Elements

Logical vs Physical Table Names

tables:
  - name: SALES_FACT   # Logical
    base_table:
      table: TBL_SALES  # Physical

SELECT * FROM prod.sales.tbl_sales
-- Uses physical name. This is CORRECT!

Deprecated Fields — Never Use These

Step-by-Step Process

Context Gathering (Step 1)

• Name: simple identifier or FQN (DATABASE.SCHEMA.NAME)
• Target: database + schema for the semantic view
• Context: SQL queries, table references, and business context — all in one request

Two Creation Paths

Deployment Options

CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  'DATABASE.SCHEMA',
  '<yaml_content>',
  FALSE  -- FALSE = create
);

CREATE STAGE IF NOT EXISTS my_stage;
PUT file://model.yaml @my_stage/;

Validation — Always Validate Before Deploying

-- In Cortex Code:
reflect_semantic_model(semantic_model_file="path/to/model.yaml")

-- Via SQL (validate only, don't create):
CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  'DB.SCHEMA', '<yaml>', TRUE  -- TRUE = validate only
);

Size Limits

Domain-Based Splitting

When a single semantic view grows too large, split by business domain:

• One SV per business domain (Sales, Finance, Marketing, etc.)
• 50-100 columns each — focused on domain-specific questions
• Duplicate shared dimensions (e.g., date, region) across SVs
• Use Cortex Analyst routing to direct questions to the right SV (up to 40 SVs)

Descriptions Have the Biggest Impact

Rich, contextual descriptions are the single most impactful thing you can do for accuracy. They outperform synonyms for English-language environments.

- name: ACCOUNT_ID
  description: "Account ID"
  expr: account_id
  data_type: NUMBER

- name: ACCOUNT_ID
  description: "Unique identifier for
    Snowflake accounts. Used to
    track usage and link with
    customer information."
  expr: account_id
  data_type: NUMBER
  unique: true

Description Best Practices

Every description should cover:

• What the column represents
• How it's commonly used in queries
• Relationships to other data (foreign keys, hierarchies)
• Business context (what domain it belongs to)

Sample Values & Cortex Search

Enhancement Strategies

Optimization Priority

When fixing SQL generation issues, apply fixes in this order:

1. Enhance Dimensions & Facts
   Improve descriptions, add synonyms, sample_values. Lowest risk, highest impact.
2. Add Metrics
   Define common aggregations (SUM, COUNT, AVG) to prevent wrong aggregation logic.
3. Add Filters
   Named WHERE clauses for business logic (active customers, last 30 days, etc.).
4. Add/Fix Relationships
   Table joins. Requires primary key on at least one side.
5. Custom Instructions
   Last resort. Only when semantic model elements cannot express the logic.

Metrics

Facts are raw numeric columns. Metrics are computed aggregations you can add.

metrics:
  - name: TOTAL_REVENUE
    description: "Sum of all order amounts"
    expr: SUM(order_amount)
    access_modifier: public_access

  - name: UNIQUE_CUSTOMERS
    description: "Count of distinct customers"
    expr: COUNT(DISTINCT customer_id)
    access_modifier: public_access

  - name: AVG_ORDER_VALUE
    description: "Average value across all orders"
    expr: AVG(order_amount)
    access_modifier: public_access

Filters

Named WHERE clauses that must be nested under the table they apply to.

tables:
  - name: CUSTOMERS
    filters:
      - name: ACTIVE_CUSTOMERS
        description: "Customers with active status and recent purchases"
        expr: status = 'ACTIVE' AND last_purchase_date >= DATEADD(month, -6, CURRENT_DATE())

      - name: LAST_30_DAYS
        description: "Records from the last 30 days"
        expr: created_date >= DATEADD(day, -30, CURRENT_DATE())

Relationships (Joins)

relationships:
  - name: orders_to_customers
    left_table: ORDERS
    right_table: CUSTOMERS
    relationship_columns:
      - left_column: CUSTOMER_ID
        right_column: CUSTOMER_ID
    relationship_type: many_to_one
    join_type: left_outer

What Are VQRs?

Verified Query Results are question + SQL pairs that serve as few-shot examples for Cortex Analyst. When a user asks a question, the system finds the most similar VQR via cosine similarity and uses it as a template.

VQR Format & Naming

verified_queries:
  - name: vqr_0                            # Sequential: vqr_0, vqr_1, ...
    question: "What are the daily active users?"  # Natural language
    sql: |                                      # Pipe syntax for multi-line
      SELECT
        DATE_TRUNC('day', logged_at)::date AS ds,
        COUNT(DISTINCT user_id) AS active_users
      FROM analytics.logs.usage_logs
      WHERE logged_at >= '2024-01-01'
      GROUP BY ds
      ORDER BY ds

  - name: vqr_1
    question: "Top 10 customers by revenue?"
    sql: |
      SELECT customer_name, SUM(revenue) AS total_revenue
      FROM sales.transactions
      GROUP BY customer_name
      ORDER BY total_revenue DESC
      LIMIT 10

VQR Best Practices

When to Use Custom Instructions

Custom instructions are natural language guidance for the LLM. Only use them after trying all other optimization levers:

Two Types

module_custom_instructions:
  sql_generation: |
    Use table aliases for readability.
    For fiscal quarters, add 11 months
    before extracting quarter.
  question_categorization: |
    Treat trend questions as
    UNAMBIGUOUS_SQL.

custom_instructions: |-
  Unless explicitly asked for
  historical analysis, ALWAYS filter
  for snapshot_flag = 'Current'.

  "Biggest X" means frequency
  (COUNT), not value (SUM).

Writing Good Instructions

"Biggest [dimension]" means most
frequent:
- GROUP BY dimension
- COUNT(DISTINCT primary_key)
- Filter out NULLs

For "Who are biggest partners?",
use COUNT(DISTINCT id)
GROUP BY partner.

Three Audit Types

Best Practices Checks

Inconsistency Detection

Duplicate Detection

Identifies when custom instructions redundantly repeat information already in model elements:

Missing Relationships Thresholds

Only flagged when multiple tables share FK-like columns or dimension tables have no relationships.

Three-Step Debug Process

Step 1: Diagnosis

• Get the problematic natural language question from the user
• Generate SQL using Cortex Analyst with the current semantic view
• Execute the SQL and present results

Step 2: Root Cause Analysis

• Compare generated SQL against expected output
• Identify gaps in the semantic model
• Present findings and recommended fixes
• Wait for user approval before applying changes

Step 3: Apply Optimizations

• Apply approved fixes using the optimization priority order
• Validate with reflect_semantic_model
• Re-test the problematic question
• Exact match required — no "close enough"

Issue-to-Gap Mapping

Three Improvement Approaches

Agentic Optimization Workflow

-- Start optimization
CALL SYSTEM$CORTEX_ANALYST_CREATE_AGENTIC_OPTIMIZATION('{
  "semantic_model": {
    "semantic_view": "DB.SCHEMA.VIEW_NAME"
  },
  "warehouse": "MY_WH",
  "experimental": "{}"
}');

-- Check status
CALL SYSTEM$CORTEX_ANALYST_GET_AGENTIC_OPTIMIZATION('<optimization_name>');

-- List all optimizations
CALL SYSTEM$CORTEX_ANALYST_LIST_AGENTIC_OPTIMIZATIONS('{
  "semantic_model": {
    "semantic_view": "DB.SCHEMA.VIEW_NAME"
  },
  "experimental": "{}"
}');

-- Cancel if needed
CALL SYSTEM$CORTEX_ANALYST_CANCEL_AGENTIC_OPTIMIZATION('<optimization_name>');

Iterative Improvement Cycle

Re-Deployment After Changes

If the semantic view was exported from a database object, re-deploy after making changes:

-- Export current definition
SELECT SYSTEM$READ_YAML_FROM_SEMANTIC_VIEW('DB.SCHEMA.VIEW_NAME');

-- After editing locally, re-deploy
CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  'DB.SCHEMA',
  '<updated_yaml_content>',
  FALSE
);

Top 10 Mistakes to Avoid

The Semantic View Maturity Model

Quick Reference Checklist