For the complete documentation index, see llms.txt. This page is also available as Markdown.

Data warehouse replication

Overview

A Data Warehouse replication continuously delivers the data from mediarithmics into your own data warehouse, through an intermediate cloud storage bucket.

Data flow

mediarithmics  ─►  cloud storage bucket (Avro files)  ─►  your warehouse (tables)
  1. mediarithmics → bucket — replicated operations are written as Avro files into a cloud storage bucket

  2. bucket → warehouse — those files are exposed as tables in your warehouse, per document type

The Avro files are loaded every 15 minutes in the bucket. Each document type has a log table (all operations, append-only) and a state table (the current, deduplicated view). A daily job rebuilds the state tables from the logs.

Supported buckets per destination

Warehouse destination
Supported bucket

BigQuery

Google Cloud Storage (GCS)

Snowflake

Google Cloud Storage (GCS) or Amazon S3

You provide the bucket credentials (GCS HMAC key or S3 access key) when setting up the replication.

For more information on how to set up a connection to you data warehouse in mediarithmics see Data warehouse management

Avro files description

This section describes the Avro files that a DATA_WAREHOUSE datamart replication writes into the intermediate cloud storage bucket, and gives the exact record schema for each document type that can be replicated.

Every file is a standard Avro Object Container File (.avro) with one record type per file — a file only ever contains one …OperationRecord type

File naming and partitioning

Files are laid out in a Hive-style partitioned directory tree. One top-level folder per document type, then partitioned by hour:

For example:

Document types and their records

Each document you select in the replication filters maps to exactly one Avro record type, i.e. one type=… folder / one warehouse table.

USER_SEGMENT

UserSegmentOperationRecord

USER_DEVICE_POINT

UserDevicePointOperationRecord

USER_DEVICE_TECHNICAL_ID

UserDeviceTechnicalIdOperationRecord

USER_ACCOUNT

UserAccountOperationRecord

USER_EMAIL

UserEmailOperationRecord

USER_PROFILE

UserProfileOperationRecord

USER_ACTIVITY

UserActivityOperationRecord

USER_EVENT

UserEventOperationRecord

USER_COMPUTED_FIELD

UserComputedFieldOperationRecord

Record schemas

There is one Avro record per document type. Each .avro file embeds the schema of its own record. All records share the same three leading fields — ts (timestamp-micros), op (OperationType enum: UPDATE / DELETE) and user_point_id (uuid).

In these schemas data is a JSON string and user_identifiers is an array — this is the raw Avro shape written on the bucket (the conversion to VARIANT / JSON happens later, in the state tables).

UserSegmentOperationRecord

UserDevicePointOperationRecord

UserDeviceTechnicalIdOperationRecord

UserAccountOperationRecord

UserEmailOperationRecord

UserProfileOperationRecord

UserActivityOperationRecord

UserEventOperationRecord

UserComputedFieldOperationRecord

Log & state table schemas

For each document type, the warehouse holds a log table (<document>_log, all operations) and a state table (<document>, deduplicated current state). Each column's type is given for both Snowflake and BigQuery.

On Snowflake, every log table also has these extra columns: partition_date DATE, year, month, day, hour (NUMBER). On BigQuery the log table is an external table over the Avro files (schema auto-detected). State tables add partition_date DATE on Snowflake only.

Avro → warehouse type mapping

Avro type
Snowflake
BigQuery

long + timestamp-micros

TIMESTAMP_NTZ

TIMESTAMP

string (incl. uuid)

STRING

STRING

enum

STRING

STRING

long

NUMBER

INT64

int

INT

INT64

boolean

BOOLEAN

BOOL

array

ARRAY

RECORD REPEATED

record

OBJECT

RECORD

In state tables, long columns are BIGINT (Snowflake) / INT64 (BigQuery), and JSON payloads (data, user_identifiers) are stored as VARIANT (Snowflake) / JSON (BigQuery).

user_segment

