Internal SQL error (000603 (XX000)) due to 300010:2077141494

Question

I am getting the following error in dbt, using snowflake and I can't figure out what the issue is.

Database Error in model stg_bank_balances2 (models/staging/cas/vpapay/finance/stg_bank_balances.sql)
  000603 (XX000): SQL execution internal error:
  Processing aborted due to error 300010:2077141494; incident 5570604.
  compiled SQL at target/run/cas_datawarehouse/staging/cas/vpapay/finance/stg_bank_balances.sql 

I have a staging table that is running 100% when I open the file and run it manually. However when I run it with dbt run --models +stg_bank_balances then I get this error... any ideas?

---

Compiled SQL code:

with
 __dbt__CTE__dw_bank_balance_base as (
with
 source as (select * from CAS_RAW.BANK_BALANCE_INFORMATION_FOR_DATAWAREHOUSE.FACILITY_DATA),

renamed as (
    select 
        to_date(date) as date
        ,FACILITY_BALANCE as facility_balance
        ,FACILITY_LIMIT as facility_limit
        ,LVR as loan_to_value_ratio_expected
        ,UNENCUMBERED_CASH as unencumbered_cash
    from source
)

select *
 from renamed
),data_sheet as ( select * 
                    ,row_number() over (order by date) as row_num
                from __dbt__CTE__dw_bank_balance_base
                ),
calendar as ( select * 
                from ANALYTICS.dev_avanwyk.stg_calendar 
                where date >= (select min(date) from data_sheet)
                    and date <= current_date()
                ),

creating_leads as (
    select a.*
        ,a.date as date_from
        ,case
            when b.date is null then current_date()
          else b.date
         end as date_to
     from data_sheet a
     left join data_sheet b on a.row_num = b.row_num-1
),

renamed as (

    select cal.date as cal_date
        ,ds.date_from, ds.date_to
        ,ds.facility_balance
        ,ds.facility_limit
        ,ds.loan_to_value_ratio_expected
        ,ds.unencumbered_cash

    from calendar cal
    left join creating_leads ds on 
                                ds.date_from <= cal.date
                                and 
                                cal.date < ds.date_to

)

select *
 from renamed

Answer 1

Your cte names are the same, try using in your models unique cte (common table expression) names. You can see you are referencing twice a cte called "renamed". Try changing this and write back what is Snowflake spitting out.

Answer 2

I think Mincho is right.

The first thing to note is that this is a Database Error (docs) — this means that Snowflake is returning the error, and dbt is just passing it on.

Here, Snowflake is having difficulty because you have two CTEs (common table expressions) with the same name — renamed. It looks like you have an upstream model named dw_bank_balance_base that is ephemeral, so it's being injected as a CTE.

You can:

• Rename one of your renamed CTEs to something else
• Make dw_bank_balance_base a view or table by changing the materialized config

Let me know if that fixes it!

Answer 3

Found the issue - dbt doesn't want me joining a table to itself. Hence I created another CTE with the prev_row_num = row_num -1 to facilitate this.

with
 __dbt__CTE__dw_bank_balance_base as (
with
 source as (select * from CAS_RAW.BANK_BALANCE_INFORMATION_FOR_DATAWAREHOUSE.FACILITY_DATA),

renamed as (
    select 
        to_date(date) as date
        ,FACILITY_BALANCE as facility_balance
        ,FACILITY_LIMIT as facility_limit
        ,LVR as loan_to_value_ratio_expected
        ,UNENCUMBERED_CASH as unencumbered_cash
    from source
)

select *
 from renamed
),data_sheet as ( select * 
                    ,row_number() over (order by date) as row_num
                    ,(row_number() over (order by date))-1 as prev_row_num
                from __dbt__CTE__dw_bank_balance_base
                ),
data_sheet1 as ( select * 
                    ,(row_number() over (order by date))-1 as prev_row_num
                from __dbt__CTE__dw_bank_balance_base
                ),
calendar as ( select * 
                from ANALYTICS.dev_avanwyk.stg_calendar 
                where date >= (select min(date) from data_sheet)
                    and date <= current_date()
                ),

creating_leads as (
    select 
         a.date as date_from
        ,a.facility_balance
        ,a.facility_limit
        ,a.loan_to_value_ratio_expected
        ,a.unencumbered_cash
        ,case
            when b.date is null then current_date()
          else b.date
         end as date_to
     from data_sheet a
     left join data_sheet1 b on a.row_num = b.prev_row_num
),

staging as (

    select cal.date as cal_date
        ,ds.date_from
        , ds.date_to
        ,ds.facility_balance
        ,ds.facility_limit
        ,ds.loan_to_value_ratio_expected
        ,ds.unencumbered_cash

    from calendar cal
    left join creating_leads ds on 
                                ds.date_from <= cal.date
                                and 
                                cal.date < ds.date_to

)

select *
 from staging