Problem

You are querying views created on top of system tables, such as the billing usage table, when you get a scheme mismatch error message.

The error typically manifests as a DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION error, indicating that certain fields in the view do not match the current table schema.

Cause

Views created on top of tables do not support schema evolution by default. When the underlying table's schema is updated (for example, new fields are added), the view's schema remains static, leading to a mismatch between the view and the table it references. This mismatch causes errors when querying the view, as it attempts to cast data to an outdated schema.

Solution

Update to Databricks Runtime 15.4 LTS or above to use schema evolution for views. For details, refer to the CREATE VIEW (AWS | Azure | GCP) documentation.

Then, when creating new views or recreating existing ones, use the WITH SCHEMA EVOLUTION clause. This ensures that the view's schema automatically updates when changes occur in the underlying table. You can use the following example code. 

%sql

CREATE VIEW <your-catalog>.<your-schema>.<your-view-name> WITH SCHEMA EVOLUTION AS
SELECT * FROM <your-table-name> WHERE <your-conditions>

Databricks recommends prioritizing recreating views on frequently updated tables or those crucial to your operations. For views created before you implemented schema evolution, be sure to use the WITH SCHEMA EVOLUTION clause when recreating them to enable automatic schema updates.