Storing · Chapter 09

Tables and Storage

Underneath every catalog and every pipeline there is a set of files on object storage and a transaction log that turns them into a table. Most performance problems and a surprising share of correctness problems come from this layer, so it is worth understanding rather than treating as an implementation detail.

Managed tables unless you have a reason #

Databricks distinguishes managed tables, where the platform owns the storage location and lifecycle, from external tables, where you point at a location you manage yourself.

That worry about control is worth answering directly. Managed storage still lives in a cloud storage account you own, in your subscription, under your keys if you configured them. The platform owns the layout and the lifecycle, not the bytes or the bill.

Delta, and the log that makes it work #

A Delta table is a directory of Parquet files plus a transaction log. Every write appends a commit to that log describing which files were added and removed, which is what gives you atomicity, time travel, and concurrent readers who never see a partial write. Periodically the log is checkpointed, so a reader does not have to replay thousands of commits to learn the current state.

Two consequences follow. A table's history is real storage that you pay for, governed by the retention properties on the table, so a heavily updated table with a long retention window costs more than its current contents suggest. And many small commits produce many small files, which are the most common cause of a query that got slower for no apparent reason.

Stop partitioning by habit #

Partitioning made sense as a default when the alternative was a full scan of undifferentiated files. On a modern Databricks table with liquid clustering and predictive optimisation available, it is usually a pessimisation. The heuristic we use is that a table under roughly a terabyte should not be partitioned at all, and a table above that should use liquid clustering on the columns that appear in filters rather than a Hive-style partition scheme. Date partitioning is the pattern that most often needs undoing, because it produces thousands of directories holding a few megabytes each.

Liquid clustering is worth understanding rather than adopting on faith. Unlike partitioning, its columns can be changed later without rewriting the table, and unlike ZORDER, the layout is maintained incrementally as data arrives rather than by a full rewrite you schedule. An automatic mode selects and adjusts the keys from observed query patterns, which is the right default for tables whose access patterns you cannot predict.

Optimisation you should not be doing by hand #

Predictive optimisation handles compaction, statistics, and clustering maintenance for managed tables, based on observed query patterns rather than a schedule somebody guessed at. Turn it on at the catalog level and delete the nightly OPTIMIZE jobs it replaces. Where you still intervene deliberately is on tables with unusual write patterns, such as one rewritten wholesale each night, where you know something the optimiser cannot infer.

Schema evolution, and being deliberate about it #

Delta will happily add columns when you ask it to, which is convenient until an upstream system renames a field and you silently acquire a duplicate column holding half the data. We allow automatic schema evolution in bronze, where the contract is deliberately loose, and forbid it in silver and gold, where the schema is a published contract and a change goes through a pull request like any other interface change.

Beyond the basics #

The features below are all standard parts of the table format, and in our experience most teams are using perhaps two of them.

Deletion vectors. A delete or update no longer rewrites whole Parquet files. The change is recorded in a compact vector alongside the file and merged away later, turning an expensive MERGE on a large table into a cheap one. For a table with regular updates, and especially for a right-to-erasure process, this is the difference between a nightly job that finishes and one that does not.

Change data feed. Switch it on and the table publishes its own row-level changes, marked as insert, update or delete, readable as a stream or a batch. Downstream consumers stop reimplementing change detection with watermark columns and full-table comparisons, which is homegrown logic that is usually subtly wrong.

Shallow clones. A shallow clone creates a new table pointing at the existing files, costing almost nothing and taking seconds. This is how we give a test suite a full copy of a production-scale table without duplicating storage. Deep clones exist for when you genuinely want an independent copy elsewhere.

Constraints and informational keys. NOT NULL and CHECK constraints are enforced on write, so a bad row fails the pipeline rather than reaching a dashboard. Primary and foreign key declarations are informational rather than enforced, but they document the model, feed the optimiser, and let BI tools generate correct joins automatically.

Column mapping, type widening, and generated columns. With column mapping enabled, renaming or dropping a column is a metadata operation rather than a rewrite, and type widening lets a column grow from an integer to a long without rewriting history. Generated columns derive a value the optimiser can use to skip files, and identity columns give you surrogate keys without a sequence table.

Row-level concurrency. Concurrent writers touching different rows no longer conflict and retry, which matters as soon as more than one stream or job writes to a table.

Managed Iceberg and format interoperability. Databricks reads and writes Iceberg as well as Delta, can publish Delta tables with Iceberg metadata so external engines read them without a copy, and exposes tables through an Iceberg REST catalog. This matters most for an organisation with an existing Iceberg estate that does not want a migration as the price of adoption. The caveat we always state is that governance expressed in Unity Catalog does not fully translate to an external engine reading through the REST catalog, so the security review happens per consumer rather than once.

Credit where it is due. BigQuery's native storage is excellent and needs almost no tuning, and the Glue and Iceberg combination on AWS is a legitimately open architecture. The distinction is that BigQuery's excellence applies inside BigQuery's own storage, and the AWS story asks you to assemble and operate the maintenance yourself.

The practical checklist #

When we take over an existing estate, four checks find most of the problems in an afternoon. Count files per table and look for anything averaging under a few tens of megabytes. Check whether a partition column matches what queries actually filter on. Compare retention properties against the history anybody relies on. And confirm predictive optimisation is enabled at the catalog level, because the nightly maintenance jobs it replaces are usually still running, still costing money, and often the reason the morning refresh is late. The performance side of this continues in Cost and Performance.

Table of Contents