Skip to content

dbt

Components

  • profile.yml: describes database connections, connections details by environment. Not required for dbt cloud
  • dbt_project.yml: settings for specific dbt project
    • default paths for project folders
    • config: e.g. model materialization using table v/s view
  • models: transformation logic
  • macros: jinja macros for code generation
  • snapshots: maintain SCD
  • tests: Generic or Singular (one-off) tests
    • Generic: unique, not_null
    • Singular: consists of a SQL; it's successful if it doesn't return any data
  • analyses:
  • seeds:

Models

  • models -> 1+ folders -> 1+ .sql files
    • each sub-folder under models may contain schema.yml that describe schema, contain docs and tests
  • source refers to data (table/view) that is outside of dbt project and not created during the dbt pipeline
  • {{ config(.....) }} for setting model specific settings:
    • e.g. {{ config(pre_hook=["ALTER SESSION SET QUERY_TAG='MODEL_A'"]
    • e.g. {{ config(materialized = 'ephemeral')
    • transient=False
    • cluster_by=[]
  • a key prefixed with + in the project yaml file implies the settings affect the key and everything that and below
  • incremental runs are supported by using:
    • materialized="incremental" and unique_key(in config)
    • in conjunction with jinja if is_incremental (in SQL text)
  • supported jinja expressions:
    • source refers to an external table/view
    • ref refers to another model, and creates a dependency
    • this (the current model being created)
  • materialization strategies: View, Table, Incremental, Ephemeral (CTE based, no objects are added)

Snapshots

  • must have models enclosed jinja2 {% snapshot DIM_CUSTOMER_SCD %}
  • define a strategy, unique_key and check_cols in config
  • strategies: timestamp and check
  • Examples:
{% snapshot orders_snp %}
{{ config(strategy='timestamp', unique_key='id', updated_at='last_upd_ts') }}

select * from {{ source('jaffle_shop', 'orders') }}

(% endsnapshot %)

{% snapshot orders_snp %}
{{ config(strategy='check', unique_key='id', check_cols=['status', 'is_cancelled']) }}

select * from {{ source('jaffle_shop', 'orders') }}

(% endsnapshot %)