CODEWITHSUNDEEP
sqljinja2dbt
user2587676
user2587676

Reputation: 127

Using DBT to create Join queries, but result omits some columns

I have the following code that uses a right join to connect my data from Table 1 to Table 2. DBT compiled the code successfully without errors but I'm not getting the columns I need...

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

with incremental_salesorder as (
  select * from {{ source('db_warehouse', 'sale_order_line') }} 
),

final as (
  select 
    distinct incremental_salesorder.product_code_cust, 
    incremental_salesorder.order_id as id,
    incremental_salesorder.create_date, 
    incremental_salesorder.name as product_name, 
    incremental_salesorder.product_name_cust, 
    sale_order.name as sale_order_ref
  from incremental_salesorder 
  right join {{ source('db_warehouse', 'sale_order')}} using (id)
  ORDER BY incremental_salesorder.create_date
)

{% if is_incremental() %}
  where incremental_salesorder.create_date >= (select max(create_date) from {{ this }} )

{% endif %}

select * from final

incremental_salesorder.order_id and incremental_salesorder.name are not in the results after the code compiled successfully

enter image description here

What am I doing wrong here... ?

Upvotes: 1

Views: 3777

Answers (1)

user2587676
user2587676

Reputation: 127

Rookie mistake:

Ensure that the defined model name is the same:

models:
    dbt_test:
      # Applies to all files under models/example/
        example:
            materialized: view
            +schema: staging
            +enabled: false
        sales_order_unique_incremental: <- this line must match the folder name
            materialized: table
            +schema: datastudio

enter image description here

I completely missed the warning. Once this was corrected I was able to compile the query and got the results I needed. In case anyone needs an example of how to do a join, this is a working method :)

Upvotes: 2

Related Questions