Snowflake Database Encountered An Error (More Columns Than Defined)

errors

(Nicholas Wong) #1

Having this error while running your query on Looker?

The Snowflake database encountered an error while running this query. SQL compilation error: View definition for ‘DB’ declared 67 column(s), but view query produces 76 column(s).

The issue is on your database (Snowflake) and not Looker. (Basically, this is a database issue which in turns generate this error on Looker.) The issue is more columns are present than defined. In order to solve this issue, you will need to use FLATTEN. It is a table function that produces a lateral view of a VARIANT, OBJECT, or ARRAY column. The sample code is:

CREATE OR REPLACE VIEW SHOPIFY.SHOPIFY_ORDERS_FLAT AS
SELECT LIST.VALUE AS ITEM_LIST_VALUE,
TRANSACTIONS.*
FROM SHOPIFY_TRANSACTION AS TRANSACTIONS,
LATERAL FLATTEN(INPUT => TRANSACTIONS.ITEMLIST) TRANSACTIONS_ITEM_LIST;

If error persists, you might want to look at the Snowflake documentation at https://docs.looker.com/setup-and-management/database-config/snowflake. It helps to set up 2 fundamental points. Firstly, creating a Looker user on Snowflake and provision access. Secondly, set up a database connection in Looker.