CODEWITHSUNDEEP
snowflake-cloud-data-platformdbtdbt-utils
BigBoy1337
BigBoy1337

Reputation: 5003

How to use dbt_utils.union_relations in snowflake?

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?

Upvotes: 1

Views: 2465

Answers (2)

Erik
Erik

Reputation: 36

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.

Upvotes: 2

Anders Swanson
Anders Swanson

Reputation: 3961

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.

Upvotes: 1

Related Questions