Problem

You’re working in Lakehouse Federation and want to read data from Snowflake. You try to access a table directly from Catalog Explorer and encounter the following error message.  

Your request failed with status FAILED: [BAD_REQUEST] SQL compilation error: View definition for '<catalog-name>.<schema-name>.<table-name>' declared X column(s), but view query produces <greater-than-X> column(s).

This issue arises while reading the <catalog-name>.<schema-name>.<table-name> table. This table in Databricks corresponds to a view defined in Snowflake.

Cause

There is a discrepancy between the columns defined in the view and the columns produced by the underlying query in Snowflake. 

The view <catalog-name>.<schema-name>.<table-name> in Snowflake declares X columns, but the underlying query for this view now produces greater than X columns, leading to a mismatch and subsequent SQL compilation error.

Solution

Confirm view definition and column discrepancy

1. First, ensure that <catalog-name>.<schema-name>.<table-name> is indeed a view in Snowflake and not a regular table in Databricks.
2. Run the following commands in Snowflake to validate the view definition and describe the structure of the view. These commands help you confirm the declared columns in the view against those produced by the underlying query.

SHOW CREATE VIEW <catalog-name>.<schema-name>.<table-name>;
DESCRIBE EXTENDED <catalog-name>.<schema-name>.<table-name>;

Modify the view in Snowflake

3. If the mismatch is confirmed, update the view definition to ensure the number of columns declared matches the columns produced.
4. Refer to the Snowflake ALTER VIEW documentation for detailed instructions on how to modify the view.

Synchronize the view definition

After updating the view definition, re-run the REFRESH FOREIGN CATALOG command in Databricks. This step ensures that the view definition and the produced query columns in Snowflake are synchronized, resolving the SQL compilation error.