CODEWITHSUNDEEP
snowflake-cloud-data-platform
sridark
sridark

Reputation: 81

snowflake: Materialized view on stage?

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.

Upvotes: 0

Views: 867

Answers (3)

Seeling Cheung
Seeling Cheung

Reputation: 157

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

Upvotes: 0

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2870

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.

Upvotes: 2

manuelschipper
manuelschipper

Reputation: 137

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.

Upvotes: 0

Related Questions