"Private" APP: Scopes issue maybe? Only able to retrieve partial list of Contacts via

[Doug_at_BX]

Hello - ever been in that situation when an employee leaves and the systems start misbehaving? Yep. Happening.

My HS tech goto guru is sailing other seas.

We (I) set up a "private" app via our developer account about 8 months ago.  The app is used by an Azure linked service in our DWH for our nightly ETL extraction jobs.  We extract the entire Contacts "table" from HS - there's no filtering or any type of SQL queries etc that would/should be causing the issue...

Symptoms:

(1) There are ~115K Contacts in HS (from HS I go to the data model section and click on the Contacts object) and I am able to see all of our Contacts being added daily.

(2) Only ~90k Contacts are coming out of the extract (app) process.

(3) The Create Date of these Contacts are from 3/28/24 and prior...  so any Contact added *after* 3/28 are NOT coming out.

(4) Around that magical date - there were some <changes> made to HS with regard to Properties.  I have no idea what was changed.

(5) My purely noob hunch is that New Contacts (post 3/28) have a Property that exists in a Scope that I don't have listed...  and that's why the new Contacts are not being exported via the app-AzureLinkedService connection.

I tried the HS tech support route already.  The ETL job runs nightly without any issues at all.

Current Scopes assigned to the app:

crm.lists.read

crm.objects.companies.read

crm.objects.contacts.read

crm.objects.custom.read

crm.objects.deals.read

crm.objects.goals.read

crm.objects.line_items.read

crm.objects.marketing_events.read

crm.objects.owners.read

crm.objects.quotes.read

crm.schemas.companies.read

crm.schemas.contacts.read

crm.schemas.custom.read

crm.schemas.deals.read

crm.schemas.line_items.read

crm.schemas.quotes.read

QUESTIONS:

(1) Is there a Scope that is akin to an all access mode?  If this ends up being a Scope issue - I don't want the system to break again due to field/Property modifcation

(2) Is there an intermediate layer or configuration that dictates the columns/layout of the "tables" in HS?  When I run the process and allow the Linked Service to dynamically spec and create the DB table, there are (12) columns that are populated from the HubSpot.Contacts "table":

       [Vid]

      ,[Added_At]

      ,[Canonical_Vid]

      ,[Portal_Id]

      ,[Is_Contact]

      ,[Profile_Token]

      ,[Profile_Url]

      ,[Associated_Company_Id]

      ,[Properties_Firstname_Value]

      ,[Properties_Lastname_Value]

      ,[Properties_Company_Value]

      ,[Properties_Last_Modified_Date_Value]

(3) The field "Is_Contact" is a boolean.  It doesn't exist in the Properties of the Contacts object in HS.  It just "appears" when you run it.  Note - we do extract Contact Properties via another ETL but I haven't checked if that set of Properties is for ~90k VIDs or more.

If you're reading this line of my post - then thank you so much for taking the time go through the details.  I really appreciate your help!

Best regards,

Doug

[HubDoPete · Accepted solution]

Hi Doug,

The scopes that you have should allow all contacts and all properties to be accessed and exported. Adding new properties should have no bearing on it, as even the existing contacts will have that new property, as the objects share the same schema.

Hopefully your developer used the export function to extract contacts in bulk

https://developers.hubspot.com/beta-docs/guides/api/crm/exports#exports

Rather than paging through thousands of contacts in batch calls.

I see that the export includes associated company id, so this is pulling from associations not just from the contact object. So the extract must be retrieving multiple objects. Perhaps the integration is adding the "is_contact" boolean as part of its internal object gathering.

This likely doesn't solve it for you but I think it is worth identifying if the integration is using the export function or contact API endpoints so that you know what you are troubleshooting.

best

Pete

[Doug_at_BX]

Hi Pete - 

Thank you for the detailed reply and I apprececiate the Scopes info.  I did notice recently that there is a new "business unit" in our HS schema.  This new BU was likely added as part of the recent integration.  All of the Properties are "owned" by one of the two BUs but all of the Properties are set to "Everyone can view and edit"... which leads me to believe that access is ok.

We have (2) nightly Copy processes.  One Copy is to grab the entire Contacts "table" and the other queries the Contact_Properties_Values "table" for a number of Property Value pairs.  The Contacts "full" copy has the ~90k records.  But interestingly the Property Value pairs job has the full ~120k Contacts (meaning that we retrieve Property Values on all of the Contacts in HS).  Both nightly processes use the exact same linked service (same app).

The "integration" is just the App (via the Developer account) that was associated to an Azure linked service and a Copy pipeline (in Azure Data Factory) which has a source dataset as the HS Contacts table.

Note - the remote table in HS was selected from a dropdown even though the Enter Manually checkbox is selected

I just ran two extracts using the Contacts dataset above but added a where clause for createdate like '2024-03%' and '2024-04%' and only retrieved March 2024 Contacts.  The April clause had zero records.... so there is some sort of block/filter on what Contacts are available - or could there be two instances in HS like an older copy of Contacts and then the current Contacts that is being used?  It's just bizarre (in my opinion)...

Thank you!

[dsmarion]

I don't have an answer for you, but if you don't get an answer I would suggest joining the HubSpot Deveoper Slack and ask over there too.  https://developers.hubspot.com/slack

[Jaycee_Lewis]

Hey, @Doug_at_BX 👋 It looks like the spam filter got agitated by something in your post (you didn't do anything wrong). I moved it back to the community, and you should be good to go as far as getting help from our community.

Talk soon! – Jaycee

[Doug_at_BX]

Thanks so much!