query_database
Execute SQL queries with Jinja templating against your dbt project's database, including ref() and source() references. Supports CTE extraction from models and multiple output formats.
Instructions
Execute a SQL query against the dbt project's database.
This tool compiles and runs SQL with Jinja templating support, allowing you to use {{ ref('model') }} and {{ source('src', 'table') }} in your queries.
SQL Templating:
Use {{ ref('model_name') }} to reference dbt models
Use {{ source('source_name', 'table_name') }} to reference source tables
dbt compiles these to actual table names before execution
CTE Querying (LLM quick reference)
Always pass
cte_name+model_namein parameters (not in SQL)Always write a normal
SELECT ... FROM __cte__ ...__cte__or{{ cte }}is replaced with the CTE name
What happens under the hood
Extracts the target CTE plus upstream CTEs from the model
Runs your query against that extracted CTE
Templating
dbt resolves all
{{ ref() }}/{{ source() }}automatically; no manual table names
Invalid syntax to avoid
{{ ref('model', cte='name') }}does not exist; always usecte_name+model_name
Output Management:
For large result sets (>100 rows), use output_file to save results
If output_file is omitted, all data returns inline (may consume large context)
output_file is automatically created with parent directories
Relative paths are resolved relative to the workspace root
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.
When using cte_name/model_name, provide a full SELECT/WITH query that
selects from __cte__ (or {{ cte }}), which is replaced with the CTE name.
output_file: Optional file path to save results. Recommended for large result sets (>100 rows).
Relative paths are resolved relative to the workspace root.
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"
cte_name: Optional CTE name to query from a model (requires model_name)
model_name: Optional model name containing the CTE (required when cte_name is specified)
state: Shared state object injected by FastMCP
Returns: JSON inline: {"status": "success", "row_count": N, "rows": [...], "elapsed_time": X.XX} JSON file: {"status": "success", "row_count": N, "saved_to": "path", "preview": [...], "elapsed_time": X.XX} CSV/TSV inline: {"status": "success", "row_count": N, "format": "csv", "csv": "...", "elapsed_time": X.XX} CSV/TSV file: {"status": "success", "row_count": N, "format": "csv", "saved_to": "path", "elapsed_time": X.XX}
Note: elapsed_time is in seconds and represents the total query execution time including compilationRaises: RuntimeError: If query execution fails ValueError: If invalid CTE/model parameters provided
Examples: # Simple query with ref() query_database(sql="SELECT * FROM {{ ref('customers') }} LIMIT 10")
# Query with source()
query_database(sql="SELECT * FROM {{ source('jaffle_shop', 'orders') }} LIMIT 5")
# Aggregation (no LIMIT needed)
query_database(sql="SELECT COUNT(*) as total FROM {{ ref('customers') }}")
# Query a specific CTE from a model
query_database(
cte_name="customer_agg",
model_name="customers",
sql="SELECT * FROM __cte__ LIMIT 10"
)
# Query a CTE with filtering
query_database(
cte_name="customer_agg",
model_name="customers",
sql="SELECT * FROM __cte__ WHERE order_count > 5 LIMIT 20"
)
# Query a CTE with aggregation (full SELECT)
query_database(
cte_name="customer_agg",
model_name="customers",
sql="SELECT customer_id, COUNT(*) AS cnt FROM __cte__ GROUP BY customer_id"
)
# WRONG - Do NOT use ref() with cte parameter (does not exist):
# query_database(sql="SELECT * FROM {{ ref('model', cte='cte_name') }}")
#
# CORRECT - Use cte_name and model_name parameters instead:
# query_database(cte_name="cte_name", model_name="model", sql="SELECT * FROM __cte__ LIMIT 10")
# Save large results to file
query_database(
sql="SELECT * FROM {{ ref('orders') }}",
output_file="temp_auto/orders_export.json"
)
# Export as CSV
query_database(
sql="SELECT * FROM {{ ref('customers') }}",
output_file="temp_auto/customers.csv",
output_format="csv"
)Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| output_file | No | ||
| output_format | No | json | |
| cte_name | No | ||
| model_name | No |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||