Overview

Prerequisites

What You'll Learn

What You'll Build

Setup Snowflake Infrastructure

Step 1: Create Database and Warehouse

USE ROLE ACCOUNTADMIN;

-- Create the demo database
CREATE DATABASE IF NOT EXISTS SUPPLY_CHAIN_DEMO;

-- Create a dedicated warehouse (X-Small, auto-suspend after 60s)
CREATE WAREHOUSE IF NOT EXISTS SUPPLY_CHAIN_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND   = 60
  AUTO_RESUME    = TRUE;

-- Set context
USE DATABASE SUPPLY_CHAIN_DEMO;
USE SCHEMA PUBLIC;
USE WAREHOUSE SUPPLY_CHAIN_WH;

-- Verify
SHOW DATABASES LIKE 'SUPPLY_CHAIN_DEMO';
SHOW WAREHOUSES LIKE 'SUPPLY_CHAIN_WH';

Step 2: Create Tables

Step 3: Load Structured Data

Step 4: Load Semi-Structured Data

Step 5: Load Unstructured Data

Create Cortex Search Services

USE DATABASE SUPPLY_CHAIN_DEMO;
USE SCHEMA PUBLIC;
USE WAREHOUSE SUPPLY_CHAIN_WH;

-- 1. SUPPLIER_COMMS_SEARCH --- search supplier emails
CREATE OR REPLACE CORTEX SEARCH SERVICE SUPPLIER_COMMS_SEARCH
  ON EMAIL_BODY
  ATTRIBUTES SUPPLIER_NAME, SUBJECT, DATE_SENT, SENDER, PRIORITY
  WAREHOUSE = SUPPLY_CHAIN_WH
  TARGET_LAG = '1 hour'
  AS (
    SELECT EMAIL_BODY, SUPPLIER_NAME, SUBJECT,
           DATE_SENT::VARCHAR AS DATE_SENT, SENDER, PRIORITY
    FROM SUPPLIER_EMAILS
  );

-- 2. WAREHOUSE_INSPECTIONS_SEARCH --- search inspection notes
CREATE OR REPLACE CORTEX SEARCH SERVICE WAREHOUSE_INSPECTIONS_SEARCH
  ON INSPECTION_NOTES
  ATTRIBUTES INSPECTION_DATE, INSPECTOR, OVERALL_RATING, FOLLOW_UP_REQUIRED
  WAREHOUSE = SUPPLY_CHAIN_WH
  TARGET_LAG = '1 hour'
  AS (
    SELECT INSPECTION_NOTES, INSPECTION_DATE::VARCHAR AS INSPECTION_DATE,
           INSPECTOR, OVERALL_RATING,
           FOLLOW_UP_REQUIRED::VARCHAR AS FOLLOW_UP_REQUIRED
    FROM WAREHOUSE_INSPECTION_NOTES
  );

NOTE:

Wait 2-3 minutes after running this script for the search services to finish indexing before proceeding.

SHOW CORTEX SEARCH SERVICES IN SUPPLY_CHAIN_DEMO.PUBLIC;

Create Semantic View

Create Cortex Agent

CREATE OR REPLACE AGENT SUPPLY_CHAIN_AGENT
  COMMENT = 'Supply chain intelligence agent combining structured data queries (Cortex Analyst) with unstructured text search (Cortex Search) across supplier communications and warehouse inspections.'
  FROM SPECIFICATION
  $$
  orchestration:
    budget:
      seconds: 60
      tokens: 16000

  instructions:
    system: >
      You are a supply chain intelligence assistant. You help users analyze
      supplier performance, inventory levels, purchase orders, and warehouse
      operations. You combine structured data analysis with unstructured
      text search across supplier emails and warehouse inspection notes.
      Shipments, delivery tracking, store sales, carrier data, and logistics
      incident reports are managed in Microsoft Fabric and are not available here.
    orchestration: >
      Use Analyst for questions about suppliers, products, inventory levels,
      purchase orders, costs, delays, and delivery rates.
      Use SupplierEmailSearch for questions about supplier communications,
      emails, negotiations, complaints, or correspondence.
      Use InspectionSearch for questions about warehouse inspections,
      facility conditions, compliance, or inspection findings.
      For logistics incidents, safety events, or disruptions, mention that
      this data is available in the Microsoft Fabric domain.
    response: >
      Provide concise, data-driven answers. When presenting numbers,
      include context and comparisons where possible. If data from
      Microsoft Fabric (shipments, sales, incidents) is needed, mention
      that it is available in the Fabric domain.
    sample_questions:
      - question: "Which suppliers have the worst on-time delivery rates?"
        answer: "I'll query the purchase orders data to calculate on-time delivery rates by supplier."
      - question: "What products are at risk of stockout?"
        answer: "I'll analyze current inventory levels against reorder points to identify at-risk items."
      - question: "Show me recent supplier complaints."
        answer: "I'll search supplier emails for recent complaints and quality issues."

  tools:
    - tool_spec:
        type: "cortex_analyst_text_to_sql"
        name: "Analyst"
        description: "Queries structured supply chain data including suppliers, products,
          warehouses, inventory levels, and purchase orders. Use for quantitative
          questions about costs, delays, stock levels, and delivery performance."
    - tool_spec:
        type: "cortex_search"
        name: "SupplierEmailSearch"
        description: "Searches supplier email communications for information about
          negotiations, complaints, updates, and correspondence with suppliers."
    - tool_spec:
        type: "cortex_search"
        name: "InspectionSearch"
        description: "Searches warehouse inspection notes for facility conditions,
          compliance findings, and maintenance issues."

  tool_resources:
    Analyst:
      semantic_view: "SUPPLY_CHAIN_DEMO.PUBLIC.SUPPLY_CHAIN_ANALYTICS"
      execution_environment:
        type: "warehouse"
        warehouse: "SUPPLY_CHAIN_WH"
    SupplierEmailSearch:
      name: "SUPPLY_CHAIN_DEMO.PUBLIC.SUPPLIER_COMMS_SEARCH"
      max_results: "5"
    InspectionSearch:
      name: "SUPPLY_CHAIN_DEMO.PUBLIC.WAREHOUSE_INSPECTIONS_SEARCH"
      max_results: "5"
  $$;

