Materialized View on Snowflake

Can I use Oracle materialized view script and use it as a simple view to load into a temporary table and then use this temporary table to create a materialized view on top of it?

No, this won't work. A materialized view in Snowflake cannot be based on another view. But don't despair, just because you needed a materialised view in Oracle does not mean that you will need one in Snowflake ! On the contrary, it is typical in scenarios where a materialized view was needed in traditional RDBMS, that no special handling is required in Snowflake due to it's superior performance. Snowflake recommends the following considerations when deciding to use a materialized or regular view:

• Create a materialized view when all of the following are true:

  • The query results from the view don’t change often. This almost always means that the underlying/base table for the view doesn’t change often, or at least that the subset of base table rows used in the materialized view don’t change often.

  • The results of the view are used often (typically significantly more often than the query results change).

  • The query consumes a lot of resources. Typically, this means that the query consumes a lot of processing time or credits, but it could also mean that the query consumes a lot of storage space for intermediate results.

• Create a regular view when any of the following are true:

  • The results of the view change often.

  • The results are not used often (relative to the rate at which the results change).

  • The query is not resource intensive so it is not costly to re-run it.