fivetran/dbt_hubspot

Data models for Hubspot built using dbt.

HubSpot Transformation dbt Package (Docs)

📣 What does this dbt package do?

• Produces modeled tables that leverage HubSpot data from Fivetran's connector in the format described by this ERD and build off the output of our HubSpot source package.
• Enables you to better understand your HubSpot email and engagement performance. The package achieves this by performing the following:
  • Generates models for contacts, companies, and deals with enriched email and engagement metrics.
  • Provides analysis-ready event tables for email and engagement activities.
• Generates a comprehensive data dictionary of your source and modeled HubSpot data through the dbt docs site.

The following table provides a detailed list of all models materialized within this package by default.

TIP: See more details about these models in the package's dbt docs site.

model	description
hubspot__companies	Each record represents a company in Hubspot, enriched with metrics about engagement activities.
hubspot__company_history	Each record represents a change to a company in Hubspot, with valid_to and valid_from information.
hubspot__contacts	Each record represents a contact in Hubspot, enriched with metrics about email and engagement activities.
hubspot__contact_history	Each record represents a change to a contact in Hubspot, with valid_to and valid_from information.
hubspot__contact_lists	Each record represents a contact list in Hubspot, enriched with metrics about email activities.
hubspot__deals	Each record represents a deal in Hubspot, enriched with metrics about engagement activities.
hubspot__deal_stages	Each record represents when a deal stage changes in Hubspot, enriched with metrics about deal activities.
hubspot__deal_history	Each record represents a change to a deal in Hubspot, with valid_to and valid_from information.
hubspot__tickets	Each record represents a ticket in Hubspot, enriched with metrics about engagement activities and information on associated deals, contacts, companies, and owners.
hubspot__daily_ticket_history	Each record represents a ticket's day in Hubspot with tracked properties pivoted out into columns.
hubspot__email_campaigns	Each record represents a email campaign in Hubspot, enriched with metrics about email activities.
hubspot__email_event_*	Each record represents an email event in Hubspot, joined with relevant tables to make them analysis-ready.
hubspot__email_sends	Each record represents a sent email in Hubspot, enriched with metrics about opens, clicks, and other email activity.
hubspot__engagement_*	Each record represents an engagement event in Hubspot, joined with relevant tables to make them analysis-ready.

🎯 How do I use the dbt package?

Step 1: Prerequisites

To use this dbt package, you must have the following:

• At least one Fivetran HubSpot connector syncing data into your destination.
• A BigQuery, Snowflake, Redshift, PostgreSQL, or Databricks destination.

Databricks Dispatch Configuration

If you are using a Databricks destination with this package you will need to add the below (or a variation of the below) dispatch configuration within your dbt_project.yml. This is required in order for the package to accurately search for macros within the dbt-labs/spark_utils then the dbt-labs/dbt_utils packages respectively.

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['spark_utils', 'dbt_utils']

Database Incremental Strategies

Some of the models (+hubspot__daily_ticket_history) in this package are materialized incrementally. We have chosen insert_overwrite as the default strategy for BigQuery and Databricks databases, as it is only available for these dbt adapters. For Snowflake, Redshift, and Postgres databases, we have chosen delete+insert as the default strategy.

insert_overwrite is our preferred incremental strategy because it will be able to properly handle updates to records that exist outside the immediate incremental window. That is, because it leverages partitions, insert_overwrite will appropriately update existing rows that have been changed upstream instead of inserting duplicates of them--all without requiring a full table scan.

delete+insert is our second-choice as it resembles insert_overwrite but lacks partitions. This strategy works most of the time and appropriately handles incremental loads that do not contain changes to past records. However, if a past record has been updated and is outside of the incremental window, delete+insert will insert a duplicate record. 😱

Because of this, we highly recommend that Snowflake, Redshift, and Postgres users periodically run a --full-refresh to ensure a high level of data quality and remove any possible duplicates.

Step 2: Install the package

Include the following hubspot package version in your packages.yml file:

TIP: Check dbt Hub for the latest installation instructions or read the dbt docs for more information on installing packages.

packages:
  - package: fivetran/hubspot
    version: [">=0.17.0", "<0.18.0"] # we recommend using ranges to capture non-breaking changes automatically

Do NOT include the hubspot_source package in this file. The transformation package itself has a dependency on it and will install the source package as well.

Databricks dispatch configuration

If you are using a Databricks destination with this package, you must add the following (or a variation of the following) dispatch configuration within your dbt_project.yml. This is required in order for the package to accurately search for macros within the dbt-labs/spark_utils then the dbt-labs/dbt_utils packages respectively.

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['spark_utils', 'dbt_utils']

Step 3: Define database and schema variables

By default, this package runs using your destination and the hubspot schema. If this is not where your hubspot data is (for example, if your hubspot schema is named hubspot_fivetran), add the following configuration to your root dbt_project.yml file:

vars:
    hubspot_database: your_destination_name
    hubspot_schema: your_schema_name

Step 4: Disable models for non-existent sources

When setting up your Hubspot connection in Fivetran, it is possible that not every table this package expects will be synced. This can occur because you either don't use that functionality in Hubspot or have actively decided to not sync some tables. In order to disable the relevant functionality in the package, you will need to add the relevant variables. By default, all variables are assumed to be true (with exception of hubspot_service_enabled, hubspot_ticket_deal_enabled, hubspot_contact_merge_audit_enabled, and hubspot_merged_deal_enabled). You only need to add variables within your root dbt_project.yml for the tables you would like to disable or enable respectively:

vars:
  # Marketing

  hubspot_marketing_enabled: false                        # Disables all marketing models
  hubspot_contact_enabled: false                          # Disables the contact models
  hubspot_contact_list_enabled: false                     # Disables contact list models
  hubspot_contact_list_member_enabled: false              # Disables contact list member models
  hubspot_contact_property_enabled: false                 # Disables the contact property models
  hubspot_contact_property_history_enabled: false         # Disables the contact property history models
  hubspot_email_event_enabled: false                      # Disables all email_event models and functionality
  hubspot_email_event_bounce_enabled: false
  hubspot_email_event_click_enabled: false
  hubspot_email_event_deferred_enabled: false
  hubspot_email_event_delivered_enabled: false
  hubspot_email_event_dropped_enabled: false
  hubspot_email_event_forward_enabled: false
  hubspot_email_event_open_enabled: false
  hubspot_email_event_print_enabled: false
  hubspot_email_event_sent_enabled: false
  hubspot_email_event_spam_report_enabled: false
  hubspot_email_event_status_change_enabled: false

  hubspot_contact_merge_audit_enabled: true               # Enables the use of the CONTACT_MERGE_AUDIT table (deprecated by Hubspot v3 API) for removing merged contacts in the final models.
                                                          # If false, ~~~contacts will still be merged~~~, but using the CONTACT.property_hs_calculated_merged_vids field (introduced in v3 of the Hubspot CRM API)
                                                          # Default = false

  # Sales

  hubspot_sales_enabled: false                            # Disables all sales models
  hubspot_company_enabled: false
  hubspot_company_property_history_enabled: false         # Disables the company property history models
  hubspot_deal_enabled: false
  hubspot_merged_deal_enabled: true                       # Enables the merged_deal table, which will be used to filter out merged deals from the final deal models. False by default. Note that `hubspot_sales_enabled` and `hubspot_deal_enabled` must not be set to False.
  hubspot_deal_company_enabled: false
  hubspot_deal_contact_enabled: false
  hubspot_deal_property_history_enabled: false            # Disables the deal property history models
  hubspot_engagement_enabled: false                       # Disables all engagement models and functionality
  hubspot_engagement_contact_enabled: false
  hubspot_engagement_company_enabled: false
  hubspot_engagement_deal_enabled: false
  hubspot_engagement_call_enabled: false
  hubspot_engagement_email_enabled: false
  hubspot_engagement_meeting_enabled: false
  hubspot_engagement_note_enabled: false
  hubspot_engagement_task_enabled: false
  hubspot_owner_enabled: false
  hubspot_property_enabled: false                         # Disables property and property_option tables
  
  # Service
  hubspot_service_enabled: true                           # Enables all service/ticket models. Default = false
  hubspot_ticket_deal_enabled: true                       # Default = false

(Optional) Step 5: Additional configurations

Configure email metrics

This package allows you to specify which email metrics (total count and total unique count) you would like to be calculated for specified fields within the hubspot__email_campaigns model. By default, the email_metrics variable below includes all the shown fields. If you would like to remove any field metrics from the final model, you may copy and paste the below snippet within your root dbt_project.yml and remove any fields you want to be ignored in the final model.

vars:
  email_metrics: ['bounces',      #Remove if you do not want metrics in final model.
                  'clicks',       #Remove if you do not want metrics in final model.
                  'deferrals',    #Remove if you do not want metrics in final model.
                  'deliveries',   #Remove if you do not want metrics in final model.
                  'drops',        #Remove if you do not want metrics in final model.
                  'forwards',     #Remove if you do not want metrics in final model.
                  'opens',        #Remove if you do not want metrics in final model.
                  'prints',       #Remove if you do not want metrics in final model.
                  'spam_reports', #Remove if you do not want metrics in final model.
                  'unsubscribes'  #Remove if you do not want metrics in final model.
                  ]

Include passthrough columns

This package includes all source columns defined in the macros folder. We highly recommend including custom fields in this package as models now only bring in a few fields for the company, contact, deal, and ticket tables. You can add more columns using our pass-through column variables. These variables allow for the pass-through fields to be aliased (alias) and casted (transform_sql) if desired, but not required. Datatype casting is configured via a sql snippet within the transform_sql key. You may add the desired sql while omitting the as field_name at the end and your custom pass-though fields will be casted accordingly. Use the below format for declaring the respective pass-through variables in your root dbt_project.yml.

