snowflake: Materialized view on stage?

Question

Is it possible to create a materialized view on top of Stage (loaded CSV files) or alternate to create MV on direct files without a table?

example:

create or replace materialized view mv_ext_v1 as

select metadata$filename, metadata$file_row_number, $1, $2 from @newstage order by 1;

error: Materialized view not supported over a stage.

Thanks.

Answer 1

You can't define a MATERIALIZED VIEW over a staged file.

But you can define Materialized Views over External Tables, which are the same, but in an extra wrapping.

Answer 2

No, it is not possible. As stated in the Snowflake docs a materialized view is limited to query only a single table and no other objects.

Answer 3

Wonder if you have looked at materialized view over external table (which are just files on stage also): https://docs.snowflake.net/manuals/user-guide/tables-external-intro.html#materialized-views-over-external-tables