Internal Stored Procedures

APPLY_INBOUND_SYNC_SETTINGS

This stored procedure is deprecated in favour of CONFIGURE_OMNATA_INBOUND_SYNC

Applies settings to an inbound sync/branch. If a BRANCH_NAME other than 'main' is provided and the branch doesn't exist, it will be created. If MATCH_REQUIRED is true and the provided SYNC_PARAMETERS and STREAMS_CONFIGURATION don't match, then the response 'data' element 'settingsApplied' will be false, along with 'mismatchDetails' which describe why. This is used by external schedulers such as dbt to know when their configuration definition is the correct one.

Arguments

Name	Type
SYNC_ID	FLOAT
SYNC_SLUG	VARCHAR
CONNECTION_SLUG	VARCHAR
BRANCH_NAME	VARCHAR
MATCH_REQUIRED	BOOLEAN
SYNC_PARAMETERS	OBJECT
SYNC_PARAMETERS_BRANCH	OBJECT
STREAMS_CONFIGURATION	OBJECT
ACTIVATE_BRANCH	BOOLEAN
INBOUND_RECORDS_BEHAVIOUR	VARCHAR
INBOUND_STREAM_STATE_BEHAVIOUR	VARCHAR
REOPEN_BEHAVIOUR	VARCHAR
INBOUND_STORAGE_LOCATION	OBJECT

Return Type

OBJECT

APPLY_OUTBOUND_SYNC_SETTINGS

This stored procedure is deprecated in favour of CONFIGURE_OMNATA_OUTBOUND_SYNC

Applies settings to an outbound sync/branch. If a BRANCH_NAME other than 'main' is provided and the branch doesn't exist, it will be created. If MATCH_REQUIRED is true and the provided SYNC_PARAMETERS and STREAMS_CONFIGURATION don't match, then the response 'data' element 'settingsApplied' will be false, along with 'mismatchDetails' which describe why. This is used by external schedulers such as dbt to know when their configuration definition is the correct one.

Arguments

Name	Type
SYNC_ID	FLOAT
SYNC_SLUG	VARCHAR
CONNECTION_SLUG	VARCHAR
BRANCH_NAME	VARCHAR
SOURCE_DATABASE	VARCHAR
SOURCE_SCHEMA	VARCHAR
SOURCE_TABLE	VARCHAR
SOURCE_ID_COLUMN	VARCHAR
MATCH_REQUIRED	BOOLEAN
SYNC_PARAMETERS	OBJECT
SYNC_PARAMETERS_BRANCH	OBJECT
FIELD_MAPPINGS	OBJECT
ACTIVATE_BRANCH	BOOLEAN
OUTBOUND_RECORD_STATE_BEHAVIOUR	VARCHAR
REOPEN_BEHAVIOUR	VARCHAR
OUTBOUND_BRANCH_RECORD_FILTER	ARRAY

Return Type

OBJECT

CONFIGURE_OMNATA_INBOUND_SYNC

Creates or updates an inbound sync. This stored procedure is idempotent.

This procedure should be invoked using named parameters.

Arguments

Argument categories:

¹ Stable identifier - A fixed value for identifying the sync

² Change Management - Relates to managing changes between environments

³ Directly Editable - Configuration changes that can be made directly to syncs

⁴ Managed Changes - Configuration changes that are applied to a sync/branch through each step of a pipeline

⁵ Branch/Account specific changes - Configuration change variations that belong permanently to a particular account/branch