vars:
  hubspot__deal_pass_through_columns:
    - name:           "property_field_new_id"
      alias:          "new_name_for_this_field_id"
      transform_sql:  "cast(new_name_for_this_field as int64)"
    - name:           "this_other_field"
      transform_sql:  "cast(this_other_field as string)"
  hubspot__contact_pass_through_columns:
    - name:           "wow_i_can_add_all_my_custom_fields"
      alias:          "best_field"
  hubspot__company_pass_through_columns:
    - name:           "this_is_radical"
      alias:          "radical_field"
      transform_sql:  "cast(radical_field as string)"
  hubspot__ticket_pass_through_columns:
    - name:           "property_mmm"
      alias:          "mmm"
    - name:           "property_bop"
      alias:          "bop"

Alternatively, if you would like to simply pass through all columns in the above four tables, add the following configuration to your dbt_project.yml. Note that this will override any hubspot__[table_name]_pass_through_columns variables.

vars:
  hubspot__pass_through_all_columns: true # default is false

Adding property label

For property_hs_* columns, you can enable the corresponding, human-readable property_option.label to be included in the staging models.

Important!

• You must have sources property and property_option enabled to enable labels. By default, these sources are enabled.
• You CANNOT enable labels if using hubspot__pass_through_all_columns: true.
• We recommend being selective with the label columns you add. As you add more label columns, your run time will increase due to the underlying logic requirements.

To enable labels for a given property, set the property attribute add_property_label: true, using the below format.

vars:
  hubspot__ticket_pass_through_columns:
    - name: "property_hs_fieldname"
      alias: "fieldname"
      add_property_label: true

Alternatively, you can enable labels for all passthrough properties by using variable hubspot__enable_all_property_labels: true, formatted like the below example.

vars:
  hubspot__enable_all_property_labels: true
  hubspot__ticket_pass_through_columns:
    - name: "property_hs_fieldname1"
    - name: "property_hs_fieldname2"

Including calculated fields

This package also provides the ability to pass calculated fields through to the company, contact, deal, and ticket staging models. If you would like to add a calculated field to any of the mentioned staging models, you may configure the respective hubspot__[table_name]_calculated_fields variables with the name of the field you would like to create, and the transform_sql which will be the actual calculation that will make up the calculated field.

vars:
  hubspot__deal_calculated_fields:
    - name:          "deal_calculated_field"
      transform_sql: "existing_field * other_field"
  hubspot__company_calculated_fields:
    - name:          "company_calculated_field"
      transform_sql: "concat(name_field, '_company_name')"
  hubspot__contact_calculated_fields:
    - name:          "contact_calculated_field"
      transform_sql: "contact_revenue - contact_expense"
  hubspot__ticket_calculated_fields:
    - name:          "ticket_calculated_field"
      transform_sql: "total_field / other_total_field"

Filtering email events

When leveraging email events, HubSpot customers may take advantage of filtering out specified email events. These filtered email events are present within the stg_hubspot__email_events model and are identified by the is_filtered_event boolean field. By default, these events are included in the staging and downstream models generated from this package. However, if you wish to remove these filtered events you may do so by setting the hubspot_using_all_email_events variable to false. See below for exact configurations you may provide in your dbt_project.yml file:

vars:
  hubspot_using_all_email_events: false # True by default

Daily ticket history

The hubspot__daily_ticket_history model is disabled by default, but will materialize if hubspot_service_enabled is set to true. See additional configurations for this model below.

Note: hubspot__daily_ticket_history and its parent intermediate models are incremental. After making any of the below configurations, you will need to run a full refresh.

Tracking ticket properties

By default, hubspot__daily_ticket_history will track each ticket's state, pipeline, and pipeline stage and pivot these properties into columns. However, any property from the source TICKET_PROPERTY_HISTORY table can be tracked and pivoted out into columns. To add other properties to this end model, add the following configuration to your dbt_project.yml file:

vars:
  hubspot__ticket_property_history_columns:
    - the
    - list
    - of 
    - property
    - names

Extending ticket history past closing date

This package will create a row in hubspot__daily_ticket_history for each day that a ticket is open, starting at its creation date. A Hubspot ticket can be altered after being closed, so its properties can change after this date.

By default, the package will track a ticket up to its closing date (or the current date if still open). To capture post-closure changes, you may want to extend a ticket's history past the close date. To do so, add the following configuration to your root dbt_project.yml file:

vars:
  hubspot:
    ticket_history_extension_days: integer_number_of_days # default = 0

Changing the Build Schema

By default this package will build the HubSpot staging models within a schema titled (<target_schema> + _stg_hubspot) and HubSpot final models within a schema titled (<target_schema> + hubspot) in your target database. If this is not where you would like your modeled HubSpot data to be written to, add the following configuration to your root dbt_project.yml file:

models:
    hubspot:
      +schema: my_new_schema_name # leave blank for just the target_schema
    hubspot_source:
      +schema: my_new_schema_name # leave blank for just the target_schema

Change the source table references

If an individual source table has a different name than the package expects, add the table name as it appears in your destination to the respective variable:

IMPORTANT: See this project's dbt_project.yml variable declarations to see the expected names.

vars:
    hubspot_<default_source_table_name>_identifier: your_table_name

(Optional) Step 6: Orchestrate your models with Fivetran Transformations for dbt Core™

Expand for details

Fivetran offers the ability for you to orchestrate your dbt project through Fivetran Transformations for dbt Core™. Learn how to set up your project for orchestration through Fivetran in our Transformations for dbt Core™ setup guides.

🔍 Does this package have dependencies?

This dbt package is dependent on the following dbt packages. Please be aware that these dependencies are installed by default within this package. For more information on the following packages, refer to the dbt hub site.

IMPORTANT: If you have any of these dependent packages in your own packages.yml file, we highly recommend that you remove them from your root packages.yml to avoid package version conflicts.

packages:
    - package: fivetran/hubspot_source
      version: [">=0.14.0", "<0.15.0"]

    - package: fivetran/fivetran_utils
      version: [">=0.4.0", "<0.5.0"]

    - package: dbt-labs/dbt_utils
      version: [">=1.0.0", "<2.0.0"]

    - package: dbt-labs/spark_utils
      version: [">=0.3.0", "<0.4.0"]

🙌 How is this package maintained and can I contribute?

Package Maintenance

The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.

Contributions

A small team of analytics engineers at Fivetran develops these dbt packages. However, the packages are made better by community contributions!

We highly encourage and welcome contributions to this package. Check out this dbt Discourse article on the best workflow for contributing to a package!

🏪 Are there any resources available?

• If you have questions or want to reach out for help, please refer to the GitHub Issue section to find the right avenue of support for you.
• If you would like to provide feedback to the dbt package team at Fivetran or would like to request a new dbt package, fill out our Feedback Form.
• Have questions or want to be part of the community discourse? Create a post in the Fivetran community and our team along with the community can join in on the discussion!

dbt_hubspot's Issues

Understanding pass through columns

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

In staging model for Companies, we have a field property_annualrevenue.

Today I used hubspot__company_pass_through_columns to only bring columns I need to the intermediate models. I added the following:

hubspot__company_pass_through_columns:
  - name: "property_annualrevenue"
    alias: "annualrevenue"

But even with the above code, the resulting column in hubspot__companies is company_annual_revenue. I don't understand few things:

1. Is the word "property" automatically stripped out?
2. Why isn't alias I chose used?
3. Why is the word "company" added to that field?

Relevant error log or model output

No response

Expected behavior

The column name is exactly as in alias

dbt Project configurations

  hubspot__company_pass_through_columns:
    - name: "property_annual_revenue"
      alias: "annual_revenue"

Package versions

packages:
  - package: dbt-labs/audit_helper
    version: 0.9.0
  - package: fivetran/hubspot
    version: [">=0.9.0", "<0.10.0"]

What database are you using dbt with?

snowflake

dbt Version

$ dbt --version
Core:
  - installed: 1.5.0
  - latest:    1.5.0 - Up to date!

Plugins:
  - bigquery:  1.5.1 - Up to date!
  - postgres:  1.5.0 - Up to date!
  - redshift:  1.5.1 - Up to date!
  - snowflake: 1.5.0 - Up to date!

Additional Context

No response

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

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

TimeStamp Adjustment

Is there an existing feature request for this?

• 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.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

Thanks for all your help!

[Bug] The hubspot__contacts calls upon the contact_merge_audits even though the table has been deprecated from the connector

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Per the HubSpot Release Notes, Fivetran deprecated the CONTACT_MERGE_AUDIT table.

However, in the hubspot__contacts.sql model that exists in this package the CONTACT_MERGE_AUDIT table is still being referenced if hubspot_contact_merge_audit_enabled is marked as True in dbt_project.yml. Since most users moving forward will not have the CONTACT_MERGE_AUDIT in their schema, hubspot_contact_merge_audit_enabled should have a default of False.

Relevant error log or model output

No response

Expected behavior

Since most users moving forward will not have the CONTACT_MERGE_AUDIT in their schema, hubspot_contact_merge_audit_enabled should have a default of False.

dbt Project configurations

They are configured to the default settings.

Package versions

n/a

What database are you using dbt with?

bigquery

dbt Version

n/a

Additional Context

No response

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

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

`engagement_tasks` model trying to pull missing column `probability_to_complete`.