Log (user_segment_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

segment_id

NUMBER

INT64

creation_ts

TIMESTAMP_NTZ

TIMESTAMP

expiration_ts

TIMESTAMP_NTZ

TIMESTAMP

last_modified_ts

TIMESTAMP_NTZ

TIMESTAMP

State (user_segment) — cluster by segment_id

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

segment_id

BIGINT

INT64

ts

TIMESTAMP

TIMESTAMP


user_device_point

Log (user_device_point_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

user_device_point_id

NUMBER

INT64

creation_ts

TIMESTAMP_NTZ

TIMESTAMP

form_factor

STRING

STRING

os_family

STRING

STRING

os_version

STRING

STRING

browser_family

STRING

STRING

brand

STRING

STRING

model

STRING

STRING

carrier

STRING

STRING

agent_type

STRING

STRING

State (user_device_point) — no clustering

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

user_device_point_id

BIGINT

INT64

brand

STRING

STRING

model

STRING

STRING

agent_type

STRING

STRING

browser_family

STRING

STRING

form_factor

STRING

STRING

os_family

STRING

STRING

ts

TIMESTAMP

TIMESTAMP


user_device_technical_id

Log (user_device_technical_id_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

user_device_point_id

NUMBER

INT64

expiration_ts

TIMESTAMP_NTZ

TIMESTAMP

registry_type

STRING

STRING

registry_id

NUMBER

INT64

user_device_technical_id_value

STRING

STRING

last_seen_ts

TIMESTAMP_NTZ

TIMESTAMP

State (user_device_technical_id) — cluster by registry_id

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

user_device_technical_id

STRING

STRING

registry_type

STRING

STRING

registry_id

BIGINT

INT64

id

STRING

STRING

user_device_point_id

BIGINT

INT64

last_seen_ts

TIMESTAMP

TIMESTAMP

expiration_ts

TIMESTAMP

TIMESTAMP

ts

TIMESTAMP

TIMESTAMP


user_account

Log (user_account_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

compartment_id

NUMBER

INT64

user_account_id

STRING

STRING

creation_ts

TIMESTAMP_NTZ

TIMESTAMP

expiration_ts

TIMESTAMP_NTZ

TIMESTAMP

State (user_account) — cluster by compartment_id

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

compartment_id

BIGINT

INT64

user_account_id

STRING

STRING

ts

TIMESTAMP

TIMESTAMP


user_email

Log (user_email_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

email_hash

STRING

STRING

email

STRING

STRING

creation_ts

TIMESTAMP_NTZ

TIMESTAMP

expiration_ts

TIMESTAMP_NTZ

TIMESTAMP

State (user_email) — no clustering

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

email_hash

STRING

STRING

email

STRING

STRING

ts

TIMESTAMP

TIMESTAMP


user_profile

Log (user_profile_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

compartment_id

NUMBER

INT64

user_account_id

STRING

STRING

creation_ts

TIMESTAMP_NTZ

TIMESTAMP

expiration_ts

TIMESTAMP_NTZ

TIMESTAMP

last_modified_ts

TIMESTAMP_NTZ

TIMESTAMP

data

STRING

STRING

State (user_profile) — cluster by compartment_id

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

compartment_id

BIGINT

INT64

user_account_id

STRING

STRING

data

VARIANT

JSON

ts

TIMESTAMP

TIMESTAMP


user_activity

Log (user_activity_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

activity_key

STRING

STRING

channel_id

STRING

STRING

schema_id

STRING

STRING

user_identifiers

ARRAY

RECORD REPEATED

expiration_ts

TIMESTAMP_NTZ

TIMESTAMP

schema_error

BOOLEAN

BOOL

data

STRING

STRING

State (user_activity) — no clustering

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

activity_key

STRING

STRING

channel_id

STRING

STRING

user_identifiers

VARIANT

JSON

expiration_ts

TIMESTAMP

TIMESTAMP

data

VARIANT

JSON

ts

TIMESTAMP

TIMESTAMP


user_event

Log (user_event_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

activity_key

STRING

STRING

event_id

STRING

STRING

channel_id

STRING

STRING

event_name

STRING

STRING

schema_id

STRING

STRING

user_identifiers

ARRAY

RECORD REPEATED

expiration_ts

TIMESTAMP_NTZ

TIMESTAMP

schema_error

BOOLEAN

BOOL

data

STRING

STRING

State (user_event) — cluster by activity_key

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

event_id

STRING

STRING

activity_key

STRING

STRING

channel_id

STRING

STRING

event_name

STRING

STRING

user_identifiers

VARIANT

JSON

expiration_ts

TIMESTAMP

TIMESTAMP

data

VARIANT

JSON

ts

TIMESTAMP

TIMESTAMP


user_computed_field

Log (user_computed_field_log)

Column
Snowflake
BigQuery

ts

TIMESTAMP_NTZ

TIMESTAMP

op

STRING

STRING

user_point_id

STRING

STRING

plugin_instance_id

NUMBER

INT64

last_modified_ts

TIMESTAMP_NTZ

TIMESTAMP

data

STRING

STRING

State (user_computed_field) — cluster by plugin_instance_id

Column
Snowflake
BigQuery

user_point_id

STRING

STRING

plugin_instance_id

BIGINT

INT64

data

VARIANT

JSON

ts

TIMESTAMP

TIMESTAMP


Setting up replication

In order to set up a replication to your data warehouse, follow these steps

Last updated

Was this helpful?