search

dbt-msft / tsql-utils

dbt-utils for the dbt-msft family of packages
MIT License
25 stars 25 forks source link

surrogate_key does not work for Azure Synapse #30

Closed dataders closed 2 years ago

dataders commented 3 years ago

error message:

Cannot find data type 'string'. , it has to be varchar for example

select
    md5(cast(concat(coalesce(cast(ID as 
    string
), '')) as 
    string
)) as Key
dataders commented 2 years ago

@alittlesliceoftom @davidclarance is this still an issue for y'all?

davidclarance commented 2 years ago

@swanderz not sure what the context was in the original issue but I just checked and dbt_utils.surrogate_key has been working well for us. Note that in prod we're not on the latest versions. Here are the versions we're using:

dbt-core == 0.18.1 dbt-synapse == 0.18.1 dbt-utils == 0.6.5 tsql-utils == 0.6.7

alittlesliceoftom commented 2 years ago

We replicated the issue on the combination of:

dbt-synapse = 0.21.0 tsql-utils = 0.8.1

When we investigated we discovered that the issue was not following updated installation instructions for the library.

This is because the documented method for assigning dispatch updated in 0.20. to require the following in your dbt_project.yml, it wasn't needed before: 

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

https://github.com/dbt-msft/tsql-utils/commit/5a109afd6d088e4957f65eb96164a5634c8b6a6f

In general this issue is a sign of your dbt compiler selecting dbt_utils instead of tsql_utils. So any time that someone finds an issue like this they should first check they have followed all dispatch instructions in readme.

Hope that helps future people !

Closing this as haven't seen evidenced versions not related to following setup instructions to get dispatch right.