Skip to main content

Quickstart for Ascend with Snowflake

Introduction​

In this quickstart guide, you'll learn how to use Ascend with Snowflake. You'll be guided through these processes:

  • Connecting Ascend to Snowflake
    • Creating required Snowflake resources
    • Setting up your Instance Vault
    • Setting up your Instance Store
    • Setting up your Environment Vault(s)
  • Creating an Ascend Project using Snowflake as the Data Plane
    • Setting up a Repository
    • Creating your developer Workspace
    • Setting up your Profile and Connection to use Snowflake
    • Building & running your first Flow
warning

This quickstart grants elevated permissions for the sake of simplicity. We recommend isolating your Ascend Instance and each Environment's resources in separate Snowflake resources based on your security requirements.

Prerequisites​

To complete this quickstart, you'll need:

  • An Ascend Instance
  • A Snowflake account
    • The ability to create Snowflake resources (may require elevated permissions for some steps)
  • A Snowflake user with both SYSADMIN and SECURITYADMIN roles to run the setup scripts
Snowflake Security Requirements

Snowflake has implemented enhanced security measures that affect user authentication:

  • Human users must use Multi-Factor Authentication (MFA) when authenticating with passwords
  • Service users (non-human users like Ascend) must use key pair authentication instead of passwords

For more details, see Snowflake's MFA rollout documentation.

Set up Snowflake as an Ascend Instance Store​

tip

You can leverage existing Snowflake resources, but if you're starting with a new Snowflake account, this section will guide you through the setup process step by step.

Follow along to use the provided scripts, which will automatically create the necessary resources for you.

You need to create a set of resources for the Ascend Instance Store. In Snowflake, you'll create the following:

  • a User
  • a Database
  • a Schema
  • a Warehouse
  • a Role
tip

If you don't manage the creation of new users and roles, reach out to your database administrator to create the Instance Store User and Role for you.

Start in the Snowflake UI and sign into the account you will use with Ascend. Click Create toward the top left of the screen and select SQL Worksheet. You'll run SQL commands to create the required Snowflake resources for Ascend.

Snowflake Create SQL Worksheet

Generate RSA Key Pair for Instance Store​

Similar to the Data Plane setup, the Instance Store also requires key pair authentication for service users. Generate a separate RSA key pair for the Instance Store:

# Generate private key for Instance Store
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out instance_rsa_key.p8 -nocrypt

# Generate public key for Instance Store
openssl rsa -in instance_rsa_key.p8 -pubout -out instance_rsa_key.pub

Now, navigate to the Ascend UI and click into Settings toward the top right of the screen. Navigate to the Instance settings. You'll need to store the private key for the Snowflake user in the Instance Vault to allow Ascend to access Snowflake resources. Click Manage Secrets under the Instance Vault section and add a secret with the RSA private key.

Snowflake Instance Vault

For this quickstart, we'll use the secret name snowflake-instance-private-key.

important

Store the entire contents of the instance_rsa_key.p8 file (including the BEGIN and END lines). You will need the corresponding public key to set up the Snowflake User in the next step.

Snowflake Instance Vault Secret

Head back to the Instance settings in the Ascend UI and scroll toward the bottom to Edit Instance Store. Select Snowflake and fill out the details. Then, click Get Setup Script. This will populate a SQL script using the values you entered.

Snowflake Instance Store setup

Paste the script into the Snowflake SQL Worksheet, and make sure to update the user creation to use key pair authentication instead of password authentication.

tip

If you don't manage the creation of new users and roles, reach out to your database administrator and share this script with them to run.

Ensure that your administrator runs GRANT ALL on the Ascend-managed Instance Store database, ASCEND_INSTANCE.

warning

The generated script may still reference password authentication. You'll need to modify the CREATE USER statement to use TYPE=SERVICE and RSA_PUBLIC_KEY instead of PASSWORD. Replace the password-based user creation with:

CREATE USER identifier($INSTANCE_USER)
  TYPE=SERVICE
  DEFAULT_ROLE=$INSTANCE_ROLE
  RSA_PUBLIC_KEY='<PASTE_YOUR_INSTANCE_PUBLIC_KEY_HERE>';

Copy the contents of instance_rsa_key.pub (without the BEGIN/END lines) as the RSA_PUBLIC_KEY value.

Snowflake Instance script

You can run the entire script all at once or execute each line individually to better understand the purpose of each command.

Now that you have the necessary resources, return to the Ascend UI and click Check and Update Instance Store.

Snowflake check and update instance

Congratulations! You successfully set up Snowflake as an Ascend Instance Store.

Set Up Snowflake as an Ascend Data Plane​

To use Snowflake as an Ascend Data Plane, you'll need to create specific resources. Ensure that each Environment has its own dedicated Data Plane setup by creating the following for each one:

  • a Service User (using key pair authentication)
  • a Database
  • a Schema
  • a Warehouse
  • a Role

