Data Types and Casting

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.

You'll see these icons in the Hightouch UI to distinguish various data types:

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:

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

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.

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

If your model's primary key is not already a string, Hightouch will intentionallly stringify 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;