SHOW AGENTS IN SUPPLY_CHAIN_DEMO.PUBLIC;

Create MCP Server and OAuth Integration

MCP Server

CREATE OR REPLACE MCP SERVER SUPPLY_CHAIN_MCP_SERVER
FROM SPECIFICATION $$
version: 1
tools:
  - title: "Supply Chain Intelligence Agent"
    name: "supply-chain-agent"
    identifier: "SUPPLY_CHAIN_DEMO.PUBLIC.SUPPLY_CHAIN_AGENT"
    type: "CORTEX_AGENT_RUN"
    description: "Supply chain intelligence agent for the Supply and Inventory domain.
      Answers questions about suppliers (reliability scores, lead times, countries),
      products (categories, costs, prices), warehouses (capacity, locations),
      inventory (stock levels, reorder points, stockout risk), and purchase orders
      (delivery delays, costs, status). Also searches supplier emails and warehouse
      inspection notes. Send natural language questions directly."
$$;

OAuth Security Integration

-- Allow privileged roles in OAuth
ALTER ACCOUNT SET OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = FALSE;

-- Create OAuth security integration for AI Foundry
CREATE OR REPLACE SECURITY INTEGRATION foundry_mcp_oauth
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  ENABLED = TRUE
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://ai.azure.com/oauth/callback'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 86400
  OAUTH_USE_SECONDARY_ROLES = 'IMPLICIT'
  BLOCKED_ROLES_LIST = ();

Retrieve OAuth Credentials

-- Client ID + endpoints:
DESCRIBE SECURITY INTEGRATION foundry_mcp_oauth;

-- Client Secret:
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('FOUNDRY_MCP_OAUTH');

https://<YOUR_ACCOUNT>.snowflakecomputing.com/api/v2/databases/SUPPLY_CHAIN_DEMO/schemas/PUBLIC/mcp-servers/SUPPLY_CHAIN_MCP_SERVER/sse

SHOW MCP SERVERS IN SUPPLY_CHAIN_DEMO.PUBLIC;

Setup Microsoft Fabric Lakehouse

Create a Fabric Workspace

Create a Lakehouse

Upload CSV Files

Load CSV Files into Delta Tables

Verify the Lakehouse Tables

