[Bug] Incorrect test config on `stg_facebook_ads__creative_history`

LewisDavies commented 9 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

I have added the ad_reporting package to my project and dbt run was successful for all models. However, I'm getting an error when testing the results because of apparent duplicates in stg_facebook_ads__creative_history. This is the only test that is failing.

The output of this model is filtered by the two directly downstream (facebook_ads__url_tags and int_facebook_ads__creative_history), which use the is_most_recent_record column to remove older rows.

Temporary workaround

Using the package name and full identifier in my project file let me disable the test and pass CI:

# dbt_project.yml
tests:
  facebook_ads_source:
    dbt_utils_unique_combination_of_columns_stg_facebook_ads__creative_history_source_relation__creative_id___fivetran_synced:
      +enabled: false

Fix

Adding a where clause in the test config prevents the error:

# stg_facebook_ads.yml
  - name: stg_facebook_ads__creative_history
    description: Each record in this table reflects a Facebook creative.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - creative_id
            - _fivetran_synced
          config:
            where: is_most_recent_record

Let me know if this an acceptable solution and I will open a PR to make the change 🙂

Relevant error log or model output

# dbt test -s package:facebook_ads_source
13:42:45  Running with dbt=1.6.9
13:42:45  Registered adapter: bigquery=1.6.9
13:42:46  Unable to do partial parsing because a project config has changed
13:42:50  Found 106 models, 3 seeds, 181 tests, 35 sources, 0 exposures, 9 metrics, 907 macros, 0 groups, 1 semantic model
13:42:50  
13:42:52  Concurrency: 4 threads (target='dev')
13:42:52  
13:42:52  1 of 18 START test dbt_utils_unique_combination_of_columns_stg_facebook_ads__account_history_source_relation__account_id___fivetran_synced  [RUN]
13:42:52  2 of 18 START test dbt_utils_unique_combination_of_columns_stg_facebook_ads__ad_history_source_relation__ad_id__updated_at  [RUN]
13:42:52  3 of 18 START test dbt_utils_unique_combination_of_columns_stg_facebook_ads__ad_set_history_source_relation__ad_set_id__updated_at  [RUN]
13:42:52  4 of 18 START test dbt_utils_unique_combination_of_columns_stg_facebook_ads__basic_ad_source_relation__date_day__ad_id__account_id  [RUN]
13:42:53  4 of 18 PASS dbt_utils_unique_combination_of_columns_stg_facebook_ads__basic_ad_source_relation__date_day__ad_id__account_id  [PASS in 1.23s]
13:42:53  5 of 18 START test dbt_utils_unique_combination_of_columns_stg_facebook_ads__campaign_history_source_relation__campaign_id__updated_at  [RUN]
13:42:53  3 of 18 PASS dbt_utils_unique_combination_of_columns_stg_facebook_ads__ad_set_history_source_relation__ad_set_id__updated_at  [PASS in 1.25s]
13:42:53  6 of 18 START test dbt_utils_unique_combination_of_columns_stg_facebook_ads__creative_history_source_relation__creative_id___fivetran_synced  [RUN]
13:42:53  1 of 18 PASS dbt_utils_unique_combination_of_columns_stg_facebook_ads__account_history_source_relation__account_id___fivetran_synced  [PASS in 1.28s]
13:42:53  7 of 18 START test not_null_stg_facebook_ads__account_history__fivetran_synced . [RUN]
13:42:53  2 of 18 PASS dbt_utils_unique_combination_of_columns_stg_facebook_ads__ad_history_source_relation__ad_id__updated_at  [PASS in 1.29s]
13:42:53  8 of 18 START test not_null_stg_facebook_ads__account_history_account_id ....... [RUN]
13:42:54  5 of 18 PASS dbt_utils_unique_combination_of_columns_stg_facebook_ads__campaign_history_source_relation__campaign_id__updated_at  [PASS in 1.22s]
13:42:54  8 of 18 PASS not_null_stg_facebook_ads__account_history_account_id ............. [PASS in 1.17s]
13:42:54  9 of 18 START test not_null_stg_facebook_ads__ad_history_ad_id ................. [RUN]
13:42:54  6 of 18 FAIL 4 dbt_utils_unique_combination_of_columns_stg_facebook_ads__creative_history_source_relation__creative_id___fivetran_synced  [FAIL 4 in 1.22s]
13:42:54  10 of 18 START test not_null_stg_facebook_ads__ad_history_updated_at ........... [RUN]
13:42:54  11 of 18 START test not_null_stg_facebook_ads__ad_set_history_ad_set_id ........ [RUN]
13:42:54  7 of 18 PASS not_null_stg_facebook_ads__account_history__fivetran_synced ....... [PASS in 1.24s]
13:42:54  12 of 18 START test not_null_stg_facebook_ads__ad_set_history_updated_at ....... [RUN]
13:42:56  12 of 18 PASS not_null_stg_facebook_ads__ad_set_history_updated_at ............. [PASS in 1.19s]
13:42:56  13 of 18 START test not_null_stg_facebook_ads__basic_ad_account_id ............. [RUN]
13:42:56  11 of 18 PASS not_null_stg_facebook_ads__ad_set_history_ad_set_id .............. [PASS in 1.25s]
13:42:56  14 of 18 START test not_null_stg_facebook_ads__basic_ad_ad_id .................. [RUN]
13:42:56  9 of 18 PASS not_null_stg_facebook_ads__ad_history_ad_id ....................... [PASS in 1.29s]
13:42:56  15 of 18 START test not_null_stg_facebook_ads__campaign_history_campaign_id .... [RUN]
13:42:56  10 of 18 PASS not_null_stg_facebook_ads__ad_history_updated_at ................. [PASS in 1.35s]
13:42:56  16 of 18 START test not_null_stg_facebook_ads__campaign_history_updated_at ..... [RUN]
13:42:57  13 of 18 PASS not_null_stg_facebook_ads__basic_ad_account_id ................... [PASS in 1.06s]
13:42:57  17 of 18 START test not_null_stg_facebook_ads__creative_history__fivetran_synced  [RUN]
13:42:57  15 of 18 PASS not_null_stg_facebook_ads__campaign_history_campaign_id .......... [PASS in 1.10s]
13:42:57  18 of 18 START test not_null_stg_facebook_ads__creative_history_creative_id .... [RUN]
13:42:57  16 of 18 PASS not_null_stg_facebook_ads__campaign_history_updated_at ........... [PASS in 1.06s]
13:42:57  14 of 18 PASS not_null_stg_facebook_ads__basic_ad_ad_id ........................ [PASS in 1.19s]
13:42:58  17 of 18 PASS not_null_stg_facebook_ads__creative_history__fivetran_synced ..... [PASS in 1.12s]
13:42:58  18 of 18 PASS not_null_stg_facebook_ads__creative_history_creative_id .......... [PASS in 1.13s]
13:42:58  
13:42:58  Finished running 18 tests in 0 hours 0 minutes and 7.86 seconds (7.86s).
13:42:58  
13:42:58  Completed with 1 error and 0 warnings:
13:42:58  
13:42:58  Failure in test dbt_utils_unique_combination_of_columns_stg_facebook_ads__creative_history_source_relation__creative_id___fivetran_synced (models/stg_facebook_ads.yml)
13:42:58    Got 4 results, configured to fail if != 0
13:42:58  
13:42:58    compiled Code at target/compiled/facebook_ads_source/models/stg_facebook_ads.yml/dbt_utils_unique_combination_o_e48f84c8ad4c2d29a6b840a1e0b5f2ee.sql
13:42:58  
13:42:58  Done. PASS=17 WARN=0 ERROR=1 SKIP=0 TOTAL=18

