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


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?

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_%';