I am trying to make macro in dbt that reads files from a specific database,schema and unions them.
So as first step I am storing all the table names from information schema in snowflake which has all tables that starts with GENERAL%.After this reading this in for loop and using source function to create relation which can be fed to dbt_utils.union_relations. Looks like this-

{% macro union_files_starting_with_gen() %}
{% set database_name = ‘X_DEV' %}
{% set schema_name = ‘X__AUDIT' %}
{% set prefix = 'GEN%' %}

  {% set file_names_query = "SELECT TABLE_NAME FROM " ~ database_name ~   ".INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" ~ schema_name ~ "' AND TABLE_NAME LIKE '" ~  prefix ~ "'" %}
{% set file_names = run_query(file_names_query) %}
{% do dbt_utils.log_info("Contents of file_names: " ~ file_names) %}

{% set sources = [] %}

{% for file_name_row in file_names %}
    {%- set table_name = file_name_row.TABLE_NAME %}
    {%- set full_table_name = schema_name ~ '.' ~ table_name %}
    {%- do dbt_utils.log_info("Processing table: " ~ full_table_name) %}
    {%- set source_obj = source(schema_name, table_name) %}
      {%- do dbt_utils.log_info("source: " ~ source_obj) %}
    {%- do sources.append(source_obj) %}
    {%- do dbt_utils.log_info("Source object: " ~ source_obj) %}
{% endfor %}

with unioned as (
    {{ dbt_utils.union_relations(relations=sources) }}
)

select * from unioned;

{% endmacro %}

Here till {%- do dbt_utils.log_info(“Processing table: ” ~ full_table_name) %} ,I see values on terminal, however {%- set source_obj = source(schema_name, table_name) %} is not working. What I am looking for is to make relation to pass on to union_relations.
Would appreciate any help!! Thanks

have tried to dry run the macro