> ## 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

> Connect your Snowflake data warehouse as a source

## Step 1: locate your public key

Locate the public key provided to you. The public key will be a long string of text, loosely resembling the format: `MIIBI...<SHORTENED>...Xrw2nwIDAQAB`. You will register this public key on the Snowflake user you create in Step 2.

## Step 2: create role, user, and warehouse

<Note>
  To complete the following setup steps, you or a Snowflake admin on your team must have the `SECURITYADMIN` and `SYSADMIN` roles. To check your account for these roles, run `SHOW GRANTS TO USER <your_username>;` and review the `role` column.
</Note>

<Steps>
  <Step title="Review the setup script">
    Review and make any changes to the following setup script. Update `database_name` and `schema_name` to point at the database and schema you want to read from, list the tables you want to sync in the grant statements, and paste the public key from Step 1 into `RSA_PUBLIC_KEY`.

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

    -- create variables for user / role / warehouse
    set user_name = 'TRANSFER_USER'; -- all letters must be uppercase
    set role_name = 'TRANSFER_ROLE'; -- all letters must be uppercase
    set warehouse_name = 'TRANSFER_WAREHOUSE'; -- all letters must be uppercase
    set database_name = 'SOURCE_DATABASE'; -- all letters must be uppercase
    set schema_name = 'SOURCE_SCHEMA'; -- all letters must be uppercase

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

    -- create role for the transfer service
    create role if not exists identifier($role_name);
    grant role identifier($role_name) to role SYSADMIN; -- establish SYSADMIN as the parent of the new role. Note: this does not grant the access privileges of SYSADMIN to the new role.

    -- create a user for the transfer service
    create user if not exists identifier($user_name)
    RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- replace with the complete public key from Step 1

    -- set default role and warehouse, and mark the user as a service account
    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 the 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 warehouse and database access to the transfer role
    grant USAGE on warehouse identifier($warehouse_name) to role identifier($role_name);
    grant USAGE on database identifier($database_name) to role identifier($role_name);

    -- grant schema and table read access to the transfer role
    use database identifier($database_name);
    grant USAGE on schema identifier($schema_name) to role identifier($role_name);
    grant SELECT on table identifier($schema_name || '.<TABLE_NAME_A>') to role identifier($role_name); -- repeat for each table you want to sync
    grant SELECT on table identifier($schema_name || '.<TABLE_NAME_B>') to role identifier($role_name);

    commit;
    ```

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

    <Note>
      To grant access to every table in the schema instead of listing tables individually, replace the per-table grants with a single grant on all tables in the schema.

      ```sql title="Grant all tables" icon="database" theme={null}
      grant SELECT on all tables in schema identifier($schema_name) to role identifier($role_name);
      ```
    </Note>
  </Step>

  <Step title="Run the script">
    In the Snowflake interface, select the dropdown next to the **Run** button, and click **Run All**. This will run every query in the script at once. If successful, you will see `Statement executed successfully` in the query results.
  </Step>
</Steps>

## Step 3: configure the Snowflake access policy

If your Snowflake data warehouse is using Snowflake Access Policies, a new policy must be added to allow the static IP to read from the warehouse. Reach out to your contact for the static IP address to use.

<Steps>
  <Step title="Review current network policies">
    Run the following to check for existing IP safelists.

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

    If there is no existing Snowflake network policy (the `SHOW` query returns no results), you can skip to Step 4. If there is an existing Snowflake network policy, you must alter the existing policy or create a new one to safelist the static IP address.
  </Step>

  <Step title="Create a network policy">
    Run the following to create a new network policy that safelists the static IP address.

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

    <Warning>
      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, and will return an error if you try. Be careful when setting your first network policy.
    </Warning>
  </Step>
</Steps>

## Step 4: submit your connection details

Provide the following details to complete the source setup:

1. The **name** is a descriptive name of the source
2. The **host** (e.g., `account.us-central1.gcp.snowflakecomputing.com`)
3. The **port** \[e.g., `443`]
4. The **database** from Step 2
5. The **username** from Step 2