Managed data schema

Version notice

Scheduled Journeys is available in Accelerator version 26.2.3. Contact your Customer Success Manager (CSM) for more information.

info

Managed Data Schema configuration requires the System Admin role and write-level access to your data warehouse. Contact your Customer Success Manager (CSM) for more information.

Overview

Managed Data Schema is designed to read from and write to a designated schema in your data warehouse. For journeys, Managed Data Schema is used to provision and maintain Journey State Tables — the authoritative record of every user currently in a journey, including their tile position, wait timing, re-entry history, and evaluation bookkeeping data.

Because the Journey State Table lives in your warehouse, your data never leaves your environment. MessageGears writes state into the schema you designate and reads it back during each evaluation cycle.

tip

Managed Data Schema configuration must be completed before User Profiles can be created and before any journey can be activated.

Supported warehouse types

Managed Data Schema supports the following native warehouse connection types for journey state tracking:

• Snowflake
• Google BigQuery
• Amazon Redshift
• Databricks

JDBC connections are not supported. If your Accelerator instance uses JDBC-based connections, contact your CSM before proceeding.

What Managed Data Schema provisions

When you configure Managed Data Schema for journeys, Accelerator creates and manages the following in the designated schema:

Object	Description
Journey State Tables	Track current journey members, their tile position, wait timing, entry timestamp, and re-entry history for each journey

MessageGears handles table creation, schema evolution, and ongoing writes to this table. You do not need to create the table manually — you only need to ensure the schema exists and that the database user has the permissions described below.

What resources do you need?

Resources	Location	Tasks
Database administrator (DBA)	Your data warehouse	Create the schema and grant permissions
System Admin	Accelerator instance	Configure Managed Data Schema in Admin menu

Prerequisites

Before configuring Managed Data Schema, confirm the following:

Accelerator database connection

• A native data warehouse connection is established in Accelerator (Admin → Data → Database Connections) with the purpose tag of Managed data schema.

In your data warehouse

• A schema called MG_MANAGED exists in your warehouse to designate for MessageGears writes — MG_MANAGED schema must exist before further setup; Accelerator does not create the schema itself
• A database user has been created in your warehouse with the permissions listed in the next section

tip

To create a new database connection or update your existing connection to include the Managed data schema purpose, see the Adding and managing database connections guide.

Note that creating a new connection may require additional steps, such as creating or updating Stage and File storage configurations.

Required permissions in the data warehouse

These user, role, or IAM permissions must be applied to the schema or dataset before you complete the Managed Data Schema configuration in Accelerator. The connection test in the setup flow validates that the required permissions are in place.

Snowflake

The following instructions are a template for creating and assigning a least-privilege role to a service account user. This role is designed to have the necessary permissions to manage the MessageGears data schema within your organization's Snowflake environment. Please use these guidelines as a reference when configuring access.

--  Your organization may have different policies or requirements for creating and 
--  managing privileged users. Please use these as a guide.

-- Create Role
CREATE ROLE <role_name>;

-- Grant warehouse
GRANT USAGE ON WAREHOUSE <client_warehouse_name> to ROLE <role_name>;

-- Grant DB usage
GRANT USAGE ON DATABASE <client_DB> TO ROLE <role_name>;

-- Grant Schema usage
GRANT USAGE ON SCHEMA <client_DB>.MG_MANAGED TO ROLE <role_name>;

-- Grant all rights include create table, etc under that schema
GRANT CREATE TABLE ON SCHEMA <client_DB>.MG_MANAGED TO ROLE <role_name>;
GRANT ALL ON ALL TABLES IN SCHEMA <client_DB>.MG_MANAGED TO ROLE <role_name>;
GRANT ALL ON FUTURE TABLES IN SCHEMA <client_DB>.MG_MANAGED TO ROLE <role_name>;

-- Admin will create db user, then assign user to Role
GRANT <user_name> TO ROLE <role_name>;

BigQuery

The following instructions are a template for creating a dataset and granting the necessary IAM permissions to a service account. This configuration is designed to provide the minimum required access to manage the MessageGears data schema within your organization's BigQuery environment. Please use these guidelines as a reference when configuring access.

# Your organization may have different policies or requirements for creating and 
# managing privileged users. Please use these as a guide.

# Create dataset for MessageGears managed data
CREATE SCHEMA IF NOT EXISTS `<project_id>.MG_MANAGED`;

# Grant jobUser (equivalent to warehouse usage: run queries)
gcloud projects add-iam-policy-binding <project_id> \
  --member="serviceAccount:<service_account_email>" \
  --role="roles/bigquery.jobUser"

