Has anyone tried to pass a dynamic list to the merge_exclude_columns option in the config block?

Help
1

Hi,

We are using the incremental_strategy = ‘merge’ for most of our models.
But, in some models, we want to exclude several columns from the update sentence. so we simply use the merge_exclude_columns option in the config block and pass it a list.
for example [‘Col1’, ‘Col2’, …]

The problem starts when we try to build this list dynamically. for example, using a macro that returns every column in the table beside those that start with “TEST_”.
the macro creates a list of column names and quotes each element with a single quote.

When we pass this dynamic list to merge_exclude_columns, dbt ignores it and updates every column in the table.
If I Hard-coding the same list and use it with the merge_exclude_columns, it works!

Has anyone tried it? what can be the reason dbt can’t work with a dynamic variable?

2

Hey @dotan.gabay, I haven’t done this myself but just a thought: you’re using a macro to generate a comma separated string, but are you then adding square brackets around this to create a list? Postgres syntax would be something like:

SELECT '[' || '''' || STRING_AGG(column_name,''',''') || '''' || ']'
  FROM information_schema.columns 
WHERE table_schema = 'schema'
AND table_name = 'table'
AND column_name NOT LIKE 'TEST_%';
3

Hi! I noticed the same.
I create the dynamic list in jinja with defining the list
{% set list = [] %}
and then appending certain columns, so no Postgresql involved for me.

{% list.append('column_name') %}

The created list looks right, and when I output that in compile and uses that string instead in the merge_exclude_columns, it works as expected.

4

Thank you!
It indeed works right for me if I pass the compiled string. I tried to put the list name (actually, the variable name) in the config block. and that doesn’t work