Run federated queries on Snowflake (OAuth)

2025-07-02

This page describes how to set up Lakehouse Federation to run federated queries on Snowflake data that is not managed by Azure Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?

To connect to your Snowflake database using Lakehouse Federation, you must create the following in your Azure Databricks Unity Catalog metastore:

A connection to your Snowflake database.
A foreign catalog that mirrors your Snowflake database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Azure Databricks user access to the database.

This page covers connecting to Snowflake using Snowflake's built-in OAuth integration. For other authentication methods, see the following pages:

You can run federated queries on Snowflake using database federation or catalog federation. See Catalog federation.

In database federation, JDBC pushes the Unity Catalog query down to the external database. This is ideal for ad hoc reporting or proof-of-concept work on your ETL pipelines.

In catalog federation, the Unity Catalog query runs directly against file storage. This approach is helpful for incremental migration without code adaptation or as a longer-term hybrid model for organizations that must maintain some data in Snowflake alongside their data registered in Unity Catalog.

Database federation

Before you begin

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.
Azure 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.

Create a security integration

In the Snowflake console, run CREATE SECURITY INTEGRATION. Replace the following values:

<integration-name>: A unique name for your OAuth integration.
<workspace-url>: A Azure Databricks workspace URL. You must set OAUTH_REDIRECT_URI to https://<workspace-url>/login/oauth/snowflake.html, where <workspace-url> is the unique URL of the Azure Databricks workspace where you will create the Snowflake connection.
<duration-in-seconds>: A time length for refresh tokens.

Important

OAUTH_REFRESH_TOKEN_VALIDITY is a custom field that is set to 90 days by default. After the refresh token expires, you must re-authenticate the connection. Set the field to a reasonable time length.

For example:

CREATE SECURITY INTEGRATION <integration-name>
TYPE = oauth
ENABLED = true
OAUTH_CLIENT = custom
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://<workspace-url>/login/oauth/snowflake.html'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = <duration-in-seconds>
OAUTH_ENFORCE_PKCE = TRUE;

Create a connection

A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION SQL command in an Azure Databricks notebook or the Databricks SQL query editor.

Note

You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.

Permissions required: Metastore admin or user with the CREATE CONNECTION privilege.

In your Azure Databricks workspace, click Catalog.
At the top of the Catalog pane, click the Add icon and select Add a connection from the menu.

Alternatively, from the Quick access page, click the External data > button, go to the Connections tab, and click Create connection.
On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.
Select a Connection type of Snowflake.
For Auth type, select OAuth from the drop-down menu.
(Optional) Add a comment.
Click Next.
Enter the following authentication and connection details for your Snowflake warehouse.
- Host: For example, snowflake-demo.east-us-2.azure.snowflakecomputing.com
- Port: For example, 443
- User: For example, snowflake-user
- Client ID: In the Snowflake console, run SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<security-integration-name>') to retrieve the client ID for the security integration.
- Client secret: In the Snowflake console, run SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<security-integration-name>') to retrieve the client secret for the security integration.
- OAuth scope: refresh_token session:role:<role-name>. Specify the Snowflake role to use in <role-name>.
- Sign in with Snowflake: Click and sign in to Snowflake using your OAuth credentials.
  
  Upon successful sign-in, you are directed back to the Set up connection wizard.
Click Create connection.
On the Catalog basics page, enter a name for the foreign catalog. A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Azure Databricks and Unity Catalog.
(Optional) Click Test connection to confirm that it works.
Click Create catalog.
On the Access page, select the workspaces in which users can access the catalog you created. You can select All workspaces have access, or click Assign to workspaces, select the workspaces, and then click Assign.
Change the Owner who will be able to manage access to all objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
Grant Privileges on the catalog. Click Grant:
1. Specify the Principals who will have access to objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
2. Select the Privilege presets to grant to each principal. All account users are granted BROWSE by default.
  - Select Data Reader from the drop-down menu to grant read privileges on objects in the catalog.
  - Select Data Editor from the drop-down menu to grant read and modify privileges on objects in the catalog.
  - Manually select the privileges to grant.
3. Click Grant.
Click Next.
On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.
(Optional) Add a comment.
Click Save.

Case-sensitive database identifiers

The database field of the foreign catalog maps to a Snowflake database identifier. If the Snowflake database identifier is not case-sensitive, the casing you use in the foreign catalog <database-name> is preserved. However, if the Snowflake database identifier is case-sensitive, you must wrap the foreign catalog <database-name> in double quotes to preserve the case.

For example:

database is converted to DATABASE
"database" is converted to database
"database""" is converted to database"

To escape a double quote, use another double quote.
"database"" results in an error because the double quote is not escaped correctly.

For more information, see Identifier requirements in the Snowflake documentation.

Supported pushdowns

The following pushdowns are supported:

Filters
Projections
Limit
Joins
Aggregates (Average, Corr, CovPopulation, CovSample, Count, Max, Min, StddevPop, StddevSamp, Sum, VariancePop, VarianceSamp)
Functions (String functions, Mathematical functions, Data, Time and Timestamp functions, and other miscellaneous functions, such as Alias, Cast, SortOrder)
Windows functions (DenseRank, Rank, RowNumber)
Sorting

Data type mappings

When you read from Snowflake to Spark, data types map as follows:

Snowflake type	Spark type
decimal, number, numeric	DecimalType
bigint, byteint, int, integer, smallint, tinyint	IntegerType
float, float4, float8	FloatType
double, double precision, real	DoubleType
char, character, string, text, time, varchar	StringType
binary	BinaryType
boolean	BooleanType
date	DateType
datetime, timestamp, timestamp_ltz, timestamp_ntz, timestamp_tz	TimestampType

Database federation limitations

The Snowflake OAuth endpoint must be accessible from Azure Databricks control plane IPs. See Outbound from Azure Databricks control plane. Snowflake supports configuring network policies at the security integration level, which allows for a separate network policy that enables direct connectivity from the Azure Databricks control plane to the OAuth endpoint for authorization.
Use Proxy, Proxy host, Proxy port, and Snowflake role configuration options are not supported. Specify Snowflake role as part of the OAuth scope.

Catalog federation

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.
Azure 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 steps for setting up catalog federation are similar to setting up database federation. However, you must also create a storage credential and an external location for the paths to the Apache Iceberg tables registered in Snowflake.

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 Storage credentials and external locations.

You can create an external location using the following interfaces:
- SQL
- Catalog Explorer
The external location will be specified as an authorized path during foreign catalog configuration.
Create a connection to Snowflake Horizon Catalog and create a foreign catalog. 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 database 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).
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 database federation

Foreign Snowflake tables read using catalog federation will display the Iceberg source format in the catalog explorer. Iceberg tables read using database federation will display the Snowflake source format in the catalog explorer.

Federated Snowflake catalogs can contain both Snowflake built-in and Iceberg tables. Queries run against tables with both storage formats will be executed using database federation, which uses Snowflake compute.

Additional resources

See the following articles in the Snowflake documentation:

Share via

Run federated queries on Snowflake (OAuth)

Database federation

Before you begin

Create a security integration

Create a connection

Case-sensitive database identifiers

Supported pushdowns

Data type mappings

Database federation limitations

Catalog federation

Before you begin

Set up catalog federation

Catalog federation limitations

Determine whether a foreign Snowflake table uses catalog or database federation

Additional resources

Feedback

Additional resources