| name | sales-ops-cortex-agent-mcp-quick |
| description | Build a Sales Ops Cortex Agent with Semantic View, MCP Server, and Amazon Quick OAuth integration. Use when: user wants to create a sales ops agent, build a demo with MCP and Amazon Quick spaces, chat agents and flows, set up Cortex Analyst over sales data. Triggers: sales ops, sales operations, cortex agent quicksight, MCP quick connector, sales demo, revenue pipeline agent. |
Sales Ops: Cortex Agent + MCP + Amazon Quick
Builds a complete Sales Operations analytics pipeline: Semantic View, Cortex Agent, MCP Server, and OAuth integration for Amazon Quick MCP Connector.
When to Use
Use this skill when someone asks:
- "Build a sales ops agent with MCP and Amazon Quick"
- "Create a Cortex Agent for sales operations data"
- "Set up a sales analytics demo with QuickSight integration"
- "Expose a sales agent to Amazon QuickSight via MCP"
Prerequisites
- Snowflake account with ACCOUNTADMIN access
- 6 sales ops tables loaded in a schema (transactions, customers, products, reps, pipeline, marketing)
- A warehouse for query execution
- Amazon QuickSight Author subscription or higher
Workflow
Step 1: Gather Information
Ask user for the following:
To build your Sales Ops agent and QuickSight integration, provide:
1. DATABASE : Database containing your sales ops tables
2. SCHEMA : Schema containing your sales ops tables
3. WAREHOUSE : Warehouse for query execution
4. ACCOUNT : Snowflake account identifier (e.g., abc12345)
5. ROLE : Snowflake role
If user doesn't know their account identifier, suggest:
SELECT CURRENT_ACCOUNT();
Verify the 6 required tables exist:
SHOW TABLES IN SCHEMA <DATABASE>.<SCHEMA>;
Expected tables:
SALES_TRANSACTIONS_FACT_TABLECUSTOMER_DIMENSION_TABLEPRODUCT_CATALOG_TABLESALES_REP_PERFORMANCE_TABLESALES_PIPELINE_OPPORTUNITIESMARKETING_ATTRIBUTION_TABLE
Stop: Confirm all values and verify tables exist before proceeding.
Step 2: Create Semantic View
Execute:
CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
'<DATABASE>.<SCHEMA>',
$$
name: SALES_OPS_SEMANTIC_VIEW
description: >
Semantic view for Sales Operations analytics covering revenue, pipeline health,
rep performance, customer segmentation, product mix, and marketing attribution.
tables:
- name: TRANSACTIONS
description: Sales transactions fact table with closed deals, revenue, and attribution.
synonyms: [deals, sales, orders, closed deals]
base_table:
database: <DATABASE>
schema: <SCHEMA>
table: SALES_TRANSACTIONS_FACT_TABLE
primary_key:
columns: [TRANSACTION_ID]
dimensions:
- name: TRANSACTION_ID
synonyms: [deal id, order id]
description: Unique transaction identifier
expr: TRANSACTION_ID
data_type: TEXT
- name: REGION
synonyms: [geo, geography]
description: "Geographic region: North America, EMEA, APAC, LATAM"
expr: REGION
data_type: TEXT
sample_values: [North America, EMEA, APAC, LATAM]
- name: LEAD_SOURCE
synonyms: [source, acquisition channel]
description: "Lead source: Inbound, Outbound, Event, Partner"
expr: LEAD_SOURCE
data_type: TEXT
sample_values: [Inbound, Outbound, Event, Partner]
- name: CUSTOMER_ID
description: Foreign key to customers dimension
expr: CUSTOMER_ID
data_type: TEXT
- name: PRODUCT_SKU
description: Foreign key to products dimension
expr: PRODUCT_SKU
data_type: TEXT
- name: SALES_REP_ID
description: Foreign key to sales reps dimension
expr: SALES_REP_ID
data_type: TEXT
time_dimensions:
name: TRANSACTION_DATE
synonyms: [ , sale , deal ]
description: the transaction was recorded
expr: TRANSACTION_DATE
data_type:
name: FORECAST_CLOSE_DATE
synonyms: [expected , forecasted ]
description: Originally forecasted
expr: FORECAST_CLOSE_DATE
data_type:
name: ACTUAL_CLOSE_DATE
synonyms: [ ]
description: Actual the deal closed
expr: ACTUAL_CLOSE_DATE
data_type:
facts:
name: DEAL_SIZE_USD
synonyms: [deal , revenue, amount, ARR]
description: Deal size USD
expr: DEAL_SIZE_USD
data_type: NUMBER
name: QUANTITY
synonyms: [units, qty]
description: Quantity product units
expr: QUANTITY
data_type: NUMBER
name: DISCOUNT_PERCENTAGE
synonyms: [discount, discount pct]
description: Discount percentage applied
expr: DISCOUNT_PERCENTAGE
data_type: NUMBER
name: SALES_CYCLE_DAYS
synonyms: [ length, ]
description: Days opportunity creation
expr: SALES_CYCLE_DAYS
data_type: NUMBER
name: CLOSE_PROBABILITY_AT_START
synonyms: [ probability]
description: Win probability deal
expr: CLOSE_PROBABILITY_AT_START
data_type: NUMBER
metrics:
name: TOTAL_REVENUE
synonyms: [total sales, gross revenue]
description: Total revenue USD
expr: (DEAL_SIZE_USD)
name: TOTAL_DEALS
synonyms: [deal count, number deals]
description: Total number transactions
expr: (TRANSACTION_ID)
name: AVG_DEAL_SIZE
synonyms: [average deal, mean deal size]
description: Average deal size USD
expr: (DEAL_SIZE_USD)
name: AVG_DISCOUNT
synonyms: [average discount]
description: Average discount percentage
expr: (DISCOUNT_PERCENTAGE)
name: AVG_SALES_CYCLE
synonyms: [average , mean ]
description: Average days
expr: (SALES_CYCLE_DAYS)
name: CUSTOMERS
description: Customer dimension firmographics, LTV, churn risk, expansion potential.
synonyms: [accounts, clients, companies]
base_table:
database: DATABASE
schema: SCHEMA
: CUSTOMER_DIMENSION_TABLE
primary_key:
columns: [CUSTOMER_ID]
dimensions:
name: CUSTOMER_ID
synonyms: [account id, client id]
description: customer identifier
expr: CUSTOMER_ID
data_type: TEXT
name: COMPANY_NAME
synonyms: [account name, company, client name]
description: Customer company name
expr: COMPANY_NAME
data_type: TEXT
name: INDUSTRY
synonyms: [vertical, sector]
description: "Industry: Technology, Finance, Healthcare, Manufacturing, Retail"
expr: INDUSTRY
data_type: TEXT
sample_values: [Technology, Finance, Healthcare, Manufacturing, Retail]
name: COMPANY_SIZE
synonyms: [segment, account size]
description: "Company size: SMB, Mid-Market, Enterprise"
expr: COMPANY_SIZE
data_type: TEXT
sample_values: [SMB, MidMarket, Enterprise]
name: ANNUAL_REVENUE_BAND
synonyms: [revenue band]
description: Annual revenue band
expr: ANNUAL_REVENUE_BAND
data_type: TEXT
name: EXPANSION_POTENTIAL
synonyms: [growth potential, upsell potential]
description: "Expansion potential: High, Medium, Low"
expr: EXPANSION_POTENTIAL
data_type: TEXT
sample_values: [High, Medium, Low]
time_dimensions:
name: FIRST_PURCHASE_DATE
synonyms: [customer since, acquisition ]
description: purchase
expr: FIRST_PURCHASE_DATE
data_type:
facts:
name: CUSTOMER_LIFETIME_VALUE
synonyms: [CLV, LTV, lifetime ]
description: Customer lifetime USD
expr: CUSTOMER_LIFETIME_VALUE
data_type: NUMBER
name: CHURN_RISK_SCORE
synonyms: [churn risk, attrition risk]
description: Churn risk score ()
expr: CHURN_RISK_SCORE
data_type: NUMBER
name: TOTAL_CONTRACTS_COUNT
synonyms: [contract count]
description: Total contracts this customer
expr: TOTAL_CONTRACTS_COUNT
data_type: NUMBER
metrics:
name: AVG_LIFETIME_VALUE
synonyms: [average CLV, mean LTV]
description: Average customer lifetime
expr: (CUSTOMER_LIFETIME_VALUE)
name: AVG_CHURN_RISK
synonyms: [average churn risk]
description: Average churn risk score
expr: (CHURN_RISK_SCORE)
name: TOTAL_CUSTOMERS
synonyms: [customer count]
description: Total customers
expr: ( CUSTOMER_ID)
name: PRODUCTS
description: Product catalog pricing, tiers, renewal upsell rates.
synonyms: [catalog, SKUs, product list]
base_table:
database: DATABASE
schema: SCHEMA
: PRODUCT_CATALOG_TABLE
primary_key:
columns: [PRODUCT_SKU]
dimensions:
name: PRODUCT_SKU
synonyms: [SKU, product id]
description: product SKU
expr: PRODUCT_SKU
data_type: TEXT
name: PRODUCT_NAME
synonyms: [name, product, item name]
description: Product display name
expr: PRODUCT_NAME
data_type: TEXT
name: PRODUCT_CATEGORY
synonyms: [category, product line]
description: Product category
expr: PRODUCT_CATEGORY
data_type: TEXT
name: PRODUCT_FEATURE
synonyms: [feature, capability]
description: product feature
expr: PRODUCT_FEATURE
data_type: TEXT
name: PRODUCT_TIER
synonyms: [tier, pricing tier, plan, edition]
description: "Product tier: Starter, Professional, Enterprise"
expr: PRODUCT_TIER
data_type: TEXT
sample_values: [Starter, Professional, Enterprise]
name: BILLING_FREQUENCY
synonyms: [billing , payment frequency]
description: "Billing frequency: Monthly, Annual, Multi-Year"
expr: BILLING_FREQUENCY
data_type: TEXT
sample_values: [Monthly, Annual, Multi]
name: TYPICAL_DISCOUNT_RANGE
synonyms: [discount ]
description: Typical discount
expr: TYPICAL_DISCOUNT_RANGE
data_type: TEXT
facts:
name: LIST_PRICE_USD
synonyms: [list price, MSRP]
description: List price USD
expr: LIST_PRICE_USD
data_type: NUMBER
name: AVERAGE_CONTRACT_LENGTH_MONTHS
synonyms: [contract length, avg term]
description: Average contract length months
expr: AVERAGE_CONTRACT_LENGTH_MONTHS
data_type: NUMBER
name: RENEWAL_RATE_PCT
synonyms: [renewal rate, retention rate]
description: Product renewal rate percentage
expr: RENEWAL_RATE_PCT
data_type: NUMBER
name: UPSELL_RATE_PCT
synonyms: [upsell rate, expansion rate]
description: Product upsell rate percentage
expr: UPSELL_RATE_PCT
data_type: NUMBER
metrics:
name: AVG_LIST_PRICE
synonyms: [average price]
description: Average list price USD
expr: (LIST_PRICE_USD)
name: AVG_RENEWAL_RATE
synonyms: [average renewal]
description: Average renewal rate
expr: (RENEWAL_RATE_PCT)
name: REPS
description: Sales rep performance quota, attainment, win rate, pipeline coverage.
synonyms: [sales reps, salespeople, account executives, AEs]
base_table:
database: DATABASE
schema: SCHEMA
: SALES_REP_PERFORMANCE_TABLE
primary_key:
columns: [SALES_REP_ID]
dimensions:
name: SALES_REP_ID
synonyms: [rep id, AE id]
description: sales rep identifier
expr: SALES_REP_ID
data_type: TEXT
name: REP_NAME
synonyms: [name, sales rep name, AE name]
description: name the sales rep
expr: REP_NAME
data_type: TEXT
name: TERRITORY
synonyms: [assigned territory, sales territory]
description: Sales territory assignment
expr: TERRITORY
data_type: TEXT
time_dimensions:
name: HIRE_DATE
synonyms: [ , joined ]
description: the rep was hired
expr: HIRE_DATE
data_type:
facts:
name: QUOTA_USD
synonyms: [quota, target, sales target]
description: Annual quota USD
expr: QUOTA_USD
data_type: NUMBER
name: QUOTA_ATTAINMENT_PCT
synonyms: [attainment, quota attainment]
description: Quota attainment percentage
expr: QUOTA_ATTAINMENT_PCT
data_type: NUMBER
name: AVG_DEAL_SIZE
synonyms: [average deal]
description: Average deal size USD
expr: AVG_DEAL_SIZE
data_type: NUMBER
name: WIN_RATE_PCT
synonyms: [win rate, rate]
description: Win rate percentage
expr: WIN_RATE_PCT
data_type: NUMBER
name: AVG_SALES_CYCLE_DAYS
synonyms: [avg ]
description: Average days
expr: AVG_SALES_CYCLE_DAYS
data_type: NUMBER
name: PIPELINE_COVERAGE_RATIO
synonyms: [coverage ratio, pipeline multiple]
description: Pipeline coverage ratio
expr: PIPELINE_COVERAGE_RATIO
data_type: NUMBER
name: ACTIVE_OPPORTUNITIES_COUNT
synonyms: [active opps, opportunities]
description: Active opportunity count
expr: ACTIVE_OPPORTUNITIES_COUNT
data_type: NUMBER
metrics:
name: AVG_QUOTA_ATTAINMENT
synonyms: [average attainment, team attainment]
description: Average quota attainment across reps
expr: (QUOTA_ATTAINMENT_PCT)
name: AVG_WIN_RATE
synonyms: [average win rate, team win rate]
description: Average win rate across reps
expr: (WIN_RATE_PCT)
name: PIPELINE
description: Active pipeline opportunities stages, probabilities, engagement signals.
synonyms: [opportunities, opps, deals progress, funnel]
base_table:
database: DATABASE
schema: SCHEMA
: SALES_PIPELINE_OPPORTUNITIES
primary_key:
columns: [OPPORTUNITY_ID]
dimensions:
name: OPPORTUNITY_ID
synonyms: [opp id, deal id]
description: opportunity identifier
expr: OPPORTUNITY_ID
data_type: TEXT
name: CURRENT_STAGE
synonyms: [stage, deal stage, pipeline stage]
description: "Stage: Prospecting, Qualification, Proposal, Negotiation, Closed Won, Closed Lost"
expr: CURRENT_STAGE
data_type: TEXT
sample_values: [Prospecting, Qualification, Proposal, Negotiation, Closed Won, Closed Lost]
name: COMPETITOR_PRESENT
synonyms: [competitive deal, has competitor]
description: Whether a competitor present
expr: COMPETITOR_PRESENT
data_type:
name: DECISION_MAKER_ENGAGED
synonyms: [DM engaged]
description: Whether decision maker engaged
expr: DECISION_MAKER_ENGAGED
data_type:
time_dimensions:
name: CREATED_DATE
synonyms: [opp created, created]
description: Opportunity creation
expr: CREATED_DATE
data_type:
name: EXPECTED_CLOSE_DATE
synonyms: [expected , target ]
description: Expected
expr: EXPECTED_CLOSE_DATE
data_type:
name: STAGE_ENTRY_DATE
synonyms: [entered stage]
description: entered stage
expr: STAGE_ENTRY_DATE
data_type:
name: LAST_ACTIVITY_DATE
synonyms: [ touch, contact]
description: Most recent activity
expr: LAST_ACTIVITY_DATE
data_type:
facts:
name: ESTIMATED_VALUE
synonyms: [opp , deal , opportunity amount]
description: Estimated deal USD
expr: ESTIMATED_VALUE
data_type: NUMBER
name: WEIGHTED_FORECAST_VALUE
synonyms: [weighted , forecast ]
description: Probabilityweighted forecast USD
expr: WEIGHTED_FORECAST_VALUE
data_type: NUMBER
name: CLOSE_PROBABILITY
synonyms: [win probability]
description: probability ()
expr: CLOSE_PROBABILITY
data_type: NUMBER
name: DAYS_IN_CURRENT_STAGE
synonyms: [days stage, stage duration]
description: Days stage
expr: DAYS_IN_CURRENT_STAGE
data_type: NUMBER
name: ACTIVITY_COUNT_LAST_30_DAYS
synonyms: [recent activity, activity count]
description: Activities days
expr: ACTIVITY_COUNT_LAST_30_DAYS
data_type: NUMBER
metrics:
name: TOTAL_PIPELINE_VALUE
synonyms: [pipeline ]
description: Total estimated pipeline
expr: (ESTIMATED_VALUE)
name: TOTAL_WEIGHTED_FORECAST
synonyms: [weighted pipeline, total forecast]
description: Total weighted forecast
expr: (WEIGHTED_FORECAST_VALUE)
name: TOTAL_OPPORTUNITIES
synonyms: [opp count]
description: Total opportunities
expr: (OPPORTUNITY_ID)
name: AVG_DAYS_IN_STAGE
synonyms: [average stage duration]
description: Average days stage
expr: (DAYS_IN_CURRENT_STAGE)
name: MARKETING
description: Marketing lead attribution campaigns, lead scores, spend.
synonyms: [leads, campaigns, marketing leads, attribution]
base_table:
database: DATABASE
schema: SCHEMA
: MARKETING_ATTRIBUTION_TABLE
primary_key:
columns: [LEAD_ID]
dimensions:
name: LEAD_ID
synonyms: [lead, marketing lead]
description: lead identifier
expr: LEAD_ID
data_type: TEXT
name: LEAD_SOURCE_DETAIL
synonyms: [source detail, lead channel]
description: "Lead source: Google Ads, LinkedIn, Trade Show, Webinar"
expr: LEAD_SOURCE_DETAIL
data_type: TEXT
sample_values: [Google Ads, LinkedIn, Trade , Webinar]
name: CAMPAIGN_NAME
synonyms: [campaign, marketing campaign]
description: Marketing campaign name
expr: CAMPAIGN_NAME
data_type: TEXT
name: FIRST_TOUCH_CHANNEL
synonyms: [ touch, channel]
description: marketing touch channel
expr: FIRST_TOUCH_CHANNEL
data_type: TEXT
name: LAST_TOUCH_CHANNEL
synonyms: [ touch, channel]
description: marketing touch before conversion
expr: LAST_TOUCH_CHANNEL
data_type: TEXT
facts:
name: LEAD_SCORE
synonyms: [score, lead quality, MQL score]
description: Lead quality score ()
expr: LEAD_SCORE
data_type: NUMBER
name: LEAD_TO_OPPORTUNITY_DAYS
synonyms: [lead conversion , days opportunity]
description: Days lead opportunity
expr: LEAD_TO_OPPORTUNITY_DAYS
data_type: NUMBER
name: MARKETING_SPEND_ALLOCATED
synonyms: [spend, marketing cost, ad spend]
description: Marketing spend allocated USD
expr: MARKETING_SPEND_ALLOCATED
data_type: NUMBER
metrics:
name: TOTAL_MARKETING_SPEND
synonyms: [total spend, total ad spend]
description: Total marketing spend USD
expr: (MARKETING_SPEND_ALLOCATED)
name: AVG_LEAD_SCORE
synonyms: [average lead score]
description: Average lead quality score
expr: (LEAD_SCORE)
name: TOTAL_LEADS
synonyms: [lead count]
description: Total marketing leads
expr: (LEAD_ID)
name: AVG_LEAD_CONVERSION_TIME
synonyms: [average conversion ]
description: Average days lead opportunity
expr: (LEAD_TO_OPPORTUNITY_DAYS)
relationships:
name: TRANSACTIONS_TO_CUSTOMERS
left_table: TRANSACTIONS
right_table: CUSTOMERS
join_type: left_outer
relationship_type: many_to_one
relationship_columns:
left_column: CUSTOMER_ID
right_column: CUSTOMER_ID
name: TRANSACTIONS_TO_PRODUCTS
left_table: TRANSACTIONS
right_table: PRODUCTS
join_type: left_outer
relationship_type: many_to_one
relationship_columns:
left_column: PRODUCT_SKU
right_column: PRODUCT_SKU
name: TRANSACTIONS_TO_REPS
left_table: TRANSACTIONS
right_table: REPS
join_type: left_outer
relationship_type: many_to_one
relationship_columns:
left_column: SALES_REP_ID
right_column: SALES_REP_ID
verified_queries:
name: revenue_by_region
question: What the total revenue region?
:
t.REGION, (t.DEAL_SIZE_USD) TOTAL_REVENUE
DATABASE.SCHEMA.SALES_TRANSACTIONS_FACT_TABLE t
t.REGION TOTAL_REVENUE
name: top_reps_by_attainment
question: Which sales reps have the highest quota attainment?
:
r.REP_NAME, r.TERRITORY, r.QUOTA_ATTAINMENT_PCT, r.QUOTA_USD
DATABASE.SCHEMA.SALES_REP_PERFORMANCE_TABLE r
r.QUOTA_ATTAINMENT_PCT LIMIT
name: pipeline_by_stage
question: What the pipeline broken down stage?
:
p.CURRENT_STAGE, (p.OPPORTUNITY_ID) NUM_OPPORTUNITIES,
(p.ESTIMATED_VALUE) TOTAL_VALUE, (p.WEIGHTED_FORECAST_VALUE) WEIGHTED_VALUE
DATABASE.SCHEMA.SALES_PIPELINE_OPPORTUNITIES p
p.CURRENT_STAGE TOTAL_VALUE
name: revenue_by_product_tier
question: How does revenue break down product tier?
:
pr.PRODUCT_TIER, (t.TRANSACTION_ID) NUM_DEALS,
(t.DEAL_SIZE_USD) TOTAL_REVENUE, (t.DEAL_SIZE_USD) AVG_DEAL_SIZE
DATABASE.SCHEMA.SALES_TRANSACTIONS_FACT_TABLE t
DATABASE.SCHEMA.PRODUCT_CATALOG_TABLE pr t.PRODUCT_SKU pr.PRODUCT_SKU
pr.PRODUCT_TIER TOTAL_REVENUE
name: top_customers_by_ltv
question: Who the top customers lifetime ?
:
c.COMPANY_NAME, c.INDUSTRY, c.COMPANY_SIZE, c.CUSTOMER_LIFETIME_VALUE,
c.CHURN_RISK_SCORE, c.EXPANSION_POTENTIAL
DATABASE.SCHEMA.CUSTOMER_DIMENSION_TABLE c
c.CUSTOMER_LIFETIME_VALUE LIMIT
name: marketing_spend_by_source
question: What the total marketing spend lead source?
:
m.LEAD_SOURCE_DETAIL, (m.LEAD_ID) NUM_LEADS,
(m.MARKETING_SPEND_ALLOCATED) TOTAL_SPEND, (m.LEAD_SCORE) AVG_LEAD_SCORE
DATABASE.SCHEMA.MARKETING_ATTRIBUTION_TABLE m
m.LEAD_SOURCE_DETAIL TOTAL_SPEND
$$,
);
Verify creation:
SHOW SEMANTIC VIEWS IN SCHEMA <DATABASE>.<SCHEMA>;
Step 3: Create Cortex Agent
Execute:
CREATE OR REPLACE AGENT <DATABASE>.<SCHEMA>.SALES_OPS_AGENT
COMMENT = 'Sales Operations analytics agent'
FROM SPECIFICATION $$
instructions:
system: >
You are a Sales Operations analyst. Help users explore revenue trends, pipeline health,
sales rep performance, customer segmentation, product mix, and marketing attribution.
Provide clear, data-driven answers. When presenting numbers, use appropriate formatting
(currency for USD values, percentages for rates). Always include relevant context.
response: >
Provide concise, actionable answers about sales operations data. Include relevant
numbers and comparisons when helpful.
tools:
- tool_spec:
type: cortex_analyst_text_to_sql
name: sales_ops_analyst
description: >
Analyzes sales operations data including revenue, pipeline, rep performance,
customer health, product catalog, and marketing attribution.
tool_resources:
sales_ops_analyst:
semantic_view: <DATABASE>.<SCHEMA>.SALES_OPS_SEMANTIC_VIEW
execution_environment:
type: warehouse
warehouse: <WAREHOUSE>
$$;
Verify creation:
DESCRIBE AGENT <DATABASE>.<SCHEMA>.SALES_OPS_AGENT;
Step 4: Test the Agent
Execute these test queries to validate the agent works:
SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN(
'<DATABASE>.<SCHEMA>.SALES_OPS_AGENT',
'{"messages": [{"role": "user", "content": [{"type": "text", "text": "What is total revenue by region?"}]}]}'
) AS AGENT_RESPONSE;
SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN(
'<DATABASE>.<SCHEMA>.SALES_OPS_AGENT',
'{"messages": [{"role": "user", "content": [{"type": "text", "text": "Which 5 reps have the highest quota attainment?"}]}]}'
) AS AGENT_RESPONSE;
SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN(
'<DATABASE>.<SCHEMA>.SALES_OPS_AGENT',
'{"messages": [{"role": "user", "content": [{"type": "text", "text": "What is pipeline value by stage and how many deals have competitors?"}]}]}'
) AS AGENT_RESPONSE;
Verify each test returns valid data with SQL generated by Cortex Analyst.
Stop: Confirm all tests pass before proceeding.
Step 5: Create MCP Server
Execute:
CREATE OR REPLACE MCP SERVER <DATABASE>.<SCHEMA>.SALES_OPS_MCP_SERVER
COMMENT = 'MCP Server exposing Sales Ops Agent for Amazon QuickSight'
FROM SPECIFICATION $$
tools:
- title: "Sales Ops Agent"
name: "sales_ops_agent"
type: "CORTEX_AGENT_RUN"
identifier: "<DATABASE>.<SCHEMA>.SALES_OPS_AGENT"
description: "Ask questions about sales operations data including revenue, pipeline, rep performance, customers, products, and marketing."
$$;
Grant permissions:
GRANT USAGE ON MCP SERVER <DATABASE>.<SCHEMA>.SALES_OPS_MCP_SERVER TO ROLE CORTEX_QUICK_HOL_ROLE;
GRANT USAGE ON AGENT <DATABASE>.<SCHEMA>.SALES_OPS_AGENT TO ROLE CORTEX_QUICK_HOL_ROLE;
GRANT SELECT ON SEMANTIC VIEW CORTEX_QUICK_HOL_DB.CORTEX_QUICK_HOL_SCHEMA.SALES_OPS_SEMANTIC_VIEW TO ROLE CORTEX_QUICK_HOL_ROLE;
Verify creation:
SHOW MCP SERVERS IN SCHEMA <DATABASE>.<SCHEMA>;
Step 6: Create OAuth Integration
Execute:
USE ROLE ACCOUNTADMIN;
Stop: Confirm if ACCOUNTADMIN role is used, which is required for next step - to create security integration.
CREATE OR REPLACE SECURITY INTEGRATION SALES_OPS_QUICK_SUITE_OAUTH
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://us-west-2.quicksight.aws.amazon.com/sn/oauthcallback'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 86400;
Get OAuth credentials:
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('SALES_OPS_QUICK_SUITE_OAUTH');
Present credentials to user:
Save these credentials for QuickSight configuration:
OAUTH_CLIENT_ID: <value from query>
OAUTH_CLIENT_SECRET: <value from query>
Stop: Ensure user has copied both values before proceeding.
Step 7: Configure Amazon QuickSight (Manual Steps)
Present these instructions to the user:
AMAZON QUICKSIGHT CONFIGURATION
================================
1. Open the Amazon QuickSight console
2. Navigate to: Manage QuickSight -> Integrations -> Click "Add" (+)
3. On the "Create Integration" page, enter:
Name: Sales Ops Cortex Agent
Description: Snowflake Sales Ops analytics via MCP
MCP server endpoint: https://<ACCOUNT>.snowflakecomputing.com/api/v2/databases/<DATABASE>/schemas/<SCHEMA>/mcp-servers/SALES_OPS_MCP_SERVER
4. Click "Next"
5. Select authentication: "User authentication (OAuth)"
6. Select: "Manual configuration"
7. Fill in OAuth details:
Client ID: <OAUTH_CLIENT_ID from Step 6>
Client Secret: <OAUTH_CLIENT_SECRET from Step 6>
Token URL: https://<ACCOUNT>.snowflakecomputing.com/oauth/token-request
Auth URL: https://<ACCOUNT>.snowflakecomputing.com/oauth/authorize
Redirect URL: https://us-west-2.quicksight.aws.amazon.com/sn/oauthcallback
8. Click "Create and continue"
**Present final instructions:**
COMPLETE THE CONNECTION
- Return to Amazon QuickSight
- Navigate to Integrations
- Find your MCP integration
- Click to authenticate with your Snowflake credentials
- Done! The Sales Ops Agent is now available in QuickSight.
TEST YOUR AGENT
Ask questions like:
- "What is total revenue by region?"
- "Which reps have the highest quota attainment?"
- "What is pipeline value by stage?"
IMPORTANT LIMITATIONS
- MCP operations have a 60-second timeout
- Tool lists are static after registration (refresh manually)
---
## Quick Reference
### Objects Created
| Object | Name |
|--------|------|
| Semantic View | `<DATABASE>.<SCHEMA>.SALES_OPS_SEMANTIC_VIEW` |
| Cortex Agent | `<DATABASE>.<SCHEMA>.SALES_OPS_AGENT` |
| MCP Server | `<DATABASE>.<SCHEMA>.SALES_OPS_MCP_SERVER` |
| OAuth Integration | `SALES_OPS_QUICK_SUITE_OAUTH` |
### Key URLs
| URL | Value |
|-----|-------|
| MCP Endpoint | `https://<ACCOUNT>.snowflakecomputing.com/api/v2/databases/<DATABASE>/schemas/<SCHEMA>/mcp-servers/SALES_OPS_MCP_SERVER` |
| Auth URL | `https://<ACCOUNT>.snowflakecomputing.com/oauth/authorize` |
| Token URL | `https://<ACCOUNT>.snowflakecomputing.com/oauth/token-request` |
---
## Troubleshooting
### "Object does not exist"
```sql
SHOW MCP SERVERS IN SCHEMA <DATABASE>.<SCHEMA>;
SHOW GRANTS ON MCP SERVER <DATABASE>.<SCHEMA>.SALES_OPS_MCP_SERVER;
OAuth errors
DESCRIBE SECURITY INTEGRATION SALES_OPS_QUICK_SUITE_OAUTH;
Agent not responding
SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN(
'<DATABASE>.<SCHEMA>.SALES_OPS_AGENT',
'{"messages": [{"role": "user", "content": [{"type": "text", "text": "What is total revenue?"}]}]}'
);
Timeout errors (HTTP 424)
- QuickSight MCP has a 60-second timeout
- Use simpler prompts or optimize queries
Cleanup
DROP MCP SERVER IF EXISTS <DATABASE>.<SCHEMA>.SALES_OPS_MCP_SERVER;
DROP AGENT IF EXISTS <DATABASE>.<SCHEMA>.SALES_OPS_AGENT;
DROP SEMANTIC VIEW IF EXISTS <DATABASE>.<SCHEMA>.SALES_OPS_SEMANTIC_VIEW;
DROP SECURITY INTEGRATION IF EXISTS SALES_OPS_QUICK_SUITE_OAUTH;
Stopping Points
- After Step 1: Confirm database/schema/warehouse/account values
- After Step 4: Verify all agent tests pass
- After Step 6: Ensure OAuth credentials are saved
- After Step 7: Wait for Redirect URL from QuickSight
Output
- Semantic View with 6 tables, relationships, and verified queries
- Cortex Agent powered by Cortex Analyst text-to-SQL
- MCP Server exposing the agent
- OAuth Security Integration for Amazon QuickSight
- Working end-to-end connection from QuickSight to Snowflake
