v5.0 - v5.5 Data Sync Documentation
  • Overview
  • Release Notes
    • V4.5
    • V4.6
    • v4.7
    • v4.8
    • v4.9
    • v4.10
    • v4.12
    • v4.13
  • v4 Connections Installation Guide
    • v5 Connections and CLI Installation Guide
  • v4 Worker/Listener Installation Guide
    • v5 Worker/Listener Installation Guide
  • Upgrades & Config Changes
  • Builder Guide
    • Overview
    • Types of Data Syncs
    • Subscribing to Event Streams
      • Supported Stream Sources
        • Amazon Simple Queue Service (SQS)
        • Cinchy Change Data Capture
        • Data Polling
        • Kafka Topic
        • MongoDB
        • Salesforce
          • Push Topic
          • Platform Event
    • Configuring a Data Sync
      • Connections Experience & XML Config Reference
        • Info
          • Parameters
            • String Escape
        • Source Config Reference
          • Schema Columns
            • Calculated Column Examples
          • Auth Requests
          • Request Headers
          • Pagination
          • Source Filter
        • Target Destination Config Reference
          • Column Mappings
          • Target Destination Filter
        • Sync Behaviour
        • Post Sync
        • Permissions
        • Jobs
        • Connections Usage Example
      • Data Validation
      • Transformations
        • StringReplacement
      • Supported Data Sources
        • File Based Source
          • Binary File
            • Binary File Data Source Example
          • Delimited File
            • Delimited File Data Source XML Example
          • Fixed Width File
          • Excel
            • Excel Source XML Example
        • Cinchy Event Broker/CDC
          • Cinchy Event Broker/CDC XML Config Example
        • Cinchy Table
          • Cinchy Table XML Config Example
        • Cinchy Query
          • Cinchy Query Data Source Example
        • Copper
        • DB2
        • Dynamics 2015
        • Dynamics
        • DynamoDB
        • Kafka Topic
          • Apache AVRO Data Format
          • Kafka Topic Source Example
        • LDAP
        • MongoDB Collection
          • MongoDB Collection Source XML Example
        • MongoDB Collection (Cinchy Event Triggered)
        • MS SQL Server Query
        • MS SQL Server Table
        • ODBC Table
        • ODBC Query
        • Oracle Table
        • Oracle Query
        • Parquet
        • Polling Event
          • Polling Event Example
        • REST API (Cinchy Event Triggered)
        • REST API
          • REST API XML Example
        • SAP SuccessFactors
        • Salesforce Object (Bulk API)
        • Salesforce Platform Event
        • Salesforce Push Topic
        • Snowflake
          • Snowflake Source XML Example
        • SOAP 1.2 Web Service
      • Supported Sync Targets
        • Cinchy Table
        • DB2 Table
        • Dynamics
        • Kafka Topic
        • MongoDB Collection (Column Based)
        • MS SQL Server Table
        • Oracle Table
        • REST API
        • Salesforce
        • Salesforce Object
        • Snowflake Table
          • Snowflake Table Target XML Example
        • SOAP 1.2 Web Service
    • Common Design Patterns
    • Testing a Data Sync
    • Promoting a Data Sync
    • Scheduling a Data Sync
    • CLI Command List
    • Connections Functions
    • Monitoring
  • Cinchy Platform Documentation
Powered by GitBook
On this page
  • 1. Overview
  • 2. Standard Column
  • 3. Standard Calculated Column
  • 4. Conditional Calculated Column
  • 5. JavaScript Calculated Column
  • 6. Columns in XML
  • 6.1 Attributes Descriptions
  • Elements:

Was this helpful?

  1. Builder Guide
  2. Configuring a Data Sync
  3. Connections Experience & XML Config Reference
  4. Source Config Reference

Schema Columns

This page details the various types of Columns available within a Source Data Sync

PreviousSource Config ReferenceNextCalculated Column Examples

Last updated 1 year ago

Was this helpful?

1. Overview

Schema columns refer to your mapping on your data source. For example, if your source is a CSV with the columns 'Name', 'Age', and 'Company', you would set up three schema columns in the Connections UI or data sync XML to match. These schema columns are then mapped to your destination columns for your data sync target, so that the data knows where to go.

Note that you do not have to, and sometimes won't be able to, set up an exact 1:1 relationship between source columns/data and schema columns.

The only difference between setting up your schema columns in the Connections UI vs in a data sync XML is the addition of the Alias column, which is only in the Experience. This attribute allows the user to give an alternative name to the column mapping (usually used for easier readability). The various types of columns are detailed below.

Note that some source types have unique parameters not otherwise specified in other sources. You can find information on those, where applicable, in the source's main page.

You can review the various attribute descriptions

2. Standard Column

Fill in the following attributes for a Standard Column (Image 1):

  • Name: The name of your column

  • Formula: The formula associated with your calculated column

  • Data Type: The return data type of your column, this can be either:

    • Text

    • Date

    • Number

    • Bool

    • Geometry

    • Geography

If a Destination column is being used as a sync key, its source column has to be set to type=Text regardless of its actual type

  • Description: Describe your column

  • Advanced Settings:

    • You can select if you want this column to be mandatory

    • You can choose whether your data must be validated

  • If both Mandatory and Validated are checked on a column, then rows where the column is empty are rejected

  • If just Mandatory is checked on a column, then all rows are synced with the execution log status of failed, and the source error of "Mandatory Rule Violation"

  • If just Validated is checked on a column, then all rows are synced.

  • If your data type was chosen as "text", you can choose whether to trim the whitespace or not

