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.