Run federated queries on Snowflake

This article describes how to set up Lakehouse Federation to run federated queries on Snowflake data that is not managed by 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 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 Databricks user access to the database.

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

• If you plan to authenticate using OAuth, create a security integration in the Snowflake console.
• If you plan to authenticate using an OAuth access token, you must also request an access token.

(Optional) Create a security integration in the Snowflake console

If you want to authenticate using OAuth, follow this step before you create a Snowflake connection. To authenticate using a username and password instead, skip this section.

note

Only Snowflake's built-in OAuth integration is supported. External OAuth integrations like Okta or Microsoft Entra ID are not supported.

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

• <integration-name>: A unique name for your OAuth integration.

• <workspace-url>: A 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 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.

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;

(Optional) Request an OAuth access token

Follow How To: Generate and use an OAuth token using Snowflake OAuth for custom clients in the Snowflake Knowledge Base.

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

Catalog Explorer

1. In your Databricks workspace, click Catalog.

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

3. On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.

4. Select a Connection type of Snowflake.

5. For Auth type, select Username and password (basic authentication), OAuth access token, PEM Private Key, or OAuth from the drop-down menu.

6. (Optional) Add a comment.

7. Click Next.

8. Enter the following authentication and connection details for your Snowflake warehouse. Properties that are specific to the authentication method you selected are preceded by the Auth type in parentheses.

   • Host: For example, snowflake-demo.east-us-2.azure.snowflakecomputing.com

   • Port: For example, 443

   • User: For example, snowflake-user

   • (Basic authentication) Password: For example, password123

   • (OAuth access token) Access token: Access token from (Optional) Request an OAuth access token.

   • (OAuth access token) Expires in secs: The expiration time (in seconds) for the access token from (Optional) Request an OAuth access token (expires_in).

   • (OAuth) 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.

   • (OAuth) 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) OAuth scope: refresh_token session:role:<role-name>. Specify the Snowflake role to use in <role-name>.

   • (OAuth) Sign in with Snowflake: Click and sign in to Snowflake using your OAuth credentials.

   • (PEM Private Key) PEM private key: Private key from RSA key par in HEX64 format.

   • (PEM Private Key) Expires in secs: The expiration time (in seconds) for the connection made with a private key.

     Upon successful sign-in, you are directed back to the Set up connection wizard.

9. Click Create connection.

10. (Basic authentication) On the Connection details page, specify the following:

    • Snowflake warehouse: For example, my-snowflake-warehouse
    • (Optional) Proxy host: Host of the proxy used to connect to Snowflake. You must also select Use proxy and specify Proxy port.
    • (Optional) Use proxy: Whether to connect to Snowflake using a proxy server.
    • (Optional) Proxy port: Port of the proxy used to connect to Snowflake. You must also select Use proxy and specify Proxy host.
    • (Optional) Snowflake role: The default security role to use for the session after connecting.

11. Click Next.

12. 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 Databricks and Unity Catalog.

13. (Optional) Click Test connection to confirm that it works.

14. Click Create catalog.

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

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

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

18. Click Next.

19. On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.

20. (Optional) Add a comment.

21. Click Save.

SQL

Run the following command in a notebook or the Databricks SQL query editor.

SQL

CREATE CONNECTION <connection-name> TYPE snowflake
OPTIONS (
  host '<hostname>',
  port '<port>',
  sfWarehouse '<warehouse-name>',
  user '<user>',
  password '<password>'
);

We recommend that you use Databricks secrets instead of plaintext strings for sensitive values like credentials. For example:

SQL

CREATE CONNECTION <connection-name> TYPE snowflake
OPTIONS (
  host '<hostname>',
  port '<port>',
  sfWarehouse '<warehouse-name>',
  user secret ('<secret-scope>','<secret-key-user>'),
  password secret ('<secret-scope>','<secret-key-password>')
)

For information about setting up secrets, see Secret management.

Create a foreign catalog

note

If you use the UI to create a connection to the data source, foreign catalog creation is included and you can skip this step.

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 Databricks and Unity Catalog. To create a foreign catalog, you use a connection to the data source that has already been defined.

To create a foreign catalog, you can use Catalog Explorer or the CREATE FOREIGN CATALOG SQL command in a Databricks notebook or the SQL query editor. You can also use the Databricks REST API or the Databricks CLI to create a catalog. See POST /api/2.1/unity-catalog/catalogs and Unity Catalog commands.

Permissions required: CREATE CATALOG permission on the metastore and either ownership of the connection or the CREATE FOREIGN CATALOG privilege on the connection.

Catalog Explorer

1. In your Databricks workspace, click Catalog to open Catalog Explorer.

2. At the top of the Catalog pane, click the Add icon and select Add a catalog from the menu.

   Alternatively, from the Quick access page, click the Catalogs button, and then click the Create catalog button.

3. Follow the instructions for creating foreign catalogs in Create catalogs.

SQL

Run the following SQL command in a notebook or SQL query editor. Items in brackets are optional. Replace the placeholder values:

• <catalog-name>: Name for the catalog in Databricks.
• <connection-name>: The connection object that specifies the data source, path, and access credentials.
• <database-name>: Name of the database you want to mirror as a catalog in Databricks.

SQL

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
OPTIONS (database '<database-name>');

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

OAuth limitations

The following are OAuth support limitations:

• The Snowflake OAuth endpoint must be accessible from Databricks control plane IPs. See Outbound IPs from 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 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.

PEM private key limitations

The following are PEM private key support limitations:

• Snowflake JDBC driver does not support authentication with encrypted private keys. To avoid errors, generate a key with -nocrypt option added, as follows:

  openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

Additional resources

See the following articles in the Snowflake documentation:

• Configure Snowflake OAuth for custom clients
• SQL reference: CREATE SECURITY INTEGRATION (Snowflake OAuth)
• Snowflake: Key-pair authentication and key-pair rotation