ElliottWilson commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

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:

MEETING_OUTCOME,
PROPERTY_HS_MEETING_OUTCOME as MEETING_OUTCOME,

This causes the sql to fail because of the duplicate.

13:44:52 Completed with 1 error and 0 warnings: 13:44:52
13:44:52 Database Error in model stg_hubspotengagement_meeting (models/stg_hubspotengagement_meeting.sql) 002025 (42S21): SQL compilation error: duplicate column name 'MEETING_OUTCOME' compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_meeting.sql 13:44:52
13:44:52 Done. PASS=1 WARN=0 ERROR=1 SKIP=2 TOTAL=4

Relevant error log or model output

> 13:44:52  Completed with 1 error and 0 warnings:
> 13:44:52  
> 13:44:52    Database Error in model stg_hubspot__engagement_meeting (models/stg_hubspot__engagement_meeting.sql)
>   002025 (42S21): SQL compilation error:
>   duplicate column name 'MEETING_OUTCOME'
>   compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_meeting.sql
> 13:44:52  
> 13:44:52  Done. PASS=1 WARN=0 ERROR=1 SKIP=2 TOTAL=4

Expected behavior

I expect to be able to used the pass through columns on engagements like below:

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"

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: "propertyconcerns" 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

package: fivetran/hubspot 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?

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

fivetran-joemarkiewicz commented 1 year ago

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 hubspot__engagement_meeting_pass_through_columns.

I actually wonder if the root of this issue originates from a combination of the HubSpot connector deprecating the meeting_outcome column and replacing it with property_hs_meeting_outcome; however, the connector did not remove the deprecated column. This then errors out because our package works to remove the property_hs_ prefix from the fields to make them more understandable. This then causes the duplicate column error since the deprecated column still resides in the table.

Would you be able to confirm that the meeting_outcome column in the source doesn't seem to be producing new records and instead the property_hs_meeting_outcome is producing new records? In the meantime, I will loop up with our product team to understand if this is the expected behavior and how we should account for this in the package.

Thanks!

ElliottWilson commented 1 year ago

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 hubspot__engagement_meeting_pass_through_columns.

I actually wonder if the root of this issue originates from a combination of the HubSpot connector deprecating the meeting_outcome column and replacing it with property_hs_meeting_outcome; however, the connector did not remove the deprecated column. This then errors out because our package works to remove the property_hs_ prefix from the fields to make them more understandable. This then causes the duplicate column error since the deprecated column still resides in the table.

Would you be able to confirm that the meeting_outcome column in the source doesn't seem to be producing new records and instead the property_hs_meeting_outcome is producing new records? In the meantime, I will loop up with our product team to understand if this is the expected behavior and how we should account for this in the package.

Thanks!

Thanks @fivetran-joemarkiewicz, this is new issue that started today around 4 hours ago. I can confirm that the meeting_outcome is null in our source data and can be removed. Let me know if I can help here.

greg-finley commented 1 year ago

Hey! I am seeing this same issue, and also for TASK_TYPE too.

16:25:37  Finished running 65 view models in 0 hours 0 minutes and 22.66 seconds (22.66s).
16:25:37  
16:25:37  Completed with 2 errors and 0 warnings:
16:25:37  
16:25:37  Database Error in model stg_hubspot__engagement_meeting (models/stg_hubspot__engagement_meeting.sql)
16:25:37    002025 (42S21): SQL compilation error:
16:25:37    duplicate column name 'MEETING_OUTCOME'
16:25:37    compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_meeting.sql
16:25:37  
16:25:37  Database Error in model stg_hubspot__engagement_task (models/stg_hubspot__engagement_task.sql)
16:25:37    002025 (42S21): SQL compilation error:
16:25:37    duplicate column name 'TASK_TYPE'
16:25:37    compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_task.sql

greg-finley commented 1 year ago

Seems like same issue, task_type is null for every record, but property_hs_task_type is not.

Screen Shot 2023-09-26 at 10 04 00 AM

Probably applies to all of these columns:

Screen Shot 2023-09-26 at 10 05 02 AM

