Delta table and storage design

azure5 min read

Table design is an operating decision: layout, retention, optimization, and ownership affect cost and reliability long after the first write succeeds. Prefer Unity Catalog managed tables unless an explicit interoperability or lifecycle requirement justifies external ownership.

Design rules#

  1. Choose ownership first. Record whether Databricks or another platform owns the files, metadata, retention, and deletion lifecycle.
  2. Partition sparingly. Do not copy a source partition scheme by habit. Use query patterns, data volume, and file size to justify physical layout.
  3. Evaluate liquid clustering for new large tables. Choose clustering keys from selective, frequent filters and joins, then verify benefit from query evidence rather than assumptions.
  4. Control small files at the writer. Tune ingestion cadence and write behavior before adding an endless repair schedule.
  5. Treat VACUUM as destructive. Retention must cover the real rollback, streaming, clone, and concurrent-reader windows. Never shorten it merely to save storage.
  6. Use one optimization owner. Confirm predictive optimization covers the table before you remove overlapping OPTIMIZE and VACUUM jobs.
  7. Measure outcomes. Track table size, file count, query scan, optimization cost, freshness, and failed writes before and after a layout change.

Choose managed or external ownership#

Use a Unity Catalog managed table as the default. An external reader does not, by itself, require an external table. Check whether the reader supports the managed table's access API. See managed tables.

Use an external table when another system must own the files or needs direct file access. Record which system controls retention, schema changes, and cleanup. Unity Catalog removes the table metadata when you drop an external table, but it leaves the data files in place. Direct access to those files from another system does not enforce Unity Catalog privileges. See external tables.

For example, an upstream system can retain ownership of a landing dataset while Databricks owns a managed reporting table. Keep these lifecycles separate. Do not give two systems an implicit right to delete the same files.

Test liquid clustering with a representative workload#

Liquid clustering does not combine with partitioning or ZORDER on the same table. Check every reader and writer for table-feature compatibility before adoption. See the liquid clustering requirements.

The following example creates a new managed test table. Use an existing sandbox catalog and schema. The caller needs USE CATALOG, USE SCHEMA, and CREATE TABLE privileges. Change the names and keys for your test. Do not use a production table name.

CREATE TABLE sandbox.layout_trial.events (
  customer_id BIGINT,
  event_date DATE,
  event_type STRING
)
USING DELTA
CLUSTER BY (customer_id, event_date);

These keys illustrate a workload that often filters by customer and date. They are not a default for every event table. Populate the test with representative, approved data before you compare layouts. An empty table cannot establish a performance benefit.

Keep the compute size, query filters, data volume, and cache conditions comparable. Record query duration, bytes scanned, and maintenance cost. Test both selective queries and broad scans. Adopt the layout only when the results meet the workload's requirements. Use the grants guide to keep sandbox access separate from production.

Inspect the table before maintenance#

These commands inspect an existing table. Replace the example name with your target.

DESCRIBE DETAIL analytics.gold.events;
DESCRIBE HISTORY analytics.gold.events LIMIT 10;
VACUUM analytics.gold.events DRY RUN;

Compare file count and table size with the write history. A rising file count with little data growth is a reason to inspect ingestion cadence and file sizes, not proof of a fault. DRY RUN lists deletion candidates without removing them. It is not a restore test. See table details, table history, and VACUUM.

Predictive optimization performs maintenance for supported Unity Catalog managed tables. It does not replace external-table maintenance. Confirm its effective settings and recent operations before you remove a scheduled job. Include its serverless cost in the comparison. See predictive optimization.

Retention and recovery checks#

VACUUM removes files that old table versions can still need. A visible history entry does not prove that its data remains available. Log retention and data-file retention must both cover your recovery requirement. See VACUUM retention and table history.

Before you change retention:

  1. Record the longest supported replay, rollback, and reader window.
  2. Include delayed streams, outages, and clone dependencies in the review.
  3. Confirm the effective table properties and maintenance owner.
  4. Test recovery with a representative table in a separate environment.
  5. Obtain the data owner's approval for the deletion policy.

Do not disable the retention safety check to force a cleanup. Do not treat a short retention period as a cost fix when consumers still depend on older files.

Troubleshoot the operating pattern#

  • Many small files: inspect the writer and arrival cadence before you add more compaction.
  • No query improvement: compare scan metrics and filters before you change clustering keys.
  • Unexpected maintenance cost: check for overlapping jobs and predictive optimization.
  • An old version fails to read: check file retention as well as the transaction history.
  • An external reader fails after a layout change: check its supported table features.

Review questions#

  • Who owns the object lifecycle and disaster-recovery copy?
  • Which workloads require time travel, replay, streaming checkpoints, or external-engine access?
  • Are clustering keys based on current workload evidence?
  • Can a compaction or vacuum operation collide with ingestion or a long-running reader?
  • Is the table's retention policy documented independently from the code default?

Official sources#

Referenced by

Related topics and tags