Thank you for the great work done on this package. There appears to be a schema-mismatch issue that's preventing this package to be usable right now.

I can confirm that the probability_to_complete is not present in the current schema for the engagement_tasks table. The fivetran connection was created a week ago with no all schemas enabled.

The problem also is simply having hubspot_engagement_tasks_enabled: false doesn't fix it at all, the model still gets materialized. The only way I was able to get it to work was setting hubspot_sales_enabled: false.

See the generated SQL below for the stg_hubspot__engagement_task model:

with base as (

    select *
    from `PROJECT`.`DATASET`.`engagement_task`

), fields as (

    select
        _fivetran_synced,
        body as task_note,
        completion_date as completion_timestamp,
        engagement_id,
        for_object_type,
        is_all_day,
        priority,
        probability_to_complete,
        status as task_status,
        subject as task_subject,
        task_type
    from base
    
)

select *
from fields;

`hubspot_engagement_call_enabled` variable misspelled on README

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The hubspot_engagement_call_enabled variable is misspelled as hubspot_engagement_calls_enabled in the README in this section: hubspot_engagement_call_enabled

Removing the "s" fixed this issue since that is how it is spelled in the hubpost__engagement_calls.sql model:

{{ config(enabled=fivetran_utils.enabled_vars(['hubspot_sales_enabled','hubspot_engagement_call_enabled','hubspot_engagement_enabled'])) }}

{{ engagements_joined(var('engagement_call')) }}

Relevant error log or model output

No response

Expected behavior

Since we do not use the engagement_call table I would expect that setting hubspot_engagement_calls_enabled: false would result in all associated models not being created.

dbt Project configurations

hubspot_engagement_call_enabled: false works
hubspot_engagement_calls_enabled: false does nothing

Package versions

• package: fivetran/hubspot
  version: [">=0.6.0", "<0.7.0"]

What database are you using dbt with?

snowflake

dbt Version

Core:

• installed: 1.2.2
• latest: 1.3.0 - Update available!

Your version of dbt-core is out of date!
You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:

• snowflake: 1.2.0 - Update available!

Additional Context

No response

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

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

Ability to disable specific tests

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

In our project, we sometimes want to avoid running certain tests depending on the context (target.name) and/or the location of the test (e.g. in an installed package). To do that, we expected dbt_project.yml to help us conditionally disable tests (enbaled = false).

Expectation is that the following pattern will disable a specific tests for a specific model.

tests:
  my_project:
    intermediate:
      my_sub_folder:
        my_model:
          +enabled: false

Describe alternatives you've considered

We have considered forking dbt_hubspot, to then disable specific tests.

Are you interested in contributing this feature?

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

Anything else?

No response

Typo's in readme

I've noticed a typo in the readme that I'm quite happy to fix if I get the rights to create a PR.
hubspot_email_event_spam_report should be hubspot_email_event_spam_report_enabled . As the typo is in the VARs that likely will be copied by folks it would be good to fix.
Also this line is a duplicate: hubspot_email_event_click_enabled: false

[Bug] dbt Cloud PR builds fail when hubspot models are disabled and I run `dbt build --select state:modified+`

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I'd like to temporarily disable model creation for the hubspot package, or disable it in pre-production environments. In my dbt_project.yml

'
models:
hubspot:
+enabled: false
hubspot_source:
+enabled: false
`

Relevant error log or model output

------------------------------------------------------------
  Invoke dbt Command
------------------------------------------------------------
dbt build --select state:modified+

19:39:48  Running with dbt=1.3.2
19:39:48  Partial parse save file not found. Starting full parse.
Compilation Error in model stg_hubspot__company_tmp (models/tmp/stg_hubspot__company_tmp.sql)
  Model 'model.hubspot_source.stg_hubspot__company_tmp' (models/tmp/stg_hubspot__company_tmp.sql) depends on a source named 'hubspot.company' which is disabled

Expected behavior

Expected no errors and no hubspot models created.

dbt Project configurations

hubspot_email_event_forward_enabled: false
hubspot_email_event_print_enabled: false
hubspot_sales_enabled: false
hubspot_service_enabled: false

Package versions

packages:

• package: dbt-labs/dbt_project_evaluator
  version: 0.4.1
• package: dbt-labs/dbt_utils
  version: 1.0.0
• package: fivetran/hubspot
  version: 0.8.0

What database are you using dbt with?

snowflake

dbt Version

1.3

Additional Context

No response

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

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

[Feature] Ability to filter out is_filtered_event events

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

In HubSpot, there is the ability to filter out events. Ideally, you could have the ability to filter out these events and any upstream models that have this flag within the dbt_project.yml file.

Describe alternatives you've considered

Unfortunately, there isn't another alternative. I did attend office hours and spoke to the team and we agreed this might make sense as a feature.

Are you interested in contributing this feature?

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

Anything else?

No response

Freshness checks fail for sources whose tables are excluded from sync

We have disabled the email events with hubspot_email_event_enabled: false because we're not syncing them. The freshness test doesn't seem to have been disabled though as we get this error message:

Database Error in source email_event (models/src_hubspot.yml)
  relation "ft_hubspot.email_event" does not exist

Is there a way to stop the table from being freshness tested? I can't overwrite it with null like I could if I controlled the schema.yml file.

We also disabled another table (contact_property_history) after initially enabling it, and I suspect that this table will also start appearing as stale sometime soon.

[Bug] valid_to within hubspot__company_history needs to be adjusted

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The current logic for the valid_to field within the hubspot__company_history model is as follows:

dbt_hubspot/models/sales/history/hubspot__company_history.sql

Line 17 in 2c8c15a

lead(change_timestamp) over (partition by company_id order by change_timestamp) as valid_to

However, this model is intended to represent the historical view of the change of properties. Therefore, we will want to instead adjust the partition to include both company_id and field_name to appropriately capture the historical changes of the company properties.

Relevant error log or model output

N/A

Expected behavior

The model captures the proper valid_from and valid_to for each historical property change for the hubspot company.

dbt Project configurations

N/A

Package versions

packages:

• package: fivetran/hubspot
  version: [">=0.5.0", "<0.6.0"]

What database are you using dbt with?

postgres, redshift, snowflake, bigquery

dbt Version

Core:

• installed: 1.1.0
• latest: 1.1.0 - Up to date!

Plugins:

• bigquery: 1.1.0 - Up to date!

Additional Context

N/A

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

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

engagement_meeting.meeting_outcome Missing from source table

We need to figure out a sophisticate way to account for these scenarios. As a short-term solution, we can remove this field from the model.

Disable all Ticket models

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

I would like to disable all ticket models

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

_fivetran_deleted filtering issues

I noticed that the hubspot\models\marketing\hubspot__contacts.sql wasn't producing any results and after doing some digging see that hubspot_source\models\stg_hubspot__contact.sql is filtering on where _fivetran_deleted is null.

As I am writing this - I did a quick search in the Release Notes for this connector and see that CONTACT table tracks deletions as of Aug 2020 and we added this a few weeks ago - so that would be why. 😄

So now that some tables support the _fivetran_deleted column, is it possible for this to be null for any table? I reviewed a couple of the tables not supported and also did not see results when filtering out null. I think in my experience with Fivetran connectors as soon as support is added (even if not fully) it will no longer show null even in unsupported tables?

email_metrics should not be redefined in email_campaigns

redefining the email_metrics var in email_campaigns causes the top level var to get ignored and hence causes failures when the user wants to disable for example unsubscribes from the dbt_project.yml.

dbt_hubspot/models/marketing/hubspot__email_campaigns.sql

Line 3 in 522c29b

{% set email_metrics = [

removing the email_metrics declaration above will fix the issue I blieve. And using var('email_metrics') instead.

BUG - Duplicate Contacts from Merged Contacts

Are you a current Fivetran customer?

Issue identified by Marion Pavillet

Describe the bug

Our integration to the datawarehouse is done with fivetran. Deletions are caught properly with a deleted flag that is correctly ignored by the package.
Everything runs smoothly except for some tests related to contacts being unique in the email_events aggregated table:

unique_hubspot__email_event_clicks_event_id.sql
unique_hubspot__email_event_delivered_event_id.sql
unique_hubspot__email_event_opens_event_id.sql
unique_hubspot__email_event_sent_event_id.sql
unique_hubspot__email_event_status_change_event_id.sql
unique_hubspot__email_sends_event_id.sql

I did some investigation and it is due to contacts being merged in Hubspot. These contacts are not flagged as either deleted or inactive. Thus, there are duplicates in the email address (which is used in a JOIN clause and leads to duplicates downstream - specifically this SQL hubspot__email_event_sent.sql).

with base as (
    select *
    from database.schema_stg_hubspot.stg_hubspot__email_event_sent
), events as (
    select *
    from database.schema_stg_hubspot.stg_hubspot__email_event
), contacts as (
    select *
    from database.schema_stg_hubspot.stg_hubspot__contact
), events_joined as (
    select 
        base.*,
        events.created_timestamp,
        events.email_campaign_id,
        events.recipient_email_address,
        events.sent_timestamp as email_send_timestamp,
        events.sent_by_event_id as email_send_id
    from base
    left join events
        using (event_id)
), contacts_joined as (
    select 
        events_joined.*,
        contacts.contact_id
    from events_joined
    left join contacts
        on events_joined.recipient_email_address = contacts.email ---THIS IS WHERE IT IS MESSED UP
)
select *
from contacts_joined

Expected behavior

The merged contact is the contact that is used in transformations and not the older contacts.

BUG - Upgrading Hubspot/Hubspot Source to 0.5.0: Test issues

Are you a current Fivetran customer?
Name: Erica Louie
Title: Head of Data
Company: dbt Labs

Describe the bug

1. In the stg_hubspot_engagements.ymlfile, there are two models with tests that do not exist in the package ([see file here](https://github.com/fivetran/dbt_hubspot_source/blob/main/models/stg_hubspot__engagement.yml#L137-L171)) a. stg_hubspot__engagement_email_ccb.stg_hubspot__engagement_email_to`
2. We've disabled several of the package's models, but I'm seeing the tests are still coming through for these disabled models (see below)

