Dynamic values for check_cols attribute in the snapshot config using dbt-bigquery

The problem I’m having

Unable to execute macro which brings in column names of table from INFORMATION_SCHEMA.TABLES and use it as value for check_cols in the snapshot model configuration.

The context of why I’m trying to do this

As part of my data model, I need to first create snapshots of source tables and use them as basis for rest of processing. However, I need to exclude some of the source columns from snapshot . I need to do this for all the source tables (10+ tables). Hence, I created a macro that can only get columns I require. In event of changes at source i.e. addition of more columns, I don’t want to come back again and revise the logic and let the macro handle it.

What I’ve already tried

I can get this working by putting in hard coded column values. To get the column values dynamically, I have created macro which is causing failure.

Some example code or error messages

Macro I created - get_column_names_excl_meta_cols

{%- macro get_column_names_excl_meta_cols(schema_name, table_name) -%}
    {% set table_query %}
            string_agg("'" || column_name || "'")
        {%- if target.name == 'prod' -%}
            gcp-project-prod.{{ schema_name }}.INFORMATION_SCHEMA.COLUMNS
        {%- elif  target.name == 'test' -%}
            gcp-project-test.{{ schema_name }}.INFORMATION_SCHEMA.COLUMNS
        {%- else -%}
            gcp-project-dev.{{ schema_name }}.INFORMATION_SCHEMA.COLUMNS
        {%- endif -%}
        where table_name = '{{ table_name }}'
            and column_name not in ('meta_ingest_ts')
    {% endset %}

    {%- set result = run_query(table_query).columns[0].values()[0] -%}
    {{return( result )}}
{%- endmacro -%}

my snapshot model using the above macro

{% snapshot store_hist %}
        check_cols=get_column_names_excl_meta_cols('stores', 'store_master')
with base as (
    from {{ src_table('stores', 'store_master') }}
select *
from base

{% endsnapshot %}

Command I am running - dbt snapshot --select store_hist .


Compilation Error in snapshot store_hist (snapshots\store_hist.sql)
‘None’ has no attribute ‘table’

Alternatives explored

I have encapsulated if execute command in my macro get_column_names_excl_meta_cols as follows

{% if execute %}
    {%- set result = run_query(table_query).columns[0].values()[0] -%}
    {{return( result )}}
{% else %}
    {{return( false ) }}
{% endif %}

Putting that in when I run the dbt snapshot --select store_hist command I am getting the following error. I don’t fully understand how this command works. Reading docs, I realize (or am assuming) that this gets utilized when dbt run command works and using that prevents parsing error but I don’t know how it works in the context of dbt snapshot command. Hence, I removed that if execute condition.

Can someone please guide me on this? I am using dbt-core 1.5, dbt-bigquery 1.5 version and using dbt cli.

Please let me know any additional details required. I have also replaced string_agg function with array_agg (as that retrieves the value as array) but to no avail.

Parsing Error at path [‘check_cols’]:
False is not valid under any of the given schemas

Can someone help me with any suggestion/ work around?

I am facing the same issue. Did you get any solutions?