Skip to main content
Glama
curious-bigcat

ECommerce MCP Server

by curious-bigcat
OverviewSchemaRelated ServersScoreDiscussions
Python
Remote

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        │
   └─────────────────┘              └───────────────────────┘

Related MCP server: Commerce-MCP

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

This server cannot be installed

F
license - not found
-
quality - not tested
C
maintenance

How are these scores calculated?

Maintenance

–Maintainers
–Response time
–Release cycle
–Releases (12mo)
Commit activity

Resources

Unclaimed servers have limited discoverability.

Looking for Admin?

If you are the server author, to access and configure the admin panel.

Latest Blog Posts

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/curious-bigcat/snowflake-iceberg-duckdb-cortex-demo'

If you have feedback or need assistance with the MCP directory API, please join our Discord server