# Integrating with Snowflake (Small Datasets)

[Prerequisites](#prerequisites)

[Snowflake Setup](#snowflake-setup)

[Creating Storage Integration](#creating-storage-integration)\
   [Step 1: Create storage integration with provider as GCS](#step-1-create-storage-integration-with-provider-as-gcs)

[Connecting via username & password](#connecting-via-username-and-password)\
   [Step 1: Create a new warehouse/use an existing warehouse](#step-1-create-a-new-warehouse-use-an-existing-warehouse)\
   [Step 2: Create a user & role](#step-2-create-a-user-and-role)\
   [Step 3: Get Snowflake identifier details](#step-3-get-snowflake-identifier-details)\
   [Step 4: Add permissions to tables](#step-4-add-permissions-to-tables)\
   [Step 5: Provide access to warehouse](#step-5-provide-access-to-warehouse)\
[Connecting via key-pair](#connecting-via-key-pair)\
   [Step 1: Generate a private key](#step-1-generate-a-private-key)\
   [Step 2: Generate a public key](#step-2-generate-a-public-key)\
   [Step 3: Assign the public key to a Snowflake user](#step-3-assign-the-public-key-to-a-snowflake-user)

[Drivetrain Setup](#drivetrain-setup)\
   [Step 1: Connect to Snowflake](#step-1-connect-to-snowflake)\
   [Step 2: Import data from Snowflake](#step-2-import-data-from-snowflake)

## Prerequisites

To connect Snowflake to Drivetrain, you will need:

* Admin privileges in Snowflake
* Owners of a user, or users with the SECURITYADMIN role or higher in Snowflake to be able to alter a user (if connecting via key-pair) . For more information, see [Overview of Access Control](https://docs.snowflake.com/en/user-guide/security-access-control-overview) and [GRANT OWNERSHIP](https://docs.snowflake.com/en/sql-reference/sql/grant-ownership).
* Admin permissions in Drivetrain.

## Snowflake Setup <a href="#snowflake-setup" id="snowflake-setup"></a>

## *Creating Storage Integration* <a href="#creating-storage-integration" id="creating-storage-integration"></a>

### Step 1: Create storage integration with provider as GCS <a href="#step-1-create-storage-integration-with-provider-as-gcs" id="step-1-create-storage-integration-with-provider-as-gcs"></a>

1. Execute the below query command to create GCS integration.

```
CREATE STORAGE INTEGRATION gcs_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'GCS'
ENABLED = TRUE
STORAGE_ALLOWED_LOCATIONS = ('gcs://dt-data-import/{Tenant_ID}/');

```

{% hint style="info" %}
**Note:** Get Tenant\_ID from your Customer Success Manager or Data Integrations Manage:
{% endhint %}

2. Execute the below query to retrieve the Service Account details, get the property\_value of "**STORAGE\_GCP\_SERVICE\_ACCOUNT**" property and store it in a secured document.

```
// Some code
DESC STORAGE INTEGRATION gcs_int;
```

<figure><img src="/files/y4e9MUZpujxE01vRkfWB" alt=""><figcaption></figcaption></figure>

## *Connecting via username & password* <a href="#connecting-via-username-and-password" id="connecting-via-username-and-password"></a>

### Step 1: Create a new warehouse/use an existing warehouse <a href="#step-1-create-a-new-warehouse-use-an-existing-warehouse" id="step-1-create-a-new-warehouse-use-an-existing-warehouse"></a>

1. Go to **Admin** → **Warehouses** in the left navigation section.

<figure><img src="/files/maMUK2mlhTcyQeOvS0O3" alt=""><figcaption></figcaption></figure>

2. We recommend creating a new warehouse to run data transfer queries for Drivetrain. However, you can also choose an existing data warehouse. If Warehouse is not present already, follow the next step to create one

<figure><img src="/files/KlOHoZR01SJsKrki7FvG" alt=""><figcaption></figcaption></figure>

3. Click on the top right button to create a new warehouse. Choose the **Size** as per the amount of data you need to query. You can change this later if needed.

<figure><img src="/files/IxO7YUQBPSsIbclY19l6" alt=""><figcaption></figcaption></figure>

{% hint style="success" %}
Please make a note of the warehouse name. This detail will be needed while creating a user.
{% endhint %}

### Step 2: Create a user & role <a href="#step-2-create-a-user-and-role" id="step-2-create-a-user-and-role"></a>

1. We recommend creating a new user. However, you can also choose an existing user. If user is not already present, follow the next step to create one.
2. To create a user and role, go to **Admin → Users & Roles** in the left navigation section.

<figure><img src="/files/c5Wbmasxl7HhjDzEy1xh" alt=""><figcaption></figcaption></figure>

3. Select the **Roles** tab and click on the **+ Role** button at the top right to create a custom role. This is an optional step and you can skip this if you already have a relevant role defined

<figure><img src="/files/ue21DxThlRYFnbF21jil" alt=""><figcaption></figcaption></figure>

4. Then click on the **+ User** button at the top right and fill in the details. Please choose the role that you created in the step above. Choose the warehouse that we had created or selected in the previous section as the Default Warehouse. You can also choose some Default Namespace if needed (this is optional).

<figure><img src="/files/tKyj3QtIwZGYh5xz5xUz" alt=""><figcaption></figcaption></figure>

{% hint style="success" %}
If you check the checkbox against 'Force user to change password on first time login' then you must activate the user by logging into the new account and changing the password.
{% endhint %}

{% hint style="info" %}
At the end of this step, you will get the username / password and warehouse name that you will need for the connection.
{% endhint %}

### Step 3: Get Snowflake identifier details <a href="#step-3-get-snowflake-identifier-details" id="step-3-get-snowflake-identifier-details"></a>

1. To get this, click on the Account name next to the Snowflake icon at the bottom left menu. Mouse hover to the respective Snowflake Account.
2. Then click on the Copy icon. This will copy the Account Idetifier. This should be something like: ***ICKXXXX.ACCOUNT\_NAME***. Please note this as well.

<figure><img src="/files/OINSdcWP60rIRyGRYKEW" alt=""><figcaption></figcaption></figure>

### Step 4: Add permissions to tables <a href="#step-4-add-permissions-to-tables" id="step-4-add-permissions-to-tables"></a>

1. Please go to **Data → Databases** in the left navigation bar. Select the tables that you want to give permissions for.

<figure><img src="/files/tRTY4tkDf8BSmDFjy0RF" alt=""><figcaption></figcaption></figure>

2. Click on the **+ Privilege** icon and then select the role that you had created in the sections above. Choose the default privilege in the **Privileges** dropdown and click on **Grant Privileges.**

<figure><img src="/files/Vf6Q8jiyuMVUj4JEib4j" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/K22heaCo3g1lM179kFjt" alt=""><figcaption></figcaption></figure>

### Step 5: Provide access to warehouse <a href="#step-5-provide-access-to-warehouse" id="step-5-provide-access-to-warehouse"></a>

1. Execute the below command to provide warehouse access to the role defined in Step 2.

```
GRANT USAGE ON WAREHOUSE EXAMPLE_WAREHOUSE TO ROLE EXAMPLE_ROLE;
```

EXAMPLE\_WAREHOUSE = name of the warehouse as per [Step 1](#step-1-create-a-new-warehouse-use-an-existing-warehouse).

EXAMPLE\_ROLE = name of the warehouse as per [Step 2](#step-2-create-a-user-and-role).

## *Connecting via key-pair* <a href="#connecting-via-key-pair" id="connecting-via-key-pair"></a>

### Step 1: Generate a private key <a href="#step-1-generate-a-private-key" id="step-1-generate-a-private-key"></a>

1. Open a terminal window on your system and type the below command line:

```
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
```

2. The commands generate a private key in PEM format.

```
-----BEGIN PRIVATE KEY----- 
MIIBIj... 
-----END PRIVATE KEY-----
```

3. Make a note of this key and store securely. You will need to enter this key on Drivetrain when connecting Drivetrain to Snowflake.

### Step 2: Generate a public key

1. Open the same terminal window on which you ran the command to generate a private key and now run the below command line:

```
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```

2. The commands generate a private key in PEM format.

```
-----BEGIN PUBLIC KEY-----
MIIE6T...
-----END PUBLIC KEY-----
```

3. Make a note of this key and store securely.

### Step 3: Assign the public key to a Snowflake user <a href="#step-3-assign-the-public-key-to-a-snowflake-user" id="step-3-assign-the-public-key-to-a-snowflake-user"></a>

1. Execute an [ALTER USER](https://docs.snowflake.com/en/sql-reference/sql/alter-user) command to assign the public key to a Snowflake user.

```
ALTER USER example_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
```

example\_user = username of the account you want to connect to Drivetrain.

<figure><img src="/files/tbMua4LKmMaUJGMIGFDg" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
While copying the public key ensure to:

* exclude the public key delimiters in the SQL statement.
* remove all line breaks.
  {% endhint %}

## Drivetrain Setup <a href="#drivetrain-setup" id="drivetrain-setup"></a>

### Step 1: Connect to Snowflake <a href="#step-1-connect-to-snowflake" id="step-1-connect-to-snowflake"></a>

1. Open you Drivetrain account and navigate to the **Data** tab, click on **+ New data source.**
2. Search for **Snowflake**.
3. Click on **Connect**.
4. Choose your preferred method for authentication and click on **OK**.

<figure><img src="/files/P2vwTvmgPT5RjVWCJrzd" alt=""><figcaption></figcaption></figure>

5. Fill all the fields with relevant details of your Snowflake account and click **Submit**. Follow the steps listed [here](#connecting-via-username-and-password) to understand how to get your Snowflake account details such as Username, Warehouse and Database.

<figure><img src="/files/m6s5uYbJ3QUsjS6zRf9f" alt=""><figcaption></figcaption></figure>

6. Click on **Data** tab again and you will see a new connection to Snowflake.

### Step 2: Import data from Snowflake <a href="#step-2-import-data-from-snowflake" id="step-2-import-data-from-snowflake"></a>

1. Navigate to the new Snowflake connection and click on **+ Add data**.
2. You will see a list of all available tables. The tables follow the below nomenclature:

```
Schema_Name.Table_Name
```

3. Select the tables from which you want to get data into Drivetrain and move them to the right side of the window.

<figure><img src="/files/NFJaX8OScDk0xQmsoTUZ" alt=""><figcaption></figcaption></figure>

4. Click **Add**. That's it! All your relevant data from Snowflake will now be available in Drivetrain for your use.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://integrations.drivetrain.ai/integration-guide/integrate-with-drivetrain/databases-and-warehouses/integrating-with-snowflake-small-datasets.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.