Unexpected Left Errors when Combining Left Join with Incremental Jinga in DBT

Question

I am trying to convert a dbt model materialised as a view to an incremental model where the model joins another table with a left Join but I seem to be getting errors

{{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

I am getting an Error of unexpected LEFT

Answer 1

The error is the compiled SQL which have two WHEREs.

dbt split the dbt run command into 2 steps.

1. dbt will first compile models to real sql in folder of target/compiled.
2. send the compiled sql to execute.

In case of sql running error. We usually can check the compiled sql under folder of target/compiled. Then copy the generated sql and run it directly in data warehouse to get the exact error.

The model shall be able to fixed like change jinga as one of the where clause.

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 % }