Mirror HubSpot contacts into a SQL Server warehouse in real time

Build a code workflow that keeps a SQL Server contacts warehouse in sync with HubSpot in near real time.

Trigger: a webhook from HubSpot fired on the contact.creation and contact.propertyChange events. The payload will include the changed HubSpot contact id (objectId).

Steps:

1) Read the contact id from the webhook payload. HubSpot batches events, so iterate over each event in the payload and run the rest of the workflow per contact id.

2) Call HubSpot's Get Contact operation for that id. Request the full property set we care about: email, firstname, lastname, company, lifecyclestage, lead_status, hubspot_owner_id, createdate, lastmodifieddate. Pass these explicitly via the properties query param so HubSpot returns them even if they are not on the default property list.

3) Use the SQL Server Upsert Row operation to write the contact into a dbo.contacts table in the connected SQL Server database, matched on hubspot_contact_id. Map HubSpot properties to typed columns as follows: hubspot_contact_id (NVARCHAR, primary key) ← id; email ← properties.email; firstname ← properties.firstname; lastname ← properties.lastname; company ← properties.company; lifecyclestage ← properties.lifecyclestage; lead_status ← properties.hs_lead_status; owner_id ← properties.hubspot_owner_id; createdate ← properties.createdate (parse to DATETIME2); lastmodifieddate ← properties.lastmodifieddate (parse to DATETIME2); synced_at ← current UTC time (DATETIME2). Use MERGE semantics so the same path handles creates and updates.

Setup step: include a one-time CREATE TABLE statement that the user runs against their database before turning the workflow on. The table should look like:

CREATE TABLE dbo.contacts ( hubspot_contact_id NVARCHAR(64) NOT NULL PRIMARY KEY, email NVARCHAR(320) NULL, firstname NVARCHAR(255) NULL, lastname NVARCHAR(255) NULL, company NVARCHAR(255) NULL, lifecyclestage NVARCHAR(64) NULL, lead_status NVARCHAR(64) NULL, owner_id NVARCHAR(64) NULL, createdate DATETIME2 NULL, lastmodifieddate DATETIME2 NULL, synced_at DATETIME2 NOT NULL );

Implementation notes: use parameterized queries with @p1, @p2, ... placeholders for every value written to SQL Server, as called out in the SQL Server common docs. Never interpolate HubSpot values directly into the SQL string. If HubSpot returns a 404 for the contact id (deleted before we could fetch it), log and skip that event rather than failing the whole batch. If the webhook payload contains multiple events for the same contact id, deduplicate by contact id before fetching so we only make one Get Contact call per id per webhook delivery.

Integrations: HubSpot (OAuth, crm.objects.contacts.read scope) and SQL Server.