using-dbt-query-profiler-package

name	using-dbt-query-profiler-package
description	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.

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

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

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:

# 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

# 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

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:

# 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:

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):

# 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

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

# 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

# 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:

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:

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:

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

  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:

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:

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:

  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