source:hubspot engagements have wrong associations with companies, deals and contacts

[nimuriel]

Hello, there is a HUGE bug in the way airbyte gets the associations to engagements (I think it can be impacting other stuff but I have just checked the engagements)

The issue is when companies, deals or contacts associated to an engagement are merged to other records. The engagement last_modified_date doesn't change (so no incremental change is identified), but the associated company / deal / record changes and on Airbyte we are left with the old records.

There is also an issue when companies, deals, contacts or tickets are associated to the engagement AFTER it's creation or last update, again, as the last_modified_date doesn't change, the data is wrong.

For instance:

From Airbyte I am getting this data on BigQuery for the engagement_id = 24696859331

[{
  "_airbyte_raw_id": "9efa5538-0f0b-4a8e-850e-a84003ce5e03",
  "_airbyte_extracted_at": "2025-01-31 12:00:54.301000 UTC",
  "_airbyte_meta": "{\"changes\":[],\"sync_id\":17164}",
  "_airbyte_generation_id": "1",
  "id": "24696859331",
  "deals": null,
  "tickets": null,
  "archived": "false",
  "contacts": "[\"18739801\"]",
  "companies": "[\"10045564374\"]",
  "createdAt": "2024-02-20 08:19:54.168000 UTC",
  "updatedAt": "2024-02-22 17:48:11.364000 UTC"
}]

However the Company ID = 10045564374 is not an active record, because it was merged to 152330328266

If I check Hubspot API directly, I get this ⬇️ getting the correct company ID record 152330328266

HTTP 200

{
  "id": "24696859331",
  "properties": {
    "hs_createdate": "2024-02-20T08:19:54.168Z",
    "hs_lastmodifieddate": "2024-02-22T17:48:11.364Z",
    "hs_object_id": "24696859331"
  },
  "createdAt": "2024-02-20T08:19:54.168Z",
  "updatedAt": "2024-02-22T17:48:11.364Z",
  "archived": false,
  "associations": {
    "companies": {
      "results": [
        {
          "id": "152330328266",
          "type": "meeting_event_to_company"
        }
      ]
    },
    "contacts": {
      "results": [
        {
          "id": "18739801",
          "type": "meeting_event_to_contact"
        }
      ]
    }
  }
}

Of course I was using incremental append, but now I have to use full refresh because of this issue.

There is an open request to support the associations API #45340 since September 9 2024.. I don't know how Airbyte Cloud users are doing, but this is a real important data quality issue, and is surprising that this kind of issue happens for Hubspot, so widely used.