> 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/exporting-your-data/datamart-replication/data-warehouse-replication/snowflake.md). # Snowflake {% hint style="info" %} The replication to Snowflake requires an object storage to store the log files. We support both **Amazon S3 or GCS** for this use case. We recommend creating a dedicated bucket, but you can also use a existing one. {% endhint %} ## Prerequisite In order to set up the replication you will need : * To have declared [the connection to your Snowflake data warehouse](/advanced-usages/data-warehouse-management/create-a-data-warehouse/snowflake.md) in mediarithmics * When declaring the warehouse we expect you declare the **mics\_output schema** in which we should have the necessary privilege (`OWNERSHIP`) to write the replicated tables. * An object storage to store the AVRO log files. We support both **Amazon S3** or **GCS** for this use case. We recommend creating a dedicated bucket, but you can also use a existing one. {% hint style="info" %} **Nota bene** : the log tables are populated once a day, when the daily job is running. If you need the log tables to be refreshed more frequently, please contact your account manager to discuss the available options. {% endhint %} ## Overview #### Global data workflow The big picture data flow is the following : ``` GCS / S3 bucket (Avro files) │ │ STORAGE INTEGRATION (grants Snowflake access to the bucket) ▼ ┌──────────┐ │ STAGE │ external stage pointing at the bucket └──────────┘ │ │ COPY INTO ▼ ┌──────────┐ │ log │ one append-only log table per document type │ tables │ └──────────┘ │ │ daily state-refresh job ▼ ┌──────────┐ │ state │ one deduplicated state table per document type │ tables │ └──────────┘ ``` * **STORAGE INTEGRATION** - authorizes Snowflake to read the bucket. * **STAGE** - points Snowflake at the bucket so it can read its files. * **COPY INTO** — loads the Avro files from the stage into the log tables. * **State refresh** — a daily job rebuilds the state tables from the log tables. #### Steps to complete We assume the prerequisite steps are completed. Then depending which bucket type you are using the steps differ. **GCS bucket** 1. Grant permissions on the GCS bucket to a service account 2. Create an HMAC key pair 3. Configure the replication in mediarithmics 4. Set up the Snowflake environment 5. Grant access to the bucket to the Snowflake Integration GCP account 6. Validate the set up in mediarithmics **S3 bucket** 1. Grant permissions on the bucket S3 to a service account 2. Create an Access key 3. Create a role that will be used by the Snowflake integration 4. Configure the replication in mediarithmics 5. Set up the Snowflake environment 6. Grant access to the bucket to the Snowflake Integration AWS account 7. Validate the set up in mediarithmics ## Snowflake replication - GCS bucket The set up will requires 3 differents service accounts with each one a given role : 1. A Snowflake service account used for **managing the connection between Snowflake and mediarithmics**, having the needed access rights on the Snowflake tables/schema 2. A GCP service account to **own the HMAC key**, having editing rights on the bucket to insert the AVRO log files 3. Another GCP service account, **representing the integration between Snowflake and your bucket**, and that allows Snowflake to read the bucket All the steps occurring in Snowflake and GCS in this section are derived from this Snwoflake documentation. Refer to it for more information : ### Step #1 : grant permissions on the GCS bucket to a service acount Create a service account (IAM & Admin>Service Accounts>"Create service account") and grant it the following role on the bucket : * `storage.objectAdmin` * `storage.viewer` To do so, in the Cloud Storage interface : * Go to Cloud Storage>Buckets, select the one bucket you wish to use * Then on the "Permissions" tab > "View by principals" > "Grant access" * Enter the service account address, then select the two necessary roles {% hint style="info" %} You will need permissions included in the `storage.admin` role to perform these actions {% endhint %} Find more information in the Cloud Storage documentation : ### Step #2 : create an HMAC key pair In the Cloud Storage interface : * Go to Cloud Storage>Settings, then in the "Interoperability" tab * Then in access keys for service accounts > "Create a key for another service account" * Select the service account created in step #1 * Click on Create (the Secret key is only shown once at creation). * **Keep these credentials somewhere as you will need them later** Do not change the "Request endpoint" : keep existing request URI {% hint style="info" %} You will need permissions included in the `storage.admin` role to perform these actions {% endhint %} Find more information in the Cloud Storage documentation : ### Step #3 : Configure the replication in mediarithmics You are now able to [configure your replication](/advanced-usages/exporting-your-data/datamart-replication.md). Just make sure to use the correct data warehouse destination, bucket, dataset with editing rights, and re-use the HMAC key-pair you generated in step #2. ### Step #4 : Set up the Snowflake environment Once the replication is created in mediarithmics, you should set up your Snowflake environment. In order to do so, mediarithmics automatically generates a custom script for you to run on your Snowflake environment. To get the script you should call : `GET` `https://api.mediarithmics.com/v1/datamarts/:datamartId/replications/:replicationId/initial_config_script` **Path Parameters** | Name | Type | Description | | ------------- | ------- | ---------------------------------- | | datamartId | integer | The ID of the datamart | | replicationId | integer | The ID of the datamart replication | **Response example** For instance this replication has filters on `USER_DEVICE_POINT` and `USER_DEVICE_TECHNICAL_ID` {% code expandable="true" %} ```sql create role mediarithmics_replication_; create user mediarithmics_external_user_; GRANT CREATE TABLE ON SCHEMA TO ROLE mediarithmics_replication_; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA TO ROLE mediarithmics_replication_; GRANT ROLE mediarithmics_replication_ TO USER mediarithmics_external_user_; USE DATABASE ; USE SCHEMA ; -- name must be unique to the snowflake account CREATE STORAGE INTEGRATION mediarithmics_replication__gcs_storage TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'GCS' ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = (''); -- located in the schema CREATE STAGE mediarithmics_replication__stage URL = '''' STORAGE_INTEGRATION = mediarithmics_replication__gcs_storage FILE_FORMAT = (TYPE = avro); CREATE TABLE IF NOT EXISTS MICS_WORKSPACE.user_device_point_log (ts TIMESTAMP_NTZ NOT NULL, op STRING NOT NULL, user_point_id STRING NOT NULL, user_device_point_id NUMBER NOT NULL, creation_ts TIMESTAMP_NTZ NOT NULL, form_factor STRING, os_family STRING, os_version STRING, browser_family STRING, brand STRING, model STRING, carrier STRING, agent_type STRING, partition_date DATE NOT NULL, year number not null, month number not null, day number not null, hour number not null ); CREATE TABLE MICS_WORKSPACE.user_device_point (user_point_id STRING, user_device_point_id BIGINT, brand STRING, model STRING, agent_type STRING, browser_family STRING, form_factor STRING, os_family STRING, ts TIMESTAMP, partition_date DATE NOT NULL) ; CREATE TABLE IF NOT EXISTS MICS_WORKSPACE.user_device_technical_id_log (ts TIMESTAMP_NTZ NOT NULL, op STRING NOT NULL, user_point_id STRING NOT NULL, user_device_point_id NUMBER NOT NULL, expiration_ts TIMESTAMP_NTZ, registry_type STRING NOT NULL, registry_id NUMBER NOT NULL, user_device_technical_id_value STRING NOT NULL, last_seen_ts TIMESTAMP_NTZ NOT NULL, partition_date DATE NOT NULL, year number not null, month number not null, day number not null, hour number not null ); CREATE TABLE MICS_WORKSPACE.user_device_technical_id (user_point_id STRING, user_device_technical_id STRING, registry_type STRING, registry_id BIGINT, id STRING, user_device_point_id BIGINT, last_seen_ts TIMESTAMP, expiration_ts TIMESTAMP, ts TIMESTAMP, partition_date DATE NOT NULL) CLUSTER BY (registry_id); DESC STORAGE INTEGRATION mediarithmics_replication__gcs_storage; ``` {% endcode %} The main taks the script does are : * Creating a STAGE that will read the bucket * Creating a STORAGE INTEGRATION that allows Snowflake to read the bucket * Creating all necessary log and states tables (based on the replication filters you chose in step #3) Run this script on you Snowflake environment in a SQL file. {% hint style="danger" %} **You should run this script using the role that has the `OWNERSHIP` privilege on the schema the data is being replicated to (mics\_output for instance)** {% endhint %} The last command (`DESC STORAGE INTEGRATION ...`) will return the following : {% code expandable="true" %} ```csv property,property_type,property_value,property_default ENABLED,Boolean,true,true STORAGE_PROVIDER,String,GCS, STORAGE_ALLOWED_LOCATIONS,List,gcs://bucket_address/,[] STORAGE_BLOCKED_LOCATIONS,List,,[] USE_PRIVATELINK_ENDPOINT,Boolean,false,false STORAGE_GCP_SERVICE_ACCOUNT,String,service-account-id@project1-123456.iam.gserviceaccount.com, COMMENT,String,, ``` {% endcode %} The important information is the `STORAGE_GCP_SERVICE_ACCOUNT` : **save it** as you will need it in the next step. ### Step #5 : Grant access to the bucket to the Snowflake Integration GCP account #### Create a custom role Now you must grant access to the bucket to the GCP service account owning the Snowflake Integration. To do so, you should go the GCP console and create a custom role (see ) with the following permissions : * `storage.buckets.get` * `storage.objects.create` * `storage.objects.delete` * `storage.objects.get` * `storage.objects.list` #### Assign the custom role To do so, in the Cloud Storage interface : * Go to Cloud Storage>Buckets, select the bucket you wish to use * Then on the "Permissions" tab > "View by principals" >Copy paste the `STORAGE_GCP_SERVICE_ACCOUNT` * Then click on "Assign roles" and select the custom role that you just created * Save ### Step #6 : Validate the set up in mediarithmics Now the set up is done you should call the following endpoint to manually confirm it : `PUT` `https://api.mediarithmics.com/v1/datamarts/{datamart_id}/replications/{replication_id}` **Path Parameters** | Name | Type | Description | | ------------- | ------- | ---------------------------------- | | datamartId | integer | The ID of the datamart | | replicationId | integer | The ID of the datamart replication | **Request body example** {% code expandable="true" %} ```json { "initial_config_status": "COMPLETED", "type": "DATA_WAREHOUSE" } ``` {% endcode %} Now you should be able to [activate](/advanced-usages/exporting-your-data/datamart-replication.md#creating-and-starting-a-replication) the replication and trigger an [initial loading](/advanced-usages/exporting-your-data/datamart-replication.md#launch-initial-synchronization-on-one-replication). ## Snowflake replication - S3 bucket The set up will requires 3 differents service accounts with each one a given role : 1. A Snowflake service account used for **managing the connection between Snowflake and mediarithmics**, having the needed access rights on the Snowflake tables/schema 2. A AWS service account to **own the Access Key**, having editing rights on the bucket to insert the AVRO log files 3. Another AWS service account, **representing the integration between Snowflake and your bucket**, and that allows Snowflake to read the bucket All the steps occurring in Snowflake and AWS in this section are derived from this Snwoflake documentation. Refer to it for more information : ### Step #1 : Grant permissions on the bucket S3 to a service account {% hint style="info" %} Before anything else make sure the **Security Token Service (STS) is active** in the region where your account is located. Go to the AWS console>IAM>Account settings>Security Token Service>Endpoints {% endhint %} #### **Create an IAM Policy** First you will need to create a AWS IAM Policy. The required permissions are the following : * `s3:GetBucketLocation` * `s3:GetObject` * `s3:GetObjectVersion` * `s3:ListBucket` * `s3:GetBucketLocation` To do so, go to the AWS console then : * IAM>Policies>Create Policy * Then Select JSON and you can use this example by replacing inside of the "Resource" field the "`BUCKET_NAME`" part with your own bucket name ```json { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:PutObject", "s3:GetObject", "s3:DeleteObject" ], "Resource": "arn:aws:s3:::julien-s3-snwoflake-test/*" }, { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:GetBucketLocation" ], "Resource": "arn:aws:s3:::julien-s3-snwoflake-test", "Condition": { "StringLike": { "s3:prefix": [ "*" ] } } } ] } ``` * Then "Next" * Enter a policy name and add a description if you like (remember the **name of the policy as you will need it later on**) * Then "Create policy" More information is available here : #### **Create an IAM user** You should create an IAM user and attach the policy you just created to the user. To do so in the AWS console go to : * IAM>IAM users>Create user * Enter a name then "Next" * In the "Permission options" panel choose "Attach policies directly" and search for the policy you just created. Check the checkbox next to it then "Next" * Review the information then "Create user" More information is available here : ### Step #2 : Create an access key Now we will configure an access key for the user we created in step #1. To do so in the AWS console, go to : * IAM>IAM users> select the user * Then in the Security credentials tabs >Create access key * When asked the use case choose the "Application running outside AWS" option. Then "Next" * Add a tag if you wish, then "Create access key" * You will land on a page where the access key and secret are shown. **Save them**. You can also download a csv file containing the key pair More information is available here : ### Step #3 : Create a role that will be used by the Snowflake integration Now we will create a Role that will be used for the Snowflake Storage Integration. The role will have the permissions carried by the policy created in step #1. In order to create this role, in the AWS console go to : * IAM>Roles>Create role * Choose the Trusted entity type : "AWS account" * Then in the "An AWS account" section choose : "Another AWS account" >put yours for instance (we’ll change it later) * In Options, check >Require external ID > put 0000 for now (we’ll change it as well) * Next * Select the policy you created in step #1, then "Next" * Choose a name and then "Create role". Remember the **name of the role.** Now go to the page of the role you created (IAM>Roles) and **copy the ARN value** (it should look like `arn:aws:iam:::role/`) ### Step #4 : Configure the replication in mediarithmics You are now able to [configure your replication](/advanced-usages/exporting-your-data/datamart-replication.md) in mediarithmics. Just make sure to use the correct data warehouse destination, bucket, schema with editing rights, and re-use the Access key key-pair you generated in step #2. ### Step #5 : Set up the Snowflake environment Once the replication is created in mediarithmics, you should set up your Snowflake environment. In order to do so, mediarithmics automatically generates a custom script for you to run on your Snowflake environment. To get the script you should call : `GET` `https://api.mediarithmics.com/v1/datamarts/:datamartId/replications/:replicationId/initial_config_script` **Path Parameters** | Name | Type | Description | | ------------- | ------- | ---------------------------------- | | datamartId | integer | The ID of the datamart | | replicationId | integer | The ID of the datamart replication | **Response example** For instance this replication has filters on `USER_DEVICE_POINT` and `USER_DEVICE_TECHNICAL_ID` {% code expandable="true" %} ```sql create role mediarithmics_replication_; create user mediarithmics_external_user_; GRANT CREATE TABLE ON SCHEMA TO ROLE mediarithmics_replication_; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA TO ROLE mediarithmics_replication_; GRANT ROLE mediarithmics_replication_ TO USER mediarithmics_external_user_; USE DATABASE ; USE SCHEMA ; -- name must be unique to the snowflake account CREATE STORAGE INTEGRATION mediarithmics_replication__gcs_storage TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' ENABLED = TRUE STORAGE_AWS_ROLE_ARN = '' STORAGE_ALLOWED_LOCATIONS = (''); -- located in the schema CREATE STAGE mediarithmics_replication__stage URL = '''' STORAGE_INTEGRATION = mediarithmics_replication__gcs_storage FILE_FORMAT = (TYPE = avro); CREATE TABLE IF NOT EXISTS MICS_WORKSPACE.user_device_point_log (ts TIMESTAMP_NTZ NOT NULL, op STRING NOT NULL, user_point_id STRING NOT NULL, user_device_point_id NUMBER NOT NULL, creation_ts TIMESTAMP_NTZ NOT NULL, form_factor STRING, os_family STRING, os_version STRING, browser_family STRING, brand STRING, model STRING, carrier STRING, agent_type STRING, partition_date DATE NOT NULL, year number not null, month number not null, day number not null, hour number not null ); CREATE TABLE MICS_WORKSPACE.user_device_point (user_point_id STRING, user_device_point_id BIGINT, brand STRING, model STRING, agent_type STRING, browser_family STRING, form_factor STRING, os_family STRING, ts TIMESTAMP, partition_date DATE NOT NULL) ; CREATE TABLE IF NOT EXISTS MICS_WORKSPACE.user_device_technical_id_log (ts TIMESTAMP_NTZ NOT NULL, op STRING NOT NULL, user_point_id STRING NOT NULL, user_device_point_id NUMBER NOT NULL, expiration_ts TIMESTAMP_NTZ, registry_type STRING NOT NULL, registry_id NUMBER NOT NULL, user_device_technical_id_value STRING NOT NULL, last_seen_ts TIMESTAMP_NTZ NOT NULL, partition_date DATE NOT NULL, year number not null, month number not null, day number not null, hour number not null ); CREATE TABLE MICS_WORKSPACE.user_device_technical_id (user_point_id STRING, user_device_technical_id STRING, registry_type STRING, registry_id BIGINT, id STRING, user_device_point_id BIGINT, last_seen_ts TIMESTAMP, expiration_ts TIMESTAMP, ts TIMESTAMP, partition_date DATE NOT NULL) CLUSTER BY (registry_id); DESC STORAGE INTEGRATION mediarithmics_replication__gcs_storage; ``` {% endcode %} The main tasks the script does are : * Creating a STAGE that will read the bucket * Creating a STORAGE INTEGRATION that allows Snowflake to read the bucket * Creating all necessary log and states tables (based on the replication filters you chose in step #4) **Before running the script look for the** `STORAGE_AWS_ROLE_ARN = ''` **part in the script and replace the value by the ARN of the role created in step #4** Now you can run this script on your Snowflake environment in a SQL file. {% hint style="danger" %} **You should run this script using the role that has the `OWNERSHIP` privilege on the schema the data is being replicated to (mics\_output for instance)** {% endhint %} The last command (`DESC STORAGE INTEGRATION ...`) will return the following : {% code expandable="true" %} ```csv property, property_type, property_value, property_default ENABLED, Boolean, true, true STORAGE_PROVIDER, String, S3 STORAGE_ALLOWED_LOCATIONS, List, s3://buceket_address [] STORAGE_BLOCKED_LOCATIONS, List , [] STORAGE_AWS_IAM_USER_ARN, String, arn:aws:iam:::user/-s STORAGE_AWS_ROLE_ARN, String, arn:aws:iam:::role/ STORAGE_AWS_EXTERNAL_ID, String, USE_PRIVATELINK_ENDPOINT, Boolean, false, false COMMENT, String ``` {% endcode %} The important information are the `STORAGE_AWS_IAM_USER_ARN` and the `STORAGE_AWS_EXTERNAL_ID` : **save them** as you will need it in the next step. ### Step #6 : Grant access to the bucket to the Snowflake Integration AWS account Now we have to replace the default values we put in step #3 for the role we created in the same step. To do so, in the AWS console, go to : * IAM>Roles> select the one created in step #3 * In the "Trust relationships" tab >Edit trust policy It should look like this : ```json { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam:::root" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "0000" } } } ] } ``` * In the `"Principal"."AWS"` replace the value by the `STORAGE_AWS_IAM_USER_ARN` you got in step #5 * In the `"Condition"."StringEquals":"sts:ExternalId"` replace the value by the `STORAGE_AWS_EXTERNAL_ID` you got in step #5 * Then click on "Update policy" ### Step #7 : Validate the set up in mediarithmics Now the set up is done you should call the following endpoint to manually confirm it : `PUT` `https://api.mediarithmics.com/v1/datamarts/{datamart_id}/replications/{replication_id}` **Path Parameters** | Name | Type | Description | | ------------- | ------- | ---------------------------------- | | datamartId | integer | The ID of the datamart | | replicationId | integer | The ID of the datamart replication | **Request body example** {% code expandable="true" %} ```json { "initial_config_status": "COMPLETED", "type": "DATA_WAREHOUSE" } ``` {% endcode %} Now you should be able to [activate](/advanced-usages/exporting-your-data/datamart-replication.md#creating-and-starting-a-replication) the replication and trigger an [initial loading](/advanced-usages/exporting-your-data/datamart-replication.md#launch-initial-synchronization-on-one-replication). ## FAQ **When running the SQL script in Snowflake, I'm getting "Insufficient privileges" errors** Make sure the role you use to run the script has : * The `OWNERSHIP` privilege on the schema * The `CREATE INTEGRATION` privilege on the Snowflake account **How can I make sure that all the access rights set up has been done correctly for the STAGE and the STORAGE INTEGRATION ?** Once you completed all the steps you can try running this command in Snowflake ```sql LIST @mediarithmics_replication__stage; ``` If you do not get any errors it means that all access rights considerations should be good. *Nota bene* : the query result **can be empty**, it just means the bucket is empty. What you should not be getting is execution error. --- # 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/exporting-your-data/datamart-replication/data-warehouse-replication/snowflake.md?ask=&goal= ``` `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.