DBT Core MCP Server

Get information about the dbt project with optional diagnostics.

Args: run_debug: Run dbt debug to validate environment and test connection (default: True)

Returns: Dictionary with project information and diagnostic results

List all resources in the dbt project with optional filtering by type.

This unified tool provides a consistent view across all dbt resource types. Returns simplified resource information optimized for LLM consumption.

Args: resource_type: Optional filter to narrow results: - "model": Data transformation models - "source": External data sources - "seed": CSV reference data files - "snapshot": SCD Type 2 historical tables - "test": Data quality tests - "analysis": Ad-hoc analysis queries - "macro": Jinja macros (includes macros from installed packages) - None: Return all resources (default)

Returns: List of resource dictionaries with consistent structure across types. Each resource includes: name, unique_id, resource_type, description, tags, etc.

Package Discovery: Use resource_type="macro" to discover installed dbt packages. Macros follow the naming pattern: macro.{package_name}.{macro_name}

Examples: list_resources() -> all resources list_resources("model") -> only models list_resources("source") -> only sources list_resources("test") -> only tests list_resources("macro") -> all macros (discover installed packages)

Get detailed information about any dbt resource (model, source, seed, snapshot, test, etc.).

This unified tool works across all resource types, auto-detecting the resource or filtering by type. Designed for LLM consumption - returns complete data even when multiple matches exist.

Args: name: Resource name. For sources, use "source_name.table_name" or just "table_name" resource_type: Optional filter to narrow search: - "model": Data transformation models - "source": External data sources - "seed": CSV reference data files - "snapshot": SCD Type 2 historical tables - "test": Data quality tests - "analysis": Ad-hoc analysis queries - None: Auto-detect (searches all types) include_database_schema: If True (default), query actual database table schema for models/seeds/snapshots/sources and add as 'database_columns' field include_compiled_sql: If True (default), include compiled SQL with Jinja resolved ({{ ref() }}, {{ source() }} → actual table names). Only applicable to models. Will trigger dbt compile if not already compiled. Set to False to skip compilation.

Returns: Resource information dictionary. If multiple matches found, returns: {"multiple_matches": True, "matches": [...], "message": "..."}

Raises: ValueError: If resource not found

Examples: get_resource_info("customers") -> auto-detect model or source get_resource_info("customers", "model") -> get model only get_resource_info("jaffle_shop.customers", "source") -> specific source get_resource_info("test_unique_customers") -> find test get_resource_info("customers", include_compiled_sql=True) -> include compiled SQL

Get lineage (dependency tree) for any dbt resource with auto-detection.

This unified tool works across all resource types (models, sources, seeds, snapshots, etc.) showing upstream and/or downstream dependencies with configurable depth.

Args: name: Resource name. For sources, use "source_name.table_name" or just "table_name" Examples: "customers", "jaffle_shop.orders", "raw_customers" resource_type: Optional filter to narrow search: - "model": Data transformation models - "source": External data sources - "seed": CSV reference data files - "snapshot": SCD Type 2 historical tables - "test": Data quality tests - "analysis": Ad-hoc analysis queries - None: Auto-detect (searches all types) direction: Lineage direction: - "upstream": Show where data comes from (parents) - "downstream": Show what depends on this resource (children) - "both": Show full lineage (default) depth: Maximum levels to traverse (None for unlimited) - depth=1: Immediate dependencies only - depth=2: Dependencies + their dependencies - None: Full dependency tree

Returns: Lineage information with upstream/downstream nodes and statistics. If multiple matches found, returns all matches for LLM to process.

Raises: ValueError: If resource not found or invalid direction

Examples: get_lineage("customers") -> auto-detect and show full lineage get_lineage("customers", "model", "upstream") -> where customers model gets data get_lineage("jaffle_shop.orders", "source", "downstream", 2) -> 2 levels of dependents

Analyze the impact of changing any dbt resource with auto-detection.

This unified tool works across all resource types (models, sources, seeds, snapshots, etc.) showing all downstream dependencies that would be affected by changes. Provides actionable recommendations for running affected resources.