Expected behavior

The tests pass or accurately reflect an error that needs to be fixed.

dbt Project configurations

models:
  ad_reporting:
    +schema: ad_reporting

  apple_search_ads:
    +schema: apple_search_ads
  apple_search_ads_source:
    +schema: apple_search_ads_source

  facebook_ads:
    +schema: facebook_ads
  facebook_ads_source:
    +schema: facebook_ads_source

  google_ads:
    +schema: google_ads
  google_ads_source:
    +schema: google_ads_source

vars:
  apple_search_ads_schema: fivetran_apple_search_ads
  facebook_ads_schema: fivetran_facebook_ads
  google_ads_schema: fivetran_google_ads
  ad_reporting__amazon_ads_enabled: False
  ad_reporting__linkedin_ads_enabled: False
  ad_reporting__microsoft_ads_enabled: False
  ad_reporting__pinterest_ads_enabled: False
  ad_reporting__reddit_ads_enabled: False
  ad_reporting__snapchat_ads_enabled: False
  ad_reporting__tiktok_ads_enabled: False
  ad_reporting__twitter_ads_enabled: False

Package versions

packages:
  - package: LewisDavies/upstream_prod
    version: [">=0.7.0", "<0.8.0"]
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<1.2.0"]
  - package: fivetran/ad_reporting
    version: [">=1.7.0", "<1.8.0"]

What database are you using dbt with?

bigquery

dbt Version

1.6.9

Additional Context

No response

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

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

fivetran-jamie commented 9 months ago

Hey there @LewisDavies , thanks for taking the time to dig into this!

your solution definitely makes sense, but i just have one question first to see if we can simplify the test further - does the following test also return a failure?

# stg_facebook_ads.yml
  - name: stg_facebook_ads__creative_history
    description: Each record in this table reflects a Facebook creative.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - _fivetran_id

LewisDavies commented 9 months ago

No worries! Just tried your config and the tests are passing for me:


dbt test -s stg_facebook_ads__creative_history
13:36:37  Running with dbt=1.6.9
13:36:37  Registered adapter: bigquery=1.6.9
13:36:38  Found 107 models, 3 seeds, 181 tests, 35 sources, 0 exposures, 9 metrics, 907 macros, 0 groups, 1 semantic model
13:36:38  
13:36:39  Concurrency: 4 threads (target='dev')
13:36:39  
13:36:39  1 of 3 START test dbt_utils_unique_combination_of_columns_stg_facebook_ads__creative_history_source_relation___fivetran_id  [RUN]
13:36:39  2 of 3 START test not_null_stg_facebook_ads__creative_history__fivetran_synced . [RUN]
13:36:39  3 of 3 START test not_null_stg_facebook_ads__creative_history_creative_id ...... [RUN]
13:36:41  2 of 3 PASS not_null_stg_facebook_ads__creative_history__fivetran_synced ....... [PASS in 1.66s]
13:36:41  3 of 3 PASS not_null_stg_facebook_ads__creative_history_creative_id ............ [PASS in 1.86s]
13:36:43  1 of 3 PASS dbt_utils_unique_combination_of_columns_stg_facebook_ads__creative_history_source_relation___fivetran_id  [PASS in 3.22s]
13:36:43  
13:36:43  Finished running 3 tests in 0 hours 0 minutes and 5.01 seconds (5.01s).
13:36:43  
13:36:43  Completed successfully
13:36:43  
13:36:43  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

fivetran-jamie commented 9 months ago

amazing, let's go that route to keep the test nice and simple. if you're still open to contributing a PR, i'd be happy to prioritze reviewing it! if not, the team can pick this up next sprint 🤠

fivetran / dbt_facebook_ads_source