Compact data files

ReferenceApplies to: Unified engine deployments

Data compaction reads eligible data files and writes a new layout. Bin packing combines small files. Sorting orders records by chosen columns within the rewritten output. Z-order places values from several columns near each other to help some multi-column filters.

Before you start

Choose a writable Iceberg catalog in Workbench and an unused practice namespace. These examples use maintenance_docs, as described in Inspect and prepare a table. Native maintenance currently requires Iceberg format v2.

Create, compact, and check a table

This complete script creates six rows in three writes, checks the logical data, compacts eligible files, and checks the same totals again. It replaces only maintenance_docs.data_demo.

SET spark.sql.caseSensitive = true;
CREATE NAMESPACE IF NOT EXISTS maintenance_docs;
DROP TABLE IF EXISTS maintenance_docs.data_demo;
CREATE TABLE maintenance_docs.data_demo (
  id BIGINT,
  region STRING,
  amount BIGINT
) USING iceberg
TBLPROPERTIES ('format-version' = '2');

INSERT INTO maintenance_docs.data_demo
VALUES (1, 'North', 10), (2, 'South', 20);
INSERT INTO maintenance_docs.data_demo
VALUES (3, 'North', 30), (4, 'South', 40);
INSERT INTO maintenance_docs.data_demo
VALUES (5, 'North', 50), (6, 'South', 60);

SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM maintenance_docs.data_demo;

CALL system.rewrite_data_files(
  table => 'maintenance_docs.data_demo',
  strategy => 'binpack',
  target_file_size_bytes => 1048576,
  max_group_size_bytes => 8388608,
  min_input_files => 2
);

SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM maintenance_docs.data_demo;
SELECT * FROM maintenance_docs.data_demo ORDER BY id;

Both aggregate queries should return:

row_count	total_amount
6	210

The row query should still return IDs 1 through 6 with their original values. The 1 MiB output target and 8 MiB group limit are small practice values, not a production sizing recommendation.

Read the maintenance result

Select the CALL statement’s result in Workbench’s statement selector.

Result column	Meaning
`rewritten_data_files_count`	Data files replaced by this work
`added_data_files_count`	Replacement data files added
`rewritten_bytes_count`	Bytes reported as rewritten by the provider

Refresh the table’s Inspection view and compare current data-file counts, sizes, and snapshot identity. Fewer files with unchanged visible rows is the intended outcome for small-file compaction. Exact counts depend on file eligibility and the writer. Zero rewritten files is a valid no-op when no group qualifies. Files are grouped within their partitions. Several partitions can produce several output files. Compaction does not change the table’s partition specification.

Sort by a column

Run the setup above first. This alternative sorts rewritten data by region, then id. min_input_files => 1 allows an eligible single-file group after the earlier bin-pack call.

CALL system.rewrite_data_files(
  table => 'maintenance_docs.data_demo',
  strategy => 'sort',
  sort_order => 'region ASC NULLS LAST, id ASC NULLS LAST',
  target_file_size_bytes => 1048576,
  max_group_size_bytes => 8388608,
  min_input_files => 1
);
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM maintenance_docs.data_demo;

The logical result remains six rows and total 210. Sorting the file layout does not guarantee the order of a later SELECT; use ORDER BY when query output needs an order. This call does not set a permanent sort rule for later writes.

Z-order several columns

Z-order is another layout strategy for the same practice table. It requires distinct column names with supported types.

CALL system.rewrite_data_files(
  table => 'maintenance_docs.data_demo',
  strategy => 'zorder',
  columns => array('id', 'amount'),
  target_file_size_bytes => 1048576,
  max_group_size_bytes => 8388608,
  min_input_files => 1
);
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM maintenance_docs.data_demo;

The row count and total remain unchanged. Compare real filter queries before choosing sort or Z-order for a production table. The six-row example teaches the command; it is too small to measure useful query speed.

Size work for the available memory

max_group_size_bytes limits candidate input grouping. It is not a promise about decoded memory use. Compressed input can expand when read, and sorting needs working memory. The provider also applies the instance’s file, byte, and memory limits. For resource-limit failures, use smaller groups or ask the operator to review maintenance task limits. Keep target_file_size_bytes no greater than max_group_size_bytes. This provider does not currently accept a where argument to select a subset of table files for these calls.

Clean up

After the operation finishes, remove only the practice table:

DROP TABLE IF EXISTS maintenance_docs.data_demo;

Continue with delete compaction, manifest optimization, or the procedure reference.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Before you start

Create, compact, and check a table

Read the maintenance result

Sort by a column

Z-order several columns

Size work for the available memory

Clean up

​Before you start

​Create, compact, and check a table

​Read the maintenance result

​Sort by a column

​Z-order several columns

​Size work for the available memory

​Clean up

Before you start

Create, compact, and check a table

Read the maintenance result

Sort by a column

Z-order several columns

Size work for the available memory

Clean up