Args: name: Resource name. For sources, use "source_name.table_name" or just "table_name" Examples: "stg_customers", "jaffle_shop.orders", "raw_customers" resource_type: Optional filter to narrow search: - "model": Data transformation models - "source": External data sources - "seed": CSV reference data files - "snapshot": SCD Type 2 historical tables - "test": Data quality tests - "analysis": Ad-hoc analysis queries - None: Auto-detect (searches all types)

Returns: Impact analysis with: - List of affected models by distance - Count of affected tests and other resources - Total impact statistics - Resources grouped by distance from changed resource - Recommended dbt command to run affected resources - Human-readable impact assessment message If multiple matches found, returns all matches for LLM to process.

Raises: ValueError: If resource not found

Examples: analyze_impact("stg_customers") -> auto-detect and show impact analyze_impact("jaffle_shop.orders", "source") -> impact of source change analyze_impact("raw_customers", "seed") -> impact of seed data change

Execute a SQL query against the dbt project's database.

BEST PRACTICES:

1. Before querying: Inspect schema using get_resource_info() with include_database_schema=True

2. Always use {{ ref('model_name') }} for dbt models (never hard-code table paths)

3. Always use {{ source('source_name', 'table_name') }} for source tables

4. For non-dbt tables: Verify schema with user before querying

5. After results: Report "Query Result: X rows retrieved" and summarize key findings

QUERY EFFICIENCY:

• Use aggregations (COUNT, SUM, AVG, etc.) instead of pulling raw data

• Apply WHERE filters early to narrow scope before aggregation

• Use LIMIT for exploratory queries to get representative samples

• Calculate totals, ratios, and trends in SQL rather than returning all rows

• Use GROUP BY for categorization within the query

• Always ask: "Can SQL answer this question directly?" before returning data

LARGE RESULT HANDLING:

• For queries returning many rows (>100), use output_file parameter to save results to disk

• This prevents context window overflow and improves performance

• The tool returns metadata + preview instead of full results when output_file is used

• Example: query_database(sql="SELECT * FROM large_table", output_file="temp_auto/results.json")

OUTPUT FORMATS:

• json (default): Returns data as JSON array of objects

• csv: Returns comma-separated values with header row

• tsv: Returns tab-separated values with header row

• CSV/TSV formats use proper quoting (only when necessary) and are Excel-compatible

Args: sql: SQL query with Jinja templating: {{ ref('model') }}, {{ source('src', 'table') }} For exploratory queries, include LIMIT. For aggregations/counts, omit it. output_file: Optional file path to save results. Recommended for large result sets (>100 rows). If provided, only metadata is returned (no preview for CSV/TSV). If omitted, all data is returned inline (may consume large context). output_format: Output format - "json" (default), "csv", or "tsv"

Returns: JSON inline: {"status": "success", "row_count": N, "rows": [...]} JSON file: {"status": "success", "row_count": N, "saved_to": "path", "preview": [...]} CSV/TSV inline: {"status": "success", "row_count": N, "format": "csv", "csv": "..."} CSV/TSV file: {"status": "success", "row_count": N, "format": "csv", "saved_to": "path"}

Run dbt models (compile SQL and execute against database).

State-based selection modes (uses dbt state:modified selector):

• select_state_modified: Run only models modified since last successful run (state:modified)

• select_state_modified_plus_downstream: Run modified + downstream dependencies (state:modified+) Note: Requires select_state_modified=True

Manual selection (alternative to state-based):

• select: dbt selector syntax (e.g., "customers", "tag:mart", "stg_*")

• exclude: Exclude specific models

Args: select: Manual selector (e.g., "customers", "tag:mart", "path:marts/*") exclude: Exclude selector (e.g., "tag:deprecated") select_state_modified: Use state:modified selector (changed models only) select_state_modified_plus_downstream: Extend to state:modified+ (changed + downstream) full_refresh: Force full refresh of incremental models fail_fast: Stop execution on first failure check_schema_changes: Detect schema changes and recommend downstream runs

Returns: Execution results with status, models run, timing info, and optional schema_changes

Examples: - run_models(select="customers") - Run specific model - run_models(select_state_modified=True) - Run only what changed - run_models(select_state_modified=True, select_state_modified_plus_downstream=True) - Run changed + downstream - run_models(select="tag:mart", full_refresh=True) - Full refresh marts - run_models(select_state_modified=True, check_schema_changes=True) - Detect schema changes

Run dbt tests on models and sources.

