Snowflake - Combine external tables into one table

Question

I have 20 snowflake External Tables, let's say they are table1, table2 ... table20, all of them have the same structure. each one of them points to their own locations (parquet files, same bucket different folders) on S3 via external stage. now I need to combine them into one view. the SQL I wrote is very simple

select * from table1
union
select * from table2
....
select * from table20

however, the performance is very slow. from what I know snowflake does not support multiple external stages, and external stage doesn't support multiple external locations

my question is: Is there a way to increase the performance? to make the view perform al least like a single external table?

thanks

Answer 1

Materialized View can be solution in your scenario , by creating them on external tables which are referred to external stages can help in improving performance of queries. Here is link to documentation which refers to when to create materialized view, pros and cons of materialized view. https://docs.snowflake.com/en/user-guide/views-materialized.html

Answer 2

Why don't you use the file pattern for all the files rather making it multiple tables. You can have table for each file location or you can have one table with regular expression so all the data could be loaded if the structure is same.

CREATE TABLE IF NOT EXISTS my_db.my_schema.my_ext_table (
    col1 STRING COMMENT 'col-1-comment',
    ....
)
WITH LOCATION = @/path/to/file/in/s3
FILE_FORMAT = 'parquet_file_format'
AUTO_REFRESH = true
PATTERN = '.*/.*/.*[.]parrquet'