Skip to main content
Last updated on

Use variant shredding to optimize performance

Variant shredding improves query performance on VARIANT columns by storing commonly occurring fields as separate columns in the underlying Parquet files. Shredding reduces the I/O required to read fields and improves compression by using a columnar format instead of a binary blob.

See VARIANT type, Variant type support for Apache Iceberg and Delta Lake, and Query variant data.

Requirements​

Databricks Runtime 17.3 or above is required to read and write shredded VARIANT tables. For optimal query performance with variant statistics collection and data skipping, Databricks recommends Databricks Runtime 18.1 or above.

Enable shredding​

Starting with Databricks Runtime 17.3, shredding is automatically enabled when you create a new table with one or more VARIANT columns using CREATE TABLE.

CREATE OR REPLACE TABLE and ALTER TABLE do not automatically enable shredding. This avoids changing the shredding behavior of existing tables and workloads.

For existing tables, you can manually opt in to shredding by setting the enableVariantShredding table property to TRUE and opt out by setting the property to FALSE:

SQL
ALTER TABLE my_table SET TBLPROPERTIES ('delta.enableVariantShredding' = 'true');

Verify that shredding is enabled by checking that the table property enableVariantShredding is set to true.

Opt out of shredding for a specific table​

To exclude a specific table from shredding on future writes, set the enableVariantShredding table property to FALSE:

SQL
ALTER TABLE my_table SET TBLPROPERTIES ('delta.enableVariantShredding' = 'false');

Remove shredding from an existing table​

To remove shredding on an existing table, drop the feature with the ALTER TABLE command. This operation also rewrites shredded VARIANT data in place to the unshredded VARIANT format and sets the enableVariantShredding table property to false.

SQL
ALTER TABLE my_table DROP FEATURE "variantShredding-preview";

Limitations​

  • Shredding data introduces some overhead on writes.
  • Enabling shredding doesn't automatically convert existing VARIANT data in a table. It only applies to data written after the feature is enabled. To rewrite existing VARIANT data, use REORG TABLE my_table APPLY (SHRED VARIANT).
  • Shredding applies to top-level VARIANT columns or VARIANT fields in structs, excluding VARIANT data stored inside arrays or maps.
On this page