Skip to main content
Glama
MCP Analytics Server

MCP Analytics Server

by Jojeda96

MCP Analytics Server

Python SDK Database Validation Code Style Type Checked Spec-Driven License: MIT

A production-grade Model Context Protocol (MCP) server built in Python that exposes typed, deterministic, and security-guarded analytical tools over a business dataset stored in DuckDB.

An external AI agent (e.g. GPT through the OpenAI Agents SDK, Claude Desktop, or Cursor) can dynamically discover and execute analytical queries without needing direct database access or running unconstrained SQL.


โœจ Key Highlights

  • Python-First MCP Server: Fully compliant with the official Model Context Protocol standard over stdio.

  • Model-Agnostic Architecture: The server contains no LLM inside. It exposes clean, deterministic tool contracts that any MCP-compatible agent can invoke.

  • Embedded Columnar Analytics: Powered by DuckDB for fast, efficient columnar aggregations on normalized enterprise data.

  • AST-Based SQL Guard: Uses sqlglot to parse and validate ad-hoc queries, strictly allowing read-only SELECT statements and eliminating SQL injection or mutation risks.

  • Strict Typed Contracts: All responses are validated through Pydantic v2 models before reaching the client.

  • Interactive GPT Demo Client: Out-of-the-box demonstration agent leveraging the OpenAI Agents SDK and evidence-based reasoning prompts.

  • Spec-Driven Development: Engineered incrementally using OpenSpec for complete requirements traceability.


Related MCP server: databricks-mcp

๐Ÿ›๏ธ System Architecture

flowchart TD
    User([User]) <--> Agent[GPT Agent / OpenAI Agents SDK]
    Agent <-->|MCP Protocol / stdio| Server[MCP Analytics Server]

    subgraph Server_Internal [MCP Analytics Server Boundary]
        Server --> Tools[Tool Layer]
        Tools --> DataTools[Dataset Tools]
        Tools --> ChurnTools[Churn Analytics Tools]
        Tools --> SQLTool[Read-Only SQL Tool]

        SQLTool --> SQLGuard[SQL Guard Security Layer]
        DataTools --> AnalyticsSvc[AnalyticsService]
        ChurnTools --> AnalyticsSvc
        SQLGuard --> DBSvc[DatabaseService]
        AnalyticsSvc --> DBSvc

        DBSvc --> DuckDB[(DuckDB)]
    end

    DuckDB --> Table[(customers Table - Telco Dataset)]

๐Ÿ›ก๏ธ Safe SQL Execution & Security Boundaries

Any SQL input received from an AI agent is treated as untrusted input. The server enforces strict AST validation via sqlglot before query execution:

Allowed Operations:
  โœ… SELECT contract, AVG(monthly_charges) FROM customers GROUP BY contract
  โœ… WITH cohorts AS (SELECT * FROM customers WHERE tenure > 24) SELECT COUNT(*) FROM cohorts

Blocked Operations:
  โŒ DELETE FROM customers WHERE churn = true        (Mutation Rejected)
  โŒ DROP TABLE customers                             (DDL Rejected)
  โŒ SELECT * FROM customers; DROP TABLE customers    (Multi-statement Rejected)
  โŒ ATTACH 'external.db'                             (Engine I/O Rejected)
  • Row Limit Guard: Ad-hoc queries are capped at MAX_RESULT_ROWS = 100 to protect the agent's context window.

  • Table Allowlists: Only authorized analytics tables (customers) can be queried.


๐Ÿงฐ MCP Tools Catalog

Tool Name

Purpose

Key Parameters

Return Type

get_dataset_info

High-level dataset metadata, row and column counts, primary table name, target variable.

None

DatasetInfo

list_columns

Schema inspection returning all available columns and their database data types.

None

list[ColumnInfo]

describe_column

Statistical metrics (min, max, mean, median) for numeric columns, or category distributions for categorical columns.

column: str

NumericColumnDescription / CategoricalColumnDescription

get_churn_summary

Overall customer count, churned count, retained count, and historical churn rate in [0.0, 1.0].

None

ChurnSummary

get_churn_by_dimension

Segmented churn metrics grouped by an approved dimension (contract, internet_service, payment_method, etc.).

dimension: str

DimensionChurnResult

run_readonly_sql

Guarded analytical SQL execution for complex custom calculations not covered by standard tools.

query: str

SQLResult


๐Ÿš€ Quickstart Guide

1. Prerequisites

  • Python 3.11+

  • Git

2. Installation

# Clone repository
git clone https://github.com/Jojeda96/mcp-analytics-server.git
cd mcp-analytics-server

