Inbound
Overall process
Inbound syncing involves less configuration complexity than outbound, since you don't need to define exactly how the data is received by the app. Instead it's pretty simple - you get a Snowflake table per object from the app. For this reason, and to avoid you needing to configure lots of syncs, each sync can support any syncing number of inbound objects from the app.
In keeping with established industry naming conventions, each object within an inbound sync is called a "stream", and each one will have a table per branch of the sync (and the "main" branch).
Inbound Sync Strategies
The Sync Strategy determines how the plugin retrieves data from the source:
Full - All data is reloaded every sync
Incremental - Using the current sync state, only new changes are pulled
By default, Omnata will automatically fetch data incrementally when possible, reverting to full-refresh if not supported by the source. You can override this behaviour and even choose the strategy on a per-stream basis.
Inbound Storage Behaviours
The Storage Behaviour determines how the sync engine applies the records to the final tables:
Merge - The new records are matched up to existing records with the same primary key, or inserted if there is no match. In this mode, if the sync strategy is Full, any records in the final table which are no longer present will be automatically marked as deleted.
Append - The new records are added to the existing set of records. Note that this will result in multiple records with the same primary key.
Note that when a stream is marked for full refresh (either manually in the UI, or via MARK_STREAMS_FOR_FULL_REFRESH), there is an option to truncate the table.
Inbound storage location
A local table is nominated and created automatically, with all of the available fields for the selected app object. Raw data is landed in the INBOUND_RAW schema and a normalized view is automatically created for most sources in the INBOUND_NORMALIZED schema.
Storage location settings
The storage location and naming syntax has defaults that can be customized at two levels.
Standard; lands the data in the OMNATA_SYNC_ENGINE app database with a preset naming syntax.
Custom default; you set a custom default for all syncs that are created (Settings > Data Management).
Override at sync level; you set location specifically for one sync (and its branches).
Customizing naming syntax
When customizing the storage locations, you are defining templates using the Jinja language. This ensures that as tables and views need creating for different branches, streams and even columns, that they are all written to a unique location.
When creating templates for database, schema and table/view names, the following Jinja variables are available:
sync_slug: The slug of the sync, visible on the sync home page. As this is a unique identifier, it makes the name unique across different syncs.
branch_name: The name of the branch, this makes the name unique across different branches within a sync. If branching mode is not enabled, this will resolve to 'main'
stream_name: The name of the stream, as provided by the plugin.
When creating templates for column names, the following Jinja variables are available:
column_name: The name of the column as provided by the plugin (usually the field name as represented in the source app). Note that these can change over time as the source data changes, and sometimes these may represent drill-down into nested objects.
Column name templates only ever apply to normalized views. Raw tables always have a single object per inbound record, stored in an object column named RECORD_DATA.
You can use any of the standard Jinja2 filters to modify values. E.g. {{stream_name|upper}} would convert the stream name to uppercase.
In addition to these, we provide the following custom filters:
fivetran_word_division: Divides alphanumeric strings containing letters and numbers into multiple words and numbers joined with underscores, as described in the Fivetran docs. We include this for naming compatibility for customers migrating from Fivetran.
multiple_underscores_to_single: Replaces two or more consecutive underscores with a single underscore
prepend_starting_number_with_underscore: If the text starts with a number, prepend it with an underscore
These can be changed together, e.g. {{column_name|upper|multiple_underscores_to_single}}
Do not include double quotes in these templates - these are added automatically by the sync engine as needed.
Modifying the storage location
There are a number of reasons to configure a custom location over the default location inside the app database. You may land data in accordance with your exsiting account and pipeline structures. In addition, Snowflake applications databases also have feature limitations compared to regular databases; such as Hybrid tables, changing tracking and others.
When setting a custom storage location, you have multiple strategies in granting the Omnata application ownership to operate on the database, schema and tables. It is recommended to give the Omnata app ownership privileges as it can dynamically create artefacts. For example, if Omnata doesn't have ownership of the views, it cannot automatically update the normalized views when there are schema changes.
Permission Strategies
Strategy 1: Applications owns whole database
Application owns databases, schemas, tables, and views.
The application will create the databases and schemas within them, and grant back USAGE privileges to the application role OMNATA_ADMINISTRATOR with grant option, so that the Omnata administrator can in turn grant this privilege to other roles.
The SELECT privilege will be granted on tables and views in the same manner.
-- Grant application full control to create and manage everything
GRANT CREATE DATABASE ON ACCOUNT TO APPLICATION OMNATA_SYNC_ENGINE;Strategy 2: Application owns schema
You own databases, application owns schemas, tables, and views.
The application will create the schemas within the databases, and grant back USAGE privileges to the application role OMNATA_ADMINISTRATOR with grant option, so that the Omnata administrator can in turn grant this privilege to other roles.
The SELECT privilege will be granted on tables and views in the same manner.
-- You create the database(s)
CREATE DATABASE MY_DB;
-- 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 MY_DB TO ROLE <role>;
GRANT SELECT ON FUTURE TABLES IN DATABASE MY_DB TO ROLE <role>;
GRANT SELECT ON FUTURE VIEWS IN DATABASE MY_DB TO ROLE <role>;Strategy 3: Managed access schema
If you need ownership of the tables and views that the sync engine creates, then this must be performed via a database role.
This is necessary because it's the only way that ownership can be "shared" with an application, and the Sync Engine must maintain ownership of the normalized views so that they can evolve over time (view definitions cannot be alter 'd after creation).
-- You create the database and schemas.
-- You may name these however you like, these are just examples.
CREATE DATABASE MY_DB;
CREATE SCHEMA MY_DB.INBOUND_RAW WITH MANAGED ACCESS;
CREATE SCHEMA MY_DB.INBOUND_NORMALIZED WITH MANAGED ACCESS;
-- You create a database role for sharing ownership
CREATE DATABASE ROLE MY_DB.OMNATA_ROLE;
-- Transfer ownership of schemas to the database role
GRANT OWNERSHIP ON SCHEMA MY_DB.INBOUND_RAW TO DATABASE ROLE MY_DB.OMNATA_ROLE;
GRANT OWNERSHIP ON SCHEMA MY_DB.INBOUND_NORMALIZED TO DATABASE ROLE MY_DB.OMNATA_ROLE;
-- Ensure that the database role owns all tables and views created in these schemas
grant ownership on future tables in schema MY_DB.INBOUND_RAW
to database role MY_DB.OMNATA_ROLE;
grant ownership on future views in schema MY_DB.INBOUND_NORMALIZED
to database role MY_DB.OMNATA_ROLE;
-- Grant the database role to the sync engine application
grant database role MY_DB.OMNATA_ROLE to application OMNATA_SYNC_ENGINE;Last updated