Data types and casting

Overview

When syncing data to a destination, their underlying APIs require the data you send to be correctly typed. For example, a Name field may require a string data type, while a timestamp field may require a particular datetime format. If you try to sync an incorrect type, for example, a number type for an identifier, when a string type is expected, APIs usually reject the request, and the sync fails.

When you define a model, Hightouch doesn't change the data types found in your source unless otherwise specified. To safeguard your syncs from failing, the data types your model returns must align with your destination's data type expectations. If your source data types don't match your destination's expectations, you can use casting while setting up your models.

Data types

Hightouch recognizes the following data types for model columns:

• Boolean
• Number
• String
• Timestamp
• Date
• Object / Array

And the following for destination fields:

• Boolean
• Number
• String
• Timestamp
• Date/time
• Associated field (linked to another table)
• Null value
• Unknown type

The Hightouch UI uses these icons to distinguish various data types for destination fields:

View model data types

If you don't know the data types of the columns returned from your model, you can view them from the Columns tab on the model's overview page:

You can also change the data types returned from a query here.

View destination data types

When creating a sync configuration, you can view the destination fields' expected data types by looking at their icons when setting up field mappings:

Casting

For your data to reach its destination without error, its data type must match the data type required by the destination. You can use type casting to change column data types. You have these options:

• Click and point casting from a model's Columns tab: suitable for all modeling methods
• Casting via SQL in your model definition: suitable if you've built your model using the SQL editor and prefer to encode casting in your SQL query
• SQL aliasing: recommended if your primary key data type is incompatible with the expected destination data type

Casting from your model configuration

1. From the Models overview page, select the model whose columns you want to cast.
2. Select the Columns tab.
3. Use the picklist to the right of each column to choose the data type you want for that column.

Casting in SQL

You can cast values from one data type to another when writing the SQL query that defines a model.

For example, this query casts a column called total_storage_used_mb as an integer:

SELECT *,
  CAST (total_storage_used_mb AS int)
FROM public.organizations;

For more on SQL casting, see this guide on SQL type casting.

SQL aliasing

If your model's primary key isn't a string, Hightouch stringifies it for enhanced record-matching performance. If stringification makes your primary key incompatible with the data type your destination is expecting, you can use SQL aliasing to create an additional field for mapping.

For example, if you use a field called id as your primary key, Hightouch stringifies its value to perform the matching and diffing operations.

Many destinations expect a field called id to be a string. Some destinations, however, expect id to be an integer. This mismatch causes 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 this issue. This example query selects the customer_id column twice. The second time, it casts the column as an int, and aliases it as a new id_for_mapping column.

SELECT customer_id, CAST(customer_id AS int) AS id_for_mapping
FROM customers;

The query results will now include a string customer_id column and an integer id_for_mapping column. You can then use customer_id as the primary key in your model setup and id_for_mapping for field mapping in a sync configuration.