> For the complete documentation index, see [llms.txt](https://docs.mediarithmics.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.mediarithmics.io/setup-and-monitor/collect-and-manage-your-data/export-your-data/data-warehouse-export.md).

# Data warehouse export

Replicate the data mediarithmics ingests into Google BigQuery or Snowflake, as log and state tables.

## Your goal

Have the data mediarithmics ingests land in your own cloud data warehouse - **Google BigQuery** or **Snowflake** - as tables your analysts and your BI tools query alongside the rest of your data.

## Expected outcome

An active **data warehouse replication**: files written continuously into a cloud storage bucket you own, and one pair of tables per document type in your warehouse, rebuilt from those files.

{% hint style="info" %}
Datamart replication is **not included in the default plan**. Contact your account manager to activate it.
{% endhint %}

## What this feature is

A data warehouse replication is a [datamart replication](/setup-and-monitor/collect-and-manage-your-data/export-your-data/datamart-replication.md) whose destination is a warehouse rather than a message queue. You select the documents to replicate once, and every `update` and `delete` operation on them is delivered from then on.

The delivery is indirect: the platform writes files to a cloud storage bucket, and your warehouse reads them.

```mermaid
flowchart LR
    MICS["mediarithmics"] -->|Avro files, every 15 minutes| B[("Cloud storage bucket")]
    B -->|append-only| LOG["log tables<br/>one per document type"]
    LOG -->|daily job| STATE["state tables<br/>current, deduplicated"]
```

| Table     | What it holds                                                                                 |
| --------- | --------------------------------------------------------------------------------------------- |
| **log**   | Every replicated operation, append-only.                                                      |
| **state** | The current, deduplicated view of the document type. Rebuilt from the log by a **daily job**. |

How fresh the log tables are depends on the warehouse:

| Warehouse     | Log tables                                                                                                                                  |
| ------------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| **BigQuery**  | External tables reading the bucket in real time, so they always match what is in the bucket.                                                |
| **Snowflake** | Populated once a day, when the daily job runs. If you need them refreshed more often, ask your account manager about the available options. |

### Supported buckets per warehouse

| Warehouse | Supported bucket                  |
| --------- | --------------------------------- |
| BigQuery  | Google Cloud Storage              |
| Snowflake | Google Cloud Storage or Amazon S3 |

You supply the bucket credentials - a GCS HMAC key pair, or an S3 access key - when you set the replication up. A dedicated bucket is recommended, but an existing one works.

### What lands in the warehouse

One table pair per document you selected:

<table data-search="false"><thead><tr><th width="330">Document</th><th>Table</th></tr></thead><tbody><tr><td><code>USER_SEGMENT</code></td><td><code>UserSegmentOperationRecord</code></td></tr><tr><td><code>USER_PROFILE</code></td><td><code>UserProfileOperationRecord</code></td></tr><tr><td><code>USER_ACCOUNT</code></td><td><code>UserAccountOperationRecord</code></td></tr><tr><td><code>USER_EMAIL</code></td><td><code>UserEmailOperationRecord</code></td></tr><tr><td><code>USER_DEVICE_POINT</code></td><td><code>UserDevicePointOperationRecord</code></td></tr><tr><td><code>USER_DEVICE_TECHNICAL_ID</code></td><td><code>UserDeviceTechnicalIdOperationRecord</code></td></tr><tr><td><code>USER_ACTIVITY</code></td><td><code>UserActivityOperationRecord</code></td></tr><tr><td><code>USER_EVENT</code></td><td><code>UserEventOperationRecord</code></td></tr><tr><td><code>USER_COMPUTED_FIELD</code></td><td><code>UserComputedFieldOperationRecord</code></td></tr><tr><td><code>USER_ML_FUNCTION</code></td><td><code>UserMlFunctionOperationRecord</code></td></tr></tbody></table>

Every record carries the same three leading fields - `ts`, the operation timestamp; `op`, `UPDATE` or `DELETE`; and `user_point_id` - followed by the fields of that document type.

{% hint style="info" %}
**Where a file lands in the bucket.** Files are laid out one folder per document type, then partitioned by hour, so a loader can read a single hour without scanning the rest:

```
<bucket>/<path>/type=<RecordName>/year=YYYY/month=MM/day=DD/hour=HH/data_<partitionId>_offset_<fromOffset>.avro
```

For example: `my-bucket/replication=12345/type=UserProfileOperationRecord/year=2026/month=07/day=13/hour=14/data_0_offset_0.avro`
{% endhint %}

## Prerequisites

* **A declared connection to your warehouse**, with the output dataset or schema the platform writes the replicated tables into, and rights on it: `bigquery.dataEditor` on the BigQuery dataset, `OWNERSHIP` on the Snowflake schema. See [Warehouse compatibilities](/setup-and-monitor/collect-and-manage-your-data/sync-with-your-data-warehouse/warehouse-compatibilities.md) and [Data warehouse sync](/setup-and-monitor/collect-and-manage-your-data/sync-with-your-data-warehouse/data-warehouse-sync.md).
* **An object storage bucket** for the files, of a type your warehouse supports.
* **The datamart replication module activated** on your organisation.

## Set it up

The order matters, and it differs per warehouse and per bucket type.

{% tabs %}
{% tab title="BigQuery" %}
{% stepper %}
{% step %}

### Grant the service account rights on the bucket

The service account created for the mediarithmics to BigQuery connection needs `storage.objectAdmin` and `storage.viewer` on the bucket. In the Cloud Storage interface, open the bucket, then **Permissions > View by principals > Grant access**, enter the service account address and select both roles.

Performing this yourself requires the permissions included in `storage.admin`. Google documents the mechanics in [Set and manage IAM policies on buckets](https://docs.cloud.google.com/storage/docs/access-control/using-iam-permissions).

The same service account can query the datasets and hold the rights on the bucket, which is the simpler setup. To separate the two, create a second service account to own the editing rights on the bucket and give the first one viewing rights.
{% endstep %}

{% step %}

### Create an HMAC key pair

In **Cloud Storage > Settings > Interoperability**, under access keys for service accounts, choose **Create a key for another service account** and select the service account. Keep the request endpoint as it is.

The secret key is shown once, at creation. Keep both keys: you need them in the next step. See [Manage HMAC keys for service accounts](https://docs.cloud.google.com/storage/docs/authentication/managing-hmackeys).
{% endstep %}

{% step %}

### Configure the replication

In the Computing console, open **Data stream** and create a new replication, choosing the data warehouse destination, the dataset with editing rights, the bucket, and the HMAC key pair from the previous step.
{% endstep %}

{% step %}

### Activate it and load history

Activate the replication, then trigger an initial synchronisation to bring your existing documents across. Both are described in [Datamart replication](/setup-and-monitor/collect-and-manage-your-data/export-your-data/datamart-replication.md#initial-synchronisation).
{% endstep %}
{% endstepper %}
{% endtab %}

{% tab title="Snowflake, GCS bucket" %}
This setup involves three identities: a Snowflake service account managing the connection with mediarithmics, a Google Cloud service account owning the HMAC key and holding editing rights on the bucket, and a second Google Cloud service account representing the Snowflake-to-bucket integration.

The Snowflake side follows Snowflake's own procedure, [Configure an integration for Google Cloud Storage](https://docs.snowflake.com/en/user-guide/data-load-gcs-config).

{% stepper %}
{% step %}

### Grant a service account rights on the bucket

Create a service account and grant it `storage.objectAdmin` and `storage.viewer` on the bucket, from **Permissions > View by principals > Grant access** on the bucket. This requires the permissions included in `storage.admin`.
{% endstep %}

{% step %}

### Create an HMAC key pair

In **Cloud Storage > Settings > Interoperability**, create a key for the service account from the previous step, and keep the endpoint as it is. The secret key is shown once.
{% endstep %}

{% step %}

### Configure the replication

In the Computing console, open **Data stream** and create the replication with the Snowflake destination, the schema with editing rights, the bucket, and the HMAC key pair.
{% endstep %}

{% step %}

### Set up the Snowflake environment with the generated script

Once the replication exists, the platform generates a script for your environment, matching the documents you selected:

```
GET https://api.mediarithmics.com/v1/datamarts/:datamartId/replications/:replicationId/initial_config_script
```

It creates the storage integration that authorises Snowflake to read the bucket, the external stage pointing at it, and the log and state tables for each replicated document type.

{% hint style="danger" %}
Run the script with the role that holds the `OWNERSHIP` privilege on the schema the data is replicated into.
{% endhint %}

Its last statement describes the storage integration. Save the `STORAGE_GCP_SERVICE_ACCOUNT` it returns: that is the identity to authorise in the next step.
{% endstep %}

{% step %}

### Let the Snowflake integration read the bucket

In the Google Cloud console, [create a custom role](https://docs.cloud.google.com/iam/docs/creating-custom-roles) carrying `storage.buckets.get`, `storage.objects.create`, `storage.objects.delete`, `storage.objects.get` and `storage.objects.list`. Then, on the bucket, under **Permissions > View by principals**, add the `STORAGE_GCP_SERVICE_ACCOUNT` from the previous step and assign it that role.
{% endstep %}

{% step %}

### Confirm the setup, then activate

Tell the platform the environment is ready:

```json
PUT https://api.mediarithmics.com/v1/datamarts/:datamartId/replications/:replicationId
{
  "initial_config_status": "COMPLETED",
  "type": "DATA_WAREHOUSE"
}
```

Then activate the replication and trigger an initial synchronisation. See [Datamart replication](/setup-and-monitor/collect-and-manage-your-data/export-your-data/datamart-replication.md#initial-synchronisation).
{% endstep %}
{% endstepper %}
{% endtab %}

{% tab title="Snowflake, S3 bucket" %}
Same three identities as the GCS setup, on AWS: a Snowflake service account for the connection with mediarithmics, an AWS identity owning the access key and writing the files, and a second AWS identity representing the Snowflake-to-bucket integration.

The Snowflake side follows Snowflake's own procedure, [Configure a Snowflake storage integration to access Amazon S3](https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration).

{% hint style="warning" %}
Before you start, check that the **Security Token Service** is active in the region your account is in, under **IAM > Account settings > Security Token Service > Endpoints**.
{% endhint %}

{% stepper %}
{% step %}

### Create the IAM policy and the user that writes the files

The policy needs `s3:GetBucketLocation`, `s3:GetObject`, `s3:GetObjectVersion` and `s3:ListBucket` on the bucket. In **IAM > Policies > Create Policy**, select JSON and adapt:

```json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:DeleteObject"
      ],
      "Resource": "arn:aws:s3:::BUCKET_NAME/*"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": "arn:aws:s3:::BUCKET_NAME",
      "Condition": {
        "StringLike": {
          "s3:prefix": [
            "*"
          ]
        }
      }
    }
  ]
}
```

Then create an IAM user and attach that policy to it directly. AWS documents the editor itself in [Create IAM policies (console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create-console.html).
{% endstep %}

{% step %}

### Create an access key

Create an access key for that user and keep both parts: you need them when configuring the replication.
{% endstep %}

{% step %}

### Create the role the Snowflake integration will use

The integration assumes a role in your account to read the bucket. Create it, then come back to it once the generated script has told you which Snowflake identity to trust.
{% endstep %}

{% step %}

### Configure the replication

In the Computing console, open **Data stream** and create the replication with the Snowflake destination, the schema with editing rights, the bucket, and the access key.
{% endstep %}

{% step %}

### Set up the Snowflake environment with the generated script

Retrieve the generated script and run it, exactly as in the GCS setup:

```
GET https://api.mediarithmics.com/v1/datamarts/:datamartId/replications/:replicationId/initial_config_script
```

{% hint style="danger" %}
Run it with the role that holds the `OWNERSHIP` privilege on the destination schema.
{% endhint %}
{% endstep %}

{% step %}

### Let the Snowflake integration read the bucket, then confirm and activate

Grant the integration access to the bucket, then confirm the setup and activate the replication:

```json
PUT https://api.mediarithmics.com/v1/datamarts/:datamartId/replications/:replicationId
{
  "initial_config_status": "COMPLETED",
  "type": "DATA_WAREHOUSE"
}
```

See [Datamart replication](/setup-and-monitor/collect-and-manage-your-data/export-your-data/datamart-replication.md#initial-synchronisation) for activation and the initial synchronisation.
{% endstep %}
{% endstepper %}
{% endtab %}
{% endtabs %}

{% hint style="warning" %}
An initial synchronisation can produce a very large volume of operations, and processing them costs money in your own cloud. Size it before you trigger one.
{% endhint %}

## This is not the same as warehouse sync

Three features move data between the platform and a warehouse, in different directions. Keeping the vocabulary straight saves a lot of confusion:

| Feature                                                                                                                                                                 | Direction                                                                                                                                      |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |
| [Data warehouse sync](/setup-and-monitor/collect-and-manage-your-data/sync-with-your-data-warehouse/data-warehouse-sync.md)                                             | **Warehouse to datamart.** The platform reads your warehouse tables.                                                                           |
| **Data warehouse export**                                                                                                                                               | **Datamart to warehouse.** The platform replicates its documents into your warehouse.                                                          |
| [Replicate live tracking to your warehouse](/setup-and-monitor/collect-and-manage-your-data/sync-with-your-data-warehouse/replicate-live-tracking-to-your-warehouse.md) | **Collection into the warehouse.** Live activity comes to rest in your warehouse instead of the datamart, in a Hybrid or Zero-copy deployment. |

## Other ways to reach a warehouse

Two lighter routes exist, and they remain the right answer in some cases:

* **A** [**file destination**](/setup-and-monitor/collect-and-manage-your-data/export-your-data/file-destination.md) targeting the storage your warehouse already reads, loaded by your own process. Appropriate when a feed already produces the content you need, or when you want control over the file format.
* **A** [**query export**](/setup-and-monitor/collect-and-manage-your-data/export-your-data/export.md) for a one-off extract, loaded by hand. Appropriate for an analysis or a migration, not for a recurring pipeline.

## Verify

* The replication status is **ACTIVE**, and files appear in the bucket under the expected `type=` folders.
* Each document type you selected has a log table and a state table in the warehouse.
* A row you can identify in the datamart is present in the state table after the daily job has run.
* Counts in the state tables are the order of magnitude you expect, and `DELETE` operations are reflected.

## Troubleshooting

| Symptom                                        | Likely cause                                                                                           |
| ---------------------------------------------- | ------------------------------------------------------------------------------------------------------ |
| The replication cannot be activated            | Credentials. Check the bucket key pair and the warehouse connection first.                             |
| Files arrive in the bucket, tables stay empty  | The Snowflake environment script has not been run, or the storage integration cannot read the bucket.  |
| State tables lag behind the log tables         | Expected between daily job runs. The state tables are rebuilt once a day.                              |
| Log tables lag behind the bucket, on Snowflake | Expected. They are populated by the daily job. Ask your account manager about more frequent refreshes. |

## Related pages

<table data-view="cards" data-search="false"><thead><tr><th></th><th></th><th data-hidden data-card-target data-type="content-ref"></th></tr></thead><tbody><tr><td><strong>Datamart replication</strong></td><td>The replication object, its statuses, and the initial synchronisation.</td><td><a href="/setup-and-monitor/collect-and-manage-your-data/export-your-data/datamart-replication.md">Datamart replication</a></td></tr><tr><td><strong>File destination</strong></td><td>Paths, macros, compression, and upload triggers.</td><td><a href="/setup-and-monitor/collect-and-manage-your-data/export-your-data/file-destination.md">File destination</a></td></tr><tr><td><strong>Sync with your data warehouse</strong></td><td>The opposite direction, and the warehouse connection itself.</td><td><a href="/setup-and-monitor/collect-and-manage-your-data/sync-with-your-data-warehouse.md">Sync with your data warehouse</a></td></tr></tbody></table>


---

# 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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.mediarithmics.io/setup-and-monitor/collect-and-manage-your-data/export-your-data/data-warehouse-export.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.