Overview

What You'll Learn

What You'll Build

What You'll Need

Understand Semantic Views

What is a Semantic View?

Why Semantic Views Matter

Basic Elements of a Semantic View Definition

Setup Environment

Download the Notebook

Setup your Database and Schema

-- Create a new test database named SAMPLE_DATA
CREATE DATABASE SAMPLE_DATA;

-- Use the newly created database
USE DATABASE SAMPLE_DATA;

-- Create a new schema named TPCDS_SF10TCL within SAMPLE_DATA
CREATE SCHEMA TPCDS_SF10TCL;

-- Set the context to use the new schema
USE SCHEMA TPCDS_SF10TCL;

Create Views from Sample Data

-- Create or replace views for the tables from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL

CREATE OR REPLACE VIEW CUSTOMER AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.CUSTOMER;

CREATE OR REPLACE VIEW CUSTOMER_DEMOGRAPHICS AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.CUSTOMER_DEMOGRAPHICS;

CREATE OR REPLACE VIEW DATE_DIM AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.DATE_DIM;

CREATE OR REPLACE VIEW ITEM AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.ITEM;

CREATE OR REPLACE VIEW STORE AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.STORE;

CREATE OR REPLACE VIEW STORE_SALES AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.STORE_SALES;

Verify your Environment Setup

-- Select the warehouse, database, and schema
USE WAREHOUSE COMPUTE_WH;
USE DATABASE SAMPLE_DATA;
USE SCHEMA TPCDS_SF10TCL;

-- Show all views in the current schema to verify creation
SHOW VIEWS;

aside positive IMPORTANT:

• Ensure you have the ACCOUNTADMIN role to create semantic views
• Verify that all views are created successfully before proceeding to the next step

Create the Semantic View

Define the Semantic View

-- Switch to ACCOUNTADMIN role to create the semantic view
USE ROLE ACCOUNTADMIN;

-- Create or replace the semantic view named TPCDS_SEMANTIC_VIEW_SM
CREATE OR REPLACE SEMANTIC VIEW TPCDS_SEMANTIC_VIEW_SM
 tables (
  CUSTOMER primary key (C_CUSTOMER_SK),
  DATE as DATE_DIM primary key (D_DATE_SK),
  DEMO as CUSTOMER_DEMOGRAPHICS primary key (CD_DEMO_SK),
  ITEM primary key (I_ITEM_SK),
  STORE primary key (S_STORE_SK),
  STORESALES as STORE_SALES
        primary key (SS_SOLD_DATE_SK,SS_CDEMO_SK,SS_ITEM_SK,SS_STORE_SK,SS_CUSTOMER_SK)
 )
 relationships (
  SALESTOCUSTOMER as STORESALES(SS_CUSTOMER_SK) references CUSTOMER(C_CUSTOMER_SK),
  SALESTODATE as STORESALES(SS_SOLD_DATE_SK) references DATE(D_DATE_SK),
  SALESTODEMO as STORESALES(SS_CDEMO_SK) references DEMO(CD_DEMO_SK),
  SALESTOITEM as STORESALES(SS_ITEM_SK) references ITEM(I_ITEM_SK),
  SALETOSTORE as STORESALES(SS_STORE_SK) references STORE(S_STORE_SK)
 )
 facts (
  ITEM.COST as i_wholesale_cost,
  ITEM.PRICE as i_current_price,
  STORE.TAX_RATE as S_TAX_PRECENTAGE,
        STORESALES.SALES_QUANTITY as SS_QUANTITY
 )
 dimensions (
  CUSTOMER.BIRTHYEAR as C_BIRTH_YEAR,
  CUSTOMER.COUNTRY as C_BIRTH_COUNTRY,
  CUSTOMER.C_CUSTOMER_SK as c_customer_sk,
  DATE.DATE as D_DATE,
  DATE.D_DATE_SK as d_date_sk,
  DATE.MONTH as D_MOY,
  DATE.WEEK as D_WEEK_SEQ,
  DATE.YEAR as D_YEAR,
  DEMO.CD_DEMO_SK as cd_demo_sk,
  DEMO.CREDIT_RATING as CD_CREDIT_RATING,
  DEMO.MARITAL_STATUS as CD_MARITAL_STATUS,
  ITEM.BRAND as I_BRAND,
  ITEM.CATEGORY as I_CATEGORY,
  ITEM.CLASS as I_CLASS,
  ITEM.I_ITEM_SK as i_item_sk,
  STORE.MARKET as S_MARKET_ID,
  STORE.SQUAREFOOTAGE as S_FLOOR_SPACE,
  STORE.STATE as S_STATE,
  STORE.STORECOUNTRY as S_COUNTRY,
  STORE.S_STORE_SK as s_store_sk,
  STORESALES.SS_CDEMO_SK as ss_cdemo_sk,
  STORESALES.SS_CUSTOMER_SK as ss_customer_sk,
  STORESALES.SS_ITEM_SK as ss_item_sk,
  STORESALES.SS_SOLD_DATE_SK as ss_sold_date_sk,
  STORESALES.SS_STORE_SK as ss_store_sk
 )
 metrics (
  STORESALES.TOTALCOST as SUM(item.cost),
  STORESALES.TOTALSALESPRICE as SUM(SS_SALES_PRICE),
  STORESALES.TOTALSALESQUANTITY as SUM(SS_QUANTITY)
            WITH SYNONYMS = ( 'total sales quantity', 'total sales amount')
 )

;

Verify the Semantic View Creation

-- Lists semantic views in the database that is currently in use
SHOW SEMANTIC VIEWS;

Describe the Semantic View

-- Describes the semantic view named TPCDS_SEMANTIC_VIEW_SM, and as a special bonus uses our new flow operator to filter and project only the metric and dimension names

