dbt jinja for column missing

Help
1

The problem I’m having

with dt as
(
select * from (
select 0 as po_id, 2 as part_id,‘B’ as record_type, 10 as rate, ‘20-May-2024’::date as ship_date union all
select 1 as po_id, 2 as part_id,‘A’ as record_type, 10 as rate, ‘20-May-2024’::date as ship_date union all
select 1 as po_id, 2 as part_id,‘B’ as record_type, 20 as rate, ‘21-May-2024’::date as ship_date union all
select 1 as po_id, 3 as part_id,‘A’ as record_type, 15 as rate, ‘22-May-2024’::date as ship_date union all
select 2 as po_id, 2 as part_id,‘A’ as record_type, 15 as rate, ‘23-May-2024’::date as ship_date union all
select 2 as po_id, 3 as part_id,‘C’ as record_type, 15 as rate, ‘24-May-2024’::date as ship_date union all
select 4 as po_id, 2 as part_id,‘B’ as record_type, 15 as rate, ‘25-May-2024’::date as ship_date
)
)
select *,
lag(PART_ID ) over (order by po_id, part_id ) as prv_PART_ID,
lag(PO_ID ) over (order by po_id, part_id ) as prv_PO_ID,
case when record_type = ‘A’ then ship_date else prv_ship_date end as v_A_ship_date,
lag(v_A_ship_date ) over (order by po_id, part_id ) as prv_ship_date
from dt;

The problem is for the first row, the column prv_ship_date doesnt exist and dbt/snowflake is returning an invalid identifier error. Is there any option in Jinja to default NULL if a column is missing ? (not column value).

so for first row - po=0, the ship_date should be null

2

You can get the columns that exist in a relation by using adapter.get_columns_in_relation, but it won’t work on a CTE because it introspects the information schema.

The actual reason you’re having troubles is the circular logic of:

  • including prv_ship_date in your v_A_ship_date case statement
  • including v_A_ship_date in your prv_ship_date column definition.

Your definition of v_A_ship_date can’t contain prv_ship_date

1 Like