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

**URL:** <https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132>\
**Category:** Help\
**Tags:** jinja, dbt-utils\
**Created:** [October 19, 2022, 4:27pm UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132 "2022-10-19T16:27:04Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeffsk](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jeffsk/32/1250_2.png) [@jeffsk](https://discourse.getdbt.com/u/jeffsk)\
**Post date:** [October 19, 2022, 4:27pm UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132/1 "2022-10-19T16:27:04Z")

</div>

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

```auto
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:

```auto
{% 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

```auto
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!

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [October 20, 2022, 12:13am UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132/4 "2022-10-20T00:13:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jeffsk](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jeffsk/32/1250_2.png) [@jeffsk](https://discourse.getdbt.com/u/jeffsk)\
**Post date:** [October 20, 2022, 1:25pm UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132/5 "2022-10-20T13:25:47Z")

</div>

> - 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?

---

<div class="post-metadata">

**Author:** ![jeffsk](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jeffsk/32/1250_2.png) [@jeffsk](https://discourse.getdbt.com/u/jeffsk)\
**Post date:** [November 4, 2022, 2:20am UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132/6 "2022-11-04T02:20:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [November 4, 2022, 5:23am UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132/7 "2022-11-04T05:23:28Z")

</div>

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](https://github.com/dbt-labs/dbt-utils/issues)

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:

> <https://github.com/dbt-labs/dbt-utils/blob/0.9.2/macros/sql/surrogate_key.sql#L10-L18>

You could also try the [utils 1.0 beta](https://github.com/dbt-labs/dbt-utils/releases/tag/1.0.0-b2) 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).

---

<div class="post-metadata">

**Author:** ![jeffsk](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jeffsk/32/1250_2.png) [@jeffsk](https://discourse.getdbt.com/u/jeffsk)\
**Post date:** [November 4, 2022, 7:34pm UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132/8 "2022-11-04T19:34:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [November 11, 2022, 7:35pm UTC](https://discourse.getdbt.com/t/combing-dbtutils-surrogate-key-with-get-filtered-columns-in-relation-produces-new-warning/5132/9 "2022-11-11T19:35:41Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.