[Databricks destination] Support for table optimization techniques in Databricks: partitioning, liquid clustering, data-skipping

[nicor88]

Feature description

Databricks supports different techniques to optimize data-retrieval, query latency, and make queries more efficient in general.
Some of the most common optimization techniques are:

1. Partitioning: it implies using PARTITIONED BY (col_1, col_2) in the table DDL statement - a similar concept to what is done in athena with the partition concept can be used
2. Liquid Clustering: the modern way to deal with partition and z-ordering in Databricks. It imply to pass cluster by (col_1, col_2) - can be achieve in a similar way as partitioning. It's also possible to have automatic clustering, which can be achieved via CLUSTER BY AUTO - that is really relevant in case we want to let Databricks do the heavy lifting for us.
3. data-skipping: more can be found here, it ca be defined as a table property; therefore to implement data-skipping the destination must be extend to support setting table properties of whatever sort

In general this feature request will allow dltHub to have more control on how Delta Tables are written.

Are you a dlt user?

Yes, I'm already a dlt user. - Run multiple PoC in the past, and now I'm currently pushing for dltHub adoption in the new working place

Use case

Optimize access pattern for tables in the raw layer, making queries faster and reducing cost.

Proposed solution

For each optimization techniques, I already provided some hints on how the feature can be implement.

Related issues

This issue: #2674 is related

[bayees]

I have started to look into this request. I agree on the clustering and partitioning should not be an issue, but I am not sure if the data-skipping is worth it since that is related to the ZORDERING and with predictive optimization on the managed tables. We should be covered.

I am also considering including the possibility for creating ICEBERG tables.

@rudolfix @nicor88 @burnash Let me know what you think?

[nicor88]

IMO these musts are:

• possibility to define partitions
• possibility to define clustering:
  • by specific columns e.g. CLUSTER BY (col0, col1)
  • liquid clustering CLUSTER BY AUTO
• possibility to define various table properties e.g.

TBLPROPERTIES (
        'delta.dataSkippingStatsColumns' = 'col0,col1',
);

This gives users full control.

Regarding ZORDERING - this is a technique that can be apply at Optimization time - so you right @bayees.

Iceberg

I believe that you could consider to include Iceberg as part of this scope, the TBLPROPERTIES becames even more relevant then, because there are various properties to set for iceberg.

[bayees]

@nicor88 There is a PR open now on the implementation.

Hopefully it is included in the next release. Try it out and let me know if you have issues with the features.