fivetran-sheringuyen
commented
1 year ago Hello @clay-walker, thank you so much for creating this issue (as well as #64)!
We've started working on a PR that should mitigate these issues here, however, this PR has a dependency on the dbt_utils 1.0.0 release. So in the meantime, we've actually created a separate branch off of main so that you can test the model logic. Would you mind testing this branch of microsoft_ads out and see if you are still seeing the same issues?
All you will need to do is change your dbt_ad_reporting/packages.yml to reference the branch above like so:
packages:
- package: fivetran/apple_search_ads
version: [">=0.1.0", "<0.2.0"]
- package: fivetran/snapchat_ads
version: [">=0.4.0", "<0.5.0"]
- package: fivetran/facebook_ads
version: [">=0.5.0", "<0.6.0"]
- package: fivetran/google_ads
version: [">=0.8.0", "<0.9.0"]
- package: fivetran/pinterest
version: [">=0.6.0", "<0.7.0"]
- package: fivetran/linkedin
version: [">=0.5.0", "<0.6.0"]
# - package: fivetran/microsoft_ads
# version: [">=0.5.0", "<0.6.0"]
- git: https://github.com/fivetran/dbt_microsoft_ads.git
revision: test-bugfix/search-uniqueness-hard-deletes
warn-unpinned: false
- package: fivetran/tiktok_ads
version: [">=0.2.0", "<0.3.0"]
- package: fivetran/twitter_ads
version: [">=0.5.0", "<0.6.0"]
clay-walker
fivetran-jamie
nszoni
fivetran-joemarkiewicz
callum-mcdata
Is there an existing issue for this?
Describe the issue
The microsoft_ads__keyword_report model uses the daily keyword report as its base and left joins to the keyword history table on keyword_id. There is a subsequent "not null" test for keyword ID, which can fail (since keyword history is left joined).
It is possible for keyword_id values to exist in the daily report and not in the keyword history table. This happens when a keyword is deleted (keyword_status = 'Deleted' in the daily table).
For Example:
I've investigated this issue and have come up with two solutions:
Add a filter in microsoft_ads__keyword_report (this requires a change to get_keyword_daily_report_columns.sql and stg_microsoft_ads__keyword_daily_report_tmp.sql):
where report.keyword_status != 'Deleted'Alternatively, change microsoft_ads__keyword_report to inner join with keyword history. This is the simplest, and imo preferred solution. As you can see, this inner join will only exclude deleted records:
Relevant error log or model output
Expected behavior
tests pass without failure
dbt Project configurations
vars: prod_database: analytics prod_schema: business operdb_schema_pattern: 'operdb%' raw_data_db: GONG "dbt_date:time_zone": "America/Los_Angeles" timezone_constant: "America/Los_Angeles"
ad_reportingapple_search_ads_enabled: False # by default this is assumed to be True ad_reportingpinterest_ads_enabled: False # by default this is assumed to be True ad_reportingtwitter_ads_enabled: False # by default this is assumed to be True ad_reporting__facebook_ads_enabled: False # by default this is assumed to be True ad_reportingsnapchat_ads_enabled: False # by default this is assumed to be True ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True
linkedin_ads_schema: linkedin_ads linkedin_ads_database: source_raw
google_ads_schema: google_ads google_ads_database: source_raw
microsoft_ads_schema: bingads microsoft_ads_database: source_raw
Package versions
packages:
package: fivetran/salesforce_formula_utils version: 0.6.4
package: calogica/dbt_date version: 0.5.7
package: fivetran/ad_reporting version: [">=1.0.0", "<1.1.0"]
What database are you using dbt with?
snowflake
dbt Version
1.0.1
Additional Context
No response
Are you willing to open a PR to help address this issue?