ANALYSES

A WORKING EXAMPLE

Drop a .sql file into an analyses/folder at the project root (create the folder if it isn't there — dvt initdoesn't scaffold it, and dbt's default analysis-paths picks it up automatically). {{ ref() }} and {{ source() }} work exactly as they do in a model:

-- analyses/revenue_by_cohort.sql
-- Compiled, never run: DVT writes the SQL, you decide where to use it.

with first_orders as (

    select
        customer_id,
        min(order_date) as first_order_date
    from {{ ref('fct_orders') }}
    group by customer_id

)

select
    date_trunc('month', f.first_order_date) as cohort,
    date_trunc('month', o.order_date)       as revenue_month,
    sum(o.amount)                           as revenue
from {{ ref('fct_orders') }} o
join first_orders f using (customer_id)
group by 1, 2
order by 1, 2

Compile it — nothing touches a warehouse, and with --select the result prints straight to your terminal:

$ dvt compile --select revenue_by_cohort

Found 14 models, 1 analysis, 2 seeds, 21 sources
Concurrency: 4 threads (target='pg_dev')

Compiled node 'revenue_by_cohort' is:
with first_orders as (

    select
        customer_id,
        min(order_date) as first_order_date
    from "analytics"."public"."fct_orders"
    group by customer_id

)
...

Every {{ ref() }} resolved to a real relation on your default target. The same executable SQL is also written to disk, ready to paste into a BI tool, hand to a colleague, or run by hand:

target/compiled/<project_name>/analyses/revenue_by_cohort.sql

WHAT YOU GET FOR FREE

Because an analysis is a real node in the project, it inherits everything a model has except execution:

• ▸Full Jinja — ref(), source(), var(), and your own macros all render.
• ▸DAG membership — an analysis refs models, so lineage knows exactly what it reads. It is always a leaf: models cannot ref an analysis back, which is precisely why it can never break your builds.
• ▸Version control — the query that answered last quarter's board question is in git, not in somebody's clipboard.
• ▸Selection — dvt compile --select <name> targets one analysis, and dvt ls lists them alongside your models.

You can document analyses the same way you document models — a properties file with an analyses: key, giving each a description that shows up in your project docs.

ANALYSIS OR MODEL?

One question decides it: does anything downstream need the result as a relation? If yes, it's a model. If you only need the SQL— to read, share, or run once — it's an analysis.

Promotion is a file move: analyses/revenue_by_cohort.sql → models/marts/revenue_by_cohort.sql, and the next dvt runbuilds it. The SQL doesn't change — that's the point of drafting it as an analysis.

TARGET FLIPS TAKE ANALYSES WITH THEM

An analysis compiles against the default target, so its SQL is written in that target's dialect — a date_trunc here, a ::date cast there. That makes analyses exactly the kind of file that quietly breaks when a project moves engines.

One asymmetry worth knowing: only models can be federated. An analysis never runs, so it never takes the f_table path — it is always judged in the target's dialect, and a flip always moves it to the new one. See switching targets for the full flip workflow.

REFERENCE