Overview

What You'll Learn

What You'll Build

Prerequisites

Access the Notebook

Architecture

┌──────────────────────┐                 ┌────────────────────────────┐
│  Campaign Data       │                 │ Existing Tableau Workbook  │
│  (Snowflake Tables)  │                 │ (.twb file)                │
└──────────┬───────────┘                 └─────────────┬──────────────┘
           │                                           │
           ▼                                           ▼
┌──────────────────────────────────────────────────────────────────────┐
│ Semantic View Autopilot "Fast-Gen"                                   │
│ (Generate Semantic View from existing analytics)                     │
└──────────┬───────────────────────────────────────────────────────────┘
           │
           ▼
┌──────────────────────┐                 ┌────────────────────────────┐
│ Snowflake Semantic   │                 │ Query History              │
│ View (Metrics + Dims)│                 │ (Real Usage Patterns)      │
└──────────┬───────────┘                 └─────────────┬──────────────┘
           │                                           │
           ▼                                           ▼
┌──────────────────────────────────────────────────────────────────────┐
│ Semantic View Autopilot                                              │
│ (Suggest verified queries using usage patterns)                      │
└──────────┬───────────────────────────────────────────────────────────┘
           │
           ▼
┌──────────────────────────────────────────────────────────────────────┐
│                       Downstream Consumers                           │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐ │
│  │ Snowflake   │  │ Cortex      │  │ Tableau     │  │ Any SQL     │ │
│  │ Intelligence│  │ Analyst API │  │ (via .tds)  │  │ Client      │ │
│  └─────────────┘  └─────────────┘  └─────────────┘  └─────────────┘ │
└──────────────────────────────────────────────────────────────────────┘

Setup Environment

Create Database and Schema

-- Switch to accountadmin role to create db, schema, and load data
USE ROLE ACCOUNTADMIN;

-- Create database and schema
CREATE OR REPLACE DATABASE SVA_VHOL_DB;
USE DATABASE SVA_VHOL_DB;

CREATE SCHEMA IF NOT EXISTS SVA_VHOL_SCHEMA;
USE SCHEMA SVA_VHOL_SCHEMA;

-- Optional: allow anyone to see the agents in this schema
GRANT USAGE ON DATABASE SVA_VHOL_DB TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA SVA_VHOL_DB.SVA_VHOL_SCHEMA TO ROLE PUBLIC;

Create File Format

-- Create file format for CSV files
CREATE OR REPLACE FILE FORMAT CSV_FORMAT
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    RECORD_DELIMITER = '\n'
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    TRIM_SPACE = TRUE
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
    ESCAPE = 'NONE'
    ESCAPE_UNENCLOSED_FIELD = '\134'
    DATE_FORMAT = 'YYYY-MM-DD'
    TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
    NULL_IF = ('NULL', 'null', '', 'N/A', 'n/a');

Load Marketing Data

Set Up Git Integration

-- Create API Integration for GitHub (public repository access)
CREATE OR REPLACE API INTEGRATION git_api_integration
    API_PROVIDER = git_https_api
    API_ALLOWED_PREFIXES = ('https://github.com/NickAkincilar/')
    ENABLED = TRUE;
    
-- Create Git repository integration for the public demo repository
CREATE OR REPLACE GIT REPOSITORY SVA_VHOL_REPO
    API_INTEGRATION = git_api_integration
    ORIGIN = 'https://github.com/NickAkincilar/Snowflake_AI_DEMO.git';

-- Create internal stage for copied data files
CREATE OR REPLACE STAGE INTERNAL_DATA_STAGE
    FILE_FORMAT = CSV_FORMAT
    COMMENT = 'Internal stage for copied demo data files'
    DIRECTORY = ( ENABLE = TRUE)
    ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

Copy Data Files

-- Fetch the latest from the repository
ALTER GIT REPOSITORY SVA_VHOL_REPO FETCH;

-- Copy data files from Git repository to internal stage
COPY FILES 
    INTO @INTERNAL_DATA_STAGE
    FROM @SVA_VHOL_REPO/branches/main/data/;

-- Copy unstructured docs (including Tableau workbook)
COPY FILES 
    INTO @INTERNAL_DATA_STAGE/unstructured_docs/
    FROM @SVA_VHOL_REPO/branches/main/unstructured_docs/;

-- Refresh directory table
ALTER STAGE INTERNAL_DATA_STAGE REFRESH;

Create Dimension Tables

-- Create CAMPAIGN_DIM table
CREATE OR REPLACE TABLE CAMPAIGN_DIM (
    campaign_key INT,
    campaign_id VARCHAR(50),
    campaign_name VARCHAR(200),
    objective VARCHAR(100),
    start_date DATE,
    end_date DATE,
    budget DECIMAL(15,2),
    status VARCHAR(50)
);

COPY INTO CAMPAIGN_DIM
FROM @INTERNAL_DATA_STAGE/campaign_dim.csv
FILE_FORMAT = CSV_FORMAT
ON_ERROR = 'CONTINUE';

-- Create CHANNEL_DIM table
CREATE OR REPLACE TABLE CHANNEL_DIM (
    channel_key INT,
    channel_id VARCHAR(50),
    channel_name VARCHAR(100),
    channel_type VARCHAR(50),
    platform VARCHAR(100)
);

COPY INTO CHANNEL_DIM
FROM @INTERNAL_DATA_STAGE/channel_dim.csv
FILE_FORMAT = CSV_FORMAT
ON_ERROR = 'CONTINUE';

-- Create PRODUCT_DIM table
CREATE OR REPLACE TABLE PRODUCT_DIM (
    product_key INT,
    product_id VARCHAR(50),
    product_name VARCHAR(200),
    category VARCHAR(100),
    subcategory VARCHAR(100),
    price DECIMAL(10,2)
);

