Delta Table Movement across Environments in Synapse Analytics?

In this blog, we will discuss the movement of the Delta tables from one environment to another environment within a same subscription. There may be a scenario where you need to transfer Delta Table or the Lake Database from the upper environment to the lower environment and vice versa.

Currently, there is no comprehensive documentation available regarding Delta Table Movement in the MS documentation. The process mentioned in the documentation can guide us to move the tables, but it is not entirely effective because the table created in the target environment becomes an External table, and the data files are not managed by Spark.

Here are some scenarios where Delta table movement would be helpful:

1. Promotion of development: If you have new developments in the DEV environment and you want to promote them to the upper environment along with the data.
2. Periodic environment refresh: Refreshing the Lower Environment from the Upper Environment or vice versa on a regular basis.

High Level steps to follow

1. Create a manged table in the source environment.
2. Take the DDL of the table and create a managed delta table in the target environment.
3. Copy the delta table folder from the source storage account to the target storage. Make sure the table folder should be copied to the managed table path i.e. <<container>>/synapse/workspaces/syn-delta-uat/warehouse/delta.db/patient

I have source data file in the CSV file and let’s create the managed delta table on top of the file you have with you.

%%pyspark

df = spark.read.load('abfss://delta@deltademo.dfs.core.windows.net/DATAFILE/patient.csv', format='csv', header=True)

df.write.format('delta').mode('overwrite').saveAsTable('delta.patient')

This will create the folder with the table in the database, which will have the data files and its delta_log.

We can select the data as well from the source table.

Since the table is not present in the UAT environment, we first need to create Delta table.

%%pyspark

spark.sql("CREATE TABLE delta.PATIENT(SERIAL_NAME int, PATIENT_NAME String, PATIENT_AGE int, SYMPTOM String) USING DELTA")

Since we are creating SPARK managed table, by default the Hive Metastore will register this table to the default predefined path where all the managed tables are stored in Synapse i.e. under the primary storage <<container>>/synapse/workspaces/<<syn-delta-uat>>/warehouse/delta.db/patient

Now, let’s copy the delta table folder from the Source environment to the target UAT storage location mentioned above. You can use the Azure Storage explorer to copy the delta table folder.

Let’s query the table in the target UAT environment, if you query the table you can see the table data that was there in the Dev and successfully migrated to the target environment.

I hope this blog gave provided some insights and would help you in cloning the Delta table in your environment.

I hope this blog provided some insights and would help you in cloning the delta table in your environment.