Note that these resources should be separate from the Instance Store resources. You should also create isolated resources for each Environment you use.

Key Pair Authentication Required

Due to Snowflake's security enhancements, service users (like Ascend) must use key pair authentication instead of passwords. This section will guide you through setting up RSA key pair authentication.

Step 1: Generate RSA Key Pair​

First, generate an RSA key pair for authentication. Run these commands in your terminal:

# Generate private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

# Generate public key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Step 2: Create Snowflake Resources​

USE ROLE SYSADMIN;
SET ENV_DEV_DB='ASCEND_ENV_DEV';
SET WAREHOUSE='ASCEND_ENV_DEV';
CREATE DATABASE identifier($ENV_DEV_DB);
CREATE WAREHOUSE identifier($WAREHOUSE) WAREHOUSE_SIZE='XSMALL' WAREHOUSE_TYPE='STANDARD' AUTO_SUSPEND=60 AUTO_RESUME=TRUE INITIALLY_SUSPENDED=TRUE;

USE ROLE SECURITYADMIN;
SET ENV_DEV_USER='ASCEND_ENV_DEV';
SET ENV_DEV_ROLE='ASCEND_ENV_DEV';
CREATE ROLE identifier($ENV_DEV_ROLE);

-- Create service user with key pair authentication
CREATE USER identifier($ENV_DEV_USER)
  TYPE=SERVICE
  DEFAULT_ROLE=$ENV_DEV_ROLE
  RSA_PUBLIC_KEY='<PASTE_YOUR_PUBLIC_KEY_HERE>';

/* Copy the contents of rsa_key.pub (without the BEGIN/END lines)
   and paste as the RSA_PUBLIC_KEY value above */

GRANT ROLE identifier($ENV_DEV_ROLE) TO USER identifier($ENV_DEV_USER);
GRANT ALL ON WAREHOUSE identifier($WAREHOUSE) TO ROLE identifier($ENV_DEV_ROLE);
GRANT ALL ON DATABASE identifier($ENV_DEV_DB) TO ROLE identifier($ENV_DEV_ROLE);
tip

If you don't manage the creation of new users and roles, reach out to your database administrator and share this script with them to run.

Ensure that your administrator runs GRANT ALL on the Ascend-managed Environment database, ASCEND_ENV_DEV, along with any other Environment databases you create (such as Staging or Production).

Run this SQL script in a Snowflake SQL worksheet after updating the RSA_PUBLIC_KEY with your generated public key.

Step 3: Store Private Key in Environment Vault​

Now, you'll need to add the private key as a secret in your Environment Vault. In the Ascend UI, navigate to the Environments settings and edit the Dev environment.

Snowflake environment edit

Click Manage Secrets and add a secret containing the RSA private key.

Snowflake environment manage secrets

Create a secret in the Environment Vault (Dev) with the entire contents of the rsa_key.p8 file (including the BEGIN and END lines). For this quickstart, we recommend using the secret name snowflake-private-key.

important

Store the entire private key file contents, including:

-----BEGIN PRIVATE KEY-----
[key content]
-----END PRIVATE KEY-----

Snowflake environment password

Congratulations! You're ready to use Snowflake as an Ascend Data Plane.

Set up a Git repository​

Set up Otto's Expeditions on GitHub.

Create your developer Workspace​

Now, let's create a Workspace where we can develop.

  1. In the Ascend UI, navigate to the Instance settings by clicking on the button in the top right with your Instance name and your user icon.
  2. Click on the Workspaces settings tab.
  3. Click the Add Workspace button.
  4. Set Title to <your-name> (e.g. Sean)
  5. Select Environment as Development
  6. Set Project to the Project you created above (Otto's Expeditions)
  7. Set Git Branch to <your-name>/dev (you can type the branch name and click "New Branch:my-first-branch" to create it if it doesn't already exist)
  8. Set Profile as workspace_template
  9. Click Save.

Build and run your first Flow​

  1. In the Ascend UI, navigate to the Workspace you created above and open the Files view toward the top left of the screen. Copy the profiles/workspace_template.yaml to one with your name, update your Workspace to use this new Profile, and update the following Parameters:

    • snowflake:
      • account: <your-snowflake-account> (the Snowflake account for the Data Plane)
      • any other values if you used different names from the quickstart above

  2. If you used secrets names in your Environment Vault other than snowflake-private-key, update the connections/data_plane_snowflake.yaml file with the correct secret name. Note that with key pair authentication, you'll need to configure the connection to use the private key instead of a password.

  3. Finally, go into the quickstart Flow and hit Run Flow!

🎉 Congratulations! You've successfully set up your first Ascend Flow!

Next steps​

Now that you have your first Flow running, consider:

Last updated on