Snowflake Semantic Views: A Hands-On Three-Table Tutorial

A regular SQL view packages a query. A Snowflake semantic view packages a small business model: logical tables, the relationships between them, and named dimensions and metrics that queries can request. That extra layer is useful only if the model is understandable and produces the result you expect, so let’s build one from scratch and compare its answer with ordinary SQL.

The example is deliberately tiny and synthetic. We will create two customers, three products, and five orders. Each order has one customer and one product. The question is: how much revenue, how many orders, and how many units do we have for each customer region and product category?

erDiagram
  CUSTOMERS ||--o{ ORDERS : places
  PRODUCTS ||--o{ ORDERS : appears_in

The important modeling detail is the row grain: one row in ORDERS represents one order for one product. The customer and product tables each have one row per key. This makes the two joins many-to-one from the orders table to their dimensions.

1. Create a Small Dataset

The commands below create three transient tables in the sandbox schema and insert a few rows. Replace the schema with one where your role can create and query these objects. Transient tables are used here because this is a short-lived tutorial, not a request to keep sample data permanently.

USE SCHEMA GLOBAL_SANDBOX.USE_CASES_GOLD;

CREATE TRANSIENT TABLE BLOG_SV_TUTORIAL_CUSTOMERS_20260928 (
  CUSTOMER_ID VARCHAR PRIMARY KEY,
  REGION VARCHAR
);

INSERT INTO BLOG_SV_TUTORIAL_CUSTOMERS_20260928 VALUES
  ('C-100', 'North'),
  ('C-200', 'South');

CREATE TRANSIENT TABLE BLOG_SV_TUTORIAL_PRODUCTS_20260928 (
  PRODUCT_ID VARCHAR PRIMARY KEY,
  CATEGORY VARCHAR
);

INSERT INTO BLOG_SV_TUTORIAL_PRODUCTS_20260928 VALUES
  ('P-10', 'Tools'),
  ('P-20', 'Books'),
  ('P-30', 'Tools');

CREATE TRANSIENT TABLE BLOG_SV_TUTORIAL_ORDERS_20260928 (
  ORDER_ID VARCHAR PRIMARY KEY,
  CUSTOMER_ID VARCHAR,
  PRODUCT_ID VARCHAR,
  QUANTITY NUMBER(10, 0),
  AMOUNT NUMBER(10, 2)
);

INSERT INTO BLOG_SV_TUTORIAL_ORDERS_20260928 VALUES
  ('O-001', 'C-100', 'P-10', 2, 25.00),
  ('O-002', 'C-100', 'P-20', 1, 15.00),
  ('O-003', 'C-200', 'P-10', 3, 75.00),
  ('O-004', 'C-200', 'P-30', 1, 50.00),
  ('O-005', 'C-100', 'P-10', 1, 25.00);

For example, C-100 has two Tools orders totaling 50.00 and one Books order totaling 15.00. C-200 has two Tools orders totaling 125.00. Those hand-checkable totals will help us catch a bad relationship or aggregation later.

2. Describe the Model

The physical table names become logical names inside the model: orders, customers, and products. These are the names the semantic definitions and query use.

CREATE SEMANTIC VIEW BLOG_SV_TUTORIAL_20260928
  TABLES (
    orders AS BLOG_SV_TUTORIAL_ORDERS_20260928 PRIMARY KEY (ORDER_ID),
    customers AS BLOG_SV_TUTORIAL_CUSTOMERS_20260928 PRIMARY KEY (CUSTOMER_ID),
    products AS BLOG_SV_TUTORIAL_PRODUCTS_20260928 PRIMARY KEY (PRODUCT_ID)
  )
  RELATIONSHIPS (
    order_customer AS orders (CUSTOMER_ID) REFERENCES customers (CUSTOMER_ID),
    order_product AS orders (PRODUCT_ID) REFERENCES products (PRODUCT_ID)
  )
  DIMENSIONS (
    customers.region AS customers.REGION,
    products.category AS products.CATEGORY
  )
  METRICS (
    orders.revenue AS SUM(orders.AMOUNT),
    orders.order_count AS COUNT(DISTINCT orders.ORDER_ID),
    orders.units AS SUM(orders.QUANTITY)
  );

Read the clauses from the outside in:

• TABLES lists the physical inputs and assigns logical names. The primary keys describe the grain we intend: one customer, product, or order per key.
• RELATIONSHIPS tells Snowflake how the order table points to each dimension. It is semantic metadata; it does not rewrite the source tables or populate their foreign keys.
• DIMENSIONS defines attributes we can group or filter by. A dimension is row-level, so customers.region is just the customer's REGION column.
• METRICS defines aggregate calculations. Revenue is a sum, order count counts unique order IDs, and units sums the quantity.

The key declarations are modeling assumptions. The sample data satisfies them, but this example does not validate production data quality or prove that a larger source table has no duplicate keys.

3. Ask for Dimensions and Metrics

The SEMANTIC_VIEW table function is the query interface. Put the dimensions and metrics you want inside it; dimensions define the groups in the result.

SELECT *
FROM SEMANTIC_VIEW(
  BLOG_SV_TUTORIAL_20260928
  DIMENSIONS customers.region, products.category
  METRICS orders.revenue, orders.order_count, orders.units
)
ORDER BY region, category;

This should produce three groups:

Region	Category	Revenue	Orders	Units
North	Books	15.00	1	1
North	Tools	50.00	2	3
South	Tools	125.00	2	4

Notice what the query does not contain: explicit joins, GROUP BY, or aggregate expressions. Those rules live in the model. The SQL still names the requested measures and grouping fields, so it remains clear what this particular result contains.

Filtering works in the semantic query too. Here is the same question, restricted to the North region:

SELECT *
FROM SEMANTIC_VIEW(
  BLOG_SV_TUTORIAL_20260928
  DIMENSIONS customers.region, products.category
  METRICS orders.revenue, orders.order_count, orders.units
  WHERE customers.region = 'North'
)
ORDER BY category;

The filter returns the two North groups, Books and Tools. The WHERE condition refers to a semantic dimension, and Snowflake applies it as part of evaluating the requested metrics.

4. Check Against Ordinary SQL

A successful CREATE only tells us the definition passed validation. To check the modeled result, calculate the same aggregates with regular joins and compare both sets in both directions. MINUS in each direction catches a missing row as well as a differing metric value.

WITH semantic_result AS (
  SELECT
    region,
    category,
    ROUND(revenue, 2) AS revenue,
    order_count AS order_count,
    units AS units
  FROM SEMANTIC_VIEW(
    BLOG_SV_TUTORIAL_20260928
    DIMENSIONS customers.region, products.category
    METRICS orders.revenue, orders.order_count, orders.units
  )
),
direct_result AS (
  SELECT
    c.REGION AS region,
    p.CATEGORY AS category,
    ROUND(SUM(o.AMOUNT), 2) AS revenue,
    COUNT(DISTINCT o.ORDER_ID) AS order_count,
    SUM(o.QUANTITY) AS units
  FROM BLOG_SV_TUTORIAL_ORDERS_20260928 AS o
  JOIN BLOG_SV_TUTORIAL_CUSTOMERS_20260928 AS c
    ON o.CUSTOMER_ID = c.CUSTOMER_ID
  JOIN BLOG_SV_TUTORIAL_PRODUCTS_20260928 AS p
    ON o.PRODUCT_ID = p.PRODUCT_ID
  GROUP BY c.REGION, p.CATEGORY
)
SELECT
  (SELECT COUNT(*) FROM semantic_result) AS semantic_groups,
  (SELECT COUNT(*) FROM direct_result) AS direct_groups,
  (SELECT COUNT(*) FROM (
    SELECT * FROM semantic_result MINUS SELECT * FROM direct_result
  )) +
  (SELECT COUNT(*) FROM (
    SELECT * FROM direct_result MINUS SELECT * FROM semantic_result
  )) AS mismatched_groups;

On this data, the result was 3 semantic groups, 3 direct-SQL groups, and 0 mismatches. The filtered query returned the two expected North groups. That confirms this model and these metrics agree with the equivalent joins for this example; it is not a performance benchmark or a general proof about other schemas.

5. Clean Up

The semantic view is a schema object, and the transient tables remain until dropped. Run the cleanup after the tutorial, including if you stop before the final comparison:

DROP SEMANTIC VIEW IF EXISTS BLOG_SV_TUTORIAL_20260928;
DROP TABLE IF EXISTS BLOG_SV_TUTORIAL_ORDERS_20260928;
DROP TABLE IF EXISTS BLOG_SV_TUTORIAL_PRODUCTS_20260928;
DROP TABLE IF EXISTS BLOG_SV_TUTORIAL_CUSTOMERS_20260928;

When This Pattern Helps

The point is not that semantic views replace SQL. The direct query is perfectly reasonable for a single report. A semantic view is more useful when multiple consumers need the same relationships, dimensions, and definitions of measures. It gives those consumers a shared model and lets each query select the slice it needs.

References

• CREATE SEMANTIC VIEW
• Querying with SEMANTIC_VIEW
• How Snowflake validates semantic views