COPY INTO PRODUCT_DIM
FROM @INTERNAL_DATA_STAGE/product_dim.csv
FILE_FORMAT = CSV_FORMAT
ON_ERROR = 'CONTINUE';

Create Fact Table

-- Create MARKETING_CAMPAIGN_FACT table
CREATE OR REPLACE TABLE MARKETING_CAMPAIGN_FACT (
    fact_key INT,
    date DATE,
    campaign_key INT,
    channel_key INT,
    product_key INT,
    impressions INT,
    clicks INT,
    spend DECIMAL(15,2),
    conversions INT,
    revenue DECIMAL(15,2),
    leads_generated INT
);

COPY INTO MARKETING_CAMPAIGN_FACT
FROM @INTERNAL_DATA_STAGE/marketing_campaign_fact.csv
FILE_FORMAT = CSV_FORMAT
ON_ERROR = 'CONTINUE';

Verify Data Load

-- List the tables
SHOW TABLES;

Run Sample Queries

Marketing Performance by Month

SELECT
    DATE_TRUNC('month', mcf.date) AS month,
    SUM(mcf.spend) AS total_spend,
    SUM(mcf.impressions) AS total_impressions,
    SUM(mcf.leads_generated) AS total_leads,
    SUM(mcf.spend) / NULLIF(SUM(mcf.leads_generated), 0) AS cost_per_lead,
    SUM(mcf.leads_generated) / NULLIF(SUM(mcf.impressions), 0) AS lead_conversion_rate
FROM MARKETING_CAMPAIGN_FACT mcf
GROUP BY 1
ORDER BY 1;

Channel Efficiency Analysis

SELECT
    cd.channel_name,
    SUM(mcf.spend) AS total_spend,
    SUM(mcf.leads_generated) AS total_leads,
    SUM(mcf.spend) / NULLIF(SUM(mcf.leads_generated), 0) AS cost_per_lead
FROM MARKETING_CAMPAIGN_FACT mcf
JOIN CHANNEL_DIM cd ON mcf.channel_key = cd.channel_key
GROUP BY 1
HAVING SUM(mcf.leads_generated) > 0
ORDER BY cost_per_lead ASC, total_leads DESC;

Top Campaigns by Leads

SELECT
    c.campaign_name,
    c.objective,
    SUM(mcf.leads_generated) AS total_leads,
    SUM(mcf.spend) AS total_spend,
    SUM(mcf.impressions) AS total_impressions,
    SUM(mcf.spend) / NULLIF(SUM(mcf.leads_generated), 0) AS cpl
FROM MARKETING_CAMPAIGN_FACT mcf
JOIN CAMPAIGN_DIM c ON mcf.campaign_key = c.campaign_key
GROUP BY 1, 2
ORDER BY total_leads DESC
LIMIT 10;

Create Semantic View

Use the Semantic View Wizard

Test the Semantic View

Query with Standard SQL

Understanding Standard SQL for Semantic Views

Example Query

-- Switch to the correct context
USE ROLE ACCOUNTADMIN;
USE DATABASE SVA_VHOL_DB;
USE SCHEMA SVA_VHOL_SCHEMA;

-- Standard SQL query against the semantic view
SELECT
   -- dimensions and facts can be selected directly
   product_name,
   -- metrics must be aggregated using AGG, MIN, MAX, or ANYVALUE
   AGG(cost_per_lead) as total_cost_per_lead
FROM
   -- directly reference the semantic view name
   SVA_MARKETING_SV
-- you must include a GROUP BY if your SELECT statement includes any metrics
GROUP BY ALL
ORDER BY total_cost_per_lead DESC
LIMIT 10;

Generate Tableau TDS File

About the TDS Export Function

SELECT SYSTEM$EXPORT_TDS_FROM_SEMANTIC_VIEW('SVA_VHOL_DB.SVA_VHOL_SCHEMA.SVA_MARKETING_SV');

Using the TDS Generator App

import streamlit as st
from snowflake.snowpark.context import get_active_session

def generate_and_download_tds():
    """Clean interface for TDS generation and download"""
    
    st.title("TDS Generator")
    st.write("Generate Tableau Data Source files from Snowflake Semantic Views")
    
    # Input section
    semantic_view = st.text_input(
        "Semantic View Name",
        value="SVA_VHOL_DB.SVA_VHOL_SCHEMA.SVA_MARKETING_SV",
        help="Enter the fully qualified semantic view name"
    )
    
    if st.button("Generate TDS", type="primary"):
        if not semantic_view:
            st.error("Please enter a semantic view name")
            return
            
        try:
            # Get Snowflake session
            session = get_active_session()
            
            # Call the system function
            with st.spinner("Generating TDS file..."):
                result = session.sql(
                    f"SELECT SYSTEM$EXPORT_TDS_FROM_SEMANTIC_VIEW('{semantic_view}');"
                ).collect()
                tds_content = result[0][0]
            
            # Check for errors
            if tds_content.startswith("<!-- Error"):
                st.error(f"Generation failed: {tds_content}")
                return
            
            # Success - show download
            st.success("TDS file generated successfully")
            
            # Create filename
            view_name = semantic_view.split('.')[-1] if '.' in semantic_view else semantic_view
            filename = f"{view_name}_Semantic_View.tds"
            
            # Download button
            st.download_button(
                label="Download TDS",
                data=tds_content,
                file_name=filename,
                mime="application/xml"
            )
            
        except Exception as e:
            st.error(f"Error: {str(e)}")

# Run the app
generate_and_download_tds()

Connect Tableau

Open the TDS File

Authenticate to Snowflake

Explore Your Data

Conclusion And Resources

What You Learned

Related Resources