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.
- 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
- 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.
- 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
Get the public key from your Faraday connection. You can find this in the connection details after creation.
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
- Configure the public key in Snowflake:
ALTER USER FARADAY_USER SET RSA_PUBLIC_KEY='<public_key_from_faraday>'
- 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
Due to the fact that Faraday utilizes RSA keypair authentication, do not set a password for the USER in Snowflake.
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.
Key rolling: You can roll the RSA keypair by setting the
rsa_public_key
field tonull
in the connection update API. This will generate a new keypair, and you'll need to update the public key in Snowflake accordingly.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.
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
- Keep in mind that if your Snowflake identifiers have case-sensitivity you will have the opportunity to configure that at the dataset level.
Connecting
Use a POST /connections
request:
curl https://api.faraday.ai/connections --json '{ "name": "Snowflake", "options": { "type": "snowflake", "user": "...", "role": "...", "warehouse": "...", "database": "...", "schema": "..." } }'
- Wait briefly while Faraday establishes your connection. It shouldn't take long.
Your new connection is now ready to use.