Skip to main content

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 FieldPurposeExample
databaseSource databaseanalytics_db
schemaSource schemapublic
tablePhysical table namefact_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​

ElementConventionExample
Semantic view name<domain>_semantic_viewsales_semantic_view
Table namesSingular, lowercaseorder, customer
DimensionsSnake_case nounsproduct_category
MeasuresSnake_case with aggregation hinttotal_revenue, avg_order_value
Time dimensionsInclude _date or _timestamporder_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​

ErrorCauseFix
Invalid YAML syntaxIndentation or quoting issueValidate YAML before deploying
Base table not foundWrong database/schema/table referenceVerify the physical table exists
Duplicate dimension nameSame name used across tablesUse unique names or prefix with table
Invalid expressionSQL syntax error in exprTest 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.