You can choose to add in a Transformation > String Replacement by inputting the following:

  • Pattern for your string replacement

  • Replacement

Note that you can have more than one String Replacement

3. Standard Calculated Column

Fill in the following attributes for a Standard Calculated Column (Image 2):

  • Name: The name of your column

  • Formula: The formula associated with your calculated column

  • Data Type: The return data type of your column, this can be either:

    • Text

    • Date

    • Number

    • Bool

    • Geometry

    • Geography

If a Destination column is being used as a sync key, its source column has to be set to type=Text regardless of its actual type.

  • Description: Describe your calculated column

  • Advanced Settings:

    • You can select if you want this column to be mandatory.

    • You can choose whether your data must be validated.

  • If both Mandatory and Validated are checked on a column, then rows where the column is empty are rejected

  • If just Mandatory is checked on a column, then all rows are synced with the execution log status of failed, and the source error of "Mandatory Rule Violation"

  • If just Validated is checked on a column, then all rows are synced.

4. Conditional Calculated Column

Fill in the following attributes for a Conditional Calculated Column (Image 3):

  • Name: The name of your column

  • Data Type: The return data type of your column, this can be either:

    • Text

    • Date

    • Number

    • Bool

    • Geometry

    • Geography

If a Destination column is being used as a sync key, its source column has to be set to type=Text regardless of its actual type.

  • Description: Describe your calculated column

  • Advanced Settings:

    • You can select if you want this column to be mandatory.

    • You can choose whether your data must be validated.

  • If both Mandatory and Validated are checked on a column, then rows where the column is empty are rejected

  • If just Mandatory is checked on a column, then all rows are synced with the execution log status of failed, and the source error of "Mandatory Rule Violation"

  • If just Validated is checked on a column, then all rows are synced.

  • Condition:

    • Name:

    • IF: Click Edit to create the "if" for your Conditional Statement (Image 4)

  • Then: Click Edit to create the "then" for your Conditional Statement (Image 5)

  • Default: Click Edit to create your default expression (Image 6)

5. JavaScript Calculated Column

Fill in the following attributes for a JavaScript Calculated Column (Image 7):

  • Name: The name of your column

  • Data Type: The return data type of your column, this can be either:

    • Text

    • Date

    • Number

    • Bool

    • Geometry

    • Geography

If a Destination column is being used as a sync key, its source column has to be set to type=Text regardless of it’s actual type

  • Description: Describe your calculated column

  • Advanced Settings:

    • You can select if you want this column to be mandatory.

    • You can choose whether your data must be validated.

  • If both Mandatory and Validated are checked on a column, then rows where the column is empty are rejected

  • If just Mandatory is checked on a column, then all rows are synced with the execution log status of failed, and the source error of "Mandatory Rule Violation"

  • If just Validated is checked on a column, then all rows are synced.

  • Script: Enter in your JavaScript

6. Columns in XML

This XML element defines each column in the data set as well as their data type:

<Column
    name="string"
    dataType="Text"| "Date"| "Number"| "Bool"| "Geometry"| "Geography"
    ordinal="int"                     -- Depends on the data source
    maxLength="int"                   --OPTIONAL
    isMandatory=["true", "false"]     --OPTIONAL
    validateData=["true", "false"]    --OPTIONAL
    trimWhitespace=["true", "false"]  --OPTIONAL
    description="string"              --OPTIONAL
    inputFormat="string"              --OPTIONAL
    >
    ...
</Column>

6.1 Attributes Descriptions

name

dataType

The data type of each column could be Text, Date, Number, Bool, Geometry, or Geography.

If a Destination column is being used as a sync key, its source column has to be set to type=Text regardless of it’s actual type

To sync into a Cinchy table with a Geometry or Geography column, those respective data types must be used in the data sync, and the input should be in well-known text (WKT) format.

The dataType affects how the source and target data is parsed, and also determines how the fields are compared for equality. If your sync keeps updating a field that has not changed, double check your data types.

For example, given line 1 of a csv file:

Name, Location, Age

The ordinal for Age would be 3.

maxLength

The max length of data in the column.

isMandatory

Boolean value determining if the field is a mandatory column to create a row entry.

A defined synckey column of any data type can be checked for NULL values using isMandatory="true”. When validation fails, an error message is displayed in the command line. For other columns when validation fails, the Execution Errors Table is updated with Error Type, Mandatory Rule violation for that column and row that failed.

validateData

Boolean value determining whether or not to validate the data before inserting. Valid data means to fit all the constraints of the column (dataType, maxLength, isMandatory, inputFormat). If the data is not valid and validateData is true, then the entry will not be synced into the table. The Execution Errors Table is also updated with the appropriate Error Type (Invalid Format Exception, Max Length Violation, Mandatory Rule Violation, Input Format Exception)

trimWhitespace

Boolean value determining whether or not to trim white space.

description

Description of the column.

inputFormat

inputFormat attribute is useful when source file need some format changes in the input data

Elements:

The user defined name for each column. This is used in when you want to indicate the name of the sourceColumn.

Date fields support the inputFormat which adheres to the c# .net DateTime.ParseExact format. See for reference.

<ColumnMapping>
here
<Transformations>
here.
Image 1: Standard Column
Image 2: Standard Calculated Columns
Image 3: Conditional Calculated Column
Image 4: Creating your Conditional statement
Image 5: Creating your Conditional Statement
Image 6: Creating your Default Expression
Image 7: JavaScript Calculated Column