In the production environment, a date field is set to NULL by default. If no date is specified or if it is deleted, HubSpot fills this date field with an empty string “”. As a result, the connector does not work in the production environment, as date fields cannot contain strings. So either all these fields must be of the date-time type, or they must all be of the string type.
How can this be fixed?
This is a classic API-to-warehouse type mismatch — and it’s not really a HubSpot bug, it’s about how the data moves.
What’s happening: HubSpot’s API returns an empty string "" for unset date properties (not a true null). Synapse’s date/datetime columns can’t ingest a string, so the copy activity fails. Your target table expects NULL-or-valid-date, but it’s receiving "".
How to fix it — normalize between HubSpot and the typed table, not inside the typed table itself:
Land raw, transform typed. Stage the HubSpot extract into a staging table where the date columns are VARCHAR. Then transform into the final table with NULLIF(TRIM(date_col), '')::DATE (or TRY_CAST(NULLIF(date_col,'') AS DATE)). Empty strings become real NULLs, valid dates cast normally. This is the robust pattern and it survives future API quirks too.
If you’re using a Synapse copy activity / data flow: add a derived column in the mapping that converts empty strings to null before the sink — e.g. iif(dateField == '', toDate(null()), toDate(dateField)). Don’t let the raw "" reach a typed column.
Check the connector’s null handling: some HubSpot connectors expose a “treat empty as null” style setting — worth checking, but don’t rely on it alone; staging as strings is more predictable.
Fix it in the pipeline rather than making everything strings downstream — typed date columns with real NULLs are what you want for date math later.
Hi @NiekHugen ,
Sounds like a NULL vs. empty string mismatch rather than a real schema problem. If I’m reading it right, the connector is returning "" for empty date properties, and the date/datetime column in Synapse can’t accept that.
Quick question: are you pulling HubSpot data through a HubSpot connector in a Synapse/Data Factory pipeline, or through a Marketplace app? The fix depends a bit on which one you’re using.
If it’s a pipeline copy activity, a common approach is to land those date fields as strings in a staging table, then convert them when loading the final table, for example:
TRY_CAST(NULLIF(your_column, '') AS datetime2)
A mapping data flow can handle the same idea by converting an empty string to NULL and otherwise keeping the timestamp. That way, your final table still has proper date fields for filtering and date calculations.
It’s also worth checking which version of the HubSpot connector you’re using, since different connector versions can handle data types differently.
If it works in test but not in production, I’d compare one affected record in each environment. If you can pin it down to a specific column, capture the source value, the value actually coming through, and the exact error. That will also make it much easier to determine whether you need to troubleshoot the pipeline, HubSpot, or the connector itself.