Jobs fail with TABLE_OR_VIEW_NOT_FOUND after upgrading from hive_metastore to Unity Catalog: names, default catalog and access modes

#Symptoms

After tables were upgraded from hive_metastore to a Unity Catalog catalog:

• Jobs fail with [TABLE_OR_VIEW_NOT_FOUND] The table or view sales.orders cannot be found.
• Or jobs succeed but write to the old hive_metastore table, and reports on the new UC table go stale.
• [UC_NOT_ENABLED] or Unity Catalog is not enabled on this cluster on some job clusters.
• Paths like /mnt/raw/... stop working on new clusters.

#Step 1: Work out how names are resolved

A two-part name sales.orders is resolved against the current catalog. That comes from, in order:

1. USE CATALOG / spark.sql.catalog settings in the session.
2. The cluster's Spark config spark.databricks.sql.initial.catalog.name (if set).
3. The workspace default catalog (admin setting).

Check what a job actually sees, by putting this at the top of a failing notebook:

print(spark.sql("SELECT current_catalog(), current_schema()").first())

If it prints hive_metastore, two-part names still point at the old tables.

#Step 2: Fix names properly: use three-part names

The robust fix is to stop depending on defaults:

-- Before
SELECT * FROM sales.orders;
-- After
SELECT * FROM prod.sales.orders;

In PySpark, parameterise the catalog so dev/test/prod differ only by a job parameter:

dbutils.widgets.text("catalog", "prod")
catalog = dbutils.widgets.get("catalog")

orders = spark.table(f"{catalog}.sales.orders")
orders.write.mode("append").saveAsTable(f"{catalog}.sales.orders_daily")

Find remaining two-part references in your repo:

grep -rnE "(spark\.table|saveAsTable|FROM|JOIN|INTO)\s*\(?\s*['\"]?[a-z_]+\.[a-z_]+['\"]?" --include=*.py --include=*.sql . \
  | grep -v "hive_metastore\.\|prod\.\|dev\." | head -50

It's a rough filter; review the hits by hand.

#Step 3: Short-term: set the default catalog

While code is being updated, set the default so two-part names go to UC:

• Workspace level (admin): Settings > Workspace admin > Advanced > Default catalog for the workspace.
• Per cluster / job cluster (Spark config):

spark.databricks.sql.initial.catalog.name prod

• Per notebook: USE CATALOG prod;

Warning

Changing the workspace default catalog affects every notebook and job that uses two-part names, including ones not yet migrated. Jobs that still need hive_metastore tables will start failing. Change it after the migration of a whole domain, or use per-cluster settings during the transition.

#Step 4: Cluster access mode

Unity Catalog tables need a UC-capable access mode:

Check job clusters:

databricks jobs get <job-id> --output json | jq '.settings.job_clusters[].new_cluster | {spark_version, data_security_mode}'

Set "data_security_mode": "SINGLE_USER" (with single_user_name set to the job's run-as principal) or "USER_ISOLATION" for standard mode, and use DBR 13.3 LTS or later. If code uses sc.parallelize, spark.sparkContext.hadoopConfiguration or similar, standard mode may reject it; use dedicated mode for those jobs.

#Step 5: Permissions for the job's identity

The job's run-as user or service principal needs UC grants (old table ACLs don't carry over):

GRANT USE CATALOG ON CATALOG prod TO `sp-orders-etl`;
GRANT USE SCHEMA, SELECT, MODIFY ON SCHEMA prod.sales TO `sp-orders-etl`;

[INSUFFICIENT_PERMISSIONS] rather than TABLE_OR_VIEW_NOT_FOUND points here. Note that UC sometimes reports a table as not found when you lack USE SCHEMA, so check grants even for "not found" errors:

SHOW GRANTS `sp-orders-etl` ON SCHEMA prod.sales;

#Step 6: Old tables still being written

If you upgraded external tables with SYNC (or UCX), the UC table points at the same storage path as the old Hive table. Writes through either name land in the same files, which is fine. But managed Hive tables copied with CREATE TABLE ... DEEP CLONE or CTAS are separate copies: jobs still writing hive_metastore.sales.orders update the old copy only.

Find which jobs still touch hive_metastore:

SELECT DISTINCT
       entity_type, entity_id, source_table_full_name, target_table_full_name
FROM   system.access.table_lineage
WHERE  event_date >= current_date() - INTERVAL 7 DAYS
  AND (source_table_catalog = 'hive_metastore' OR target_table_catalog = 'hive_metastore');

(Lineage for hive_metastore tables may be partial; also check job run logs.) Once nothing writes the old table, make it read-only or rename it (ALTER TABLE hive_metastore.sales.orders RENAME TO hive_metastore.sales.orders_migrated_do_not_use) so stragglers fail loudly.

#Step 7: Mounts and paths

DBFS mounts (/mnt/...) aren't governed by UC and don't work on standard access mode clusters in the same way. Replace them:

#Symptom → fix table

#Prevent it next time

• Migrate by domain: upgrade tables, update code to three-part names, switch job clusters, then retire Hive tables, in one change per domain.
• Make the catalog a job parameter everywhere so environments never depend on defaults.
• Run every job as a service principal with explicit UC grants.
• After migration, rename or drop old Hive tables quickly so nothing keeps writing to them.