Configure Data Destination - Snowflake

  • Updated

Configuring a Data Destination to Snowflake.

At One Model, we think it's critical that your data goes to where you need it most. If Snowflake is your platform of choice, you can use our Snowflake Data Destination to send data from One Model to Snowflake.

Choose an authentication method

One Model can connect to Snowflake using either of the methods below. Whichever you choose, start with Step 1. Configure Snowflake Role and finish with Step 4. One Model Configuration.

  • External OAuth (client credentials) – uses your Identity Provider to issue short-lived tokens. Complete Step 2 and Step 3.
  • Programmatic access token – a Snowflake-generated token used in place of a password. Simpler to set up, but the token expires and must be rotated. Skip Steps 2 and 3 and follow Using a programmatic access token instead.

Note: Snowflake is phasing out single-factor password sign-in for users, so we don't recommend a standard Snowflake password. Key-pair authentication is not currently supported.

1. Configure Snowflake Role

The Snowflake role can be configured using the following commands

-- The required permissions for snowflake role to be able to use data destination 

CREATE ROLE IF NOT EXISTS snowflake_om; -- this can be named what you want

GRANT USAGE

  ON WAREHOUSE COMPUTE_WH -- your warehouse

  TO ROLE snowflake_om;

GRANT USAGE

  ON DATABASE SNOWFLAKE_DB -- your db

  TO ROLE snowflake_om;

GRANT CREATE INTEGRATION -- will need account with higher privilege

  ON ACCOUNT TO ROLE snowflake_om;

USE DATABASE SNOWFLAKE_DB;

SHOW GRANTS TO ROLE snowflake_om;

-- Access to create a schema can be given by:

GRANT CREATE SCHEMA 

    on DATABASE SNOWFLAKE_DB 

    TO ROLE snowflake_om;

-- Access to use a schema can be given by:

GRANT USAGE, CREATE STAGE, CREATE FILE FORMAT, CREATE TABLE

    on SCHEMA TEST -- your schema

    TO ROLE snowflake_om;

Note: we also recommend setting QUOTED_IDENTIFIERS_IGNORE_CASE to True at the account level if you don't want One Model to create tables as lower-case. This can be done using the following command:

ALTER ACCOUNT SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE;

2. Configure your Identity Provider (External OAuth only)

In this example, we're using Amazon Cognito.

A. Configure the User Pool

Firstly navigate to User Pools, and select "Create user Pool"

Specify "Machine-to-machine application", and then name your application. This does not need to match the Role configured in snowflake, but in this case we will keep them similar.

Then select "Create user directory"

Finally, the pool is created. 

Note: We will use the Token signing by URL later.

 

B. Configure the Client Settings

In the User Pool created above, navigate to App clients, and select the application created above

You will then see  Client ID and Client Secret that we will use later

 

C. Resource Server

The next step is to create a Resource server. Navigate to  Userpool > Domain > Create resource server 

This will return the following page.

Note: We will use the Resource server identifier and the Scope Name later

 

D. Lambda

You will need to create following nodejs lambda. This can use the following code:

export const handler = function(event, context) {

  var claims = {}

  claims["aud"]= event.callerContext.clientId;

  claims["scp"]="session:role:snowflake_m2m_role";

  event.response = {

      "claimsAndScopeOverrideDetails": {

          "idTokenGeneration": {

              "claimsToAddOrOverride": claims

           

          },

          "accessTokenGeneration": {

              "claimsToAddOrOverride": claims

          }

      }

  };

  context.done(null, event);

};

Back in Amazon Cognito, add the Lambda as an Extension:

and set the following:

  • Type: Authentication
  • Authentication: Pre token generation trigger
  • Trigger event version: Basic Features + access token customization for user and machine identities - Recommended
  • Assign Lambda Function: Reference the function created in the previous step

 

3. Configure the Integration in Snowflake (External OAuth only)

A. Create the Integration

Add new integration using the code below:

-- Integration for oauth credential flow

