3. Create a new model

Sync the result of a SQL query to one or more destinations

Once we have a source and a destination, we need to define a model. A model represents a query or a table that generates the data we'll use to sync from our source to our destination.

Query with SQL

Navigate to the Models page and start by choosing a Source.

Next, create a new query. If you're using the demo database, you can try:

select * from users

Click Run to see a preview of the data. By default, we limit to the first 100 rows. Then click Continue and give the model a name. You'll also need to pick the unique identifier for this table, usually id.

Each model needs a unique identifier. Hightouch uses this identifier to compare subsequent runs to only change rows where a value has changed for that particular entry.

If you want to learn more about exploring your data warehouse with SQL and with our visual query builder, check out the Exploring your data section of our docs.

Adding destinations

In the next step, we can select which destinations we want to send the query results to.

There are two ways to do this. The first way is to go to the Sync page, select the desired Model, them choose your destination.

The second way is to select a Model from the Models page and add a destination from there.

We can always add multiple destinations, but let's take Hubspot as an example. To set up the Sync, we have to map the fields in the query results (from the database / data warehouse) to the fields in Hubspot.

Not all fields have to be mapped! If you leave certain mappings empty, the fields simply wont be synced to the object created in Hubspot.

This configuration is specific to each destination that the query results are being sent to. This particular configuration will upsert the Hubspot object contacts for each row in the query of users, and map id, name, and email to id, lastname, and email respectively.

For more information on the configuration of each destination, check out the Destinations section of the documentation.

Configure your query and sync schedule

Lastly, we have to schedule how often the query is run and subsequently synced to your destinations.

Make sure to also pick a unique primary key for your query. The unique primary key column is used by Hightouch to differentiate between new rows and changed rows when diffing results between consecutive runs before sending changes to downstream destinations like Hubspot.

Your segment can be synced at per minute, hourly, daily, and weekly intervals.