-- Verify freight costs
SELECT carrier_name, COUNT(*) AS shipment_count,
       AVG(shipping_cost_usd) AS avg_cost,
       SUM(CASE WHEN on_time = 'Yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS on_time_pct
FROM freight_costs
GROUP BY carrier_name
ORDER BY shipment_count DESC;

-- Verify customer returns
SELECT reason_category, COUNT(*) AS return_count,
       SUM(refund_amount) AS total_refunds,
       COUNT(DISTINCT product_id) AS products_affected
FROM customer_returns
GROUP BY reason_category
ORDER BY return_count DESC;

Create Fabric Data Agent

Add the Lakehouse as a Data Source

Add Data Agent Instructions

You are a Supply Chain Data Agent for freight cost and customer return data.

Data Source: SupplyChainLakehouse with two tables:

1. freight_costs --- Shipment-level freight records: shipment_id, product_id, carrier_name,
   origin_warehouse, destination_store, ship_date, shipping_cost_usd, weight_kg,
   distance_miles, on_time (Yes/No)
2. customer_returns --- Customer return records with complaint narratives: return_id,
   product_id, store_id, return_date, reason_category (Damaged in Transit / Quality Issue /
   Defective / Wrong Item / Changed Mind), customer_complaint (free-text narrative),
   refund_amount

Guidelines:
- For freight/shipping questions, query the freight_costs table
- For return/complaint questions, query the customer_returns table
- Use product_id to link records to Snowflake's product catalog (numeric IDs 1001-1050)
- Use store_id to link returns to specific stores (STR-xyz format)
- Use carrier_name to analyze carrier performance (FastFreight Logistics, TransGlobal
  Shipping, ExpressRoute Carriers, PrimeHaul Transport, SwiftMove Inc, Pacific Carriers
  Ltd, MidWest Express)
- The on_time column in freight_costs is Yes/No --- use it for on-time delivery rate calculations
- The customer_complaint column contains free-text narratives --- use LIKE for keyword searches
- When summarizing complaints, extract key details: what happened, product impact, and
  resolution requested

Add Example Queries

SELECT carrier_name, COUNT(*) AS total_shipments,
       SUM(CASE WHEN on_time = 'Yes' THEN 1 ELSE 0 END) AS on_time_count,
       SUM(CASE WHEN on_time = 'Yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS on_time_pct
FROM freight_costs
GROUP BY carrier_name
ORDER BY on_time_pct DESC

SELECT return_id, return_date, product_id, store_id, reason_category,
       customer_complaint, refund_amount
FROM customer_returns
WHERE reason_category IN ('Damaged in Transit', 'Defective')
ORDER BY return_date DESC

SELECT carrier_name, COUNT(*) AS shipment_count,
       AVG(shipping_cost_usd) AS avg_cost,
       AVG(weight_kg) AS avg_weight,
       AVG(distance_miles) AS avg_distance
FROM freight_costs
GROUP BY carrier_name
ORDER BY avg_cost DESC

Publish the Data Agent

Configure Microsoft AI Foundry Orchestrator

Create an AI Foundry Project

Deploy an OpenAI Model

Create the Orchestrator Agent

You are a supply chain assistant with two data sources. Your job is to query BOTH sources
and combine the results.

You have two tools:
- snowflake-mcp-supplychain: inventory, suppliers, purchase orders, warehouses, products,
  supplier emails, warehouse inspections, IoT sensor logs
- sc_fabric_agent: freight costs/shipment data, customer returns with complaint narratives

These two tools have completely different data. One tool cannot answer for the other.

Snowflake (snowflake-mcp-supplychain) has STRUCTURED + UNSTRUCTURED data:
  - Suppliers, products, warehouses, inventory levels, purchase orders
  - Supplier emails (searchable)
  - Warehouse inspection notes (searchable)
  - IoT sensor logs

Fabric (sc_fabric_agent) has two tables:
  - freight_costs: shipment-level freight records with shipment_id, product_id, carrier_name,
    origin_warehouse, destination_store, ship_date, shipping_cost_usd, weight_kg,
    distance_miles, on_time (Yes/No)
  - customer_returns: return records with return_id, product_id, store_id, return_date,
    reason_category, customer_complaint (free-text narrative), refund_amount

Cross-platform join keys:
  - product_id: links Snowflake products/suppliers to Fabric freight costs and customer returns
  - carrier_name: Fabric freight_costs uses the same carrier names as referenced in Snowflake data
  - origin_warehouse / destination_store: city-based in Fabric, can be correlated with
    Snowflake warehouse data

For every user question, you must complete three phases before answering:

PHASE 1: Rewrite the question for each tool. Each tool should only be asked about data
it has. Always include linking IDs (product_id, store_id) in your request so results can
be joined later.

PHASE 2: Call both tools with their rewritten questions. You must call both tools before answering.

PHASE 3: Combine the results. Join on product_id (or other shared keys). Present one unified
answer showing data from both tools. Label which data came from which tool.

You are not allowed to answer the user until you have completed all three phases. If you
answer after calling only one tool, your answer is incomplete and wrong.

If one tool returns no results, still present the other tool's data and note the gap.

Add MCP Server and Fabric Tools to the Agent

Add Snowflake MCP Server as a Tool

Create an OAuth Connection in AI Foundry

Add the MCP Server Tool

Connect Fabric Data Agent

NOTE:

Alternative --- A2A Protocol: If direct Fabric integration is not available in your region, use the Agent-to-Agent (A2A) protocol: In Tools > + Add tool > A2A endpoint, provide the Fabric Data Agent's endpoint URL and configure authentication via Entra ID.

Test the Multi-Agent Orchestrator

Snowflake-Routed Queries (via snowflake-mcp-supplychain)

Which suppliers have reliability scores below 0.7?

What products are at risk of stockout in the next 5 days?

Search supplier emails about quality complaints

Show me warehouse inspection reports with Poor ratings

Fabric-Routed Queries (via sc_fabric_agent)

Which carriers have the highest average shipping costs?

What are the most common reasons for customer returns?

Show me customer complaints about defective products

Cross-Platform Queries (Both Tools)

Which products with critical stockout risk have the most customer complaints?

Are customers returning products from unreliable suppliers?

Cleanup

DROP DATABASE IF EXISTS SUPPLY_CHAIN_DEMO;
DROP WAREHOUSE IF EXISTS SUPPLY_CHAIN_WH;
DROP SECURITY INTEGRATION IF EXISTS foundry_mcp_oauth;
ALTER ACCOUNT SET OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = TRUE;

Conclusion

What You Accomplished

What You Learned

Related Resources