Sometimes you are stuck with a slow view in Snowflake. Imagine this scenario:

You’re trying to create an object with a slowpoke view as the source and development is as slow as taking the highway at 8 AM to work.

You cannot edit and improve the view because it is a secure, shared object. The owner of the view is too busy cutting his toenails so no help will be forthcoming from that front.

Bottom line, you’re stuck having to deal with this view that takes ages to show you the result of each iteration of your development efforts.

Is all hope lost? No, I tell thee!

One way to speed up your development is to create a temporary table from the view.

CREATE TABLE SUPER_FAST_TABLE 
  AS 
SELECT * FROM SLOWPOKE_VIEW
;

Now, why does this work?

A view is basically a stored SQL query. If this query has a lot of expensive operations, like complex JOINs, filters, etc. it will be slow.

Every time you use the view in a query that you are modifying, all the underlying slow operations have to be performed from scratch.

When you create a table from this view, those slow operations do not have to be performed anymore, because the result is materialized as a table. Snowflake stores the table and you can leverage performance enhancing techniques as micro-partitioning, clustering, pruning, etc.

In short, part of the reasons why this works are detailed below:

SELECT * FROM SLOWPOKE_VIEW WHERE COLUMN1 = ‘THIS’

→ All the slow operations have to be performed (complex JOINs, filters, aggregations, etc.)

→ Result of the query

SELECT * FROM SUPER_FAST_TABLE WHERE COLUMN1 = ‘THIS’

→ Result of the query