Configure Delta Lake to control data file size

Delta Lake provides options for manually or automatically configuring the target file size for writes and for OPTIMIZE operations. Azure Databricks automatically tunes many of these settings, and enables features that automatically improve table performance by seeking to right-size files.

For Unity Catalog managed tables, Databricks tunes most of these configurations automatically if you're using a SQL warehouse or Databricks Runtime 11.3 LTS or above.

If you're upgrading a workload from Databricks Runtime 10.4 LTS or below, see Upgrade to background auto compaction.

When to run OPTIMIZE

Auto compaction and optimized writes each reduce small file problems, but are not a full replacement for OPTIMIZE. Especially for tables larger than 1 TB, Databricks recommends running OPTIMIZE on a schedule to further consolidate files. Azure Databricks does not automatically run ZORDER on tables, so you must run OPTIMIZE with ZORDER to enable enhanced data skipping. See Data skipping for Delta Lake.

What is auto optimize on Azure Databricks?

The term auto optimize is sometimes used to describe functionality controlled by the settings delta.autoOptimize.autoCompact and delta.autoOptimize.optimizeWrite. This term has been retired in favor of describing each setting individually. See Auto compaction for Delta Lake on Azure Databricks and Optimized writes for Delta Lake on Azure Databricks.

Auto compaction for Delta Lake on Azure Databricks

Auto compaction combines small files within Delta table partitions to automatically reduce small file problems. Auto compaction occurs after a write to a table has succeeded and runs synchronously on the cluster that has performed the write. Auto compaction only compacts files that haven't been compacted previously.

You can control the output file size by setting the Spark configuration spark.databricks.delta.autoCompact.maxFileSize. Databricks recommends using autotuning based on workload or table size. See Autotune file size based on workload and Autotune file size based on table size.

Auto compaction is only triggered for partitions or tables that have at least a certain number of small files. You can optionally change the minimum number of files required to trigger auto compaction by setting spark.databricks.delta.autoCompact.minNumFiles.

Auto compaction can be enabled at the table or session level using the following settings:

• Table property: delta.autoOptimize.autoCompact
• SparkSession setting: spark.databricks.delta.autoCompact.enabled

These settings accept the following options:

Options	Behavior
auto (recommended)	Tunes target file size while respecting other autotuning functionality. Requires Databricks Runtime 10.4 LTS or above.
legacy	Alias for true. Requires Databricks Runtime 10.4 LTS or above.
true	Use 128 MB as the target file size. No dynamic sizing.
false	Turns off auto compaction. Can be set at the session level to override auto compaction for all Delta tables modified in the workload.

Optimized writes for Delta Lake on Azure Databricks

Optimized writes improve file size as data is written and benefit subsequent reads on the table.

Optimized writes are most effective for partitioned tables, as they reduce the number of small files written to each partition. Writing fewer large files is more efficient than writing many small files, but you might still see an increase in write latency because data is shuffled before being written.

The following image demonstrates how optimized writes works:

Optimized writes are enabled by default for the following operations in Databricks Runtime 9.1 LTS and above:

• MERGE
• UPDATE with subqueries
• DELETE with subqueries

Optimized writes are also enabled for CTAS statements and INSERT operations when using SQL warehouses. In Databricks Runtime 13.3 LTS and above, all Delta tables registered in Unity Catalog have optimized writes enabled for CTAS statements and INSERT operations for partitioned tables.

Optimized writes can be enabled at the table or session level using the following settings:

• Table setting: delta.autoOptimize.optimizeWrite
• SparkSession setting: spark.databricks.delta.optimizeWrite.enabled

These settings accept the following options:

Options	Behavior
true	Use 128 MB as the target file size.
false	Turns off optimized writes. Can be set at the session level to override auto compaction for all Delta tables modified in the workload.

Set a target file size

If you want to tune the size of files in your Delta table, set the table property delta.targetFileSize to the desired size. If this property is set, all data layout optimization operations will make a best-effort attempt to generate files of the specified size. Examples here include optimize or Z-order, auto compaction, and optimized writes.

Table property
delta.targetFileSize Type: Size in bytes or higher units. The target file size. For example, 104857600 (bytes) or 100mb. Default value: None

For existing tables, you can set and unset properties using the SQL command ALTER TABLE SET TBL PROPERTIES. You can also set these properties automatically when creating new tables using Spark session configurations. See Delta table properties reference for details.

Autotune file size based on workload

Databricks recommends setting the table property delta.tuneFileSizesForRewrites to true for all tables that are targeted by many MERGE or DML operations, regardless of Databricks Runtime, Unity Catalog, or other optimizations. When set to true, the target file size for the table is set to a much lower threshold, which accelerates write-intensive operations.

If not explicitly set, Azure Databricks automatically detects if 9 out of last 10 previous operations on a Delta table were MERGE operations and sets this table property to true. You must explicitly set this property to false to avoid this behavior.

Table property
delta.tuneFileSizesForRewrites Type: Boolean Whether to tune file sizes for data layout optimization. Default value: None

For existing tables, you can set and unset properties using the SQL command ALTER TABLE SET TBL PROPERTIES. You can also set these properties automatically when creating new tables using Spark session configurations. See Delta table properties reference for details.

Autotune file size based on table size

To minimize the need for manual tuning, Azure Databricks automatically tunes the file size of Delta tables based on the size of the table. Azure Databricks will use smaller file sizes for smaller tables and larger file sizes for larger tables so that the number of files in the table does not grow too large. Azure Databricks does not autotune tables that you have tuned with a specific target size or based on a workload with frequent rewrites.

The target file size is based on the current size of the Delta table. For tables smaller than 2.56 TB, the autotuned target file size is 256 MB. For tables with a size between 2.56 TB and 10 TB, the target size will grow linearly from 256 MB to 1 GB. For tables larger than 10 TB, the target file size is 1 GB.

When a table is written incrementally, the target file sizes and file counts will be close to the following numbers, based on table size. The file counts in this table are only an example. The actual results will be different depending on many factors.

Table size	Target file size	Approximate number of files in table
10 GB	256 MB	40
1 TB	256 MB	4096
2.56 TB	256 MB	10240
3 TB	307 MB	12108
5 TB	512 MB	17339
7 TB	716 MB	20784
10 TB	1 GB	24437
20 TB	1 GB	34437
50 TB	1 GB	64437
100 TB	1 GB	114437

Limit rows written in a data file

Occasionally, tables with narrow data might encounter an error where the number of rows in a given data file exceeds the support limits of the Parquet format. To avoid this error, you can use the SQL session configuration spark.sql.files.maxRecordsPerFile to specify the maximum number of records to write to a single file for a Delta Lake table. Specifying a value of zero or a negative value represents no limit.

In Databricks Runtime 11.3 LTS and above, you can also use the DataFrameWriter option maxRecordsPerFile when using the DataFrame APIs to write to a Delta Lake table. When maxRecordsPerFile is specified, the value of the SQL session configuration spark.sql.files.maxRecordsPerFile is ignored.

Upgrade to background auto compaction

Background auto compaction is available for Unity Catalog managed tables in Databricks Runtime 11.3 LTS and above. When migrating a legacy workload or table, do the following:

• Remove the Spark config spark.databricks.delta.autoCompact.enabled from cluster or notebook configuration settings.
• For each table, run ALTER TABLE <table_name> UNSET TBLPROPERTIES (delta.autoOptimize.autoCompact) to remove any legacy auto compaction settings.

After removing these legacy configurations, you should see background auto compaction triggered automatically for all Unity Catalog managed tables.