Partitioning
(and when not to).

What it is

Partitioning physically splits a table's files into separate folders by the value of a column. Partition PARTITIONED BY (event_date) and every day gets its own directory. A query with WHERE event_date = '2026-06-01' can jump straight to that one folder and ignore the rest — this is partition pruning (skipping whole folders the query can't match).

Analogy
Partitioning is like filing paper records in drawers labeled by year. A handful of year-drawers is tidy. A drawer per customer ID — millions of one-sheet drawers — is a nightmare to store and to search. Pick a label with few, stable values.

Real-world use case
A 5 TB clickstream table almost always queried with WHERE event_date BETWEEN … is a textbook good partition (date has ~365 values/year). A table keyed on customer_id (50k+ values) is a textbook bad one — use liquid clustering there instead.

Why it matters

• Right column → huge skipping win. Low-cardinality, frequently-filtered columns (date, region) let the engine prune entire folders, reading a tiny fraction of the table.
• Wrong column → permanent damage. A high-cardinality column (timestamp, customer_id) creates a partition explosion: thousands or millions of partitions, each holding almost nothing → millions of tiny files → slow listing, slow scans, metadata bloat.
• It's a one-time, hard-to-undo decision. Unlike liquid clustering (redefine keys anytime, no rewrite), changing a partition column requires a full table rewrite. A wrong guess is expensive.

See it break: partition cardinality

A 2 TB table split into partitions by one column. Drag the slider to raise the partition column's cardinality (distinct values) and watch the layout flip from a few healthy partitions to a partition explosion of tiny files.

Simplified illustration: a fixed 2 TB table over a target ~256 MB file size. Real file counts depend on data skew, write patterns, and file-size autotuning.

Compare three layouts

The same large table, three ways. Switch tabs to see the pros, cons, and resulting on-disk layout for a good partition key, a bad one, and no partition (ingestion-time clustering).

Simplified illustration of layout shape only; it does not run real queries.

How it works — sub-topic deep dives

01 Hive-style partitioning & PARTITIONED BY

"Hive-style" partitioning writes data into nested directories named col=value (e.g. .../event_date=2026-06-01/), declared once at table creation. A predicate on the partition column lets the engine prune whole directories without listing their files. Trade-off: the layout is fixed at creation and the wrong column produces the small-file problem by design. Critically, Hive-style layout is not part of the Delta protocol — the _delta_log is the source of truth, so never depend on the directory structure.

SQL-- Partition a large table by a LOW-cardinality, frequently-filtered column.
-- Delta is the default format, so no `USING DELTA`.
CREATE TABLE main.delta_opt_demo.events (
  event_id    BIGINT,
  customer_id BIGINT,
  region      STRING,
  event_ts    TIMESTAMP,
  event_date  DATE
)
PARTITIONED BY (event_date);   -- one folder per day; few, stable values

-- Partition pruning: reads only the 2026-06-01 folder, skips all other days.
SELECT count(*) FROM main.delta_opt_demo.events
WHERE event_date = DATE'2026-06-01';

PySpark# partitionBy() at write time defines the partition columns.
(df.write
   .partitionBy("event_date")        # low-cardinality column
   .mode("overwrite")
   .saveAsTable("main.delta_opt_demo.events"))

02 The modern recommendation: liquid clustering replaces partitioning for ALL new tables

Liquid clustering (CLUSTER BY) colocates related rows using clustering keys without baking the layout into directories. It gives the skipping benefit of partitioning and the colocation benefit of Z-order, but you can redefine the keys anytime with no rewrite of existing data. Rule: it's not compatible with partitioning or ZORDER — use it instead of them.

SQL-- The modern default for a NEW table: liquid clustering, NOT partitioning.
CREATE TABLE main.delta_opt_demo.events (
  event_id    BIGINT,
  customer_id BIGINT,
  region      STRING,
  event_date  DATE
)
CLUSTER BY (event_date, region);   -- redefine these keys later with NO rewrite
-- (Lesson 08. Never combine CLUSTER BY with PARTITIONED BY / ZORDER.)

03 Don't partition tables < 1 TB

Below ~1 TB the whole table is small enough that file listing + data skipping already make scans fast; partitioning just fragments the data into small partitions/files for no benefit. Databricks says most tables under 1 TB need no partitions — partitioning a small table usually hurts.

SQL-- ANTI-PATTERN: partitioning a small (< 1 TB) table fragments it.
CREATE TABLE main.delta_opt_demo.small_orders (order_id BIGINT, order_date DATE)
PARTITIONED BY (order_date);   -- bad: no benefit, creates tiny files

-- DO THIS: leave it unpartitioned. Ingestion-time clustering + data skipping
-- handle it; add liquid clustering later if filters demand it.
CREATE TABLE main.delta_opt_demo.small_orders (order_id BIGINT, order_date DATE);

04 Minimum partition size ≥ 1 GB

Aim for at least 1 GB of data per partition. Fewer, larger partitions consistently beat many small ones — each holds enough data to be read efficiently and produce right-sized files. A partition with only a few MB re-creates the small-file problem at the folder level.

SQL-- Coarsen the grain so each partition stays ≥ 1 GB:
-- partition by MONTH instead of DAY if daily partitions are < 1 GB.
CREATE TABLE main.delta_opt_demo.events_monthly (
  event_id BIGINT, region STRING, event_month STRING  -- 'YYYY-MM'
)
PARTITIONED BY (event_month);   -- ~30x fewer, ~30x larger partitions than daily

05 Over-partitioning on high-cardinality columns → partition explosion

Partitioning by a high-cardinality column (a timestamp to the second, or customer_id) creates one folder per distinct value — millions of folders, each with almost nothing → millions of tiny files. Partition only low/known-cardinality fields (date, region); for high-cardinality filter columns, use liquid clustering — it's built for them.

