Does Changing the External Location in My Databricks Destination Cause Any Side Effects?

Question

Does changing the external location in my Databricks destination cause any side effects?

Environment

Databricks destination

Answer

Yes, if you are already syncing data to an existing external location (folder path), changing the external location may cause some side effects. When you configure the destination, during table creation, we set the path of the table to the external location, and this doesn't change until you manually drop the table. If you update the external location in the destination setup form, then we recreate the table in Databricks, but in the new folder path. All the data in the previous folder is left as is, and the queries may fail.

If you want to migrate your data from one location to another, you should manually migrate the tables before switching the external location in the destination setup form. Do the following:

NOTE: Perform the following steps if you want migrate from the Databricks File System (DBFS) to an external location.

1. Pause your connectors.

2. Execute the following queries to migrate the tables:

   • CREATE TABLE {schema}.{table}_copy USING DELTA AS SELECT * FROM {schema}.{table}

   • DROP TABLE {schema}.{table}

   • CREATE TABLE {schema}.{table} USING DELTA LOCATION s3://{new_path}/{schema}/{table} AS SELECT * FROM {schema}.{table}_copy

     NOTE: You can also use REPLACE TABLE command.

   • DROP TABLE {schema}.{table}_copy

   NOTE: You must perform Step 2 for all the existing tables of all the connectors.

3. In the destination setup form, change the External Location.

4. Unpause connectors.