Snowflake

[Snowflake][snowflake] is a popular cloud-based data platform.

Snowflake

Snowflake is a popular cloud-based data platform.

Prerequisites

In order to connect Cube to Snowflake, you need to grant certain permissions to the Snowflake role used by Cube. Cube requires the role to have USAGE on databases and schemas and SELECT on tables. An example configuration:

GRANT USAGE ON DATABASE ABC TO ROLE XYZ;
GRANT USAGE ON ALL SCHEMAS IN DATABASE ABC TO ROLE XYZ;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ABC TO ROLE XYZ;
GRANT SELECT ON ALL TABLES IN DATABASE ABC TO ROLE XYZ;
GRANT SELECT ON FUTURE TABLES IN DATABASE ABC TO ROLE XYZ;

• Account/Server URL for Snowflake.
• User name and password or an RSA private key for the Snowflake account.
• Optionally, the warehouse name, the user role, and the database name.

Setup

If you’re having Network error and Snowflake can’t be reached please make sure you tried format 2 for an account id.

Manual

Add the following to a .env file in your Cube project:

CUBEJS_DB_TYPE=snowflake
CUBEJS_DB_SNOWFLAKE_ACCOUNT=XXXXXXXXX.us-east-1
CUBEJS_DB_SNOWFLAKE_WAREHOUSE=MY_SNOWFLAKE_WAREHOUSE
CUBEJS_DB_NAME=my_snowflake_database
CUBEJS_DB_USER=snowflake_user
CUBEJS_DB_PASS=**********

Cube Cloud

In some cases you’ll need to allow connections from your Cube Cloud deployment IP address to your database. You can copy the IP address from either the Database Setup step in deployment creation, or from Settings → Configuration in your deployment.

The following fields are required when creating a Snowflake connection:

Cube Cloud also supports connecting to data sources within private VPCs if dedicated infrastructure is used. Check out the VPC connectivity guide for details.

Environment Variables

Environment Variable	Description	Possible Values	Required
CUBEJS_DB_SNOWFLAKE_ACCOUNT	The Snowflake account identifier to use when connecting to the database	A valid Snowflake account ID	✅
CUBEJS_DB_SNOWFLAKE_REGION	The Snowflake region to use when connecting to the database	A valid Snowflake region	❌
CUBEJS_DB_SNOWFLAKE_WAREHOUSE	The Snowflake warehouse to use when connecting to the database	A valid Snowflake warehouse in the account	✅
CUBEJS_DB_SNOWFLAKE_ROLE	The Snowflake role to use when connecting to the database	A valid Snowflake role in the account	❌
CUBEJS_DB_SNOWFLAKE_CLIENT_SESSION_KEEP_ALIVE	If true, keep the Snowflake connection alive indefinitely	true, false	❌
CUBEJS_DB_NAME	The name of the database to connect to	A valid database name	✅
CUBEJS_DB_USER	The username used to connect to the database	A valid database username	✅
CUBEJS_DB_PASS	The password used to connect to the database	A valid database password	✅
CUBEJS_DB_SNOWFLAKE_AUTHENTICATOR	The type of authenticator to use with Snowflake. Use SNOWFLAKE with username/password, or SNOWFLAKE_JWT with key pairs. Defaults to SNOWFLAKE	SNOWFLAKE, SNOWFLAKE_JWT, OAUTH	❌
CUBEJS_DB_SNOWFLAKE_PRIVATE_KEY	The content of the private RSA key	Content of the private RSA key (encrypted or not)	❌
CUBEJS_DB_SNOWFLAKE_PRIVATE_KEY_PATH	The path to the private RSA key	A valid path to the private RSA key	❌
CUBEJS_DB_SNOWFLAKE_PRIVATE_KEY_PASS	The password for the private RSA key. Only required for encrypted keys	A valid password for the encrypted private RSA key	❌
CUBEJS_DB_SNOWFLAKE_OAUTH_TOKEN	The OAuth token	A valid OAuth token (string)	❌
CUBEJS_DB_SNOWFLAKE_OAUTH_TOKEN_PATH	The path to the valid oauth toket file	A valid path for the oauth token file	❌
CUBEJS_DB_SNOWFLAKE_HOST	Host address to which the driver should connect	A valid hostname	❌
CUBEJS_DB_SNOWFLAKE_QUOTED_IDENTIFIERS_IGNORE_CASE	Whether or not quoted identifiers should be case insensitive. Default is false	true, false	❌
CUBEJS_DB_MAX_POOL	The maximum number of concurrent database connections to pool. Default is 20	A valid number	❌
CUBEJS_CONCURRENCY	The number of concurrent queries to the data source	A valid number	❌

