# Using Dbt Query Profiler Package

> Use when troubleshooting dbt query performance, comparing model implementations, retrieving query plans/stats, or helping users understand how to use the dbt-query-profiler package. Also use when users ask about getting query IDs from dbt runs.

- Skill: `dbt-labs/using-dbt-query-profiler-package` (Agent Skill)
- Install (CLI): `npx skillmds@latest add dbt-labs/using-dbt-query-profiler-package`
- Raw SKILL.md: https://api.skillmd.com/api/skills/dbt-labs/using-dbt-query-profiler-package/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: dbt-labs (https://skillmd.com/u/dbt-labs)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/dbt-labs/using-dbt-query-profiler-package

---


# Using dbt-query-profiler

## Overview

dbt-query-profiler retrieves query history, SQL text, execution plans, and statistics from your data warehouse via `dbt run-operation`. Use it to troubleshoot slow queries, compare implementations, or understand query behavior.

## The Troubleshooting Flow

```mermaid
flowchart TB
    A[1. Get query ID] --> B[2. List recent queries<br/>print_query_history]
    B --> C[3. Get full SQL<br/>print_query_sql]
    C --> D[4. Analyze plan<br/>print_execution_plan]
    D --> E[5. Get metrics<br/>print_query_stats]
```

## Getting Query IDs

Query IDs are required for `print_query_sql`, `print_execution_plan`, and `print_query_stats`.

### Method 1: From print_query_history
```bash
dbt run-operation dbt_query_profiler.print_query_history --args '{table_name: my_model, limit: 5}' --quiet
```
Output includes `query_id` for each result.

### Method 2: From dbt logs with OTEL format

When running dbt (Core or Fusion), use OTEL log format to see query IDs:

```bash
# dbt Core
dbt run --log-format json  # Look for query_id in output

# dbt with OTEL logging (shows all warehouse query IDs)
DBT_LOG_FORMAT=otel dbt run
```

The OTEL format logs each query sent to the warehouse with its ID, making it easy to grab IDs for profiling.

### Method 3: From warehouse UI
Most warehouses show query IDs in their query history UI (Snowflake Query History, BigQuery Jobs, etc.).

## Core Operations

### List Recent Queries
```bash
# Basic: your recent queries
dbt run-operation dbt_query_profiler.print_query_history --quiet

# Filter by table name (partial match)
dbt run-operation dbt_query_profiler.print_query_history \
  --args '{table_name: customers, limit: 10}' --quiet

# Filter by query type
dbt run-operation dbt_query_profiler.print_query_history \
  --args '{query_type: SELECT, limit: 5}' --quiet

# All users (requires elevated permissions)
dbt run-operation dbt_query_profiler.print_query_history \
  --args '{user_name: "", limit: 10}' --quiet
```

### Get Query SQL
```bash
dbt run-operation dbt_query_profiler.print_query_sql \
  --args '{query_id: "01c20db1-060a-bcad-0004-7d832cd6b002"}' --quiet
```

### Get Execution Plan (Actual Stats from Executed Queries)

Use `print_execution_plan` to retrieve the actual execution plan with real statistics from an already-executed query:

```bash
# JSON format (default)
dbt run-operation dbt_query_profiler.print_execution_plan \
  --args '{query_id: "01c20db1-..."}' --quiet

# Text format (more readable)
dbt run-operation dbt_query_profiler.print_execution_plan \
  --args '{query_id: "01c20db1-...", format: text}' --quiet

# Markdown (Snowflake only)
dbt run-operation dbt_query_profiler.print_execution_plan \
  --args '{query_id: "01c20db1-...", format: markdown}' --quiet

# Wide plan: only operators at/above 5% of total time, sorted by time% desc (Snowflake only)
dbt run-operation dbt_query_profiler.print_execution_plan \
  --args '{query_id: "01c20db1-...", format: text, min_pct: 5}' --quiet
```

For a condensed view that adds Snowflake spill-to-disk bytes (the evidence for a
warehouse-sizing decision), use `print_execution_plan_summary` instead - same args,
minus `min_pct`/`top_n`:
```bash
dbt run-operation dbt_query_profiler.print_execution_plan_summary \
  --args '{query_id: "01c20db1-...", format: text}' --quiet
```