# Create and activate virtual environment
python -m venv .venv
source .venv/bin/activate  # On Windows: .\.venv\Scripts\Activate.ps1

# Install in editable mode with development tools
pip install -e ".[dev]"

3. Build Analytics Database

# Ingest raw Telco CSV, validate schema, normalize, and build DuckDB
python scripts/build_database.py

4. Run the MCP Server

# Run server standalone over stdio
mcp-analytics
# or
python -m mcp_analytics.server

5. Run the Interactive GPT Demo Client

Configure your OpenAI API key in .env:

cp .env.example .env
# Edit .env and set OPENAI_API_KEY=sk-...

Run the interactive demo:

# Interactive REPL mode
python client/gpt_demo.py

# Or evaluate all 10 standard demonstration questions in batch
python client/gpt_demo.py --all-examples

๐Ÿ”Œ Connecting to MCP Clients

Claude Desktop / Cursor

Add the following configuration to your claude_desktop_config.json or Cursor MCP settings:

{
  "mcpServers": {
    "telco-analytics": {
      "command": "python",
      "args": ["-m", "mcp_analytics.server"],
      "cwd": "/absolute/path/to/mcp-analytics-server",
      "env": {
        "DUCKDB_PATH": "data/processed/telco.duckdb",
        "LOG_LEVEL": "INFO",
        "MAX_RESULT_ROWS": "100"
      }
    }
  }
}

๐Ÿงช Testing & Quality Assurance

# Run complete test suite (Unit & Integration) with coverage
pytest --cov=src --cov-report=term-missing

# Run Ruff linter and formatter checks
ruff check .
ruff format --check .

# Run static type checking
mypy src client scripts tests

๐Ÿ“ Development Workflow (OpenSpec)

This project was developed following Spec-Driven Development (SDD) with OpenSpec. Every capability is tracked through explicit proposals, delta specs, design documents, and verifiable tasks:

openspec/
โ”œโ”€โ”€ specs/                          # Consolidated capabilities
โ”‚   โ”œโ”€โ”€ project-foundation/
โ”‚   โ”œโ”€โ”€ telco-data-foundation/
โ”‚   โ”œโ”€โ”€ core-analytics-service/
โ”‚   โ”œโ”€โ”€ core-mcp-tools/
โ”‚   โ”œโ”€โ”€ safe-readonly-sql-tool/
โ”‚   โ”œโ”€โ”€ openai-gpt-demo-client/
โ”‚   โ””โ”€โ”€ portfolio-hardening/
โ””โ”€โ”€ changes/archive/                # Historical change audit trail

๐Ÿ“‚ Project Structure

mcp-analytics-server/
โ”œโ”€โ”€ .github/workflows/ci.yml       # GitHub Actions CI matrix pipeline
โ”œโ”€โ”€ assets/                        # Diagrams and visual assets
โ”œโ”€โ”€ client/
โ”‚   โ””โ”€โ”€ gpt_demo.py                # Interactive OpenAI Agents SDK demo client
โ”œโ”€โ”€ data/
โ”‚   โ”œโ”€โ”€ raw/                       # Source CSV files
โ”‚   โ””โ”€โ”€ processed/                 # Generated DuckDB database
โ”œโ”€โ”€ docs/
โ”‚   โ”œโ”€โ”€ architecture.md            # Deep-dive architecture and layers
โ”‚   โ”œโ”€โ”€ security.md                # Threat model and AST SQL Guard details
โ”‚   โ””โ”€โ”€ decisions.md               # Architecture Decision Records (ADRs)
โ”œโ”€โ”€ examples/
โ”‚   โ”œโ”€โ”€ questions.md               # 10 evaluated demo business questions
โ”‚   โ””โ”€โ”€ mcp-config.example.json    # Standard client configuration
โ”œโ”€โ”€ scripts/
โ”‚   โ”œโ”€โ”€ download_dataset.py        # Dataset provenance & download instructions
โ”‚   โ”œโ”€โ”€ validate_dataset.py        # Strict raw data schema & domain validator
โ”‚   โ””โ”€โ”€ build_database.py          # Data cleaner and DuckDB table builder
โ”œโ”€โ”€ src/mcp_analytics/
โ”‚   โ”œโ”€โ”€ config.py                  # Pydantic Settings and environment config
โ”‚   โ”œโ”€โ”€ errors.py                  # Domain exception hierarchy
โ”‚   โ”œโ”€โ”€ server.py                  # MCP server lifecycle and CLI entrypoint
โ”‚   โ”œโ”€โ”€ schemas/                   # Pydantic response models
โ”‚   โ”œโ”€โ”€ security/                  # AST SQLGuard parser
โ”‚   โ”œโ”€โ”€ services/                  # DatabaseService & AnalyticsService
โ”‚   โ””โ”€โ”€ tools/                     # Dataset, Analytics & SQL MCP tools
โ”œโ”€โ”€ tests/
โ”‚   โ”œโ”€โ”€ fixtures/                  # Curated sample CSV test fixtures
โ”‚   โ”œโ”€โ”€ unit/                      # Fast unit tests for logic and security
โ”‚   โ””โ”€โ”€ integration/               # Database and MCP tool integration tests
โ”œโ”€โ”€ Dockerfile                     # Containerization recipe
โ”œโ”€โ”€ pyproject.toml                 # Package definition & tool configs
โ”œโ”€โ”€ CHANGELOG.md                   # Version release notes
โ”œโ”€โ”€ LICENSE                        # MIT License
โ””โ”€โ”€ README.md

