[Bug] Campaign Performance Daily Report

mzanatta commented 3 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

Recently, two new fields were added to the CAMPAIGN_PERFORMANCE_DAILY_REPORT table. This new fields are budget_name and budget_status and these situation is causing that the unique combination of columns test for stg_microsoft_ads__campaign_daily_report model fail

dbt_utils_unique_combination_of_columns_stg_microsoft_ads__campaign_daily_report_source_relation__date_day__account_id__campaign_id__device_os__device_type__network__currency_code__ad_distribution__bid_match_type__delivered_match_type__top_vs_other__budget_association_status (models/stg_microsoft_ads.yml)

Relevant error log or model output

No response

Expected behavior

This test pass successfully

dbt Project configurations

microsoft_ads_schema: 'BINGADS'

Package versions

packages:

package: dbt-labs/dbt_utils version: 1.1.1
package: dbt-labs/codegen version: 0.12.0
package: get-select/dbt_snowflake_monitoring version: 4.6.0
package: fivetran/facebook_ads version: [">=0.7.0", "<0.8.0"] # we recommend using ranges to capture non-breaking changes automatically
package: fivetran/google_ads version: [">=0.10.0", "<0.11.0"] # we recommend using ranges to capture non-breaking changes automatically
package: fivetran/microsoft_ads version: [">=0.7.0", "<0.8.0"] # we recommend using ranges to capture non-breaking changes automatically

What database are you using dbt with?

snowflake

dbt Version

1.7

Additional Context

Once the test started failing and based on Fivetran documentation for this table

https://fivetran.com/docs/connectors/applications/microsoft-advertising#campaignperformancedailyreport

I opened a ticket in fivetran due duplicated data in the synced table and the answer was that the PK of this table is

select account_id, campaign_id, date, currency_code, ad_distribution, device_type, network, delivered_match_type, device_os, top_vs_other, bid_match_type, Budget_Association_Status, budget_name, budget_status count(*) AS test from "FIVETRAN_DB"."BINGADS"."CAMPAIGN_PERFORMANCE_DAILY_REPORT" group by all having test > 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
[X] No.

fivetran-jamie commented 2 months ago

hey there @mzanatta! Currently working on a PR for this if you'd like to test out my branch:

To test just the source package:

packages:
- git: https://github.com/fivetran/dbt_microsoft_ads_source.git
  revision: bugfix/campaign-report-add-budget

The transform package with the source package updates

packages:
- git: https://github.com/fivetran/dbt_microsoft_ads.git
  revision: bugfix/campaign-report-add-budget

And make sure to comment out your current package: fivetran/microsoft_ads dependency

mzanatta commented 2 months ago

Thanks @fivetran-jamie. I've just tested it out and it works great

fivetran-jamie commented 2 months ago

great! this is live now in v0.8.0 of the microsoft_ads package

fivetran / dbt_microsoft_ads_source