Write support for externally managed Apache Iceberg™ tables¶

Workflow¶

Use the workflow in this section to get started with this feature:

1. Configure an external volume

2. Create a catalog integration

3. Choose from the following options:

• Create a catalog-linked database. With this option, you can write to auto-discovered Iceberg tables in your catalog, or use the catalog-linked database to create additional Iceberg tables.

• Create an Iceberg table (in a catalog-linked database or a standard Snowflake database).

After you complete these steps, you can perform write operations on your Iceberg tables.

Considerations¶

Consider the following when you use write support for externally managed Iceberg tables:

• To write to an externally managed Iceberg table, you must have the OWNERSHIP privilege on the table.

• Snowflake provides Data Definition Language (DDL) and Data Manipulation Language (DML) commands for externally managed tables. However, you configure metadata and data retention using your external catalog and the tools provided by your external storage provider. For more information, see Tables that use an external catalog.

• If you use a catalog-linked database, you can use the CREATE ICEBERG TABLE syntax with column definitions to create a table in Snowflake and in your remote catalog. If you use a standard Snowflake database (not linked to a catalog), you must first create a table in your remote catalog. After that, you can use the CREATE ICEBERG TABLE (Iceberg REST catalog) syntax to create an Iceberg table in Snowflake and write to it.

• Partitioned writes are supported. For more information, see Partitioning for Apache Iceberg™ tables.

• The following features aren’t currently supported when you write to externally managed Iceberg tables:

  • Catalog-vended credentials.

  • Cross-cloud/cross-region support.

  • Azure storage.

  • Server-side encryption (SSE) for external volumes.

  • Conversion to Snowflake-managed tables.

  • External Iceberg catalogs that don’t conform to the Iceberg REST protocol.

  • Row-level deletes (merge-on-read).

• For creating schemas in a catalog-linked database, be aware of the following:

  • The CREATE SCHEMA command creates a corresponding namespace in your remote catalog only when you use a catalog-linked database.

  • The ALTER and CLONE options aren’t supported.

  • Delimiters aren’t supported for schema names. Only alphanumeric schema names are supported.

Configuring an external volume for writes to externally managed tables¶

To enable writes to externally managed tables, you must use an external volume configured with both read and write permissions for the Snowflake service principal (the same permissions required for Snowflake-managed tables). For more information about the required permissions, see Granting Snowflake access to your storage.

You must specify the storage location (STORAGE_BASE_URL) where your Iceberg REST catalog writes Iceberg data and metadata.

For example, the following statement creates an external volume for S3 with encryption that allows write access to a bucket named my-iceberg-tables:

CREATE OR REPLACE EXTERNAL VOLUME my_external_volume
  STORAGE_LOCATIONS =
    (
      (
        NAME = 'my-s3-us-west-2'
        STORAGE_PROVIDER = 'S3'
        STORAGE_BASE_URL = 's3://my-iceberg-tables/'
        STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/my-write-access-role'
      )
    )
  ALLOW_WRITES = TRUE;

For complete instructions, see the following topics:

• Amazon S3

• Google Cloud Storage

Configuring a catalog integration¶

For writing to externally managed Iceberg tables, you must configure a catalog integration to connect Snowflake to your remote catalog.

Your remote catalog must comply with the open source Apache Iceberg REST OpenAPI specification, such as Open Catalog or the AWS Glue Iceberg REST catalog.

Create a catalog-linked database¶

Snowflake supports creating writable externally managed tables in a catalog-linked database, which is a Snowflake database that you sync with an external Iceberg REST catalog. You can also write to Iceberg tables that Snowflake automatically discovers in your remote catalog. For more information, see Use a catalog-linked database for Apache Iceberg™ tables.

The following example creates a catalog-linked database that uses an external volume:

CREATE DATABASE my_linked_db
LINKED_CATALOG = (
  CATALOG = 'my_catalog_int'
),
EXTERNAL_VOLUME = 'my_external_vol';

CREATE SCHEMA <name>;

DROP SCHEMA my_schema;

Create an Iceberg table¶

Creating an externally managed Iceberg table that you can write to from Snowflake varies, depending on the kind of database you use:

