Combing dbtutils "surrogate key" with "get filtered columns in relation" produces new warning

[jeffsk]

Environment:
dbt 1.3, dbt could beta, snowflake, windows

packages:
  - package: dbt-labs/dbt_utils
    version: 0.9.2

Issue:
doing this: {{ dbt_utils.surrogate_key(dbt_utils.get_filtered_columns_in_relation( source('source', 'table'))) }}
Produces this Warning:

the surrogate_key macro now takes a single list argument instead of multiple string arguments. Support for multiple string arguments will be deprecated in a future release of dbt-utils. The dbt_.foo_bar model triggered this warning.

The warning makes a lot of sense to me. But I’m having trouble fixing it…

Adding square brackets like this: {{ dbt_utils.surrogate_key(dbt_utils.get_filtered_columns_in_relation([ source('source', 'table'))]) }}
produces SQL which is not what I’d expect.
By that I mean:
instead of md5(cast(coalesce(cast(field1 as TEXT), '') || '-' || coalesce(cast(field2 as TEXT), '') as TEXT))
I’m getting md5(cast(coalesce(cast(['field1', 'field2'])))

More detailed example:

print get_filtered_columns_in_relation to see what it looks like:

{% set column_list = dbt_utils.get_filtered_columns_in_relation( source('source',  'table')) %}

-- print the column list to see what it looks like
{{column_list}}
-- it prints something that looks like a list, surrounded in square brackets. example ['field1','field2']

--  manually add some square brackets as another test
{% set column_list2 = [dbt_utils.get_filtered_columns_in_relation( source('source',  'table'))] %}

-- print the column list 2 to see what it looks like
{{column_list2}}
-- it prints something that looks like a list, with one list in it.  example [['field1','field2']]

Try to use it in SQL

with example as
(
select *,
{{ dbt_utils.surrogate_key(column_list) }},
-- the above line produces the new warning
{{ dbt_utils.surrogate_key(column_list2) }}
-- the above produces the strange sql which does not coalesce nulls, etc
from
source('source',  'table')
)
select * from example

I hope this makes sense!

[joellabes]

I’m surprised that you’re seeing that warning! get_filtered_columns_in_relation definitely returns a list; the you showed with column_list2 is a list of lists which is not the right input shape.

Could you experiment with a couple of things:

• Try making a list by hand: {% set custom_list = ['item1', 'item2'] %} and pass that into surrogate_key.
• Try using the get_filtered_columns_in_relation macro to create a variable which you pass into the surrogate_key macro. There’s no reason it should care, but it might help draw out the cause instead of having so much happening on a single line.

You said that it’s a new warning message; what version of dbt_utils were you using prior to this? This warning has been around for a loooong time, but we recently changed the macro and I wonder whether we are incorrectly triggering it.

[jeffsk]

• Try making a list by hand: {% set custom_list = ['item1', 'item2'] %} and pass that into surrogate_key.

This works as expected.

( Try using the get_filtered_columns_in_relation macro to create a variable which you pass into the surrogate_key macro.

If I’m understanding you correctly, I did show that I tried that in my original post?

select *,
{{ dbt_utils.surrogate_key(column_list) }}, … …

You said that it’s a new warning message; what version of dbt_utils were you using prior to this?

I just looked back at my old projects where I thought I was was doing this, but I was doing something slightly different. I was actually looping over adapter.get_columns_in_relation and passing in a custom list of that result. So in fact I have not done exactly this before.

Brass tacks: If we know get_filtered_columns_in_relation is returning a list, then I can safely ignore this warning / my code will not break in future releases… correct?

[jeffsk]

Hey @joellabes , do you think I’m future proof here?
We know get_filtered_columns_in_relation returns a list, so it feels like I’m good. But I’m embedding this in a lot of places and the warning is scary.

Can you reproduce it? Or, I’m happy to open a support ticket if that is more appropriate.

[joellabes]

Sorry for the silence here! It feels like this is a bug, so an issue on utils would be the thing to do: Issues · dbt-labs/dbt-utils · GitHub

I am out of office for the next 2.5 weeks - if you wanted to do some exploration on your own, I would poke around in here, and see whether you could work out why it’s triggering unnecessarily:

dbt-labs/dbt-utils/blob/0.9.2/macros/sql/surrogate_key.sql#L10-L18

You could also try the utils 1.0 beta which replaces surrogate_key() with generate_surrogate_key() and doesn’t do this checking any more (if you give it a single string it’ll break).

[jeffsk]

I’m happy to report that utils 1.0 beta (generate_surrogate_key) does not have the issue! I get no warnings or errors when running the same code with the new generate_surrogate_key macro.