ECommerce MCP Server

Snowflake Iceberg + DuckDB + Cortex AI + MCP Demo

End-to-end demo: Iceberg tables federated to DuckDB via Horizon Catalog, Cortex AI stack (Analyst, Search, Agent), and MCP Server exposed externally.

Architecture

┌─────────────────────────── Snowflake ───────────────────────────┐
│                                                                  │
│  Iceberg Tables (v2)         Cortex AI                           │
│  ┌──────────────────┐        ┌─────────────────────────────┐    │
│  │ CUSTOMERS        │───────▶│ Semantic View (Analyst)      │    │
│  │ PRODUCTS         │        │ Cortex Search (Reviews)      │    │
│  │ ORDERS           │        │ Cortex Agent                 │    │
│  └────────┬─────────┘        └──────────────┬──────────────┘    │
│           │                                  │                   │
│           │ S3 (Parquet)                     │ MCP Server        │
│           │                                  │                   │
├───────────┼──────────────────────────────────┼───────────────────┤
│  Horizon REST Catalog                        │                   │
│  (OAuth2 + vended credentials)               │ (PAT / OAuth2)    │
└───────────┼──────────────────────────────────┼───────────────────┘
            │                                  │
            ▼                                  ▼
   ┌─────────────────┐              ┌───────────────────────┐
   │  DuckDB          │              │  External Clients     │
   │  (read + write)  │              │  Claude · Cursor ·    │
   │                  │              │  Python · curl        │
   └─────────────────┘              └───────────────────────┘

Demo Steps

Part A: Iceberg + DuckDB Federation

Step 1: Run setup_snowflake.sql in Snowsight

Creates database, Iceberg tables, sample data, service user, and PAT. Save the PAT token from the output.

Step 2: Set Up Python

python3 -m venv .venv && source .venv/bin/activate && pip install -r requirements.txt

Step 3: Export PAT

export HORIZON_PAT="<paste PAT from Step 1 output>"

All scripts read from this environment variable — set it once per terminal session.

Step 4: Run DuckDB Demo

python3 step1_connect.py   # Connect DuckDB to Horizon
python3 step2_read.py      # Read Iceberg tables
python3 step3_write.py     # Write new rows from DuckDB
python3 step4_verify.py    # Verify round-trip

Step 5: Verify from Snowflake

SELECT * FROM ICEBERG_DUCKDB_DEMO.PUBLIC.CUSTOMERS WHERE customer_id = 200;

---

Part B: Cortex AI Stack

Step 5: Run setup_cortex.sql in Snowsight

Creates Semantic View, Cortex Search, Agent, and MCP Server.

Step 6: Test in Snowsight

Go to AI & ML > Agents > ECOMMERCE_AGENT and ask:

• "What is the total revenue?"
• "Which city has the most customers?"
• "What do customers say about the mechanical keyboard?"

---

Part C: MCP Server (External Access)

Step 7: Run Python MCP Client

python3 mcp_client.py

This discovers tools, then enters interactive mode where you ask questions and get answers via Cortex Analyst + SQL execution.

Step 8: Test via curl

export MCP_URL="https://<ORG>-<ACCOUNT>.snowflakecomputing.com/api/v2/databases/ICEBERG_DUCKDB_DEMO/schemas/PUBLIC/mcp-servers/ECOMMERCE_MCP_SERVER"
export PAT="<YOUR_PAT>"

# Discover tools
curl -s -X POST "$MCP_URL" \
  -H "Authorization: Bearer $PAT" \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list","params":{}}' | python3 -m json.tool

# Ask a question (Cortex Analyst)
curl -s -X POST "$MCP_URL" \
  -H "Authorization: Bearer $PAT" \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"ecommerce-analytics","arguments":{"message":"What is the total revenue?"}}}' | python3 -m json.tool

# Execute the SQL
curl -s -X POST "$MCP_URL" \
  -H "Authorization: Bearer $PAT" \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"run-sql","arguments":{"sql":"SELECT * FROM SEMANTIC_VIEW(ICEBERG_DUCKDB_DEMO.PUBLIC.ECOMMERCE_ANALYTICS_SV METRICS total_revenue)"}}}' | python3 -m json.tool

# Search reviews
curl -s -X POST "$MCP_URL" \
  -H "Authorization: Bearer $PAT" \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"product-reviews-search","arguments":{"query":"keyboard typing experience","limit":3}}}' | python3 -m json.tool

Step 9: Connect Claude.ai (optional)

1. Settings > Connectors > Add custom connector
2. URL: https://<ORG>-<ACCOUNT>.snowflakecomputing.com/api/v2/databases/ICEBERG_DUCKDB_DEMO/schemas/PUBLIC/mcp-servers/ECOMMERCE_MCP_SERVER
3. Authentication: Bearer token using your PAT

Key Notes

• DuckDB ATTACH requires: DISABLE_MULTI_TABLE_COMMIT true, SKIP_CREATE_TABLE_METADATA_UPDATES true, REMOVE_FILES_ON_DELETE false
• CORTEX_AGENT_RUN tool type does not work with external MCP clients — use Analyst + Search + SQL individually
• Service user needs DEFAULT_WAREHOUSE set for SYSTEM_EXECUTE_SQL tool
• PAT expires in 30 days — regenerate if needed

Files

| File | Purpose | |------|---------| | setup_snowflake.sql | Database, Iceberg tables, data, service user, PAT | | setup_cortex.sql | Semantic View, Search, Agent, MCP Server | | step1_connect.py | DuckDB: Connect to Horizon | | step2_read.py | DuckDB: Read Iceberg tables | | step3_write.py | DuckDB: Write to Iceberg | | step4_verify.py | DuckDB: Verify round-trip | | mcp_client.py | Python MCP client (external access demo) | | requirements.txt | Python dependencies |