如何在DBT模型的SELECT语句中正确使用env_var环境变量？

问题场景 #

需要将环境变量记录到表中，编写的初始DBT脚本执行时报错，提示目标列不存在。

初始错误代码 #

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

错误日志 #

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

报错原因 #

Jinja表达式渲染后的值被双引号包裹，在SQL语法中双引号用于标识列名或对象名，而非字符串常量。数据库会将渲染后的"20221212000000"识别为列名，而实际不存在该列，因此抛出错误。同时原CTE块中缺少select关键字，属于SQL语法错误。

正确解法 #

将包裹Jinja表达式的双引号改为单引号，并补充select关键字修正CTE语法，修改后的代码如下：

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

关键说明 #

• 单引号在SQL中用于包裹字符串常量，Jinja渲染后会将环境变量的值作为字符串传入，再通过::bigint/::varchar转换为对应数据类型。
• 补充select关键字确保CTE的SQL结构合法，避免语法报错。

内容的提问来源于stack exchange，提问作者naga satish