topic Re: Copying Delta table from QA to Prod in Data Engineering

Copying Delta table from QA to Prod

MachuPichu — Sat, 21 Jan 2023 19:36:11 GMT

I am looking for a way to copy large managed Delta table like 4TB from one environment (QA) to other(Prod) . QA and Prod are in different subscription and in different region. I understand Databricks provides a way to clone table. But I am not sure if cloning can work across the subscriptions. Yes, there is network connectivity between QA and prod in case files need to be copied from lower to higher environment. I am sure I am not the first person trying to copy tables across the environment. Can you share how you performed such copy/migration ?

Re: Copying Delta table from QA to Prod

sher — Sun, 22 Jan 2023 12:37:09 GMT

USE DEEP CLONE

CREATE TABLE delta.`/data/target/` CLONE delta.`/data/source/` -- Create a deep clone of /data/source at /data/target

ref link: https://docs.databricks.com/optimizations/clone.html

Re: Copying Delta table from QA to Prod

Cami — Sun, 22 Jan 2023 15:52:04 GMT

I don't know if it would be a ideal option, but please read more Unity Catalog and delta sharing. DEEP CLONE souds good.

Re: Copying Delta table from QA to Prod

MachuPichu — Mon, 23 Jan 2023 03:41:48 GMT

Does it support cloning across the subscription ? If so can you share an example?

Re: Copying Delta table from QA to Prod

MachuPichu — Mon, 23 Jan 2023 03:43:03 GMT

We are not using unity catalog. This is still based on Hive catalog

Re: Copying Delta table from QA to Prod

daniel_sahal — Mon, 23 Jan 2023 09:34:34 GMT

@Ratnadeep Bose

The best way would be to create a storage that will be used to copy the data between two envs.

Create an external table on Sub1 with the same schema as source table, with location on the storage,
Insert into external table from source table,
Create an external table on Sub2 with the same schema as source table, with location on the storage,

Thanks to that you've got the same data on both subscriptions.

Re: Copying Delta table from QA to Prod

Hubert-Dudek — Mon, 23 Jan 2023 13:24:41 GMT

I would use a data factory to copy 4TB files as it has gigantic throughput. After completing a copy of everything, I would register as a table in the new metastore.

Re: Copying Delta table from QA to Prod

MachuPichu — Tue, 24 Jan 2023 20:29:26 GMT

Just to be clear we are using managed delta table not external table. I am not sure if above solution will still work. Thanks very much for your feedback

Re: Copying Delta table from QA to Prod

MachuPichu — Tue, 24 Jan 2023 20:33:41 GMT

Thought about using ADF. Since we are using managed Delta table, I am not sure how you can register based on external data. Any idea?

Re: Copying Delta table from QA to Prod

daniel_sahal — Wed, 25 Jan 2023 06:32:18 GMT

@Ratnadeep Bose

That's why I've mentioned creating external table as a table that will be used for data copy between two environments. It should be a copy of source table but with the location on the storage.