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 requiredtextThe Snowflake role that will be used by Faraday to connect to the instance (Usually this is FARADAY)
  • Warehouse requiredtext
  • Database requiredtext
  • Schema requiredtext

Granting access

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

Snowflake connections require determining the "organization name" and "account name" in addition to a user and password.

This can be confusing, but here is a hint:

orgname-account_name.snowflakecomputing.com

We suggest that you create a Faraday-only schema to both send and receive data. Within this schema, Faraday would have full read and write access. Alternatively, you can give Faraday access to certain tables in a shared schema.

Example commands:

  • CREATE USER FARADAY_USER
ALTER USER FARADAY_USER SET
RSA_PUBLIC_KEY='MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAveZjTXJEJzFSUqrw/U1u
OBEzdNXWf19AzcTl5r1UOyTOjC5iHhi3BFPE0oYwCdpo1aYmkJqunzzaMCHexS4T
TxStCfxpFwV2gjB2SvT+eIE6e3HM8uizuWv9ENYW8r5pGUkwsOcg9jQKm017IfUG
iSQeZkllX1ZZRxPb/pjim4uyWU49WNeF/hr0kIpd6I/r2Pph7UmOxbuTGpb9wkko
bsoevjGVjzlhQOFZc6roYMBVLXkGiJTVMHX9eKa/QRf1KQMmM5VwWaRGO7VUx6+r
pZHQvNXDfMaY3gErx5UsCq3xxhqTV07T4IuCP1C7vPIhn5/xJBxR/yv6x9hS/Czn
07IPhCL3WRFa2OMCeHwnw90J10aNqAJmWrMccbC6VIInK6Vv/rbpOYfhsI4Wu9PJ
Ia6cm4g1kv2Mz7m7VhHpZAVIPKLxg0E7bsNQjx+XxfSV3dyq2kblKs+Ki5J2JUFw
hhMKTGiEDO2rxbnqKAiOEUlFpi9RbS+lWep+rREvj0XTE3f4rV+i3JdgeyACJ+Ua
uqCaVbmFPWtKca7D1bSwBnvf6TURSK2FLhHFC80NRMNXi3fkakL8y7XrmVBttzg3
wdVq+g/+DtYsmtb/Z+pcm1joGsLt3lyKUc6/w/1fbZi+svDhW2E7JvijuBmNgSSO
Q3Zs5aVfZyX2Hn/b35UmZtUCAwEAAQ=='
  • 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

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. 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.
  3. At this time we only support the following Snowflake datatypes in tables.
  • NUMBER
  • DECIMAL
  • NUMERIC
  • INT
  • INTEGER
  • BIGINT
  • SMALLINT
  • FLOAT
  • FLOAT4
  • FLOAT8
  • DOUBLE
  • DOUBLE PRECISION
  • REAL
  • TEXT
  • VARCHAR
  • CHAR
  • CHARACTER
  • STRING
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMP_LTZ
  • TIMESTAMP_TZ
  • TIMESTAMP_NTZ
  • BOOLEAN
  • 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.
  2. Please provide an
    • Organization name and Account name together OR
    • a Legacy account locator by itself.

Connecting

API via cURL
Dashboard

Use a POST /connections request:

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

Your new connection is now ready to use.