Objective
This article explains how to successfully clear data from a HubSpot property using Segment Reverse ETL when a source field is removed or becomes null in your data model. If you notice that deleted event IDs or null values are not syncing to HubSpot contact records (even with the "Sync null values" toggle enabled) this guide will help you adjust your SQL query to ensure canceled or removed data accurately clears the target field.
Product
Twilio Segment
Environment
Segment Console
User Account Permission/Role(s) Required
Workspace Owner
Procedure
Segment Reverse ETL uses a diffing mechanism to compare data states between syncs. While Segment correctly detects changes from a value to NULL and passes that null downstream, the HubSpot API ignores null values for property updates. To explicitly clear a property value via the HubSpot API, the payload must pass an empty string ("") rather than a null object.
To resolve this issue, you must configure your data model to output an empty string instead of a null value.
- Navigate to you Reverse ETL model.
- Locate the SQL model that powers your HubSpot Reverse ETL sync.
- Modify the SQL query to handle null values for the affected field. Wrap the specific property name (e.g., field_event) with a COALESCE function to replace any NULL with an empty string. Example: Update your SELECT statement to include: COALESCE(field_event, '') AS field_event
- Save and execute the updated SQL model. Going forward, if the column has a value, COALESCE passes the exact value through to Segment. When the column becomes NULL, it will catch it and replace it with an empty string.
- Verify your mapping configuration to ensure the "Sync null values" toggle is enabled for the updated field.
- Trigger a manual sync or wait for the next scheduled sync interval. The empty string will now successfully clear the corresponding field on the contact record in HubSpot.
Additional Information
For more details on how the HubSpot API handles payloads and property updates, refer to HubSpot's developer documentation regarding clearing property values.