search

fivetran / dbt_facebook_ads_source

Fivetran data models for Facebook Ads built using dbt.
https://fivetran.github.io/dbt_facebook_ads_source/
Apache License 2.0
14 stars 16 forks source link

[Bug] Quickstart transformations failed because basic_ad table wasn't found #30

Closed Jeralckx closed 1 year ago

Jeralckx commented 1 year ago

Is there an existing issue for this?

Describe the issue

I've configured the Facebook Ads connector and enabled all available Quickstart transformations for Facebook Ads:

image

However, all Quickstart transformations are failing because of the same reason: it appears the basic_ad table wasn't found: 

23:26:36  Completed with 1 error and 0 warnings:
23:26:36  
23:26:36  Runtime Error in model stg_facebook_ads__basic_ad_tmp (models/tmp/stg_facebook_ads__basic_ad_tmp.sql)
23:26:36    404 Not found: Table daikin-be:facebook_ads.basic_ad was not found in location EU
23:26:36    
23:26:36    Location: EU
23:26:36    Job ID: 8b32ee04-6654-49a7-aa08-f93b1566c9a3
23:26:36    
23:26:36  
23:26:36  Done. PASS=12 WARN=0 ERROR=1 SKIP=6 TOTAL=19

This seems odd since according to this documentation: https://fivetran.com/docs/applications/facebook-ads/prebuilt-reports, the basic_ad table is included in the 21 prebuilt reports that are created by default upon activating the Facebook Ads connector.

Relevant error log or model output

dbt run --models +facebook_ads__url_report +facebook_ads__ad_set_report +facebook_ads__account_report +facebook_ads__campaign_report +facebook_ads__ad_report
23:26:16  Running with dbt=1.3.1
23:26:16  Partial parse save file not found. Starting full parse.
23:26:21  Found 19 models, 30 tests, 0 snapshots, 0 analyses, 541 macros, 0 operations, 0 seed files, 6 sources, 0 exposures, 0 metrics
23:26:21  
23:26:23  Concurrency: 4 threads (target='prod')
23:26:23  
23:26:23  1 of 19 START sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__account_history_tmp  [RUN]
23:26:23  2 of 19 START sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_history_tmp  [RUN]
23:26:23  3 of 19 START sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_set_history_tmp  [RUN]
23:26:23  4 of 19 START sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__basic_ad_tmp  [RUN]
23:26:26  4 of 19 ERROR creating sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__basic_ad_tmp  [ERROR in 2.29s]
23:26:26  5 of 19 START sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__campaign_history_tmp  [RUN]
23:26:26  3 of 19 OK created sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_set_history_tmp  [CREATE VIEW (0 processed) in 2.82s]
23:26:26  2 of 19 OK created sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_history_tmp  [CREATE VIEW (0 processed) in 2.84s]
23:26:26  7 of 19 SKIP relation facebook_ads_facebook_ads_source.stg_facebook_ads__basic_ad  [SKIP]
23:26:26  8 of 19 START sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_set_history  [RUN]
23:26:26  6 of 19 START sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__creative_history_tmp  [RUN]
23:26:26  1 of 19 OK created sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__account_history_tmp  [CREATE VIEW (0 processed) in 3.08s]
23:26:26  9 of 19 START sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_history  [RUN]
23:26:28  5 of 19 OK created sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__campaign_history_tmp  [CREATE VIEW (0 processed) in 2.16s]
23:26:28  10 of 19 START sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__account_history  [RUN]
23:26:28  6 of 19 OK created sql view model facebook_ads_facebook_ads_source.stg_facebook_ads__creative_history_tmp  [CREATE VIEW (0 processed) in 2.10s]
23:26:28  11 of 19 START sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__campaign_history  [RUN]
23:26:30  9 of 19 OK created sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_history  [CREATE TABLE (18.0 rows, 2.0 KB processed) in 3.18s]
23:26:30  12 of 19 START sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__creative_history  [RUN]
23:26:30  8 of 19 OK created sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__ad_set_history  [CREATE TABLE (11.0 rows, 1.5 KB processed) in 3.64s]
23:26:31  10 of 19 OK created sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__account_history  [CREATE TABLE (2.0 rows, 178.0 Bytes processed) in 2.89s]
23:26:31  11 of 19 OK created sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__campaign_history  [CREATE TABLE (10.0 rows, 1.2 KB processed) in 2.40s]
23:26:31  13 of 19 SKIP relation facebook_ads_facebook_ads.facebook_ads__account_report .. [SKIP]
23:26:31  14 of 19 SKIP relation facebook_ads_facebook_ads.facebook_ads__ad_report ....... [SKIP]
23:26:31  15 of 19 SKIP relation facebook_ads_facebook_ads.facebook_ads__ad_set_report ... [SKIP]
23:26:31  16 of 19 SKIP relation facebook_ads_facebook_ads.facebook_ads__campaign_report . [SKIP]
23:26:32  12 of 19 OK created sql table model facebook_ads_facebook_ads_source.stg_facebook_ads__creative_history  [CREATE TABLE (15.0 rows, 2.7 KB processed) in 2.43s]
23:26:32  17 of 19 START sql table model facebook_ads_facebook_ads.facebook_ads__url_tags  [RUN]
23:26:34  17 of 19 OK created sql table model facebook_ads_facebook_ads.facebook_ads__url_tags  [CREATE TABLE (6.0 rows, 958.0 Bytes processed) in 2.20s]
23:26:34  18 of 19 START sql view model facebook_ads_facebook_ads.int_facebook_ads__creative_history  [RUN]
23:26:36  18 of 19 OK created sql view model facebook_ads_facebook_ads.int_facebook_ads__creative_history  [CREATE VIEW (0 processed) in 1.38s]
23:26:36  19 of 19 SKIP relation facebook_ads_facebook_ads.facebook_ads__url_report ...... [SKIP]
23:26:36  
23:26:36  Finished running 7 view models, 12 table models in 0 hours 0 minutes and 14.53 seconds (14.53s).
23:26:36  
23:26:36  Completed with 1 error and 0 warnings:
23:26:36  
23:26:36  Runtime Error in model stg_facebook_ads__basic_ad_tmp (models/tmp/stg_facebook_ads__basic_ad_tmp.sql)
23:26:36    404 Not found: Table daikin-be:facebook_ads.basic_ad was not found in location EU
23:26:36    
23:26:36    Location: EU
23:26:36    Job ID: 8b32ee04-6654-49a7-aa08-f93b1566c9a3
23:26:36    
23:26:36  
23:26:36  Done. PASS=12 WARN=0 ERROR=1 SKIP=6 TOTAL=19

