┌─────────────────┐       stdio / MCP        ┌──────────────────────┐
│  Claude Desktop │ ◄────────────────────── ► │    horizon-mcp       │
└─────────────────┘                           │  (this server)       │
                                              └──────────┬───────────┘
                                                         │
                              ┌──────────────────────────▼────────────────────────────┐
                              │          Snowflake Horizon IRC REST Catalog            │
                              │   GET /v1/{database}/namespaces                        │
                              │   GET /v1/{database}/namespaces/{ns}/tables            │
                              │   GET /v1/{database}/namespaces/{ns}/tables/{table}    │
                              └──────────────────────────┬────────────────────────────┘
                                                         │
                                               Vended credentials
                                         (ephemeral STS / SAS tokens)
                                                         │
                              ┌──────────────────────────▼────────────────────────────┐
                              │                     DuckDB                             │
                              │   ATTACH via Iceberg extension + OAuth secret          │
                              │   Reads Parquet files directly from cloud storage      │
                              └──────────────────────────┬────────────────────────────┘
                                                         │
                              ┌──────────────────────────▼────────────────────────────┐
                              │         Iceberg Parquet files (S3 / Azure ADLS)        │
                              │        managed by Snowflake external volumes            │
                              └───────────────────────────────────────────────────────┘

POST /polaris/api/catalog/v1/oauth/tokens
  grant_type=client_credentials
  scope=session:role:<ROLE>
  client_secret=<PAT>
→ { "access_token": "...", "expires_in": 3600 }

CREATE SECRET horizon_secret (
    TYPE iceberg,
    CLIENT_SECRET '<PAT>',
    OAUTH2_SERVER_URI '...',
    OAUTH2_GRANT_TYPE 'client_credentials',
    OAUTH2_SCOPE 'session:role:<ROLE>'
)

ATTACH 'MYDB' AS mydb (
    TYPE iceberg,
    SECRET horizon_secret,
    ENDPOINT 'https://<account>.snowflakecomputing.com/polaris/api/catalog'
)

SELECT * FROM horizonmcpdb.NORTHMART.ORDERS LIMIT 10

-- ── 1. Create the MCP role ──────────────────────────────────────────────────
CREATE ROLE IF NOT EXISTS HORIZON_MCP_ROLE;

-- ── 2. Grant catalog access ─────────────────────────────────────────────────
-- Replace MYDB and MYSCHEMA with your actual database/schema names
GRANT USAGE ON DATABASE MYDB TO ROLE HORIZON_MCP_ROLE;
GRANT USAGE ON SCHEMA MYDB.MYSCHEMA TO ROLE HORIZON_MCP_ROLE;

-- ── 3. Grant read access to Iceberg tables ──────────────────────────────────
-- Grant on all existing tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA MYDB.MYSCHEMA TO ROLE HORIZON_MCP_ROLE;
-- Grant on future tables automatically
GRANT SELECT ON FUTURE TABLES IN SCHEMA MYDB.MYSCHEMA TO ROLE HORIZON_MCP_ROLE;

-- ── 4. Grant access to the external volume ──────────────────────────────────
-- Required for Horizon to vend storage credentials
GRANT USAGE ON EXTERNAL VOLUME <your_external_volume_name> TO ROLE HORIZON_MCP_ROLE;

-- ── 5. Create a service user ────────────────────────────────────────────────
CREATE USER IF NOT EXISTS MCP_SERVICE_USER
    TYPE = SERVICE
    DEFAULT_ROLE = HORIZON_MCP_ROLE
    COMMENT = 'Service user for horizon-mcp MCP server';

GRANT ROLE HORIZON_MCP_ROLE TO USER MCP_SERVICE_USER;

-- ── 6. Create an authentication policy scoped to Horizon IRC ────────────────
CREATE AUTHENTICATION POLICY IF NOT EXISTS HORIZON_MCP_AUTH_POLICY
    AUTHENTICATION_METHODS = ('PROGRAMMATIC_ACCESS_TOKEN')
    COMMENT = 'Allow only PAT auth for horizon-mcp service user';

ALTER USER MCP_SERVICE_USER
    SET AUTHENTICATION POLICY HORIZON_MCP_AUTH_POLICY;

-- ── 7. Generate a PAT ───────────────────────────────────────────────────────
-- Log in as MCP_SERVICE_USER and run:
ALTER USER MCP_SERVICE_USER ADD PROGRAMMATIC ACCESS TOKEN HORIZON_MCP_PAT
    ROLE_RESTRICTION = HORIZON_MCP_ROLE
    DAYS_TO_EXPIRY = 31
    COMMENT = 'PAT for horizon-mcp MCP server';

-- Copy the token value — it is shown only once.

# 1. Clone the repository
git clone https://github.com/kshash919/snowflake-horizon-irc-mcp.git
cd snowflake-horizon-irc-mcp

