[Bug] Issue while running facebook_ads transformations with new schema or for a new instance or environment

chandu98 commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

We are facing a facebook_ads issue when we try to build new facebook_ads dbt transformations in a new environment or instance. Because of this issue, we were blocked to build the full ad_reporting model of version 0.7.0 in the new environment which we created.

We tried to find the root cause for this but we couldn't find the actual issue.

Here is the actual error that we are getting when we tried to create Facebook_ads transformations in schema or instance or environment. Here is the actual error that we are getting when we tried to create Facebook_ads transformations in schema or instance or environment.

Error message -
Completed with 1 error and 0 warnings:
 Runtime Error in model facebook_ads__creative_history_prep (models\intermediate\facebook_ads__creative_history_prep.sql)
 404 Not found: Dataset gcp-ftd-bi-sandbox:bi_chandu_practice_v3_facebook_ads_creative_history was not found in location US

Relevant error log or model output

03:21:04  Found 147 models, 117 tests, 0 snapshots, 0 analyses, 785 macros, 0 operations, 0 seed files, 117 sources, 0 exposures, 0 metrics
03:21:04  
03:21:08  Concurrency: 16 threads (target='dev')
03:21:08  
03:21:08  1 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history_tmp  [RUN]
03:21:08  2 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history_tmp  [RUN]
03:21:08  3 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history_tmp  [RUN]
03:21:08  4 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad_tmp  [RUN]
03:21:08  5 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history_tmp  [RUN]
03:21:08  6 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history_tmp  [RUN]
03:21:10  4 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad_tmp  [OK in 2.18s]
03:21:10  2 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history_tmp  [OK in 2.19s]
03:21:10  6 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history_tmp  [OK in 2.18s]
03:21:10  7 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad  [RUN]
03:21:10  8 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history  [RUN]
03:21:10  9 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history  [RUN]
03:21:11  5 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history_tmp  [OK in 2.25s]
03:21:11  3 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history_tmp  [OK in 2.25s]
03:21:11  10 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history  [RUN]
03:21:11  11 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history  [RUN]
03:21:11  1 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history_tmp  [OK in 2.43s]
03:21:11  12 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history  [RUN]
03:21:16  12 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history  [CREATE TABLE (7.0 rows, 288.0 Bytes processed) in 5.02s]
03:21:16  8 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history  [CREATE TABLE (23.2k rows, 2.8 MB processed) in 
5.35s]
03:21:16  7 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad  [CREATE TABLE (115.9k rows, 6.2 MB processed) in 5.49s]
03:21:16  10 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history  [CREATE TABLE (815.0 rows, 60.7 KB processed) in 5.36s]
03:21:16  11 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history  [CREATE TABLE (6.0k rows, 538.9 KB processed) in 5.50s]
03:21:16  9 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history  [CREATE TABLE (16.5k rows, 12.3 MB processed) in 5.70s]
03:21:16  13 of 17 START view model bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__creative_history_prep  [RUN]
03:21:17  13 of 17 ERROR creating view model bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__creative_history_prep  [ERROR in 1.13s]
03:21:17  14 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__ad_adapter  [SKIP]
03:21:17  15 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__account_report  [SKIP]
03:21:17  16 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__ad_set_report  [SKIP]
03:21:17  17 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__campaign_report  [SKIP]
03:21:17
03:21:17  Finished running 7 view models, 10 table models in 13.23s.
03:21:18  
03:21:18  Completed with 1 error and 0 warnings:
03:21:18
03:21:18  Runtime Error in model facebook_ads__creative_history_prep (models\intermediate\facebook_ads__creative_history_prep.sql)
03:21:18    404 Not found: Dataset gcp-ftd-bi-sandbox:bi_chandu_practice_v3_facebook_ads_creative_history was not found in location US
03:21:18
03:21:18    Location: US
03:21:18    Job ID: 9ac7911f-7b36-492c-ab55-3d9bfd89152b
03:21:18
03:21:18
03:21:18  Done. PASS=12 WARN=0 ERROR=1 SKIP=4 TOTAL=17

Expected behavior

