SQL / Snowflake - Select if Table Exists

Question

I am working with N tables in a Snowflake warehouse where N will grow arbitrarily large overtime.

I am creating a SQL statement dynamically based on the list of tables, the SQL script looks something like:

SELECT * FROM table_1
UNION ALL
SELECT * FROM table_2
UNION ALL
...
SELECT * FROM table_n;

The problem is that the list of tables is itself set dynamically and some tables may not exist. In the case a table does not exist I want to simply ignore it in my select statement. So something like:

SELECT * FROM table_1 IF EXISTS table_1
UNION ALL
SELECT * FROM table_2 IF EXISTS table_2
UNION ALL
...
SELECT * FROM table_n IF EXISTS table_n;

Does anyone know if this behavior is achievable in SQL / Snowflake?

For the record, I am using the templating system dbt to loop over each table and join them together in a single table.

I am a beginner with SQL - any chance you can provide an extended answer? — Daniel
Loop over all the schema in a database with show statements, like (show tables in schema <DB_Name>.<Schema_Name>. You would have list of table from it and input this list to your script. — FKayani

Nat Taylor Nat Taylor · Accepted Answer · 2021-05-26T21:26:32

To check if table exists in a database you need to use a SELECT statement on the information_schema.tables.

I think something like this should work in dbt but I did not test it.

{% for table_name in run_query("select table_name from information_schema.tables where table_name in ('my', 'list', 'of', 'n', 'tables')").rows %}
    {% if not loop.first %}
        UNION ALL
    {% endif %}
        SELECT * FROM table_name
    {% endif -%}
{% endfor %}

Since you said you are a beginner with SQL, this seems like a path to pain. You will need to guarantee that all the tables in your last have identical schemas. The results set could be very large depending on how many rows are in the tables. You also said "join" which means something speficic in SQL when it seems like you meant union

SQL / Snowflake - Select if Table Exists

1 Answers