Building Semantic Views
Overview
A Semantic View is a Snowflake object that contains a YAML definition mapping physical tables to business concepts. This lesson covers the YAML structure, the DDL syntax, and best practices for building production-quality semantic views.
YAML Definition Structure
A semantic view YAML has these top-level sections:
name: sales_analytics
tables:
- name: orders
base_table:
database: analytics_db
schema: public
table: fact_orders
dimensions:
- name: order_status
expr: status
description: "Current status of the order"
synonyms: [status, state]
measures:
- name: total_revenue
expr: SUM(amount)
description: "Sum of order amounts in USD"
default_aggregation: sum
time_dimensions:
- name: order_date
expr: created_at
description: "When the order was placed"
data_type: date
- name: customers
base_table:
database: analytics_db
schema: public
table: dim_customers
dimensions:
- name: region
expr: customer_region
description: "Customer geographic region"
relationships:
- name: orders_to_customers
left_table: orders
right_table: customers
relationship_columns:
- left_column: customer_id
right_column: customer_id
join_type: left_outer
relationship_type: many_to_one
CREATE SEMANTIC VIEW Syntax
CREATE OR REPLACE SEMANTIC VIEW analytics_db.public.sales_semantic_view
COMMENT = 'Sales analytics semantic model for Cortex Analyst'
AS $$
name: sales_analytics
tables:
- name: orders
base_table:
database: analytics_db
schema: public
table: fact_orders
dimensions:
- name: order_status
expr: status
description: "Current status of the order"
measures:
- name: total_revenue
expr: SUM(amount)
description: "Total revenue in USD"
default_aggregation: sum
time_dimensions:
- name: order_date
expr: created_at
description: "Date the order was placed"
data_type: date
$$;
Linking to Physical Tables
Each table entry in the YAML maps to a physical table via base_table:
| YAML Field | Purpose | Example |
|---|---|---|
database | Source database | analytics_db |
schema | Source schema | public |
table | Physical table name | fact_orders |
The expr field on dimensions and measures references columns from this physical table. You can use SQL expressions:
measures:
- name: gross_margin_pct
expr: "SUM(revenue - cost) / NULLIF(SUM(revenue), 0) * 100"
description: "Gross margin as a percentage"
default_aggregation: sum
Defining Metrics and KPIs
Measures in a semantic view serve as governed metric definitions. Define them once, and every Cortex Analyst query uses the same calculation.
measures:
- name: monthly_recurring_revenue
expr: "SUM(CASE WHEN subscription_type = 'recurring' THEN amount ELSE 0 END)"
description: "MRR from active recurring subscriptions"
default_aggregation: sum
synonyms:
- MRR
- recurring revenue
- name: customer_lifetime_value
expr: "SUM(total_spend) / NULLIF(COUNT(DISTINCT customer_id), 0)"
description: "Average total spend per customer"
default_aggregation: sum
synonyms:
- CLV
- LTV
Filters
Filters define reusable WHERE-clause conditions:
filters:
- name: active_customers_only
expr: "is_active = TRUE AND deleted_at IS NULL"
description: "Exclude inactive and deleted customers"
Best Practices
Naming Conventions
| Element | Convention | Example |
|---|---|---|
| Semantic view name | <domain>_semantic_view | sales_semantic_view |
| Table names | Singular, lowercase | order, customer |
| Dimensions | Snake_case nouns | product_category |
| Measures | Snake_case with aggregation hint | total_revenue, avg_order_value |
| Time dimensions | Include _date or _timestamp | order_date |
Organization Tips
- One semantic view per domain: sales, marketing, finance each get their own
- Write thorough descriptions: the AI relies on them heavily
- Add synonyms generously: think about how different users phrase the same concept
- Keep measures simple: prefer clear single-metric definitions over complex compound calculations
- Test incrementally: add a few columns, test with Cortex Analyst, then expand
Validating Your Semantic View
After creating the semantic view, verify it:
-- Check the semantic view exists
SHOW SEMANTIC VIEWS IN SCHEMA analytics_db.public;
-- Describe its structure
DESCRIBE SEMANTIC VIEW analytics_db.public.sales_semantic_view;
Common Errors
| Error | Cause | Fix |
|---|---|---|
Invalid YAML syntax | Indentation or quoting issue | Validate YAML before deploying |
Base table not found | Wrong database/schema/table reference | Verify the physical table exists |
Duplicate dimension name | Same name used across tables | Use unique names or prefix with table |
Invalid expression | SQL syntax error in expr | Test the expression in a standalone query |
Next Steps
With your semantic view built, proceed to Verified Queries and Metrics to improve Cortex Analyst accuracy with curated query examples.