Snowflake SQL Compilation Error: View Definition Declared but view Query Produced

This is speculation, but it sounds like your view is using select x.*, where * means to get all the columns from some table.

Then, the underlying table changes . . . and voila, you might have a problem.

I've just gotten a new query error that I haven't changed anything to. Any advice on what to do?

If the query started to produce errors it means that the defintion of view is no longer "valid/up-to-date". Most likely the underlying table has been altered.

CREATE VIEW

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. For example:

• A view is created referencing a specific column in a source table and the column is subsequently dropped from the table.

• A view is created using SELECT * from a table and any column is subsequently dropped from the table.

In either of these scenarios, querying the view returns a column mismatch error.

Steps to reproduce the scenario:

CREATE OR REPLACE TABLE t(col1 INT, col2 INT);
INSERT INTO t(col1, col2) VALUES (1,1);

CREATE OR REPLACE VIEW v_t AS SELECT * FROM t;

SELECT * FROM v_t;
--COL1  COL2
--1 1

So far so good. Now altering the underlying table by adding new column:

ALTER TABLE t ADD COLUMN col3 INT DEFAULT 3;

SELECT * FROM v_t;

SQL compilation error: View definition for 'V_T' declared 2 column(s), but view query produces 3 column(s).

Recreation of the view and keeping its definition on par with underlying tables should resolve it:

CREATE OR REPLACE VIEW v_t 
COPY GRANTS 
AS 
SELECT * FROM t;
-- using * will work to refresh it but I would not recommend it 
-- and explicitly describe columns instead


CREATE OR REPLACE VIEW v_t 
COPY GRANTS   -- to preserve already granted permissions
AS 
SELECT col1, col2, col3 FROM t;

SELECT * FROM v_t;
-- COL1   COL2    COL3
-- 1      1     3

I found myself with a similar issue this morning. I had copied my query from a txt file I had and pasted it into a worksheet and tired to run it and got the same error. I had used the Table with the definition issue for a join and only for 1 column so I didn't see why it was giving me such an error.

All a look of check tables I commented on the Worksheet the error I was seeing. But I decided to run it again and it worked.

This tells me that Snowflake was using what it had cached but after editing the query it saw it as a new query and re-ran it instead of erroring out when what is in the cache doesn't match the definition.