Skip to content

Setting up a Snowflake Connection

Snowflake is a cloud data platform where many finance and operations teams keep their actuals, plans, and reference data. Nexadata integrates directly with Snowflake in both directions: you can read the results of a SQL statement into a Nexadata Dataset, transform and harmonize it, and write the results back to a Snowflake table.

Here are the key features:

  • SQL reads on your warehouse: A Dataset runs your own SQL statement on a Snowflake warehouse you choose, so you can filter, join, and aggregate before any data moves.

  • Write-back to Snowflake tables: Send Workflow outputs to a Snowflake table with Append, Upsert, or Overwrite. Amounts keep their decimal precision, so totals tie to the cent.

  • Two authentication types: Connect as a person with OAuth, or as a dedicated service user with a Key-pair. Neither one stores a Snowflake password in Nexadata.

  • Secure credential storage: Secrets, keys, and tokens are encrypted and stored server-side.

Snowflake has no settings screen for this kind of setup. Instead, a Snowflake admin runs a few SQL statements once per Snowflake account, and this article gives you each one to copy and paste. Parts 1 and 2 are usually completed with your Snowflake admin, who needs the SECURITYADMIN or ACCOUNTADMIN role. Part 3 takes place in Nexadata.


A Snowflake connection uses one of two authentication types. Both can read and write.

Authentication typeWho Nexadata acts asHow it signs inBest for
OAuthThe Snowflake user who signs inYou sign in to Snowflake once and approve accessTeams that want every action tied to a named user
Key-pairA service user created for NexadataNexadata signs each request with a private key; nobody signs inScheduled, unattended Workflows

OAuth connects Nexadata as a real Snowflake user. Your admin creates an OAuth integration, an entry that tells Snowflake that Nexadata is allowed to ask users for access. When you click Connect to Snowflake, you sign in to Snowflake yourself and approve the request, the same way you might allow an app to use your Google account. Nexadata then acts as you, with the role you approved, and every query is recorded under your name. Snowflake also gives Nexadata a refresh token, which lets your scheduled Workflows keep running without asking you to sign in again until the token expires.

Key-pair connects Nexadata as a service user: a Snowflake user made for an application rather than a person. It has no password and cannot sign in through a browser. Instead, your admin creates a pair of matching keys. The public key is stored on the Snowflake user, and the private key is pasted into Nexadata. Snowflake trusts a request only when it is signed with the private key that matches. Because no person signs in, the connection keeps working when people change roles or leave.

Both authentication types need the same two things in Snowflake:

  • A role that holds exactly the privileges Nexadata needs. In Snowflake, privileges are granted to roles, and roles are granted to users.

  • A warehouse that role can use. A warehouse is the compute that runs queries and loads, and it is billed while it runs.


Run these statements in a Snowsight worksheet as a user with SECURITYADMIN or ACCOUNTADMIN. Replace the placeholders in angle brackets with your own names. The examples use the role NEXADATA_ROLE and the warehouse NEXADATA_WH; you can keep those names or choose your own.

Step 1: Create the Role, Warehouse, and Grants

Section titled “Step 1: Create the Role, Warehouse, and Grants”

This step is the same for both authentication types. It gives Nexadata read access to the schema it reads from, and write access to the schema it writes to, and nothing else.