Name	Type	Description	Example(s)
SYNC_SLUG 1	VARCHAR	The human-readable identifier for the sync. Must be unique within this Sync Engine instance.	'salesforce-prod-to-snowflake'
MULTI_ACCOUNT 2 (optional)	BOOLEAN	Use this for syncs which span multiple Snowflake accounts. If this is true, then SINGLE_ACCOUNT_BRANCHING must be false	true
SINGLE_ACCOUNT_BRANCHING 2 (Optional)	BOOLEAN	Use this for a single account with branching	true
SINGLE_ACCOUNT_BRANCH_TO_CONFIGURE 2 (Optional)	VARCHAR	This parameter indicates which environment we are in, and its value needs to come from the executing process (CI tool variable, etc)	'dev' 'qa' 'main'
SYNC_NAME 3	VARCHAR	The name of the sync	'Salesforce Prod to Snowflake'
SYNC_TAGS 3 (Optional)	ARRAY	Arbitrary tags to apply to the sync	['CRM','Accounting']
SYNC_PARAMETERS 4	OBJECT	Configuration parameters for the sync. Each of these can be fully-defined parameters with values and metadata fields, or a more terse value-only form	{ 'sync_type':'watermark',   'object_type':'table' } { 'sync_type':{ 'value': 'watermark', 'metadata': {} },   'object_type':{ 'value': 'table', 'metadata': { 'abc': 123 } }
STREAM_NAMES 4	ARRAY	The names of all streams to include in the sync	['Account','Contact','Opportunity']
SYNC_STRATEGY 4	VARIANT	The sync strategy for all streams, or a mapping from stream name to sync strategy	'auto' { 'Account': 'Incremental', 'Contact': 'Full Refresh', 'Opportunity': 'Incremental' }
STORAGE_BEHAVIOUR 4	VARIANT	The storage behaviour for all streams, or a mapping from stream name to storage behaviour	'merge' { 'Account': 'merge', 'Contact': 'append', 'Opportunity': 'append' }
STREAM_PRIMARY_KEYS 4 (Optional)	OBJECT	A mapping of stream names to primary key field(s). Only required if the plugin does not provide the primary key definition.	{ 'Account': 'Id', 'Contact': ['Name','DOB'], }
STREAM_CURSOR_FIELDS 4 (Optional)	OBJECT	A mapping of stream names to cursor field. Only required if the plugin does not provide a default cursor field.	{ 'Account': 'UpdatedDate', 'Contact': 'UpdatedDate'
CONNECTION_SLUG 5	VARIANT	The connection slug for a sync or branch. Can be either a string (simple sync) or an object (multi-account or branching)	'my-connection' { 'main':'my-prod-conn', 'dev':'my-dev-account' }
TUNING_PARAMETERS 5 (Optional)	OBJECT	Tuning parameters for the sync, keyed on branch/account name. Each of these can be fully-defined parameters with values and metadata fields, or a more terse value-only form	{ 'max_records_per_file':'500', 'max_concurrent_uploads':'2' }
SYNC_SCHEDULE 5	OBJECT	The schedule, keyed on branch/account name.	{ 'main':{ 'mode': 'manual', 'warehouse': 'COMPUTE_WH', 'time_limit_mins': 240 }, 'dev':{ 'mode': 'manual', 'warehouse': 'COMPUTE_WH', 'time_limit_mins': 60 }, }
STORAGE_LOCATION 5 (Optional)	OBJECT	The location of the inbound tables, optionally keyed on account name. For single-account branches, the branch name is part of the template and therefore shares a definition with the main sync.	{ 'raw_database': 'CUSTOMER_1234567', 'raw_schema': 'RAW', 'raw_object': '{{stream_name}}', 'raw_table_type': 'standard', -- other options: 'hybrid' 'raw_table_standard_change_tracking': false, 'normalized_database':'CUSTOMER_1234567', 'normalized_schema':'NORMALIZED', 'normalized_object':'{{stream_name}}', 'normalized_column':'{{column_name | upper}}' }

Return Type

OBJECT

CONFIGURE_OMNATA_OUTBOUND_SYNC

Creates or updates an outbound sync. This stored procedure is idempotent.

This procedure should be invoked using named parameters.

Arguments

Argument categories:

¹ Stable identifier - A fixed value for identifying the sync

² Change Management - Relates to managing changes between environments

³ Directly Editable - Configuration changes that can be made directly to syncs

⁴ Managed Changes - Configuration changes that are applied to a sync/branch through each step of a pipeline

⁵ Branch/Account specific changes - Configuration change variations that belong permanently to a particular account/branch

Name	Type	Description	Example(s)
SYNC_SLUG 1	VARCHAR	The human-readable identifier for the sync. Must be unique within this Sync Engine instance.	'salesforce-prod-to-snowflake'
MULTI_ACCOUNT 2 (optional)	BOOLEAN	Use this for syncs which span multiple Snowflake accounts. If this is true, then SINGLE_ACCOUNT_BRANCHING must be false	true
SINGLE_ACCOUNT_BRANCHING 2 (Optional)	BOOLEAN	Use this for a single account with branching	true
SINGLE_ACCOUNT_BRANCH_TO_CONFIGURE 2 (Optional)	VARCHAR	This parameter indicates which environment we are in, and its value needs to come from the executing process (CI tool variable, etc)	'dev' 'qa' 'main'
SINGLE_ENVIRONMENT_BRANCHING_MODE 2 (Optional)	VARCHAR	For outbound syncs with a single environment, the branching mode to use.	off alternate_source_via_table alternate_source_via_record_filter alternate_target
SYNC_NAME 3	VARCHAR	The name of the sync	'Salesforce Prod to Snowflake'
SYNC_TAGS 3 (Optional)	ARRAY	Arbitrary tags to apply to the sync	['CRM','Accounting']
SYNC_PARAMETERS 4	OBJECT	Configuration parameters for the sync. Each of these can be fully-defined parameters with values and metadata fields, or a more terse value-only form	{ 'sync_type':'watermark',   'object_type':'table' } { 'sync_type':{ 'value': 'watermark', 'metadata': {} },   'object_type':{ 'value': 'table', 'metadata': { 'abc': 123 } }
FIELD_MAPPINGS 4	OBJECT	An object which describes how source columns are mapped to the target.	{ 'mapper_type': 'field_mapping_selector', 'field_mappings': [ { 'source_type': 'column_name', 'source_value': 'ACCOUNT_ID', 'app_field': 'AccountId__c', 'app_metadata': {} } ] } { 'mapper_type': 'jinja_template', 'jinja_template': "This is a jinja template {{ row['column_a'] }}" }
SYNC_STRATEGY 4	VARCHAR	The name of the sync strategy to use	'Upsert' 'Mirror'
CONNECTION_SLUG 5	VARIANT	The connection slug for a sync or branch. Can be either a string (simple sync) or an object (multi-account or branching)	'my-connection' { 'main':'my-prod-conn', 'dev':'my-dev-account' }
TUNING_PARAMETERS 5 (Optional)	OBJECT	Tuning parameters for the sync, keyed on branch/account name. Each of these can be fully-defined parameters with values and metadata fields, or a more terse value-only form	{ 'max_records_per_file':'500', 'max_concurrent_uploads':'2' }
SOURCE_TABLE	OBJECT	The source table for the sync, keyed on branch/account name.	{ 'main': { 'database': 'SALESFORCE_PROD', 'schema': 'PUBLIC', 'table': 'ACCOUNTS', 'id_column': 'ACCOUNT_ID' }, 'dev': { 'database': 'SALESFORCE_TEST', 'schema': 'PUBLIC', 'table': 'ACCOUNTS', 'id_column': 'ACCOUNT_ID' } }
SYNC_SCHEDULE 5	OBJECT	The schedule, keyed on branch/account name.	{ 'main':{ 'mode': 'manual', 'warehouse': 'COMPUTE_WH', 'time_limit_mins': 240 }, 'dev':{ 'mode': 'manual', 'warehouse': 'COMPUTE_WH', 'time_limit_mins': 60 }, }

Return Type

OBJECT

DELETE_OUTBOUND_STAGED_RECORDS_BY_APPLY_STATE

Deletes outbound records from the OUTBOUND_SYNC_RECORD_STATE table, if they match the given APPLY_STATE. This procedure is intended to only be used manually in emergencies, such as if duplicate identifiers somehow make their way past the checks into the table.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
BRANCH_NAME	VARCHAR
APPLY_STATE	VARCHAR

Return Type

OBJECT

GET_INBOUND_ALL_STREAMS_VIEW_DEFINITIONS

Provides normalized view definitions to external systems like dbt, if it's their job to create them instead of the sync engine.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
BRANCH_NAME	VARCHAR

Return Type

OBJECT

GET_INBOUND_STREAM_VIEW_DEFINITION

Provides a normalized view definition to external systems like dbt, if it's their job to create them instead of the sync engine.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
BRANCH_NAME	VARCHAR
STREAM_NAME	VARCHAR

Return Type

OBJECT

MARK_RECORDS_FOR_SKIP

Marks a specific list of outbound records (by identifier) for skip in bulk.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
BRANCH_NAME	VARCHAR
APPLY_STATE	VARCHAR
RECORD_IDS	ARRAY

Return Type

OBJECT

MARK_STREAMS_FOR_FULL_REFRESH

Marks a set of streams for full refresh at the next scheduled run. Includes a flag which determines whether or not to truncate the tables.

Arguments

Name	Type
SYNC_ID	NUMERIC
BRANCH_NAME	VARCHAR
STREAMS	ARRAY
TRUNCATE	BOOLEAN

Return Type

OBJECT

PAUSE_SYNC

Marks a sync as being paused, and suspends any related Snowflake tasks.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
BRANCH_NAME	VARCHAR

Return Type

OBJECT

RESUME_SYNC

Marks a sync as scheduled, and resumes any associated Snowflake tasks.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
BRANCH_NAME	VARCHAR

Return Type

OBJECT

RUN_SYNC

Enqueues a sync run for processing, and optionally waits for it to finish. If WAIT_FOR_COMPLETION is false, the result of this call is information about whether the sync run was able to be enqueued. In this situation, a second task is manually ran which performs the processing.

Arguments

Name	Type	Notes
SYNC_ID	FLOAT	If you provide this, SYNC_SLUG must be null.
SYNC_SLUG	VARCHAR	If you provide this, SYNC_ID must be null.
BRANCH_NAME	VARCHAR	Pass in 'main' for the main sync.
RUN_SOURCE_NAME	VARCHAR	Provide the value 'external' if you are calling this proc from your own external scheduler.
RUN_SOURCE_METADATA	OBJECT	Provide any values relevant to your external scheduler that you'd like to track.
WAIT_FOR_COMPLETION	BOOLEAN	'true' to execute the proc until the run completes, 'false' to return immediately and run asynchronously.

Return Type

OBJECT

SET_FULL_REFRESH_SCHEDULE

Instructs the sync engine to perform a full refresh whenever this schedule falls due.

This does not create an additional schedule for sync runs. Instead, each time a sync run occurs, the sync engine evaluates whether or not this schedule has falled due since the previous sync run.

Arguments

Name	Type	Notes
SYNC_SLUG	VARCHAR
BRANCH_NAME	VARCHAR	Pass in 'main' for the main sync.
FULL_REFRESH_SCHEDULE	OBJECT	See below for details

The FULL_REFRESH_SCHEDULE object must contain the following fields:

• 'frequency' - the cron expression of the schedule, including timezone

• 'frequency_name' - A human-readable label for the schedule, one of: "Always", "Daily", "Weekly", "Monthly nth day", "Monthly last day", "Monthly nth weekday", "Monthly last weekday", "Custom"

• 'truncate_inbound' - whether or not to truncate the table

Example:

{
    "frequency": "* * * * * UTC",
    "frequency_name": "Always",
    "truncate_inbound": True
}

Return Type

OBJECT

SET_INBOUND_SYNC_STATE

Manually overrides the current sync state for an inbound sync. Only intended to be used under the direction of support staff, as it's safest for plugins to maintain their own state.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
STREAM_NAME	VARCHAR
STREAM_STATE	OBJECT

Return Type

OBJECT

SET_SYNC_NAME

Updates the name of a sync.

Arguments

Name	Type
SYNC_SLUG	VARCHAR
SYNC_NAME	VARCHAR

Return Type

OBJECT