CREATE OR REPLACE SECURITY INTEGRATION external_oauth_cognito_m2m -- this can be named whatever you want

    TYPE = EXTERNAL_OAUTH

    ENABLED = TRUE

    EXTERNAL_OAUTH_TYPE = CUSTOM

    EXTERNAL_OAUTH_ISSUER = 'https://cognito-idp.ap-southeast-2.amazonaws.com/ap-southeast-2_hN8H7rsZo' -- User pool =>  App client Client settings

    EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://cognito-idp.ap-southeast-2.amazonaws.com/ap-southeast-2_hN8H7rsZo/.well-known/jwks.json' -- User pool => Overview User pool

    EXTERNAL_OAUTH_AUDIENCE_LIST = ('188u34ndj6i7mfhqbbcm22am3b') -- need to use lambda with pre token generations in cognito under EXTENSIONS. It is also 

    EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'

    EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE'

    EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'LOGIN_NAME'

    EXTERNAL_OAUTH_SCOPE_MAPPING_ATTRIBUTE = 'scp' -- the field in your response token from idp that decides the role being used format session:role:snowflake_m2m_role

    EXTERNAL_OAUTH_ALLOWED_ROLES_LIST = ('snowflake_m2m_role'); -- must be in aws pre token generation lambda needs to add scp (default scope field in token look at line 14) to claim

    

 

B. Create the Snowflake User

Use the following code to create the user 

CREATE USER 188u34ndj6i7mfhqbbcm22am3b -- client id of cognito app client and in token response from idp field sub

PASSWORD = 'MyPassword123!@#' -- set this to the desired value

DEFAULT_ROLE = snowflake_om; -- role we created earlier 

 

 

Using a programmatic access token (alternative to Steps 2 and 3)

If you're not using External OAuth, you can authenticate with a Snowflake programmatic access token (PAT). Snowflake supports using a PAT in place of a password, so One Model connects as if using a username and password.

A. Create the Snowflake user

Create a dedicated service user for One Model and grant it the role from Step 1:

CREATE USER IF NOT EXISTS onemodel_svc -- this can be named what you want
  TYPE = SERVICE
  DEFAULT_ROLE = snowflake_om; -- role we created in Step 1

GRANT ROLE snowflake_om TO USER onemodel_svc;

Note: Snowflake requires a user to be subject to a network policy before a programmatic access token can be used. If your account doesn't already apply one to this user, add a network policy that allows One Model's IP addresses (contact One Model Support if you need these).

Authentication policies: if an authentication policy already applies to this user or your account, make sure its AUTHENTICATION_METHODS list includes 'PROGRAMMATIC_ACCESS_TOKEN', otherwise the token will be rejected.

B. Generate the token

  1. In Snowflake, go to Governance & Security > Users & roles and select the One Model service user.
  2. Under Programmatic access tokens, select Generate token.
  3. Restrict the token to the role configured in Step 1, and set an expiry that suits your security policy. (Note: Snowflake defaults to 15 days, so we recommend setting a longer expiry to avoid frequent rotation)
  4. Copy the token. Snowflake only shows it once.

Important: when the token expires, the Data Destination will fail to connect. Generate a new token before the expiry date and update the Password field in One Model.

4. One Model Configuration

The following settings will need to be configured for a Snowflake Data Destination:

  • Host - The host URI of your Snowflake account
    • Note: This should not have https:// or http:// as starting characters.
  • Port - The port used to connect to Snowflake. 443 is the default
  • Database - The Database in Snowflake that data is destined for. This field is Case Sensitive
  • Warehouse - The Warehouse in Snowflake that the data is destined for. This field is Case Sensitive.
  • Schema - The Schema configured in Snowflake that the data is destined for. This field is Case Sensitive
  • Username - The username of the Snowflake user that One Model will connect through. We recommend setting up a user in Snowflake specifically for One Model so that it can have the required Snowflake permissions applied
  • Authentication Method - External OAuth, or Username/Password if using a programmatic access token
  • Token Endpoint (External OAuth only) - see Step 2. A.
  • Client ID (External OAuth only) - see step 2. B. Client Settings
  • Client Secret (External OAuth only) - see step 2. B. Client Settings
  • Requested Scopes (External OAuth only) - This is a combination of the Resource server identifier and the Scope Name from Step 2. C.. The format should be <Resource server identifier>/<Scope name>
  • Account - For External OAuth, your Account Identifier in your IdP. For a programmatic access token, your Snowflake account identifier
  • Role - The role that has been configured with the required permissions to upload data, see Step 1. This field is Case Sensitive.

Was this article helpful?

0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.