# Internal Stored Procedures

## APPLY\_INBOUND\_SYNC\_SETTINGS

{% hint style="warning" %}
This stored procedure is deprecated in favour of CONFIGURE\_OMNATA\_INBOUND\_SYNC
{% endhint %}

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

{% hint style="warning" %}
This stored procedure is deprecated in favour of CONFIGURE\_OMNATA\_OUTBOUND\_SYNC
{% endhint %}

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:

<sup>1</sup> Stable identifier - A fixed value for identifying the sync

<sup>2</sup> Change Management - Relates to managing changes between environments

<sup>3</sup> Directly Editable - Configuration changes that can be made directly to syncs

<sup>4</sup> Managed Changes - Configuration changes that are applied to a sync/branch through each step of a pipeline

<sup>5</sup> Branch/Account specific changes - Configuration change variations that belong permanently to a particular account/branch

<table data-full-width="true"><thead><tr><th width="301.484375">Name</th><th width="110.54296875">Type</th><th width="250.0859375">Description</th><th>Example(s)</th></tr></thead><tbody><tr><td>SYNC_SLUG <sup>1</sup></td><td>VARCHAR</td><td>The human-readable identifier for the sync. Must be unique within this Sync Engine instance.</td><td><pre data-overflow="wrap"><code>'salesforce-prod-to-snowflake'
</code></pre></td></tr><tr><td>MULTI_ACCOUNT <sup>2</sup><br><br><em>(optional)</em></td><td>BOOLEAN</td><td>Use this for syncs which span multiple Snowflake accounts. If this is true, then SINGLE_ACCOUNT_BRANCHING must be false</td><td><code>true</code></td></tr><tr><td>SINGLE_ACCOUNT_BRANCHING <sup>2</sup><br><br><em>(Optional)</em></td><td>BOOLEAN</td><td>Use this for a single account with branching</td><td><code>true</code></td></tr><tr><td>SINGLE_ACCOUNT_BRANCH_TO_CONFIGURE <sup>2</sup><br><br><em>(Optional)</em></td><td>VARCHAR</td><td>This parameter indicates which environment we are in, and its value needs to come from the executing process (CI tool variable, etc)</td><td><code>'dev'</code><br><br><code>'qa'</code><br><br><code>'main'</code></td></tr><tr><td>SYNC_NAME <sup>3</sup></td><td>VARCHAR</td><td>The name of the sync</td><td><pre data-overflow="wrap"><code>'Salesforce Prod to Snowflake'
</code></pre></td></tr><tr><td>SYNC_TAGS <sup>3</sup><br><br><em>(Optional)</em></td><td>ARRAY</td><td>Arbitrary tags to apply to the sync</td><td><pre class="language-json" data-overflow="wrap"><code class="lang-json">['CRM','Accounting']
</code></pre></td></tr><tr><td>SYNC_PARAMETERS <sup>4</sup></td><td>OBJECT</td><td>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</td><td><pre class="language-json" data-overflow="wrap"><code class="lang-json">{
'sync_type':'watermark',
  'object_type':'table'
}
</code></pre><p></p><pre class="language-json" data-overflow="wrap"><code class="lang-json">{
  'sync_type':{
    'value': 'watermark',
    'metadata': {}
  },
  'object_type':{
    'value': 'table',
    'metadata': {
      'abc': 123
    }
}
</code></pre></td></tr><tr><td>STREAM_NAMES <sup>4</sup></td><td>ARRAY</td><td>The names of all streams to include in the sync</td><td><pre class="language-json" data-overflow="wrap"><code class="lang-json">['Account','Contact','Opportunity']
</code></pre></td></tr><tr><td>SYNC_STRATEGY <sup>4</sup></td><td>VARIANT</td><td>The sync strategy for all streams, or a mapping from stream name to sync strategy</td><td><pre><code>'auto'
</code></pre><p></p><pre class="language-json" data-overflow="wrap"><code class="lang-json">{
  'Account': 'Incremental',
  'Contact': 'Full Refresh',
  'Opportunity': 'Incremental'
}
</code></pre></td></tr><tr><td>STORAGE_BEHAVIOUR <sup>4</sup></td><td>VARIANT</td><td>The storage behaviour for all streams, or a mapping from stream name to storage behaviour</td><td><pre><code>'merge'
</code></pre><p></p><pre><code>{
  'Account': 'merge',
  'Contact': 'append',
  'Opportunity': 'append'
}
</code></pre></td></tr><tr><td>STREAM_PRIMARY_KEYS <sup>4</sup><br><br><em>(Optional)</em></td><td>OBJECT</td><td>A mapping of stream names to primary key field(s).<br>Only required if the plugin does not provide the primary key definition.</td><td><pre><code>{
  'Account': 'Id',
  'Contact': ['Name','DOB'],
}
</code></pre></td></tr><tr><td>STREAM_CURSOR_FIELDS <sup>4</sup><br><br><em>(Optional)</em></td><td>OBJECT</td><td>A mapping of stream names to cursor field.<br>Only required if the plugin does not provide a default cursor field.</td><td><pre><code>{
  'Account': 'UpdatedDate',
  'Contact': 'UpdatedDate'
</code></pre></td></tr><tr><td>CONNECTION_SLUG <sup>5</sup></td><td>VARIANT</td><td>The connection slug for a sync or branch. Can be either a string (simple sync) or an object (multi-account or branching)</td><td><pre><code>'my-connection'
</code></pre><pre class="language-json" data-overflow="wrap"><code class="lang-json">{
  'main':'my-prod-conn',
  'dev':'my-dev-account'
}
</code></pre></td></tr><tr><td>TUNING_PARAMETERS <sup>5</sup><br><br><em>(Optional)</em></td><td>OBJECT</td><td>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</td><td><pre class="language-json"><code class="lang-json">{
  'max_records_per_file':'500',
  'max_concurrent_uploads':'2'
}
</code></pre></td></tr><tr><td>SYNC_SCHEDULE <sup>5</sup></td><td>OBJECT</td><td>The schedule, keyed on branch/account name.</td><td><pre class="language-json"><code class="lang-json">{
  'main':{
    'mode': 'manual',
    'warehouse': 'COMPUTE_WH',
    'time_limit_mins': 240
  },
  'dev':{
    'mode': 'manual',
    'warehouse': 'COMPUTE_WH',
    'time_limit_mins': 60
  },
}
</code></pre></td></tr><tr><td>STORAGE_LOCATION <sup>5</sup><br><br><em>(Optional)</em></td><td>OBJECT</td><td>The location of the inbound tables, optionally keyed on account name.<br>For single-account branches, the branch name is part of the template and therefore shares a definition with the main sync.</td><td><pre data-overflow="wrap"><code>{
  '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}}'
}
</code></pre></td></tr></tbody></table>

### 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:

<sup>1</sup> Stable identifier - A fixed value for identifying the sync

<sup>2</sup> Change Management - Relates to managing changes between environments

<sup>3</sup> Directly Editable - Configuration changes that can be made directly to syncs

<sup>4</sup> Managed Changes - Configuration changes that are applied to a sync/branch through each step of a pipeline

<sup>5</sup> Branch/Account specific changes - Configuration change variations that belong permanently to a particular account/branch

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

### 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.                                                                                   |
| RAISE\_ERRORS         | BOOLEAN | 'true' to throw a SQL exception if there are any errors during the run (WAIT\_FOR\_COMPLETION should be set to 'true' as well). 'false' to return error information in the object response. |
| CURRENT\_USER         | VARCHAR | Used in conjuction with sync/branch owners. If a sync or branch has a designated owner, then this parameter value must match it by passing `current_user()` to this parameter.              |

### Return Type

OBJECT

## SET\_FULL\_REFRESH\_SCHEDULE

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

{% hint style="info" icon="triangle-exclamation" %}
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.
{% endhint %}

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