Expected behavior

The basic_ad table should be created given the basic config of the Facebook Ads connector. All Quickstart transformations should run succesfully.

dbt Project configurations

These are Quickstart transformations provided by Fivetran.

Package versions

These are Quickstart transformations provided by Fivetran.

What database are you using dbt with?

bigquery

dbt Version

1.6.6

Additional Context

No response

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

fivetran-joemarkiewicz commented 1 year ago

Hi @Jeralckx thanks for opening this issue. That is strange that the Quickstart package did not capture the basic_ad table if it was already created in the connector. I wonder if the name of the basic_ad table is different from what the package is expecting. Would you be able to inspect your tables and let me know the exact name of the basic_ad table in your warehouse?

Jeralckx commented 1 year ago

Hi @fivetran-joemarkiewicz, thanks for getting back to me.

That's just the thing... Although my Facebook Ads connector schema says it's syncing the basic_ad table:

image

The table isn't stored whatsoever in BigQuery:

image

fivetran-joemarkiewicz commented 1 year ago

Thanks for sharing @Jeralckx. That is peculiar 🤔. Unfortunately, my team does not have much insight into the nuances of the connector. I would expect the basic_ad to be synced in your target schema.

I would recommend opening a support ticket for our customer support and connector team to help you understand why the basic_ad is not syncing for you. Once that is syncing to your destination, the data model should succeed as expected!

fivetran-joemarkiewicz commented 1 year ago

I will close this ticket and mark as wont fix as this is an issue that needs to be addressed at the connector with how the basic ad table is synced.

henok commented 9 months ago

I'm curious how this was resolved for you. I am experiencing he same issue. he facebook ads account is not mine but one that I have been added to. I suspect there are some permissions or access rights missing.