[Bug] Duplicate column on stg_hubspot__engagement_meeting #119
Comments
|
Hi @ElliottWilson thanks for opening this issue. Did the dbt run for you start failing after previously succeeding, or has it always failed with this error message? Additionally, I am a bit confused as I do not believe our package currently has a variable for I actually wonder if the root of this issue originates from a combination of the HubSpot connector deprecating the Would you be able to confirm that the Thanks! |
Thanks @fivetran-joemarkiewicz, this is new issue that started today around 4 hours ago. I can confirm that the |
|
Hey! I am seeing this same issue, and also for |
|
Seems like same issue,
Probably applies to all of these columns:
|
|
Thanks for chiming in @greg-finley that you are also seeing this error. Would you be able to check the raw table as well. What do you see there in regards to the |
|
Hey @fivetran-joemarkiewicz yep, the data looks like that in the source. Also
|
|
Here is the code for with base as (
select *
from prod_mozart_dwh.dbt_transformations_stg_hubspot.stg_hubspot__engagement_task_tmp
), macro as (
select
_fivetran_synced
as
_fivetran_synced
,
_fivetran_deleted
as
_fivetran_deleted
,
engagement_id
as
engagement_id
,
type
as engagement_type ,
property_hs_createdate
as created_timestamp ,
property_hs_timestamp
as occurred_timestamp ,
property_hubspot_owner_id
as owner_id ,
property_hubspot_team_id
as team_id
,BODY,
SUBJECT,
STATUS,
FOR_OBJECT_TYPE,
TASK_TYPE,
COMPLETION_DATE,
IS_ALL_DAY,
PRIORITY,
PROPERTY_HS_TASK_FOR_OBJECT_TYPE as TASK_FOR_OBJECT_TYPE,
PROPERTY_HS_TASK_PRIORITY as TASK_PRIORITY,
PROPERTY_HS_ENGAGEMENT_SOURCE_ID as ENGAGEMENT_SOURCE_ID,
PROPERTY_HS_LASTMODIFIEDDATE as LASTMODIFIEDDATE,
PROPERTY_HS_OBJECT_SOURCE as OBJECT_SOURCE,
PROPERTY_HS_NUM_ASSOCIATED_CONTACTS as NUM_ASSOCIATED_CONTACTS,
PROPERTY_HS_TASK_IS_COMPLETED_EMAIL as TASK_IS_COMPLETED_EMAIL,
PROPERTY_HS_TASK_COMPLETION_DATE as TASK_COMPLETION_DATE,
PROPERTY_HS_NUM_ASSOCIATED_TICKETS as NUM_ASSOCIATED_TICKETS,
PROPERTY_HS_BODY_PREVIEW_HTML as BODY_PREVIEW_HTML,
PROPERTY_HS_TASK_SEND_DEFAULT_REMINDER as TASK_SEND_DEFAULT_REMINDER,
PROPERTY_HS_TASK_IS_COMPLETED_CALL as TASK_IS_COMPLETED_CALL,
PROPERTY_HS_TASK_LAST_SALES_ACTIVITY_TIMESTAMP as TASK_LAST_SALES_ACTIVITY_TIMESTAMP,
PROPERTY_HS_TASK_MISSED_DUE_DATE as TASK_MISSED_DUE_DATE,
PROPERTY_HS_TASK_MISSED_DUE_DATE_COUNT as TASK_MISSED_DUE_DATE_COUNT,
PROPERTY_HS_TASK_STATUS as TASK_STATUS,
PROPERTY_HS_NUM_ASSOCIATED_COMPANIES as NUM_ASSOCIATED_COMPANIES,
PROPERTY_HS_NUM_ASSOCIATED_DEALS as NUM_ASSOCIATED_DEALS,
PROPERTY_HS_TASK_IS_COMPLETED_SEQUENCE as TASK_IS_COMPLETED_SEQUENCE,
PROPERTY_HS_USER_IDS_OF_ALL_OWNERS as USER_IDS_OF_ALL_OWNERS,
PROPERTY_HS_MODIFIED_BY as MODIFIED_BY,
PROPERTY_HS_TASK_CONTACT_TIMEZONE as TASK_CONTACT_TIMEZONE,
PROPERTY_HS_ALL_ACCESSIBLE_TEAM_IDS as ALL_ACCESSIBLE_TEAM_IDS,
PROPERTY_HS_NUM_ASSOCIATED_QUEUE_OBJECTS as NUM_ASSOCIATED_QUEUE_OBJECTS,
PROPERTY_HS_TASK_TYPE as TASK_TYPE,
PROPERTY_HS_BODY_PREVIEW_IS_TRUNCATED as BODY_PREVIEW_IS_TRUNCATED,
PROPERTY_HS_TASK_LAST_CONTACT_OUTREACH as TASK_LAST_CONTACT_OUTREACH,
PROPERTY_HS_TASK_IS_COMPLETED as TASK_IS_COMPLETED,
PROPERTY_HS_TASK_IS_COMPLETED_LINKED_IN as TASK_IS_COMPLETED_LINKED_IN,
PROPERTY_HS_TASK_BODY as TASK_BODY,
PROPERTY_HS_TASK_IS_ALL_DAY as TASK_IS_ALL_DAY,
PROPERTY_HS_UPDATED_BY_USER_ID as UPDATED_BY_USER_ID,
PROPERTY_HS_CREATED_BY_USER_ID as CREATED_BY_USER_ID,
PROPERTY_HS_ENGAGEMENT_SOURCE as ENGAGEMENT_SOURCE,
PROPERTY_HS_BODY_PREVIEW as BODY_PREVIEW,
PROPERTY_HS_TASK_COMPLETION_COUNT as TASK_COMPLETION_COUNT,
PROPERTY_HUBSPOT_OWNER_ASSIGNEDDATE,
PROPERTY_HS_GDPR_DELETED as GDPR_DELETED,
PROPERTY_HS_TASK_IS_OVERDUE as TASK_IS_OVERDUE,
PROPERTY_HS_TASK_FAMILY as TASK_FAMILY,
PROPERTY_HS_ALL_TEAM_IDS as ALL_TEAM_IDS,
PROPERTY_HS_CREATED_BY as CREATED_BY,
PROPERTY_HS_OBJECT_ID as OBJECT_ID,
PROPERTY_HS_TASK_IS_PAST_DUE_DATE as TASK_IS_PAST_DUE_DATE,
PROPERTY_HS_TASK_SUBJECT as TASK_SUBJECT,
PROPERTY_HS_ALL_OWNER_IDS as ALL_OWNER_IDS
from base
)
select *
from macro |
|
Thanks for sharing more details @greg-finley. Do you know if I am wondering if a possible workaround here would be for our staging model to perform a coalesce on the two similar fields as to avoid the duplicate column name. |
|
Nah, the property seems to be from Fivetran: 
https://fivetran.com/docs/applications/hubspot#crmandsaleshubschema |
|
I like your coalesce idea, but I also think maybe some people won't sync the empty "task_type" column in the future and maybe that column won't exist in the source. Some other ideas:
|
|
Hmm the ERD link you shared is for the old HubSpot V2. This makes me think it is a left over from the old version of HubSpot and has since been removed in the new version.
Yeah this is a bit tricky and I think it would be hard to capture all possible types like this where there is a new |
|
I was tinkering with an idea like this: fivetran/dbt_hubspot_source@eee64f0 like if both items in the conflict pair exist, only keep the But my jinja is not correct |
|
If you like how EDIT: Heh I am new to jinja so it might be hard for me to solve this myself |
|
Hey @greg-finley thanks for sharing that! I'll dive further into this tomorrow and let you know if I can use your code in a possible solution. I'll share back once I have something working. |
|
So, this simple change does fix it for me: fivetran/dbt_hubspot_source@9f9a675 but ...
|
|
Hi All, So I believe I may have found a workaround. I chatted with our internal product team and found that With this information, I was able to create a new macro (remove_duplicate_and_prefix_from_columns) in the hubspot_source package that is a fork of the It would be great if you can provide your feedback on this approach and test out the working branch. You can test the working branch by swapping the official dbt hub version of the package in your packages:
- git: https://github.com/fivetran/dbt_hubspot.git
revision: bugfix/duplicate-columns
warn-unpinned: false Let me know your thoughts! |
|
I like the approach, but maybe it's not working? This output seems to be getting packages from your branch, but I still hit the same error. And |
|
I think a testing CSV with uppercase column names might be helpful. Like I think this part and maybe others needs to do a lowercasing on |
|
@greg-finley thanks for calling that out! I just updated the macro to apply a |
|
I think this fixes a subtle bug, and it works after this: fivetran/dbt_hubspot_source#114 |
|
Thanks for creating this PR! I was able to recreate the scenario you positioned and then saw it be resolved with your enhancement. With this, I feel we are ready to take steps for pushing this out to an official release! |
|
Agreed! |
fivetran-joemarkiewicz
added
priority:p2
|
I'm happy to say that the latest version of dbt_hubspot Thank you all for helping identify and resolve this issue. ❤️ |
Is there an existing issue for this?
Describe the issue
My dbt job ran this morning and recived the following error. I have checked the raw sql and there is 2 columns:
This causes the sql to fail because of the duplicate.
Relevant error log or model output
Expected behavior
I expect to be able to used the pass through columns on engagements like below:
dbt Project configurations
Sales
hubspot_sales_enabled: true # Disables all sales models
hubspot_company_enabled: false
hubspot_deal_enabled: true
hubspot_deal_company_enabled: false
hubspot_deal_contact_enabled: true
hubspot_engagement_enabled: true # Disables all engagement models and functionality
hubspot_engagement_contact_enabled: true
hubspot_engagement_company_enabled: false
hubspot_engagement_deal_enabled: true
hubspot_engagement_call_enabled: true
hubspot_engagement_emails_enabled: true
hubspot_engagement_meetings_enabled: true
hubspot_engagement_notes_enabled: true
hubspot_engagement_tasks_enabled: true
hubspot_owner_enabled: true
Service
hubspot_service_enabled: true # Enables all service models
hubspot_ticket_deal_enabled: false
Columns pass through
hubspot__contact_pass_through_columns:
- name: "property_intent_2"
alias: "landing_drop_down"
hubspot__engagement_pass_through_columns:
- name: "PROPERTY_HS_MEETING_OUTCOME"
alias: "PROPERTY_HS_MEETING_OUTCOME"
hubspot__engagements_pass_through_columns:
- name: "PROPERTY_HS_MEETING_OUTCOME"
alias: "PROPERTY_HS_MEETING_OUTCOME"
hubspot__engagement_meeting_pass_through_columns:
- name: "PROPERTY_HS_MEETING_OUTCOME"
alias: "PROPERTY_HS_MEETING_OUTCOME"
hubspot__engagement_meetings_pass_through_columns:
- name: "PROPERTY_HS_MEETING_OUTCOME"
alias: "PROPERTY_HS_MEETING_OUTCOME"
hubspot__deal_pass_through_columns:
- name: "property_closed_lost_reason"
alias: "closed_lost_reason"
- name: "property_concerns_"
alias: "concerns"
- name: "property_priority_ranking"
alias: "priority_ranking"
- name: "property_treatment_start"
alias: "planned_treatment_start_date"
hubspot__ticket_pass_through_columns:
- name: "property_hs_last_message_from_visitor"
alias: "last_message_from_visitor"
- name: "property_source_type"
alias: "source_type"
- name: "property_hs_time_to_first_response_sla_at"
alias: "time_to_first_response_sla_at"
- name: "property_hs_time_to_first_response_sla_status"
alias: "time_to_first_response_sla_status"
- name: "property_time_to_first_agent_reply"
alias: "time_to_first_agent_reply"
- name: "property_time_to_close"
alias: "time_to_close"
- name: "property_priority_reason"
alias: "priority_reason"
- name: "property_category_drill_down"
alias: "category_drill_down"
- name: "property_category_free_text"
alias: "category_free_text"
- name: "property_hs_originating_email_engagement_id"
alias: "originating_email_engagement_id"
Package versions
This package models hubspot data from Fivetran's connector
version: [">=0.11.0", "<0.12.0"]
What database are you using dbt with?
snowflake
dbt Version
Latest 1.6.2
Additional Context
No response
Are you willing to open a PR to help address this issue?
The text was updated successfully, but these errors were encountered: