Skip to main content

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:

ObjectDescription
Journey State TablesTrack 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. Your DBA creates the schema and grants the database user the permissions described below. You choose the schema when you configure Managed Data Schema in Accelerator.

What resources do you need?​

ResourcesLocationTasks
Database administrator (DBA)Your data warehouseCreate the schema and grant permissions
System AdminAccelerator instanceConfigure 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

  • Your DBA has created a schema in your warehouse for MessageGears writes. Accelerator does not create the schema itself. The templates in the next section include the step that creates it.
  • The Snowflake database and schema names, and the Redshift schema name, must be unquoted identifiers: they start with a letter or underscore and contain only letters, digits, underscores, and dollar signs. Accelerator lists schemas with other names, but the connection test fails for them.
  • 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 confirms that the database user can create and drop tables in the schema. For more information, see Step 4: Test and save the connection.

In each template, replace <schema_name> with the name of the schema your DBA creates for Managed Data Schema. This is the schema you select in Step 3: Select your database connection and schema.

note

These are the same template instructions Accelerator displays in Step 2: Select your database type when you select a database type.

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>;

-- Create MG Managed Schema
CREATE SCHEMA IF NOT EXISTS <client_DB>.<schema_name>;

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

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

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

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

The CREATE VIEW grant is required because the Snowflake connection test also creates and drops a test view in the schema.

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
Managed Data Schema list page with Configure Managed Data Schema button and Admin Data navigation highlighted
Configure Managed data schema page
  1. Select the Database type. Accelerator displays template instructions for configuring the role, service account, or IAM settings for the schema. These are the same templates shown in Required permissions in the data warehouse. If your DBA hasn't applied them yet, complete that step before you continue. Select Continue.
Database type options for Snowflake, BigQuery, Redshift, and Databricks, with Snowflake selected and its SQL template instructions displayed
Select database type from the available data warehouses

Step 3: Select your database connection and schema​

You select the database connection and the schema on the same screen.

  1. In the Select database connection dropdown, select the native warehouse connection you want to use for journey state tracking.
  2. In the Select schema dropdown, select the schema your DBA created for Managed Data Schema. The dropdown lists the schemas available on the selected connection's database (Snowflake and Redshift), project (BigQuery), or catalog (Databricks). No schema is selected by default.

If you change the database connection, select the schema again from the new connection's list.

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.

Snowflake configuration step with the Select database connection and Select schema dropdowns, followed by the selected connection's database type, account identifier, user ID, role, and masked private key
Select a database connection and schema

Step 4: Test and save the connection​

  1. Select Test connection.

For Snowflake, Redshift, and Databricks connections, Accelerator confirms access by creating a uniquely named test table in the selected schema and then dropping it, using the selected connection's credentials. For Snowflake, Accelerator also creates and drops a test view. Accelerator doesn't inspect roles or grants, so permissions inherited through other roles pass the test. No test objects remain in the schema after a successful test. BigQuery's connection test is unchanged in 26.4.1: it continues to check IAM permissions directly rather than creating and dropping a test table.

If the test fails, see Troubleshoot a failed connection test.

Clicking test connection returns a success message when all requirements are met.
Test connection success message
Clicking test connection returns a failure message when all requirements are not met.
Test connection failure message
  1. Select Save.

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

To change the schema of an existing Managed Data Schema configuration, edit the configuration, select a different schema, and test the connection again before you save.

Troubleshoot a failed connection test​

For Snowflake, Redshift, and Databricks connections, a failed test returns one of the following messages:

MessageCauseFix
Create/drop table access to <schema> is missing: <warehouse error>The database user can't create or drop a table in the schema, has no usage on the schema, or the schema doesn't exist. Snowflake names the schema as <database>.<schema> and Databricks as <catalog>.<schema>.Grant the permissions in the template for your warehouse, or select a schema that exists.
Create/drop view access to <database>.<schema> is missing: <warehouse error>Snowflake only. The database user can't create or drop a view in the schema.Grant CREATE VIEW on the schema.
Unsupported characters in database or schema nameSnowflake and Redshift only. The database or schema name isn't an unquoted identifier.Select a schema whose name starts with a letter or underscore and contains only letters, digits, underscores, and dollar signs.

If Accelerator creates a test object but can't drop it, the message names the leftover table or view. Test objects use the MGA_ACCESS_CHECK_ name prefix. Ask your DBA to drop the object manually, then test again.

Errors that aren't about permissions, such as a network failure or no running warehouse or compute, display as the warehouse reports them.

Next steps​

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

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