tdsql-mcp

An MCP server that turns Teradata Vantage into a full-stack analytics agent platform — giving AI agents not just SQL execution, but a structured, hierarchical knowledge base of Teradata's native function ecosystem.

What This Is

Most database MCP servers provide query execution. This one goes further: it equips agents with the knowledge they need to use Teradata correctly and optimally — not just to run arbitrary SQL, but to reach for the right native distributed function for each step of an analytics workflow.

Teradata Vantage includes hundreds of built-in table operators for ML, statistics, data preparation, text analytics, and vector search. These run distributed across all AMPs in parallel and consistently outperform equivalent hand-written SQL. The challenge for agents is discovery — knowing these functions exist, knowing which one to use, and knowing how to combine them into pipelines.

This server solves that with a structured syntax reference library and an agent guidance architecture that directs models toward native functions at every decision point.

How does it work? See docs/architecture.md for a full explanation of the MCP server, Skill, and combined architectures — including diagrams of all three modes.

Why This Matters

The conventional pattern for AI-assisted analytics looks like this:

Agent pulls data → processes in Python / LLM context → returns result

This works at small scale but collapses under real-world conditions: the data transfer is expensive, the LLM context fills with raw data instead of insights, results are ephemeral and non-reproducible, and nothing produced is reusable at scale.

The in-database approach enabled by this server inverts that model:

Agent orchestrates SQL → analytics execute on the platform → only results returned

Zero Data Movement

Native Teradata table operators execute where the data lives — across all AMPs in parallel. No rows are transferred to a client for processing. Whether the operation is computing summary statistics on a billion-row table, fitting a XGBoost model, or running a vector similarity search, the data stays on the platform. What crosses the wire is the result, not the input.

This isn't just a performance consideration. It eliminates a class of problems: no serialization overhead, no memory pressure on the client, no network bottleneck, no privacy risk from data leaving the platform boundary.

Token and Context Efficiency

When an agent pulls raw data into its context window to analyze it, it burns tokens on data that a SQL function could process in a single pass. A table of a million rows passed to an LLM for statistical analysis is a context window catastrophe. The same analysis via TD_UnivariateStatistics returns a compact result set — a handful of rows — that the agent can reason about directly.

This means the agent's context stays clean: the LLM reasons about results, not raw data. Fewer tokens consumed. Longer, more coherent conversations. More headroom for complex multi-step workflows.

AMP-Parallel Performance and Repeatability

Teradata's native analytics functions are not ordinary SQL — they are massively parallel table operators distributed across the AMP architecture. A TD_XGBoost training call, a TD_KMeans clustering run, or a TD_ColumnTransformer preprocessing pass all execute across every AMP simultaneously. The agent gets the benefit of the platform's full parallel compute without orchestrating any of it.

Repeatability follows naturally from this model. Fit functions persist their learned parameters to named tables. Model training persists model weights. The same TD_ScaleTransform applied with the same ScaleFitTable produces identical output every time — on training data, on new batches, or on a single incoming row. This is not incidental; it is a structural property of the Fit/Transform and Train/Predict patterns built into the library.

From Agent Session to Operational Pipeline

The highest-value outcome of this architecture is not ad-hoc analysis — it is the transition from agent-assisted exploration to a production SQL pipeline that runs independently at any scale and concurrency.

The CTE prediction pipeline pattern captures this directly:

-- This query is a complete operational scoring pipeline.
-- It requires no Python, no client-side processing, no agent at runtime.
-- Any process with database access can call it.
WITH transformed AS (
    SELECT * FROM TD_ColumnTransformer(
        ON db.incoming_data AS InputTable
        ON db.impute_fit    AS ImputeFitTable        DIMENSION
        ON db.encode_fit    AS OneHotEncodingFitTable DIMENSION
        ON db.scale_fit     AS ScaleFitTable          DIMENSION
    ) AS t
)
SELECT * FROM TD_XGBoostPredict(
    ON transformed AS InputTable
    ON db.fraud_model AS ModelTable DIMENSION
    USING IDColumn('txn_id') Accumulate('amount', 'merchant')
) AS dt;

