Configuring Snowflake via Snowflake CLI

Set up the environment variables. Copy the .env.template to .env and configure:

# Core Snowflake Configuration 

SA_USER="CORTEX_MCP_SERVICE" 
SNOWFLAKE_MCP_DEMO_ROLE="MCP_DEMO_ROLE" 
SNOWFLAKE_MCP_DEMO_DATABASE="MCP_DEMO" 
SNOWFLAKE_ACCOUNT="your_account_identifier" 
SNOWFLAKE_MCP_DEMO_WAREHOUSE="DEMO_WH" 

# Cortex Configuration 

SEMANTIC_MODEL_FILE="@MCP_DEMO.DATA.MY_MODELS/support_tickets.yaml" 
CORTEX_SEARCH_SERVICE="MCP_DEMO.DATA.INVOICE_SEARCH_SERVICE" 
CORTEX_AGENT_LLM_MODEL="claude-3-5-sonnet"

Run the setup script to create the foundational database objects. Execute scripts/setup.sql with your configured environment variables.

This creates:

• MCP_DEMO database
• DATA, MY_MODELS, NETWORKS, POLICIES schemas
• SUPPORT_TICKETS table with sample data
• Document processing infrastructure
• Cortex Search Service

USE ROLE ACCOUNTADMIN; 

-- Create the main demo role 

CREATE ROLE IF NOT EXISTS MCP_DEMO_ROLE
      COMMENT = 'Role for MCP server demo operations'; 

-- Grant basic compute permissions 

GRANT EXECUTE TASK ON ACCOUNT TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON WAREHOUSE DEMO_WH TO ROLE MCP_DEMO_ROLE;

USE ROLE ACCOUNTADMIN; 

-- Create service user for programmatic access 

CREATE USER IF NOT EXISTS CORTEX_MCP_SERVICE
     TYPE = SERVICE
     COMMENT = 'Service User for Cortex Agents MCP Demo'
     DEFAULT_ROLE = MCP_DEMO_ROLE
     DEFAULT_WAREHOUSE = DEMO_WH; 

-- Grant role to service user 

GRANT ROLE MCP_DEMO_ROLE TO USER CORTEX_MCP_SERVICE; 
GRANT APPLY AUTHENTICATION POLICY ON USER CORTEX_MCP_SERVICE TO ROLE MCP_DEMO_ROLE;

USE ROLE ACCOUNTADMIN; 

-- Grant ownership of demo database to MCP role 

GRANT OWNERSHIP ON DATABASE MCP_DEMO TO ROLE MCP_DEMO_ROLE; 

-- Switch to MCP role for schema creation 

USE ROLE MCP_DEMO_ROLE; 
USE DATABASE MCP_DEMO; 

-- Ensure all required schemas exist 

CREATE SCHEMA IF NOT EXISTS DATA; 
CREATE SCHEMA IF NOT EXISTS MY_MODELS; 
CREATE SCHEMA IF NOT EXISTS NETWORKS; 
CREATE SCHEMA IF NOT EXISTS POLICIES;

USE ROLE ACCOUNTADMIN; 

-- Grant comprehensive access to all objects in MCP_DEMO database 

GRANT SELECT ON ALL TABLES IN DATABASE MCP_DEMO TO ROLE MCP_DEMO_ROLE; 
GRANT SELECT ON FUTURE TABLES IN DATABASE MCP_DEMO TO ROLE MCP_DEMO_ROLE; 
GRANT SELECT ON ALL VIEWS IN DATABASE MCP_DEMO TO ROLE MCP_DEMO_ROLE; 
GRANT SELECT ON FUTURE VIEWS IN DATABASE MCP_DEMO TO ROLE MCP_DEMO_ROLE; 

-- Grant stage permissions 

GRANT READ, WRITE ON ALL STAGES IN DATABASE MCP_DEMO TO ROLE MCP_DEMO_ROLE; 
GRANT READ, WRITE ON FUTURE STAGES IN DATABASE MCP_DEMO TO ROLE MCP_DEMO_ROLE;

USE ROLE ACCOUNTADMIN; 

-- Grant access to Snowflake Cortex functions 

GRANT USAGE ON DATABASE SNOWFLAKE TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON SCHEMA SNOWFLAKE.CORTEX TO ROLE MCP_DEMO_ROLE; 

-- Grant specific Cortex function permissions 

GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.COMPLETE TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.EMBED_TEXT_768 TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.SENTIMENT TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.SUMMARIZE TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.TRANSLATE TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.EXTRACT_ANSWER TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.PARSE_DOCUMENT TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.SPLIT_TEXT_RECURSIVE_CHARACTER TO ROLE MCP_DEMO_ROLE;