๐Ÿ“„ License

This project is licensed under the MIT License โ€” see the LICENSE file for details.

Available Tools

6 tools
describe_columnB

Provides statistical summaries for a specified column in the customers table. For numeric columns, returns min, max, mean, median, and null count. For categorical columns, returns unique category count and top frequent categories.

Inputs: column (required). Returns: object

get_churn_by_dimensionA

Calculates customer count, churned customer count, and historical churn rate grouped by one approved categorical dimension such as contract, internet_service, payment_method, paperless_billing, tech_support, online_security, etc.

Inputs: dimension (required). Returns: object

get_churn_summaryA

Calculates overall customer count, churned customer count, retained customer count, and historical churn rate across the entire Telco dataset. Churn rate is returned as a ratio between 0.0 and 1.0.

Inputs: no named parameters. Returns: object

get_dataset_infoA

Returns high-level metadata for the Telco Customer Churn dataset, including the available analytics table, row count, column count and target variable. Use this tool before deeper analysis when dataset context is unknown.

Inputs: no named parameters. Returns: object

list_columnsA

Lists all available columns in the customers dataset along with their data types. Use this tool to discover available fields before running specific column queries.

Inputs: no named parameters. Returns: result

run_readonly_sqlA

Executes one read-only analytical SQL query against the customers dataset. Use this tool only when the predefined analytics tools cannot answer the requested question. Mutation, DDL and multi-statement queries are strictly rejected by security boundaries. Results are capped at a maximum of 100 rows.

Inputs: query (required). Returns: object

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelogโ€ฆ

    TDQS

    A4.1/5.0

    Scored across 6 tools

    Disambiguation5/5

    Each tool serves a distinct purpose: dataset overview, column discovery, column statistics, overall churn summary, grouped churn analysis, and custom SQL fallback. The overlap between churn summary and churn by dimension is clearly differentiated by the latter's grouping parameter, and SQL is explicitly a last resort. No ambiguity remains for the agent.

    Naming Consistency5/5

    All tools follow a consistent verb_noun snake_case pattern: get_dataset_info, list_columns, describe_column, get_churn_summary, get_churn_by_dimension, run_readonly_sql. The verbs (get, list, describe, run) and noun phrases (dataset_info, columns, column, churn_summary, etc.) are uniform, creating a predictable and readable API surface.

    Tool Count5/5

    With 6 tools, the server is well-scoped for its purpose of analyzing a single churn dataset. Each tool addresses a distinct analytical need, and none feel redundant or extraneous. The count sits comfortably within the ideal 3-15 range.

    Completeness5/5

    The tool set covers the full analytics lifecycle: discovery (dataset info, columns), exploration (describe column), summary statistics (churn summary), dimension breakdowns (churn by dimension), and arbitrary ad-hoc queries (SQL fallback). The inclusion of read-only SQL ensures no analytical question remains unanswered, making the surface effectively complete for a read-only analytics server.

    Maintenance

    ActivityInactive
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      B
      quality
      D
      maintenance
      Enables LLMs to interact with DuckDB databases through MCP tools for SQL queries, table management, data import/export, and schema inspection, with optional read-only mode for safety.
      12
      MIT
    • F
      license
      Not graded
      quality
      B
      maintenance
      Enables AI assistants and MCP clients to ask natural-language questions about DuckDB or CSV data and receive safe, read-only SQL-generated tabular insights with automatic schema discovery and multi-table joins.
      1
      -
    • F
      license
      Not graded
      quality
      A
      maintenance
      Enables MCP clients and LLMs to interact with a DuckDB database through SQL query execution, table creation, and schema inspection, with optional read-only operation.
      -