Snowflake

Create a connection between Faraday and Snowflake so that your data is always up to date to make predictions, and your predictions can seamlessly sync back to your warehouse.

In this tutorial, we'll show you how to:

  • Connect your Snowflake account to Faraday using a connection.

Let's dive in.

  1. You'll need a Faraday account — signup is free!

Prerequisites

You'll need the following details to create your connection to Snowflake:

  • Organization name optionaltextSnowflake organization name. If provided, also provide account name. ORGNAME-ACCOUNTNAME.snowflakecomputing.com. Preferred over legacy account locator. https://docs.snowflake.com/en/user-guide/admin-account-identifier.html#format-1-preferred-account-name-in-your-organization.
  • Account name optionaltextSnowflake account name. If provided, also provide organization name. ORGNAME-ACCOUNTNAME.snowflakecomputing.com. Preferred over legacy account locator. https://docs.snowflake.com/en/user-guide/admin-account-identifier.html#format-1-preferred-account-name-in-your-organization.
  • Legacy account locator optionaltextNOT needed if organization name is provided. If your Snowflake account existed before the Organizations feature was enabled, the Format 2 (Legacy): Account Locator in a Region is used as the account name.
  • User requiredtextSnowflake user.
  • Role optionaltextThe Snowflake role that will be used by Faraday to connect to the instance (Usually this is FARADAY).
  • Warehouse requiredtext
  • Database requiredtext
  • Schema optionaltextSnowflake schema name. If not provided, it may be provided at the dataset level, or omitted entirely.
  • RSA public key optionaltextRSA public key that should be assigned to the Snowflake user. Unique per Faraday Snowflake connection. Set by the Faraday system. Use the rotate_credentials endpoint to regenerate.

Granting access

First, you'll need Faraday access to your Snowflake account.

There a few options for authorizing Faraday in Snowflake:

  • Create a Faraday-only schema that Faraday has full read and write access to
  • Give Faraday access to specific tables inside of existing schemas
  • A hybrid of these, for example giving Faraday access to existing tables but requiring it write to a new Faraday-only schema.

Setup process

Since Snowflake requires an RSA keypair, you should first create the Faraday connection, which will generate a keypair that is unique to your connection. Then, you can provision the Snowflake user with the keypair configured. The Faraday Connection will error until you have set up access, but then you can use Force Update (in the 3-dots menu) to refresh.

  1. Create your Faraday connection using the API or UI. Faraday will automatically generate a unique RSA keypair for this connection. Please provide an
  • Organization name and Account name OR
  • a Legacy account locator by itself

You can find these identifiers in the Snowflake menu, or you can infer it from $organization_name-$account_name.snowflakecomputing.com or https://app.snowflake.com/$organization_name/$account_name

  1. Get the public key from your Faraday connection. You can find this in the connection details after creation.

  2. Create your Snowflake user and role:

  • CREATE USER FARADAY_USER
  • CREATE ROLE FARADAY_ROLE
  • GRANT ROLE FARADAY_ROLE TO USER FARADAY_USER
  • GRANT USAGE ON WAREHOUSE <WAREHOUSE_NAME> TO FARADAY_ROLE
  • GRANT USAGE ON DATABASE <DATABASE_NAME> TO ROLE FARADAY_ROLE
  • GRANT USAGE ON SCHEMA <DATABASE_NAME>.FARADAY_SCHEMA TO ROLE FARADAY_ROLE
  • GRANT ALL PRIVILEGES ON SCHEMA <DATABASE_NAME>.FARADAY_SCHEMA TO ROLE FARADAY_ROLE
  • GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <DATABASE_NAME>.FARADAY_SCHEMA TO ROLE FARADAY_ROLE
  • GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA <DATABASE_NAME>.FARADAY_SCHEMA TO ROLE FARADAY_ROLE
  1. Configure the public key in Snowflake:
   ALTER USER FARADAY_USER SET RSA_PUBLIC_KEY='<public_key_from_faraday>'
  1. Test the connection by using the "Force update" button in Faraday. The initial connection may fail until the public key is properly configured in Snowflake.

Faraday suggests that you use an unguessable string somewhere in the path to your data. This avoids what is called the Confused deputy problem

For example, let's say you were using S3. Instead of naming an S3 bucket s3://faraday-acme/, name it s3://faraday-acme-pwiiprz162ez. This guarantees that malicious actors cannot guess the name and request that Faraday import data from it into their account. The same logic applies to any path that is used to locate data.

Additional notes

  1. Due to the fact that Faraday utilizes RSA keypair authentication, do not set a password for the USER in Snowflake.

  2. Key management: Each Faraday connection gets its own unique RSA keypair. The private key is stored securely in Faraday's secret vault and is never exposed to users. Only the public key needs to be configured in Snowflake.

  3. Key rolling: You can roll the RSA keypair by setting the rsa_public_key field to null in the connection update API. This will generate a new keypair, and you'll need to update the public key in Snowflake accordingly.

  4. You may need to set a NETWORKPOLICY and allowlist Faraday IPs. The newly created NETWORKPOLICY needs to be assigned to the USER created for access.

  5. At this time we only support the following Snowflake datatypes in tables:

  • ARRAY (converted to string)
  • BIGINT
  • BOOLEAN
  • CHAR
  • CHARACTER
  • DATE
  • DATETIME
  • DECIMAL
  • DOUBLE
  • DOUBLE PRECISION
  • FLOAT
  • FLOAT4
  • FLOAT8
  • INT
  • INTEGER
  • NUMBER
  • NUMERIC
  • REAL
  • SMALLINT
  • STRING
  • TEXT
  • TIMESTAMP
  • TIMESTAMP_LTZ
  • TIMESTAMP_NTZ
  • TIMESTAMP_TZ
  • VARCHAR
  • VARIANT
  1. Keep in mind that if your Snowflake identifiers have case-sensitivity you will have the opportunity to configure that at the dataset level.

Connecting

API via cURL
Dashboard

Use a POST /connections request:

curl https://api.faraday.ai/connections --json '{
  "name": "Snowflake",
  "options": {
    "type": "snowflake",
    "user": "...",
    "warehouse": "...",
    "database": "..."
  }
}'
  1. Wait briefly while Faraday establishes your connection. It shouldn't take long.

Your new connection is now ready to use.