dbt: how can I run ad_reporting model (only with google_ads source) from fivetran transformation?

Question

I have a dbt project in BitBucket repo, which I connected to fivetran transformation.

my deployment.yml file contains:

jobs:
    - name: daily
      targetName: dev
      schedule: 0 12 * * * # Define when this job should run, using cron format. This example will run every day at 12:00pm (according to your warehouse timezone).
      steps:
           - name: run models # Give each step in your job a name. This will enable you to track the steps in the logs.
           command: dbt run

my dbt_project.yml file is:

name: 'myproject'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'fivetran'

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
    - "target"
    - "dbt_modules"

vars:
    ad_reporting__pinterest_enabled: False
    ad_reporting__microsoft_ads_enabled: False
    ad_reporting__linkedin_ads_enabled: False
    ad_reporting__google_ads_enabled: True
    ad_reporting__twitter_ads_enabled: False
    ad_reporting__facebook_ads_enabled: False
    ad_reporting__snapchat_ads_enabled: False
    ad_reporting__tiktok_ads_enabled: False
    api_source: google_ads  ## adwords by default and is case sensitive! 
    google_ads_schema: google_ads
    google_ads_database: fivetran
models:
    # disable all models except than google_ads
    linkedin:
        enabled: False
    linkedin_source:
        enabled: False
    twitter_ads:
        enabled: False
    twitter_ads_source:
        enabled: False
    snapchat_ads:
        enabled: False
    snapchat_ads_source:
        enabled: False
    pinterest:
        enabled: False
    pinterest_source:
        enabled: False
    facebook_ads:
        enabled: False
    facebook_ads_source:
        enabled: False
    microsoft_ads:
        enabled: False
    microsoft_ads_source:
        enabled: False
    tiktok_ads:
        enabled: False
    tiktok_ads_source:
        enabled: False
    google_ads:
        enabled: True
    google_ads_source:
        enabled: True

my packages.yml file is:

packages:
    - package: fivetran/ad_reporting
      version: 0.7.0

bottom line: I have a dbt project that needs eventually run from fivetran transformation. which means I cannot push the dbt_packages folder, instead I have the packages.yml file that "installing" the needed packages using the command dbt deps.

after installing the packages, dbt run command will be running and since packages.yml contains ad_reporting package, the run command will cause the ad_reporting model to run. and since in dbt_project.yml we disabled all sources except than google_ads, only google_ads will triggered from ad_reporting.

now all I want is to run dbt ad_reporting model, that includes only the google_ads source. this option is built in and should work.

however, when I run this command LOCALLY

dbt run --select ad_reporting

I get this error:

Compilation Error
dbt found two resources with the name "google_ads__url_ad_adapter". Since these resources have 
the same name,
dbt will be unable to find the correct resource when ref("google_ads__url_ad_adapter") is 
used. To fix this,
change the name of one of these resources:
- model.google_ads.google_ads__url_ad_adapter   (**models\url_adwords\google_ads__url_ad_adapter.sql**)
- model.google_ads.google_ads__url_ad_adapter     (models\url_google_ads\google_ads__url_ad_adapter.sql)

and when I changed manually this file name:

dbt_packages\google_ads\**models\url_google_ads\google_ads__url_ad_adapter.sql**

from google_ads__url_ad_adapter.sql to google_ads**1**__url_ad_adapter.sql (just to avoid duplicate file names, as I read in dbt documentation that file names should be uniques even if they are in different folders,

everything worked just fine.

but, as I said before, I need this project to run from fivetran transformation, not locally. and when I push this project to it's repo, I don't push the dbt_packages folder, since a dbt project should be up to 30 MB size. and then, according to packages.yml file, dbt deps command executed, and then the project could run. BUT- as I showed, I needed to change file name MANUALLY, and now, when I cant push dbt_packages folder, dbt deps "downolading" the files, and as you saw, there is a bug: 2 files are coming from installation with same name. that's why when the fivetran transformation is trying to run the command dbt run - I get this error again:

Compilation Error
dbt found two resources with the name "google_ads__url_ad_adapter". Since these resources have 
the same name,
dbt will be unable to find the correct resource when ref("google_ads__url_ad_adapter") is 
used. To fix this,
change the name of one of these resources:
- model.google_ads.google_ads__url_ad_adapter    (models/url_google_ads/google_ads__url_ad_adapter.sql)
- model.google_ads.google_ads__url_ad_adapter    (models/url_adwords/google_ads__url_ad_adapter.sql)

what can I do to enable ad_reporting run from fivetran transformation without this compilation error? and how is it possible that dbt produces this dupliacte file names, after writing in documentation that file names should be unique?

Answer 1

For the ad_reporting package to run it needs atleast 2 data sources. For more info setting up the ad_reporting look at this answer from the Fivetran team:

https://github.com/fivetran/dbt_ad_reporting/issues/48

Answer 2

I found a solution.
as I said, the problem was the unnecessary file dbt_packages\google_ads\models\url_google_ads\google_ads__url_ad_adapter.sql.
so in deployment.yml file, I added step that delete the file,
now the deployment.yml file looks like: (I added the step called 'delete unnecessary file')

    jobs:
    - name: daily
   targetName: dev
   schedule: 0 12 * * * # Define when this job should run, using cron format. This example will run every day at 12:00pm (according to your warehouse timezone).
   steps:
     - name: delete unnecessary file
       command: dbt clean
     - name: run models
       command: dbt run # Enter the dbt command that should run in this step. This example will run all your models.

also I had to add dbt clean command that looking for paths declared in dbt_project.yml
so I added the problematic folder path, like that: (I added the last line)

clean-targets:         # directories to be removed by `dbt clean`
- "target"
- "dbt_modules"
- "dbt_packages/google_ads/models/url_adwords"

and now after pushing the project, when fivetran transformation is running the project, using dbt_project.yml file, the first step is deleting the duplicate file and then the dbt run command could run just fine.

problem solved :)