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.
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.
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
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 ASint) 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.
Ready to get started?
Jump right in or a book a demo. Your first destination is always free.