USE ROLE ACCOUNTADMIN; 

-- Grant access to Snowflake Intelligence database 

GRANT USAGE ON DATABASE SNOWFLAKE_INTELLIGENCE TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON SCHEMA SNOWFLAKE_INTELLIGENCE.AGENTS TO ROLE MCP_DEMO_ROLE; 

-- Grant ability to create and manage Cortex Agents 

GRANT CREATE CORTEX AGENT ON SCHEMA SNOWFLAKE_INTELLIGENCE.AGENTS TO ROLE MCP_DEMO_ROLE;

USE ROLE ACCOUNTADMIN; 

-- Create RETAIL_SALES database for semantic models 

CREATE DATABASE IF NOT EXISTS RETAIL_SALES
     COMMENT = 'Database for retail sales data and semantic models'; 

USE DATABASE RETAIL_SALES; 

-- Create schema 

CREATE SCHEMA IF NOT EXISTS RETAIL_SALES_DEMO
     COMMENT = 'Schema for retail sales demo data and models'; 

USE SCHEMA RETAIL_SALES_DEMO; 

-- Create stage for semantic models 

CREATE STAGE IF NOT EXISTS RETAIL_SALES_STAGE
     COMMENT = 'Stage for storing semantic model YAML files'; 

-- Create sample tables 

CREATE TABLE IF NOT EXISTS CUSTOMERS (
     customer_id INT PRIMARY KEY,
     customer_name VARCHAR(100),
     email VARCHAR(100),
     phone VARCHAR(20),
     address VARCHAR(200),
     city VARCHAR(50),
     state VARCHAR(50),
     country VARCHAR(50),
     created_date DATE 
); 
CREATE TABLE IF NOT EXISTS SUPPORT_TICKETS (
     ticket_id INT PRIMARY KEY,
     customer_id INT,
     service_type VARCHAR(50),
     issue_description TEXT,
     status VARCHAR(20),
     created_date TIMESTAMP,
     resolved_date TIMESTAMP,
     priority VARCHAR(20),
     assigned_to VARCHAR(100)
 );

-- Create semantic model view 

CREATE OR REPLACE VIEW CUSTOMER_SUPPORT_MODEL AS 

SELECT
     st.ticket_id,
     st.service_type,
     st.issue_description,
     st.status,
     st.priority,
     st.created_date,
     st.resolved_date,
     st.assigned_to,
     c.customer_name,
     c.email as customer_email,
     c.city as customer_city,
     c.state as customer_state,
     DATEDIFF(day, st.created_date, COALESCE(st.resolved_date, CURRENT_TIMESTAMP())) as resolution_time_days 

FROM SUPPORT_TICKETS st 

LEFT JOIN CUSTOMERS c ON st.customer_id = c.customer_id;

USE ROLE ACCOUNTADMIN; 

-- Grant comprehensive access to RETAIL_SALES database 

GRANT USAGE ON DATABASE RETAIL_SALES TO ROLE MCP_DEMO_ROLE; 
GRANT USAGE ON SCHEMA RETAIL_SALES.RETAIL_SALES_DEMO TO ROLE MCP_DEMO_ROLE; 

-- Grant permissions on all objects 

GRANT SELECT ON ALL TABLES IN SCHEMA RETAIL_SALES.RETAIL_SALES_DEMO TO ROLE MCP_DEMO_ROLE; 
GRANT SELECT ON FUTURE TABLES IN SCHEMA RETAIL_SALES.RETAIL_SALES_DEMO TO ROLE MCP_DEMO_ROLE; 
GRANT SELECT ON ALL VIEWS IN SCHEMA RETAIL_SALES.RETAIL_SALES_DEMO TO ROLE MCP_DEMO_ROLE; 
GRANT SELECT ON FUTURE VIEWS IN SCHEMA RETAIL_SALES.RETAIL_SALES_DEMO TO ROLE MCP_DEMO_ROLE; 

-- Grant stage permissions 

GRANT READ, WRITE ON STAGE RETAIL_SALES.RETAIL_SALES_DEMO.RETAIL_SALES_STAGE TO ROLE MCP_DEMO_ROLE;

USE ROLE MCP_DEMO_ROLE; 
USE DATABASE MCP_DEMO; 

-- Create network rule for local access (you may need to update the IP) 

