SolidData MCP POC — CrewAI + Solid MCP (Query-Only Demo)

Minimal demonstration of Solid's MCP server with CrewAI:

What it does: You ask a natural-language question → the crew calls Solid’s MCP text2sql tool → Solid returns the generated SQL. Optionally that SQL is executed in Snowflake via the Snowflake Python connector (username/password); the Reporter then analyzes the actual query results. Otherwise the Reporter explains what the SQL does. All output is printed in the terminal.
Snowflake (optional): When Snowflake connector env vars are set, the flow runs the generated SQL in Snowflake using the connector (no PAT, no MCP API, no network policy); the Reporter analyzes the data returned.

Use this repo to see the end-to-end flow (MCP → SQL + analysis) and to publish the Solid MCP tool as a CrewAI custom tool so any agent can use it.

Architecture

Crew flow (with optional Snowflake execution)

SQL Analyst — Uses Solid MCP text2sql to turn the user question into a SQL query and a short explanation.
Snowflake SQL Executor (only when Snowflake is configured) — Takes the SQL from step 1, runs it in Snowflake via the Snowflake Python connector (username/password), and returns the raw query results.
Reporter — If step 2 ran: summarizes the query results and writes a stakeholder report. If step 2 was skipped: explains what the SQL does in plain language.

Question (natural language)
        │
        ▼
┌──────────────────┐   MCP (text2sql)    ┌─────────────────────┐
│  SQL Analyst     │ ──────────────────► │  SolidData MCP       │
│  (Agent 1)       │ ◄────────────────── │  Server             │
└────────┬─────────┘   SQL + explanation └─────────────────────┘
         │
         ▼
┌──────────────────┐   (optional)        ┌─────────────────────┐
│  SQL Executor    │ ──────────────────► │  Snowflake          │
│  (Agent 2)       │   execute SQL       │  Python connector   │
└────────┬─────────┘ ◄────────────────── └─────────────────────┘
         │             query results
         ▼
┌──────────────────┐
│  Reporter        │  → Report on results (or explain the SQL if no Snowflake)
│  (Agent 3)       │
└────────┬─────────┘
         │
         ▼
   Result printed in terminal only

The SQL Analyst connects directly to SolidData’s MCP server via MCPServerHTTP in crew.py: crew.py passes SOLIDDATA_MANAGEMENT_KEY as the x-solid-management-key header on the MCP transport — no token exchange step (not through the Azure REST bridge). The solid_mcp_tool/ folder is the same integration pattern for publishable BaseTools: CrewAI MCPClient + HTTPTransport with x-solid-management-key to Solid’s MCP URL (see Part 2). Use it in AMP or other crews when you want explicit tools instead of attaching MCPServerHTTP to an agent. This demo does not import solid_mcp_tool directly.
Snowflake is used only via the Snowflake Python connector (snowflake_connector_tool.py) with username/password; no Snowflake MCP or PAT. Query results are capped at 1000 rows (configurable on the tool) to keep context manageable.

Testing MCP Connection without Crew

You can test the SolidData MCP connection and credentials in a browser using the official MCP Inspector. No Python or CrewAI required—useful for quick credential and connection checks.

Dependencies

Node.js (includes npm and npx). Not included in this repo.
- Install from nodejs.org or your package manager (e.g. brew install node on macOS).

Run the Inspector

From any directory (no need to be in this repo):

npx --clear-npx-cache && npx @modelcontextprotocol/inspector@latest

A browser window opens. Add an MCP server:

