DEV Community: Fabian Stadler

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

Fabian Stadler — Mon, 28 Sep 2026 11:41:46 +0000

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

Snowflake has now a PERIOD Data Type

Fabian Stadler — Mon, 28 Sep 2026 10:11:01 +0000

Snowflake recently made the PERIOD generally available. I saw it in the release notes and wondered what it would change in a model that already has valid_from and valid_to. Two date columns are not hard to understand. The part that tends to cause trouble is agreeing on what happens at the edge.

Therefore, I tried a small subscription example to make that edge visible. It has an old plan, a renewal that overlaps it, and a replacement that starts on the old plan's end date. I wanted to check two things: would PERIOD call the last pair adjacent rather than overlapping, and would its result match the scalar-column rule I would normally write?

Both representations are kept in the same temporary table: the two DATE columns and a PERIOD(DATE) built from them. That is a more useful comparison than testing the new type in isolation. For half-open ranges, the scalar overlap rule is a.start_date < b.end_date AND b.start_date < a.end_date. Two ranges meet when one end equals the other's start. PERIOD has named predicates for those same questions.

The dates are deliberately simple. The legacy plan ends on April 1, and the next plan starts on April 1. The renewal begins in the middle of March and runs into June. The query compares the two rules pair by pair and asks both representations for the shared window.

CREATE TEMPORARY TABLE plan_windows (
    customer_id VARCHAR,
    plan_name VARCHAR,
    start_date DATE,
    end_date DATE,
    valid_for PERIOD(DATE)
);

INSERT INTO plan_windows VALUES
    ('C-17', 'legacy', DATE '2026-01-01', DATE '2026-04-01',
        PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-04-01')),
    ('C-17', 'renewal', DATE '2026-03-15', DATE '2026-06-01',
        PERIOD_CONSTRUCT(DATE '2026-03-15', DATE '2026-06-01')),
    ('C-17', 'next', DATE '2026-04-01', DATE '2026-07-01',
        PERIOD_CONSTRUCT(DATE '2026-04-01', DATE '2026-07-01'));

SELECT
    a.plan_name || '/' || b.plan_name AS plan_pair,
    PERIOD_OVERLAPS(a.valid_for, b.valid_for) AS period_overlaps,
    (a.start_date < b.end_date AND b.start_date < a.end_date) AS scalar_overlaps,
    PERIOD_MEETS(a.valid_for, b.valid_for) AS period_meets,
    (a.end_date = b.start_date OR b.end_date = a.start_date) AS scalar_meets,
    PERIOD_INTERSECT(a.valid_for, b.valid_for) AS period_shared_window,
    CASE
        WHEN a.start_date < b.end_date AND b.start_date < a.end_date
            THEN PERIOD_CONSTRUCT(
                GREATEST(a.start_date, b.start_date),
                LEAST(a.end_date, b.end_date)
            )
    END AS scalar_shared_window
FROM plan_windows AS a
JOIN plan_windows AS b
    ON a.customer_id = b.customer_id
 AND a.plan_name < b.plan_name
ORDER BY plan_pair;

PLAN_PAIR       PERIOD_OVERLAPS  SCALAR_OVERLAPS  PERIOD_MEETS  SCALAR_MEETS  PERIOD_SHARED_WINDOW    SCALAR_SHARED_WINDOW
legacy/next     false            false            true          true          NULL                    NULL
legacy/renewal  true             true             false         false         [2026-03-15, 2026-04-01) [2026-03-15, 2026-04-01)
next/renewal    true             true             false         false         [2026-04-01, 2026-06-01) [2026-04-01, 2026-06-01)

For these three pairs, the old half-open comparisons and the PERIOD functions agreed. legacy/next meet on April 1 but do not overlap. The renewal overlaps each of them, and both approaches return the same intersection. That's the result I hoped to see; the useful part was checking the awkward pair instead of only inserting and reading a range.

This does not show that PERIOD is faster, nor does it prove that every temporal model should use it. It does show that, for this DATE example, the built-in operations express the same boundary rule as the scalar predicates. The intersection is a little clearer too: PERIOD returns the range directly, while the scalar version needs GREATEST and LEAST, plus a guard so the adjacent pair does not become an invalid empty range.

That is the distinction I was looking for. The old solution works. PERIOD makes the range and the operations on it harder to misread. Whether that reduces mistakes in a real project depends on how often those predicates are repeated and whether the team currently applies them consistently.

A couple of limits that matter

This test used finite DATE ranges in SQL. PERIOD also supports TIME and timestamp element types, but the bounds have to use the same type. Open-ended ranges are not supported, so a nullable valid_to cannot simply become an unbounded PERIOD. Snowflake's docs also call out limited or deferred Snowpark support and no managed Iceberg PERIOD columns. Those would matter in a system that depends on those paths; they did not affect this small query.

The test also does not enforce that a customer's plans never overlap. I inserted an overlap on purpose. PERIOD can describe and compare ranges, but a rule such as "at most one active plan per customer" still needs to be enforced separately.

Takeaway

For this scenario, I would use PERIOD if these validity windows were queried in several places and the same endpoint logic kept being rewritten. PERIOD makes the existing answer more explicit and gives a direct intersection result. If the model is small and its two-column convention is already clear, I would not migrate it just for the new type.

That is a small improvement, but a useful one. I would test it on a representative model next, especially its null and boundary cases, before making it a shared schema decision.