Databricks DE · Topic 3.4

Deep vs Shallow CLONE & Predictive Optimization

Deep clone = independent copy (data + metadata), incremental on re-run. Shallow clone = instant pointer to the source's files. Predictive Optimization auto-runs maintenance for you. This deep dive walks each with the code.

DEEP CLONESHALLOW CLONEincrementalPredictive Optimization

The one-liner

DEEP CLONE copies data + metadata → independent table (re-runs sync only the delta). SHALLOW CLONE copies only metadata and references the source's files → instant & cheap but dependent. Predictive Optimization auto-runs OPTIMIZE/VACUUM/ANALYZE on UC managed tables.

📚 Analogy: deep clone = photocopying a whole book (your own copy); shallow clone = sticky-notes referencing the original (instant, but useless if the original is thrown out); PO = a robot librarian that reshelves & declutters automatically.

Clone types — and the VACUUM trap

▸ pick a clone type, then run VACUUM on the source

DEEP CLONE

SHALLOW CLONE

Source table

data files: present

— copied —

Clone

owns its own data

1 · The two clone types

Both copy schema, partitioning, invariants, nullability, and TBLPROPERTIES (deep also copies stream/COPY INTO metadata). Neither copies history, description, or UC tags. CLONE defaults to deep.

SQL — deep, shallow, versioned

CREATE TABLE main.dr.orders_backup DEEP CLONE main.sales.orders;     -- independent copy
CREATE TABLE main.dev.orders_dev SHALLOW CLONE main.sales.orders;     -- instant, points at source
CREATE TABLE main.dev.orders_v5 SHALLOW CLONE main.sales.orders VERSION AS OF 5;

PySpark — CLONE is SQL DDL, run via spark.sql

spark.sql("CREATE OR REPLACE TABLE main.dr.orders_backup DEEP CLONE main.sales.orders")

2 · Deep clone is INCREMENTAL (the backup superpower)

Re-running a deep clone into an existing target commits only the new/changed data since the last clone — cost scales with the change, not table size. That's why it's the go-to for scheduled backups, DR, and cross-catalog/region migration.

-- Day 1: full copy. Day 2..N: same command re-syncs ONLY the delta since last run.
CREATE OR REPLACE TABLE main.dr.orders_backup DEEP CLONE main.sales.orders;

3 · Shallow clone & the VACUUM trap

Shallow clone borrows the source's data files, so it's instant — but two consequences interviewers probe:

VACUUM on the source breaks the shallow clone → readers hit FileNotFoundException.
Readers need access to both the source's storage and the clone's directory.

CREATE TABLE main.dev.orders_dev SHALLOW CLONE main.sales.orders;
-- ...later, on the SOURCE:
VACUUM main.sales.orders;     -- ⚠️ removes files orders_dev references → clone breaks

💡 For a durable copy that survives source maintenance, use DEEP CLONE. Also note: streaming tables & materialized views can't be a clone source or target.

4 · Predictive Optimization — click each

What it does▸

Automatically runs maintenance — OPTIMIZE (incl. incremental clustering), VACUUM, and ANALYZE — on Unity Catalog managed tables based on usage. No manual scheduling.

Why it matters▸

Removes the "did you schedule OPTIMIZE/VACUUM?" toil and avoids both under- and over-maintenance. Modern answer to "how do you keep tables fast?"

Scope & default▸

Applies to UC managed tables (external you maintain yourself). Enabled by default for accounts created on/after Nov 11, 2024; older accounts via gradual rollout.

Enable at catalog or schema level (inherits down)

ALTER CATALOG main ENABLE PREDICTIVE OPTIMIZATION;
ALTER SCHEMA  main.sales ENABLE PREDICTIVE OPTIMIZATION;   -- or DISABLE / INHERIT

Deep vs shallow — at a glance

	Deep clone	Shallow clone
Copies data files	Yes	No (points at source)
Speed / cost	Slower / more storage	Instant / cheap
Independent of source	Yes	No (VACUUM on source breaks it)
Incremental re-sync	Yes	n/a
Good for	Backup, archive, DR, migration	Test/dev, sharing

Why it matters

Shallow clone = instant test/dev copy of a huge prod table without duplicating terabytes — a favorite trick & interview answer.
Deep clone is incremental → cheap, repeatable backups/DR.
PO removes maintenance toil — the modern answer to "how do you keep tables performant?"

🧪 Real-world use case: before a risky migration, SHALLOW CLONE prod.orders to a sandbox in seconds, experiment, then drop it — zero data copied. For DR, a nightly DEEP CLONE re-syncs only the day's changes.

Uses, edge cases & limitations

Uses: shallow → cheap test/dev/sharing copies; deep → backups, archiving versions, ML reproducibility, DR, cross-region migration.
Edge case: shallow clone depends on source files — VACUUM on the source breaks it (FileNotFoundException); readers need access to both source & clone. Clones have independent history; deep re-runs are incremental.
Limitation: can't clone streaming tables or materialized views; CLONE doesn't copy history/description/UC tags. PO applies to UC managed tables only.

Common gotchas

❌ Treating a shallow clone as a real backup — VACUUM on the source destroys it. Use deep clone for durable copies.
❌ Expecting CLONE to copy history/tags/description — it doesn't.
❌ Manually scheduling OPTIMIZE/VACUUM where PO already runs.
❌ Trying to clone a streaming table / materialized view — unsupported.
❌ Re-doing a full copy for backups — a repeat DEEP CLONE only syncs the delta.