SQL-- ANTI-PATTERN: high-cardinality partition column = partition explosion.
CREATE TABLE main.delta_opt_demo.bad (event_ts TIMESTAMP, customer_id BIGINT, v DOUBLE)
PARTITIONED BY (event_ts);    -- bad: millions of 1-row folders → tiny files

-- RIGHT: cluster on the high-cardinality column (no folder explosion).
CREATE TABLE main.delta_opt_demo.good (event_ts TIMESTAMP, customer_id BIGINT, v DOUBLE)
CLUSTER BY (customer_id);     -- liquid clustering handles high cardinality

06 Partition columns must be top-level (no complex types / struct fields)

A partition column must be a top-level column. You cannot partition by a complex type (Struct / Map / Array / Variant) or a field inside a struct (struct_col.field) — the col=value directory naming needs a single scalar value. Workaround: liquid clustering does support struct fields via dot notation.

SQL-- Cannot partition by a struct field or complex type:
--   PARTITIONED BY (payload.region)   -- not allowed
--   PARTITIONED BY (tags_array)       -- not allowed (complex type)

-- Liquid clustering supports struct fields via dot notation:
ALTER TABLE main.delta_opt_demo.events CLUSTER BY (payload.region);

07 Supported partition data types

Supported types: Date, Timestamp, TimestampNTZ, Interval, String, Binary, Boolean, Integer / Long / Short / Byte, Float / Double / Decimal. Caution: supported ≠ good choice — Timestamp is supported but is almost always a partition-explosion trap. Cardinality, not type, decides whether a column is a good partition key.

08 Ingestion-time clustering (DBR 11.3 LTS+)

On DBR 11.3 LTS+, unpartitioned Delta tables are automatically clustered by ingestion time — rows written together tend to land together on disk. This gives a date-partition-like benefit for time-range queries with zero tuning, which is why "just don't partition a < 1 TB table" works in practice.

SQL-- No syntax needed: just DON'T partition; ingestion-time clustering works for you.
CREATE TABLE main.delta_opt_demo.events (event_id BIGINT, region STRING, event_ts TIMESTAMP);
-- Recent data is colocated, so recency filters still skip well:
SELECT * FROM main.delta_opt_demo.events
WHERE event_ts >= current_timestamp() - INTERVAL 1 DAY;

09 Z-order works only WITHIN a partition

ZORDER BY (Lesson 03) colocates related rows, but on a partitioned table it operates within each partition only — it cannot combine files across partition boundaries, and you cannot Z-order a partition column. Partition + Z-order is the old two-tool combo; liquid clustering replaces both with one mechanism and no boundaries to fight.

SQL-- On a partitioned table, Z-order reorganizes files WITHIN each partition only.
OPTIMIZE main.delta_opt_demo.events
WHERE event_date = DATE'2026-06-01'   -- scope to one partition
ZORDER BY (customer_id);              -- colocate within it (NOT a partition col)

10 Converting partitioned → liquid clustering (DBR 18.1+)

Convert a partitioned table to liquid clustering in place with ALTER TABLE … REPLACE PARTITIONED BY WITH CLUSTER BY. It's the migration path off a bad/legacy partition layout. Afterward, run OPTIMIZE … FULL to physically recluster existing data to the new keys.

SQL-- DBR 18.1+: convert a partitioned table to liquid clustering in place.
ALTER TABLE main.delta_opt_demo.events
  REPLACE PARTITIONED BY WITH CLUSTER BY (event_date, region);

-- Recluster existing data to the new keys (can be slow on huge tables):
OPTIMIZE main.delta_opt_demo.events FULL;

Comparison — partition keys vs. the alternative

Uses, edge cases & limitations

Uses (when partitioning still makes sense)

• A very large (≥ 1 TB) table almost always filtered on a low/known-cardinality column (date, region), with each partition ≥ 1 GB.
• Legacy / external tables already partitioned, or non-Databricks consumers expecting Hive-style directory layout.
• When your runtime doesn't (yet) support liquid clustering — but for new tables, prefer clustering.

Edge cases an interviewer probes

• High-cardinality partition key (customer_id, timestamp) → partition explosion → millions of tiny files.
• Tiny table partitioned (< 1 TB) → small partitions, slower than an unpartitioned full scan.
• Partition under 1 GB → folder-level small-file problem; coarsen the grain (day → month).
• Z-order across partitions → impossible; Z-order is per-partition and can't target the partition column.
• Reasoning by directory listing → wrong; Hive layout isn't the Delta protocol, the _delta_log is.
• Struct-field / complex-type partitioning → not allowed; use liquid clustering's dot-notation keys.

Limitations

• Partition column must be top-level; no complex types or struct fields.
• The choice is fixed at creation — changing it needs a full rewrite (or REPLACE PARTITIONED BY WITH CLUSTER BY on DBR 18.1+).
• Not compatible with liquid clustering — pick one, never both.
• Hive-style layout is not part of the Delta protocol — don't depend on directory structure.

Common gotchas

• Don't partition tables < 1 TB. Ingestion-time clustering + data skipping already make them fast.
• Never partition on a high-cardinality column (customer_id, timestamp) — partition explosion + tiny files. Use liquid clustering.
• Keep each partition ≥ 1 GB. If daily partitions are smaller, partition by month instead.
• You can't Z-order a partition column, and Z-order can't cross partitions — it works only within a partition.
• Partition columns are top-level only — no struct fields or complex types.
• The directory layout is not the source of truth. Use DESCRIBE DETAIL (partitionColumns, numFiles) — never a folder listing.
• For all new tables, prefer liquid clustering. Partitioning is the legacy layout.