An agent builds and validates this pipeline. Once it works, it is promoted to a view, a stored procedure, or a scheduled job. The agent is no longer in the loop at inference time. The pipeline runs in-situ, at the platform's full scale and concurrency, against whatever volume of incoming data the business requires.

This is the progression this server is designed to enable: exploration → validation → operationalization — entirely within Teradata, without the data ever leaving the platform.

The Syntax Reference Library

The get_syntax_help tool (and the teradata://syntax/{topic} resources) expose a comprehensive, agent-optimized reference library covering the full Teradata Vantage analytics stack.

Coverage

Library Architecture

The library is structured in three layers, matching how agents reason about analytics problems:

guidelines.md          ← "What native function covers this operation?"
                          Canonical mapping of 50+ common operations to native functions.
                          Agents consult this first, before writing any SQL.

index.md               ← Topic directory + Workflows section
                          Maps common use cases (fraud detection, clustering, NLP, etc.)
                          to ordered topic sequences. Agents load topics in the right order.

<topic>.md files       ← Detailed syntax for each function or domain
                          Full argument reference, output schemas, usage patterns,
                          and complete pipeline examples.

An agent tackling a fraud detection problem can navigate this hierarchy:

1. Load guidelines — confirms TD_XGBoost, TD_ScaleFit, TD_SMOTE, TD_ClassificationEvaluator are the right tools

2. Load index — finds the Classification workflow topic sequence

3. Load data-prep, ml-functions, model-evaluation in order

4. Assemble the pipeline using the CTE prediction pipeline pattern from ml-patterns

Pipeline Patterns

A key capability is the CTE prediction pipeline — a fully encapsulated scoring pipeline that applies all preprocessing and scoring in a single SQL query, with no intermediate tables:

WITH transformed AS (
    SELECT id, feat1, feat2, feat3_encoded, feat4_scaled, target_col
    FROM TD_ColumnTransformer(
        ON db.new_data AS InputTable
        ON db.impute_fit     AS ImputeFitTable        DIMENSION
        ON db.encoding_fit   AS OneHotEncodingFitTable DIMENSION
        ON db.scale_fit      AS ScaleFitTable          DIMENSION
    ) AS t
)
SELECT * FROM TD_XGBoostPredict(
    ON transformed AS InputTable
    ON db.my_model AS ModelTable DIMENSION
    USING IDColumn('id') Accumulate('target_col')
) AS dt;

The saved FitTables and model table are built once at training time and reused indefinitely — enabling reproducible, versioned inference pipelines entirely in-database.

Tools

Resources

Configuration

Connection is specified as a single URI via the DATABASE_URI environment variable or --uri CLI flag.

URI format

teradata://user:password@host[:port][/database][?param=value&...]

Environment variables

Common extra parameters

Any teradatasql connection parameter can be appended as a query-string argument:

Multiple parameters example

# LDAP + encryption + full TLS + timeouts
teradata://myuser:mypassword@myhost/mydb?logmech=LDAP&encryptdata=true&sslmode=VERIFY-FULL&logon_timeout=30&connect_timeout=5000

# Default auth + encryption + custom port + session reconnect
teradata://myuser:mypassword@myhost:1025/mydb?encryptdata=true&reconnect_count=5&reconnect_interval=10

# LDAP + ANSI transaction mode + no COP discovery (single-node dev system)
teradata://myuser:mypassword@myhost/mydb?logmech=LDAP&tmode=ANSI&cop=false

See .env.example for more annotated examples.

Two Ways to Use This

You can use either one independently, or both together. When combined, the MCP server handles query execution and the skill loads the native function guidelines at the start of every session.

Setup

Option 1 — MCP Server (SQL execution + syntax library)

Claude Desktop

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "teradata": {
      "command": "uvx",
      "args": ["tdsql-mcp"],
      "env": {
        "DATABASE_URI": "teradata://myuser:mypassword@myhost/mydb"
      }
    }
  }
}

With extra parameters (LDAP auth + encryption):

{
  "mcpServers": {
    "teradata": {
      "command": "uvx",
      "args": ["tdsql-mcp"],
      "env": {
        "DATABASE_URI": "teradata://myuser:mypassword@myhost/mydb?logmech=LDAP&encryptdata=true"
      }
    }
  }
}

