# dbt macro-using dbt\_utils.union\_relations to union n number of tables, however not able to make relation parameter for input

**URL:** <https://discourse.getdbt.com/t/dbt-macro-using-dbt-utils-union-relations-to-union-n-number-of-tables-however-not-able-to-make-relation-parameter-for-input/13277>\
**Category:** Help\
**Tags:** jinja, snowflake, macros, dbt-core\
**Created:** [May 10, 2024, 4:45am UTC](https://discourse.getdbt.com/t/dbt-macro-using-dbt-utils-union-relations-to-union-n-number-of-tables-however-not-able-to-make-relation-parameter-for-input/13277 "2024-05-10T04:45:34Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![nehadarwin](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/nehadarwin/32/3992_2.png) [@nehadarwin](https://discourse.getdbt.com/u/nehadarwin)\
**Post date:** [May 10, 2024, 4:45am UTC](https://discourse.getdbt.com/t/dbt-macro-using-dbt-utils-union-relations-to-union-n-number-of-tables-however-not-able-to-make-relation-parameter-for-input/13277/1 "2024-05-10T04:45:34Z")

</div>

Hello Readers,  
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-

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

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

I went thru this https://discourse.getdbt.com/t/unioning-identically-structured-data-sources/921
do we need to have source defined in yml?
```