setup.sql

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE SNOWFLAKE_LEARNING_WH;
ALTER ACCOUNT SET CORTEX_ENABLED_CROSS_REGION = 'ANY_REGION';
ALTER ACCOUNT SET CORTEX_MODELS_ALLOWLIST = 'All';
CREATE DATABASE IF NOT EXISTS SEMANTIC_DATABASE;
USE DATABASE SEMANTIC_DATABASE;
CREATE SCHEMA IF NOT EXISTS DASH_SCHEMA;
CREATE SCHEMA IF NOT EXISTS TPCDS_SF10TCL;
CREATE STAGE SEMANTIC_DATABASE.TPCDS_SF10TCL.SEMANTIC_MODEL_TPCDS;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE SNOWFLAKE_LEARNING_ROLE;
GRANT USAGE ON DATABASE SEMANTIC_DATABASE TO ROLE SNOWFLAKE_LEARNING_ROLE;
GRANT USAGE ON SCHEMA SEMANTIC_DATABASE.TPCDS_SF10TCL TO ROLE SNOWFLAKE_LEARNING_ROLE;
GRANT SELECT ON VIEW SEMANTIC_DATABASE.TPCDS_SF10TCL.TPCDS_SEMANTIC_VIEW_SM TO ROLE SNOWFLAKE_LEARNING_ROLE;

CREATE DATABASE IF NOT EXISTS SNOWFLAKE_INTELLIGENCE;
GRANT USAGE ON DATABASE SNOWFLAKE_INTELLIGENCE TO ROLE SNOWFLAKE_LEARNING_ROLE;
CREATE SCHEMA IF NOT EXISTS SNOWFLAKE_INTELLIGENCE.AGENTS;
GRANT USAGE ON SCHEMA SNOWFLAKE_INTELLIGENCE.AGENTS TO ROLE SNOWFLAKE_LEARNING_ROLE;
GRANT CREATE AGENT ON SCHEMA SNOWFLAKE_INTELLIGENCE.AGENTS TO ROLE SNOWFLAKE_LEARNING_ROLE;
GRANT USAGE ON SCHEMA SNOWFLAKE_LEARNING_DB.DASH_SCHEMA TO ROLE SNOWFLAKE_LEARNING_ROLE;
GRANT USAGE ON CORTEX SEARCH SERVICE SEMANTIC_DATABASE.DASH_SCHEMA.VEHICLES_INFO to role SNOWFLAKE_LEARNING_ROLE;
GRANT MODIFY PROGRAMMATIC AUTHENTICATION METHODS ON USER BHANUJ TO ROLE SNOWFLAKE_LEARNING_ROLE;
USE SCHEMA TPCDS_SF10TCL;

-- Cortex Semantic View
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;

-- 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)
          WITH SYNONYMS = ('customer', 'customers', 'customer master')
          COMMENT = 'Customer master data including demographics and location',
    
        DATE as DATE_DIM primary key (D_DATE_SK)
          WITH SYNONYMS = ('date', 'calendar date', 'order date', 'sale date')
          COMMENT = 'Calendar date dimension for all sales',
    
        DEMO as CUSTOMER_DEMOGRAPHICS primary key (CD_DEMO_SK)
          WITH SYNONYMS = ('demographics', 'customer demographics')
          COMMENT = 'Customer demographics such as marital status and credit rating',
    
        ITEM primary key (I_ITEM_SK)
          WITH SYNONYMS = ('item', 'product', 'sku', 'catalog item')
          COMMENT = 'Product details including brand, category, and pricing',
    
        STORE primary key (S_STORE_SK)
          WITH SYNONYMS = ('store', 'retail store', 'shop', 'location')
          COMMENT = 'Store information including market, floor space, and state',
    
        STORESALES as STORE_SALES
          primary key (SS_SOLD_DATE_SK, SS_CDEMO_SK, SS_ITEM_SK, SS_STORE_SK, SS_CUSTOMER_SK)
          WITH SYNONYMS = ('store sales', 'sales', 'transactions', 'retail transactions')
          COMMENT = 'Sales fact table capturing items sold by date, store, and customer'
    )
	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')
	)
;

-- Cortex Search
create or replace file format csvformat  
  skip_header = 1  
  field_optionally_enclosed_by = '"'  
  type = 'CSV';  
  
create or replace stage support_tickets_data_stage  
  file_format = csvformat  
  url = 's3://sfquickstarts/sfguide_integrate_snowflake_cortex_agents_with_slack/';  
  
create or replace table SUPPORT_TICKETS (  
  ticket_id VARCHAR(60),  
  customer_name VARCHAR(60),  
  customer_email VARCHAR(60),  
  service_type VARCHAR(60),  
  request VARCHAR,  
  contact_preference VARCHAR(60)  
);  
  
copy into SUPPORT_TICKETS  
  from @support_tickets_data_stage;

-- Run the following statement to create a Snowflake managed internal stage to store the semantic model specification file.
create or replace stage DASH_SEMANTIC_MODELS encryption = (TYPE = 'SNOWFLAKE_SSE') directory = ( ENABLE = true );

-- Run the following statement to create a Snowflake managed internal stage to store the PDF documents.
create or replace stage DASH_PDFS encryption = (TYPE = 'SNOWFLAKE_SSE') directory = ( ENABLE = true );