Writing data

ReferenceApplies to: Unified engine deployments

Prepare a private write namespace as described in Example data. Examples use demo.examples for its private namespace. Each script first recreates its own small table from SF1, making that example independent and repeatable. Replace the example names with your catalog, source namespace, and private write namespace. Scripts are not transactions. IRC object creation requires exact, case-sensitive names. Each script sets spark.sql.caseSensitive to true before creating its lab table.

INSERT INTO

Append rows in target-column order, or supply an explicit target column list when supported. Avoid repeating an append when the earlier commit is uncertain.

SET spark.sql.caseSensitive = true;
DROP TABLE IF EXISTS demo.examples.append_demo;
CREATE TABLE demo.examples.append_demo (i_item_sk BIGINT, i_item_id STRING) USING iceberg;
INSERT INTO demo.examples.append_demo
SELECT i_item_sk, i_item_id FROM demo.tpcds.item WHERE i_item_sk <= 3;
INSERT INTO demo.examples.append_demo
SELECT i_item_sk, i_item_id FROM demo.tpcds.item WHERE i_item_sk = 4;
SELECT * FROM demo.examples.append_demo ORDER BY i_item_sk;

The result contains keys 1–4.

INSERT OVERWRITE

Whole-table overwrite replaces the current contents. Static partitions, dynamic partitions, and predicate overwrite have additional target capability and partition validation requirements.

SET spark.sql.caseSensitive = true;
DROP TABLE IF EXISTS demo.examples.overwrite_demo;
CREATE TABLE demo.examples.overwrite_demo (i_item_sk BIGINT, i_item_id STRING) USING iceberg;
INSERT INTO demo.examples.overwrite_demo
SELECT i_item_sk, i_item_id FROM demo.tpcds.item WHERE i_item_sk <= 3;
INSERT OVERWRITE TABLE demo.examples.overwrite_demo
SELECT i_item_sk, i_item_id FROM demo.tpcds.item WHERE i_item_sk IN (4, 5);
SELECT * FROM demo.examples.overwrite_demo ORDER BY i_item_sk;

The result contains keys 4 and 5.

Iceberg write options

The dedicated TBLPROPERTIES page covers the full property workflow, supported keys, defaults, and validation. Use Table maintenance to change the layout of existing files. Use TBLPROPERTIES to choose the Parquet layout for an Iceberg table. These settings stay on the table and apply to later writes. PARTITIONED BY defines how rows are split into Iceberg partitions. This example copies twelve generated items into a private table. It sets file and Parquet targets, then rewrites the table with a different compression codec.

SET spark.sql.caseSensitive = true;
DROP TABLE IF EXISTS demo.examples.write_options_demo;
CREATE TABLE demo.examples.write_options_demo (
  i_item_sk BIGINT,
  i_item_id STRING
)
USING iceberg
PARTITIONED BY (bucket(4, i_item_sk))
TBLPROPERTIES (
  'write.target-file-size-bytes' = '1048576',
  'write.parquet.compression-codec' = 'zstd',
  'write.parquet.compression-level' = '3',
  'write.parquet.row-group-size-bytes' = '65536',
  'write.parquet.page-size-bytes' = '8192',
  'write.parquet.page-row-limit' = '64',
  'write.parquet.dict-size-bytes' = '8192'
);
INSERT INTO demo.examples.write_options_demo
SELECT i_item_sk, i_item_id FROM demo.tpcds.item WHERE i_item_sk <= 12;
ALTER TABLE demo.examples.write_options_demo SET TBLPROPERTIES (
  'write.parquet.compression-codec' = 'gzip',
  'write.parquet.compression-level' = '6'
);
INSERT OVERWRITE TABLE demo.examples.write_options_demo
SELECT i_item_sk, i_item_id FROM demo.tpcds.item WHERE i_item_sk <= 12;
SELECT * FROM demo.examples.write_options_demo ORDER BY i_item_sk;

ALTER affects later writes. It does not rewrite existing files. Use a new table or a whole-table overwrite when a benchmark needs one layout across the dataset. For CSV, create a local temporary file view with an explicit schema and INSERT from that view in the same session. Its path needs configured storage access. See File sources.