00:23:17  Running with dbt=1.0.1-rc1
00:23:17  Partial parse save file not found. Starting full parse.
00:23:29  [WARNING]: Did not find matching node for patch with name 'stg_hubspot__engagement_email_cc' in the 'models' section of file 'models/stg_hubspot__engagement.yml'
00:23:29  [WARNING]: Did not find matching node for patch with name 'stg_hubspot__engagement_email_to' in the 'models' section of file 'models/stg_hubspot__engagement.yml'
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__deal_pipeline_stage_deal_pipeline_stage_id.ba90fa6113' (models/stg_hubspot__deal.yml) depends on a node named 'stg_hubspot__deal_pipeline_stage' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__deal_pipeline_stage_deal_pipeline_stage_id.d53656553e' (models/stg_hubspot__deal.yml) depends on a node named 'stg_hubspot__deal_pipeline_stage' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__deal_pipeline_deal_pipeline_id.dff6c45403' (models/stg_hubspot__deal.yml) depends on a node named 'stg_hubspot__deal_pipeline' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__deal_pipeline_deal_pipeline_id.8a0e3cd7a3' (models/stg_hubspot__deal.yml) depends on a node named 'stg_hubspot__deal_pipeline' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__deal_deal_id.ff48693e20' (models/stg_hubspot__deal.yml) depends on a node named 'stg_hubspot__deal' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__deal_deal_id.ac1628ac69' (models/stg_hubspot__deal.yml) depends on a node named 'stg_hubspot__deal' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__contact_list_contact_list_id.036739c1b0' (models/stg_hubspot__contact.yml) depends on a node named 'stg_hubspot__contact_list' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__contact_list_contact_list_id.ff8e4cc29a' (models/stg_hubspot__contact.yml) depends on a node named 'stg_hubspot__contact_list' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__email_event_deferred_event_id.2f779e2912' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_deferred' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__email_event_deferred_event_id.80293078e0' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_deferred' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__email_event_dropped_event_id.40706bbf71' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_dropped' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__email_event_dropped_event_id.c92d449f52' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_dropped' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__email_event_forward_event_id.7f133d2dd1' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_forward' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__email_event_forward_event_id.b9a8bf63b3' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_forward' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__email_event_print_event_id.40a8ccd3f3' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_print' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__email_event_print_event_id.94d4e09b3b' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_print' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__email_event_spam_report_event_id.448320447a' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_spam_report' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__email_event_spam_report_event_id.2a56aced8f' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_spam_report' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__email_event_status_change_event_id.13105203e0' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_status_change' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__email_event_status_change_event_id.c5e80297b1' (models/stg_hubspot__email.yml) depends on a node named 'stg_hubspot__email_event_status_change' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__engagement_call_engagement_id.5ceb3917bf' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_call' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__engagement_call_engagement_id.43b4413603' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_call' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__engagement_email_engagement_id.175561ecca' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_email' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__engagement_email_engagement_id.5eeb06dcfc' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_email' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__engagement_meeting_engagement_id.18dd11dc28' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_meeting' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__engagement_meeting_engagement_id.70721fb830' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_meeting' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__engagement_note_engagement_id.244059891e' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_note' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__engagement_note_engagement_id.e2eaf5256e' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_note' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__engagement_task_engagement_id.0bbd752d82' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_task' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__engagement_task_engagement_id.0f578bc80c' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement_task' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__engagement_engagement_id.b26f4d328f' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__engagement_engagement_id.4f054187b0' (models/stg_hubspot__engagement.yml) depends on a node named 'stg_hubspot__engagement' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__ticket_ticket_id.78c6fbfcaf' (models/stg_hubspot__ticket.yml) depends on a node named 'stg_hubspot__ticket' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__ticket_ticket_id.299e7c4c53' (models/stg_hubspot__ticket.yml) depends on a node named 'stg_hubspot__ticket' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.unique_stg_hubspot__company_company_id.394a2a4ba0' (models/stg_hubspot__company.yml) depends on a node named 'stg_hubspot__company' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot_source.not_null_stg_hubspot__company_company_id.0d0a080d43' (models/stg_hubspot__company.yml) depends on a node named 'stg_hubspot__company' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__deals_deal_id.bdbfe47fcd' (models/sales/sales.yml) depends on a node named 'hubspot__deals' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__deals_deal_id.d728fe5f71' (models/sales/sales.yml) depends on a node named 'hubspot__deals' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__deal_stages_deal_stage_id.162d2d204b' (models/sales/sales.yml) depends on a node named 'hubspot__deal_stages' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__deal_stages_deal_stage_id.7fd3732373' (models/sales/sales.yml) depends on a node named 'hubspot__deal_stages' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__companies_company_id.a58ceac3f2' (models/sales/sales.yml) depends on a node named 'hubspot__companies' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__companies_company_id.687ec98e97' (models/sales/sales.yml) depends on a node named 'hubspot__companies' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__engagements_engagement_id.59b32bc0d7' (models/sales/sales.yml) depends on a node named 'hubspot__engagements' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__engagements_engagement_id.01d5c3ee5d' (models/sales/sales.yml) depends on a node named 'hubspot__engagements' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__company_history_id.f1af964b1f' (models/sales/history/history.yml) depends on a node named 'hubspot__company_history' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__company_history_id.33035793ff' (models/sales/history/history.yml) depends on a node named 'hubspot__company_history' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__deal_history_id.1cb93fca79' (models/sales/history/history.yml) depends on a node named 'hubspot__deal_history' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__deal_history_id.a1c6cd6a75' (models/sales/history/history.yml) depends on a node named 'hubspot__deal_history' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__engagement_calls_engagement_id.00f8d8357f' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_calls' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__engagement_calls_engagement_id.972572ce6c' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_calls' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__engagement_emails_engagement_id.13a39d1e09' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_emails' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__engagement_emails_engagement_id.b18e2acbde' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_emails' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__engagement_meetings_engagement_id.83a791c3df' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_meetings' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__engagement_meetings_engagement_id.3098aabcd1' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_meetings' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__engagement_notes_engagement_id.76434ac965' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_notes' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__engagement_notes_engagement_id.c9864b5001' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_notes' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__engagement_tasks_engagement_id.67738794ae' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_tasks' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__engagement_tasks_engagement_id.754d36b939' (models/sales/engagement_events/engagement_events.yml) depends on a node named 'hubspot__engagement_tasks' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__contact_lists_contact_list_id.ea9ef03bac' (models/marketing/marketing.yml) depends on a node named 'hubspot__contact_lists' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__contact_lists_contact_list_id.3bde1bb891' (models/marketing/marketing.yml) depends on a node named 'hubspot__contact_lists' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__email_event_deferred_event_id.bb75dcb83a' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_deferred' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__email_event_deferred_event_id.2c6decaa91' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_deferred' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__email_event_dropped_event_id.093040860b' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_dropped' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__email_event_dropped_event_id.5cfe4eeb95' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_dropped' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__email_event_forward_event_id.064956aaa7' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_forward' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__email_event_forward_event_id.417a73da08' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_forward' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__email_event_print_event_id.548f6cece7' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_print' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__email_event_print_event_id.9dac8a147e' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_print' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__email_event_spam_report_event_id.20aab81ec6' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_spam_report' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__email_event_spam_report_event_id.5ddca8771a' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_spam_report' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__email_event_status_change_event_id.2e5b2b72e2' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_status_change' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__email_event_status_change_event_id.a1737e44b3' (models/marketing/email_events/email_events.yml) depends on a node named 'hubspot__email_event_status_change' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_hubspot__contact_history_id.aef69ae1ec' (models/marketing/history/history.yml) depends on a node named 'hubspot__contact_history' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_hubspot__contact_history_id.eaae22e088' (models/marketing/history/history.yml) depends on a node named 'hubspot__contact_history' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_int_hubspot__email_aggregate_status_change_email_send_id.7c03e87c05' (models/marketing/intermediate/intermediate.yml) depends on a node named 'int_hubspot__email_aggregate_status_change' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_int_hubspot__email_aggregate_status_change_email_send_id.16bf74a3bc' (models/marketing/intermediate/intermediate.yml) depends on a node named 'int_hubspot__email_aggregate_status_change' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_int_hubspot__email_metrics__by_contact_list_contact_list_id.095d72d5b8' (models/marketing/intermediate/intermediate.yml) depends on a node named 'int_hubspot__email_metrics__by_contact_list' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_int_hubspot__email_metrics__by_contact_list_contact_list_id.1d3cf4caf2' (models/marketing/intermediate/intermediate.yml) depends on a node named 'int_hubspot__email_metrics__by_contact_list' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.unique_int_hubspot__engagement_metrics__by_contact_contact_id.1c156b64ed' (models/marketing/intermediate/intermediate.yml) depends on a node named 'int_hubspot__engagement_metrics__by_contact' which is disabled
00:23:30  [WARNING]: Test 'test.hubspot.not_null_int_hubspot__engagement_metrics__by_contact_contact_id.4a800df06b' (models/marketing/intermediate/intermediate.yml) depends on a node named 'int_hubspot__engagement_metrics__by_contact' which is disabled
00:23:31  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 2 unused configuration paths:
- tests.hubspot_source.stg_hubspot__engagement_email_cc
- tests.hubspot_source.stg_hubspot__engagement_email_to

