Overview

The following documents how you can use branching on an inbound sync to separate your Test and Prod data. In this example, we have test and prod Salesforce orgs, and we want to land the data from both of those in separate Test and Prod schemas.

Prerequisites:

1. Multiple environments of the source application or database (test and prod)

2. One Snowflake account (Mutli-Snowflake account branching is coming soon)

There are several config steps that are required to implement this type of branching:

1. Create multi-environment connections

2. Create the sync; branch first then test it

3. Configure the custom storage location

4. Merge the branch to main

Creating the connections

This is where you tell Omnata what type of environments you have.

In this case, we have multiple environments and so we’ll create connections to both. The Test connection will default to being on a Branch, and the Production connection will default to being on the Main.

To use branching properly you create the sync configuration on the Test branch, test it, then merge the configuration to the Main once it’s good.

Configure the sync

You start with the Test connection on the test branch. You can run a test branch on a schedule, or just fire some manual runs to check that the config is what you want.

The UI prioritises visibility of the Main branch, so the health state of the branch runs will not be shown on the sync.

Configure the custom storage location

We are going to use a custom storage location to land our test and prod data in different schemas. We’ll do this by configuring a custom location for the sync which uses {{branch_name}} in the schema.

We need to do a couple of things to set this up:

1. Configure privileges for the Omnata Sync Engine to write to an external database.

2. Modify the storage location of the sync

1. Grant privileges to the Omnata Sync Engine to own and manage the external database

There are multiple privilege management strategies provided in our docs. However, for simplicity, we recommend allowing the Omnata Sync Engine to own the schemas, then grant back usage to other roles. In all cases, the Omnata Sync Engine must own the location since it dynamically creates and updates tables/views.

-- Grant application permissions to create its own schemas
GRANT USAGE ON DATABASE MY_DB TO APPLICATION OMNATA_SYNC_ENGINE;
GRANT CREATE SCHEMA ON DATABASE MY_DB TO APPLICATION OMNATA_SYNC_ENGINE;

--(Optional) Grant access to some other role in advance
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE OMNATA_PLUGIN_TEST_DATA TO ROLE OTHER_ROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE OMNATA_PLUGIN_TEST_DATA TO ROLE OTHER_ROLE;
GRANT SELECT ON FUTURE VIEWS IN DATABASE OMNATA_PLUGIN_TEST_DATA TO ROLE OTHER_ROLE;

Modify the storage location for the sync

Your custom storage location settings will look something like this. The form accepts jinja to customize the branch names and separate by schema

We insert the {{branch_name}} variable which will send to a different location based on branch name.

Note that in Omnata the production branch is always called ‘main’ and branches default to ‘test’, although you can create branches with any name.

In this example, we want to prefix our production schema names with PROD_ and test schema gets TEST_. We are hard-coding the schema name SALESFORCE, because this is going to be the only production sync for Salesforce. However, if you plan on having multiple syncs from one source, you would want to use {{sync_name}} here and follow a sync name convention.

In the Schema fields:

// For RAW tables
{{ 'PROD' if branch_name == 'main' else branch_name | upper }}_SALESFORCE_RAW

// For Normalized Views
{{ 'PROD' if branch_name == 'main' else branch_name | upper }}_SALESFORCE_NORMALIZED

For Streams

A popular config is for each table (stream in the source) to appear exactly as in the source but upper-cased for ease of querying in Snowflake, so we apply this to the Table name fields.

{{stream_name | upper }}

Once you’ve complete this, your storage locations for the sync will look like this.

And, Omnata will go and create those locations in your chosen database. If you have already synced data, then it will move the data to the new locations.

Merge the branch to main

When using branching, you will always propose changes on the branch, then merge those settings to the main. The lifecycle of branches looks like this as you make changes.

And it looks like this in the UI.

Wrapping up

This config will enable you to run test and production syncs permanently from the one sync configuration and segregate your test and production data within the one Snowflake account.