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

analyses/running_total_by_account.sql

-- analyses/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}/analyses/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.

Overview​

Overview