### Get Query Plan (EXPLAIN-based for SQL)

Use `print_query_plan` to get an EXPLAIN-based plan for arbitrary SQL (estimated, not actual stats):

```bash
# From SQL string
dbt run-operation dbt_query_profiler.print_query_plan \
  --args '{sql: "SELECT * FROM my_table WHERE id = 1"}' --quiet
```

**Note:** `print_query_plan` uses EXPLAIN and shows estimated costs. `print_execution_plan` retrieves actual execution statistics from a query that has already run.

### Getting Query Plans for dbt Models

dbt models contain Jinja that must be compiled to SQL before you can run EXPLAIN. Here's how to get the query plan for a model:

**Step 1: Compile the model**
```bash
dbt compile --select my_model
```

**Step 2: Find the compiled SQL**
The compiled SQL is written to:
```
target/compiled/<project_name>/models/<path>/<model_name>.sql
```

**Step 3: Run EXPLAIN on the compiled SQL**
```bash
# Copy the compiled SQL and pass it to print_query_plan
dbt run-operation dbt_query_profiler.print_query_plan \
  --args '{sql: "SELECT ... (paste compiled SQL)"}' --quiet
```

**Note:** dbt models contain Jinja that must be compiled before running EXPLAIN. There's no way to programmatically get the compiled SQL via macros because dbt's `graph.nodes` only exposes `raw_code` (uncompiled Jinja), not `compiled_code`. The manual compile workflow above is the recommended approach.

### Get Query Stats
```bash
# JSON format (default)
dbt run-operation dbt_query_profiler.print_query_stats \
  --args '{query_id: "01c20db1-..."}' --quiet

# Text format
dbt run-operation dbt_query_profiler.print_query_stats \
  --args '{query_id: "01c20db1-...", format: text}' --quiet
```

## Using --quiet Flag

**Always use `--quiet` with run-operation commands** to get clean output.

Without `--quiet`:
```
Running with dbt=1.7.0
Registered adapter: snowflake=1.7.0
Found 0 models, 0 tests...
{"query_id": "01c20db1-...", "query_text": "SELECT..."}
```

With `--quiet`:
```
{"query_id": "01c20db1-...", "query_text": "SELECT..."}
```

The `--quiet` flag suppresses dbt's startup logs, leaving only the profiler's JSON/text output - much easier to parse or pipe to other tools.

## Comparing Implementations

To compare two approaches to a model:

```mermaid
flowchart LR
    A[Run impl A] --> B[Note query_id A]
    C[Run impl B] --> D[Note query_id B]
    B --> E[Compare stats]
    D --> E
    E --> F[Compare plans if needed]
```

1. **Run first implementation**, note the query ID from logs
2. **Run second implementation**, note its query ID
3. **Compare stats**:
```bash
dbt run-operation dbt_query_profiler.print_query_stats \
  --args '{query_id: "first-query-id", format: text}' --quiet

dbt run-operation dbt_query_profiler.print_query_stats \
  --args '{query_id: "second-query-id", format: text}' --quiet
```
4. **Compare plans** if needed:
```bash
dbt run-operation dbt_query_profiler.print_execution_plan \
  --args '{query_id: "first-query-id", format: text}' --quiet
```

## Common Arguments

| Argument | Description | Default |
|----------|-------------|---------|
| `table_name` | Filter by table name (partial match) | None |
| `user_name` | Filter by user (empty string = all users) | Current user |
| `query_type` | Filter: SELECT, INSERT, CREATE_TABLE_AS_SELECT, etc. | None |
| `limit` | Number of queries to return | 1 |
| `result_limit` | Lookback depth for `print_query_history` (all adapters) and `print_query_sql` (Snowflake only) | 100 (history), 1000 (sql) |
| `query_id` | Specific query to analyze | Required for sql/plan/stats |
| `format` | Output: json, text, markdown (varies by adapter) | json |
| `min_pct` / `top_n` | `print_execution_plan` only, Snowflake only: filter to operators at/above a time% threshold and/or cap to the top N by time% | None (all operators) |