-- 1. A role for Nexadata
USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS NEXADATA_ROLE;
GRANT ROLE NEXADATA_ROLE TO ROLE SYSADMIN;
-- 2. A warehouse for Nexadata (skip if you will use an existing one)
USE ROLE SYSADMIN;
CREATE WAREHOUSE IF NOT EXISTS NEXADATA_WH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;
-- 3. Grants
USE ROLE SECURITYADMIN;
GRANT USAGE ON WAREHOUSE NEXADATA_WH TO ROLE NEXADATA_ROLE;
GRANT USAGE ON DATABASE <DATABASE> TO ROLE NEXADATA_ROLE;
-- Read: the schema Nexadata reads from
GRANT USAGE ON SCHEMA <DATABASE>.<SOURCE_SCHEMA> TO ROLE NEXADATA_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA <DATABASE>.<SOURCE_SCHEMA> TO ROLE NEXADATA_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <DATABASE>.<SOURCE_SCHEMA> TO ROLE NEXADATA_ROLE;
-- Write: the schema Nexadata writes to
GRANT USAGE, CREATE TABLE, CREATE STAGE ON SCHEMA <DATABASE>.<TARGET_SCHEMA> TO ROLE NEXADATA_ROLE;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA <DATABASE>.<TARGET_SCHEMA> TO ROLE NEXADATA_ROLE;
GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA <DATABASE>.<TARGET_SCHEMA> TO ROLE NEXADATA_ROLE;

Here is what each piece does, in business terms:

GrantWhy Nexadata needs it
USAGE on the warehouseTo run queries and loads. Without it, the warehouse does not appear in Nexadata.
USAGE on the database and schemasTo see that they exist. Snowflake hides anything a role has no access to.
SELECT on the source tablesTo read data into Datasets. FUTURE TABLES covers tables added to the schema later.
CREATE TABLE on the target schemaTo create a target table for you, and to create the temporary table an Upsert uses.
CREATE STAGE on the target schemaTo upload a run’s rows to Snowflake before loading them into the table.
INSERT, UPDATE, DELETE, TRUNCATETo add rows (Append), update rows in place (Upsert), and replace a table’s rows (Overwrite).
OWNERSHIP on future tables in the target schemaSo that tables created in this schema later belong to the Nexadata role, which can then load them and add columns.

To keep Nexadata read-only, grant only the warehouse, database, and source schema lines. Your admin can also narrow SELECT to specific tables instead of a whole schema.

Then complete Option A for OAuth or Option B for Key-pair.

  1. Give the role to each person who will sign in from Nexadata:

    USE ROLE SECURITYADMIN;
    GRANT ROLE NEXADATA_ROLE TO USER <SNOWFLAKE_USERNAME>;
  2. In Nexadata, start a new Snowflake connection with OAuth selected (see Part 3). Below the form, Nexadata shows the line “Add this callback URL to your Snowflake OAuth integration:” followed by the URL. Copy that URL exactly. Snowflake rejects the sign-in if it does not match.

  3. Create the integration. The integration’s name is what people see on the Snowflake sign-in and consent screens (“Sign in to Snowflake to continue to …”), so choose a name a business user will recognize, such as NEXADATA_FINANCE_OAUTH.

    For a confidential client, which uses a Client ID and a Client Secret:

    USE ROLE ACCOUNTADMIN;
    CREATE SECURITY INTEGRATION NEXADATA_FINANCE_OAUTH
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
    OAUTH_REDIRECT_URI = '<CALLBACK URL FROM THE NEXADATA FORM>'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
    ALLOWED_ROLES_LIST = ('NEXADATA_ROLE');

    For a public client, which uses a Client ID only and proves its identity with PKCE instead of a secret, change one line:

    OAUTH_CLIENT_TYPE = 'PUBLIC'

    What the settings mean:

    • OAUTH_ISSUE_REFRESH_TOKENS = TRUE lets Nexadata renew its access without asking you to sign in again, so scheduled Workflows keep running.

    • OAUTH_REFRESH_TOKEN_VALIDITY = 7776000 keeps a sign-in valid for 90 days (the value is in seconds). After that, someone must re-authorize the connection in Nexadata. A shorter value, such as 86400 (one day), means signing in again every day.

    • ALLOWED_ROLES_LIST limits the integration to the Nexadata role, so a sign-in can never use a more powerful role.

  4. Copy the Client ID and Client Secret. This query returns them as two rows, one value per row, ready to copy:

    WITH secrets AS (
    SELECT PARSE_JSON(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('NEXADATA_FINANCE_OAUTH')) AS j
    )
    SELECT 'Client ID' AS field, j:OAUTH_CLIENT_ID::STRING AS value FROM secrets
    UNION ALL
    SELECT 'Client Secret', j:OAUTH_CLIENT_SECRET::STRING FROM secrets;

    For a public client, you need only the Client ID.