Using a local virtual environment (instead of uvx):

{
  "mcpServers": {
    "teradata": {
      "command": "/path/to/tdsql-mcp/.venv/bin/tdsql-mcp",
      "args": [],
      "env": {
        "DATABASE_URI": "teradata://myuser:mypassword@myhost/mydb"
      }
    }
  }
}

Replace /path/to/tdsql-mcp with the absolute path to where you cloned the repo. The .venv/bin/tdsql-mcp script is created automatically when you run pip install -e . inside the venv.

For read-only mode, add --read-only to args:

"args": ["--read-only"]

Claude Code

Add the MCP server to your Claude Code settings (.claude/settings.json or via claude mcp add):

claude mcp add teradata -- uvx tdsql-mcp
# then set DATABASE_URI in your environment or .env file

Or add it manually to .claude/settings.json:

{
  "mcpServers": {
    "teradata": {
      "command": "uvx",
      "args": ["tdsql-mcp"],
      "env": {
        "DATABASE_URI": "teradata://myuser:mypassword@myhost/mydb"
      }
    }
  }
}

Running directly

# Install
pip install tdsql-mcp

# Via environment variable
DATABASE_URI="teradata://me:secret@myhost/mydb" tdsql-mcp

# Via CLI flag
tdsql-mcp --uri "teradata://me:secret@myhost/mydb"

# With extra connection parameters
tdsql-mcp --uri "teradata://me:secret@myhost/mydb?logmech=LDAP&sslmode=VERIFY-FULL"

# Read-only
tdsql-mcp --uri "teradata://me:secret@myhost/mydb" --read-only

Streamable-HTTP transport

The server also supports streamable-http transport for remote or multi-client use — e.g. a shared server on a team machine, or an agent framework that connects over HTTP rather than spawning a subprocess.

# Start in HTTP mode (binds to localhost:8000 by default)
tdsql-mcp --uri "teradata://me:secret@myhost/mydb" --transport streamable-http

# Custom host/port
tdsql-mcp --uri "teradata://me:secret@myhost/mydb" --transport streamable-http --host 0.0.0.0 --port 8080

The MCP endpoint is available at http://<host>:<port>/mcp.

To connect Claude Code to an HTTP transport server, add it to .claude/settings.json:

{
  "mcpServers": {
    "teradata": {
      "type": "http",
      "url": "http://localhost:8000/mcp"
    }
  }
}

Note: The HTTP transport has no built-in authentication. For anything beyond localhost, put the server behind a reverse proxy with appropriate access controls.

Install from source (with virtual environment)

# Clone the repository
git clone https://github.com/ksturgeon-td/tdsql-mcp.git
cd tdsql-mcp

# Create a virtual environment
python3 -m venv .venv

# Activate it
source .venv/bin/activate        # macOS / Linux
# .venv\Scripts\activate         # Windows

# Install in editable mode
# Code and syntax file changes take effect immediately — no reinstall needed
pip install -e .

# Set up your connection credentials
cp .env.example .env
# Edit .env and set DATABASE_URI to your Teradata connection string

# Run the server
tdsql-mcp

To deactivate the virtual environment when you're done:

deactivate

To install from the pinned requirements.txt snapshot instead of resolving fresh dependencies:

pip install -r requirements.txt
pip install -e . --no-deps

Option 2 — Skill (syntax library, no credentials required)

The skill loads the native function guidelines and full syntax topic index into any Claude Code session. No Teradata connection or credentials are required — it's a read-only knowledge library.

What you get: Type /teradata-sql-analytics at the start of a session to load the guidelines and topic index. Claude will check for an available database connection tool and confirm whether it can execute queries or work in SQL-writing-only mode.

Installation — Claude Code (CLI / IDE extensions)

# Clone the repository
git clone https://github.com/ksturgeon-td/tdsql-mcp.git
cd tdsql-mcp

