Search documentation...

K

Overview

Third-party APIs require the data you send to be of a specific data type. Unless otherwise specified, Hightouch takes the data from your source and leaves the data type unchanged to send to your destination.

Data Types

You'll see these icons in the Hightouch UI to distinguish various data types:
Hightouch intentionallly stringfies the field you designate as the 'primary key' in your model for enhanced record matching performance. If you also need to sync the primary key as a non-string value, use SQL aliasing in your model to create a new field specifically for syncing.

Viewing Model Data Types in Your Sync

If you don't know the data type of a column returned in your model, you can use the 'Test' feature in your sync's configuration to view the data types before sending data to your destination:

Viewing Destination Data Types

Also in your sync's configuration, you can view the data types of any destination fields you set up for your mappings:

Casting

In order for your data to reach its destination without error, its data type must match the data type required by the destination. You can use 'casting' to turn of value of one data type into another data type.

Casting in SQL

You can use SQL in your model to cast values from one data type to another.
For example, this query will cast a STRING as a DATETIME:
SELECT CAST('2017-08-25' AS datetime);
For more on SQL casting, see How To: SQL Type Casting

SQL Aliasing

If your model's primary key is not already a string, Hightouch will intentionallly stringfy it for enhanced record matching performance.
If the casting of the primary key makes it incompatible with the data type your destination is expecting, we recommend you use SQL aliasing to create an additional field for mapping.
For example, if you use a field called id as your primary key, Hightouch will stringify that number to perform the matching and diffing operations.
Many destinations actually expect a field called id to be a string. Some destinations, however, expect id to be an integer. This will cause a data type conflict if you need to map your primary key in addition to using it for matching.
Using SQL aliasing and casting can solve for this issue. In this example query of a customers table, we select the customer_id - the primary key - which will be stringified and used for matching in Hightouch. Then, we select the customer_id again but alias it as id_for_mapping and cast it is an int so that id_for_mapping can be mapped to a destination field:
SELECT customer_id, CAST(customer_id AS int) AS id_for_mapping
FROM customers;

    Need help?

    Our team is relentlessly focused on your success. We're ready to jump on a call to help unblock you.

    • Connection issues with your data warehouse?
    • Confusing API responses from destination systems?
    • Unsupported destination objects or modes?
    • Help with complex SQL queries?

    or

    Feature Requests?

    If you see something that's missing from our app, let us know and we'll work with you to build it!

    We want to hear your suggestions for new sources, destinations, and other features that would help you activate your data.

On this page

OverviewData TypesViewing Model Data Types in Your SyncViewing Destination Data TypesCastingCasting in SQLSQL Aliasing

Was this page helpful?