dbt using variable to make your sql more flexible

 dbt uses jinja as a template engine to work with file. Therefor we can configure our variable 2 ways.

1. dbt_project.yaml or vars.yml (on the project root) - we can introduce variable by using the followings - as you can see here we are introducing a variable call person. 

name: jaffle_shop

profile: jaffle_shop

seed-paths: ["seeds"]

model-paths: ["models"]

macro-paths: ["macros"]

test-paths: ["tests"]           # ← Make sure this exists

clean-targets:

  - "target"

  - "dbt_packages"

seeds:

  # Builds seeds into '<your_schema_name>_raw'

  jaffle_shop:

    +schema: raw

vars:

  person: JohnDoe

this is what your vars.yml looks like 

vars:

  person: JohnDoe2

  sold_date_sk: 20160901

2. passing variable in from the command line using --var argument. Please note variable are passed in as json - so we need to do something like this. Please note: it is important to use double quote for variable name, otherwise you won't be able to pass it in. 

Plese refer to variable precedence for additional guide here.

dbt run --vars '{"sold_date_sk": 20000, "person": "JohnDoe"}'

And this is what our template looks like where we have our "sold_date_dk". This is for illustration purpose only. 

{{ config(materialized='view')}}

with

source_store_sales as (

    select * from {{ source('SNOWFLAKE_SAMPLE_DATA', 'store_sales') }}

),

renamed as (

    select

      *

    from source_store_sales where SS_SOLD_DATE_SK = {{ var("sold_date_sk") }}

    {% if var('person') == "JohnDoe" %}

     and SS_SOLD_DATE_SK = 20160901

    {% endif %}

)

select * from renamed

And you can see that dbt has compile it as such 

with

source_store_sales as (

    select * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.store_sales

),

renamed as (

    select

      *

    from source_store_sales where SS_SOLD_DATE_SK = 20000

     

     and SS_SOLD_DATE_SK = 20160901

   

)

select * from renamed

When we passed in  the followings 

dbt run --vars '{"sold_date_sk": 10000, "person": "JohnDoe2"}'

Noticed that sql statement gets udpated accordinly.

with

source_store_sales as (

    select * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.store_sales

),

renamed as (

    select

      *

    from source_store_sales where SS_SOLD_DATE_SK = 10000

   

)

select * from renamed