Dremio for dbt

MCP server for GitHub Copilot to retrieve Dremio database metadata and generate dbt staging models from templates.

---

Architecture

Overview

The server exposes four MCP tools to Copilot. Tools return structured data; Copilot handles file writing — except fix_yaml and amend_yaml, which write files directly because they are mechanical transformations with precise rules.

GitHub Copilot
     │  MCP (stdio)
     ▼
server.py  (FastMCP)
     │
     ├── DBSchema ──► DbtWrapper ──► dbt adapter ──► Dremio
     │
     ├── StageModelRenderer
     │       └── templates/stage_model.sql.j2
     │
     └── yaml_tools (fix_yaml, amend_yaml)

Classes

Class	Module	Responsibility
DbtWrapper	dbt_wrapper.py	DB connectivity via dbt-core internals. Profile/adapter resolution, SQL execution, manifest compilation with mtime-based staleness check.
DBSchema	db_schema.py	Schema introspection via two INFORMATION_SCHEMA queries: SCHEMATA for sub-schemas, TABLES for relations.
StageModelRenderer	stage_model.py	Renders staging SQL from Jinja template using explicit source/target column lists.
—	yaml_tools.py	fix_yaml and amend_yaml implementations using ruamel.yaml (comment-preserving).

MCP Tools

Tool	Description
get_objects(path, resource_type)	Browse Dremio schema hierarchy. path=None = top-level. resource_type filters by schema/table/view/relation.
get_stage_model(source_path, source_name)	Returns rendered SQL + YAML fragments + suggested file paths for a staging model.
fix_yaml(file_name)	Sort entries, remove version: 2, normalise whitespace. In-place.
amend_yaml(model_name)	Add missing column entries to a model's YAML docs by querying the live DB.

Key Design Decisions

Copilot writes files, server returns data. The server's value is database knowledge Copilot cannot obtain itself (Dremio schema, column types). File writing is something Copilot already does well and has project context for. Merging both concerns would create a competing agent rather than an extension. Exceptions: fix_yaml and amend_yaml are mechanical transformations with precise rules where LLM improvisation would produce incorrect output.

DbtWrapper uses dbt-core internals, not subprocess. Re-using the project's existing profiles.yml avoids duplicating connection configuration. The tradeoff is that dbt's internal Python APIs are not guaranteed stable across versions; the wrapper targets dbt-core ≥ 1.8.

get_objects uses two INFORMATION_SCHEMA queries. Sub-schemas come from INFORMATION_SCHEMA.SCHEMATA (so empty schemas — folders with no direct tables — are still visible). Relations come from INFORMATION_SCHEMA.TABLES WHERE table_schema = path (pre-filtered in SQL, no full scan). Python extracts only immediate children from the schemata results.

Manifest caching is two-level. On cold start, get_manifest checks whether target/manifest.json is newer than all model, macro, and config files via mtime comparison. If fresh, it loads the existing manifest directly — skipping dbt compile, which can take several seconds. Within a server session the parsed manifest is kept in memory. Since the MCP server runs as a persistent subprocess for the lifetime of the VS Code session, the in-memory cache effectively lives for the whole Copilot session.

Stage model template uses explicit column lists. StageModelRenderer passes source_columns and target_columns (snake_case) separately to the Jinja template. The template's source CTE selects source column names explicitly; renamed maps every column as src as tgt; final selects all target names. When target_columns is None, the template uses source names on both sides (skeleton mode for manual editing).

YAML editing uses ruamel.yaml. ruamel.yaml preserves inline and block comments through load/dump cycles. The standard library yaml (PyYAML) silently discards comments, which would break user-authored documentation.

---

Configuration

The server reads two optional environment variables:

Variable	Default	Description
DBT_PROJECT_DIR	. (current directory)	Path to the dbt project root.
DBT_PROFILES_DIR	~/.dbt	Path to the directory containing profiles.yml.

The database adapter (e.g. dbt-dremio) must be installed separately.

---

Usage

Install

uv sync

Run as MCP server (stdio transport)

uv run db-mcp-for-dbt

Or in a Copilot MCP config:

{
  "mcpServers": {
    "db-mcp-for-dbt": {
      "command": "uv",
      "args": ["run", "--directory", "/path/to/dbt-project", "db-mcp-for-dbt"],
      "env": {
        "DBT_PROJECT_DIR": "/path/to/dbt-project"
      }
    }
  }
}

Run tests

uv run pytest

---

Typical Workflows

1. Browse schema → generate model Ask Copilot: "List schemas in myspace" → pick a table → "Generate a staging model for myspace.sales.orders"

2. Paste path directly "Generate a staging model for myspace.sales.orders" — Copilot calls get_stage_model and writes the resulting SQL and YAML files.

3. Bulk staging for a whole schema Copilot calls get_objects(path="myspace.sales", resource_type="relation"), loops get_stage_model per table, writes all files.

4. Fix YAML formatting "Fix models/01_staging/sales/_sales__models.yml" → fix_yaml sorts entries and normalises formatting in-place.

5. Sync column docs after schema change "Amend docs for stg_sales__orders" → amend_yaml queries live columns and appends undocumented ones.

---

Project Structure

src/db_mcp_for_dbt/
  __init__.py
  server.py            # FastMCP entry point, tool definitions
  dbt_wrapper.py       # DbtWrapper — dbt-core adapter access
  db_schema.py         # DBSchema — Dremio INFORMATION_SCHEMA queries
  stage_model.py       # StageModelRenderer — Jinja staging SQL
  yaml_tools.py        # fix_yaml and amend_yaml implementations
  templates/
    stage_model.sql.j2 # Jinja template for staging models
tests/
  test_dbt_wrapper.py
  test_db_schema.py
  test_stage_model.py
  test_yaml_tools.py
  test_server.py