DESC SEMANTIC VIEW TPCDS_SEMANTIC_VIEW_SM
    ->> SELECT "object_kind","property_value" as "parent_object","object_name" FROM $1
        WHERE "object_kind" IN ('METRIC','DIMENSION') AND "property" IN ('TABLE')
;

"Talk To" the Semantic View with Cortex Analyst

Natural Language Querying

Access Cortex Analyst

SELECT 'https://app.snowflake.com/' || CURRENT_ORGANIZATION_NAME() || '/' || CURRENT_ACCOUNT_NAME() || '/#/studio/analyst/databases/SAMPLE_DATA/schemas/TPCDS_SF10TCL/semanticView/TPCDS_SEMANTIC_VIEW_SM/edit' AS RESULT;

import streamlit as st

link = sql_step7.to_pandas()['RESULT'].iloc[0]

st.link_button("Go to Cortex Analyst", link)

Example Natural Language Query

Query Semantic Views

Basic Query Structure

SELECT * FROM SEMANTIC_VIEW (view_name
    METRICS metric1, metric2, ...
    DIMENSIONS dimension1, dimension2, ...
);

Add Filters and Sorting

Example Query: Top Selling Brands

-- Query the semantic view to find top selling brands
SELECT * FROM SEMANTIC_VIEW
(
 TPCDS_SEMANTIC_VIEW_SM
    DIMENSIONS
            Item.Brand,
            Item.Category,
            Date.Year,
            Date.Month,
            Store.State
    METRICS
        StoreSales.TotalSalesQuantity
)
WHERE Year = '2002' AND Month = '12' AND State ='TX' AND Category = 'Books'
ORDER BY TotalSalesQuantity DESC LIMIT 10;

Build an Interactive Data App

Query data

-- Query the semantic view for month 12
SELECT * FROM SEMANTIC_VIEW
( 
 TPCDS_SEMANTIC_VIEW_SM
    DIMENSIONS 
            Item.Brand,
            Item.Category,            
            Date.Year,
            Date.Month,
            Store.State
    METRICS 
        StoreSales.TotalSalesQuantity
    WHERE
        Date.Year = '2002' AND Date.Month = '12' AND Item.Category = 'Books'
) 
ORDER BY TotalSalesQuantity DESC;

cell1.to_pandas()

App 1: Interactive data visualization app

import streamlit as st
import pandas as pd

st.title("📊 Sales Data Interactive Visualization")

# Create selectbox for grouping option
group_by = st.selectbox(
    "Select grouping option:",
    options=['BRAND', 'STATE'],
    index=0
)

# Group the data based on selection
if group_by == 'BRAND':
    grouped_data = df.groupby('BRAND')['TOTALSALESQUANTITY'].sum().reset_index()
    grouped_data = grouped_data.set_index('BRAND')
    chart_title = "Total Sales Quantity by Brand"
else:  # group_by == 'STATE'
    grouped_data = df.groupby('STATE')['TOTALSALESQUANTITY'].sum().reset_index()
    grouped_data = grouped_data.set_index('STATE')
    chart_title = "Total Sales Quantity by State"

# Display the chart
st.subheader(chart_title)
st.bar_chart(grouped_data['TOTALSALESQUANTITY'])

# Optional: Display the data table
if st.checkbox("Show data table"):
    st.subheader("Grouped Data")
    st.dataframe(grouped_data)

App 2: Simple interactive dashboard

import streamlit as st
import pandas as pd

st.title("📊 Sales Data Dashboard")

# Create selectbox for grouping option
group_by = st.selectbox(
    "Select grouping option:",
    options=['BRAND', 'STATE'],
    index=0
)

# Group the data based on selection
if group_by == 'BRAND':
    grouped_data = df.groupby('BRAND')['TOTALSALESQUANTITY'].sum().reset_index()
    grouped_data = grouped_data.set_index('BRAND')
    chart_title = "Total Sales Quantity by Brand"
else:  # group_by == 'STATE'
    grouped_data = df.groupby('STATE')['TOTALSALESQUANTITY'].sum().reset_index()
    grouped_data = grouped_data.set_index('STATE')
    chart_title = "Total Sales Quantity by State"

# Calculate KPIs based on current grouping
total_sales = df['TOTALSALESQUANTITY'].sum()
avg_sales = df['TOTALSALESQUANTITY'].mean()
top_performer = grouped_data['TOTALSALESQUANTITY'].max()
top_performer_name = grouped_data['TOTALSALESQUANTITY'].idxmax()

# Display KPI metrics in 3 columns
col1, col2, col3 = st.columns(3)

with col1:
    st.metric(
        label="Total Sales Quantity", 
        value=f"{total_sales:,.0f}",
        delta=None
    )

with col2:
    if group_by == 'BRAND':
        st.metric(
            label="Average Sales per Brand", 
            value=f"{avg_sales:,.0f}",
            delta=f"{((avg_sales/total_sales)*100):.3f}% of total"
        )
    else:
        st.metric(
            label="Average Sales per State", 
            value=f"{avg_sales:,.0f}",
            delta=f"{len(df['STATE'].unique())} state(s)"
        )

with col3:
    st.metric(
        label=f"Top {group_by.title()}", 
        value=f"{top_performer:,.0f}",
        delta=f"{top_performer_name}"
    )

# Display the chart
st.subheader(chart_title)
st.bar_chart(grouped_data['TOTALSALESQUANTITY'])

# Optional: Display the data table
if st.checkbox("Show data table"):
    st.subheader("Grouped Data")
    st.dataframe(grouped_data)

Conclusion And Resources

What You Learned

Related Resources