Enable Snowflake catalog federation

This article shows how to enable Snowflake federation using catalog federation. Catalog federation allows Unity Catalog queries to run directly against file storage, providing better performance and cost-effectiveness compared to query federation.

With catalog federation, Unity Catalog directly accesses the foreign table in object storage. The query is only executed using Databricks compute, which is more cost-effective and performance-optimized.

Both catalog federation and query federation rely on the same authentication methods to set up the connection:

• Snowflake OAuth
• Microsoft Entra ID
• OAuth access token
• PEM private key
• Basic authentication (username/password)

Before you begin

You must meet the following requirements to run federated queries on Snowflake using catalog federation:

Workspace requirements:

• Workspace enabled for Unity Catalog.

Compute requirements:

• Network connectivity from your compute resource to the target database systems. See Networking recommendations for Lakehouse Federation.
• Databricks compute must use Databricks Runtime 13.3 LTS or above and Standard or Dedicated access mode.
• SQL warehouses must be pro or serverless and must use 2023.40 or above.

Permissions required:

• To create a connection, you must be a metastore admin or a user with the CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
• To create a foreign catalog, you must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege on the connection.

Additional permission requirements are specified in each task-based section that follows.

• Use Databricks Runtime 16.4 LTS or above or Databricks SQL.

Set up catalog federation

The general steps for setting up catalog federation are similar to setting up query federation: create a connection to Snowflake first, then create and configure a foreign catalog. However, you must also create a storage credential and an external location for the paths to the Apache Iceberg tables registered in Snowflake.

1. Create an external location for Iceberg tables.

   You must configure an external location in Unity Catalog to govern access to the cloud storage locations that hold the Iceberg tables registered in your Snowflake database. External locations are Unity Catalog securable objects that associate storage credentials with cloud storage container paths. See Overview of external locations.

   You can create an external location using the following interfaces:

   • Catalog Explorer
   • SQL

   The external location will be specified as an authorized path during foreign catalog configuration.

2. Create a connection to Snowflake Horizon Catalog and create a foreign catalog using one of the authentication methods listed above. You must specify a location in cloud storage where metadata will be stored for Iceberg tables in this catalog.

   Configuring a storage location on the catalog enables reading Iceberg tables in a foreign catalog via object storage. Reading or writing to Iceberg tables via the JDBC protocol will no longer be available (Non-Iceberg tables will not be affected).

   You must also add Authorized paths, the cloud storage paths that can be accessed via the catalog. Only tables falling under these paths can be queried via the federated catalog. Paths must be covered by external locations.

Catalog federation limitations

Running federated queries on Snowflake using catalog federation has the following limitations:

• Some iceberg tables can only be read using query federation:
  • Tables with URI-incompatible locations (which contain special characters).
  • Tables with metadata location outside of the table location.
  • Tables with locations that have unsupported schemes (only s3, s3a, s3n, abfs, abfss, gs, r2, wasb, and wasbs are supported).
• Foreign Iceberg tables accessed from external engines must be refreshed using ALTER TABLE <table_name> REFRESH to ensure read/write consistency between writes to the external catalog and reads with Unity Catalog.
• Tables with identical names, ignoring case, conflict. The first table to load is the one that appears.
• For Azure, Snowflake Iceberg tables can't be at the container's root location.

See Iceberg table limitations for Iceberg related limitations.

Determine whether a foreign Snowflake table uses catalog or query federation

Foreign Snowflake tables read using catalog federation display the Iceberg source format in Catalog Explorer. Iceberg tables read using query federation display the Snowflake source format in Catalog Explorer. Queries run against tables with both storage formats are executed using query federation, which uses Snowflake compute.

Fallback mode

When you query a table in a federated Snowflake catalog, Databricks automatically tries to read the Iceberg table directly from cloud storage for optimal performance. If the table doesn't meet the criteria for direct access, Databricks falls back to federating the query to Snowflake using JDBC. The following table outlines the query resolution flow:

Query resolution stage	Description
Catalog discovery	During query planning, Databricks Runtime checks with Unity Catalog and detects that the table resides in a Snowflake federated catalog.
Snowflake metadata resolution	Using the Unity Catalog connection, Databricks Runtime issues queries to Snowflake to: Verify that the target table is an Iceberg table. Request the latest metadata.json path and table location.
Direct access attempt	If any of the following criteria fail, Databricks Runtime federates the query using the Snowflake JDBC driver. The table displays the Snowflake source format in Catalog Explorer. Tables with URI-incompatible locations, which contain special characters. Tables with metadata location outside of the table location. Tables with locations that have unsupported schemes. Only s3, s3a, s3n, abfs, abfss, gs, r2, wasb, and wasbs are supported. If all criteria are met, Databricks Runtime checks the returned metadata and reads the Iceberg table directly from the object storage path. The table displays the Iceberg source format in Catalog Explorer.