BUG - Mailchimp tests fail to pass

CraigWilson-ZOE commented 2 years ago

Are you a current Fivetran customer? Craig Wilson, Data Engineering, ZOE

Describe the bug When running the tests in the mailchimp package we are getting the following errors:

Completed with 4 errors and 0 warnings:

Failure in test unique_stg_mailchimp_members_member_id (models/base/schema.yml)
Got 551602 results, configured to fail if != 0

compiled SQL at target/compiled/mailchimp/models/base/schema.yml/schema_test/unique_stg_mailchimp_members_member_id.sql

Failure in test not_null_automation_activities_by_automation_automation_id (models/intermediate/schema.yml)
Got 1 result, configured to fail if != 0

compiled SQL at target/compiled/mailchimp/models/intermediate/schema.yml/schema_test/not_null_automation_activities_by_automation_automation_id.sql

Failure in test unique_automation_activities_by_member_member_id (models/intermediate/schema.yml)
Got 116 results, configured to fail if != 0

compiled SQL at target/compiled/mailchimp/models/intermediate/schema.yml/schema_test/unique_automation_activities_by_member_member_id.sql

Failure in test unique_mailchimp_members_member_id (models/schema.yml)
Got 551679 results, configured to fail if != 0

compiled SQL at target/compiled/mailchimp/models/schema.yml/schema_test/unique_mailchimp_members_member_id.sql

Steps to reproduce

Ensure you have data where a user can subscribe to many lists
Install dbt package for Mailchimp (version: [">=0.3.0", "<0.4.0"])
Run a full dbt run for the Mailchimp package e.g. dbt run --select Mailchimp
run a full dbt test for the Mailchimp package e.g. dbt test --select Mailchimp
See the failing tests

Expected behavior All tests to pass without error

Project variables configuration

vars:
    mailchimp_database: xxxxxxxx --hidden for privacy reasons
    mailchimp_schema: mailchimp
    mailchimp_using_segments: false

Package Version

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

Warehouse

- [x] BigQuery - [ ] Redshift - [ ] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context**

When investigating this with the solutions team we found that user can belong to many lists (list_id) and that is the cause of at least one of the errors. This should be added as part of the primary key check. In addition, the mailchimp_members table aggregation seemed to join too many times generating an aggregate with duplicates apart from two distinct fields which seemed to be mutually exclusive: automation_sends and automation_unsubscribes

On the duplicate rows, each of these were 0 on one row and had a number on the other i.e. ROW 1: automation_sends = 6, automation_unsubscribes = 0 ROW 2: automation_sends = 0, automation_unsubscribes = 5

Screenshots

Please indicate the level of urgency

This is impacting our ability to see if we wish to use the Mailchimp data for analysis.

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.