State-based selection modes (uses dbt state:modified selector):

• select_state_modified: Test only models modified since last successful run (state:modified)

• select_state_modified_plus_downstream: Test modified + downstream dependencies (state:modified+) Note: Requires select_state_modified=True

Manual selection (alternative to state-based):

• select: dbt selector syntax (e.g., "customers", "tag:mart", "test_type:generic")

• exclude: Exclude specific tests

Args: select: Manual selector for tests/models to test exclude: Exclude selector select_state_modified: Use state:modified selector (changed models only) select_state_modified_plus_downstream: Extend to state:modified+ (changed + downstream) fail_fast: Stop execution on first failure

Returns: Test results with status and failures

Run DBT build (run + test in DAG order).

State-based selection modes (uses dbt state:modified selector):

• select_state_modified: Build only models modified since last successful run (state:modified)

• select_state_modified_plus_downstream: Build modified + downstream dependencies (state:modified+) Note: Requires select_state_modified=True

Manual selection (alternative to state-based):

• select: dbt selector syntax (e.g., "customers", "tag:mart", "stg_*")

• exclude: Exclude specific models

Args: select: Manual selector exclude: Exclude selector select_state_modified: Use state:modified selector (changed models only) select_state_modified_plus_downstream: Extend to state:modified+ (changed + downstream) full_refresh: Force full refresh of incremental models fail_fast: Stop execution on first failure

Returns: Build results with status, models run/tested, and timing info

Load seed data (CSV files) from seeds/ directory into database tables.

Seeds are typically used for reference data like country codes, product categories, etc.

State-based selection modes (detects changed CSV files):

• select_state_modified: Load only seeds modified since last successful run (state:modified)

• select_state_modified_plus_downstream: Load modified + downstream dependencies (state:modified+) Note: Requires select_state_modified=True

Manual selection (alternative to state-based):

• select: dbt selector syntax (e.g., "raw_customers", "tag:lookup")

• exclude: Exclude specific seeds

Important: Change detection for seeds works via file hash comparison:

• Seeds < 1 MiB: Content hash is compared (recommended)

• Seeds >= 1 MiB: Only file path changes are detected (content changes ignored) For large seeds, use manual selection or run all seeds.

Args: select: Manual selector for seeds exclude: Exclude selector select_state_modified: Use state:modified selector (changed seeds only) select_state_modified_plus_downstream: Extend to state:modified+ (changed + downstream) full_refresh: Truncate and reload seed tables (default behavior) show: Show preview of loaded data

Returns: Seed results with status and loaded seed info

Examples: seed_data() # Load all seeds seed_data(select_state_modified=True) # Load only changed CSVs seed_data(select="raw_customers") # Load specific seed

Execute dbt snapshots to capture slowly changing dimensions (SCD Type 2).

Snapshots track historical changes over time by recording:

• When records were first seen (valid_from)

• When records changed or were deleted (valid_to)

• The state of records at each point in time

Unlike models and seeds, snapshots are time-based and should be run on a schedule (e.g., daily or hourly), not during interactive development.

Args: select: dbt selector syntax (e.g., "snapshot_name", "tag:daily") exclude: Exclude specific snapshots

Returns: Snapshot results with status and captured changes

Examples: snapshot_models() # Run all snapshots snapshot_models(select="customer_history") # Run specific snapshot snapshot_models(select="tag:hourly") # Run snapshots tagged 'hourly'

Note: Snapshots do not support state-based selection (select_state_modified*) because they are time-dependent, not change-dependent.

Install dbt packages defined in packages.yml.

This tool enables interactive workflow where an LLM can:

1. Suggest using a dbt package (e.g., dbt_utils)

2. Edit packages.yml to add the package

3. Run install_deps() to install it

4. Write code that uses the package's macros

This completes the recommendation workflow without breaking conversation flow.

Returns: Installation results with status and installed packages

Example workflow: User: "Create a date dimension table" LLM: 1. Checks: list_resources(type="macro") -> no dbt_utils 2. Edits: packages.yml (adds dbt_utils package) 3. Runs: install_deps() (installs package) 4. Creates: models/date_dim.sql (uses dbt_utils.date_spine)

Note: This is an interactive development tool, not infrastructure automation. It enables the LLM to act on its own recommendations mid-conversation.