column_list in views are displayed as manadory

Question

In recent day, <column_list> is added automatically to views, eventhough the parameter is documented as optional: https://docs.snowflake.com/en/sql-reference/sql/create-view.html . This causes views that are defined as

create view my_view
as 
select *
from tbl

to throw an error, if a new field is added to a table, unless the view is refreshed. Is there a way to define <column_list> as optional?

Answer 1

This behavior is by designand it is decribed at CREATE VIEW - Usage Notes:

• View definitions are not dynamic. A view is not automatically updated if the underlying sources are modified such that they no longer match the view definition, particularly when columns are dropped

To reporduce the case:

CREATE OR REPLACE TABLE t AS SELECT 1 AS c;

CREATE VIEW v_t AS SELECT * FROM t;

SELECT * FROM v_t;

ALTER TABLE t ADD COLUMN d INT;

SELECT * FROM v_t;
-- SQL compilation error: 
-- View definition for 'PUBLIC.V_T' declared 1 column(s), 
-- but view query produces 2 column(s).