Pre-Aggregation Feature Support

count_distinct_approx

Measures of type count_distinct_approx can be used in pre-aggregations when using Snowflake as a source database. To learn more about Snowflake’s support for approximate aggregate functions, click here.

Pre-Aggregation Build Strategies

To learn more about pre-aggregation build strategies, head here.

Feature	Works with read-only mode?	Is default?
Batching	❌	✅
Export Bucket	❌	❌

By default, Snowflake uses batching to build pre-aggregations.

Batching

No extra configuration is required to configure batching for Snowflake.

Export Bucket

Snowflake supports using both AWS S3 and Google Cloud Storage for export bucket functionality.

AWS S3

Ensure proper IAM privileges are configured for S3 bucket reads and writes, using either storage integration or user credentials for Snowflake and either IAM roles/IRSA or user credentials for Cube Store, with mixed configurations supported.

Using IAM user credentials for both:

CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_BUCKET_AWS_KEY=<AWS_KEY>
CUBEJS_DB_EXPORT_BUCKET_AWS_SECRET=<AWS_SECRET>
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>

Using a Storage Integration to write to export buckets and user credentials to read from Cube Store:

CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_INTEGRATION=aws_int
CUBEJS_DB_EXPORT_BUCKET_AWS_KEY=<AWS_KEY>
CUBEJS_DB_EXPORT_BUCKET_AWS_SECRET=<AWS_SECRET>
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>

Using a Storage Integration to write to export bucket and IAM role/IRSA to read from Cube Store:**

CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_INTEGRATION=aws_int
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>

Google Cloud Storage

When using an export bucket, remember to assign the Storage Object Admin role to your Google Cloud credentials (CUBEJS_DB_EXPORT_GCS_CREDENTIALS).

Before configuring Cube, an integration must be created and configured in Snowflake. Take note of the integration name (gcs_int from the example link) as you’ll need it to configure Cube.

Once the Snowflake integration is set up, configure Cube using the following:

CUBEJS_DB_EXPORT_BUCKET=snowflake-export-bucket
CUBEJS_DB_EXPORT_BUCKET_TYPE=gcs
CUBEJS_DB_EXPORT_GCS_CREDENTIALS=<BASE64_ENCODED_SERVICE_CREDENTIALS_JSON>
CUBEJS_DB_EXPORT_INTEGRATION=gcs_int

Azure

To use Azure Blob Storage as an export bucket, follow the guide on using a Snowflake storage integration (Option 1). Take note of the integration name (azure_int from the example link) as you’ll need it to configure Cube.

Retrieve the storage account access key from your Azure account.

Once the Snowflake integration is set up, configure Cube using the following:

CUBEJS_DB_EXPORT_BUCKET_TYPE=azure
CUBEJS_DB_EXPORT_BUCKET=wasbs://[e-mail]
CUBEJS_DB_EXPORT_BUCKET_AZURE_KEY=<AZURE_STORAGE_ACCOUNT_ACCESS_KEY>
CUBEJS_DB_EXPORT_INTEGRATION=azure_int

SSL

Cube does not require any additional configuration to enable SSL as Snowflake connections are made over HTTPS.