@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
