How to use dbt_utils.union_relations in snowflake?

Question

I have the following code:

{{
    config(
        materialized='table',
        unique_key='dbt_scd_id'
    )
}}

with unioned as (
  {{ dbt_utils.union_relations(
    relations=[
      ref('blaze_inventory_stg'),
    ],
    include=[
      "source_name",
      "location_id_ext",
      "product_name",
      "brand",
      "strain_name",
      "category",
      "product_weight_grams",
      "product_unit_of_measure",
      "purchase_category",
      "quantity",
      "sku",
      "dbt_updated_at",
      "dbt_valid_from",
      "dbt_valid_to",
      "dbt_scd_id"
    ]
  )}}
)

select * from unioned

It produces this sql

      create or replace transient table POS_DATA.dbt_dev.retail_inventory_snapshot_stg  as
      (

with unioned as (
  

        (
            select

                cast('POS_DATA.dbt_dev.blaze_inventory_stg' as 
    varchar
) as _dbt_source_relation,
                

            from POS_DATA.dbt_dev.blaze_inventory_stg
        )

        
)

select * from unioned
      );

Clearly its not getting all of the column names from that table and inserting them. Why is this? I have read here that the dbt_utils.union_relation relies on the information schema to build its query. Perhaps snowflakes information schema is undiscoverable by dbt?

Answer 1

I ran into the same problem - or what seems to be the same problem, also using DBT on Snowflake.

In my case, I had not quoted the columns when creating the sources, so the case was Snowflake default (uppercase). This doesn't match the include list since it is all lowercase.

So, I my case, I first removed the include list (created a copy of the source table). That worked.

Then I tried the include list, but upper cased, and voila - it works as expected.