fivetran-joemarkiewicz commented 1 year ago

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 task_type and property_hs_task_type? I just want to confirm if the staging model is doing anything unexpected and what this data looks like at the source.

greg-finley commented 1 year ago

Hey @fivetran-joemarkiewicz yep, the data looks like that in the source. Also stg_hubspot__engagement_task_tmp is just select * from the source table.

Screen Shot 2023-09-26 at 11 35 19 AM

Screen Shot 2023-09-26 at 11 36 12 AM

greg-finley commented 1 year ago

Here is the code for stg_hubspot__engagement_task. You can see it's selecting both TASK_TYPE, and PROPERTY_HS_TASK_TYPE as TASK_TYPE,.

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

fivetran-joemarkiewicz commented 1 year ago

Thanks for sharing more details @greg-finley. Do you know if task_type is a calculated field in your HubSpot environment? It does seem that this error can be tied to an update in the connector which now supports calculated fields.

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.

greg-finley commented 1 year ago

Nah, the property seems to be from Fivetran:

https://fivetran.com/docs/applications/hubspot#crmandsaleshubschema

https://docs.google.com/presentation/d/1KABQnt8WmtZe7u5l7WFUoPIsWzv63P9gsWF79XGLoZE/edit#slide=id.gd207c04e3c_4_5

greg-finley commented 1 year ago

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:

The code manually removes certain columns.
A util function see that you have both PROPERTY_HS_TASK_TYPE as TASK_TYPE, and TASK_TYPE, and removes the one without a realias. Kind of hard to think of an algorithm that would always pick the right column if it's in there twice.

fivetran-joemarkiewicz commented 1 year ago

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 property_hs_ version and a previous non aliased version of a field. I think we may be able to do achieve the second one with the existing code we have in the staging model. Let me try and get something working and share a branch with you.

greg-finley commented 1 year ago

I was tinkering with an idea like this: https://github.com/fivetran/dbt_hubspot_source/commit/eee64f04859cef3ddbe55b033a9fd67e3f78b53f

like remove_column_conflict(columns=default_cols, conflict_pair=('property_hs_task_type', 'task_type'), keep='property_hs_task_type')

if both items in the conflict pair exist, only keep the keep.

But my jinja is not correct

greg-finley commented 1 year ago

If you like how models/stg_hubspot__engagement_task.sql was changed in my commit, I could try to fix up the jinja to actually work (or feel free to send a branch)

EDIT: Heh I am new to jinja so it might be hard for me to solve this myself

fivetran-joemarkiewicz commented 1 year ago

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.

greg-finley commented 1 year ago

So, this simple change does fix it for me: https://github.com/fivetran/dbt_hubspot_source/commit/9f9a6752affd59cf1028c62ffa58b0d5dc1a8335

but ...

Instead of my hardcoded list, seems like there should be a function that looks over the column list and omits like TASK_TYPE if PROPERTY_HS_TASK_TYPE also exists in the list (i.e. any column has its name plus the PROPERTY_HS prefix). Like maybe some customers don't have PROPERTY_HS_TASK_TYPE yet.
Maybe other databases need to handle different casings (usually Snowflake is all uppercase).

fivetran-joemarkiewicz commented 1 year ago

Hi All,

So I believe I may have found a workaround. I chatted with our internal product team and found that task_type in this scenario in fact may be the older version of property_hs_task_type and there is a high chance some customers using the HubSpot connector will want to retain this information for historical records. As such, I don't believe the best option here is to completely remove the column. Instead a coalesce is likely the best option with preference going to property_hs_task_type.

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 fivetran_utils.remove_prefix_from_columns which takes into consideration the possibility of duplicates existing after the prefix is removed. If duplicates are identified, then they are coalesced with preference going to the prefixed field.

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.yml for the following git branch version.

packages:
  - git: https://github.com/fivetran/dbt_hubspot.git
    revision: bugfix/duplicate-columns
    warn-unpinned: false

Let me know your thoughts!

greg-finley commented 1 year ago

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 target/compiled/hubspot_source/models/stg_hubspot__engagement_task.sql still lists both , PROPERTY_HS_TASK_TYPE as TASK_TYPE and , TASK_TYPE but not the coalesce (it's at least doing the leading comma thing, so it's at least partially using your code).

