> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prequel.co/llms.txt
> Use this file to discover all available pages before exploring further.

# Snowflake

> Instructions for connecting to a Snowflake data warehouse as a source.

## Prerequisites

* Your Snowflake user must be granted `securityadmin` and `sysadmin` roles to complete the next step. To verify these roles, run `SHOW GRANTS TO USER <your_username>;` and review the `role` column.

<Steps>
  <Step title="Create role, user, and warehouse in the data warehouse">
    <Tabs>
      <Tab title="Key-pair authentication (recommended)">
        1. Review and make any changes to the following setup script. As written, the script should be run once for each table that needs to be transferred.

        ```sql title="Setup script" icon="database" expandable theme={null}
        begin;

           -- create variables for user / password / role / warehouse / database
           set role_name = 'TRANSFER_ROLE'; -- all letters must be uppercase
           set user_name = 'TRANSFER_USER'; -- all letters must be uppercase
           set warehouse_name = 'TRANSFER_WAREHOUSE'; -- all letters must be uppercase
           set database_name = 'SOME_DATABASE';
           set schema_name = 'SOME_SCHEMA';
           set table_name = 'SOME_TABLE'; -- repeat for all tables

           -- change role to securityadmin for user / role steps
           use role securityadmin;

           -- create role for data transfer service
           create role if not exists identifier($role_name);
           grant role identifier($role_name) to role SYSADMIN;

           -- create a user for data transfer service
           create user if not exists identifier($user_name)
           RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- replace with the complete public key copied from the connection form in the onboarding UI

           -- set default role and warehouse to new user
           alter user identifier($user_name) SET default_role = $role_name;
           alter user identifier($user_name) SET default_warehouse = $warehouse_name;
           alter user identifier($user_name) SET type = service;


           grant role identifier($role_name) to user identifier($user_name);

           -- change role to sysadmin for warehouse / database steps
           use role sysadmin;

           -- create a warehouse for data transfer service
           create warehouse if not exists identifier($warehouse_name)
           warehouse_size = xsmall
           warehouse_type = standard
           auto_suspend = 60
           auto_resume = true
           initially_suspended = true;

           -- grant service role access to warehouse
           grant USAGE
           on warehouse identifier($warehouse_name)
           to role identifier($role_name);

           -- grant service access to database
           grant MONITOR, USAGE
           on database identifier($database_name)
           to role identifier($role_name);

           -- use provided database
           use database identifier($database_name);

           -- grant service access to schema
           grant USAGE
           on schema identifier($schema_name)
           to role identifier($role_name);

           -- use provided schema
           use schema identifier($schema_name);

           -- grant service access to table
           grant SELECT
           on identifier($table_name)
           to role identifier($role_name);

         commit;
        ```

        <Note>
          **Using an existing warehouse**

          By default, this script creates a new warehouse. If you'd prefer to use an existing warehouse, change the `warehouse_name` variable from `TRANSFER_WAREHOUSE` to the name of the warehouse to be shared.
        </Note>

        2. In the Snowflake interface, select the **All Queries** checkbox, and click "Run". This will run every query in the script at once. If successful, you will see `Statement executed successfully` in the query results.

        <Frame>
          ![](https://storage.googleapis.com/prequel_docs/images/snowflake-run-all-queries.png "Screen Shot 2021-12-16 at 11.12.34 AM.png")
        </Frame>

        <Frame>
          ![](https://storage.googleapis.com/prequel_docs/images/snowflake-statement-executed.png "statement executed.png")
        </Frame>
      </Tab>

      <Tab title="Username & password authentication">
        By default, the setup script creates a new user using key-pair authentication. If you'd prefer to use username & password authentication, instead of:

        ```sql title="Key-pair user" icon="database" theme={null}
        create user if not exists identifier($user_name)
         RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
        ```

        Use the following block:

        ```sql title="Password user" icon="database" theme={null}
        create user if not exists identifier($user_name)
        password = 'some_password';
        ```

        Because service accounts cannot use username & password authentication, you will also need to remove the following line from the provided script:

        ```sql title="Remove service type" icon="database" theme={null}
        alter user identifier($user_name) SET type = service;
        ```

        <Note>
          **Using an existing warehouse**

          By default, this script creates a new warehouse. If you'd prefer to use an existing warehouse, change the `warehouse_name` variable from `TRANSFER_WAREHOUSE` to the name of the warehouse to be shared.
        </Note>

        After applying the changes above, select the **All Queries** checkbox in the Snowflake interface, and click "Run". This will run every query in the script at once. If successful, you will see `Statement executed successfully` in the query results.

        <Frame>
          ![](https://storage.googleapis.com/prequel_docs/images/snowflake-run-all-queries.png "Screen Shot 2021-12-16 at 11.12.34 AM.png")
        </Frame>

        <Frame>
          ![](https://storage.googleapis.com/prequel_docs/images/snowflake-statement-executed.png "statement executed.png")
        </Frame>
      </Tab>
    </Tabs>
  </Step>

  <Step title="Configure the Snowflake access policy">
    If your Snowflake data warehouse is using Snowflake Access Policies, a new policy must be added to allow the transfer service static IP to write to the warehouse.

    1. Review current network policies to check for existing IP safelists.

    ```sql title="Review network policies" icon="database" theme={null}
    SHOW NETWORK POLICIES;
    ```

    2. If there is no existing Snowflake Network Policies (the `SHOW` query returns no results), you can skip to Step 3.
    3. If there is an existing Snowflake Network Policy, you must alter the existing policy or create a new one to safelist the data transfer service static IP address. Use the `CREATE NETWORK POLICY` command to specify the IP addresses that can access your Snowflake warehouse. The IP addresses can be found [here](/export/deployment/prequel-ips).

    ```sql title="Create network policy" icon="database" theme={null}
    CREATE NETWORK POLICY <transfer_service_policy_name> ALLOWED_IP_LIST = ('5.4.7.8/32');
    ```

    <Warning>
      **Creating your first network policy**

      If you have no existing network policies and you create your first as part of this step, all other IPs outside of the `ALLOWED_IP_LIST` will be blocked. Snowflake does not allow setting a network policy that blocks your current IP address. (An error message results while trying to create a network policy that blocks the current IP address.) But be careful when setting your first network policy.
    </Warning>
  </Step>

  <Step title="Add source to service">
    Use the **host name**, **database name**, **username**, and **password**  to complete the connection.
  </Step>
</Steps>