Faraday’s integration with Snowflake as a source and destination for your data allows a sturdy and flexible data pipeline for connecting to Faraday. The recipe for success provisions a custom WAREHOUSE and ROLE for Faraday connection, and only takes several GRANT statements.

Prior to granting access

During this process, you will interact with the following objects:

  • Snowflake account name, aws region optional

  • Snowflake USER, utilizing a keypair for authentication

  • Snowflake WAREHOUSE

  • Snowflake SCHEMA, could be "PUBLIC"

  • Snowflake DATABASE

  • Snowflake TABLE

  • Incremental column, optional

Let us know you'd like to set up Snowflake (AWS) as a source and/or destination.

Please review our IP allowlist article for an up to date list of Faraday's current IPs: https://knowledgebase.faraday.io/en/articles/4315835-ip-allowlist

We recommend

  1. Create or designate a FARADAY WAREHOUSE

  2. Create a separate SCHEMA for Faraday access within DATABASE (for ROLE permissions inside of a context)

  3. Create a FARADAY USER

  4. Create a custom FARADAY ROLE that mimics SYSADMIN, without SYSADMIN being explicitly GRANTED

    1. Expected to be a unique ROLE "FARADAY" (not shared by any other objects in Snowflake)

    2. Privileges necessary for pull (source only)

      { USAGE { TABLE | FILE FORMAT | STAGE | PIPE }}
    3. Privileges necessary for pull and push (source and destination):

      { MODIFY | MONITOR | USAGE | CREATE { TABLE | EXTERNAL TABLE | VIEW | MATERIALIZED VIEW | MASKING POLICY | ROW ACCESS POLICY | TAG | SEQUENCE | FUNCTION | PROCEDURE | FILE FORMAT | STAGE | PIPE | STREAM | TASK }}

  5. Ensure PRIVILEGES ON FUTURE TABLES is set for FARADAY ROLE

    CREATE USER FARADAY;
    CREATE ROLE FARADAY;
    GRANT ROLE FARADAY TO USER FARADAY;

    GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE FARADAY;
    ALTER USER FARADAY SET DEFAULT_WAREHOUSE = <warehouse_name>;

    ALTER USER FARADAY SET DEFAULT_ROLE = FARADAY;
    ALTER USER FARADAY SET LOGIN_NAME = FARADAY;
    ALTER USER FARADAY SET DISPLAY_NAME = FARADAY;

    GRANT USAGE ON DATABASE <database_name> TO ROLE FARADAY;
    GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO ROLE FARADAY;

    GRANT ALL PRIVILEGES ON SCHEMA <database_name>.<schema_name> TO ROLE FARADAY;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <database_name>.<schema_name> TO ROLE FARADAY;
    GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA <database_name>.<schema_name> TO ROLE FARADAY;

For keypair authentication

We need a database user with keypair authentication. You’ll need to set the FARADAY USER's public key, as shown below:

ALTER USER FARADAY 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==';

Key notes

Handling of hosting location (construction of account name URL)

  • Region identification is handled automatically, but is good to know

  • Some instance URLs may not specify region. You will receive a 403 error if used

  • No known need for .aws in the URL, sometimes found after the region

DROP TABLE (e.g. refresh) processes can wipe permissions for the USER (unless PRIVILEGES ON FUTURE TABLES is set for USER)

Name changes to OBJECTS will break permissions (permissions will need to be GRANTED again)

USER password (for login) does not matter if using the keypair (currently it is required to use Faraday keypair authentication)


Sending credentials securely to Faraday:

SOURCE (your WAREHOUSE to FARADAY)

  • Visit the sources console, click + Create source at the top.

  • Find Snowflake (AWS) as a source, enter a descriptive name, and click →Next.

  • Enter your Snowflake details, and click →Next.

  • Under Finalize, click √ Create source. Faraday will now be able to configure your source and begin bringing over data.

DESTINATION (FARADAY to your WAREHOUSE)

  • Visit the destinations console, click + New destination at the top.

  • Find Snowflake (AWS) as a destination, enter a descriptive name, and click →Next.

  • Enter your Snowflake details, and click →Next.

  • Under Finalize, click √ Create destination. Faraday will now be able to configure your destination and begin bringing over data.

Did this answer your question?