Skip to main content

Run federated queries on Snowflake (Okta)

This page 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.

Learn how to run federated queries on Snowflake data using Okta as the external OAuth provider. This page covers both the user-to-machine (U2M) and machine-to-machine (M2M) flows.

For other authentication methods, see the following pages:

You can run federated queries on Snowflake using query federation or catalog federation.

In query federation, JDBC pushes the Unity Catalog query down to the external database. This is ideal for on-demand 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. See Enable Snowflake catalog 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.
  • 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.

What is external OAuth in Snowflake?​

External OAuth is an authentication method that allows you to use external servers that use OAuth 2.0 for accessing Snowflake. Instead of a user or an application logging into Snowflake directly, they authenticate with the OAuth provider (e.g. Okta, Microsoft Entra ID, or PingFederate).

The OAuth provider then issues an access token, which Databricks presents to Snowflake. Snowflake then verifies the token's signature and claims with the configured security integration, granting access.

Determine an OAuth flow​

To choose the correct OAuth flow for your Snowflake connector, you need to understand the two primary types: U2M (User-to-Machine) and M2M (Machine-to-Machine).

U2M is an authorization flow where the application (Databricks) receives permission to act on behalf of the user. This flow is interactive, meaning the user is prompted to sign in when creating a foreign connection, and the application receives an access token scoped to the user's permissions.

M2M is a non-interactive flow used when an application needs to access resources directly without user involvement. In this flow, the application must be registered in the OAuth provider with a set of permissions, independent of any specific user.

In a Databricks workspace, OAuth refers to U2M authentication, and OAuth Machine to Machine refers to M2M authentication.

Configure Okta​

The exact Okta configuration depends on the needs and policies of your company. The steps in this and following sections serve as a guide to get you to a functional OAuth setup, and represent a simple, working example, not an exhaustive Okta resource. Refer to Okta documentation for in-depth coverage of their platform and features.

Create an OAuth client​

The OAuth-compatible client in Okta represents an application that needs to interact with Okta's authorization server to obtain tokens. In our case, the client represents Databricks.

  1. In the Okta admin console, navigate to Applications, then click Create App Integration.

  2. Choose the OIDC (OpenID Connect) sign-in method.

  3. Select the application type:

    • (U2M only) Choose Native Application.
    • (M2M only) Choose Web Application.

  4. Click Next.

  5. Enter a name for the application.

  6. Select the grant type:

    • (U2M only) Choose Authorization Code and Refresh Token.
    • (M2M only) Choose Client Credentials.

  7. (U2M only) Add https://<WORKSPACE>/login/oauth/snowflake.html as the sign-in redirect URI.

  8. Choose a desired value for controlled access. For testing purposes, Allow everyone in your organization to access is sufficient.

  9. Keep immediate access with Federation Broker Mode enabled.

  10. Click Save.

    You are redirected to the newly created application page.

  11. In the Client Credentials section, make a note of the Client ID value.

  12. Click Edit and select Client secret, then click Save.

  13. Copy the secret and store this value for later.

If you opt to disable immediate access with Federation Broker Mode, you must assign a user in the assignment tab. For more information, see the Okta documentation.

Create an authorization server​

  1. In the Okta admin console, expand the Security section and click API.

  2. Click Add Authorization Server.

  3. Enter a name.

  4. For the audience, use your Snowflake JDBC URL and host, keeping the URL schema (the https prefix).

  5. Click Save. You will be redirected to the newly created authorization server.

  6. In the Scopes tab, click Add Scope. Set the name to your desired Snowflake scope (for example, session:role:PUBLIC). Click Create.

  7. Click Access Policies, then click Add New Access Policy.

  8. Enter a name and a description for the policy.

  9. Assign the policy to the client that you created, then click Create.

  10. In the access policy, click Add rule.

  11. Enter a rule name.

  12. Select the authorized grant types:

    • (U2M only) Choose Authorization Code.
    • (M2M only) Choose Client Credentials.

  13. For scopes, you can select Any scopes or choose the desired scope created earlier.

  14. (U2M only) If you want to receive refresh tokens, include offline_access in the list of scopes.

  15. Configure any additional settings as needed.

  16. Finally, click Create Rule.

Retrieve Okta information​

Make a note of the following information:

  • Client ID and client secret: Retrieve these from the client application you created.

  • OIDC (OpenID Connect) metadata:

    1. On the authorization server page, click the Metadata URI link in the Settings tab.
    2. (U2M only) Copy the authorization_endpoint value in the following format: https://<OKTA_ENV_ID>.okta.com/oauth2/<AUTH_SERVER_ID>/v1/authorize
    3. (M2M only) Copy the Metadata URI link. Snowflake M2M connections in Databricks read this metadata directly and automatically retrieve all necessary information.
    4. Copy the issuer value - this will be needed during Snowflake setup.

Create a security integration​

This step configures a security integration in Snowflake so that Snowflake can communicate securely with Okta, validate tokens, and provide the appropriate data access based on the role associated with the OAuth access token.

  1. Connect to your Snowflake account as a user with the ACCOUNTADMIN role.

  2. Run the CREATE SECURITY INTEGRATION command. For example:

    SQL
    CREATE SECURITY INTEGRATION <OKTA_U2M_SECURITY_INTEGRATION_NAME>
    TYPE = EXTERNAL_OAUTH
    ENABLED = TRUE
    EXTERNAL_OAUTH_TYPE = OKTA
    EXTERNAL_OAUTH_ISSUER = '<OKTA_OAUTH_ISSUER_URL>'
    EXTERNAL_OAUTH_JWS_KEYS_URL = '<OKTA_OAUTH_ISSUER_URL>/v1/keys'
    EXTERNAL_OAUTH_AUDIENCE_LIST = ('<SNOWFLAKE_APPLICATION_ID_URI>')
    EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'
    EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'EMAIL_ADDRESS';

    The example uses the email mapping claim and attribute. This requires that your Snowflake user email matches the email of the Okta user.

    SQL
    ALTER USER <SNOWFLAKE_USER> SET EMAIL = '<YOUR_EMAIL>';

    You can use different claims, depending on your requirements.

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.

  1. In your Databricks workspace, click Data icon. Catalog.

  2. At the top of the Catalog pane, click the Add or plus icon 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 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:

    • Host: For example, snowflake-demo.east-us-2.azure.snowflakecomputing.com
    • Port: For example, 443
    • User: For example, snowflake-user
    • Authorization endpoint: https://<OKTA_ENV_ID>.okta.com/oauth2/<AUTH_SERVER_ID>/v1/authorize
    • Client secret: The client secret that you saved when you created the security integration.
    • Client ID: The client ID that you saved when you created the security integration.
    • OAuth scope: session:role:PUBLIC offline_access
    • OAuth provider: Okta
    • Sign in with Okta: Click and sign in to Snowflake using your Okta credentials.

  9. Click Next.

  10. Enter the following connection details for your connection:

    • Snowflake warehouse: The name of the warehouse you want to use.
    • (Optional) Use proxy: Whether to connect to Snowflake using a proxy server.
    • (Optional) Proxy host: Host of the proxy used to connect to Snowflake. You must also select Use proxy and specify Proxy port.
    • (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 Create connection.

  12. On the Catalog basics page, enter a name for the foreign 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, then click Assign.

  16. Change the Owner who can 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 can access 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.

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

Limitations​

  • The Snowflake OAuth endpoint must be accessible from Databricks control plane IPs. See IP addresses and domains for Databricks services and assets. 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.
Last updated on