Snowflake using CTE to create table

Question

Does Snowflake allow you to create a series of CTEs then join them together at the end to create a table?

For example:

with CTE1 as ( SELECT * FROM TABLE1)
,CTE2 AS (SELECT * FROM TABLE2)
,CTE3 AS (SELECT * FROM TABLE3)

CREATE TABLE TABLE_NAME_HERE AS 
SELECT * FROM CTE1 AS 1
LEFT JOIN CTE2 AS 2 ON 1.KEY = 2.KEY
LEFT JOIN CTE3 AS 3 ON 1.KEY = 3.KEY

I'm getting a unexpected 'CREATE'. error

Answer 1

Yes, it is possible:

CREATE TABLE TABLE_NAME_HERE AS 
WITH CTE1 as ( SELECT * FROM TABLE1)
    ,CTE2 AS (SELECT * FROM TABLE2)
    ,CTE3 AS (SELECT * FROM TABLE3)
SELECT *     -- here should be explicit column list to avoid name duplication error
FROM CTE1 AS 1
LEFT JOIN CTE2 AS 2 ON 1.KEY = 2.KEY
LEFT JOIN CTE3 AS 3 ON 1.KEY = 3.KEY;