# Install the skill — copies SKILL.md and creates a symlink to the syntax library
mkdir -p ~/.claude/teradata-sql-analytics
cp skills/teradata-sql-analytics/SKILL.md ~/.claude/teradata-sql-analytics/SKILL.md
ln -sf "$(pwd)/src/tdsql_mcp/syntax" ~/.claude/teradata-sql-analytics/syntax

To get syntax library updates later, just git pull in the repo directory — the symlink means your installed skill picks up changes automatically.

If you prefer a standalone copy with no repo dependency:

# Copy all files directly (no ongoing repo dependency)
cp -rL tdsql-mcp/skills/teradata-sql-analytics ~/.claude/teradata-sql-analytics

Installation — Claude Desktop & Claude.ai (ZIP upload)

Skills can be uploaded as a ZIP file via Customize > Skills in both the Claude Desktop app and Claude.ai web app.

Prerequisite: Code execution must be enabled in Settings > Capabilities before Skills become accessible.

Build the ZIP:

# Clone the repository
git clone https://github.com/ksturgeon-td/tdsql-mcp.git
cd tdsql-mcp

# Build a self-contained skill folder (follows symlinks so syntax files are included)
rsync -rL skills/teradata-sql-analytics/ /tmp/teradata-sql-analytics/

# Zip it
cd /tmp && zip -r teradata-sql-analytics.zip teradata-sql-analytics/

Then upload:

1. Open Claude Desktop or Claude.ai

2. Go to Customize > Skills

3. Click + → Upload a skill

4. Select teradata-sql-analytics.zip

The skill will appear in your Skills list and can be toggled on/off. Uploaded skills are private to your account. Enterprise admins can provision skills organization-wide via Organization settings > Skills.

Usage

Claude Code — type this at the start of any session:

/teradata-sql-analytics

Claude Desktop / Claude.ai — enable the skill via Customize > Skills, then start a new conversation. The skill activates automatically.

Claude will load the native function guidelines and topic index, check for a database connection, and confirm it's ready. From there, ask it to write, review, or optimize any Teradata SQL — or load a specific syntax topic with get_syntax_help(topic="...") if you also have the MCP server running.

Recommended project/session instructions

For best results, add the following as project instructions (Claude Desktop: Project > Instructions, Claude.ai: Project > Custom instructions) or paste at the start of a session. This tells the agent to use the skill proactively rather than waiting to be asked:

You are working with a Teradata Vantage database.

IMPORTANT: Always prefer native Teradata table operators over hand-written SQL equivalents.
Teradata Vantage has built-in distributed functions for analytics, ML, data preparation,
text processing, and vector search. These run across all AMPs in parallel and outperform
equivalent hand-written SQL. Do NOT write manual SQL for operations like scaling, encoding,
binning, statistics, clustering, classification, or similarity search when a native function exists.

Before writing any analytics, transformation, or ML SQL:
  (1) use the /teradata-sql-analytics skill (syntax/guidelines.md) to see the canonical
      mapping of common operations to native Teradata functions,
  (2) use the /teradata-sql-analytics skill (syntax/index.md) to discover all available topics,
  (3) load the relevant topic(s) for exact syntax.

Use available MCP tools to explore the schema.

Extending the Syntax Library

The library is designed to grow. To add a new topic:

1. Create src/tdsql_mcp/syntax/<topic>.md

2. Add an entry to index.md

3. Add relevant mappings to guidelines.md

No code changes needed — the tool auto-discovers .md files at call time. Topics planned for future addition include time-series-patterns.md, json-functions.md, and object-store.md (READ_NOS, foreign tables, Iceberg/Delta query patterns).

Result format

execute_query returns:

{
  "rows": [{"col1": "val", "col2": 42}, ...],
  "row_count": 100,
  "truncated": true
}

truncated: true means more rows were available beyond the max_rows limit. Increase max_rows or add a TOP N / WHERE clause to your query.

Notes

• The server establishes a persistent connection on startup and automatically reconnects on failure.

• execute_query defaults to max_rows=100 to keep token usage manageable. Maximum is 10,000.

• Use explain_query before running unfamiliar queries — Teradata's EXPLAIN validates syntax and shows the execution plan without actually running the query.

• All logging goes to stderr; stdout is reserved for the MCP JSON-RPC protocol.