The SYNC Command - Your Primary Migration Tool

Databricks provides a SYNC command specifically designed for this exact use case - migrating tables from one location to another while maintaining synchronization . This command can upgrade tables from Hive Metastore to Unity Catalog and can be used for ongoing synchronization between catalogs .

 

Key Features of SYNC:

 

Example Usage:

 

-- Sync individual table with new name/location
SYNC TABLE new_catalog.new_schema.abc FROM old_catalog.old_schema.xxx;

-- Sync entire schema
SYNC SCHEMA new_catalog.tidy_schema FROM old_catalog.messy_schema;

-- Test before executing
SYNC TABLE new_catalog.new_schema.abc FROM old_catalog.old_schema.xxx DRY RUN;

There are also some automated refresh strategies you can consider:

1. Materialized Views with Scheduled Refresh

2. Lakeflow Streaming Tables

3. Synched Tables (currently in Preview Mode)

 

Some helpful links: 

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-aux-sync

https://community.databricks.com/t5/technical-blog/planning-a-migration-to-unity-catalog/ba-p/50759

https://www.databricks.com/glossary/materialized-views

https://docs.databricks.com/aws/en/dlt/dbsql/materialized

https://docs.databricks.com/aws/en/dlt/streaming-tables

https://docs.databricks.com/aws/en/dlt/flows

 

These recommendations are just that so depending on the circumstances of your situation some of them may not apply.  However, the goal here is to let you know that there are options and you will need to dig in to see which fits your situation.

Hope this helps, Louis.