Transport: choose the option that matches Solid’s MCP (e.g. Streamable HTTP if available, or the HTTP/URL option).
URL: your SolidData MCP URL (e.g. https://mcp.production.soliddata.io/mcp; for dev use the dev MCP URL).
Headers: add x-solid-management-key: <your-soliddata-management-key>. No prior Bearer token or auth exchange needed.

Then use the Inspector UI to list tools and call text2sql (or glossary_search) to confirm the connection works before running the full crew.

Part 1: Run the Demo (Terminal Only)

Simplest path: ask a question → see the SQL response from Solid and the agent’s analysis in the terminal.

How It Works

Auth — crew.py passes SOLIDDATA_MANAGEMENT_KEY directly to the MCP transport via the x-solid-management-key header. No token exchange step.
MCP — crew.py creates an MCPServerHTTP client for the SolidData MCP server and attaches it to the SQL Analyst agent (Solid exposes text2sql and glossary_search; the task text tells the agent when to use each).
SQL Analyst — Uses MCP text2sql for data questions or glossary_search for definitions / terminology.
Snowflake Executor (optional) — If Snowflake connector is configured, runs that SQL in Snowflake and returns query results.
Reporter — If Snowflake ran: summarizes the query results and writes a stakeholder report. Otherwise: explains in plain language what the query does.
Output — Result is printed in the terminal only.

1.1 Prerequisites

Python 3.10–3.13 (see pyproject.toml for the exact supported range)
SolidData management key (MCP-enabled)
Google Gemini API key (e.g. Google AI Studio)
Optional: uv

1.2 Setup

From the project root (where pyproject.toml and .env live):

cp .env.example .env
# Edit .env: set SOLIDDATA_MANAGEMENT_KEY and GEMINI_API_KEY (required)

Also set in .env: SEMANTIC_LAYER_ID (required — UUID from the Solid platform). Optional: MODEL, MCP_SERVER_URL (for SolidData dev; default is production).

Snowflake (optional): To run the generated SQL in Snowflake and have the Reporter analyze the data, set in .env: SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_PASSWORD, SNOWFLAKE_WAREHOUSE, SNOWFLAKE_DATABASE, and SNOWFLAKE_SCHEMA (and optionally SNOWFLAKE_ROLE). The app uses the Snowflake Python connector with username/password only—no PAT, no MCP API, no network policy or IP whitelisting. See Snowflake setup.

1.3 Run

With uv:

uv sync
uv run soliddata_mcp_poc "How many users signed up last month?"
# Or: uv run run_crew "Your question here"
# Interactive (prompt for question):
uv run soliddata_mcp_poc

With pip:

python -m venv .venv
source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -e .
soliddata_mcp_poc "How many users signed up last month?"
# Or: run_crew "Your question here"

1.4 What You See

Crew runs: SQL Analyst calls Solid MCP text2sql; if Snowflake connector is configured, the executor runs the SQL in Snowflake and the Reporter summarizes the results; otherwise the Reporter explains what the query does.
Result is printed in the terminal only (no file output).

Part 3: Multi-Model Crew (Router + Aggregation)

For questions that span multiple semantic models (e.g. Marketing: paid media + web analytics + CRM), use the separate CLI in solid_multi_model_crew/:

uv run solid_multi_model_crew --mode both "How did Q1 campaigns perform across paid and organic channels?"

--mode router — one text2sql with all semantic_layer_ids; Solid router picks the best model.
--mode multi — planner decomposes the question, queries each model separately, aggregates analysis.
--mode both (default) — runs router baseline then multi-model synthesis.

Configure models via MULTI_MODEL_CONFIG (YAML) or SEMANTIC_LAYER_IDS in .env. See solid_multi_model_crew/README.md.

Part 2: Solid MCP as a CrewAI Custom Tool

CrewAI + Solid (this repo): pass SOLIDDATA_MANAGEMENT_KEY as the x-solid-management-key header and connect directly to Solid’s MCP HTTP endpoint. Part 1 does that with MCPServerHTTP on an agent; solid_mcp_tool does the same with MCPClient + HTTPTransport inside BaseTool implementations — no Azure REST-to-MCP bridge. The bridge is only for REST/OpenAPI consumers (Using the OpenAPI spec).

The solid_mcp_tool folder is optional publishable tools for CrewAI Enterprise (AMP) or other crews: use them when you want explicit solid_* tools instead of wiring MCPServerHTTP on an agent.

2.1 What’s in `solid_mcp_tool/`

tool.py — Self-contained: direct streamable HTTP MCP (MCPClient / HTTPTransport with x-solid-management-key, same idea as Part 1). Defines four BaseTool classes — one per Solid MCP tool. Declares env_vars so CrewAI Enterprise (AMP) injects secrets at runtime.
README.md — Usage, env vars, publish instructions, and AMP deployment notes.

CrewAI tool	Class	MCP tool
`solid_text2sql`	`SolidMcpTool`	`text2sql`
`solid_glossary_search`	`SolidGlossarySearchTool`	`glossary_search`
`solid_specific_asset_information`	`SolidSpecificAssetInformationTool`	`specific_asset_information_tool`
`solid_semantic_model_qa`	`SolidSemanticModelQATool`	`semantic_model_qa`

2.2 How it works (tool flow — direct MCP `BaseTool`s)

Agent sends arguments to the appropriate solid_* tool (see table below).
Tool reads env fallbacks when args are omitted (SEMANTIC_LAYER_ID, SEMANTIC_MODEL_ID, ASSET_NAME, etc.).
Tool opens a short-lived MCP session to MCP_SERVER_URL with x-solid-management-key, calls the matching MCP tool, then disconnects.
Returns the tool result text from the MCP server.

CrewAI tool selection (direct MCP — not the bridge):

User intent	CrewAI tool	MCP tool	MCP arguments
Generate SQL from a data question	`solid_text2sql`	`text2sql`	`question`, `semantic_layer_ids`
Look up a term or acronym	`solid_glossary_search`	`glossary_search`	`query`
Ask about a specific table or dashboard	`solid_specific_asset_information`	`specific_asset_information_tool`	`question`, `asset_name`, optional `asset_type`
Ask about semantic model metadata	`solid_semantic_model_qa`	`semantic_model_qa`	`question`, `semantic_model_id`

2.3 Environment variables (tool)

Variable	Required	Description
`SOLIDDATA_MANAGEMENT_KEY`	Yes (all tools)	SolidData management key with MCP access (passed as `x-solid-management-key` header).
`MCP_SERVER_URL`	No	Solid MCP HTTP URL. Default: production (same as Part 1 `MCP_SERVER_URL`).
`SEMANTIC_LAYER_ID`	Yes for text2sql	UUID of the semantic layer (passed to MCP as `semantic_layer_ids`).
`SEMANTIC_MODEL_ID`	Yes for semantic_model_qa (unless passed as arg)	UUID of the semantic model.
`ASSET_NAME`	Yes for specific_asset_information (unless passed as arg)	Default table or dashboard name.
`ASSET_TYPE`	No	Optional asset type hint (e.g. `table`, `dashboard`).

In CrewAI Enterprise, set these in the tool configuration in Crew Studio. The tool class declares them via env_vars so AMP injects them into os.environ before _run executes.

2.4 Publish the Tool to CrewAI (CLI)

Do these steps in a normal terminal, in a new directory.

Log in to CrewAI
```
crewai login
```
Create the tool project
```
crewai tool create solid_mcp_tool
```
Replace the scaffold tool.py
Copy the entire contents of this repo's solid_mcp_tool/tool.py into the new project's tool.py.
Update pyproject.toml
- Set name, version, description.
- Increment version for every publish.
- Ensure dependencies include: crewai, pydantic, nest-asyncio.

Commit and publish

git add .
git commit -m "Solid MCP tools (text2sql, glossary, asset info, semantic model QA)"
crewai tool publish

Use crewai tool publish --public for a public tool.

After publishing, install with crewai tool install <tool-name>. Set SOLIDDATA_MANAGEMENT_KEY in the project or in CrewAI AMP tool config, plus tool-specific vars (SEMANTIC_LAYER_ID, SEMANTIC_MODEL_ID, ASSET_NAME, etc.) as needed.

Using the OpenAPI spec (Workato, Power Platform, etc.)

If your agent or platform only supports HTTP/REST with a Swagger or OpenAPI spec (no native MCP or Python SDK), use the root openapi.yaml to call Solid MCP tools through the Azure REST-to-MCP bridge.

CrewAI and direct MCP (this repo’s Part 1 and Part 2) do not use this spec: they call Solid’s MCP URL directly with x-solid-management-key. The OpenAPI file is only for REST/OpenAPI consumers (Workato, Copilot Studio, Logic Apps, Postman, etc.) that cannot consume SSE streaming.

This OpenAPI path applies to:

Workato (custom connector)
Microsoft Power Platform / Copilot Studio (custom connector or HTTP action)
Logic Apps, n8n, or other automation tools that consume OpenAPI
API testers (e.g. apinotes.io, Postman) to validate the contract

What’s in the spec (v2.0.0)

The spec defines one server (the Azure bridge base URL) and four POST operations. Every operation uses the same pattern: management_key plus tool-specific fields in the JSON body—no separate auth call and no Bearer token on the bridge.

Path	MCP tool	Success response field
POST /text2sql	`text2sql`	`message` (SQL + explanation, often markdown)
POST /glossary_search	`glossary_search`	`result` (e.g. `synthesized_answer`, `answer_status`)
POST /specific_asset_information_tool	`specific_asset_information_tool`	`result` (asset metadata + natural-language answer)
POST /semantic_model_qa	`semantic_model_qa`	`result` (semantic model Q&A payload)

On every call the bridge forwards management_key directly to Solid's MCP server as x-solid-management-key. No token exchange occurs. Callers only store the Solid management key.

The code query parameter is the Azure Function host key (Portal → Function App → App keys → _master or default). The same host key applies to every path (pre-filled in openapi.yaml). Function-specific keys only work on one route and return 401 on others. For local E2E, set BRIDGE_FUNCTION_KEY in .env or let scripts/e2e_openapi_test.py read the default from openapi.yaml via scripts/bridge_openapi.py.

Flow for each request

For every bridge operation:

Send one POST to {bridge_base}/{path} (e.g. …/api/mcp/text2sql) with JSON containing management_key and the fields required for that tool.
Include the code query parameter (importing openapi.yaml into Workato/Copilot Studio applies the spec default automatically).

No auth endpoint call and no Authorization: Bearer header to the bridge.

Example request bodies

Replace management_key with your Solid key. Use your own UUIDs for semantic layers and models where applicable.

text2sql

{
  "management_key": "YOUR-SOLID-MGMT-KEY-HERE",
  "question": "What were the top 5 products in terms of revenue?",
  "semantic_layer_ids": ["998b655a-75eb-4873-bb1e-3ddd23164065"]
}

glossary_search

{
  "management_key": "YOUR-SOLID-MGMT-KEY-HERE",
  "query": "What does LLS mean?"
}

specific_asset_information_tool

{
  "management_key": "YOUR-SOLID-MGMT-KEY-HERE",
  "question": "What column includes information about when an order was delivered?",
  "asset_name": "SUN_SPECTRA.PUBLIC.ORDERS"
}

Optional: "asset_type": "table" (or dashboard).

semantic_model_qa

{
  "management_key": "YOUR-SOLID-MGMT-KEY-HERE",
  "semantic_model_id": "00000000-0000-0000-0000-000000000000",
  "question": "What does this model cover?"
}

Direct MCP (CrewAI / Inspector — not the bridge)
MCP tool calls use tool arguments only—no management_key in the tool payload (auth is via the x-solid-management-key header). Examples:

text2sql

{
  "question": "How many users signed up last month?",
  "semantic_layer_ids": ["00000000-0000-0000-0000-000000000000"]
}

glossary_search

{
  "query": "What does LLS mean?"
}

specific_asset_information_tool

{
  "question": "What column includes information about when an order was delivered?",
  "asset_name": "SUN_SPECTRA.PUBLIC.ORDERS"
}

semantic_model_qa

{
  "question": "What does this model cover?",
  "semantic_model_id": "00000000-0000-0000-0000-000000000000"
}

OpenAPI YAML spec walkthrough

This section walks through openapi.yaml so you can use it in Workato, Copilot Studio, or similar.

openapi and info — OpenAPI 3.0.3, title Solid MCP Bridge, version 2.0.0. info.description lists all four supported tools and the single-call model (management_key in body; bridge forwards it as a header). No two-step auth for bridge callers.
servers — Single bridge base (e.g. https://…azurewebsites.net/api/mcp). Paths are relative: …/text2sql, …/glossary_search, …/specific_asset_information_tool, …/semantic_model_qa.
paths — Each operation is POST only, security: [], optional code query param (same host key default on every path), required application/json body with management_key, and shared error responses 400, 401, 405, 502.
components/schemas — Request/response types per tool (Text2SqlRequest → message; others → result). ErrorResponse has required error string.

How Workato (or similar) uses this: Import openapi.yaml → four operations appear → store management_key as a connection secret → one POST per action with management_key plus the fields for that operation. No token handling on the bridge.

How to use it

Workato: Import the spec; map management_key from the connection into each action body; pass tool-specific fields (question / semantic_layer_ids, query, asset_name, semantic_model_id, etc.).
Power Platform / Copilot Studio: Custom connector or HTTP action per path; same single POST body shape.
API testers / CI: Import openapi.yaml or run python scripts/e2e_openapi_test.py (defaults to text2sql; set BRIDGE_TOOL=glossary_search etc.). Bridge URL and code resolve from .env or from openapi.yaml (see scripts/bridge_openapi.py).

The root openapi.yaml is the source of truth for bridge URLs, request/response shapes, and the Azure host code default for REST/OpenAPI clients.

Project Structure

solid-mcp-poc/                  # Repo root
├── .env.example
├── pyproject.toml
├── README.md
├── uv.lock
├── openapi.yaml                # OpenAPI 3.0 Azure bridge (4 tools, single-call auth); see "Using the OpenAPI spec" above
├── scripts/
│   ├── bridge_openapi.py       # Read bridge base URL / code default from openapi.yaml
│   └── e2e_openapi_test.py     # E2E: single POST per bridge tool
├── solid_mcp_tool/             # Standalone CrewAI custom tool (publish separately; not used by this demo’s crew)
│   ├── __init__.py
│   ├── tool.py                 # Self-contained: MCP call + env_vars for AMP injection
│   └── README.md
├── solid_multi_model_crew/     # Multi-model CLI: router mode + per-model aggregation (see README)
│   ├── main.py
│   ├── crew.py
│   ├── tools.py
│   ├── config.py
│   ├── models.py
│   ├── marketing_models.example.yaml
│   └── README.md
└── src/
    └── soliddata_mcp_poc/      # Demo app: MCP crew → terminal output
        ├── __init__.py
        ├── main.py             # Entry: crew → print result
        ├── config.py           # Settings from .env
        ├── crew.py             # Crew: SQL Analyst (MCP text2sql) → [Snowflake Executor] → Reporter
        └── snowflake_connector_tool.py  # Snowflake SQL via connector (username/password; max 1000 rows)

No file output; no config/ YAML (agents/tasks are in code). Entry points: soliddata_mcp_poc, run_crew, and solid_multi_model_crew (see pyproject.toml).

REST bridge (Workato, Copilot Studio, other agents): An Azure Function App exposes Solid MCP tools as REST (text2sql, glossary_search, specific_asset_information_tool, semantic_model_qa). Use it when the consumer only supports HTTP/OpenAPI. The root openapi.yaml documents the bridge base URL, paths, bodies, and the shared host code default. See Using the OpenAPI spec.

Snowflake setup

Snowflake is used only via the Snowflake Python connector with username and password. No PAT, no Snowflake MCP API, and no network policy or IP whitelisting is required.

In .env set:

SNOWFLAKE_ACCOUNT — e.g. xy12345.us-east-1 (see Account identifiers)
SNOWFLAKE_USER — your Snowflake user
SNOWFLAKE_PASSWORD — your password
SNOWFLAKE_WAREHOUSE — warehouse to use
SNOWFLAKE_DATABASE — database to use
SNOWFLAKE_SCHEMA — schema to use
SNOWFLAKE_ROLE — (optional) role to use

When all of the required vars are set, the crew runs the generated SQL in Snowflake and the Reporter analyzes the results. If any are missing, the crew skips the Snowflake step and the Reporter only explains what the SQL does. The Snowflake tool returns at most 1000 rows per query (configurable via the tool’s max_rows when instantiating it in code).

Troubleshooting

MCP connection failed / HTTP 500
A missing or invalid SOLIDDATA_MANAGEMENT_KEY often returns HTTP 500 from the MCP endpoint (not 401). Verify the key is set correctly and has MCP access. Use the correct MCP_SERVER_URL (prod vs dev).
Missing or placeholder key
Set real SOLIDDATA_MANAGEMENT_KEY, GEMINI_API_KEY, and SEMANTIC_LAYER_ID in .env.
Tool returns 'question' or empty result in AMP
The tool's env_vars must be configured in CrewAI Enterprise tool config so AMP injects SOLIDDATA_MANAGEMENT_KEY and SEMANTIC_LAYER_ID into os.environ. Without this, the tool can't authenticate or pass the semantic layer ID. After changing tool config, republish the tool so AMP picks up the latest version.
ImportError: cannot import name 'SolidMcpTool'
The deployed package on AMP is stale. Republish the tool with an incremented version.
Snowflake step not running
Snowflake runs only when all of these are set in .env: SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_PASSWORD, SNOWFLAKE_WAREHOUSE, SNOWFLAKE_DATABASE, SNOWFLAKE_SCHEMA. If any are missing, the crew uses the two-task flow (SQL + report on the query only).
"Invalid response from LLM call - None or empty"
This can occur when the LLM (e.g. Gemini) returns an empty response after a tool run. The crew retries the task automatically. To reduce how often it happens, the Snowflake SQL Executor uses a lower temperature and explicit max_tokens; the Snowflake tool also caps results at 1000 rows so context stays manageable. If it persists, check API rate limits and try again.

Navigation Menu

Uh oh!

Repository files navigation

SolidData MCP POC — CrewAI + Solid MCP (Query-Only Demo)

Table of Contents

Architecture

Crew flow (with optional Snowflake execution)

Testing MCP Connection without Crew

Dependencies

Run the Inspector

Part 1: Run the Demo (Terminal Only)

How It Works

1.1 Prerequisites

1.2 Setup

1.3 Run

1.4 What You See

Part 3: Multi-Model Crew (Router + Aggregation)

Part 2: Solid MCP as a CrewAI Custom Tool

2.1 What’s in `solid_mcp_tool/`

2.2 How it works (tool flow — direct MCP `BaseTool`s)

2.3 Environment variables (tool)

2.4 Publish the Tool to CrewAI (CLI)

Using the OpenAPI spec (Workato, Power Platform, etc.)

What’s in the spec (v2.0.0)

Flow for each request

Example request bodies

OpenAPI YAML spec walkthrough

How to use it

Project Structure

Snowflake setup

Troubleshooting

References

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
scripts		scripts
solid_mcp_tool		solid_mcp_tool
solid_multi_model_crew		solid_multi_model_crew
src		src
.DS_Store		.DS_Store
.env.example		.env.example
.gitignore		.gitignore
AGENTIC_FRAMEWORK_TEMPLATE.md		AGENTIC_FRAMEWORK_TEMPLATE.md
README.md		README.md
openapi.yaml		openapi.yaml
pyproject.toml		pyproject.toml
uv.lock		uv.lock

Uh oh!

Latest commit

History

Folders and files

Repository files navigation

SolidData MCP POC — CrewAI + Solid MCP (Query-Only Demo)

Table of Contents

Architecture

Crew flow (with optional Snowflake execution)

Testing MCP Connection without Crew

Dependencies

Run the Inspector

Part 1: Run the Demo (Terminal Only)

How It Works

1.1 Prerequisites

1.2 Setup

1.3 Run

1.4 What You See

Part 3: Multi-Model Crew (Router + Aggregation)

Part 2: Solid MCP as a CrewAI Custom Tool

2.1 What’s in solid_mcp_tool/

2.2 How it works (tool flow — direct MCP BaseTools)

2.3 Environment variables (tool)

2.4 Publish the Tool to CrewAI (CLI)

Using the OpenAPI spec (Workato, Power Platform, etc.)

What’s in the spec (v2.0.0)

Flow for each request

Example request bodies

OpenAPI YAML spec walkthrough

How to use it

Project Structure

Snowflake setup

Troubleshooting

References

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

2.1 What’s in `solid_mcp_tool/`

2.2 How it works (tool flow — direct MCP `BaseTool`s)