• If you use a catalog-linked database, you can use the CREATE ICEBERG TABLE (catalog-linked database) syntax to create a table and register it in your remote catalog. For instructions, see Create an Iceberg table in a catalog-linked database.

• If you use a standard Snowflake database (not linked to a catalog), you must first create a a table in your remote catalog. Then, you can use the CREATE ICEBERG TABLE (Iceberg REST catalog) syntax to create an Iceberg table in Snowflake and write to it. For instructions, see Create an Iceberg table in a standard Snowflake database.

CREATE ICEBERG TABLE [ IF NOT EXISTS ] <table_name>
  [
    --Column definition
    <col_name> <col_type>

    -- Additional column definitions
  [ , <col_name> <col_type> [ ... ] ]
  ]
  [ PARTITION BY ( partitionExpression [, partitionExpression , ...] ) ]
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = <catalog_integration_name> ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]

CREATE OR REPLACE ICEBERG TABLE my_iceberg_table (
  first_name string,
  last_name string,
  amount int,
  create_date date
)
  EXTERNAL_VOLUME = 'my_external_volume'
  CATALOG = 'glue_rest_catalog_int';

CREATE OR REPLACE ICEBERG TABLE my_iceberg_table
  EXTERNAL_VOLUME = 'my_external_volume'
  CATALOG = 'my_rest_catalog_integration'
  CATALOG_TABLE_NAME = 'my_remote_table_name';

Writing to Iceberg tables¶

You can use the following DML commands for externally managed Iceberg tables:

• INSERT

  • Example: Multi-row insert using a query (INSERT INTO … SELECT FROM)

  • Example: INSERT INTO … SELECT FROM (stream)

• UPDATE

• DELETE

• MERGE

• TRUNCATE TABLE

• COPY INTO <table>. For more information, see Load data into Apache Iceberg™ tables.

You can also use the Snowpark API to process Iceberg tables.

INSERT INTO my_iceberg_table VALUES (1, 'a');
INSERT INTO my_iceberg_table VALUES (2, 'b');
INSERT INTO my_iceberg_table VALUES (3, 'c');

UPDATE my_iceberg_table
  SET a = 10
  WHERE b = 'b';

DELETE my_iceberg_table
  WHERE b = 'a';

MERGE INTO my_iceberg_table USING my_snowflake_table
  ON my_iceberg_table.a = my_snowflake_table.a
  WHEN MATCHED THEN
      UPDATE SET my_iceberg_table.b = my_snowflake_table.b
  WHEN NOT MATCHED THEN
      INSERT VALUES (my_snowflake_table.a, my_snowflake_table.b);

COPY INTO customer_iceberg_ingest
  FROM @my_parquet_stage
  FILE_FORMAT = 'my_parquet_format'
  LOAD_MODE = ADD_FILES_COPY
  PURGE = TRUE
  MATCH_BY_COLUMN_NAME = CASE_SENSITIVE;

CREATE OR REPLACE STREAM my_stream ON TABLE my_snowflake_table;

//...

INSERT INTO my_iceberg_table(id,name)
  SELECT id, name
  FROM my_stream;

def copy_into_iceberg():

  try:
      df = session.table("my_snowflake_table")

      df.write.save_as_table("my_iceberg_table")

  except Exception as e:
      print(f"Error processing {table_name}: {e}")

Altering tables¶

You can use the ALTER ICEBERG TABLE command to add, rename, or drop table columns.

ALTER TABLE [ IF EXISTS ] <name>
    ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type>
  | RENAME COLUMN <col_name> TO <new_col_name>
  | DROP [ COLUMN ] [ IF EXISTS ] <col1_name> [, <col2_name> ... ]

Dropping tables¶

When you drop a table in Snowflake, Snowflake also instructs your external Iceberg REST catalog to drop the table.

DROP ICEBERG TABLE [IF EXISTS] <table_name>;

Where:

table_name

Specifies the identifier for the table to drop.

For example, the following command drops the my_iceberg_table from Snowflake. At the same time, Snowflake makes a call to your remote Iceberg catalog instructing it to drop the table and delete the table’s underlying data and metadata.

DROP ICEBERG TABLE my_iceberg_table;