The commands below run in a terminal on macOS or Linux, or in Git Bash on Windows. They use openssl, which is already installed on macOS and most Linux systems.

  1. Generate an encrypted private key. You are asked for a passphrase; choose a strong one and store it in your password manager.

    Terminal window
    openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

    rsa_key.p8 is the private key. It goes into Nexadata and nowhere else.

  2. Create the matching public key from it. You are asked for the passphrase again.

    Terminal window
    openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
  3. Copy the public key without its first and last lines, as one line of text:

    Terminal window
    grep -v "PUBLIC KEY" rsa_key.pub | tr -d '\n' | pbcopy

    pbcopy puts the result on the clipboard on macOS. On Linux or Git Bash, leave off | pbcopy and copy the printed line instead.

  4. In Snowflake, create the service user with the public key, and give it the role:

    USE ROLE SECURITYADMIN;
    CREATE USER NEXADATA_SVC
    TYPE = SERVICE
    DEFAULT_ROLE = NEXADATA_ROLE
    DEFAULT_WAREHOUSE = NEXADATA_WH
    RSA_PUBLIC_KEY = '<PASTE THE PUBLIC KEY LINE HERE>'
    COMMENT = 'Nexadata connector (key-pair only)';
    GRANT ROLE NEXADATA_ROLE TO USER NEXADATA_SVC;

    TYPE = SERVICE means the user has no password and cannot sign in through a browser. The GRANT ROLE line is required: DEFAULT_ROLE names the role Nexadata uses for this connection, but it grants nothing on its own. A Key-pair connection has no Role field, so the default role must be the Nexadata role.

  5. Confirm that Snowflake holds the right key by comparing fingerprints. First, compute the fingerprint of your public key:

    Terminal window
    openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64

    A terminal showing the commands that copy the public key and compute its fingerprint, with the SHA-256 fingerprint printed below them.

    Then, in Snowflake, show the fingerprint stored on the user:

    USE WAREHOUSE NEXADATA_WH;
    DESC USER NEXADATA_SVC;
    SELECT "property", "value"
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    WHERE "property" LIKE 'RSA_PUBLIC_KEY%';

    The value of RSA_PUBLIC_KEY_FP after SHA256: must match the terminal output exactly.

    A Snowsight worksheet running DESC USER NEXADATA_SVC and RESULT_SCAN, with the RSA_PUBLIC_KEY_FP row and its SHA256 fingerprint highlighted.


  1. From the Nexadata home screen, click New Connection in the Quick Actions panel, or go to Setup → Connections and click Create Connection in the upper-right corner.

  2. In the Create New Connection form:

    • Enter a unique Name for your Snowflake connection.

    • Select Snowflake as the Connection Type.

  3. Under Authentication Type, select OAuth or Key-pair.

For OAuth, enter the details from Option A:

  • Instance URL: Your Snowflake account URL, for example https://myorg-account.snowflakecomputing.com.

  • Role: The role from Part 2, for example NEXADATA_ROLE. The form marks it optional, but fill it in: if it is blank, Snowflake uses the person’s default role, and OAuth blocks sign-ins with ACCOUNTADMIN or SECURITYADMIN. Role names are case-sensitive and must match the role in Snowflake.

  • Client ID: The Client ID of the OAuth integration.

  • Client Secret: The Client Secret of a confidential integration. Leave it blank for a public client that authenticates with PKCE only.

  • Single-use refresh tokens: Leave this unchecked unless your Snowflake admin set the integration to require single-use refresh tokens. When it is checked, each refresh token is valid for one use and Snowflake returns a new one on every refresh.

