topic Re: Partitioning vs Liquid Clustering (per-table): in Data Engineering

Partitioning vs Liquid Clustering (per-table):

AshokB — Tue, 01 Sep 2026 20:16:23 GMT

Can PARTITION BY and CLUSTER BY (Liquid Clustering) be used simultaneously on the same table? If we use only PARTITION BY, is there a negative performance impact on materialized-view refreshes in Silver/Gold? Since materialized views read only incremental delta files, is Liquid Clustering redundant in this scenario, or does it still provide benefits for file compaction and read optimization?

Re: Partitioning vs Liquid Clustering (per-table):

balajij8 — Wed, 02 Sep 2026 03:53:13 GMT

@AshokB PARTITION BY and CLUSTER BY cannot be used simultaneously on the same table. You can pick one strategy as Liquid Clustering cannot be combined with PARTITIONED BY. You can use CLUSTER BY over legacy partitioning for Silver and Gold layers. Liquid clustering functions as a flexible, self-tuning replacement of Partitioning and it eliminates partitioning rigidity. It allows you to redefine clustering keys on the fly without rewriting the underlying data.

Traditional partitioning still works. You can take advantage of layout and query optimizations on the table by using Liquid Clustering. You can keep liquid clustering as default for all greenfield tables because traditional partitioning remains rigid, high maintenance and generally prone to performance degradation under heavy data skew. Liquid clustering is built to handle varied access patterns, skewed datasets and queries filtering on high cardinality columns without the risk of over partitioning.

Liquid Clustering is far from redundant in an incremental pipeline. While the engine only reads incremental delta files from upstream sources to refresh the view, liquid clustering dictates how that materialized view is physically organized on object storage for downstream consumers. It clusters similar records together to maximize data skipping, continuously compacts the small files generated by repeated incremental writes via OPTIMIZE, and allows the physical layout to evolve alongside consumption patterns. If you use CLUSTER BY AUTO, Databricks dynamically identifies and tunes the optimal clustering keys based on the actual downstream query workloads making it especially effective when multiple consumers query the Silver and Gold tables with differing filter requirements.

You can use below configs for good performance if feasible

ALTER TABLE workspace.files.files SET TBLPROPERTIES ( delta.enableDeletionVectors = true, delta.enableRowTracking = true, delta.enableChangeDataFeed = true );

More details here

Re: Partitioning vs Liquid Clustering (per-table):

Satyasai — Wed, 02 Sep 2026 05:56:53 GMT

Hi @AshokB

I Agree with @balajij8

Use Liquid Clustering on columns frequently used in WHERE clauses, join keys, or aggregation keys (e.g., CLUSTER BY (tenant_id, event_date)).

Traditional partitioning should generally only be used for legacy compatibility or extremely low-cardinality keys (such as year or environment) where file counts stay minimal.

Re: Partitioning vs Liquid Clustering (per-table):

VenuD — Wed, 02 Sep 2026 06:49:20 GMT

Hey @AshokB, good question. To add on to what @balajij8 and @Satyasai already said:

On combining PARTITION BY and CLUSTER BY: you can't do it, as they are mutually exclusive. Delta will throw an error if you try to define both on the same table. Have to commit to one strategy per table.

On whether keeping PARTITION BY hurts MV refresh: the refresh itself isn't really affected, since incremental refresh cares about which rows changed upstream, not how the table is physically laid out. Where it matters most is after the refresh, in read performance and file management. If the partition column has any skew or high cardinality (something like customer_id or a fine grained timestamp), you'll end up with a ton of tiny partition folders and small files that never get cleaned up efficiently. Liquid Clustering doesn't have that problem because OPTIMIZE only rewrites the files that actually need re-clustering instead of touching a whole partition every time.

On whether Liquid Clustering is redundant for an MV that only reads incremental deltas: Incremental refresh and physical layout are two completely different things. Incremental refresh is about which rows the engine needs to reprocess. Liquid clustering is about how the resulting files from the MV are arranged on storage once they're written. Even if only a tiny fraction of rows changed on a given run, those new rows still land as small, scattered files unless something comes along and compacts/co-locates them. That's exactly what liquid clustering does through OPTIMIZE. So skip it and your "incrementally refreshed" MV quietly turns into a small file mess over time even though the refresh logic is working exactly as intended.

Additionally, combining LC + Predictive Optimization on UC managed tables handles the incremental re-clustering automatically and avoids the need for periodic OPTIMIZE jobs. It also takes care of VACUUM and ANALYZE the same way, so no manual maintenance scheduling is needed. A step further with CLUSTER BY AUTO, Predictive Optimization even picks the clustering keys based on query history, not just the re-clustering cadence. Just make sure to disable any existing scheduled OPTIMIZE jobs once this is on, running both is redundant.