Refreshing test environments with Delta deep and shallow clones on Databricks

Test environments with toy data hide bugs that only appear at production scale. Copying terabytes every week is slow and expensive. Delta clones sit in between.

#Shallow vs deep

CREATE OR REPLACE TABLE sales_test.silver.orders SHALLOW CLONE sales_prod.silver.orders;
CREATE OR REPLACE TABLE sales_uat.silver.orders  DEEP CLONE    sales_prod.silver.orders;

Clone a specific version:

CREATE OR REPLACE TABLE sales_test.silver.orders SHALLOW CLONE sales_prod.silver.orders VERSION AS OF 812;

#The shallow clone trap

A shallow clone reads the source's data files. When the source runs VACUUM, files the source no longer needs are deleted, even if the clone still references them. The clone then fails:

FileNotFoundException / DeltaFileNotFoundException: ... file referenced in the transaction log cannot be found

Warning

Use shallow clones only for short-lived copies (hours to a few days, shorter than the source's VACUUM retention), or re-create them before each test run. For anything long-lived, use a deep clone.

Unity Catalog managed tables have additional rules for shallow clones (for example the clone type must match the source's management type in some cases); check the current documentation for your runtime.

#Refresh a deep clone incrementally

Re-running CREATE OR REPLACE ... DEEP CLONE copies only files that are new since the last clone, which makes weekly refreshes much cheaper than the first copy:

CREATE OR REPLACE TABLE sales_uat.silver.orders DEEP CLONE sales_prod.silver.orders;

The operation metrics in DESCRIBE HISTORY sales_uat.silver.orders show num_copied_files and copied_files_size, so you can confirm it was incremental.

Writes made in the clone since the last refresh are overwritten. Tell testers.

#Clone a whole schema

There is no single schema-clone statement; loop over tables:

src, dst, mode = "sales_prod.silver", "sales_uat.silver", "DEEP"
spark.sql(f"CREATE SCHEMA IF NOT EXISTS {dst}")
for t in spark.sql(f"SHOW TABLES IN {src}").collect():
    if t.isTemporary:
        continue
    spark.sql(f"CREATE OR REPLACE TABLE {dst}.{t.tableName} {mode} CLONE {src}.{t.tableName}")
    print("cloned", t.tableName)

Views, functions and grants are not cloned; recreate them from code. Run the loop as a job with a service principal that has SELECT on the source and CREATE TABLE on the target.

#Mask sensitive data

Cloned production data is production data. Options:

1. Mask after cloning (deep clones only, since you write to them):

UPDATE sales_uat.silver.customers
SET email = concat('user', customer_id, '@example.test'),
    phone = NULL,
    name  = concat('Customer ', customer_id);

2. Column masks and row filters on the clone, so testers see masked values while the data stays real for pipeline tests:

CREATE FUNCTION sales_uat.security.mask_email(email STRING) RETURN
  CASE WHEN is_account_group_member('uat-pii-readers') THEN email ELSE '***' END;
ALTER TABLE sales_uat.silver.customers ALTER COLUMN email SET MASK sales_uat.security.mask_email;

3. Clone only non-sensitive tables, generate synthetic data for the rest.

Caution

Never shallow-clone tables with personal data into a less protected catalog. Anyone with access to the clone reads production files.

#Permissions and isolation

• Clones inherit nothing: grant access on the target catalog/schema explicitly.
• Bind the UAT catalog to the UAT workspace only.
• The cloning service principal needs read on prod; keep that principal out of interactive use.

#Cost control

#Use clones for safer changes

Before a risky production change (schema change, big backfill), deep clone the table, run the change on the clone, compare results, then run it on production. Keep the pre-change deep clone for a few days as a fast rollback point.

#Prevent it next time

• Policy: shallow clones live for less than the source VACUUM retention; jobs recreate them per run.
• Masking step is part of the clone job, before access is granted.
• A refresh job per environment, scheduled and monitored like any pipeline.