Create and Use Managed Iceberg Tables

This guide covers creating Managed Iceberg Tables in Singdata Lakehouse, configuring hidden partitioning and row-level write modes, running DML and schema evolution, and integrating with Dynamic Tables and Table Streams.

Creating Managed Iceberg Tables

Add the USING ICEBERG clause to a CREATE TABLE statement to create a Managed Iceberg Table. By default, Singdata Lakehouse initializes the table metadata using the currently supported Iceberg format version (v2).

Basic Table Creation

CREATE TABLE IF NOT EXISTS events_iceberg ( event_id BIGINT, user_id BIGINT, event_type STRING, amount DECIMAL(10, 2), event_time TIMESTAMP ) USING ICEBERG COMMENT 'Managed Iceberg events table';

Partitioned Tables (Hidden Partitioning)

Managed Iceberg Tables support identity partitioning and standard Iceberg hidden partition transforms:

TransformDescriptionSupported Types & Notes
<column>Identity partitionPrimitive types, DATE, TIMESTAMP_NTZ; TIMESTAMP_LTZ identity partitioning is not yet available
years(col)Yearly partitionAlways uses UTC semantics
months(col)Monthly partitionAlways uses UTC semantics
days(col)Daily partitionAlways uses UTC semantics
hours(col)Hourly partitionAlways uses UTC semantics; requires a TIMESTAMP source column
bucket(N, col)Hash bucket partitionDistributes values into N buckets using the Iceberg hash function
truncate(col, W)Truncation partitionTruncates string or integer values to width W

-- Daily hidden partition CREATE TABLE orders_iceberg ( order_id BIGINT, user_id BIGINT, category STRING, amount DECIMAL(10, 2), ds STRING, created_at TIMESTAMP ) USING ICEBERG PARTITIONED BY (days(created_at)); -- String identity partition CREATE TABLE daily_orders_iceberg ( order_id BIGINT, user_id BIGINT, amount DECIMAL(10, 2), ds STRING ) USING ICEBERG PARTITIONED BY (ds); -- Bucket hidden partition CREATE TABLE user_events_iceberg ( user_id INT, event_name STRING ) USING ICEBERG PARTITIONED BY (bucket(16, user_id));

Creating Tables with CTAS

You can create and populate a Managed Iceberg Table from a query using CREATE TABLE ... USING ICEBERG AS SELECT:

CREATE TABLE high_value_orders USING ICEBERG AS SELECT order_id, user_id, amount, created_at FROM orders_iceberg WHERE amount >= 1000;

Table Properties and Row-Level Write Modes (MoR / CoW)

Managed Iceberg Tables use standard Iceberg table properties to control how row-level DML (DELETE, UPDATE, MERGE INTO) executes:

PropertyAllowed ValuesDefaultDescription
format-version2 (currently supported)2Iceberg format version written to the top-level metadata.json field (not persisted in the regular properties map)
write.delete.modemerge-on-read, copy-on-writemerge-on-readWrite strategy for DELETE
write.update.modemerge-on-read, copy-on-writemerge-on-readWrite strategy for UPDATE
write.merge.modemerge-on-read, copy-on-writemerge-on-readWrite strategy for MERGE INTO
  • Merge-on-Read (MoR, default): Writes lightweight position delete files for faster writes and merges deletes at read time. Ideal for frequent updates or deletes.
  • Copy-on-Write (CoW): Rewrites affected Parquet data files directly without producing delete files. Ideal for read-heavy workloads sensitive to read latency.

Configuring and Modifying Write Modes

-- Create a table with Copy-on-Write for all row-level DML CREATE TABLE orders_cow ( order_id INT, status STRING, amount DECIMAL(10, 2) ) USING ICEBERG PROPERTIES ( 'write.delete.mode' = 'copy-on-write', 'write.update.mode' = 'copy-on-write', 'write.merge.mode' = 'copy-on-write' ); -- Update a specific DML write mode ALTER TABLE orders_cow SET PROPERTIES ('write.delete.mode' = 'merge-on-read'); -- Unset a property (falls back to the default merge-on-read behavior) ALTER TABLE orders_cow UNSET PROPERTIES ('write.update.mode');

Data Writes and Row-Level DML

Managed Iceberg Tables support standard SQL inserts, overwrites, and row-level mutations:

-- Append rows INSERT INTO orders_cow VALUES (1, 'pending', 100.00), (2, 'paid', 250.50); -- Row-level update UPDATE orders_cow SET status = 'paid' WHERE order_id = 1; -- Row-level delete DELETE FROM orders_cow WHERE order_id = 2; -- MERGE INTO upsert MERGE INTO orders_cow t USING (SELECT 1 AS order_id, 'shipped' AS status, 100.00 AS amount) s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount WHEN NOT MATCHED THEN INSERT VALUES (s.order_id, s.status, s.amount); -- Overwrite and truncate INSERT OVERWRITE TABLE orders_cow VALUES (10, 'paid', 500.00); TRUNCATE TABLE orders_cow;

Schema Evolution and Partition Operations

Managed Iceberg Tables resolve columns by Iceberg field id, enabling safe schema evolution without rewriting existing data files:

-- Add a column (existing rows read back as NULL) ALTER TABLE orders_cow ADD COLUMN currency STRING; -- Rename a column (preserves field id; existing rows remain readable under the new name) ALTER TABLE orders_cow RENAME COLUMN status TO order_status; -- Widen a column type (Iceberg-compatible promotions such as INT -> BIGINT, FLOAT -> DOUBLE, DECIMAL precision widening) ALTER TABLE orders_cow CHANGE COLUMN order_id TYPE BIGINT; -- Reorder columns (supports FIRST / AFTER <col>) ALTER TABLE orders_cow CHANGE COLUMN amount FIRST; ALTER TABLE orders_cow ADD COLUMN remark STRING AFTER order_id; -- Update a column comment ALTER TABLE orders_cow CHANGE COLUMN order_status COMMENT 'Current order status'; -- Drop a column ALTER TABLE orders_cow DROP COLUMN currency; -- Drop a partition (partitioned tables only) ALTER TABLE daily_orders_iceberg DROP PARTITION (ds = '2025-02-01');

Working with Dynamic Tables and Table Streams

Dynamic Tables

Managed Iceberg Tables can serve as both upstream sources and target Dynamic Tables with incremental refresh:

CREATE DYNAMIC TABLE category_sales_iceberg ( category STRING, total_amount DECIMAL(18, 2), order_count BIGINT ) USING ICEBERG AS SELECT category, SUM(amount), COUNT(*) FROM orders_iceberg GROUP BY category; REFRESH DYNAMIC TABLE category_sales_iceberg;

Table Streams (Change Data Capture)

You can create a Table Stream directly on a Managed Iceberg Table to consume incremental row changes (INSERT / DELETE):

CREATE TABLE STREAM orders_iceberg_stream ON TABLE orders_iceberg WITH PROPERTIES ('TABLE_STREAM_MODE' = 'standard'); -- Query captured changes SELECT __change_type, * FROM orders_iceberg_stream;