➜  dbt_mozart_data git:(master) ✗ dbt deps && dbt run --models +hubspot__deals +hubspot__deal_history +hubspot__company_history +hubspot__companies +hubspot__deal_stages +hubspot__engagement_meetings +hubspot__contact_lists +hubspot__engagement_tasks +hubspot__engagements +hubspot__companies +hubspot__engagement_notes +hubspot__engagement_emails +hubspot__contacts --exclude stripe_test_v2 stage_views_row_counts

18:35:37  Running with dbt=1.5.4
18:35:53  Installing fivetran/salesforce
18:35:54  Installed from version 0.8.0
18:35:54  Updated version available: 0.9.2
18:35:54  Installing fivetran/salesforce_formula_utils
18:35:54  Installed from version 0.8.2
18:35:54  Updated version available: 0.9.2
18:35:54  Installing fivetran/ad_reporting
18:35:55  Installed from version 1.2.1
18:35:55  Updated version available: 1.5.0
18:35:55  Installing fivetran/stripe
18:35:56  Installed from version 0.9.0
18:35:56  Updated version available: 0.10.1
18:35:56  Installing https://github.com/fivetran/dbt_hubspot.git
18:35:57  Installed from revision bugfix/duplicate-columns
18:35:57  Installing fivetran/social_media_reporting
18:35:57  Installed from version 0.2.0
18:35:57  Up to date!
18:35:57  Installing fivetran/salesforce_source
18:35:58  Installed from version 0.6.0
18:35:58  Updated version available: 0.7.0
18:35:58  Installing fivetran/fivetran_utils
18:35:58  Installed from version 0.4.7
18:35:58  Up to date!
18:35:58  Installing fivetran/apple_search_ads
18:35:59  Installed from version 0.2.2
18:35:59  Up to date!
18:35:59  Installing fivetran/snapchat_ads
18:35:59  Installed from version 0.5.1
18:35:59  Up to date!
18:35:59  Installing fivetran/facebook_ads
18:36:00  Installed from version 0.6.0
18:36:00  Up to date!
18:36:00  Installing fivetran/google_ads
18:36:00  Installed from version 0.9.3
18:36:00  Up to date!
18:36:00  Installing fivetran/pinterest
18:36:01  Installed from version 0.7.1
18:36:01  Updated version available: 0.9.0
18:36:01  Installing fivetran/linkedin
18:36:01  Installed from version 0.6.1
18:36:01  Updated version available: 0.7.0
18:36:01  Installing fivetran/microsoft_ads
18:36:02  Installed from version 0.6.0
18:36:02  Up to date!
18:36:02  Installing fivetran/tiktok_ads
18:36:02  Installed from version 0.3.0
18:36:02  Updated version available: 0.4.0
18:36:02  Installing fivetran/twitter_ads
18:36:03  Installed from version 0.6.0
18:36:03  Up to date!
18:36:03  Installing fivetran/amazon_ads
18:36:03  Installed from version 0.1.0
18:36:03  Updated version available: 0.2.0
18:36:03  Installing fivetran/stripe_source
18:36:04  Installed from version 0.9.3
18:36:04  Up to date!
18:36:04  Installing https://github.com/fivetran/dbt_hubspot_source.git
18:36:05  Installed from revision bugfix/duplicate-columns
18:36:05  Installing calogica/dbt_expectations
18:36:06  Installed from version 0.8.5
18:36:06  Updated version available: 0.10.0
18:36:06  Installing fivetran/facebook_pages
18:36:06  Installed from version 0.2.0
18:36:06  Up to date!
18:36:06  Installing fivetran/instagram_business
18:36:07  Installed from version 0.2.1
18:36:07  Up to date!
18:36:07  Installing fivetran/twitter_organic
18:36:07  Installed from version 0.2.0
18:36:07  Up to date!
18:36:07  Installing fivetran/linkedin_pages
18:36:08  Installed from version 0.2.0
18:36:08  Up to date!
18:36:08  Installing dbt-labs/spark_utils
18:36:08  Installed from version 0.3.0
18:36:08  Up to date!
18:36:08  Installing dbt-labs/dbt_utils
18:36:08  Installed from version 1.1.1
18:36:08  Up to date!
18:36:08  Installing fivetran/apple_search_ads_source
18:36:09  Installed from version 0.2.2
18:36:09  Up to date!
18:36:09  Installing fivetran/snapchat_ads_source
18:36:10  Installed from version 0.5.0
18:36:10  Up to date!
18:36:10  Installing fivetran/facebook_ads_source
18:36:10  Installed from version 0.6.0
18:36:10  Up to date!
18:36:10  Installing fivetran/google_ads_source
18:36:11  Installed from version 0.9.5
18:36:11  Up to date!
18:36:11  Installing fivetran/pinterest_source
18:36:12  Installed from version 0.7.2
18:36:12  Updated version available: 0.9.0
18:36:12  Installing fivetran/linkedin_source
18:36:12  Installed from version 0.6.0
18:36:12  Updated version available: 0.7.0
18:36:12  Installing fivetran/microsoft_ads_source
18:36:13  Installed from version 0.7.0
18:36:13  Up to date!
18:36:13  Installing fivetran/tiktok_ads_source
18:36:13  Installed from version 0.3.0
18:36:13  Updated version available: 0.4.0
18:36:13  Installing fivetran/twitter_ads_source
18:36:14  Installed from version 0.6.0
18:36:14  Up to date!
18:36:14  Installing fivetran/amazon_ads_source
18:36:14  Installed from version 0.1.1
18:36:14  Updated version available: 0.2.0
18:36:14  Installing calogica/dbt_date
18:36:15  Installed from version 0.7.2
18:36:15  Updated version available: 0.9.1
18:36:15  Installing fivetran/facebook_pages_source
18:36:15  Installed from version 0.2.1
18:36:15  Up to date!
18:36:15  Installing fivetran/instagram_business_source
18:36:16  Installed from version 0.2.1
18:36:16  Up to date!
18:36:16  Installing fivetran/twitter_organic_source
18:36:18  Installed from version 0.2.0
18:36:18  Up to date!
18:36:18  Installing fivetran/linkedin_pages_source
18:36:19  Installed from version 0.2.0
18:36:19  Up to date!
18:36:19  
18:36:19  Updates available for packages: ['fivetran/salesforce', 'fivetran/salesforce_formula_utils', 'fivetran/ad_reporting', 'fivetran/stripe', 'fivetran/salesforce_source', 'fivetran/pinterest', 'fivetran/linkedin', 'fivetran/tiktok_ads', 'fivetran/amazon_ads', 'calogica/dbt_expectations', 'fivetran/pinterest_source', 'fivetran/linkedin_source', 'fivetran/tiktok_ads_source', 'fivetran/amazon_ads_source', 'calogica/dbt_date']                 
Update your versions in packages.yml, then run dbt deps
18:36:20  Running with dbt=1.5.4
18:36:21  Registered adapter: snowflake=1.5.2
18:36:21  Unable to do partial parsing because profile has changed
18:36:34  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.facebook_ads_creative_history
18:36:34  Found 300 models, 308 tests, 0 snapshots, 0 analyses, 992 macros, 0 operations, 0 seed files, 126 sources, 0 exposures, 9 metrics, 0 groups
18:36:34  
18:36:37  Concurrency: 8 threads (target='dev')
18:36:37  
18:36:37  1 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__company_property_history_tmp  [RUN]
18:36:37  2 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__company_tmp  [RUN]
18:36:37  3 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list_member_tmp  [RUN]
18:36:37  4 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list_tmp  [RUN]
18:36:37  5 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_tmp  [RUN]
18:36:37  6 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline_stage_tmp  [RUN]
18:36:37  7 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline_tmp  [RUN]
18:36:37  8 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_property_history_tmp  [RUN]
18:36:39  3 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list_member_tmp  [SUCCESS 1 in 1.34s]
18:36:39  7 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline_tmp  [SUCCESS 1 in 1.33s]
18:36:39  1 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__company_property_history_tmp  [SUCCESS 1 in 1.34s]
18:36:39  4 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list_tmp  [SUCCESS 1 in 1.34s]
18:36:39  9 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_stage_tmp  [RUN]
18:36:39  10 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_tmp  [RUN]
18:36:39  11 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_sent_tmp  [RUN]
18:36:39  12 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_status_change_tmp  [RUN]
18:36:39  6 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline_stage_tmp  [SUCCESS 1 in 1.36s]
18:36:39  13 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_tmp  [RUN]
18:36:39  8 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_property_history_tmp  [SUCCESS 1 in 1.40s]
18:36:39  14 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_company_tmp  [RUN]
18:36:39  2 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__company_tmp  [SUCCESS 1 in 1.50s]
18:36:39  15 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_contact_tmp  [RUN]
18:36:39  5 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_tmp  [SUCCESS 1 in 1.75s]
18:36:39  16 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_deal_tmp  [RUN]
18:36:40  12 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_status_change_tmp  [SUCCESS 1 in 1.03s]
18:36:40  17 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_email_tmp  [RUN]
18:36:40  9 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_stage_tmp  [SUCCESS 1 in 1.04s]
18:36:40  18 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_meeting_tmp  [RUN]
18:36:40  11 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_sent_tmp  [SUCCESS 1 in 1.05s]
18:36:40  19 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_note_tmp  [RUN]
18:36:40  15 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_contact_tmp  [SUCCESS 1 in 0.91s]
18:36:40  13 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_tmp  [SUCCESS 1 in 1.04s]
18:36:40  14 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_company_tmp  [SUCCESS 1 in 1.01s]
18:36:40  20 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_task_tmp  [RUN]
18:36:40  21 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_tmp  [RUN]
18:36:40  22 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__owner_tmp  [RUN]
18:36:40  10 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_tmp  [SUCCESS 1 in 1.12s]
18:36:40  23 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list_member  [RUN]
18:36:40  16 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_deal_tmp  [SUCCESS 1 in 0.92s]
18:36:40  24 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__company_property_history  [RUN]
18:36:41  19 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_note_tmp  [SUCCESS 1 in 0.98s]
18:36:41  25 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline  [RUN]
18:36:41  17 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_email_tmp  [SUCCESS 1 in 1.04s]
18:36:41  22 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__owner_tmp  [SUCCESS 1 in 1.00s]
18:36:41  18 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_meeting_tmp  [SUCCESS 1 in 1.03s]
18:36:41  26 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list  [RUN]
18:36:41  27 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline_stage  [RUN]
18:36:41  28 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_property_history  [RUN]
18:36:41  21 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_tmp  [SUCCESS 1 in 1.11s]
18:36:41  29 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__company  [RUN]
18:36:42  20 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_task_tmp  [SUCCESS 1 in 1.11s]
18:36:42  23 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list_member  [SUCCESS 1 in 1.15s]
18:36:42  24 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__company_property_history  [SUCCESS 1 in 1.17s]
18:36:42  30 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact  [RUN]
18:36:42  31 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_status_change  [RUN]
18:36:42  32 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_stage  [RUN]
18:36:42  25 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline  [SUCCESS 1 in 1.23s]
18:36:42  33 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_sent  [RUN]
18:36:42  26 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact_list  [SUCCESS 1 in 1.26s]
18:36:42  34 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_contact  [RUN]
18:36:42  27 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_pipeline_stage  [SUCCESS 1 in 1.28s]
18:36:42  29 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__company  [SUCCESS 1 in 1.17s]
18:36:42  35 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event  [RUN]
18:36:42  36 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_company  [RUN]
18:36:42  28 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_property_history  [SUCCESS 1 in 1.30s]
18:36:42  37 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal  [RUN]
18:36:43  31 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_status_change  [SUCCESS 1 in 1.19s]
18:36:43  38 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_deal  [RUN]
18:36:43  30 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__contact  [SUCCESS 1 in 1.20s]
18:36:43  32 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal_stage  [SUCCESS 1 in 1.20s]
18:36:43  39 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_note  [RUN]
18:36:43  40 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_email  [RUN]
18:36:43  33 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event_sent  [SUCCESS 1 in 1.19s]
18:36:43  41 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__owner  [RUN]
18:36:43  36 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_company  [SUCCESS 1 in 1.21s]
18:36:43  42 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_meeting  [RUN]
18:36:43  37 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__deal  [SUCCESS 1 in 1.20s]
18:36:43  43 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement  [RUN]
18:36:44  34 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_contact  [SUCCESS 1 in 1.36s]
18:36:44  44 of 65 START sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_task  [RUN]
18:36:44  35 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__email_event  [SUCCESS 1 in 1.35s]
18:36:44  45 of 65 START sql view model dbt_transformations_hubspot.hubspot__company_history  [RUN]
18:36:44  39 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_note  [SUCCESS 1 in 1.23s]
18:36:44  46 of 65 START sql view model dbt_transformations_hubspot.hubspot__deal_history  [RUN]
18:36:44  38 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_deal  [SUCCESS 1 in 1.26s]
18:36:44  47 of 65 START sql view model dbt_transformations_hubspot.int_hubspot__contact_merge_adjust  [RUN]
18:36:44  40 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_email  [SUCCESS 1 in 1.32s]
18:36:44  48 of 65 START sql view model dbt_transformations_hubspot.int_hubspot__email_event_aggregates  [RUN]
18:36:44  42 of 65 ERROR creating sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_meeting  [ERROR in 0.93s]
18:36:44  41 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__owner  [SUCCESS 1 in 1.18s]
18:36:44  49 of 65 START sql view model dbt_transformations_hubspot.int_hubspot__deals_enhanced  [RUN]
18:36:45  44 of 65 ERROR creating sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement_task  [ERROR in 1.00s]
18:36:45  43 of 65 OK created sql view model dbt_transformations_stg_hubspot.stg_hubspot__engagement  [SUCCESS 1 in 1.16s]
18:36:45  45 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__company_history  [SUCCESS 1 in 1.03s]
18:36:45  50 of 65 START sql view model dbt_transformations_hubspot.hubspot__engagements . [RUN]
18:36:45  46 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__deal_history  [SUCCESS 1 in 1.12s]
18:36:45  47 of 65 OK created sql view model dbt_transformations_hubspot.int_hubspot__contact_merge_adjust  [SUCCESS 1 in 1.24s]
18:36:45  51 of 65 START sql view model dbt_transformations_hubspot.hubspot__email_event_sent  [RUN]
18:36:45  52 of 65 START sql view model dbt_transformations_hubspot.hubspot__email_event_status_change  [RUN]
18:36:45  48 of 65 OK created sql view model dbt_transformations_hubspot.int_hubspot__email_event_aggregates  [SUCCESS 1 in 1.17s]
18:36:46  49 of 65 OK created sql view model dbt_transformations_hubspot.int_hubspot__deals_enhanced  [SUCCESS 1 in 1.23s]
18:36:46  53 of 65 START sql view model dbt_transformations_hubspot.hubspot__deal_stages . [RUN]
18:36:46  50 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__engagements  [SUCCESS 1 in 1.24s]
18:36:46  54 of 65 START sql view model dbt_transformations_hubspot.hubspot__companies ... [RUN]
18:36:46  55 of 65 START sql view model dbt_transformations_hubspot.hubspot__deals ....... [RUN]
18:36:46  56 of 65 START sql view model dbt_transformations_hubspot.hubspot__engagement_emails  [RUN]
18:36:46  57 of 65 SKIP relation dbt_transformations_hubspot.hubspot__engagement_meetings  [SKIP]
18:36:46  58 of 65 START sql view model dbt_transformations_hubspot.hubspot__engagement_notes  [RUN]
18:36:46  59 of 65 SKIP relation dbt_transformations_hubspot.hubspot__engagement_tasks ... [SKIP]
18:36:46  60 of 65 START sql view model dbt_transformations_hubspot.int_hubspot__engagement_metrics__by_contact  [RUN]
18:36:47  52 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__email_event_status_change  [SUCCESS 1 in 1.33s]
18:36:47  61 of 65 START sql view model dbt_transformations_hubspot.int_hubspot__email_aggregate_status_change  [RUN]
18:36:47  51 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__email_event_sent  [SUCCESS 1 in 1.34s]
18:36:47  54 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__companies  [SUCCESS 1 in 1.36s]
18:36:47  55 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__deals .. [SUCCESS 1 in 1.36s]
18:36:47  53 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__deal_stages  [SUCCESS 1 in 1.51s]
18:36:47  58 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__engagement_notes  [SUCCESS 1 in 1.49s]
18:36:47  56 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__engagement_emails  [SUCCESS 1 in 1.49s]
18:36:47  60 of 65 OK created sql view model dbt_transformations_hubspot.int_hubspot__engagement_metrics__by_contact  [SUCCESS 1 in 1.58s]
18:36:48  61 of 65 OK created sql view model dbt_transformations_hubspot.int_hubspot__email_aggregate_status_change  [SUCCESS 1 in 1.30s]
18:36:48  62 of 65 START sql view model dbt_transformations_hubspot.hubspot__email_sends . [RUN]
18:36:49  62 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__email_sends  [SUCCESS 1 in 1.61s]
18:36:49  63 of 65 START sql view model dbt_transformations_hubspot.hubspot__contacts .... [RUN]
18:36:49  64 of 65 START sql view model dbt_transformations_hubspot.int_hubspot__email_metrics__by_contact_list  [RUN]
18:36:51  64 of 65 OK created sql view model dbt_transformations_hubspot.int_hubspot__email_metrics__by_contact_list  [SUCCESS 1 in 1.75s]
18:36:51  65 of 65 START sql view model dbt_transformations_hubspot.hubspot__contact_lists  [RUN]
18:36:52  63 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__contacts  [SUCCESS 1 in 2.26s]
18:36:54  65 of 65 OK created sql view model dbt_transformations_hubspot.hubspot__contact_lists  [SUCCESS 1 in 2.38s]
18:36:54  
18:36:54  Finished running 65 view models in 0 hours 0 minutes and 19.67 seconds (19.67s).
18:36:54  
18:36:54  Completed with 2 errors and 0 warnings:
18:36:54  
18:36:54  Database Error in model stg_hubspot__engagement_meeting (models/stg_hubspot__engagement_meeting.sql)
18:36:54    002025 (42S21): SQL compilation error:
18:36:54    duplicate column name 'MEETING_OUTCOME'
18:36:54    compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_meeting.sql
18:36:54  
18:36:54  Database Error in model stg_hubspot__engagement_task (models/stg_hubspot__engagement_task.sql)
18:36:54    002025 (42S21): SQL compilation error:
18:36:54    duplicate column name 'TASK_TYPE'
18:36:54    compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_task.sql
18:36:54  
18:36:54  Done. PASS=61 WARN=0 ERROR=2 SKIP=2 TOTAL=65
➜  dbt_mozart_data git:(master) ✗