The Create New Connection form for Snowflake with OAuth selected, the Role field and its help text highlighted, and the callback URL to add to the Snowflake integration highlighted.

For Key-pair, enter the details from Option B:

  • Instance URL: Your Snowflake account URL.

  • Account Identifier: Your account identifier in uppercase, with any periods replaced by hyphens, for example MYORGANIZATION-MYACCOUNT. If you use an account locator, leave off the region.

  • Username: The service user, for example NEXADATA_SVC.

  • Private Key: Open rsa_key.p8 in a text editor and paste its entire contents, including the -----BEGIN ENCRYPTED PRIVATE KEY----- and -----END ENCRYPTED PRIVATE KEY----- lines.

  • Private Key Passphrase: The passphrase you chose in Option B. It is required only if the private key is encrypted.

The Create New Connection form for Snowflake with Key-pair selected, the Account Identifier field and its formatting rule, and the Private Key field highlighted.

For OAuth, connect to Snowflake:

  1. Click Connect to Snowflake. The button is available once the required fields are filled in.

    The OAuth connection form filled in, with NEXADATA_ROLE as the Role, the Client ID blurred, and the Connect to Snowflake button highlighted.

  2. A new browser tab opens with the Snowflake sign-in page, which names your integration. Sign in with your Snowflake username and password. This page appears even if you are already signed in to Snowsight, because Snowsight and the account sign-in use separate sessions.

    The Snowflake sign-in page reading Sign in to Snowflake to continue to NEXADATA_FINANCE_OAUTH, with Username and Password fields.

  3. Snowflake shows what Nexadata is asking for: access to your account using the Nexadata role, access to your data, and permission to run offline processes on your behalf. The last item is what lets scheduled Workflows run while you are away. Click Allow.

    The Snowflake consent page for NEXADATA_FINANCE_OAUTH listing access using role NEXADATA_ROLE, access to your data, and running offline processes, with Allow and Cancel buttons.

  4. The tab shows “SUCCESS! You may now close this window and navigate back to Nexadata.” Close the tab yourself and return to the Nexadata tab.

    A browser tab with plain text reading SUCCESS! You may now close this window and navigate back to Nexadata.

  5. The form now shows Snowflake is connected., and the Access Token and Refresh Token fields are filled in and masked. Nexadata stores both tokens encrypted and renews the access token automatically.

For Key-pair, click Verify Credentials. Nexadata signs a request with your private key and checks it with Snowflake. When it succeeds, a “Credentials successfully verified” message appears and a green shield appears next to Submit.

The Key-pair connection form with FTLPIMS-KCC24660 as the Account Identifier, NEXADATA_SVC as the Username, the private key blurred, and the Credentials successfully verified message.

If you click Submit before a successful verification, Nexadata does not save the connection and shows “Verify the Snowflake key-pair credentials before submitting this connection.”

The Key-pair connection form with a red X next to Submit and the banner Verify the Snowflake key-pair credentials before submitting this connection.

Click Submit to save the connection.

The OAuth connection form after signing in, with the Access Token and Refresh Token masked, the message Snowflake is connected, and the Verify Credentials and Submit buttons highlighted.


  • OAuth sign-in fails or Snowflake reports a redirect mismatch? The integration’s OAUTH_REDIRECT_URI must match the callback URL shown on the Nexadata form exactly. Copy it from the form again.

  • Snowflake refuses the role during sign-in? Check that the Role field matches the role name exactly, that the role is in the integration’s ALLOWED_ROLES_LIST, and that the role is granted to the person signing in. ACCOUNTADMIN and SECURITYADMIN cannot be used with OAuth.

  • Key-pair verification fails? Check the passphrase, the Account Identifier format, that the whole private key was pasted including its first and last lines, and that the fingerprints match.

For more, see Troubleshooting the Snowflake Connector.

Next: Learn how to use the Snowflake connection