FAQ

This article covers common questions when using Omnata.

Inbound syncs

How do I grant inbound table access to other roles?

If you would like to grant access to this table to other roles, you grant the OMNATA_ADMINISTRATOR role to the other role. Run the command:

grant application role OMNATA_SYNC_ENGINE.OMNATA_ADMINISTRATOR to role MY_OTHER_ROLE

How do I customize the inbound table and view names

Omnata uses jinja to give you the flexibility to set custom names. See the custom naming section of inbound storage location article.

How can I store the inbound data in my own database?

See the section inbound storage location for privilege strategies and example grant statements.

Can I materialize my inbound views?

We are eager to provide a simple solution to this built into the product, however we are treading carefully as we don't customers to experience any bill shock due to excess view materialization compute.

For the time being, here is a solution you can use.

This will not work for views where values update dynamically within Snowflake based on the current date or time, such as when our Salesforce plugin has formula field support enabled and a formula includes today()

These instructions assume a database named OMNATA_LANDING and schemas named INBOUND_RAW and INBOUND_NORMALIZED, however you can name them whatever you like.

Step 1: Create a separate database to store the inbound tables and views.

create database OMNATA_LANDING;
create schema INBOUND_RAW with managed access;
create schema INBOUND_NORMALIZED with managed access;
create database role OMNATA_LANDING.OMNATA_ROLE;
grant ownership on schema OMNATA_LANDING.INBOUND_RAW to database role OMNATA_LANDING.OMNATA_ROLE;
grant ownership on schema OMNATA_LANDING.INBOUND_NORMALIZED to database role OMNATA_LANDING.OMNATA_ROLE;
grant ownership on future tables in schema OMNATA_LANDING.INBOUND_RAW
to database role OMNATA_LANDING.OMNATA_ROLE;
grant ownership on future views in schema OMNATA_LANDING.INBOUND_NORMALIZED
to database role OMNATA_LANDING.OMNATA_ROLE;
grant database role OMNATA_LANDING.OMNATA_ROLE to application OMNATA_SYNC_ENGINE;
-- current Snowflake role (whichever it is) needs the database role
grant database role OMNATA_LANDING.OMNATA_ROLE to role ACCOUNTADMIN;

Step 2: Update the sync's storage location

Under the DATA tab, Inbound storage location, click the MODIFY button.

Override at the sync level, and toggle on Enable change tracking: then scroll down and update the Databases and schemas to point to the new location:

image.png

after that's done, back on the DATA table check that the new locations look correct, and also click "Recreate all views":

image.png

Step 3: Create dynamic tables

First, check that the raw tables and views are in their new locations:

show tables in schema OMNATA_LANDING.INBOUND_RAW;
show views in schema OMNATA_LANDING.INBOUND_NORMALIZED;

Then, create another schema to hold the dynamic tables:

create schema OMNATA_LANDING.INBOUND_DYNAMIC_TABLES with managed access;
grant ownership on schema OMNATA_LANDING.INBOUND_DYNAMIC_TABLES to database role OMNATA_LANDING.OMNATA_ROLE;
grant ownership on future tables in schema OMNATA_LANDING.INBOUND_DYNAMIC_TABLES
to database role OMNATA_LANDING.OMNATA_ROLE;

Now for each view, you can create a simple dynamic table over the top of it. Initially we will just check for updates every minute, but I can also give you instructions on how to update them on demand whenever a sync run finishes.

create dynamic table OMNATA_LANDING.INBOUND_DYNAMIC_TABLES."my-salesforce-org-to-snowflake_main_Account"
target_lag = '1 minute'
warehouse = COMPUTE_WH
refresh_mode = auto
initialize = on_create
as (
    select *
    from OMNATA_LANDING.INBOUND_NORMALIZED."my-salesforce-org-to-snowflake_main_Account"
);

After new data arrives, Snowflake should re-materialize just the new/changed rows, which is much more cost effective than a materialized view (which re-materializes the whole table each time).

Step 4: Monitor cost

Set yourself a calendar appointment for a few day's time, maybe a week. The reminder is to go back and read this doc to check that the warehouse credit usage meets your expectations.

Outbound syncs

There are records in the source table, why does the sync engine not find any?

You may have a row access policy in place. If so, your policy will need to return true when CURRENT_ROLE() = 'OMNATA_SYNC_ENGINE'

How long does the sync engine keep a history of record changes for?

Omnata keeps a full record history of sync activity inside the app's internal tables. This is to enable record-level troubleshooting for syncs and runs. For large sync payloads, this duplicate copy of records can create a storage overhead and can increase sync post-processing times.

You can customize the retention period in days under Settings > Data Management > Record History Retention. In the case of long running sync runs, setting this parameter to Zero can cut overall sync run times by ~30%, however, you will only be able to view aggregated historical metrics for sync runs going forward.

How do I adjust the sync run timeout?

If a sync or an object in a sync fails due to timeout, it can be the result of two possible timeout settings.

1. The timeout parameter on the individual sync. This is configured in the Deployment step of the sync creation wizard after setting the sync schedule; the default is 240min.

2. The STATEMENT_TIMEOUT_IN_SECONDS setting for the Snowflake account. This places an upper limit on the time a query can run for. The default is 2-days, but if this has been lowered, it will override the setting in the Omnata Sync application.