Sync a Snowflake-managed table with Snowflake Open Catalog¶

Step 1: Set a BASE_LOCATION_PREFIX¶

Snowflake writes the files for each Iceberg table under a directory that includes a dynamically generated string (random ID).

To ensure that Open Catalog can see all of the Snowflake-managed tables that you sync, we recommend that you use a BASE_LOCATION_PREFIX (such as my-open-catalog-tables) at the account, database, or schema level, and omit the BASE_LOCATION parameter in your CREATE ICEBERG TABLE statements. Doing so organizes the files for all Iceberg tables that you create in the account, database, or schema under a known directory with the same name as the prefix. For more information, see Data and metadata directories for Snowflake-managed tables <label-tables_iceberg_configure_external_volume_base_location>.

The following statement sets a BASE_LOCATION_PREFIX for a schema named open_catalog:

ALTER SCHEMA open_catalog
  SET BASE_LOCATION_PREFIX = 'my-open-catalog-tables';

Step 2: Create an external volume¶

If you don’t have one already, start by creating an external volume in Snowflake that provides access to the cloud storage location where you want to store your table data and metadata.

Complete the instructions for your cloud storage service:

• Amazon S3

• Google Cloud Storage

• Azure Storage

Step 3: Configure Open Catalog resources¶

Next, complete the steps in this section to create an external catalog and service connection in your Open Catalog account.

1. Follow the instructions in Create a catalog to create an external catalog in your Open Catalog account. Make sure that the following settings for the external catalog are configured:

   • The External toggle is enabled.

   • The Default base location combines the STORAGE_BASE_URL for the external volume you created in Step 2: Create an external volume and the BASE_LOCATION_PREFIX that you set for the schema; for example https://<storage_base_url>/<base_url_prefix>/.

   Open Catalog syncs your Snowflake-managed tables to this external catalog.

2. If you don’t already have a service connection for Snowflake, follow the instructions in Configure a service connection to create a connection for the Snowflake engine in your Open Catalog account.

3. Configure a catalog role for your external catalog with privileges that allow access to your external catalog. For instructions, see Grant privileges to a catalog.

   The catalog role must have the following privileges on the catalog:

   • TABLE_CREATE

   • TABLE_WRITE_PROPERTIES

   • TABLE_DROP

   • NAMESPACE_CREATE

   • NAMESPACE_DROP

   You can either grant each of these privileges to the catalog role, or grant the CATALOG_MANAGE_CONTENT privilege, which includes these privileges. For more information, see Catalog privileges for Snowflake Open Catalog.

4. Attach the catalog role to the principal role for your service connection. This lets the service connection access the catalog. For instructions, see Grant a catalog role to a principal role.

Step 4: Create a catalog integration for Open Catalog¶

Create a catalog integration for Open Catalog by using the CREATE CATALOG INTEGRATION (Snowflake Open Catalog) command.

For CATALOG_NAME, specify the name of the external catalog that you configured in your Open Catalog account. Snowflake syncs the table and its parent namespace in Snowflake to this external catalog in Open Catalog. For example, if you have a db1.public.table1 Iceberg table registered in Snowflake and you specify catalog1 in the catalog integration, Snowflake syncs the table with Open Catalog with the following fully qualified name: catalog1.db1.public.table1.

To troubleshoot issues with creating a catalog integration, see You can’t create a catalog integration for Open Catalog.

CREATE OR REPLACE CATALOG INTEGRATION my_open_catalog_int
  CATALOG_SOURCE = POLARIS
  TABLE_FORMAT = ICEBERG
  REST_CONFIG = (
    CATALOG_URI = 'https://<orgname>-<my-snowflake-open-catalog-account-name>.snowflakecomputing.cn/polaris/api/catalog'
    CATALOG_NAME = 'myOpenCatalogExternalCatalogName'
  )
  REST_AUTHENTICATION = (
    TYPE = OAUTH
    OAUTH_CLIENT_ID = 'myClientId'
    OAUTH_CLIENT_SECRET = 'myClientSecret'
    OAUTH_ALLOWED_SCOPES = ('PRINCIPAL_ROLE:ALL')
  )
  ENABLED = TRUE;

Step 5: Set up catalog sync¶

For Snowflake to sync Snowflake-managed Iceberg tables to Open Catalog, you must specify the external catalog in Open Catalog that Snowflake should sync the tables to. To configure this, you set the CATALOG_SYNC parameter to the name of a catalog integration for Open Catalog.

• Set CATALOG_SYNC at the database level

• Set CATALOG_SYNC at the schema level

ALTER DATABASE db1 SET CATALOG_SYNC = 'my_open_catalog_int';

CREATE DATABASE db2
  CATALOG_SYNC = 'my_open_catalog_int';

ALTER SCHEMA public SET CATALOG_SYNC = 'my_open_catalog_int';

CREATE SCHEMA schema1
  CATALOG_SYNC = 'my_open_catalog_int';

Step 6: Create a Snowflake-managed table¶

Create a Snowflake-managed Iceberg table by using the CREATE ICEBERG TABLE (Snowflake as the Iceberg catalog) command.

USE SCHEMA open_catalog;

CREATE OR REPLACE ICEBERG TABLE my_iceberg_table (col1 INT)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_external_volume';

For the BASE_LOCATION_PREFIX (my-open-catalog-tables) and table name (my_iceberg_table) used in the previous example statements, Snowflake writes the table files to the following paths:

• STORAGE_BASE_URL/my-open-catalog-tables/my_iceberg_table.randomId/data/

• STORAGE_BASE_URL/my-open-catalog-tables/my_iceberg_table.randomId/metadata/

When you modify the table in Snowflake, the changes are automatically synchronized with the external catalog in your Open Catalog account. Other engines such as Apache Spark™ can query the table by connecting to Open Catalog.

To troubleshoot issues with creating a Snowflake-managed table, see You can’t create a Snowflake-managed table.