Best practices for Liquid clustering and z-ordering for existing streaming delta tables

[prakash360]

Hello, I have been tasked to optimize some of our existing tables in delta lake in Databricks and I was able to perform following clause on some of our delta tables but I wasn't able to execute the same clause against some of our streaming tables.

ALTER TABLE table_name
CLUSTER BY (column1, column2);

Error that I receive when I execute above statement for our streaming tables:

[STREAMING_TABLE_OPERATION_NOT_ALLOWED.UNSUPPORTED_OPERATION] The operation ALTER TABLE is not allowed: The operation is not supported on Streaming Tables. SQLSTATE: 42601

My goal is to apply liquid clustering against all tables but seems like, we may have to build tables from scratch to apply liquid clustering on streaming table, is that right?As an alternative, I thought I should apply z-ordering against streaming table since following SQL, I was able to apply. Now thinking, what's the right way to apply z-ordering? Can I just continue executing following SQL every day or shall I apply the spark config params in my code?

OPTIMIZE table_name ZORDER BY column1, column2;

Or

pipelines.autoOptimize.managed = true
pipelines.autoOptimize.zOrderCols = "column1,column2"

source: https://docs.databricks.com/en/delta-live-tables/properties.html

Appreciate the feedback and guide for best path forward to apply best possible optimization against my delta table to limit the file scans.

[filipniziol]

Hi @prakash360 ,

There are a couple of things here:
1. You do not want to run ALTER TABLE commands on DLT. The idea of DLT is that they are fully managed by the DLT pipeline. Try to modify the DLT pipeline itself. 

2. At least try liquid clustering, as this a new, recommended feature. However, the liquid clustering in DLT was added just 2 weeks ago.

3. In order to make it work use preview 15.2 runtime and set channel of your DLT pipeline to preview:

[prakash360]

Hi @filipniziol , thank you for your quick response. Not executing the alter statement against the DLT table directly and instead adjusting the pipeline configuration approach makes sense. 

Regarding the liquid clustering on the DLT tables, I assume if we set the "Channel" setting to "Preview", it would enable us to apply "ALTER STATEMENT" against DLT tables perhaps or any thoughts on how we can apply liquid clustering on the existing DLT Table alternatively?

My goal is to avoid performing refresh on DLT tables since it will require huge amounts of data writes.