Tulip Data Bridge

Introduction#

Tulip Data Bridge is a middleware solution built to streamline data integration, transformation, and validation between external systems and the Tulip platform.

Tulip Data Bridge helps address the challenges of manual data formatting, middleware coding, and data quality assurance by providing an interface for users to manage complex data workflows efficiently. It enables organizations to synchronize, transform, and validate data going into or coming out of Tulip without disrupting existing workflows or requiring third party middleware.

Benefits of Tulip Data Bridge#

Setup#

Data bridge transformers are set up using Tulip’s Admin Console within the Integrations tab.

To create a new transformation:

1. Navigate to Integrations → Transformer Management.
2. Click Create Transformer

3. Fill out the following fields:

   Field	Description
   Enable transformer	Enables or disables the transformer
   External ID	The unique identifier of the transformer. This identifier will be used when invoking the transformer in your API calls.
   Description	Description of the transformer
   Direction	INBOUND: this transformer is intended to modify data coming into Tulip. OUTBOUND: this transformer is intended to modify the response of data fetched from Tulip. Note: This section focuses on transforming the inbound data feed
   Jsonata transformer definition	These transformers leverage the JSONATA syntax to transform input JSON into output JSON. The transformers will also work for flat files (since flat files get converted to JSON before being processed). For an overview of how to structure JSONATA, you can follow this documentation: JSONATA Documentation
   Resource Type	Metadata to indicate to the user where the transformation is intended to be used. The resource type is not used to constrain which APIs will support this transfomer; it is merely for bookkeeping purposes.

Using Transformers for Inbound API calls#

You can use transformers for any inbound Aerial API request to create, update or patch data (Supported APIs are found in our Core API docs).

To make an API request that leverages the transformer:

1. Add the X-Tulip-Inbound-Transformer-Id header to the API request
2. Set the external ID of the transformer you created as the value of X-Tulip-Inbound-Transformer-Id

For example, if you created a transformer with the external ID ShopifyLocationToTulipStore:

To use this transformer on an inbound request, set the X-Tulip-Inbound-Transformer-Id header value to ShopifyLocationToTulipStore.

Using Transformers with Flat File Importer (FFI)#

When setting up a transformer, after clicking Inbound, a File Prefix box will appear:

Flat File Importer (FFI) will look for any transformers associated with a file prefix when processing a file. If a transformer is detected that matches the prefix of a file, it will be applied to the incoming data.

Any writeable endpoint that exists in Aerial can also be sent via a data bridge modified FFI file.

Setting up a transformer for FFI#

To set up a transformer for FFI:

1. Log into Admin Console.
2. Navigate to Integrations > Transformer Management
3. Create a transformer with the following fields per the table below:

FFI file naming convention#

Use this prefix convention when inputting your file prefix into Admin Console: File Prefix: TGEN_<recordType>_<importType>_<apiVersion>

FFI will extract this part of the filename to send to Aerial in the X-Tulip-File-Prefix header. If a transformer for this File Prefix isn’t defined, no transformation will be applied.

The filename of the file you upload to the upload/generic folder (whether through SFTP or directly via Admin Console) must follow the format:

TGEN_<recordType>_<importType>_<apiVersion>_<optionalSuffix>.csv

Only the part up to <apiVersion> should be included in the file prefix.

How Do I Deal With Spaces in the File Header?#

Use `` in your transformer definition. For instance, `customer code`.

Is the Transformer Case-Sensitive?#

Yes. If your column header is subject and you call for Subject in the transformer, it will not transform that field.

Using JSONATA to Power Transformations#

You can use JSONATA to run any transform to your input data. You can use the Test Transformer modal in Admin Console as a playground to see the output of your values.

Using JSONATA as PATCH#

Where available, PATCH is typically the first method we recommend to customers for integrating with Tulip. PATCH allows you to only update/alter the data you want to send, and falls back to create if the record does not exist.

When using JSONATA to transform data, you may want to transform a subset of data, but leave the rest as-is.

For instance, you may have a number of fields that do not need to be modified or were modified in another source system. It is possible to use the $ symbol to return the original object. Further, if you want to return the original object with the exception of specified fields, you can leverage the sift() function.

A Simple Example to Show the Power of JSONATA#

For instance, examine the following example: https://try.jsonata.org/tcdClyieV

Here, we have fields firstName, prefix, image, and externalId that we want to keep as is, but want don’t want to have to explicitly declare them (there could be any n values like this). We have age here, but we want age to be an attribute, since it is not a top-level field in Tulip. We also want to transform the phone number into E164 format and place it in the phone numbers array. And then, just to show some of the flexibility of JSONata, we’ll make another attrubute called countryLength that combines the country name and the number of characters in the country name.

Starting with the input payload:

{
  "age": 30,
  "countryName": "USA",
  "externalId": "12345",
  "firstName": "Jess",
  "image": "https://www.pngitem.com/middle/howRRmi_no-profile-picture-jpg-hd-png-download/",
  "phone": "2238080033",
  "prefix": "Doctor"
}

You can apply the below transform in jsonata:

$merge([
  $sift($, function($value, $key) { $key != "age" and $key != "phone" }),
  {
    "attributes": [
      {
          "value": age,
          "attributeExternalId": "age",
          "languageExternalId": "en"
      },
      {
          "value": countryName & "-" & $length(countryName),
          "attributeExternalId": "countryLength",
          "languageExternalId": "en"
      }
    ],
    "phoneNumbers": [
      {
          "externalId": externalId & "-phone",
          "number": "+1" & phone,
          "isPrimary": true,
          "disabled": false
      }
    ]
  }
])

Here, we:

• Leverage the sift function to remove age and phone from the top level fields.

• Use $ as the input to that sift function, allowing us to retain the original input as-is with the exception of age and phone, which are sifted out.

• Create another object with arrays for attributes and phoneNumbers.

• Set an attribute for age and country length, filling out some required Tulip data while extracting/transforming the values for age and country length from the input.

• Create an array of phone numbers, transforming the phone into E164 format, and generating an externalId using the externalId of the customer and -phone.

• Apply merge to the entire object to merge the result of the sift function and the additional transformations we’ve made into the single output of a Tulip customer object.

And you will achieve the below:

{
  "countryName": "USA",
  "externalId": "12345",
  "firstName": "Jess",
  "image": "https://www.pngitem.com/middle/howRRmi_no-profile-picture-jpg-hd-png-download/",
  "lastName": "Jones",
  "prefix": "Doctor",
  "attributes": [
    {
      "value": 30,
      "attributeExternalId": "age",
      "languageExternalId": "en"
    },
    {
      "value": "USA-3",
      "attributeExternalId": "countryLength",
      "languageExternalId": "en"
    }
  ],
  "phoneNumbers": [
    {
      "externalId": "12345-phone",
      "number": "+12238080033",
      "isPrimary": true,
      "disabled": false
    }
  ]
}

Outbound Data Mapper#

The outbound data mapper for Tulip Data Bridge allows the transformation of a payload from a Tulip payload to any external payload. For instance, you can fetch a Tulip customer using the transformer header and receive the customer in a Salesforce payload without having to do any additional transformation.

When clicking Create Transformer, a form opens with a dropdown selector with the options: INBOUND, OUTBOUND.

Upon clicking OUTBOUND, you will see the following options:

Using Transformers for API calls#

You can use transformers for any Aerial API request to fetch data.

To make an API request that leverages a transfromer:

1. Add the X-Tulip-Outbound-Transformer-Id header to the API request

2. Set the external ID of the transformer you created as the value of X-Tulip-Outbound-Transformer-Id

For example, if you created a transformer with the external ID ShopifyLocationToTulipStore

To use this transformer on a GET request, set the X-Tulip-Outbound-Transformer-Id header value to ShopifyLocationToTulipStore.

In the response of this fetch, you will receive a transformed object; the Tulip store received by fetching Tulip’s Aerial endpoint will be transformed to a Shopify location.

Leveraging Transformer for API Calls to External Systems#

Since the outbound transformer transforms data fetched from Tulip, it can be used in conjunction with other Tulip tooling such as webhooks, job scheduler, extensions, and extensibility.

For instance, whenever a customer is created in Tulip, you want to send it to Salesforce as a Salesforce customer. You could use customer webhooks to trigger an extension whenever a customer is created in Tulip. This extension could then call Tulip’s customer fetch API using the UUID provided in the webhook event. By including the X-Tulip-Outbound-Transformer-Id header in the fetch, a Salesforce customer can be returned directly from Tulip. No additional mapping would be required. Then the extension would hit the Salesforce endpoint with the appropriate auth and the payload just retrieved.

Data Bridge Validators#

Tulip data bridge validators allows clients to prevalidate data before it is processed by Tulip, allowing the data to meet the expected standard of quality before being ingested.

Setting up Validators#

To create a validator:

1. Navigate to Integrations → Validator Management

2. Click Create Validator

You can use the Test Validator button to test your validator in a sandbox directly in Admin Console.

How to Build a Validation Schema#

The schema must follow the standard 2020-12 JSON schema as outlined here: JSON Schema: A Media Type for Describing JSON Documents

This means you can use all the standard keywords from that specification, such as:

• type: (e.g., “object”, “string”, “number”, “array”, “boolean”)

• properties: To define the fields within an object.

• required: To list which properties are mandatory.

• minLength / maxLength: For strings.

• minimum / maximum: For numbers.

• pattern: For regular expression matching on strings.

• items: To define the structure of elements within an array.