greg-finley commented 1 year ago

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 dupe.name: {%- for dupe in columns if col.name[prefix|length:]|lower == dupe.name -%}

fivetran-joemarkiewicz commented 1 year ago

@greg-finley thanks for calling that out! I just updated the macro to apply a lower filter in the code. I tested locally and it did the trick to resolve the issue. Would you be able to dbt deps again and see if the issue persists?

greg-finley commented 1 year ago

I think this fixes a subtle bug, and it works after this: https://github.com/fivetran/dbt_hubspot_source/pull/114

fivetran-joemarkiewicz commented 1 year ago

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!

greg-finley commented 1 year ago

Agreed!

fivetran-joemarkiewicz commented 1 year ago

I'm happy to say that the latest version of dbt_hubspot v0.12.0 is now live on the dbt hub with these updates.

Thank you all for helping identify and resolve this issue. ❤️

fivetran / dbt_hubspot

[Bug] Duplicate column on stg_hubspot__engagement_meeting #119

Is there an existing issue for this?

Describe the issue

Relevant error log or model output

Expected behavior

dbt Project configurations

Sales

Service

Columns pass through

Package versions

This package models hubspot data from Fivetran's connector

What database are you using dbt with?

dbt Version

Additional Context

Are you willing to open a PR to help address this issue?