Analyses

Overview

dbt's notion of models makes it easy for data teams to version control and collaborate on data transformations. Sometimes though, a certain sql statement doesn't quite fit into the mold of a dbt model. These more "analytical" sql files can be versioned inside of your dbt project using the analysis functionality of dbt.

Any .sql files found in the analysis/ directory of a dbt project will be compiled, but not executed. This means that analysts can use dbt functionality like {{ ref(...) }} to select from models in an environment-agnostic way.

In practice, an analysis file might look like this (via the open source Quickbooks models):

running_total_by_account.sql
-- analysis/running_total_by_account.sql
with journal_entries as (
select *
from {{ ref('quickbooks_adjusted_journal_entries') }}
), accounts as (
select *
from {{ ref('quickbooks_accounts_transformed') }}
)
select
txn_date,
account_id,
adjusted_amount,
description,
account_name,
sum(adjusted_amount) over (partition by account_id order by id rows unbounded preceding)
from journal_entries
order by account_id, id

To compile this analysis into runnable sql, run:

dbt compile

Then, look for the compiled SQL file in target/compiled/{project name}/analysis/running_total_by_account.sql. This sql can then be pasted into a data visualization tool, for instance. Note that no running_total_by_account relation will be materialized in the database as this is an analysis, not a model.