dbt MCP Server

# dbt MCP Server API Reference **Version**: 1.0.0 **Protocol**: MCP 1.0 **Server**: dbt-data-stack Complete API reference for the dbt Model Context Protocol (MCP) server, providing Claude Code with intelligent dbt project assistance capabilities. ## Table of Contents - [Overview](#overview) - [Server Configuration](#server-configuration) - [Tool Categories](#tool-categories) - [CLI Tools](#cli-tools) - [Discovery Tools](#discovery-tools) - [Remote Tools](#remote-tools) - [Resources](#resources) - [Error Handling](#error-handling) - [Examples](#examples) ## Overview The dbt MCP server exposes dbt project functionality through the Model Context Protocol, enabling Claude Code to perform intelligent data transformation assistance. The server provides three main categories of tools: CLI operations, project discovery, and remote database access. ### Architecture ``` Claude Code → MCP Protocol → dbt MCP Server → {dbt Core, DuckDB, Local Project} ``` ### Server Information | Property | Value | |----------|-------| | **Server Name** | `dbt-data-stack` | | **Protocol Version** | MCP 1.0 | | **Server Version** | 1.0.0 | | **Async Support** | ✅ Full async/await | | **Type Safety** | ✅ Pydantic models | ## Server Configuration ### Environment Variables | Variable | Type | Default | Description | |----------|------|---------|-------------| | `DBT_PROJECT_DIR` | Path | `./transform` | Absolute path to dbt project directory | | `DBT_PROFILES_DIR` | Path | `./transform/profiles/duckdb` | Absolute path to dbt profiles directory | | `DBT_PROFILE` | String | `data_stack` | dbt profile name | | `DBT_TARGET` | String | `dev` | dbt target environment | | `DUCKDB_PATH` | Path | `./data/warehouse/data_stack.duckdb` | DuckDB database file path | | `DUCKDB_SCHEMA` | String | `main` | Default database schema | ### Tool Group Configuration | Variable | Type | Default | Description | |----------|------|---------|-------------| | `DBT_MCP_ENABLE_CLI_TOOLS` | Boolean | `true` | Enable dbt CLI operations | | `DBT_MCP_ENABLE_DISCOVERY_TOOLS` | Boolean | `true` | Enable project discovery tools | | `DBT_MCP_ENABLE_SEMANTIC_LAYER_TOOLS` | Boolean | `false` | Enable semantic layer tools (future) | | `DBT_MCP_ENABLE_REMOTE_TOOLS` | Boolean | `true` | Enable database query tools | ### Logging Configuration | Variable | Type | Default | Description | |----------|------|---------|-------------| | `DBT_LOG_LEVEL` | String | `info` | dbt logging level | | `MCP_DEBUG` | Boolean | `false` | Enable debug logging | | `DBT_WARN_ERROR_OPTIONS` | String | `all` | dbt warning treatment | ## Tool Categories The server exposes tools across three main categories, each configurable via environment variables. ### Tool Category Matrix | Category | Tools Count | Environment Variable | Description | |----------|-------------|---------------------|-------------| | **CLI Tools** | 4 | `DBT_MCP_ENABLE_CLI_TOOLS` | Direct dbt command execution | | **Discovery Tools** | 3 | `DBT_MCP_ENABLE_DISCOVERY_TOOLS` | Project metadata and discovery | | **Remote Tools** | 2 | `DBT_MCP_ENABLE_REMOTE_TOOLS` | Database query and exploration | | **Total** | 9 | - | All available tools | ## CLI Tools Execute dbt commands directly through the MCP interface with proper error handling and logging. ### `dbt_run` Execute dbt models to build data transformations. **Tool Schema:** ```json { "name": "dbt_run", "description": "Execute dbt models to build data transformations", "inputSchema": { "type": "object", "properties": { "models": { "type": "string", "description": "Specific models to run (optional, space-separated)" }, "full_refresh": { "type": "boolean", "description": "Perform full refresh of incremental models", "default": false } } } } ``` **Example Request:** ```json { "tool": "dbt_run", "arguments": { "models": "stg_employees dim_employees", "full_refresh": false } } ``` **Response Format:** ```json { "content": [ { "type": "text", "text": "Exit code: 0\n\nSTDOUT:\n✅ dbt run completed successfully\n..." } ], "isError": false } ``` ### `dbt_test` Run data quality tests on dbt models and sources. **Tool Schema:** ```json { "name": "dbt_test", "description": "Run data quality tests on dbt models and sources", "inputSchema": { "type": "object", "properties": { "models": { "type": "string", "description": "Specific models to test (optional, space-separated)" } } } } ``` **Example Request:** ```json { "tool": "dbt_test", "arguments": { "models": "stg_employees" } } ``` ### `dbt_compile` Compile dbt models to executable SQL without running. **Tool Schema:** ```json { "name": "dbt_compile", "description": "Compile dbt models to executable SQL without running", "inputSchema": { "type": "object", "properties": { "models": { "type": "string", "description": "Specific models to compile (optional, space-separated)" } } } } ``` ### `dbt_build` Run models, tests, snapshots and seeds in DAG order. **Tool Schema:** ```json { "name": "dbt_build", "description": "Run models, tests, snapshots and seeds in DAG order", "inputSchema": { "type": "object", "properties": { "models": { "type": "string", "description": "Specific models to build (optional, space-separated)" } } } } ``` ## Discovery Tools Explore and analyze dbt project structure, models, and metadata. ### `discovery_list_models` List all dbt models in the project with metadata. **Tool Schema:** ```json { "name": "discovery_list_models", "description": "List all dbt models in the project with metadata", "inputSchema": { "type": "object", "properties": { "filter": { "type": "string", "description": "Filter models by name pattern (optional)" } } } } ``` **Example Request:** ```json { "tool": "discovery_list_models", "arguments": { "filter": "stg_" } } ``` **Response Format:** ```json { "content": [ { "type": "text", "text": "[\n {\n \"name\": \"stg_employees\",\n \"path\": \"staging/stg_employees.sql\",\n \"layer\": \"staging\",\n \"size_bytes\": 245,\n \"modified\": 1705123456.789\n }\n]" } ], "isError": false } ``` ### `discovery_model_details` Get detailed information about a specific dbt model. **Tool Schema:** ```json { "name": "discovery_model_details", "description": "Get detailed information about a specific dbt model", "inputSchema": { "type": "object", "properties": { "model_name": { "type": "string", "description": "Name of the model to analyze" } }, "required": ["model_name"] } } ``` **Example Request:** ```json { "tool": "discovery_model_details", "arguments": { "model_name": "stg_employees" } } ``` **Response Format:** ```json { "content": [ { "type": "text", "text": "{\n \"name\": \"stg_employees\",\n \"content\": \"{{ config(materialized='view') }}\\n\\nselect...\",\n \"line_count\": 10,\n \"references\": [\"employees\"],\n \"columns\": [\"employee_id\", \"employee_name\", \"department\"]\n}" } ], "isError": false } ``` ### `discovery_lineage` Get data lineage for models and dependencies. **Tool Schema:** ```json { "name": "discovery_lineage", "description": "Get data lineage for models and dependencies", "inputSchema": { "type": "object", "properties": { "model_name": { "type": "string", "description": "Model name for lineage analysis (optional for full lineage)" } } } } ``` ## Remote Tools Execute SQL queries and explore database structure directly. ### `remote_query_database` Execute SQL queries against the DuckDB database. **Tool Schema:** ```json { "name": "remote_query_database", "description": "Execute SQL queries against the DuckDB database", "inputSchema": { "type": "object", "properties": { "sql": { "type": "string", "description": "SQL query to execute" }, "limit": { "type": "integer", "description": "Maximum number of rows to return", "default": 100 } }, "required": ["sql"] } } ``` **Example Request:** ```json { "tool": "remote_query_database", "arguments": { "sql": "SELECT * FROM main.stg_employees", "limit": 10 } } ``` **Response Format:** ```json { "content": [ { "type": "text", "text": "Query: SELECT * FROM main.stg_employees LIMIT 10\n\nemployee_id | employee_name | department\n1 | John Doe | Engineering\n2 | Jane Smith | Marketing\n\nRows returned: 2" } ], "isError": false } ``` ### `remote_describe_table` Get schema and column information for database tables. **Tool Schema:** ```json { "name": "remote_describe_table", "description": "Get schema and column information for database tables", "inputSchema": { "type": "object", "properties": { "table_name": { "type": "string", "description": "Name of the table to describe" }, "schema": { "type": "string", "description": "Schema name (default: main)", "default": "main" } }, "required": ["table_name"] } } ``` **Example Request:** ```json { "tool": "remote_describe_table", "arguments": { "table_name": "stg_employees", "schema": "main" } } ``` ## Resources MCP resources provide access to project configuration and model metadata. ### Resource Types | URI Pattern | Name | Description | MIME Type | |-------------|------|-------------|-----------| | `dbt://project/config` | Project Configuration | Current dbt project settings | `application/json` | | `dbt://model/{model_name}` | Model Content | Individual model SQL content | `text/sql` | ### `dbt://project/config` **Description**: Current dbt project configuration and server settings. **Response Format:** ```json { "project_name": "data_stack", "dbt_version": "1.6.0+", "profile": "data_stack", "target": "dev", "project_dir": "/path/to/transform", "profiles_dir": "/path/to/profiles", "database_path": "/path/to/database.duckdb", "tool_groups": { "cli_tools": true, "discovery_tools": true, "semantic_layer_tools": false, "remote_tools": true } } ``` ### `dbt://model/{model_name}` **Description**: Raw SQL content of a specific dbt model. **Example**: `dbt://model/stg_employees` **Response Format:** ```sql {{ config(materialized='view') }} select cast(id as integer) as employee_id, name as employee_name, email as employee_email, cast(created_at as date) as hire_date, department, cast(salary as integer) as annual_salary from {{ source('raw', 'employees') }} ``` ## Error Handling All tools implement comprehensive error handling with consistent response formats. ### Error Response Structure ```json { "content": [ { "type": "text", "text": "Error: [specific error message]" } ], "isError": true } ``` ### Common Error Types | Error Type | Description | Example | |------------|-------------|---------| | **Tool Not Found** | Invalid tool name | `Unknown tool: invalid_tool` | | **Invalid Arguments** | Missing required parameters | `model_name is required` | | **dbt Command Error** | dbt execution failure | `dbt run failed with exit code 1` | | **Database Error** | SQL or connection error | `SQL Error: table not found` | | **File Not Found** | Model or resource missing | `Model stg_invalid not found` | | **Timeout Error** | Operation exceeded time limit | `Command timed out after 5 minutes` | ### Error Handling Features - ✅ **Input Validation**: All tool arguments validated before execution - ✅ **SQL Injection Protection**: Parameterized queries and input sanitization - ✅ **Timeout Management**: 5-minute timeout for dbt operations - ✅ **Graceful Degradation**: Partial results when possible - ✅ **Secure Error Messages**: No sensitive information exposed ## Examples ### Complete Workflow Examples #### Model Development Workflow ```json // 1. Discover available models { "tool": "discovery_list_models", "arguments": {} } // 2. Get model details { "tool": "discovery_model_details", "arguments": { "model_name": "stg_employees" } } // 3. Compile model { "tool": "dbt_compile", "arguments": { "models": "stg_employees" } } // 4. Run model { "tool": "dbt_run", "arguments": { "models": "stg_employees" } } // 5. Test model { "tool": "dbt_test", "arguments": { "models": "stg_employees" } } ``` #### Data Exploration Workflow ```json // 1. Query sample data { "tool": "remote_query_database", "arguments": { "sql": "SELECT * FROM main.stg_employees LIMIT 5", "limit": 5 } } // 2. Get table schema { "tool": "remote_describe_table", "arguments": { "table_name": "stg_employees", "schema": "main" } } // 3. Analyze data quality { "tool": "remote_query_database", "arguments": { "sql": "SELECT COUNT(*), COUNT(DISTINCT employee_id) FROM main.stg_employees", "limit": 1 } } ``` ### Claude Code Integration #### Tool Selection Logic Claude Code can intelligently select tools based on user intent: | User Intent | Recommended Tool | Example | |-------------|------------------|---------| | "Run dbt models" | `dbt_run` | Execute transformations | | "Test data quality" | `dbt_test` | Validate data integrity | | "Show me models" | `discovery_list_models` | Project exploration | | "What's in this table?" | `remote_query_database` | Data investigation | | "Check model structure" | `discovery_model_details` | Model analysis | #### Error Recovery Patterns ```json // If dbt_run fails, Claude can: // 1. Check compilation first { "tool": "dbt_compile", "arguments": { "models": "problematic_model" } } // 2. Examine model details for issues { "tool": "discovery_model_details", "arguments": { "model_name": "problematic_model" } } // 3. Query underlying data { "tool": "remote_query_database", "arguments": { "sql": "SELECT * FROM raw.source_table LIMIT 5" } } ``` ## Performance Characteristics ### Response Time Targets | Operation Type | Target | Typical | Max | |----------------|--------|---------|-----| | **Model Discovery** | <100ms | 50ms | 200ms | | **Database Query** | <200ms | 100ms | 500ms | | **dbt Compile** | <2s | 1.5s | 5s | | **dbt Run** | <5s | 3s | 300s | | **Resource Read** | <50ms | 20ms | 100ms | ### Resource Limits | Resource | Limit | Purpose | |----------|-------|---------| | **Query Result Rows** | 100 (default), 1000 (max) | Memory management | | **dbt Command Timeout** | 300 seconds | Prevent hanging operations | | **Concurrent Operations** | 3 | Resource protection | | **Log Message Length** | 10,000 characters | Response size management | ## Security Considerations ### Input Validation - ✅ **SQL Injection Prevention**: All database queries parameterized - ✅ **Path Traversal Protection**: File system access restricted - ✅ **Command Injection Prevention**: dbt arguments sanitized - ✅ **Resource Limits**: Query timeouts and row limits enforced ### Access Controls - ✅ **Environment Isolation**: Operations restricted to configured paths - ✅ **Database Access**: Limited to specified DuckDB instance - ✅ **File System**: Read-only access to dbt project directory - ✅ **Command Execution**: Only allowed dbt commands executed ### Data Protection - ✅ **Error Information**: No sensitive data in error messages - ✅ **Logging**: Structured logging without credentials - ✅ **Configuration**: Environment variables for sensitive settings - ✅ **Network Security**: Local-only database connections --- **Last Updated**: 2025-07-19 **API Version**: 1.0.0 **MCP Protocol**: 1.0 **Maintainer**: Claude Code Integration Team