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 can choose to cast data into different types using SQL.
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.
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.
Make sure to read through the primary key
updates section before making
any changes to your primary key.
Hightouch intentionally stringifies your chosen primary
key column for
enhanced performance during change data
capture. If you need to
sync the primary key column as a non-string value, use SQL
aliasing in your model to create a new column specifically for
syncing.
When you set up field mappings in a sync, each column and destination field shows a data-type icon. Use these icons to confirm a source column’s type matches the destination field’s expected type before you sync.
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
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;
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_mappingFROM 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.