The model should not give any errors and all the dependencies should get created automatically by the facebook_ads itself

dbt Project configurations

vars:
  api_source: google_ads
  ad_reporting__pinterest_enabled: True
  ad_reporting__microsoft_ads_enabled: True
  ad_reporting__linkedin_ads_enabled: False
  ad_reporting__google_ads_enabled: True
  ad_reporting__twitter_ads_enabled: False
  ad_reporting__facebook_ads_enabled: True
  ad_reporting__snapchat_ads_enabled: True
  ad_reporting__tiktok_ads_enabled: True

  facebook_ads_schema: bi_landing_fivetran_facebook_ads_v2
  facebook_ads_database: gcp-ftd-data-int-prod
  tiktok_ads_schema: bi_landing_fivetran_tiktok_ads
  tiktok_ads_database: gcp-ftd-data-int-prod
  pinterest_schema: bi_landing_fivetran_pinterest_ads
  pinterest_database: gcp-ftd-data-int-prod
  snapchat_schema: bi_landing_fivetran_snapchat_ads_v2
  snapchat_database: gcp-ftd-data-int-prod
  google_ads_schema: bi_landing_fivetran_google_adv
  google_ads_database: gcp-ftd-data-int-prod
  microsoft_ads_schema: bi_landing_fivetran_microsoftads_v2
  microsoft_ads_database: gcp-ftd-data-int-prod

models:

  bigdata:
    # Config indicated by + and applies to all files under models/example/
    example:
      +materialized: view
    marketing:
      +materialized: view
  # disable both pinterest models if not using pinterest ads
  pinterest:
    enabled: true
    +schema: pinterest
  pinterest_source:
    enabled: true
    +schema: pinterest_source

  # disable both microsoft ads models if not using microsoft ads
  microsoft_ads:
    +schema: microsoft_ads_v2
  microsoft_ads_source:
    +schema: microsoft_ads_source_v2

  # disable both linkedin ads models if not using linkedin ads
  linkedin:
    enabled: false
  linkedin_source:
    enabled: false

  # disable both twitter ads models if not using twitter ads
  twitter_ads:
    enabled: false
  twitter_ads_source:
    enabled: false

  # disable all three facebook ads models if not using facebook ads
  facebook_ads:
    enabled: true #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: facebook_ads_v2
  facebook_ads_source:
    enabled: true #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: facebook_ads_source_v2
  #facebook_ads_creative_history:
  #  enabled: true #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
  #  +schema: facebook_ads

  # disable both google ads models if not using google ads
  google_ads:
    #enabled: true #IF YOU ARE USING GOOGLE ADS, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: google_ads
  google_ads_source:
    #enabled: true #IF YOU ARE USING GOOGLE ADS, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: google_ads_source

  # disable both snapchat ads models if not using snapchat ads
  snapchat_ads:
    enabled: true
    +schema: snapchat_ads_v2
  snapchat_ads_source:
    enabled: true
    +schema: snapchat_ads_source_v2

  # disable both tiktok ads models if not using tiktok ads
  tiktok_ads:
    enabled: true
    +schema: tiktok_ads
  tiktok_ads_source:
    enabled: true
    +schema: tiktok_ads_source

Package versions

packages:

package: fivetran/ad_reporting version: 0.7.0

What database are you using dbt with?

bigquery

dbt Version

1.1.1

Additional Context

facebook_ads_issue

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
[ ] No.

fivetran-joemarkiewicz commented 1 year ago

Hi @chandu98 thank you for opening this issue. I was curious about one piece in particular before diving too deep into a possible solution.

I am curious that the error log looks like it is searching for the table in the the *_v3 schema. However, the schemas in your dbt_project.yml seem to all be *_v2. Are there additional configs that are being used that are not shown in the bug description?

fivetran-joemarkiewicz commented 1 year ago

Hi @chandu98 as this bug as been left stale for a period of time, and I am unsure what may be causing the issue at hand and believe this may be a config issue within your dbt_project.yml. As such, I will close this bug report.

If anyone else encounters the same issue, please feel free to reopen this bug report.

fivetran / dbt_ad_reporting