> ## Documentation Index > Fetch the complete documentation index at: https://docs.e6data.com/llms.txt > Use this file to discover all available pages before exploring further. # Writing data > Prepare a private write namespace as described in Example data(example-data.md). Examples use demo.examples for its private namespace. Each script first recreates its own small tab ReferenceApplies to: Unified engine deployments Prepare a private write namespace as described in [Example data](https://docs.e6data.com/transformation/sql/example-data.md). 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. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 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. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 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](https://docs.e6data.com/transformation/sql/tblproperties.md) page covers the full property workflow, supported keys, defaults, and validation. Use [Table maintenance](https://docs.e6data.com/transformation/sql/maintenance.md) 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. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 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](https://docs.e6data.com/transformation/sql/tables-and-sources.md#file-sources). ### Data files and write records New writes use `data//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 `-manifest-*.avro` and `-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. ```python theme={"theme":{"light":"github-light","dark":"github-dark"}} 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. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 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. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 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. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 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. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 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. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.