dbt_utils 0.8.6 surrogate_key

Help
1

The problem I’m having

When running dbt run with dbt_utils at version 0.8.6. I’m currently unable to upgrade.

with vendors as ( select {{ dbt_utils.surrogate_key([ 'vendor_name', 'vendor_address_street_line1', 'vendor_address_street_line2', 'vendor_address_street_line3', 'vendor_address_city', 'vendor_address_state_code', 'vendor_address_postal_code' ]) }} as id,
Each value is a column name from the vendors list and my understanding is that this would generate a key based off the values with those column names for each entry.

The context of why I’m trying to do this

With the goal of unioning an external source and existing data I’m generating unique Id’s to avoid potential id collisions

What I’ve already tried

I’ve tried a lot of variations with very little change to the error message. Following the examples provided online I think this should work but I also feel like I’m missing some important piece.

Some example code or error messages

The error I get is

22:59:21  Database Error in model int_vendors_merged (models\intermediate\int_vendors_merged.sql)
22:59:21    1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as22:59:21        string
22:59:21    ), '') as
22:59:21        string
22:59:21    )) as id,
22:59:21        vendor_name as name' at line 6
22:59:21    compiled SQL at target\run\data\models\intermediate\int_vendors_merged.sql
2

Can you have a look at the compiled SQL in target\run\data\models\intermediate\int_vendors_merged.sql?

I suspect that your problem is that MySQL doesn’t support string as a data type (from a quick Google, I think text is the right one to use). This version of dbt utils has support for a handful of the most common warehouses hardcoded in; later versions use the cross-database functionality added to dbt Core, however it doesn’t look like dbt-mysql has been updated to support those anyway.

Because of this, there’s two options:

The easy solution: make a copy of the surrogate_key macro

You can copy the bones of the surrogate_key macro into your own project and change references from dbt_utils.type_string() to a hardcoded text.

The fancy solution: dispatch your own MySQL implementation of type_string()

dbt supports this natively: check out the docs on dispatch, especially setting a search_order for dispatch. It’ll be something like this:

{% macro mysql__type_string() %}
    text
{% endmacro %}

and in dbt_project.yml:

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['my_project', 'dbt_utils']