Semantic View Creation Guide: Autopilot or CoCo, or both?
CloudHive is bringing together experts from across the Snowflake community to share hands-on experience, technical insights and different perspectives from the field. In this latest article in the series, Anastasiia Stefanska, Snowflake Data Superhero and Tech Lead at TUI looks at one of this year’s hottest topics: semantic views in Snowflake, and two different ways of creating them using Semantic View Autopilot and CoCo.
Intro
Welcome to the world of semantics, one of this year's hottest topics. In this article we explore two ways of creating semantic views in Snowflake. We'll explore the peculiarities of semantic view creation and look at common pitfalls you can avoid with each method. Let’s begin by having a quick refresher on the basics of this technology.
What is a semantic view in Snowflake?
Semantic views are schema-level objects within Snowflake which store business context of the structured data in Snowflake: table descriptions, relationships between tables, metric definitions, and more. Think of it as a way to capture and store subject-matter knowledge. The main function of semantic views is to feed knowledge to Cortex Analyst, enabling it to generate more precise, context-relevant text-to-SQL.
Where can I build a Semantic View in Snowflake?
I promised you two ways of creating a semantic view, here they are:
- Create the semantic view in the Snowsight UI using the Autopilot experience, a no-code option for users who prefer a visual interface.
- Use the CoCo /semantic_studio skill to create the semantic view in a conversational manner.
How to build a Semantic View in Snowflake UI?
Let's proceed with an example of creating a semantic view in Snowsight. For this setup, we are going to use a star-schema dataset with the data from Terry Pratchett’s Discworld universe:
- Table DIM_BOOKS: BOOK_ID, TITLE, PUBLICATION_YEAR, SUB_SERIES
- Table DIM_CHARACTERS: CHARACTER_ID, CHARACTER_NAME, SPECIES, PRIMARY_AFFILIATION
- Table FACT_APPEARANCES: BOOK_ID, CHARACTER_ID, ROLE
To access the Semantic View Autopilot in Snowflake, in the left panel menu select AI & ML → Analyst → Create with Autopilot. The window that opens has four steps. In the first step, you'll see an option to provide context from external sources: query history, a Tableau or Power BI file, or OSI specs. If the data is broadly used in the organization, do not skip this option - especially if there are metrics or calculated fields defined in those entities, as you will get a free conversion from the existing reports/queries into the Snowflake semantic view. If the data is relatively new and there is no existing context to draw on, skip this step.
Semantic View Autopilot Step 1: provide context from external resources
Moving to step 2, name your semantic view and define what type of entity it is. Among the legacy options, you can create or upload a semantic model's YAML file (previous versions of semantic models in Snowflake were defined with this format). It is not recommended to use the legacy YAML file option, as it could limit your options downstream. At least one limitation is known to date: the ability to use Cortex Analyst with the Snowflake MCP server is limited to semantic views, not semantic models.
The first option is Snowflake semantic view - use it to avoid legacy limitations
Steps 3 and 4 allow you to choose the entities (tables, views) for the semantic view and columns within them. An important setting, recommended by Snowflake and selected by default, is to include sample values. Note, however, that it makes most sense for categorical items and you may remove the values manually later for the items where examples of values are less valuable.
Another setting included by default is “Add descriptions” - these also apply to parent tables/views, and not only columns. Descriptions are AI-generated and derived from the data. My recommendation is to review and fine-tune them before releasing the view.
Semantic view allows you to choose a subset of columns from the tables/views
At this point, your semantic view is technically ready. It likely contains the following entities:
- Dimensions, time dimensions - attributes of the entities.
- Facts - a field that can be aggregated.
Also, if you benefited from step 1 - adding external context, you might have these already pre-filled:
- Named filters - saved SQL to filter the data by.
- Metrics - saved SQL aggregation on column(s) of the parent table.
In reality, however, this is just a bare minimum carcass of the semantic view, and now the main exercise begins: filling it in with the business context. In addition to the above, here are the entities which must be added to the view before it can be released:
- Synonyms
- Relationships
- Model-level/derived metrics
- Verified queries
- Custom instructions.
Let’s tackle these one by one!
How to add synonyms to the semantic view?
Synonyms allow Cortex Analyst to understand users' requests better. As the main goal of semantic views is to convert human questions into accurate SQL, it is critical to know how a typical user addresses the data.
In our data model, users may address the DIM_BOOKS column of SUB_SERIES as simply “Series”
How to add relationships to the semantic views?
Relationships are the primary-foreign key dependencies between the view components. In our case, we would like to have 2 relationships between the fact and dim tables, so we code them as follows:
Relationships allow 3 types of JOIN: EQUI, ASOF and RANGE
<aside>
💡
Relationships are a critical and dangerous part of data modeling for semantic views. For complex models, Snowflake allows more than one relationship path between tables - this is called a multi-path. Having multi-paths creates ambiguity for physical joins which in turn prevents the calculation of the metrics which rely on these joins. This is because for every model-level metric that relies on a combination of tables, only one relationship path must exist. See the next section for an additional example.
</aside>
How can I define SQL in my semantic view?
One of the main challenges for human analysts in the enterprise is consistently converting business logic to SQL. We have all been in a situation where two reports from the same data show a different value for the same named metric - and both are considered correct by the teams who created them. The devil is in the detail SQL logic of the reports. Below are the sections in semantic views which are a mini SQL reference of a glossary of business definitions:
- Named filters - a way to define a WHERE clause for commonly filtered items. - WITCHES_SERIES: SUB_SERIES = 'Witches'
- Table-level metric - a calculation derived from a column of a logical table.
- TOTAL_BOOKS: COUNT(DISTINCT BOOK_ID)
- TOTAL_CHARACTERS: COUNT(DISTINCT CHARACTER_ID) - Model-level metric - a calculation which is derived from multiple metrics.
- TOTAL_DISCWORLD_ENTITIES: DIM_BOOKS.TOTAL_BOOKS + DIM_CHARACTERS.TOTAL_CHARACTERS
<aside>
💡
There's a shortcut for defining a metric that spans multiple tables: referencing a second table in the table-level metric. For example, if we want to count total CAREER_SPAN_YEARS of a character, we could use MAX(DIM_BOOKS.PUBLICATION_YEAR) - MIN(DIM_BOOKS.PUBLICATION_YEAR) on a FACT_APPEARANCES table. It only works if a metric on a table references columns from tables that are reachable via a relationship from that table.
Use this method with caution, as it will not work if multi-paths exist between the tables. In our testing, this method gives a warning in the UI, which proved to be a false-positive in queries.
</aside>
- Verified query - a complex question that a user can ask about the data. -Which characters appear in the most books:
SELECT
dc.CHARACTER_NAME,
COUNT(DISTINCT fa.BOOK_ID) AS book_count
FROM
FACT_APPEARANCES AS fa
INNER JOIN DIM_CHARACTERS AS dc ON fa.CHARACTER_ID = dc.CHARACTER_ID
GROUP BY
dc.CHARACTER_NAME
ORDER BY
book_count DESC
We highly recommend that a subject matter expert with SQL knowledge fill in and maintain all of the above fields. Without them, the text-to-SQL conversion will be an educated guessing game. Snowflake recommends not more than 20 verified queries per model to avoid performance degradation.
What shall I add to the custom instructions of the semantic view?
Custom instructions allow you to guide the style of the SQL generated by Cortex Analyst. For example, if you notice that your text fields are not filtered properly based on capitalization, you can instruct the Analyst to always wrap the text fields in the WHERE clause in the UPPER() function on both sides.
Custom instruction to ensure case-insensitive matching
How do I add Cortex Search to semantic view?
If your use case needs to match strings by meaning rather than exact text, you can wrap a column with Cortex Search to ensure semantic search over it. This ensures fuzzy logic matching and reduces the number of empty results. The Cortex Search service on the column must pre-exist and cannot be created in the same flow.
Adding Cortex Search over Species column
In which order do I create a semantic view?
In the process of adding context to the semantic view, you might run out of ideas for what to add next. The rule of thumb is to follow the flow:
- First validate all the AI-generated content (check descriptions, add/remove sample values).
- Proceed with adding new structures (synonyms, relationships, metrics, etc).
- Proceed to the Suggestions tab.
The reason for this particular order is that the Suggestions tab relies on existing context, and you want to make sure it uses only validated content.
Review and accept/edit/dismiss the suggestions after updating the basic entities of the semantic views
How can I create a semantic view with CoCo?
At this point, you should have a really good understanding of the components of the semantic view and the Semantic View Autopilot flow in the Snowsight UI. Now, let's switch gears and do a similar exercise with CoCo. Setting the scene, we are going to use the special skill /semantic_studio for this. Calling the skill in the UI allows us to select the option to “Work with Semantic Views”.
Task selector in CoCo after calling the /semantic_studio skill
The flow is similar to the Autopilot one: define the source tables, name the semantic view and its location:
Creation flow of the Semantic View in CoCo
As a result of this process, we arrive at the same point as after the four Autopilot steps. The next proposed stage is again similar - to fill in the extra context:
CoCo suggests to add descriptions, relationships, VQRs (verifies queries) and audit the resulting view
These steps can be completed conversationally, so for the sake of conciseness, I'll show one example of an interaction here:
CoCo curated creation of a verified query based on the user requirements
Upon filling in those details, as a result of using either method, you'll have a working semantic view. So what is next? Are we done? Not quite. Let's test them first!
CoCo unique feature - Semantic View Auditor
Once the view has been created by either method, we can use CoCo to audit the semantic view against Snowflake's best practices. Use the same skill as before, but this time ask it to audit the view we created.
Call the auditor using the /semantic_studio CoCo skill
In addition to calling the auditor, you can also point CoCo to a particular part of the semantic view, which requires an extra test:
Checking all the metrics work for the semantic view we have created
How to create the semantic view with SQL?
This method stands apart from the first two, as you can see it is a pure code-based approach without any guidance or steps. Useful applications of this method from my experience are centered around deployment automation and version control. If you already have the initial version and would like to get the full SQL for it, run SELECT GET_DDL('SEMANTIC VIEW', 'full_view_name') to get code like this:
CREATE OR REPLACE SEMANTIC VIEW DISCWORLD_ANALYTICS
TABLES (
PRATCHETT_DB.DISCWORLD.DIM_BOOKS
PRIMARY KEY (BOOK_ID)
COMMENT = 'Represents individual Discworld novels.',
PRATCHETT_DB.DISCWORLD.DIM_CHARACTERS
PRIMARY KEY (CHARACTER_ID)
COMMENT = 'Represents unique named characters in the Discworld series.',
PRATCHETT_DB.DISCWORLD.FACT_APPEARANCES
COMMENT = 'Records individual character appearances in books. Grain: one row per character per book.'
)
RELATIONSHIPS (
FACT_APPEARANCES_TO_DIM_BOOKS AS FACT_APPEARANCES(BOOK_ID) REFERENCES DIM_BOOKS(BOOK_ID),
FACT_APPEARANCES_TO_DIM_CHARACTERS AS FACT_APPEARANCES(CHARACTER_ID) REFERENCES DIM_CHARACTERS(CHARACTER_ID)
)
FACTS (
DIM_BOOKS.WITCHES_SERIES LABELS = (filter) AS SUB_SERIES = 'Witches'
WITH SYNONYMS = ('Lancre witches','Witches chronicles')
COMMENT = 'Only show books which belong to the Witches series'
)
DIMENSIONS (
DIM_BOOKS.BOOK_ID AS BOOK_ID,
DIM_BOOKS.PUBLICATION_YEAR AS PUBLICATION_YEAR,
DIM_BOOKS.SUB_SERIES AS SUB_SERIES,
DIM_BOOKS.TITLE AS TITLE,
DIM_CHARACTERS.CHARACTER_ID AS CHARACTER_ID,
DIM_CHARACTERS.CHARACTER_NAME AS CHARACTER_NAME
SAMPLE_VALUES ('Death','Sam Vimes','Granny Weatherwax','Vetinari','Rincewind',...),
DIM_CHARACTERS.PRIMARY_AFFILIATION AS PRIMARY_AFFILIATION,
DIM_CHARACTERS.SPECIES AS SPECIES,
FACT_APPEARANCES.BOOK_ID AS BOOK_ID,
FACT_APPEARANCES.CHARACTER_ID AS CHARACTER_ID,
FACT_APPEARANCES.ROLE AS ROLE
)
METRICS (
TOTAL_DISCWORLD_ENTITIES AS DIM_BOOKS.TOTAL_BOOKS + DIM_CHARACTERS.TOTAL_CHARACTERS,
DIM_BOOKS.TOTAL_BOOKS AS COUNT(*),
DIM_CHARACTERS.TOTAL_CHARACTERS AS COUNT(*),
FACT_APPEARANCES.APPEARANCE_COUNT AS COUNT(*),
FACT_APPEARANCES.CAREER_SPAN_YEARS AS MAX(DIM_BOOKS.PUBLICATION_YEAR) - MIN(DIM_BOOKS.PUBLICATION_YEAR),
FACT_APPEARANCES.CHARACTER_SERIES_COUNT AS COUNT(DISTINCT DIM_BOOKS.SUB_SERIES),
FACT_APPEARANCES.MAJOR_ROLE_COUNT AS SUM(CASE WHEN ROLE = 'Major' THEN 1 ELSE 0 END)
)
COMMENT = 'Literary analytics for Terry Pratchett''s Discworld series.'
AI_SQL_GENERATION 'When filtering on text columns, always wrap in UPPER() and capitalize the comparison string.'
AI_VERIFIED_QUERIES (
"Which characters appear in the most books?" AS (
QUESTION 'Which characters appear in the most books?'
SQL '...'
),
"In which books outside of Wyrd Sisters does character Granny Weatherwax appear?" AS (
QUESTION '...'
SQL '...'
),
"Show all books for each character outside of their primary series" AS (
QUESTION '...'
SQL '...'
)
);
How much do Semantic Views cost?
Semantic views are considered metadata and as such are free. There is no storage cost and no compute cost for the semantic view itself. There are two ways you can incur costs with semantic views:
Querying semantic views directly incurs standard warehouse costs.
The main cost is associated with Cortex Analyst, which relies on semantic views. The standalone Analyst API bills 67 credits per 1,000 messages, while Cortex Analyst invoked through Cortex Agents uses token-based AI Credits instead. I cover both in detail in the previous article.
Conclusions
Semantic views encapsulate the value of the business context. Regardless of which method you choose for creating it, the real added value still comes from the subject matter input you provide. Both the Autopilot and CoCo flows create a carcass - a vessel which can be filled with the valuable knowledge of the experts. The human remains in control during creation, but even more so at the fine-tuning step. It is critical to treat these semantic view creation flows as helpers to transfer human knowledge, and not replace it. I would recommend starting with the Semantic View Autopilot and polishing it with the CoCo auditor and metrics validation.