# Grant dataset editor (usage, create table, read/write, future tables)
bq update --dataset \
  --add_iam_member=serviceAccount:<service_account_email>:roles/bigquery.dataEditor \
  <project_id>:MG_MANAGED

# Grant dataset viewer role if you only want read access
bq update --dataset \
  --add_iam_member=serviceAccount:<service_account_email>:roles/bigquery.dataViewer \
  <project_id>:MG_MANAGED

# Grant user directly (instead of service account)
gcloud projects add-iam-policy-binding <project_id> \
  --member="user:<user_email>" \
  --role="roles/bigquery.dataEditor"

Redshift

The following instructions are a template for creating a role and assigning least-privilege permissions to a database user. This configuration is designed to provide the minimum required access to manage the MessageGears data schema within your organization's Redshift environment. Please use these guidelines as a reference when configuring access.

--  Your organization may have different policies or requirements for creating and
--  managing privileged users. Please use these as a guide.
--  Note: Older Redshift clusters may require using Groups instead of Roles.

-- Create Role
CREATE ROLE <role_name>;

-- Create User
CREATE USER <user_name> PASSWORD '<password>';

-- Assign Role to User
GRANT ROLE <role_name> TO <user_name>;

-- Grant Schema usage
GRANT USAGE ON SCHEMA MG_MANAGED TO ROLE <role_name>;

-- Grant Create Table permission
GRANT CREATE ON SCHEMA MG_MANAGED TO ROLE <role_name>;

-- Grant all rights on existing tables in schema
GRANT ALL ON ALL TABLES IN SCHEMA MG_MANAGED TO ROLE <role_name>;

-- Grant default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA MG_MANAGED
GRANT ALL ON TABLES TO ROLE <role_name>;

Databricks

The following instructions are a template for creating a schema and granting least-privilege permissions to a principal. This configuration is designed to provide the minimum required access to manage the MessageGears data schema within your organization's Databricks environment. Please use these guidelines as a reference when configuring access.

--  Your organization may have different policies or requirements for creating and
--  managing privileged users. Please use these as a guide.

-- Grant warehouse usage
GRANT USAGE ON WAREHOUSE <client_warehouse_name> TO '<role_name>';

-- Grant catalog usage
GRANT USE CATALOG ON CATALOG <client_catalog> TO '<role_name>';

-- Grant schema usage
GRANT USE SCHEMA ON SCHEMA <client_catalog>.MG_MANAGED TO '<role_name>';

-- Allow table creation
GRANT CREATE TABLE ON SCHEMA <client_catalog>.MG_MANAGED TO '<role_name>';

-- Grant permissions on existing tables
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA <client_catalog>.MG_MANAGED
TO '<role_name>';

-- Grant permissions on future tables
GRANT SELECT, INSERT, UPDATE, DELETE
ON FUTURE TABLES IN SCHEMA <client_catalog>.MG_MANAGED
TO '<role_name>';

Configure Managed Data Schema in Accelerator

Step 1: Navigate to Managed Data Schema

In the MessageGears navigation, select Admin → Data → Managed Data Schema.

Step 2: Select your database type

1. Click Configure managed data schema

Configure Managed data schema page

2. Select the Database type. Sample template instructions will display for configuring the appropriate role, service account, and/or IAM settings in the data warehouse for MG_MANAGED schema. Click Continue.

Select database type from the available data warehouses

Step 3: Select your database connection

In the Database connection dropdown, select the native warehouse connection you want to use for journey state tracking

tip

Only native database connections with a tagged Purpose of Managed data schema appear. If your connection does not appear, confirm it is configured as a native connection type and tagged with the Managed data schema Purpose in Admin → Data → Database Connections.

Use the same database connection

Managed Data Schema needs to use the same database connection as your Audiences and User Profiles for Journeys.

Select database connection from the dropdown

Step 4: Test and save the connection

1. Click Test connection

Accelerator validates that the database user associated with the selected connection has the required permissions on the selected MG_MANAGED schema. If the test fails, review the permissions listed in the previous section and retry.

Test connection success message

Test connection failure message

2. Click Save

Accelerator saves the Managed Data Schema configuration. MessageGears will automatically create Journey State Tables in the designated schema the first time a journey activates and runs its first evaluation cycle.

Next steps

With Managed Data Schema configured, you can proceed to the next prerequisite:

• Create a user profile

For a full overview of all prerequisites, see Journeys prerequisites.

Related articles

• About journeys
• Journeys prerequisites
• User profiles