TimeStamp Adjustment

[rnorthcutt93]

Is there an existing feature request for this?

• [X] I have searched the existing issues

Describe the Feature

We had an issue where a field (property_closedate in hubspot.deals) did not show properly after Fivetran transformation. Introduction: Hubspot stores this property_closedate field as a timestamp. To change the field in Hubspot, a calendar pops up and allow you to select a date; no time information is collected. I think this field uses the time that the selection is made to update the close time. It thens stores this date/time in UTC. Problem: My coworker in Arizona (9 hours behind UTC) updated a close date around 4:00pm local time or so to 03/31/2023. Hubspot took this information somehow converted it to 2023/04/01T01:09.44. In hubspot, this showed as 2023/03/31 (because it knew to convert the timezones), but it didn't show correctly in Fivetran/Redshift. Feature Request: Some variable to set that tells Fivetran what timezone our hubspot instance is on so that hubspot can correctly display the dates.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

• [ ] Yes.
• [X] Yes, but I will need assistance and will schedule time during your office hours for guidance.
• [ ] No.

Anything else?

Thanks for all your help!

[elanfivetran]

Hey @rnorthcutt93, thanks for your engagements with out package and our submitting this feature request.

I definitely understand how it can be frustrating that the closed_at field in the HUBSPOT__DEALS table is different than what you see within the UI. As a Fivetran principal, we attempt to have our connector schema map time's in the same format and timezone that come out from the API.

That being said you are correct that it would make sense for our Data Models to offer the ability to transform the those times/dates based on the user's preference. This is actually a feature we offer in a couple other packages -- here is example of the timezone configuration offered by the Stripe package.

I noticed that you are open to creating a PR for this which is great! One caveat I want to add is that if we add this conversion feature, we would have to transpire it across all instances of times/dates throughout the package, not just the HUBSPOT__DEALS table. If you set up some time within our office hours we would be happy to help you get started. Even if you want to help us out by adding this to the HUBSPOT__DEALS table then we can pick up the rest of the work when our priority allows for it.

[anguswilliams93]

00:06:43 Database Error in model stg_hubspotengagement_task (models/stg_hubspotengagement_task.sql) 00:06:43 000904 (42000): SQL compilation error: error line 1 at position 7 00:06:43 invalid identifier 'COMPLETION_DATE' 00:06:43
00:06:43 Done. PASS=81 WARN=0 ERROR=1 SKIP=1 TOTAL=83

This is a issue within the staging section, is this a known issue?

[fivetran-joemarkiewicz]

Hi @anguswilliams93 thanks for posting the issue you are experiencing. Did the error you are seeing above just start showing on your end, or is this your first time attempting to use the package?

My first thought for this error is that the stg_hubspot__engagement_task model does in fact query the completion_date field from the source table. Are you able to confirm that the completion_date field exists in the engagement_task source table?

[wpride]

Hi team - I've started seeing this as well:

15:14:29.919973 [error] [MainThread]: Database Error in model stg_hubspot__engagement_task (models/stg_hubspot__engagement_task.sql)
15:14:29.920363 [error] [MainThread]:   000904 (42000): SQL compilation error: error line 1 at position 7
15:14:29.920743 [error] [MainThread]:   invalid identifier 'COMPLETION_DATE'

Looking at the Fivetran schemas, it looks like some engagement_task tables have the COMPLETION_DATE field while others do not. For example, a "good" schema:

compared to a "bad" schema:

Is it possible that Fivetran's Hubspot schema has changed, at least in some cases?

[fivetran-joemarkiewicz]

Hi @wpride thanks for joining the thread and sharing that you are experiencing this as well. To me this seems like an unexpected behavior. Unfortunately, I am unaware of a change that rolled out to the HubSpot connector that could be causing this.

I will continue to investigate on my end; however, I would recommend opening a support ticket to have our support team help to triage what may have occurred.

[anguswilliams93]

We had an issue where a field (property_closedate in hubspot.deals) did not show properly after Fivetran transformation. Introduction: Hubspot stores this property_closedate field as a timestamp. To change the field in Hubspot, a calendar pops up and allow you to select a date; no time information is collected. I think this field uses the time that the selection is made to update the close time. It thens stores this date/time in UTC.

Problem: My coworker in Arizona (9 hours behind UTC) updated a close date around 4:00pm local time or so to 03/31/2023. Hubspot took this information somehow converted it to 2023/04/01T01:09.44.

I've noticed that there has been 0 closed deals for my data after this date. Particularly the 2nd of feburary.. we all know dealing with datetime datatypes can be so incredibly fickle. This has nothing to do with dbt transformation, but more a connection issue between hubspot and azure sql server.

[fivetran-jamie]

related inquiry from dbt-slack #tools-fivetran channel: https://getdbt.slack.com/archives/C01D1R2JLLA/p1687242046926269