Steps to reproduce

1. Upgrade to package version 0.5.0
2. Disable a majority of the models
3. dbt clean && dbt deps && dbt seed -- you'll get a slew of the warnings above

Expected behavior
These tests should skip over if the models are disabled, I think (?) 👀

Project variables configuration

name: 'fishtown_internal_analytics'
profile: "garage-snowflake"
version: '1.0'
require-dbt-version: ">=1.0.0"
config-version: 2

model-paths: ["models"]
analysis-paths: ["analysis"]
target-path: "target"
clean-targets: ["target", "dbt_modules", "dbt_packages"]
test-paths: ["tests"]
seed-paths: ["data"]
macro-paths: ["macros"]
asset-paths: ["assets"]

vars:
.....

  zendesk_schema: fivetran_zendesk
  zendesk_database: raw
  using_schedules:            False         #Disable if you are not using schedules
  using_user_tags:            False         #Disable if you are not using user tags
  using_organization_tags:    False         #Disable if you are not using organization tags

  hubspot_source:
    hubspot_database: raw
    hubspot_schema: fivetran_hubspot

  # Marketing
  hubspot_marketing_enabled: true                        # Disables all marketing models
  hubspot_contact_enabled: true                          # Disables the contact models
  hubspot_contact_list_enabled: false                     # Disables contact list models
  hubspot_contact_list_member_enabled: false              # Disables contact list member models
  hubspot_contact_property_enabled: false                 # Disables the contact property models
  hubspot_email_event_enabled: true                      # Disables all email_event models and functionality
  hubspot_email_event_bounce_enabled: true
  hubspot_email_event_delivered_enabled: true
  hubspot_email_event_click_enabled: true
  hubspot_email_event_opens_enabled: true
  hubspot_email_event_sent_enabled: true

  hubspot_email_event_deferred_enabled: false
  hubspot_email_event_dropped_enabled: false
  hubspot_email_event_forward_enabled: false
  hubspot_email_event_print_enabled: false
  hubspot_email_event_spam_report_enabled: false
  hubspot_email_event_status_change_enabled: false
  
  hubspot_contact_merge_audit_enabled: true               # Enables contact merge auditing to be applied to final models (removes any merged contacts that are still persisting in the contact table)

  # Sales

  hubspot_sales_enabled: false                            # Disables all sales models
  hubspot_company_enabled: false
  hubspot_deal_enabled: false
  hubspot_deal_company_enabled: false
  hubspot_engagement_enabled: false                       # Disables all engagement models and functionality
  hubspot_engagement_contact_enabled: false
  hubspot_engagement_company_enabled: false
  hubspot_engagement_deal_enabled: false
  hubspot_engagement_calls_enabled: false
  hubspot_engagement_emails_enabled: false
  hubspot_engagement_meetings_enabled: false
  hubspot_engagement_notes_enabled: false
  hubspot_engagement_tasks_enabled: false

  hubspot_service_enabled: false                       # Disables all sales models

  email_metrics: ['bounces',
                'clicks',
                'deliveries',
                'opens']

# enable custom fields to be passed through
  hubspot__contact_pass_through_columns:
    - name: "property_salesforcecontactid"
      alias: "salesforce_contact_id"

.....

Package Version

packages:
  - package: dbt-labs/audit_helper
    version: 0.5.0
  - package: dbt-labs/codegen
    version: 0.5.0
  - package: dbt-labs/dbt_utils
    version: 0.8.0
  - package: dbt-labs/snowplow
    version: 0.14.0
  - package: fivetran/hubspot
    version: 0.5.0
  - package: fivetran/zendesk_source
    version: 0.5.0

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Feature] Include property labels alongside property internal values

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Any property in HubSpot has an internal value and a label. Especially for properties provided by HubSpot (e.g. Persona), the label is much easier to interpret than the internal value (see screenshot). Hence, In many cases, it's not the internal value but the property label that you'd want to surface in your reports and dashboards.

As the current dbt-hubspot plugin is not passing through the property labels from the property_option table, I'd suggest adding a use_property_label option to the pass-through column configuration, e.g.:

hubspot__contact_pass_through_columns:
    - name: property_hs_persona
      alias: persona
      use_property_label: true

Describe alternatives you've considered

The only alternative that seems reasonable is to build custom dbt models that join the property labels from the property_option table and the hubspot_{contacts,companies,deals} tables together, based on property_option.value

Are you interested in contributing this feature?

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

Anything else?

No response

[Bug] Duplicate column on stg_hubspot__engagement_meeting

Is there an existing issue for this?

• 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:

1. MEETING_OUTCOME,
2. 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_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

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

• 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.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[Bug] hubspot_engagement_contact_enabled needs to be taken into consideration within hubspot__contacts model

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Currently if you are using the hubspot__contacts end model, it offers the ability to turn on/off the engagements portions using this config.

When inspecting the code block that leverages this config you can see it is pulling from the int_hubspot__engagement_metrics__by_contact model. However, upon inspecting that intermediate model you can see the model is dependent on the following variables.

dbt_hubspot/models/marketing/intermediate/int_hubspot__engagement_metrics__by_contact.sql

Line 1 in 6b1900d

{{ config(enabled=fivetran_utils.enabled_vars(['hubspot_sales_enabled','hubspot_engagement_enabled','hubspot_engagement_contact_enabled'])) }}

As you can see there is an inconsistency where the intermediate model is able to be disabled by the hubspot_engagement_contact_enabled variable. Whereas the end model does not take this additional variable into consideration.

We will need to update the hubspot__contacts end model to take the hubspot_engagement_contact_enabled variable into consideration. If we do not, a customer may have the intermediate model disabled, but not the end model. Which will cause a runtime failure.

Relevant error log or model output

13:52:29  Running with dbt=1.5.1
13:52:29  Unable to do partial parsing because saved manifest not found. Starting full parse.
13:52:34  Encountered an error:
Compilation Error
  Model 'model.hubspot.hubspot__contacts' (models/marketing/hubspot__contacts.sql) depends on a node named 'int_hubspot__engagement_metrics__by_contact' which is disabled

Expected behavior

The package is able to run without any errors regardless of the variable configurations.

dbt Project configurations

dbt=1.5.1

Package versions

v0.14.0

What database are you using dbt with?

snowflake

dbt Version

1.5.1

Additional Context

No response

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

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

FEATURE - Adjust Join Conditions

Are you a Fivetran customer?

Fivetran created Feature Request

Is your feature request related to a problem? Please describe.

The customer that raised Issue #61 found a join condition that leveraged using was not working as intended due to a datatype error. The datatype error was able to be resolved within the source package. However, we should practice using left join for our join conditions going forward as it is more accurate across warehouse types.

I would like for the team to refactor the joins used throughout this package to leverage left join opposed to using so they may be more accurate across warehouses and can avoid possible bugs in the future.

BUG - Typo in email_sends model

Are you a current Fivetran customer?

Brandon Thomson, Marketing Analytics Engineer @ dbt Labs

Describe the bug

This is a simple issue - in the email_sends model the field was_deferred is spelled was_deffered. By fixing this typo, the field will be consistent across other models.

Steps to reproduce

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

N/A

Expected behavior

N/A

Project variables configuration

Package Version

  - package: fivetran/hubspot_source
    version: [">=0.3.0", "<0.4.0"]

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency

This isn't urgent at all. Just wanted to make the team aware of it because it does create some small confusion.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] Missing column in intermediate model if variable is set

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

When running project with var hubspot_email_event_status_change_enabled: false, it fails with Database Error

Problem is on this line:

dbt_hubspot/models/marketing/hubspot__email_sends.sql

Line 43 in 7d76e94

{% if fivetran_utils.enabled_vars(['hubspot_email_event_status_change_enabled']) %}

When column unsubscribes is not created, it should not be accessed, too. It can be fixed by using custom email_metrics array in dbt_project.yml, but it is impossible to figure it out without looking into the code of package

Relevant error log or model output

09:39:53  Database Error in model hubspot__email_campaigns (models/marketing/hubspot__email_campaigns.sql)
09:39:53    Name unsubscribes not found inside email_sends at [65:25]
09:39:53    compiled Code at target/run/hubspot/models/marketing/hubspot__email_campaigns.sql
09:39:53  
09:39:53  Database Error in model int_hubspot__email_metrics__by_contact_list (models/marketing/intermediate/int_hubspot__email_metrics__by_contact_list.sql)
09:39:53    Unrecognized name: unsubscribes at [74:13]
09:39:53    compiled Code at target/run/hubspot/models/marketing/intermediate/int_hubspot__email_metrics__by_contact_list.sql
09:39:53  
09:39:53  Database Error in model hubspot__contacts (models/marketing/hubspot__contacts.sql)
09:39:53    Unrecognized name: unsubscribes at [112:13]
09:39:53    compiled Code at target/run/hubspot/models/marketing/hubspot__contacts.sql

Expected behavior

Run does not fail with Database Error

dbt Project configurations

vars:
hubspot_email_event_status_change_enabled: false

Package versions

packages:

• package: fivetran/hubspot
  version: 0.9.0

What database are you using dbt with?

bigquery

dbt Version

1.3.1

Additional Context

No response

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

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

FEATURE - Ability to shut off history tables individually.

Are you a Fivetran customer?
Brett Kobold, Director of Analytics @ Retina.AI

