将dbt视图转为增量模型时Left Join出现Unexpected Left错误

问题描述 #

将以view物化的dbt模型转换为增量模型时，执行出现语法错误，报错信息为：意外的LEFT

错误代码如下：

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

SELECT
    SRC_FILE_ROW_NO_ID,
    LOAD_TS,
    RFD_DESTINATION_CD,
    RFD_ORIGIN_CD, 
     CASE
        WHEN fltd.RFD_ORIGIN_CD = stno.stn_icao_cd 
        THEN stno.stn_iata_cd
        ELSE fltd.RFD_ORIGIN_IATA_CD
    END :: VARCHAR(20) AS RFD_ORIGIN_IATA_CD,  

      CASE
        WHEN fltd.RFD_DESTINATION_CD = stnd.stn_icao_cd 
        THEN stnd.stn_iata_cd
        ELSE fltd.RFD_DESTINATION_IATA_CD
    END :: VARCHAR(20) AS RFD_DESTINATION_IATA_CD, 
    SRC_FILE_NM_TXT

FROM {{ ref('process_data') }} fltd

{ % if is_incremental() % }
     WHERE LOAD_TS > (SELECT MAX(LOAD_TS) FROM {{ this }})
{ % endif % }

LEFT OUTER JOIN {{ source('base', 'REF_STATIONS')}} stno  ON fltd.RFD_ORIGIN_CD = stno.stn_icao_cd
LEFT OUTER JOIN {{ source('base', 'REF_STATIONS')}} stnd ON fltd.RFD_DESTINATION_CD = stnd.stn_icao_cd 
WHERE TRY_TO_TIMESTAMP(Time_Stamp_ts) IS NOT NULL

错误原因 #

1. SQL语法顺序错误：SQL中WHERE子句必须放在所有JOIN子句之后，不能在FROM表之后直接插入WHERE（即使是增量判断的条件），否则会破坏SQL结构，导致解析器无法识别后续的LEFT JOIN。
2. dbt模板标签格式错误：模板标签{ % if is_incremental() % }和{ % endif % }中存在多余空格，正确格式应为{% if is_incremental() %}和{% endif %}，否则dbt无法正确解析增量逻辑。

修正后的代码 #

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

SELECT
    SRC_FILE_ROW_NO_ID,
    LOAD_TS,
    RFD_DESTINATION_CD,
    RFD_ORIGIN_CD, 
     CASE
        WHEN fltd.RFD_ORIGIN_CD = stno.stn_icao_cd 
        THEN stno.stn_iata_cd
        ELSE fltd.RFD_ORIGIN_IATA_CD
    END :: VARCHAR(20) AS RFD_ORIGIN_IATA_CD,  

      CASE
        WHEN fltd.RFD_DESTINATION_CD = stnd.stn_icao_cd 
        THEN stnd.stn_iata_cd
        ELSE fltd.RFD_DESTINATION_IATA_CD
    END :: VARCHAR(20) AS RFD_DESTINATION_IATA_CD, 
    SRC_FILE_NM_TXT

FROM {{ ref('process_data') }} fltd
LEFT OUTER JOIN {{ source('base', 'REF_STATIONS')}} stno  ON fltd.RFD_ORIGIN_CD = stno.stn_icao_cd
LEFT OUTER JOIN {{ source('base', 'REF_STATIONS')}} stnd ON fltd.RFD_DESTINATION_CD = stnd.stn_icao_cd 
WHERE TRY_TO_TIMESTAMP(Time_Stamp_ts) IS NOT NULL
{% if is_incremental() %}
    AND LOAD_TS > (SELECT MAX(LOAD_TS) FROM {{ this }})
{% endif %}

修改说明 #

• 将所有LEFT JOIN子句移至FROM之后、WHERE之前，符合SQL语法规范。
• 修正dbt模板标签的空格问题，确保增量逻辑被正确解析。
• 将增量判断的LOAD_TS条件与原有WHERE条件用AND连接，保证过滤逻辑的完整性。

内容的提问来源于stack exchange，提问作者Abiodun Adeoye