> For the complete documentation index, see [llms.txt](https://developer.mediarithmics.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://developer.mediarithmics.io/advanced-usages/data-warehouse-management/snowflake.md).

# Snowflake

## Steps

1. Service account and permissions setup 
2. Create dedicated schemas
3. Configure key-pair authentication
4. Create and format a credentials JSON file

{% hint style="info" %}
For more information on Snowflake key-pair authentication please refer to Snowflake documentation <https://docs.snowflake.com/en/user-guide/key-pair-auth>
{% endhint %}

### 1. Service account and permissions setup

For the initial set up, start with the SQL script template below. The script will :

* Create a dedicated service account, role, and warehouse for mediarithmics to use
* Give the service account read only access on a database of your choice and all its content

{% hint style="info" %}
**Grant limited access**

If you wish to restrict access to specific tables or views the minimum permissions required are :&#x20;

* `USAGE on DATABASE`
* `USAGE on SCHEMA`
* `SELECT on TABLES` (or VIEWS)&#x20;
  {% endhint %}

```sql
-- =============================================================
-- Setup variables - update these values for your environment
-- =============================================================
SET mics_username          = 'MICS_USER';
SET mics_default_warehouse = 'MICS_WAREHOUSE';
SET mics_database          = '<database>';
SET mics_default_namespace = '<database.schema>';
SET mics_default_role      = 'MICS_USER_ROLE';

-- =============================================================
-- Switch to admin role
-- =============================================================
USE ROLE ACCOUNTADMIN;

-- =============================================================
-- Provision resources: warehouse, role and service user
-- =============================================================
CREATE WAREHOUSE IF NOT EXISTS identifier($mics_default_warehouse);
CREATE ROLE IF NOT EXISTS identifier($mics_default_role);

CREATE USER IF NOT EXISTS identifier($mics_username)
    TYPE              = SERVICE
    DEFAULT_WAREHOUSE = $mics_default_warehouse
    DEFAULT_NAMESPACE = $mics_default_namespace
    DEFAULT_ROLE      = $mics_default_role;

-- =============================================================
-- Role assignments
-- =============================================================
GRANT ROLE identifier($mics_default_role) TO ROLE SYSADMIN;
GRANT ROLE identifier($mics_default_role) TO USER identifier($mics_username);

-- =============================================================
-- Warehouse access
-- =============================================================
GRANT USAGE ON WAREHOUSE identifier($mics_default_warehouse) TO ROLE identifier($mics_default_role);

-- =============================================================
-- Read-only access on the database, schemas, tables and views
-- (includes future objects to cover tables/views created later)
-- =============================================================
GRANT USAGE  ON DATABASE identifier($mics_database)                       TO ROLE identifier($mics_default_role);
GRANT USAGE  ON ALL SCHEMAS       IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON ALL TABLES        IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON ALL VIEWS         IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON FUTURE TABLES     IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON FUTURE VIEWS      IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
```

### 2. Create dedicated schemas

We recommend to create two dedicated schemas for mediarithmics' usage.&#x20;

* **mics\_workspace** schema that holds all temporary tables created while syncing with your warehouse
* **mics\_output** schema that holds all output tables — the clean results of daily processing, ready for consumption

{% hint style="info" %}
Nota bene : If you wish to use a [synchronization strategy](/data-ingestion/data-warehouse-ingestion.md#synchronization-strategies) that handles updates and deletes of data, you will need a [working schema](/data-ingestion/data-warehouse-ingestion.md#working-schema) (i.e. the **mics\_workspace** dataset)
{% endhint %}

{% hint style="info" %}
Note that the tables in mics\_output are not meant to be interface tables : their schema could change with successive releases. If you need a specific stable table schema to expose it to other tools, please contact your account manager.&#x20;
{% endhint %}

The service account you created should have full access (read/write) on both schemas. Use this code to do so :

```sql
-- =============================================================
-- Dedicated schemas with full ownership
-- =============================================================
SET mics_database = '<database>';

USE ROLE ACCOUNTADMIN;
USE DATABASE IDENTIFIER($mics_database);

CREATE SCHEMA IF NOT EXISTS mics_workspace;
GRANT OWNERSHIP ON SCHEMA mics_workspace TO ROLE MICS_USER_ROLE;

CREATE SCHEMA IF NOT EXISTS mics_output;
GRANT OWNERSHIP ON SCHEMA mics_output TO ROLE MICS_USER_ROLE;
```

### 3. Configure key-pair authentication&#x20;

Use the following command to generate a **private key** (called `rsa_key.p8`)

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

Then use the following command to generate a **public key** (called `rsa_key.pub` linked to the private key called rsa\_key.pub stored in the current directory)

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

Then, from the Snowflake interface or the Snowflake CLI run the following SQL query to assign the public key generated at step 1 (`rsa_key.pub`) to the service account created for mediarithmics to use :&#x20;

```sql
ALTER USER <MICS_USER> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
```

{% hint style="info" %}
Nota bene :&#x20;

* Exclude the public key delimiters in the SQL statement
* To run this query you will need one of the following role/privilege
  * The MODIFY PROGRAMMATIC AUTHENTICATION METHODS or OWNERSHIP privilege on the user
  * The SECURITYADMIN role or higher
    {% endhint %}

### 4. Edit a credentials file

Now you need to generate a JSON file with the following format :&#x20;

```json
{
"user" : "MICS_USER",
"account" : "ORGANIZATION_NAME-ACCOUNT_NAME",
"database" : "DATABASE",
"private_key" : "-----BEGIN PRIVATE KEY-----\nMII..."
}
```

* `"user"` : the user you associated the public key with
* `"account"` : the "account" property is what is called "Account identifier" in Snowflake. It's usually the organization name and the account name hyphenated
* `"database"` : the database containing the data you want to make available to mediarithmics
* `"private key"` : the private key generated in step 3 and associated to the user public key. Be ware of the formatting of the private key : the key must be in on a single line, this means you should use a "\n" separator at each return to line of the private key.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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, and the optional `goal` query parameter:

```
GET https://developer.mediarithmics.io/advanced-usages/data-warehouse-management/snowflake.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.