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?

1. do you already have `dbt_utils` installed as a package? 2. what happens if you remove (temporarily) the config block? 3. why are you only trying to union one relation? — Anders Swanson, Nov 22 '21 at 23:37
I do have dbt_utils installed as a package via the packages.yml version 0.7.4 from dbt-labs. If I remove the conflig block it generates the same exact query. I originally had more models that I was unioning and removed those. Perhaps I should just skip this unioning then and just select those columns from this table — BigBoy1337, Nov 23 '21 at 16:14

score 2 · Accepted Answer · edited Nov 26 '21 at 19:29

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.

score 1 · Answer 2 · answered Nov 23 '21 at 17:07

My guess is that the macro is failing to fetch the column names from the relations you're naming (macro source code) As to why I'm not sure. Have you called dbt run -m blaze_inventory_stg already?

To prove my hypothesis, make a new model file, big_boy.sql with the following code, then run dbt run -m big_boy. My guess is that the columns won't resolve.

-- big_boy.sql
{% if execute %}
  {%- set cols = adapter.get_columns_in_relation(ref('blaze_inventory_stg')) -%}
  {{ log('colums: ' ~ cols, info=True) }}
{% endif %}

SELECT 1 as my_col

If the above model file does indeed fetch and print the columns to the console, then something else is going on. To further debug, I'd recommend renaming the default__union_relations macro linked above to a new name, then invoking the new macro in your model. Then you can intersperse log statements to get more information about where exactly things are going wrong.

Those columns do resolve properly from that template, however they are uppercased by snowflake, something which I had forgotten and which @Erik pointed out in his answer. — BigBoy1337, Nov 25 '21 at 20:25

How to use dbt_utils.union_relations in snowflake?

2 Answers2