Is your feature request related to a problem? Please describe.
Kinda. I would like the option to disable the history tables. I currently have them turned off in Fivetran to save on rows as I do not need them at the moment for the type of reporting I am doing.

Describe the solution you'd like
I would love a pattern similar to exactly how the other tables are enabled but just for the history tables. Unless those tables are need to build the core tables which I am unaware of at the moment. The only way I say to shut them off was to turn of the full table.

Describe alternatives you've considered
I have thought about just deleteing or commenting of those models in my local repo.

Additional context

Please indicate the level of urgency and business impact of this request
Not urgent but is just says my DBT run has failed because I do not have the history tables.

Are you interested in contributing to this package?

• [X ] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this work implemented.

Models still created when `_enabled: false` is set

Hi!

We have this in our dbt_project.yml:

vars:
  hubspot_source:
...
    hubspot_marketing_enabled: false                        # Disables all marketing models
    hubspot_email_event_enabled: false                      # Disables all email_event models and functionality
...

But these are still created.

[Bug] Include Owner variable to disable int_hubspot__deals_enhanced model

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Currently the int_hubspot__deals_enhanced (and downstream models) are dependent on the following variables: ['hubspot_sales_enabled','hubspot_deal_enabled']. However, the intermediate model uses the owner source and the hubspot_owner_enabled variable should also be incorporated in the enable/disablement of the model(s)

Relevant error log or model output

Partial parse save file not found. Starting full parse.
14:15:19
Encountered an error:
Compilation Error in model int_hubspot__deals_enhanced (models/sales/intermediate/int_hubspot__deals_enhanced.sql)
Model 'model.hubspot.int_hubspot__deals_enhanced' (models/sales/intermediate/int_hubspot__deals_enhanced.sql) depends on a node named 'stg_hubspot__owner' which is disabled

Expected behavior

The models run properly even if the owner source is not included in the source schema.

dbt Project configurations

vars:
   hubspot_owner_enabled: false

Package versions

packages:
  - package: fivetran/hubspot
    version: [">=0.15.0", "<0.16.0"]

What database are you using dbt with?

bigquery

dbt Version

1.3.2

Additional Context

We should also check if there are other variables that need to be considered across the other intermediate/end models.

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

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

[Feature] Union Feature Enhancement

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Apply the same union feature that is available in other packages (like the ad reporting packages) to the HubSpot package.

Describe alternatives you've considered

There are no real alternatives at the moment.

Are you interested in contributing this feature?

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

Anything else?

No response

Broken Links to Models in README

It seems the links within the README that point to the models are broken and not pointing to the correct models within the sales and marketing folders.

FEATURE - databricks and postgres compatibility

Are you a Fivetran customer?

nope!

Is your feature request related to a problem? Please describe.

No, just want to roll out postgres + databricks compatibility for our most popular packages.

Describe the solution you'd like

make it compatible with both!

Please indicate the level of urgency and business impact of this request

Does not seem urgent, but did come out of a customer request on dbt slack

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this work implemented.
• No, I'd prefer if someone else did this. I don't have the time and/or don't know how to incorporate the changes necessary.

[Feature] <title>Overwrite dbt_project.yml

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

I need to be able to overwrite the default settings for database and schema from dbt_project.yml. I keep my development transformations in different database and use this line:
+database: "{{ env_var('DBT_SNOWFLAKE_ENV') }}" but I can't apply it to the package?

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

[Feature] Include Description for HubSpot Custom Properties in dbt Models

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

📍 Current state

Although it is possible to include pass through columns in the configuration to retrieve hubspot custom properties, the package currently does not pass through the description of custom properties defined in HubSpot into the dbt models. This missing information can be crucial for better understanding the context and usage of custom properties, especially when analyzing the data or sharing documentation.

⚙️ Feature Request

We would love, and be eternally grateful 😄, if the dbt HubSpot package could include the description of custom properties from HubSpot in the generated dbt models.

🧬 Expected Benefits

• Improved Data Understanding: Having property descriptions readily available in the dbt models will provide users with better insights into the meaning and purpose of each custom property, facilitating more effective data analysis.
• Enhanced Data Documentation: The property descriptions will be available through the dbt docs site, enabling users to access a comprehensive data dictionary for both the source and modeled HubSpot data.

Describe alternatives you've considered

• Generate the corresponding yml file for the hubspot table (e.g. hubspot__deals) and input the descriptions for each pass through columns.

🧪 Other implementation considerations:

• The property descriptions can be retrieved through the HubSpot API when fetching metadata for custom properties.
• The descriptions can be included in the dbt models as part of the corresponding field's metadata or as an additional column in the data model.

Are you interested in contributing this feature?

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

Anything else?

No response

[Bug] Merged deals don't get remove in final models

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

• [expected] Merged contacts automatically get removed and collapsed into calculated_merged_vids in the final models (using the CONTACT.property_hs_calculated_merged_vids column)
• [expected] Merged companies can be manually filtered from final models, using the is_deleted column
• [unexpected] Merged deals don't get removed in final models and can't be filtered, which causes deals to still stick around even though they are "stale"

Relevant error log or model output

No response

Expected behavior

Adopt the same behavior as for contacts: merged deals should get removed in final models (using the merged_deal table) and the IDs of merged deals should get collapsed into a calculated_merged_vids column

dbt Project configurations

Running with dbt=1.7.4
dbt version: 1.7.4
python version: 3.10.12
python path: /home/vscode/venv/bin/python
os info: Linux-5.15.49-linuxkit-aarch64-with-glibc2.31
Using profiles dir at /workspace/dbt
Using profiles.yml file at /workspace/dbt/profiles.yml
Using dbt_project.yml file at /workspace/dbt/dbt_project.yml
adapter type: snowflake
adapter version: 1.7.1
Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]
Required dependencies:
 - git [OK found]

Package versions

packages:
  - package: dbt-labs/dbt_external_tables
    version: "0.8.5"
  - package: fivetran/hubspot
    version: [">=0.15.0", "<0.16.0"]
  - package: dbt-labs/dbt_utils
    version: "1.1.1"
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

What database are you using dbt with?

snowflake

dbt Version

1.7.4

Additional Context

No response

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

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

Feature Request - Deal_stage modeling

This will be added to the next release of Hubspot:

Model DEAL_STAGE (which has History Mode) in both Hubspot source & transformation packages.

Deal Pipeline Stage Label is duplicated

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Here is an extract for a deal that has two different stages:

The stage_ids are properly mapped in stg_hubspot__deal_pipeline_stage

But then, hubspot__deal_stages shows the same id for both stages

Relevant error log or model output

No response

Expected behavior

The first row should have Pipeline_stage_label = New and Deal_pipeline_stage_id = 6309924

dbt Project configurations

vars:
  hubspot_source:
    hubspot_database: FIVETRAN
    hubspot_schema: hubspot
  hubspot_email_event_print_enabled: false
  hubspot_email_event_forward_enabled: false
  hubspot_contact_merge_audit_enabled: true
  hubspot__pass_through_all_columns: true

Package versions

packages:

• package: dbt-labs/audit_helper
  version: 0.5.0
• package: fivetran/hubspot
  version: [">=0.5.0", "<0.6.0"]

What database are you using dbt with?

snowflake

dbt Version

1.1.1

Additional Context

No response

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

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

BUG - hubspot_engagment_task CASTING error

Are you a current Fivetran customer?
Hello name name is Brett Kobold and I am currently a customer of Fivetran under the account Retina.AI

Describe the bug
When I run the models, I get an error for the hubspot_engagment_table. I have pasted the error below. I am not sure what coloum is failing to CAST and I believe I do not have custom properies on engagments.

Database Error in model stg_hubspot__engagement_task (models/stg_hubspot__engagement_task.sql)�
  001065 (22023): SQL compilation error:
  Function TRY_CAST cannot be used with arguments of types TIMESTAMP_TZ(9) and TIMESTAMP_NTZ(9)
  compiled SQL at target/run/hubspot_source/models/stg_hubspot__engagement_task.sql

Steps to reproduce
Run FiveTran for hubspot tables, excluding any history tables to redunce number of calls.
Install the hubspot DBT package.
dbt_run on DBT cloud.
Get error that can't TRY_CAST timestamps.
Go check Snowflake to see if tables were created.

Expected behavior
I expected the engagement tasks table to be created, currently I do not see that in my Snowflake instance.

Project variables configuration

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'retina_rev_ana'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'default'

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
    - "target"
    - "dbt_modules"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  retina_rev_ana:
      # Applies to all files under models/example/
      example:
          materialized: view

vars:
  hubspot_source:
    hubspot_database: PC_FIVETRAN_DB
    hubspot_schema: HUBSPOT
    hubspot__pass_through_all_columns: true
    hubspot__email_event_forward_enabled: false
    hubspot__email_event_print_enabled: false

Package Version

packages:
  - package: fivetran/hubspot
    version: 0.4.1

Warehouse

• BigQuery
• Redshift
• [ X] Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency
This isn't critical but I would like to have all the tables built as I will be doing reporting on SDRs this week and their different engagements within Hubspot

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• [ X] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

I just started working with DBT but would be happy to help in the future on development. I know SQL.

[Feature] Add custom properties to the base models

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

How to add custom properties to, for example, Deal object. The package creates a transformation with basic values only

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

BUG - email_events_joined macro joins on email address which is not unique in contact

Are you a current Fivetran customer?
Dominic, Data Engineer at Rezdy

Describe the bug
The macro email_events_joined has the below join

    from events_joined
    left join contacts
        on events_joined.recipient_email_address = contacts.email

