Problem

When you run a query with the SELECT command on a Unity Catalog (UC) view, the query fails with the following error.

com.databricks.backend.common.rpc.SparkDriverExceptions$SQLExecutionException: java.util.concurrent.ExecutionException: com.databricks.sql.managedcatalog.UnityCatalogServiceException: [RequestId=<request-id> ErrorClass=INVALID_PARAMETER_VALUE] credential requested for table <table-id> (<table-name>) is not under dependent <view-id>(<view-name>)

Cause

When creating a view with a query that includes an unused common table expression (CTE), UC fails to properly handle the credentials for tables referenced in those unused CTEs. This results in an INVALID_PARAMETER_VALUE error when you attempt to query the view.

Solution

Upgrade your Databricks Runtime version to 14.1 or above. 

If upgrading Databricks Runtime is not feasible, modify the view's defining query to remove any unused CTEs. Rewrite the query to only include CTEs that are actually used. You can use the following example code.

create or replace view <catalog>.<schema>.<view> as
with <cte-in-use> as (select * from <table>)
select * from <cte-in-use>