The Snowflake Data Cloud, a leading cloud-based data platform, has revolutionized how companies store, manage, and analyze their data. Yet, the journey from raw data to actionable insights often involves multiple complex steps, from data ingestion to transformation. This is where the Coalesce steps in, bridging the gap and simplifying the data transformation process.
In this blog, we will take you step by step through connecting Snowflake to Coalesce, giving you all the connection options and some tips and tricks along the way. Once this guide is completed, your Coalesce workspace will be fully connected to Snowflake and ready for users to transform your data in Snowflake.
What is Coalesce?
Coalesce is a data transformation platform specifically designed for Snowflake. It is a hybrid development environment that combines code-first and GUI capabilities, allowing users to build complex transformations visually or write code directly.Â
Coalesce is also column-aware, meaning it can understand the structure and relationships of your data at a granular level. This allows it to automate many repetitive tasks involved in data transformation, such as generating Snowflake-native code and managing column-level metadata.
Prerequisites
Here are the prerequisites required to connect Snowflake to Coalesce:
Snowflake Account
Snowflake User
Snowflake Role with usage on a virtual warehouse
Login Credentials
Coalesce Account with a Created Workspace
How to Connect Snowflake to Coalesce
Step 1: Retrieve Snowflake Account Identifier
You’ll need your Snowflake account identifier to connect your Snowflake account to Coalesce. Snowflake has made a simple way to retrieve this information:
Log into your Snowflake account and navigate to Snowsight
In the bottom left, click the account selector
Hover over the account to view additional details and select the Copy icon to copy the account identifier in the format
<orgname>.<account_name>to your clipboard.
Save this information, as you’ll need it in an upcoming step.
Step 2: Create a New Storage Location
In your Coalesce workspace, navigate to Build Settings (the gear located in the bottom left corner)
In the Storage Locations tab, click New Storage Location and give it a name:
This new storage location will be used for the Snowflake account
Step 3: Add Account Identifier to Workspace
Navigate to the Workspace editor by clicking the pencil next to the workspace name.
Under the Settings tab, paste the Snowflake Account Identifier from the first step into the Snowflake Account field and hit Save
Step 4: Add Login Credentials
We’re ready to add login credentials but must choose how to log in. Coalesce provides 3 ways to log into Snowflake: simple username and password, key pair, and OAuth using Snowflake as the Single Sign On (SSO).Â
Simple Username and Password
Username and password are the simplest connection type but also the least secure. There are two authentication types with username and password: Cloud and Browser. Cloud will save your password to Coalesce securely, whereas Browser will save your password as plain text in your browser. Browser type is not recommended and will issue a warning if you try to use it.
To use username and password authentication, choose Username and Password in the Authentication Type dropdown in the User Credentials tab and enter your credentials. Optionally, you can add the Role and Virtual Warehouse you’d like to use on Snowflake.
Click the Test Connection button to ensure a successful connection, and press Save. Your Coalesce workspace is now connected to Snowflake.
Keypair
Coalesce supports Snowflake’s key pair authentication for connecting to Snowflake instances. Both encrypted and unencrypted private keys are supported. Encrypted keys have a corresponding passphrase required, while unencrypted keys can be used directly.Â
While keys can be encrypted with an empty passphrase by Snowflake, this is not supported in Coalesce and will result in an error.
If you haven’t already, go through Snowflake’s key pair authentication instructions, Steps 1 to 5, to generate your keys and assign the public key to your Snowflake user.
In the Workspace editor, under the User Credentials tab, select Key Pair in the Authentication Type dropdown. Enter your username and paste the private key into the Private Key field. Enter your key into the Private Key Passphrase field if it was created with a required passphrase. Optionally, the desired Snowflake Role and Virtual Warehouse can also be entered.
Click the Test Connection button to ensure a successful connection, and press Save. Your Coalesce workspace is now connected to Snowflake.
OAuth
Coalesce supports OAuth authentication with Snowflake. When Snowflake OAuth is enabled, users can authorize their Development credentials using Single Sign-On (SSO) via Snowflake rather than submitting a username and password (or key pair) to Coalesce directly.
Create Security Integration in Snowflake:
To use OAuth in Coalesce, a security integration must be created in Snowflake. To do so, the
ACCOUNTADMINor a role withCREATE INTEGRATIONprivileges will need to create a security integration with the following parameters:
CREATE OR REPLACE SECURITY INTEGRATION "COALESCE_OAUTH"
ENABLED = TRUE
TYPE = OAUTH
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://<COALESCE_APP_DOMAIN>/oauthredirect'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
Replace <COALESCE_APP_DOMAIN> with your Coalesce app domain.
For example: app.coalescesoftware.io
Back in Coalesce in the Workspace editor, navigate to the OAuth Settings tab and switch the Enable OAuth button to on.
In the User Credentials tab, select Snowflake OAuth as the Authentication Type, enter the desired Role and Virtual Warehouse, and hit Save.
Note: Unlike other authentication types, selecting a role is required for OAuth.
Obtain the Client ID and Client Secret generated by creating a Snowflake security integration. To retrieve these values, execute the following SQL query in Snowflake:
WITH INTEGRATION_SECRETS AS (
SELECT parse_json(system$show_oauth_client_secrets('COALESCE_OAUTH')) AS SECRETS
)
SELECT
SECRETS:"OAUTH_CLIENT_ID"::STRING AS CLIENT_ID,
SECRETS:"OAUTH_CLIENT_SECRET"::STRING AS CLIENT_SECRET
FROM
INTEGRATION_SECRETS;
In the Coalesce Workspace editor, click Edit next to the Edit OAuth Credentials, populate Client ID and Client Secret from the previous step, and press Save.
Once OAuth settings are saved, hit Authenticate in the User Credentials tab. This will enable a popup for you to log in to Snowflake. Once your login is successful, your Coalesce account will be connected to Snowflake.
3 Tips
If you have connectivity issues between Coalesce and Snowflake, the Coalesce IP Addresses may need to be white-listed in your Snowflake network policy.Â
If your organization limits traffic to the public internet, outbound HTTPS traffic may need to be allowed to specific Coalesce domains.
The Snowflake role must be set when utilizing OAuth before logging in to Snowflake from Coalesce. If a change in role is desired, the user must log off, change the role in the Coalesce Workspace editor, and then log back in.
Closing
Following the step-by-step instructions outlined in this blog, you’ve successfully connected Snowflake to Coalesce, creating a bridge that streamlines the data transformation process. Your Coalesce workspace is now fully integrated with Snowflake, and your team is equipped with the tools they need to efficiently and effectively transform data, unlocking its full potential.
This integration simplifies your data workflow and empowers your organization to make more informed decisions. Whether you’re a data engineer, an analyst, or simply interested in optimizing data management, this connection opens up a world of possibilities. Your data transformation journey has become more accessible, efficient, and powerful.
Looking for help with your data engineering project?
From data engineering projects to cloud migrations, phData helps organizations make more data-driven decisions.
