Introduction
External tables registered in Unity Catalog allow Databricks users to query data stored in external cloud storage (AWS S3, Azure ADLS, GCS) while benefiting from centralized governance and access control. However, users may fail to access external tables due to missing permissions, incorrect storage credentials, or metastore misconfigurations.
🚨 Common issues when accessing Unity Catalog external tables:
- Tables are visible but cannot be queried (
SELECT
fails). - Permission errors when querying tables.
- Table metadata exists, but data is inaccessible.
- Storage credential errors for AWS S3, Azure ADLS, or GCS.
This guide provides troubleshooting steps and fixes to resolve access issues with Unity Catalog external tables.
1. Check If the Table Exists in Unity Catalog
Symptoms:
- Error: “Table not found in catalog.”
SHOW TABLES IN my_catalog.my_schema;
returns an empty result.
Causes:
- The table was not successfully created or registered.
- The table was created in a different catalog or schema.
- The user does not have permission to view tables in the catalog.
Fix:
✅ Verify the table exists in Unity Catalog:
SHOW TABLES IN my_catalog.my_schema;
✅ Check if the table exists in another schema/catalog:
SHOW TABLES IN hive_metastore.default;
✅ Check for missing permissions:
GRANT USAGE ON CATALOG my_catalog TO `user@example.com`;
GRANT SELECT ON TABLE my_catalog.my_schema.my_table TO `user@example.com`;
✅ If the table is missing, recreate it:
CREATE EXTERNAL TABLE my_catalog.my_schema.my_table
LOCATION 's3://my-bucket/my-folder/'
AS SELECT * FROM hive_metastore.default.my_table;
2. Verify Storage Credentials for External Tables
Symptoms:
- Error: “Access Denied: Could not access the storage location.”
- Tables exist but querying returns permission errors.
Causes:
- Unity Catalog requires an explicit storage credential for external tables.
- The Databricks workspace does not have access to the cloud storage bucket/container.
- Incorrect IAM role (AWS) or Azure AD permissions.
Fix:
✅ Check the storage credential assigned to the table:
SHOW EXTERNAL LOCATIONS;
SHOW STORAGE CREDENTIALS;
✅ Grant Unity Catalog permission to access the storage location:
GRANT READ FILES ON EXTERNAL LOCATION my_storage_location TO `user@example.com`;
✅ For AWS S3, ensure the IAM role has the right permissions:
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:ListBucket"],
"Resource": ["arn:aws:s3:::my-bucket", "arn:aws:s3:::my-bucket/*"]
}
✅ For Azure ADLS, ensure the storage container has the right role assignments:
az role assignment create --assignee <databricks-service-principal> --role "Storage Blob Data Reader" --scope /subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Storage/storageAccounts/<storage-name>
✅ For Google Cloud Storage, ensure Databricks has the right IAM permissions:
gcloud projects add-iam-policy-binding my-project \
--member="serviceAccount:databricks-sa@my-project.iam.gserviceaccount.com" \
--role="roles/storage.objectViewer"
3. Validate Unity Catalog Permissions for the External Table
Symptoms:
- Error: “Permission Denied: User does not have access to this table.”
- The table is visible but cannot be queried.
Causes:
- User lacks the necessary Unity Catalog permissions to read external tables.
- The catalog, schema, or table is restricted to specific roles.
Fix:
✅ Check current grants for the table:
SHOW GRANTS ON TABLE my_catalog.my_schema.my_table;
✅ Grant required permissions to users/groups:
GRANT SELECT ON TABLE my_catalog.my_schema.my_table TO `user@example.com`;
GRANT USAGE ON SCHEMA my_catalog.my_schema TO `user@example.com`;
GRANT USAGE ON CATALOG my_catalog TO `user@example.com`;
✅ If using groups, assign the correct roles:
GRANT SELECT ON TABLE my_catalog.my_schema.my_table TO `data_engineers`;
4. Check Cluster and SQL Warehouse Compatibility
Symptoms:
- Error: “This cluster does not support Unity Catalog.”
- Unity Catalog tables do not appear in query results.
Causes:
- The Databricks cluster is not Unity Catalog-enabled.
- The SQL Warehouse does not have access to Unity Catalog tables.
Fix:
✅ Check if the cluster supports Unity Catalog:
- Go to Databricks UI → Clusters → Advanced settings → Check if Unity Catalog is enabled.
- If not enabled, create a new cluster with Unity Catalog support.
✅ For SQL Warehouses, ensure Unity Catalog is enabled:
- Go to Databricks UI → SQL Warehouses → Enable Unity Catalog.
✅ Restart the cluster or SQL Warehouse after enabling Unity Catalog.
5. Ensure Table Schema Matches Storage Format
Symptoms:
- Error: “Schema mismatch: Table expects different schema than storage format.”
- Queries return null values or incorrect results.
Causes:
- The table schema does not match the underlying data format.
- Partitioning or column names differ between the table and the actual data.
Fix:
✅ Check the schema of the registered table:
DESCRIBE TABLE my_catalog.my_schema.my_table;
✅ Compare with the actual data schema:
SELECT * FROM my_catalog.my_schema.my_table LIMIT 10;
✅ If schema mismatch exists, recreate the table with the correct schema:
CREATE OR REPLACE EXTERNAL TABLE my_catalog.my_schema.my_table (
id INT,
name STRING,
created_at TIMESTAMP
)
USING PARQUET
LOCATION 's3://my-bucket/my-folder/';
✅ If using partitions, explicitly define them:
ALTER TABLE my_catalog.my_schema.my_table RECOVER PARTITIONS;
6. Troubleshooting Step-by-Step
Step 1: Verify If the Table Exists in Unity Catalog
SHOW TABLES IN my_catalog.my_schema;
- If missing, register the table again.
Step 2: Check Storage Credentials and External Locations
SHOW EXTERNAL LOCATIONS;
SHOW STORAGE CREDENTIALS;
- If missing, create a storage credential and assign permissions.
Step 3: Validate User Permissions for Unity Catalog Tables
SHOW GRANTS ON TABLE my_catalog.my_schema.my_table;
- If missing, grant
USAGE
andSELECT
permissions.
Step 4: Ensure Clusters and SQL Warehouses Support Unity Catalog
- Restart the Unity Catalog-enabled cluster or SQL Warehouse.
Step 5: Check Schema Mismatches and Fix Partitioning Issues
DESCRIBE TABLE my_catalog.my_schema.my_table;
- If different, recreate the table with the correct schema.
Best Practices for Managing External Tables in Unity Catalog
✅ Use Unity Catalog-Enabled Clusters and SQL Warehouses
- Legacy clusters do not support Unity Catalog tables.
✅ Grant the Right Permissions for External Tables
GRANT SELECT ON TABLE my_catalog.my_schema.my_table TO `user@example.com`;
✅ Ensure Storage Credentials Are Properly Configured
GRANT READ FILES ON EXTERNAL LOCATION my_storage_location TO `user@example.com`;
✅ Monitor Logs for Storage Access Errors
- Check AWS CloudTrail, Azure Monitor, or GCP Logs for storage permission issues.
Conclusion
If external tables registered with Unity Catalog are not accessible, check:
✅ The table exists in Unity Catalog.
✅ Storage credentials are properly assigned.
✅ User permissions are correctly configured.
✅ Clusters and SQL Warehouses support Unity Catalog.
✅ Schema and data formats match the table definition.
By following these steps, you can resolve access issues and successfully query external tables in Unity Catalog.