CREATE OR REPLACE NETWORK RULE MCP_DEMO.networks.pat_local_access_rule
   mode = ingress
   type = ipv4
   value_list = ('YOUR_LOCAL_IP/32')  -- Replace with your actual IP
   comment = 'Allow local machine IPv4 access'; 

-- Create network policy 

USE ROLE ACCOUNTADMIN; 
CREATE OR REPLACE NETWORK POLICY LOCAL_PAT_NETWORK_POLICY
   allowed_network_rule_list = ('MCP_DEMO.networks.pat_local_access_rule')
   comment = 'Network policy for PAT access';

USE ROLE MCP_DEMO_ROLE; 

-- Create authentication policy for PAT 

CREATE OR REPLACE AUTHENTICATION POLICY MCP_DEMO.policies.mcp_auth_policy
   authentication_methods = ('PROGRAMMATIC_ACCESS_TOKEN')
   pat_policy = (
     default_expiry_in_days=15,
     max_expiry_in_days=90,
     network_policy_evaluation = ENFORCED_REQUIRED
   ); 

-- Apply policies to service user 

USE ROLE ACCOUNTADMIN; 
ALTER USER CORTEX_MCP_SERVICE SET
    NETWORK_POLICY = 'LOCAL_PAT_NETWORK_POLICY',
    AUTHENTICATION_POLICY = 'MCP_DEMO.policies.mcp_auth_policy';

USE ROLE ACCOUNTADMIN; 

-- Create PAT for service user 

ALTER USER CORTEX_MCP_SERVICE ADD PAT mcp_demo
    ROLE_RESTRICTION = MCP_DEMO_ROLE
    COMMENT = 'PAT for MCP server access';

The output will contain the token_secret - save this for your .env file.

Alternatively, you can use the provided script: ./scripts/pat.sh Run it after seting environment variables first, then run:

This script will:

• Create the service user;
• Set up network policies;
• Create authentication policies;
• Generate the PAT;
• Update your .env file with the token.

USE ROLE ACCOUNTADMIN; 
DESC USER CORTEX_MCP_SERVICE; 
SHOW USER PATS FOR USER CORTEX_MCP_SERVICE;

SHOW GRANTS TO ROLE MCP_DEMO_ROLE; 
SHOW GRANTS TO USER CORTEX_MCP_SERVICE;

USE ROLE MCP_DEMO_ROLE; 
USE DATABASE SNOWFLAKE_INTELLIGENCE; 
USE SCHEMA AGENTS; 
DESC CORTEX AGENT RETAIL_SALES;

USE ROLE MCP_DEMO_ROLE; 
USE DATABASE MCP_DEMO; 
SELECT COUNT(*) FROM DATA.SUPPORT_TICKETS; 

-- If RETAIL_SALES is set up: 

USE DATABASE RETAIL_SALES; 
SELECT COUNT(*) FROM RETAIL_SALES_DEMO.CUSTOMER_SUPPORT_MODEL;

# Test Cortex Search Service 

curl --location \   "https://${SNOWFLAKE_ACCOUNT}.snowflakecomputing.com/api/v2/databases/${SNOWFLAKE_MCP_DEMO_DATABASE}/schemas/data/cortex-search-services/invoice_search_service:query" \
   --header 'X-Snowflake-Authorization-Token-Type: PROGRAMMATIC_ACCESS_TOKEN' \
   --header 'Content-Type: application/json' \
   --header 'Accept: application/json' \
   --header "Authorization: Bearer ${SNOWFLAKE_PASSWORD}" \
   --data '{
     "query": "What kind of service does Gregory have?",
     "columns": ["CHUNK", "FILE_NAME"],
     "limit": 1
   }'

snow connection test -x \
   --user "${SA_USER}" \
   --role "${SNOWFLAKE_MCP_DEMO_ROLE}"

After completing this setup, the .env file should look like:

SA_USER="CORTEX_MCP_SERVICE" 
SNOWFLAKE_MCP_DEMO_ROLE="MCP_DEMO_ROLE" 
SNOWFLAKE_MCP_DEMO_DATABASE="MCP_DEMO" 
SNOWFLAKE_ACCOUNT="your_account_identifier" 
SNOWFLAKE_PASSWORD="generated_pat_token_here" 
SEMANTIC_MODEL_FILE="@MCP_DEMO.DATA.MY_MODELS/support_tickets.yaml" 
CORTEX_SEARCH_SERVICE="MCP_DEMO.DATA.INVOICE_SEARCH_SERVICE" 
SNOWFLAKE_MCP_DEMO_WAREHOUSE="DEMO_WH" 
CORTEX_AGENT_LLM_MODEL="claude-3-5-sonnet"