Data files and write records

New writes use data/<write-id>/part-*.parquet for unpartitioned tables. Writes that resume from individual batches add a batch directory below the write ID. Partitioned tables also add a partition directory. The write ID keeps concurrent attempts separate and stays the same when the saved attempt resumes. Recovery records such as begin.json and prepared.json are stored under metadata/_transactions/. They record write progress and prepared output so a retry can avoid publishing the same write twice. They are not table rows. New manifest files use <commit-id>-manifest-*.avro and <commit-id>-list.avro under metadata/. Existing files remain at their recorded locations. Saved writes from older versions keep their original paths when retried. Read tables through the catalog so their snapshots and delete files are applied.

Supported Parquet settings

A Parquet row group contains rows within a file. Pages hold smaller blocks of column data. A dictionary holds repeated values that pages can refer to by code.

Table property	Meaning	Default before runtime limits
`write.target-file-size-bytes`	Approximate file target	536870912 bytes (512 MiB)
`write.parquet.compression-codec`	Column compression codec	`zstd`
`write.parquet.compression-level`	Level for Zstd, Gzip, or Brotli	3, 6, or 1 for that codec
`write.parquet.row-group-size-bytes`	Approximate row-group target	134217728 bytes (128 MiB)
`write.parquet.page-size-bytes`	Approximate data-page target	1048576 bytes (1 MiB)
`write.parquet.page-row-limit`	Data-page row limit	20000 rows
`write.parquet.dict-size-bytes`	Dictionary target before falling back to plain values	2097152 bytes (2 MiB)

The encoder accepts uncompressed, snappy, zstd, gzip, brotli, lz4, and lz4_raw. LZO is unsupported. Compression levels must fit the chosen codec. A level with Snappy, LZ4, or uncompressed output is rejected. Sizes and row limits must be positive. Unknown write.parquet.* and write.target-* keys fail when a write is planned. Other custom table properties remain allowed. Content-defined chunking chooses page boundaries from data values before encoding and compression. It defaults to disabled.

Table property	Default
`write.parquet.content-defined-chunking.enabled`	`false`
`write.parquet.content-defined-chunking.min-chunk-size`	262144 bytes
`write.parquet.content-defined-chunking.max-chunk-size`	1048576 bytes
`write.parquet.content-defined-chunking.norm-level`	0

When enabled, the minimum must be positive and the maximum must be greater than the minimum. The encoder also checks their range.

Partition transforms

Form in PARTITIONED BY	Rows grouped together
`column`	Same column value
`bucket(16, id)`	Same hash bucket, from 0 to 15
`truncate(10, id)`	Same truncated value
`years(event_time)`	Same year
`months(event_time)`	Same month
`days(event_time)`	Same day
`hours(event_time)`	Same hour

Several transforms can be combined. Time transforms require a compatible date or timestamp; hour partitioning requires a timestamp. Null partition values remain null. Per-write options cannot change an existing table’s partition spec.

Options for one DataFrame write

Per-write overrides are available through Spark Connect’s DataFrame API for existing tables. SQL INSERT has no per-write OPTIONS clause. The full property keys above are accepted. compression, compression-level, and target-file-size-bytes are aliases.

incoming.writeTo("lakehouse.benchmark.events").option(
    "compression", "gzip"
).option("compression-level", "6").option(
    "target-file-size-bytes", "1048576"
).append()

Per-write settings take priority over the saved table properties, then encoder defaults. They do not change catalog properties or later writes. A codec change drops an inherited level for a different codec unless the write supplies a level. Conflicting aliases and unknown per-write keys fail. Use table properties when creating a table; create-time per-write option mapping remains unsupported.

File-size limits

Files still close at input-batch boundaries. A 128 MiB target cannot combine many small batches into one 128 MiB file. File, row-group, page, and dictionary targets are capped by the runtime buffer budget. Compression, encoding, and metadata affect the final byte size. Final files in a partition can be smaller. Per-column Parquet controls, enforced write sort order, and parallel writer distribution remain unsupported in this write path.

UPDATE

SET assigns new values to rows selected by WHERE. Omitting WHERE selects all rows. Mutations require a connector that supplies row identity and supports the planned write.