We have duplicate emails in contacts (stg_hubspot__contact).
These emails have different contact ids but the email address is the same.
Although an edge case there doesn't appear to be a rule against it

Steps to reproduce

1. Have two different hubspot contacts with the same email address (these will be loaded into stg_hubspot__contact) in hubspot
2. Make sure there are emails for the contact
3. Just run and test the dbt_hubspot package normally it should create duplicate events in the specific email type tabes such as hubspot__email_event_dropped hubspot__email_event_opens However the original email table stg_hubspot__email_event will pass on it's unique test.

To get bad rows:

WITH duplicated_emails AS (
select
    email,
    count(*) as n_records

from `rezdy-dwh`.`dbt_dominic_stg_hubspot`.`stg_hubspot__contact`
where email is not null
group by email
having count(*) > 1
)


SELECT * 
FROM  `rezdy-dwh`.`dbt_dominic_hubspot`.`hubspot__email_event_bounce`
WHERE event_id IN (SELECT event_id FROM `rezdy-dwh`.`dbt_dominic_dbt_test__audit`.`unique_hubspot__email_event_bounce_event_id`)
AND recipient_email_address in (SELECT email FROM duplicated_emails )

Expected behavior
A join on contact id rather than the email address to produce a unique grain

Project variables configuration

not relevant to this issue

Package Version

  - package: fivetran/hubspot_source
    version: 0.4.2

  - package: fivetran/hubspot
    version: 0.4.1

Warehouse

• [ x] BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
thats pretty much it ey

Screenshots

Please indicate the level of urgency
Annoying but not critical, its creating a bunch of failing tests but we will likely fork the package and solve with a ROW_NUMBER() /QUALIFY to get the unique grain

Are you interested in contributing to this package?

• [ X ] Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

QUESTION - what does the Campaign ContentId relate to?

Are you a Fivetran customer?
Yes, Chistoph, Sustain.life

Your Question
I'm seeing content_id in the campaign
- name: content_id
description: The ID of the content.

What is this referring to? I would have expected a table with contents which i don't see. My hope is to see the email body somewhere?

Additional context
see above

Please indicate the level of urgency and business impact of this request
medium... I'd like to avoid our dev team building a custom call into hubspot just to get the email body.

Unique assertions are failing for certain tables

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I'm getting failures on some assertions for uniqueness in event_id columns.

Relevant error log or model output

Completed with 7 errors and 0 warnings:�
06:44:05  Failure in test unique_hubspot__email_event_clicks_event_id (models/marketing/email_events/email_events.yml)�
06:44:05    Got 54 results, configured to fail if != 0
06:44:05    compiled SQL at target/compiled/hubspot/models/marketing/email_events/email_events.yml/unique_hubspot__email_event_clicks_event_id.sql
06:44:05  Failure in test unique_hubspot__email_event_deferred_event_id (models/marketing/email_events/email_events.yml)�
06:44:05    Got 1 result, configured to fail if != 0
06:44:05    compiled SQL at target/compiled/hubspot/models/marketing/email_events/email_events.yml/unique_hubspot__email_event_deferred_event_id.sql
06:44:05  Failure in test unique_hubspot__email_event_delivered_event_id (models/marketing/email_events/email_events.yml)�
06:44:05    Got 179 results, configured to fail if != 0
06:44:05    compiled SQL at target/compiled/hubspot/models/marketing/email_events/email_events.yml/unique_hubspot__email_event_delivered_event_id.sql
06:44:05  Failure in test unique_hubspot__email_event_dropped_event_id (models/marketing/email_events/email_events.yml)�
06:44:05    Got 3 results, configured to fail if != 0
06:44:05    compiled SQL at target/compiled/hubspot/models/marketing/email_events/email_events.yml/unique_hubspot__email_event_dropped_event_id.sql
06:44:05  Failure in test unique_hubspot__email_event_sent_event_id (models/marketing/email_events/email_events.yml)�
06:44:05    Got 179 results, configured to fail if != 0
06:44:05    compiled SQL at target/compiled/hubspot/models/marketing/email_events/email_events.yml/unique_hubspot__email_event_sent_event_id.sql
06:44:05  Failure in test unique_hubspot__email_event_opens_event_id (models/marketing/email_events/email_events.yml)�
06:44:05    Got 404 results, configured to fail if != 0
06:44:05    compiled SQL at target/compiled/hubspot/models/marketing/email_events/email_events.yml/unique_hubspot__email_event_opens_event_id.sql
06:44:05  Failure in test unique_hubspot__email_event_status_change_event_id (models/marketing/email_events/email_events.yml)�
06:44:05    Got 28 results, configured to fail if != 0
06:44:05    compiled SQL at target/compiled/hubspot/models/marketing/email_events/email_events.yml/unique_hubspot__email_event_status_change_event_id.sql

Expected behavior

No Failures

dbt Project configurations

vars:
  hubspot_source:
    hubspot_database: FIVETRAN
    hubspot_schema: hubspot
  hubspot_email_event_print_enabled: false
  hubspot_email_event_forward_enabled: false

Package versions

packages:
  - package: dbt-labs/audit_helper
    version: 0.5.0
  - package: fivetran/hubspot
    version: [">=0.5.0", "<0.6.0"]

What database are you using dbt with?

snowflake

dbt Version

1.1.1

Additional Context

The tmp table shows correct values when I run the following:

SELECT
  COUNT(*),
  COUNT(DISTINCT id)
FROM <db>.dbt_dawid_base_hubspot.stg_hubspot__email_event_click_tmp

but then this assertion shows 54 event ids with 2 rows

select
    event_id as unique_field,
    count(*) as n_records
from <db>.dbt_dawid_hubspot.hubspot__email_event_clicks
where event_id is not null
group by event_id
having count(*) > 1

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

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

BUG - alias not working

Are you a current Fivetran customer?
Yes.
Mark Hansen
Consultant
Edcura

Describe the bug
alias fails to rename the hubspot__contact fields 'first_name' and ''last_name' to 'firstname' and 'lastname'

Steps to reproduce

1. Add the following code to dbt_project.yml

hubspot__contact_pass_through_columns:
    - name: "first_name"
      alias: "firstname"
    - name: "last_name"
      alias: "lastname"

2. Run dbt -m hubspot --full-refresh
3. Example the table hubspot__contacts
4. You will see the table has columns 'first_name' and 'last_name' rather than 'firstname' and 'lastname'

Expected behavior
The column 'first_name' should have become 'firstname' and the column 'last_name' should have become 'lastname'

Project variables configuration

name: 'hubspot'
version: '0.3.3'
config-version: 2
require-dbt-version: [">=0.18.0", "<0.20.0"]

vars:
  hubspot:
    contact: "{{ ref('stg_hubspot__contact') }}"
    contact_list: "{{ ref('stg_hubspot__contact_list') }}"
    contact_list_member: "{{ ref('stg_hubspot__contact_list_member') }}"
    contact_property_history: "{{ ref('stg_hubspot__contact_property_history') }}"

    company: "{{ ref('stg_hubspot__company') }}"
    company_property_history: "{{ ref('stg_hubspot__company_property_history') }}"

    deal: "{{ ref('stg_hubspot__deal') }}"
    deal_stage: "{{ ref('stg_hubspot__deal_stage') }}"
    deal_company: "{{ ref('stg_hubspot__deal_company') }}"
    deal_pipeline: "{{ ref('stg_hubspot__deal_pipeline') }}"
    deal_pipeline_stage: "{{ ref('stg_hubspot__deal_pipeline_stage') }}"
    deal_property_history: "{{ ref('stg_hubspot__deal_property_history') }}"

    owner: "{{ ref('stg_hubspot__owner') }}"

    email_event_bounce: "{{ ref('stg_hubspot__email_event_bounce') }}"
    email_event_click: "{{ ref('stg_hubspot__email_event_click') }}"
    email_event_deferred: "{{ ref('stg_hubspot__email_event_deferred') }}"
    email_event_delivered: "{{ ref('stg_hubspot__email_event_delivered') }}"
    email_event_dropped: "{{ ref('stg_hubspot__email_event_dropped') }}"
    email_event_forward: "{{ ref('stg_hubspot__email_event_forward') }}"
    email_event_open: "{{ ref('stg_hubspot__email_event_open') }}"
    email_event_print: "{{ ref('stg_hubspot__email_event_print') }}"
    email_event_sent: "{{ ref('stg_hubspot__email_event_sent') }}"
    email_event_spam_report: "{{ ref('stg_hubspot__email_event_spam_report') }}"
    email_event_status_change: "{{ ref('stg_hubspot__email_event_status_change') }}"
    email_event: "{{ ref('stg_hubspot__email_event') }}"

    engagement: "{{ ref('stg_hubspot__engagement') }}"
    engagement_call: "{{ ref('stg_hubspot__engagement_call') }}"
    engagement_company: "{{ ref('stg_hubspot__engagement_company') }}"
    engagement_contact: "{{ ref('stg_hubspot__engagement_contact') }}"
    engagement_deal: "{{ ref('stg_hubspot__engagement_deal') }}"
    engagement_email_cc: "{{ ref('stg_hubspot__engagement_email_cc') }}"
    engagement_email_to: "{{ ref('stg_hubspot__engagement_email_to') }}"
    engagement_email: "{{ ref('stg_hubspot__engagement_email') }}"
    engagement_meeting: "{{ ref('stg_hubspot__engagement_meeting') }}"
    engagement_note: "{{ ref('stg_hubspot__engagement_note') }}"
    engagement_task: "{{ ref('stg_hubspot__engagement_task') }}"

    email_metrics: [
                    'bounces',
                    'clicks',
                    'deferrals',
                    'deliveries',
                    'drops',
                    'forwards',
                    'opens',
                    'prints',
                    'spam_reports',
                    'unsubscribes'
                    ]
  hubspot__contact_pass_through_columns:
    - name: "first_name"
      alias: "firstname"
    - name: "last_name"
      alias: "lastname"


