How to use env_var inside quotes in DBT Model?-CodePudding

I have a DBT script that uses some environment variables and logs those to the table.

with source_data as (

    "{{ env_var('run_id') }}"::bigint  as run_id, 
    "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"::varchar as warehouse, 
    "{{ env_var('SNOWFLAKE_DATABASE') }}"::varchar as db
)

select *
from source_data

but the value returned by Jinja expression is considered as column name and it throws error by saying column to present in table.

satish@000000 dbt run -m test.sql
08:28:02  Running with dbt=1.3.0
08:28:02  Found 4 models, 4 tests, 0 snapshots, 0 analyses, 327 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
08:28:02  
08:28:06  Concurrency: 5 threads (target='dev')
08:28:06  
08:28:06  1 of 1 START sql view model dbt_development.test ............................... [RUN]
08:28:07  1 of 1 ERROR creating sql view model dbt_development.test ...................... [ERROR in 1.15s]
08:28:08  
08:28:08  Finished running 1 view model in 0 hours 0 minutes and 5.98 seconds (5.98s).
08:28:08  
08:28:08  Completed with 1 error and 0 warnings:
08:28:08  
08:28:08  Database Error in model test (models/test.sql)
08:28:08    column "20221212000000" does not exist
08:28:08    compiled Code at target/run/dbt_practice/models/test.sql
08:28:08  
08:28:08  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

How to correctly use env_var in select statements in DBT models?

Update:

with source_data as (

    select '1' as id, 
    '{{ env_var("run_id") }}'::bigint as run_id, 
    '{{ env_var("SNOWFLAKE_WAREHOUSE") }}'::varchar as warehouse, 
    '{{ env_var("SNOWFLAKE_DATABASE") }}'::varchar as db
)

select *
from source_data

changing the quotes worked.

CodePudding user response：

could you try?

with source_data as (

    "{{ env_var('run_id') | as_number }}"  as run_id, 
    "{{ env_var('SNOWFLAKE_WAREHOUSE') | varchar }}" as warehouse, 
    "{{ env_var('SNOWFLAKE_DATABASE') | varchar }}" as db
)

select *
from source_data

And, "{{ env_var('run_id') | int }}" as run_id, is another option for specifying number types.

CodePudding user response：

with source_data as (

    select '1' as id, 
    '{{ env_var("run_id") }}'::bigint as run_id, 
    '{{ env_var("SNOWFLAKE_WAREHOUSE") }}'::varchar as warehouse, 
    '{{ env_var("SNOWFLAKE_DATABASE") }}'::varchar as db
)

select *
from source_data

changing the quotes worked.