SET spark.sql.caseSensitive = true;
DROP TABLE IF EXISTS demo.examples.update_demo;
CREATE TABLE demo.examples.update_demo (i_item_sk BIGINT, i_item_id STRING, price DOUBLE) USING iceberg;
INSERT INTO demo.examples.update_demo
SELECT i_item_sk, i_item_id, CAST(i_current_price AS DOUBLE) AS price
FROM demo.tpcds.item WHERE i_item_sk <= 3;
UPDATE demo.examples.update_demo SET price = CAST(9.99 AS DOUBLE) WHERE i_item_sk = 1;
SELECT * FROM demo.examples.update_demo ORDER BY i_item_sk;

DELETE

DELETE removes matching rows; without WHERE it selects the whole target. It changes the table snapshot rather than merely hiding rows in a query.

SET spark.sql.caseSensitive = true;
DROP TABLE IF EXISTS demo.examples.delete_demo;
CREATE TABLE demo.examples.delete_demo (i_item_sk BIGINT, i_item_id STRING) USING iceberg;
INSERT INTO demo.examples.delete_demo
SELECT i_item_sk, i_item_id FROM demo.tpcds.item WHERE i_item_sk <= 3;
DELETE FROM demo.examples.delete_demo WHERE i_item_sk = 2;
SELECT * FROM demo.examples.delete_demo ORDER BY i_item_sk;

MERGE

MERGE joins target and source using ON, then applies matching clauses in order. Ensure the source does not produce multiple conflicting matches for a target row. Supported clauses include matched UPDATE/DELETE and not-matched INSERT, subject to connector capabilities.

SET spark.sql.caseSensitive = true;
DROP TABLE IF EXISTS demo.examples.merge_demo;
CREATE TABLE demo.examples.merge_demo (i_item_sk BIGINT, i_item_id STRING, i_category STRING) USING iceberg;
INSERT INTO demo.examples.merge_demo
SELECT i_item_sk, i_item_id, i_category FROM demo.tpcds.item WHERE i_item_sk <= 2;
MERGE INTO demo.examples.merge_demo AS target
USING (SELECT i_item_sk, i_item_id, i_category FROM demo.tpcds.item WHERE i_item_sk IN (2, 3)) AS source
ON target.i_item_sk = source.i_item_sk
WHEN MATCHED THEN UPDATE SET i_category = 'Updated'
WHEN NOT MATCHED THEN INSERT (i_item_sk, i_item_id, i_category)
VALUES (source.i_item_sk, source.i_item_id, source.i_category);
SELECT * FROM demo.examples.merge_demo ORDER BY i_item_sk;

TRUNCATE TABLE

TRUNCATE removes all rows through the target’s whole-table write path. Partition and inheritance modifiers are not accepted.

SET spark.sql.caseSensitive = true;
DROP TABLE IF EXISTS demo.examples.truncate_demo;
CREATE TABLE demo.examples.truncate_demo (i_item_sk BIGINT) USING iceberg;
INSERT INTO demo.examples.truncate_demo
SELECT i_item_sk FROM demo.tpcds.item WHERE i_item_sk <= 3;
TRUNCATE TABLE demo.examples.truncate_demo;
SELECT COUNT(*) AS remaining_rows FROM demo.examples.truncate_demo;

The count is zero. Acceptance, task execution, and confirmed publication are separate stages; use the execution details to inspect an uncertain outcome before starting a new write.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

INSERT INTO

INSERT OVERWRITE

Iceberg write options

Data files and write records

Supported Parquet settings

Partition transforms

Options for one DataFrame write

File-size limits

UPDATE

DELETE

MERGE

TRUNCATE TABLE

​INSERT INTO

​INSERT OVERWRITE

​Iceberg write options

​Data files and write records

​Supported Parquet settings

​Partition transforms

​Options for one DataFrame write

​File-size limits

​UPDATE

​DELETE

​MERGE

​TRUNCATE TABLE

INSERT INTO

INSERT OVERWRITE

Iceberg write options

Data files and write records

Supported Parquet settings

Partition transforms

Options for one DataFrame write

File-size limits

UPDATE

DELETE

MERGE

TRUNCATE TABLE