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.
Hightouch recognizes the following data types:
- Associated field (linked to another table)
- Null value
- Unknown type
The Hightouch UI uses these icons to distinguish various data types:
Hightouch intentionally stringifies your chosen primary key field for enhanced record-matching performance. If you need to sync the primary key as a non-string value, use SQL aliasing in your model to create a new column specifically for syncing.
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:
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
- From the Models overview page, select the model whose columns you want to cast.
- Select the Columns tab.
- 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.
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
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.