Mastering dbt's analyses Directory: Ad-hoc SQL and Reuse Patterns

analyses is the location for analysis SQL that dbt only compiles. Unlike models, it is not executed or persisted by dbt run, but you can still leverage ref, source, and macros, and version-control shareable "ad-hoc SQL" across your team.

This article covers the role of analyses, reuse patterns, operational best practices, criteria for promoting to models, and compile-time behavior, all with the Analytics Engineer certification exam in mind. See the dbt official documentation for the full specification.

What analyses Is and How It Behaves

SQL under analyses is resolved by dbt compile (Jinja, ref, source) and written out beneath target/compiled. Because dbt run does not execute them, no tables or views are created in the warehouse. The benefit is that ad-hoc analysis can be preserved in a reproducible form.

When you run docs generate, analyses appear in the navigation under "Analyses." Descriptions and tags can be managed via YAML. A key trait is that execution responsibility lies with the developer. If you want to run a heavy query continuously, consider promoting it to a model.

• Compile target, not an execution target. Not included in dbt run or dbt build
• Jinja features such as ref, source, macros, var, and env_var are available
• The final SQL is written to target/compiled/<project>/analyses
• Shown on the docs site under "Analyses"

Workflow for analyses

Minimal analysis example: analyses/user_growth.sql

-- analyses/user_growth.sql
-- References models users_daily and orders_daily to check weekly growth
with u as (
  select * from {{ ref('users_daily') }}
), o as (
  select * from {{ ref('orders_daily') }}
)
select
  u.ds as week_start,
  count(distinct u.user_id) as weekly_active_users,
  sum(o.order_amount) as weekly_gmv
from u
left join o on o.user_id = u.user_id and o.ds = u.ds
where u.ds >= dateadd('day', -90, current_date)
group by 1
order by 1 desc;

Reuse Patterns: When to Use ref/source, Macros, and Variables

In analyses you can reference models and sources via ref/source, and call the same logic packaged as Jinja macros. This makes "ad-hoc SQL" consistent with production logic, which improves the reproducibility of validation and exploration.

Parameterize with var and env_var to make swapping warehouses or time ranges easy. Pull heavy shared logic into macros, and if you want to share the query structure itself, consider combining with ephemeral models.

• Macro-ize aggregation and normalization logic so both analyses and models can use it
• Externalize time ranges and thresholds with var (in profiles or dbt_project.yml)
• Start small: validate as an analysis first, then promote to a model if reused
• If inline CTEs balloon, split them into ephemeral models

Macro reuse example (called from an analysis)

-- macros/metrics.sql
{% macro sum_if_positive(col) %}
    case when {{ col }} > 0 then {{ col }} else 0 end
{% endmacro %}

-- analyses/profit_probe.sql
with f as (
  select * from {{ ref('fact_orders') }} where order_date >= {{ var('start_date', 'current_date - interval 30 day') }}
)
select
  order_date,
  sum({{ sum_if_positive('gross_profit') }}) as profit_pos
from f
group by 1
order by 1 desc;

Operational Best Practices for Ad-hoc SQL

Name files to make the purpose obvious, including time range and grain. Example: retention_probe_90d.sql. Capture supplementary details in YAML so it's clear who uses the analysis and why.

Always inspect the resolved SQL with dbt compile before running it in the warehouse. Check that ref resolution and variable expansion match expectations. For expensive queries, keep a draft version with LIMIT or sampling separate from the production version.

• Naming convention: include purpose_target_grain and time range
• Manage descriptions and tags per analysis in YAML to improve discoverability
• Review compile output before executing in the warehouse
• Validate heavy joins or long time ranges in a small scope first (LIMIT, restrictive where)
• Share via PR after review, and periodically prune stale analyses

Manage analysis metadata in YAML (schema: analyses.yml)

version: 2
analyses:
  - name: user_growth
    description: Ad-hoc analysis visualizing weekly user growth and GMV for the last 90 days. Depends on models users_daily and orders_daily.
    tags: ['ad-hoc', 'growth']
  - name: profit_probe
    description: Normalize the sign of profit and inspect the recent period.
    tags: ['ad-hoc', 'finance']

When to Promote an Analysis to a Model, and How

Consider promoting to a model when you find yourself running the same analysis repeatedly, want it referenced as a permanent dashboard metric, or want to reuse it downstream from other models. Becoming a model unlocks scheduling, dependency management, and automated tests.

To migrate, move the analysis SQL under models, add tests and docs, and pick a materialization (view, table, incremental) as needed. Ephemeral is another option if you want to inline-expand it into other models.

• Decision criteria: high run frequency, ongoing use by others, downstream dependencies
• Move under models and add tests such as unique and not_null
• Schedule via dbt Cloud or an orchestrator
• If it's heavy, consider incremental or pre-aggregated tables

Minimal migration example

-- 1) Move analyses/user_growth.sql to models/user_growth.sql
-- 2) Add materialization and tests in models/user_growth.sql

-- models/user_growth.sql
{{ config(materialized='view') }}
with u as (
  select * from {{ ref('users_daily') }}
), o as (
  select * from {{ ref('orders_daily') }}
)
select u.ds as week_start,
       count(distinct u.user_id) as weekly_active_users,
       sum(o.order_amount) as weekly_gmv
from u left join o on o.user_id = u.user_id and o.ds = u.ds
group by 1

-- tests/schema.yml
version: 2
models:
  - name: user_growth
    tests: []
    columns:
      - name: week_start
        tests: [not_null]
      - name: weekly_active_users
        tests: [not_null]
      - name: weekly_gmv
        tests: [not_null]

Compile and Doc Generation: Behavioral Caveats

Analyses are only activated by dbt compile and are not executed by dbt run or dbt build. You can enumerate them with ls and document them with docs generate. ref and source are parsed, but because nothing executes, warehouse-side permissions and cost only apply when you run the SQL manually.

On large projects, leaving unused analyses around tends to bloat the docs. Filter with tags or split into folders to keep things manageable.

• dbt compile writes analyses into target/compiled
• Not included in dbt run or dbt build (a frequent exam point)
• Use dbt ls --resource-type analysis to list them
• docs generate exposes them in the navigation under Analyses

Frequently used commands

# Parse only (includes analyses)
dbt compile

# List analysis nodes
dbt ls --resource-type analysis

# Generate and view documentation
dbt docs generate
dbt docs serve  # for local browsing

Exam Tips and a Practical Checklist

On the Analytics Engineer exam, expect questions on these points: analyses are "compiled only and not executed by dbt run," ref/source and macros are usable, and they appear in the docs. Pair that with the rule that you promote to a model when you want continuous operation.

In practice, treating analyses as a "safe draft and knowledge-sharing space" and codifying naming, YAML metadata, review, and cleanup all together improves both analysis speed and quality.

• Exam: analyses are a compile target, not a run target
• Exam: ref/source and macros are usable in analyses
• Exam: shown as "Analyses" in the docs
• Ops: institutionalize naming, metadata, review, and periodic cleanup

Practical checklist (excerpt)

- [ ] File name follows the naming convention (purpose, time range, grain)
- [ ] description, tags, and owners are recorded in YAML
- [ ] dbt compile output has been reviewed
- [ ] Heavy queries have a scope-limited draft version
- [ ] Repeat-use signals trigger a discussion of promoting to a model

Check Your Understanding

Frequently Asked Questions