dbt Analyses

Analyses are SQL files stored in the analyses/ folder that dbt compiles with Jinja but never executes as models. They do not create warehouse tables or views. Use analyses for ad-hoc SQL queries, one-time investigations, and exploratory work that you want version-controlled alongside your models but that should never run automatically as part of dbt run.

The Key Difference: Analyses vs Models

Models (models/ folder):
  dbt compiles AND executes them
  Creates a table or view in the warehouse
  Runs every time you call dbt run

Analyses (analyses/ folder):
  dbt compiles them ONLY (no execution)
  Creates nothing in the warehouse
  Never runs automatically
  You copy the compiled SQL and run it yourself

When to Use Analyses

• Ad-hoc SQL queries that use ref() to reference dbt models
• One-time investigations you want saved and version-controlled
• SQL prototypes you plan to turn into a model later
• Complex queries used by analysts that should not create permanent tables
• Report SQL delivered to stakeholders that needs Jinja variable support

Creating an Analysis File

analyses/
  revenue_deep_dive.sql
  customer_cohort_analysis.sql
  q1_churn_investigation.sql

An analysis file looks exactly like a model file — SQL with Jinja:

-- analyses/revenue_deep_dive.sql

with monthly_revenue as (
    select
        date_trunc('month', order_date)  as revenue_month,
        region,
        sum(amount_dollars)              as total_revenue,
        count(distinct order_id)         as order_count,
        count(distinct customer_id)      as unique_customers
    from {{ ref('fct_orders') }}
    where order_date between
        '{{ var("start_date", "2024-01-01") }}'
        and '{{ var("end_date", "2024-12-31") }}'
    group by 1, 2
),

growth as (
    select
        revenue_month,
        region,
        total_revenue,
        lag(total_revenue) over (
            partition by region
            order by revenue_month
        )                               as prev_month_revenue
    from monthly_revenue
)

select
    revenue_month,
    region,
    total_revenue,
    prev_month_revenue,
    round(
        100.0 * (total_revenue - prev_month_revenue)
        / nullif(prev_month_revenue, 0),
        2
    )                                   as mom_growth_pct
from growth
order by revenue_month, region

Compiling an Analysis

dbt compile --select analyses/revenue_deep_dive.sql

dbt resolves all ref() and var() calls and writes the compiled SQL to:

target/compiled/my_project/analyses/revenue_deep_dive.sql

Open that file, copy the SQL, and run it directly in your warehouse or paste it into a BI tool query editor.

Compiling with Variable Overrides

dbt compile \
    --select analyses/revenue_deep_dive.sql \
    --vars '{"start_date": "2025-01-01", "end_date": "2025-03-31"}'

The compiled file now contains the Q1 2025 date range hardcoded. Share this file with stakeholders who do not use dbt.

Analysis vs Singular Test

Singular test (tests/ folder):
  Returns rows = data quality failures
  Run by dbt test
  Passes when zero rows returned

Analysis (analyses/ folder):
  Returns rows = query results for a human to read
  Never run by dbt automatically
  No pass/fail concept

Practical Example: Cohort Retention Analysis

-- analyses/customer_cohort_analysis.sql

with cohorts as (
    select
        customer_id,
        date_trunc('month', min(order_date))  as cohort_month
    from {{ ref('fct_orders') }}
    group by 1
),

orders_with_cohort as (
    select
        o.customer_id,
        c.cohort_month,
        date_trunc('month', o.order_date)     as order_month,
        datediff(
            'month',
            c.cohort_month,
            date_trunc('month', o.order_date)
        )                                     as months_since_first_order
    from {{ ref('fct_orders') }} o
    join cohorts c on o.customer_id = c.customer_id
)

select
    cohort_month,
    months_since_first_order,
    count(distinct customer_id) as active_customers
from orders_with_cohort
group by 1, 2
order by 1, 2

This analysis uses ref() so it always points to the correct dbt-managed tables regardless of which environment you compile in. The compiled SQL is ready to paste into Tableau or run in Snowflake.

Analyses in Documentation

Analyses do not appear in the main model list in dbt docs, but they are included in the full project inventory. Add descriptions in YAML if needed:

# analyses/schema.yml
version: 2

analyses:
  - name: revenue_deep_dive
    description: "Monthly revenue by region with MoM growth. Run manually for quarterly reviews."

Keeping the analyses/ Folder Tidy

• Name files with a date prefix for time-sensitive investigations: 2025-03-churn-spike.sql
• Delete one-time investigation files after the work is done to avoid clutter
• Promote a mature analysis to a model if it gets run regularly — it then creates a real table and gets tested automatically