BUG - [Duplicates in intercom__admin_metrics]

[marie-aircall]

Are you a current Fivetran customer? Yes, I am a Data Analyst at Aircall.

Describe the bug Unicity test on admin_id fails when refreshing the intercom__admin_metrics model due to duplicates caused by admins part of more than 1 team.

Steps to reproduce

1. Run the model intercom__admin_metrics
2. See error: Failure in test unique_intercom__admin_metrics_admin_id (models/intercom.yml)

Expected behavior No fail.

Project variables configuration

# A good package name should reflect your organization's name or the intended use of these models
name: 'aircall'
version: '1.0.0'
config-version: 2

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

source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]

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

models:
  aircall:
    +materialized: view_if_not_exists
    intermediate_marts:
      +schema: intermediate
    sources:
      +schema: sources

vars:
  dbt_date:time_zone: 'UTC'
  phone_schema: "client_events_phone_staging"
  android_schema: "client_events_android_staging"
  ios_schema: "client_events_ios_staging"
  browser_extension_schema: "client_events_browser_extension_staging"
  dashboard_schema: "client_events_dashboard_staging"
  dry_run : False #adding a flag variable to the compile step in order to avoid failure during compiling, when the current code depends on the data of a model which is not yet materialized
  jira:
    jira_include_comments: false
    # ----- IF YOU WANT TO ADD A FIELD TO jira_changelog ----- #
    field_columns: # lower case with _ if more than 1 word
      - name: status
        table: stg_jira__status
        joining_id: status_id
        source_field: status_name
      - name: assignee
        table: stg_jira__user
        joining_id: user_id
        source_field: user_display_name
      - name: assignment_group
        table: clean_jira_field_option
        joining_id: field_id
        source_field: field_name

    # ----- IF YOU WANT TO ADD A FIELD TO jira_issues_d (and jira_issues) ----- #
    issue_field_history_columns: ['customfield_10161', 'assignee', 'priority', 'customfield_10170'] # variable used in jira__daily_issue_field_history

    # ----- IF YOU WANT TO ADD A CUSTOM FIELD TO jira_issues ONLY (not to jira_issues_d)----- #
    custom_fields_columns: ['product_category','severity','labels','blast_radius','region','service_impact','symptoms']

    # ----- IF YOU WANT TO ADD A SPECIAL CUSTOM FIELD TO jira_issues ONLY (not to jira_issues_d)----- #
    # ----- special custom field: field for which the value is not represented by an id but its value in int_jira__combine_field_histories----- #
    special_custom_fields_columns: ['story_points', 'email_verification']

  intercom:
    using_contact_tags: false
    using_team: false

Package Version

packages:
  - package: dbt-labs/redshift
    version: [">=0.5.0", "<0.6.0"]
  - package: fivetran/jira
    version: [">=0.5.0", "<0.6.0"]
  - package: fivetran/intercom
    version: [">=0.3.0", "<0.4.0"]
  - package: calogica/dbt_expectations
    version: [">=0.4.0", "<0.5.0"]

Warehouse

- [ ] BigQuery - [x] Redshift - [ ] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** No **Screenshots** **Please indicate the level of urgency** Impacting us as we cannot refresh any of our Intercom models due to this error. **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.
• [x] 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.

[fivetran-joemarkiewicz]

Hi @marie-aircall thanks so much for raising this issue.

It definitely seems we did not update the test for this model appropriately once we added the admin_team support for this model. Before we move forward with implementing a fix, I wanted to ensure the granularity of the intercom__admin_team model is still appropriate per your understanding with the addition of the admin_team.

The new model description would be the below:

Each record represents an admin (employee) and a unique team they are assigned on, enriched with admin specific conversation aggregates. For example, the admin's total conversations, average rating, and median response times by specific team.

Let me know if you have any issues with this new model description and granularity.

[marie-aircall]

Hi Joe,

Thanks a lot for your response! This sounds good to me 👌

[fivetran-jamie]

hi @marie-aircall -- i've added a fix that should dynamically include team_id (with admin_id) in uniqueness tests for the admin metrics model. could you confirm that this branch works for ya?

# packages.yml
packages:
  - git: https://github.com/fivetran/dbt_intercom.git
    revision: bug/admin-tests

[fivetran-jamie]

@marie-aircall just merged and released the above branch if you'd still like to test it out! the branch is still up