For instance, the below schema will ensure that externalID, emailAddress, and timezone are sent as required fields, that the external ID is at least 5 characters long, the timezone is at least 1 character long, and the email includes and @ sign:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Tulip Store Payload Validator",
  "description": "Ensures store data meets minimum requirements.",
  "type": "object",
  "properties": {
    "externalID": {
      "description": "The store's unique external ID.",
      "type": "string",
      "minLength": 5
    },
    "emailAddress": {
      "description": "The store's email address.",
      "type": "string",
      "pattern": ".*@.*"
    },
    "timezone": {
      "description": "The IANA timezone string for the store (e.g., 'America/New_York').",
      "type": "string",
      "minLength": 1
    }
  },
  "required": [
    "externalID",
    "emailAddress",
    "timezone"
  ]
}

Building a validator schema for your specific data mapping#

You do not need to use a transformer alongside a validator. However, if you do use a transformer, your schema validates the data after the transformer has run. Therefore, you must build your schema to match the final, transformed JSON structure, not the raw inbound data.

Let’s walk through a concrete example.

Step 1: Raw Inbound Payload#

This payload from the external system remains the same, containing extra fields we will not map.

{
  "employee_id": "jdoe123",
  "username": "jdoe",
  "first_name": "Jane",
  "last_name": "Doe",
  "email_address": "jane.doe@example.com",
  "language_code": "en",
  "default_store_id": "STORE-001",
  "active_status": true,
  "roles": [
    "associate",
    "manager"
  ]
}

Step 2: The Inbound Transformer#

This data bridge mapper will transform the original inbound payload into a Tulip payload

{
  "externalId": `employee_id`,
  "firstName": `first_name`,
  "lastName": `last_name`,
  "email": `email_address`,
  "store": `default_store_id`,
  "authUser": {
    "username": `username`,
    "roles": `roles`
  }
}

After this transformer runs, the data passed to the validator will be:

{
  "externalId": "jdoe123",
  "firstName": "Jane",
  "lastName": "Doe",
  "email": "jane.doe@example.com",
  "store": "STORE-001",
  "authUser": {
    "username": "jdoe",
    "roles": [
      "associate",
      "manager"
    ]
  }
}

Step 3: The Final Validator Schema#

This schema enforces these rules:

The fields externalId, username, firstName, lastName, email, roles are mandatory.

email must be a valid format.

username must be lowercase letters/numbers.

roles must be an array with at least one string.

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Tulip Employee Validator (with authUser)",
  "description": "Validates inbound employee data with the nested authUser object.",
  "type": "object",
  "properties": {
    "externalId": {
      "description": "The unique identifier for the employee.",
      "type": "string",
      "minLength": 1
    },
    "firstName": {
      "type": "string",
      "minLength": 1
    },
    "lastName": {
      "type": "string",
      "minLength": 1
    },
    "email": {
      "description": "The employee's email address.",
      "type": "string",
      "format": "email"
    },
    "store": {
      "description": "The externalId or uuid of the employee's default store.",
      "type": "string",
      "minLength": 1
    },
    "authUser": {
      "description": "The employee's authentication and permissions object.",
      "type": "object",
      "properties": {
        "username": {
          "description": "The employee's login username.",
          "type": "string",
          "pattern": "^[a-z0-9]+$"
        },
        "roles": {
          "description": "A list of the employee's role codes.",
          "type": "array",
          "items": {
            "type": "string"
          },
          "minItems": 1
        }
      },
      "required": [
        "username",
        "roles"
      ],
      "additionalProperties": false
    }
  },
  "required": [
    "externalId",
    "firstName",
    "lastName",
    "email",
    "authUser"
  ]
}

(Note: you can set “additionalProperties”: false to cause any field not defined in your schema to be rejected.)

How to Use the Validator in Your API Call#

Validators are invoked through a parameter in the header of the API call. Any Aerial POST, PUT or PATCH endpoint supports data bridge validators.

Use the Header X-Tulip-Validator-Id as the key

As the value, use the external ID of the validator

How to Use the Validator with FFI#

Simply add the prefix of the file into the File Prefix field. Then any file uploaded to Tulip’s SFTP (directly or through Admin Console) will hit this validator after any transformations.

For generic FFI, which is the standard file integration method for any endpoint in Aerial, the File Prefix is TGEN_<recordType>_<importType>_<apiVersion>

How Does a Validator Pair with a Transformer?#

Any transformations on inbound data are applied first, before the validator is invoked.

Let’s say you are sending a field to Tulip called customer code and you have a transformer that converts that field to externalID - {“externalId”: `customer code`,…}

To validate that the customer code is at least 6 characters long, you would perform the validation on the externalId field, not the customer code field. By the time the payload gets to the validator, customer code will already have been transformed to externalId.