How can I change google_ads schema in dbt project?

Question

I have a dbt project and I'm using ad_reporting model. all the sources are working good, except than google_ads. and that's because I don't have google_ads schema, instead I have google_ads_us schema. I tried to change it in main dbt_project.yml file:

vars:
    google_ads_schema: google_ads_us

but still when I'm running this command:

dbt run --select ad_reporting

I keep get this error:

Database Error in model stg_google_ads (models\stg_google_ads.sql)
SQL compilation error:
Object 'DATABASE.HISTORY_GOOGLE_ADS.GOOGLE_ADS__URL_AD_ADAPTER' does not exist or not 
authorized.
compiled SQL at target\run\ad_reporting\models\stg_google_ads.sql

the HISTORY_ prefix is from profiles.yml file (SCHEMA).

Anyone know where else I need to change the schema?

Answer 1

For the package to find the specified vars it has defined, you will need to do four things, all specified in the docs:

1. Make sure that ad_reporting for google_ads is enabled -->

# inside your dbt_project.yml

vars:
  ad_reporting__google_ads_enabled: true

2. Also, make sure that you let dbt know where to look for the google_ads raw data:

# inside your dbt_project.yml

vars:
  google_ads_database: your_database_name  # in our case, `raw`
  google_ads_schema: your_schema_name      # in our case, `google_ads`

3. Define the schemas where dbt will build the models for both google_ads and google_ads_source -->

# inside your dbt_project.yml

models:
  google_ads:
    +schema: my_new_schema_name # leave blank for just the target_schema
  google_ads_source:
    +schema: my_new_schema_name # leave blank for just the target_schema

Since, as according to the docs:

"By default this package will build the Google Ads staging models within a schema titled (<target_schema> + _stg_google_ads) and the Google Ads final models with a schema titled (<target_schema> + _google_ads) in your target database."

4. Specify which API you're going to use (Google Adwords or Google Ads):

# inside your dbt_project.yml

vars:
    api_source: google_ads  # adwords by default and is case sensitive!