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:
Transform
Description
Supported Types & Notes
<column>
Identity partition
Primitive types, DATE, TIMESTAMP_NTZ; TIMESTAMP_LTZ identity partitioning is not yet available
years(col)
Yearly partition
Always uses UTC semantics
months(col)
Monthly partition
Always uses UTC semantics
days(col)
Daily partition
Always uses UTC semantics
hours(col)
Hourly partition
Always uses UTC semantics; requires a TIMESTAMP source column
bucket(N, col)
Hash bucket partition
Distributes values into N buckets using the Iceberg hash function
truncate(col, W)
Truncation partition
Truncates string or integer values to width W
⚠️ Note: Temporal transforms (years, months, days, hours) follow Apache Iceberg UTC semantics and do not accept disabling UTC (e.g., days(ts, false)). The non-Iceberg weeks transform and CLUSTERED BY hash bucketing are not currently available on Managed Iceberg Tables.
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:
Property
Allowed Values
Default
Description
format-version
2 (currently supported)
2
Iceberg format version written to the top-level metadata.json field (not persisted in the regular properties map)
write.delete.mode
merge-on-read, copy-on-write
merge-on-read
Write strategy for DELETE
write.update.mode
merge-on-read, copy-on-write
merge-on-read
Write strategy for UPDATE
write.merge.mode
merge-on-read, copy-on-write
merge-on-read
Write 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.
⚠️ Note:
Table property keys on Managed Iceberg Tables are case-sensitive, following the Iceberg specification. Only exact lowercase keys (such as write.delete.mode) take effect; uppercase variants (such as WRITE.DELETE.MODE) are stored as ordinary custom properties and do not affect write behavior.
Unsetting or downgrading format-version via ALTER TABLE is not supported.
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;
⚠️ Note:
COPY INTO TABLE and Pipes (object storage / Kafka) are supported via the generic SQL write path (check_commit_conflict=false is not yet available for COPY INTO).
IGS (Ingestion Service) real-time/bulk ingestion is not yet available for Managed Iceberg Tables.
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');
⚠️ Note:
Dropped column field IDs are never reused. Re-adding a column with the same name creates a new column that reads as NULL for historical rows.
Partition evolution, sort order evolution, and column masking changes are not yet available at this stage.
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;
⚠️ Note: Direct schema alteration via ALTER TABLE is not supported on a Managed Iceberg Dynamic Table. Modify the Dynamic Table's query definition instead.
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;
⚠️ Note: Managed Iceberg Tables track changes natively through Iceberg snapshots and row positions, so setting the change_tracking property is not needed (specifying 'change_tracking'='true' or 'false' is rejected).