models:
  hubspot:
    +materialized: table
    +schema: hubspot
    marketing:
      intermediate:
        +materialized: ephemeral
    sales:
      intermediate:
        +materialized: ephemeral

Package Version

packages:
  - package: fivetran/hubspot_source
    version: [">=0.3.0", "<0.4.0"]

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency
Urgent. Dashboarding is broken since upgrading to latest version of fivetran/hubspot_source

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Feature] Leverage macro to test uniqueness of models

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

With the removal of dbt_expectations dependencies in PR #118 , we should create a macro that tests uniqueness of primary keys in the models, based on the given conditions

"not coalesce(is_x_deleted, false)"

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

[Bug] Poor performance on int_hubspot__contact_merge_adjust

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

With the changes that came in v0.9.0 our project has seen a increase in run times with the HubSpot package. It looks like int_hubspot__contact_merge_adjust is the issue, and specifically the merge_contacts macro that is driving the decrease in performance. We've seen run times range between 15-35 minutes.

Relevant error log or model output

No response

Expected behavior

I don't expect the query to run as long as it does.

dbt Project configurations

vars:
  hubspot_source:
    hubspot_schema: hubspot_fivetran 
  hubspot_email_event_forward_enabled: false
  hubspot_email_event_print_enabled: false
  hubspot_email_event_spam_report_enabled: false
  hubspot_service_enabled: false
  hubspot_contact_property_enabled: false
  hubspot__pass_through_all_columns: true

Package versions

  - package: fivetran/hubspot
    version: [">=0.9.0", "<0.10.0"]

What database are you using dbt with?

redshift

dbt Version

1.3

Additional Context

My guess is the merge_contacts query is suboptimal in redshift. I played around with a few solutions that sped up my run:

• I filtered the return set of numbers so that the generated number was <= (select max(json_array_length(json_serialize(split_to_array(calculated_merged_vids, ';')), true)) from contacts). I added an additional subquery to numbers to achieve this, but I also considered doing this by setting the upper_bound variable using the run_query macro.
• I also filtered contacts where calculated_merged_vids is not null. I'm not sure this achieved a significant boost but calculated_merged_vids is populated for <.01% of our contacts.

The query time averaged a little over a minute with the changes.

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

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

[Feature] update dbt-expectations version range

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

The latest version out of dbt-expectations is 0.9.0 and this package will force folks to pin to older versions

informed by fivetran/dbt_google_ads_source#44

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

[Bug] DBT docs are not rendered

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

FYI dbt_hubspot documentation isn't rendered in the browser. Cleared cache, and tried in various different browsers.
Link: https://fivetran.github.io/dbt_hubspot/

Relevant error log or model output

No response

Expected behavior

expect the docs to be rendered

dbt Project configurations

NA

Package versions

NA

What database are you using dbt with?

other (mention it in "Additional Context")

dbt Version

NA

Additional Context

No response

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

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

Deal ID duplication in hubspot_deals model

We recently attempted an upgrade to 0.3.0 and noticed that during our CI run, a schema test was suddenly failing:

Failure in test unique_hubspot__deals_deal_id (models/sales/sales.yml)
  Got 5 results, expected 0.
  compiled SQL at target/compiled/hubspot/models/sales/sales.yml/schema_test/unique_hubspot__deals_deal_id.sql

After digging into the test failure, we noticed that the deal_id duplication was stemming from these lines of code. It looks like this logic is a new addition in the 0.3.0 release.

It seems like there is an assumption baked in to the modeling logic here that a Hubspot Company and a Hubspot Deal have a 1:1 relationship. In reality, we have multiple instances where a deal_id is assigned to multiple companies. Could this logic be updated to reflect this reality?

stg_hubspot__engagement_email depreciated columns breaking build

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

There's an error with depreciated fields containing a '-' are breaking the build. Example: property_hs_all-deprecated-127cf554-5d2f-4c78-b3fc-ac0986bce102

Relevant error log or model output

11:49:53  Database Error in model stg_hubspot__engagement_email (models/stg_hubspot__engagement_email.sql)
11:49:53    syntax error at or near "-"
11:49:53    compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_email.sql

Expected behavior

No error to be thrown and model built.

dbt Project configurations

hubspot_source:
# If there is a need to enable a hubspot source, ensure its enabled at both
hubspot_database: "beehive"
hubspot_schema: "fivetran_hubspot"
# Disabled unused hubspot source models
# enable company model for testing temporarily
# hubspot_deal_company_enabled: false
hubspot_deal_contact_enabled: false
hubspot_deal_enabled: false
hubspot_email_event_forward_enabled: false
hubspot_email_event_print_enabled: false
hubspot_engagement_deal_enabled: false
hubspot_engagement_task_enabled: false
stg_hubspot__engagement_email: false

hubspot:
hubspot_database: "beehive"
hubspot_schema: "fivetran_hubspot"
# Disabled unused hubspot models
# enable company model for testing temporarily
# hubspot_company_enabled: false
hubspot_deal_company_enabled: false
hubspot_deal_contact_enabled: false
hubspot_deal_enabled: false
hubspot_email_event_forward_enabled: false
hubspot_email_event_print_enabled: false
hubspot_engagement_deal_enabled: false
hubspot_engagement_task_enabled: false
stg_hubspot__engagement_email: false

email_metrics tracked for hubspot

email_metrics: ['bounces', 'clicks', 'deferrals', 'deliveries', 'drops',
'forwards', 'opens', 'prints', 'spam_reports',
'suppresses', 'processes']

Package versions

• package: fivetran/hubspot
  version: [">=0.12.0", "<0.13.0"]

What database are you using dbt with?

redshift

dbt Version

1.5.4

Additional Context

No response

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

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

[Feature] Add conversations model

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

https://developers.hubspot.com/docs/api/conversations/conversations

Information about conversations for users is critical to us (HubSpot Live Chat / WhatsApp / etc.).
It would be really cool to add it to the HubSpot dbt

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

BUG - Deal and deal stages data type join mismatch on Redshift

Are you a current Fivetran customer?
Yes. Scott Hibberd - Infogrid, BI Analyst

Describe the bug
Running dbt run returns an error for the hubspot__deals and hubspot__deal_stages models

The error I receive is JOIN/USING types bigint and character varying cannot be matched

Steps to reproduce

1. Import the Fivetran Hubspot package
2. Run dbt run

Expected behavior
Models should build without error

Project variables configuration

name: 'infogrid_bi'
version: '1.0.0'
config-version: 2

profile: 'default'

model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
    - "target"
    - "dbt_modules"

vars:

  hubspot__pass_through_all_columns: true 

  hubspot_email_event_spam_report_enabled: false
  hubspot_email_event_print_enabled: false
  hubspot_email_event_forward_enabled: false

Package Version

  - package: fivetran/hubspot
    version: [">=0.5.0", "<0.6.0"]

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
For both models, the build fails due to the join within the deal_fields_joined CTE

It fixes for me to rewrite the join as follow:

select 
        deals.*,
        pipelines.pipeline_label,
        pipelines.is_active as is_pipeline_active,
        pipeline_stages.pipeline_stage_label,
        owners.email_address as owner_email_address,
        owners.full_name as owner_full_name

    from deals
    left join pipelines on deals.deal_pipeline_id = cast(pipelines.deal_pipeline_id as bigint)
    left join pipeline_stages on deals.deal_pipeline_stage_id = cast(pipeline_stages.deal_pipeline_stage_id as bigint)
    left join owners on deals.owner_id = owners.owner_id

Screenshots

Please indicate the level of urgency
Currently in the process of building Hubspot reporting - we can workaround temporarily by overwriting the package model files, but a longer term solution is preferable.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] Removal of deleted record filter resulting in uniqueness test failures

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Updating to version 0.8.0 of the HubSpot package resulted in uniqueness test failures. I read in dbt_hubspot_source #96 that the methods where not coalesce(is_deleted, false) and where not coalesce(_fivetran_deleted, false) were removed from a number of models to allow for greater flexibility. In testing this does appear to be the cause of the failures. The records that are failing have different deleted flag values. Each set of duplicates has one false flag and the remaining flags are true. Note, I have contact merge auditing enabled and tested it to rule it out as the cause. In my case, this was specific to email events but seems like it might apply to other models where the filter was removed.

Relevant error log or model output

No response

Expected behavior

Uniqueness tests should pass.

dbt Project configurations

n/a

Package versions

0.8.0

What database are you using dbt with?

redshift

dbt Version

1.3

Additional Context

Discussion with Jamie in dbt slack

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

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

[Bug] Your package relies on older versions of dbt-utils, which means our project is stuck on an older version

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Your package relies on older versions of dbt-utils, which means our project is stuck on an older version

Relevant error log or model output

No response

Expected behavior

We're allowed to upgrade to latest dbt-utils

dbt Project configurations

n

Package versions

packages:
  - package: fivetran/stripe
    version: [">=0.6.0", "<0.9.0"]
  - package: fivetran/hubspot
    version: [">=0.5.0", "<0.8.0"]

What database are you using dbt with?

bigquery

dbt Version

1.3.0

Additional Context

No response

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

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