Amazon Redshift introduces update, delete, and merge operations for Apache Iceberg tables

Amazon Redshift has introduced support for row-level UPDATE, DELETE, and MERGE operations on Apache Iceberg tables. This new capability allows users who utilize Iceberg for creating interoperable data lakes to perform data manipulation language (DML) operations directly within Amazon Redshift. This eliminates the need to transfer data to external processing engines, which previously added complexity and delay to data workflows.

Before this update, modifying individual rows in Iceberg tables necessitated the use of separate engines, complicating data pipelines. Now, users can execute UPDATE, DELETE, and MERGE (often referred to as UPSERT) commands on both partitioned and unpartitioned Iceberg tables, including those stored in Amazon S3. The supported Iceberg partition transformations include identity, bucket, truncate, year, month, day, and hour.

The MERGE function is particularly useful as it allows users to integrate insert and update logic within a single command. This is advantageous for common data integration scenarios like change data capture and managing slowly changing dimensions. Importantly, tables altered by Redshift maintain compatibility with other Iceberg-compatible engines, such as Amazon EMR and Amazon Athena, ensuring cross-engine interoperability.

Additionally, AWS Lake Formation permissions are applicable to Iceberg write operations, enhancing security and access management. This new support for UPDATE, DELETE, and MERGE commands on Apache Iceberg tables is available across all AWS Regions where Amazon Redshift operates.

For those interested in getting started, the Amazon Redshift Database Developer Guide contains a section on writing to Apache Iceberg tables, offering comprehensive documentation on the SQL syntax required.