Using SQL commands to create and manage semantic views¶
This topic explains how to use the following SQL commands to create and manage semantic views.
Creating a semantic view¶
To create a semantic view, execute the CREATE SEMANTIC VIEW command. You must use a role with the following privileges:
CREATE SEMANTIC VIEW on the schema where you are creating the semantic view.
USAGE on the database and schema where you are creating the semantic view.
SELECT on the tables and views used in the semantc view.
The semantic view must be valid. See How Snowflake validates semantic views.
The example uses the TPC-H sample data available in Snowflake. This dataset contains tables that represent a simplified business scenario with customers, orders, and line items.
The example creates a semantic view named tpch_rev_analysis, using the tables in the TPC-H data set. The semantic view
defines:
Three logical tables (
orders,customers, andline_items).A relationship between the
ordersandcustomerstables.A relationship between the
line_itemsandorderstables.Facts that will be used to calculate metrics.
Dimensions for the customer name, the order date, and the year in which the order was placed.
Metrics for the average value of an order and the average number of line items in an order.
CREATE SEMANTIC VIEW tpch_rev_analysis
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
)
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers,
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders
)
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name')
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
COMMENT = 'Semantic view for revenue analysis';
The next sections explain this example in more detail:
Note
For a full example, see Example of using SQL to create a semantic view.
Defining the logical tables¶
In the CREATE SEMANTIC VIEW command, use the TABLES clause to define the logical tables in the view. In this clause, you can:
Specify the physical table name and an optional alias.
Identify the columns in the logical table to be used as primary keys for relationships defined in this semantic view.
Add synonyms for the table (for enhanced discoverability).
Include a descriptive comment.
In the example presented earlier, the TABLES clause defines three logical tables:
An
orderstable containing the order information from the TPC-Horderstable.A
customerstable containing the customer information from the TPC-Hcustomerstable.A
line_itemtable containing the line items in orders from the TPC-Hlineitemtable.
The example identifies the columns to be used as primary keys for each logical table so that you can identify the relationships between the tables.
The example also provides synonyms and comments that describe the logical tables and make the data easier to discover.
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
Identifying the relationships between logical tables¶
In the CREATE SEMANTIC VIEW command, use the RELATIONSHIPS clause to identify the relationships between the tables in view. For each relationship, you specify:
An optional name for the relationship.
The name of the logical table containing the foreign key.
The columns in that table that define the foreign key.
The name of the logical table containing the primary key.
The columns in that table that define the primary key. This is optional if you already used PRIMARY KEY in the TABLES clause to identify the primary key for the table.
In the example presented earlier, the RELATIONSHIPS clause specifies two relationships:
A relationship between the
ordersandcustomerstables. In theorderstable,o_custkeyis the foreign key that refers to the primary key in thecustomerstable (c_custkey).A relationship between the
line_itemsandorderstables. In theline_itemstable,l_orderkeyis the foreign key that refers to the primary key in theorderstable (o_orderkey).
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers (c_custkey),
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders (o_orderkey)
)
Defining facts, dimensions, and metrics¶
In the CREATE SEMANTIC VIEW command, use the FACTS, DIMENSIONS, and METRICS clauses to define the facts, dimensions, and metrics in the semantic view.
You must define at least one dimension or metric in the semantic view.
For each fact, dimension, or metric, you specify:
The logical table it belongs to.
A name for the fact, dimension, or metric.
The SQL expression to calculate it.
Optional synonyms and comments.
The example presented earlier defines several facts, dimensions, and metrics:
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name')
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
Listing semantic views¶
To list semantic views in the current schema or a specified schema, execute the SHOW SEMANTIC VIEWS command. For example:
SHOW SEMANTIC VIEWS;
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
| created_on | name | database_name | schema_name | comment | owner | owner_role_type | extension |
|-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------|
| 2025-03-20 15:06:34.039 -0700 | MY_NEW_SEMANTIC_MODEL | MY_DB | MY_SCHEMA | A semantic model created through the wizard. | MY_ROLE | ROLE | ["CA"] |
| 2025-02-28 16:16:04.002 -0800 | O_TPCH_SEMANTIC_VIEW | MY_DB | MY_SCHEMA | NULL | MY_ROLE | ROLE | NULL |
| 2025-03-21 07:03:54.120 -0700 | TPCH_REV_ANALYSIS | MY_DB | MY_SCHEMA | Semantic view for revenue analysis | MY_ROLE | ROLE | NULL |
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
You can also query the views for semantic views in the ACCOUNT_USAGE and INFORMATION_SCHEMA schemas.
Viewing the details about a semantic view¶
To view the details of a semantic view, execute the DESCRIBE SEMANTIC VIEW command. For example:
DESCRIBE SEMANTIC VIEW tpch_rev_analysis;
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| object_kind | object_name | parent_entity | property | property_value |
|--------------+------------------------------+---------------+--------------------------+----------------------------------------|
| NULL | NULL | NULL | COMMENT | Semantic view for revenue analysis |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_NAME | CUSTOMER |
| TABLE | CUSTOMERS | NULL | PRIMARY_KEY | ["C_CUSTKEY"] |
| TABLE | CUSTOMERS | NULL | COMMENT | Main table for customer data |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | TABLE | CUSTOMERS |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | EXPRESSION | customers.c_name |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | DATA_TYPE | VARCHAR(25) |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | SYNONYMS | ["customer name"] |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | COMMENT | Name of the customer |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_NAME | LINEITEM |
| TABLE | LINE_ITEMS | NULL | PRIMARY_KEY | ["L_ORDERKEY","L_LINENUMBER"] |
| TABLE | LINE_ITEMS | NULL | COMMENT | Line items in orders |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | TABLE | LINE_ITEMS |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | REF_TABLE | ORDERS |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | FOREIGN_KEY | ["L_ORDERKEY"] |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | REF_KEY | ["O_ORDERKEY"] |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | TABLE | LINE_ITEMS |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | EXPRESSION | l_extendedprice * (1 - l_discount) |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | DATA_TYPE | NUMBER(25,4) |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | COMMENT | Extended price after discount |
| FACT | LINE_ITEM_ID | LINE_ITEMS | TABLE | LINE_ITEMS |
| FACT | LINE_ITEM_ID | LINE_ITEMS | EXPRESSION | CONCAT(l_orderkey, '-', l_linenumber) |
| FACT | LINE_ITEM_ID | LINE_ITEMS | DATA_TYPE | VARCHAR(134217728) |
| TABLE | ORDERS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | ORDERS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | ORDERS | NULL | BASE_TABLE_NAME | ORDERS |
| TABLE | ORDERS | NULL | SYNONYMS | ["sales orders"] |
| TABLE | ORDERS | NULL | PRIMARY_KEY | ["O_ORDERKEY"] |
| TABLE | ORDERS | NULL | COMMENT | All orders table for the sales domain |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | TABLE | ORDERS |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | REF_TABLE | CUSTOMERS |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | FOREIGN_KEY | ["O_CUSTKEY"] |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | REF_KEY | ["C_CUSTKEY"] |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | TABLE | ORDERS |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | EXPRESSION | AVG(orders.count_line_items) |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | DATA_TYPE | NUMBER(36,6) |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | COMMENT | Average number of line items per order |
| FACT | COUNT_LINE_ITEMS | ORDERS | TABLE | ORDERS |
| FACT | COUNT_LINE_ITEMS | ORDERS | EXPRESSION | COUNT(line_items.line_item_id) |
| FACT | COUNT_LINE_ITEMS | ORDERS | DATA_TYPE | NUMBER(18,0) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | TABLE | ORDERS |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | EXPRESSION | AVG(orders.o_totalprice) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | DATA_TYPE | NUMBER(30,8) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | COMMENT | Average order value across all orders |
| DIMENSION | ORDER_DATE | ORDERS | TABLE | ORDERS |
| DIMENSION | ORDER_DATE | ORDERS | EXPRESSION | o_orderdate |
| DIMENSION | ORDER_DATE | ORDERS | DATA_TYPE | DATE |
| DIMENSION | ORDER_DATE | ORDERS | COMMENT | Date when the order was placed |
| DIMENSION | ORDER_YEAR | ORDERS | TABLE | ORDERS |
| DIMENSION | ORDER_YEAR | ORDERS | EXPRESSION | YEAR(o_orderdate) |
| DIMENSION | ORDER_YEAR | ORDERS | DATA_TYPE | NUMBER(4,0) |
| DIMENSION | ORDER_YEAR | ORDERS | COMMENT | Year when the order was placed |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
Getting the SQL statement for a semantic view¶
You can call the GET_DDL function to retrieve the DDL statement that created a semantic view.
When calling GET_DDL, pass in 'SEMANTIC_VIEW' as the object type. For example:
SELECT GET_DDL('SEMANTIC_VIEW', 'tpch_rev_analysis', TRUE);
+-----------------------------------------------------------------------------------+
| GET_DDL('SEMANTIC_VIEW', 'TPCH_REV_ANALYSIS', TRUE) |
|-----------------------------------------------------------------------------------|
| create or replace semantic view DYOSHINAGA_DB.DYOSHINAGA_SCHEMA.TPCH_REV_ANALYSIS |
| tables ( |
| ORDERS primary key (O_ORDERKEY) with synonyms=('sales orders') comment='All orders table for the sales domain', |
| CUSTOMERS as CUSTOMER primary key (C_CUSTKEY) comment='Main table for customer data', |
| LINE_ITEMS as LINEITEM primary key (L_ORDERKEY,L_LINENUMBER) comment='Line items in orders' |
| ) |
| relationships ( |
| ORDERS_TO_CUSTOMERS as ORDERS(O_CUSTKEY) references CUSTOMERS(C_CUSTKEY), |
| LINE_ITEM_TO_ORDERS as LINE_ITEMS(L_ORDERKEY) references ORDERS(O_ORDERKEY) |
| ) |
| facts ( |
| ORDERS.COUNT_LINE_ITEMS as COUNT(line_items.line_item_id), |
| LINE_ITEMS.DISCOUNTED_PRICE as l_extendedprice * (1 - l_discount) comment='Extended price after discount', |
| LINE_ITEMS.LINE_ITEM_ID as CONCAT(l_orderkey, '-', l_linenumber) |
| ) |
| dimensions ( |
| ORDERS.ORDER_DATE as o_orderdate comment='Date when the order was placed', |
| ORDERS.ORDER_YEAR as YEAR(o_orderdate) comment='Year when the order was placed', |
| CUSTOMERS.CUSTOMER_NAME as customers.c_name with synonyms=('customer name') comment='Name of the customer' |
| ) |
| metrics ( |
| ORDERS.AVERAGE_LINE_ITEMS_PER_ORDER as AVG(orders.count_line_items) comment='Average number of line items per order', |
| ORDERS.ORDER_AVERAGE_VALUE as AVG(orders.o_totalprice) comment='Average order value across all orders' |
| ); |
+-----------------------------------------------------------------------------------+
Removing a semantic view¶
To remove a semantic view, execute the DROP SEMANTIC VIEW command. For example:
DROP SEMANTIC VIEW tpch_rev_analysis;
Access control privileges on semantic views¶
You can grant the following privileges on an existing semantic view.
Privilege |
Usage |
|---|---|
REFERENCES |
Enables viewing the structure of a view (but not the data) by executing a DESCRIBE or SHOW command or by querying the Information Schema. This includes the DESCRIBE and SHOW commands of the underlying entities, calculations and relationships. |
OWNERSHIP |
Grants full control over the semantic view. Required to alter a semantic view. Only a single role can hold this privilege on a specific object at a time. Note that in a managed access schema, only the schema owner (that is, the role with the OWNERSHIP privilege on the schema) or a role with the MANAGE GRANTS privilege can grant or revoke privileges on objects in the schema, including future grants. |
ALL [ PRIVILEGES ] |
Grants all privileges, except OWNERSHIP, on a semantic view. |