# 2. Create and activate a Python virtual environment
python3 -m venv .venv
source .venv/bin/activate   # Windows WSL2: same command

# 3. Install the package and all dependencies
pip install -e .

# 4. Configure credentials
cp .env.example .env
# Edit .env and fill in all values (see .env.example for field descriptions)

# 5. Verify the connection
python scripts/test_connection.py

── Horizon IRC MCP Connection Test ─────────────────────

  OK  Config loaded
       account  = MYACCOUNT-MYLOCATOR
       database = MYDB
       role     = HORIZON_MCP_ROLE
       backend  = none
  OK  PAT -> bearer token  (length=640)
  OK  Namespaces: ['NORTHMART', 'ANALYTICS']
  OK  Tables in 'NORTHMART': ['ORDERS', 'CUSTOMERS', 'PRODUCTS']
  OK  Schema of NORTHMART.ORDERS: ['order_id', 'customer_id', 'order_date', ...]
  OK  Vended credentials present  keys=['s3.access-key-id', 's3.secret-access-key', ...]
  OK  DuckDB Iceberg secret configured
  OK  Semantic backend: none (live Horizon IRC browsing)

── All checks passed ────────────────────────────────────

{
  "mcpServers": {
    "horizon-iceberg": {
      "command": "wsl",
      "args": [
        "--",
        "/home/<your-wsl-username>/openclaw-workspace/mcp-servers/snowflake/horizon-mcp/.venv/bin/python",
        "-m",
        "horizon_mcp.server"
      ],
      "cwd": "/home/<your-wsl-username>/openclaw-workspace/mcp-servers/snowflake/horizon-mcp"
    }
  }
}

What data do you have access to?

List all the schemas and tables in the catalog.

Show me the first 5 orders.

How many customers are there per country?

What are the 10 most expensive products?

What is total revenue by region for the last 12 months?

What's the refund rate by product category?

Which day of the week has the highest order volume?

Which customer segment has the highest refund rate?

What's the average order value per customer cohort (by signup month)?

Run this SQL:
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value,
    RANK() OVER (ORDER BY SUM(total_amount) DESC) AS ltv_rank
FROM horizonmcpdb.northmart.orders
GROUP BY customer_id
ORDER BY ltv_rank
LIMIT 20

What credentials are you using to read this data? Are the storage keys long-lived?

# Live Horizon IRC browsing (default, no index needed)
SEMANTIC_BACKEND=none

# Local vector search (faster for large catalogs)
SEMANTIC_BACKEND=lancedb

source .venv/bin/activate
horizon-seed

-- Regenerate in Snowflake as MCP_SERVICE_USER
ALTER USER MCP_SERVICE_USER ADD PROGRAMMATIC ACCESS TOKEN HORIZON_MCP_PAT
    ROLE_RESTRICTION = HORIZON_MCP_ROLE
    DAYS_TO_EXPIRY = 31;

-- Check grants
SHOW GRANTS TO ROLE HORIZON_MCP_ROLE;

-- Fix missing grants
GRANT USAGE ON DATABASE MYDB TO ROLE HORIZON_MCP_ROLE;
GRANT USAGE ON SCHEMA MYDB.MYSCHEMA TO ROLE HORIZON_MCP_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA MYDB.MYSCHEMA TO ROLE HORIZON_MCP_ROLE;

# Wrong
SNOWFLAKE_ACCOUNT=myaccount.us-east-1.aws

# Correct
SNOWFLAKE_ACCOUNT=MYORG-MYACCOUNT

DESCRIBE SELECT * FROM horizonmcpdb.MYSCHEMA.MYTABLE LIMIT 0;

horizon-mcp/
├── pyproject.toml                  # Package metadata and dependencies
├── .env.example                    # Environment variable template
├── .gitignore                      # Excludes .env, .venv, __pycache__
├── README.md                       # This file
│
├── scripts/
│   └── test_connection.py          # End-to-end smoke test (run before first use)
│
└── src/
    └── horizon_mcp/
        ├── __init__.py             # Package init
        ├── config.py               # Loads and validates .env into a Config dataclass
        ├── horizon_client.py       # Horizon IRC REST API client (auth + catalog calls)
        ├── duckdb_engine.py        # DuckDB + Iceberg extension query engine
        ├── server.py               # MCP server — 5 tools, FastMCP transport
        ├── seed.py                 # horizon-seed CLI — builds LanceDB index
        ├── nl_to_sql.py            # NL → SQL via OpenAI-compatible API
        ├── semantic.py             # Backend factory (returns none or lancedb backend)
        └── semantic/
            ├── __init__.py         # Semantic subpackage init
            ├── base.py             # Abstract SemanticBackend base class
            ├── none_backend.py     # Live IRC browse + keyword scoring
            └── lancedb_backend.py  # LanceDB vector search backend