## Platform-Specific Notes

### Snowflake
- Use `format: markdown` for readable plan tables
- Account-level history requires `SNOWFLAKE.ACCOUNT_USAGE` access
- Set `use_account_level_history: true` in vars for cross-user queries
- Filter for dbt model refreshes by query type:
  - Tables: `query_type: CREATE_TABLE_AS_SELECT`
  - Views: `query_type: CREATE_VIEW`
  ```bash
  dbt run-operation dbt_query_profiler.print_query_history \
    --args '{table_name: my_model, query_type: CREATE_TABLE_AS_SELECT, limit: 5}' --quiet
  ```

### BigQuery
- Query plan (EXPLAIN) not available via SQL (raises error)
- **Execution Plan:** Returns Query Insights instead of an operator-level plan — diagnostics like slot contention, high cardinality joins, partition skew. Returns a "no issues detected" message if `performance_insights` is null.
- Uses `INFORMATION_SCHEMA.JOBS_BY_USER` or `JOBS_BY_PROJECT`

### Databricks
- Plans from `system.query.history`
- Unity Catalog required for system tables

### DuckDB
- Requires `CALL enable_logging('QueryLog')` before queries to profile
- Use file-based logging for cross-session profiling: `CALL enable_logging('QueryLog', storage_path = 'path/to/logs')`
- Plans via `EXPLAIN ANALYZE` re-execution

### Redshift
- Plans from `stl_explain` system table
- Stats from `svl_query_metrics_summary`

## Troubleshooting

**"No query found" / "Query not found"**
- Query may be outside lookback window (increase `result_limit`)
- Query not yet in history (some warehouses have latency)
- **Wrong user context** - the query belongs to a different user, e.g. a dbt Cloud job's
  service user (`DBT_CLOUD_USER`). This is the common case when profiling *production job
  runs* rather than your own dev session. Fix depends on which macro:
  - `print_query_history`: pass `user_name: ""` (requires elevated permissions), or
  - `print_query_sql`, `print_query_stats` (Snowflake): pass
    `--vars '{use_account_level_history: true}'` - these look the query up through the same
    account-level history view as `print_query_history`.
  - `print_execution_plan` / `print_execution_plan_summary` (Snowflake): once you already
    have the `query_id`, `use_account_level_history` does **not** apply - these call
    `GET_QUERY_OPERATOR_STATS()` directly, which is privilege-gated, not view-gated. If it
    can't find another user's query, the fix is `MONITOR` privilege on the query's
    warehouse (or `ACCOUNTADMIN`), not the `use_account_level_history` var.

**One-off `--vars` override**

`use_account_level_history` is normally set once in `dbt_project.yml`, but for a single
historical-query lookup pass it inline instead of changing project config:
```bash
dbt run-operation dbt_query_profiler.print_query_stats \
  --args '{query_id: "..."}' --vars '{use_account_level_history: true}' --quiet
```

**Identifying an unknown query_id's model**

Given a `query_id` with no known origin (e.g. pulled from a warehouse UI or a job's
history), run `print_query_sql` and read the trailing dbt query-comment JSON in the
returned text - it carries `node_id`, and for dbt Cloud job runs also `dbt_cloud_job_id`,
`dbt_cloud_run_id`, and `invocation_id`:
```bash
dbt run-operation dbt_query_profiler.print_query_sql --args '{query_id: "..."}' --quiet
```

**Query plan not available**
- BigQuery doesn't expose plans
- Some query types don't generate plans

**Wide/large execution plans**
- Union-heavy staging models can produce 100+ operators, mostly at `time: 0%` - reading
  top to bottom wastes time on noise. On Snowflake, `print_execution_plan` and
  `print_execution_plan_summary` take `min_pct`/`top_n` to filter/cap by time% instead:
  ```bash
  dbt run-operation dbt_query_profiler.print_execution_plan \
    --args '{query_id: "...", format: text, min_pct: 5}' --quiet
  ```

**Permission denied**
- Check warehouse-specific permissions in README
- For all-user queries, need account-level access