Docs
Database Connections
Snowflake

Snowflake

If you have a lot of data and want to keep throwing it into a data warehouse, Snowflake (opens in a new tab) is a tried and tested solution. They continue to push the boundaries of SQL and analytics features and their data sharing features between organizations are particularly strong for larger enterprises.

About 50% of Hashboard's customers use Snowflake. If you're using AWS, Snowflake could be a good solution for you. If you don't have much data, using Postgres, Redshift, or even DuckDB could be good options too. Compared to Redshift, Snowflake has a more modern architecture with decoupled storage and compute that can scale to larger datasets more easily. This architecture is also much easier to manage with more flexibility over how much compute you use and fewer required decisions over how you lay out data. If Google Cloud is an option, you may also want to consider BigQuery as an alternative to Snowflake.

How to get set up

  1. Sign up for a 30 day trial (opens in a new tab) on Snowflake if you don't have an account.
  2. Create a Snowflake user that Hashboard can use.
  3. Set up a database connection in Hashboard.

Create a Snowflake user

Authentication types

Snowflake offers two authentication methods that Hashboard supports:

  • Password authentication: The traditional username/password login.
  • Key-pair authentication: A more secure method using public/private key pairs, in which you associate a public key with your Snowflake user.

To use key-pair authentication, you will need to generate a public/private key pair yourself. Additionally you can optionally encrypt your private key with a passphrase for more security. Read more about generating key pairs in Snowflake's documentation (opens in a new tab).

Create a user

You can run the following script. Make sure you fill out your database name, warehouse and a password or public key in the script:

use role ACCOUNTADMIN;
 
set database_name = '--YOUR DATABASE NAME--';
set warehouse_name = '--YOUR WAREHOUSE NAME--';
set hashboard_user = 'HASHBOARD_USER';
set hashboard_role = 'HASHBOARD_ROLE';
 
create user if not exists identifier($hashboard_user);
 
-- if using password authentication:
ALTER USER identifier($hashboard_user) SET PASSWORD='--YOUR PASSWORD--';
 
-- if using key-pair authentication:
ALTER USER identifier($hashboard_user) SET RSA_PUBLIC_KEY='--YOUR PUBLIC KEY--';
 
create role if not exists identifier($hashboard_role);
grant role identifier($hashboard_role) to user identifier($hashboard_user);
 
-- grant Hashboard role access to warehouse
grant USAGE on warehouse identifier($warehouse_name)
to role identifier($hashboard_role);
 
-- grant Hashboard access to database
grant MONITOR, USAGE  on database identifier($database_name) to role identifier($hashboard_role);
grant USAGE on all schemas in database identifier($database_name) to role identifier($hashboard_role);
grant USAGE on future schemas in database identifier($database_name) to role identifier($hashboard_role);
grant SELECT on all tables in database identifier($database_name) to role identifier($hashboard_role);
grant SELECT on future tables in database identifier($database_name) to role identifier($hashboard_role);
grant SELECT on all views in database identifier($database_name) to role identifier($hashboard_role);
grant SELECT on future views in database identifier($database_name) to role identifier($hashboard_role);

Create a Snowflake database connection in Hashboard

  1. In Hashboard, go to the Data sources (opens in a new tab) page.
  2. Click + Add connection, select Snowflake and fill out the fields below.

Settings

  • Authentication type: either Password or Key Pair depending on whether your user is associated with a password or a public key. Learn more about key pair authentication (opens in a new tab).
  • Account: Your snowflake account identifier (opens in a new tab) is provided by Snowflake and included in the start of the URL you use to login to Snowflake: <account_identifier>.snowflakecomputing.com.
  • User: The Snowflake user. Best practice is to have a service user specifically dedicated to Hashboard.
  • Database: The Snowflake database to use. Databases correspond with logical groupings of tables and views in Snowflake.
  • Schema: optional If you provide a schema, we will limit listed tables in the model builder to this schema. If you need to limit permissions to a specific schema, you should do this with Snowflake permissions.
  • Warehouse: The warehouse to use in Snowflake. Warehouses correspond with compute resources.
  • Role: You must specify a role to be assumed when connecting from Hashboard. See Snowflake's roles (opens in a new tab) documentation.

If password authentication is selected:

  • Password: The password used to login to Snowflake.

If key pair authentication is selected:

  • Private key: The (encrypted or unencrypted) private key used to login to Snowflake, provided as a .p8 or .pem file.
  • Encryption passphrase: optional If an encrypted private key was provided, you will need to provide the passphrase used to decrypt it.
PostgreSQLRole-Based Database Credentials