[FEATURE] Missing table 'keyword_report' in dbt Pinterest package

[Igor-Kuzenkov]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

Recently we set Pinterest Fivetran connector and enabled Pinterest in dbt_project.yml of our existing ad_reporting project where FB, Google and Tiktok are already enabled and working well.

However, Pinterest part fails because no table keyword_report was loaded into Fivetran target schema (which is a source schema for ad_reporting dbt project).

Relevant error log or model output

Extracts from our `dbt deps` and `dbt run` logs:

1) dbt deps

13:11:51  Running with dbt=1.3.2
13:11:55  Installing fivetran/ad_reporting
13:11:56    Installed from version 1.1.0
13:11:56    Up to date!
...

2) dbt run

13:12:54  24 of 97 START sql view model <my_schema>.stg_pinterest_ads__keyword_report_tmp  [RUN]
13:12:55  24 of 97 ERROR creating sql view model <my_schema>.stg_pinterest_ads__keyword_report_tmp  [ERROR in 1.07s]
...
13:13:28  57 of 97 SKIP relation <my_schema>.stg_pinterest_ads__keyword_report ... [SKIP]
...
13:13:52  81 of 97 SKIP relation <my_schema>.pinterest_ads__keyword_report ....... [SKIP]
...
13:14:02  91 of 97 SKIP relation <my_schema>.ad_reporting__keyword_report ........ [SKIP]
...
13:14:10  Completed with 1 error and 0 warnings:
13:14:10  
13:14:10  Database Error in model stg_pinterest_ads__keyword_report_tmp (models/tmp/stg_pinterest_ads__keyword_report_tmp.sql)
13:14:10    002003 (42S02): SQL compilation error:
13:14:10    Object '<src_database_name>.<src_schema_name>.KEYWORD_REPORT' does not exist or not authorized.
13:14:10    compiled Code at target/run/pinterest_source/models/tmp/stg_pinterest_ads__keyword_report_tmp.sql
13:14:10  
13:14:10  Done. PASS=93 WARN=0 ERROR=1 SKIP=3 TOTAL=97

Expected behavior

Fivetran DBT package ad_reporting works correctly when Pinterest Ads is enabled.

dbt Project configurations

vars:
  ad_reporting__pinterest_ads_enabled: True

Package versions

packages:
  - package: fivetran/ad_reporting
    version: [">=1.1.0"]

What database are you using dbt with?

snowflake

dbt Version

Core:
  - installed: 1.3.2   
  - latest:    1.3.2 - Up to date!

Plugins:
  - snowflake: 1.3.0 - Up to date!

Additional Context

The unclear thing here is that the mentioned table keyword_report is selected to be loaded in Schema settings of Fivetran Pinterest connector but this table is not presented in Pinterest ERD model (link) and, as a result, it's not loaded. It's unclear what is the issue:

1. Missing table in Pinterest database model (but this table is expected by ad_reporting) or
2. ad_reporting refers to the Pinterest table keyword_report that might be obsolete at the moment

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

[fivetran-joemarkiewicz]

Hi @Igor-Kuzenkov thank you for raising this issue with our team.

As you mentioned, this issue seems to stem from the keyword related reports in your warehouse not existing. Unfortunately, at this moment the dbt package assumes these tables are present and does not offer a functionality to turn them off. However, this is something we can explore in a future update to the package.

In the meantime, I would like to explore the reason these tables are not present in your warehouse. Especially since you have selected them to load. Do you by chance know if you use keywords in your Pinterest account? Also, can you confirm that your first initial sync has completed?

[Igor-Kuzenkov]

In the meantime, I would like to explore the reason these tables are not present in your warehouse. Especially since you have selected them to load.

I have keyword_report table selected to load on Fivetran connector/Schema tab because this is in the list of sources for Pinterest Ads and for keyword report. However, as I said above, this table is not mentioned in Pinterest Ads ERD (https://fivetran.com/docs/applications/pinterest-ads#schemainformation). And most probably it's not in Pinterest database (I don't know this for sure).

Do you by chance know if you use keywords in your Pinterest account?

We are not using keywords at the moment but the plan is to start using in the next few weeks.

Also, can you confirm that your first initial sync has completed?

Initial sync is definitely completed a week ago. No issues detected.

[fivetran-joemarkiewicz]

Thanks for the additional information @Igor-Kuzenkov. This table should be included in the ERD you linked. I will share this with our product team to ensure they include the table in the ERD.

One behavior of Fivetran to note is that your connector will only sync data into a table if you have data within that endpoint. For example, although you selected to sync the keyword_report, it will not actually load in your warehouse until you have keyword data to sync.

This seems to be the crux of the issue. Once you start using keyword data, you should see the keyword_report populated and the dbt package successfully run!

As I mentioned, we can explore adding a variable to this package (and the greater ad reporting package) that disables the keyword report for Pinterest. However, this seems like it may not be your preferred approach as you will ultimately want to use this data once you start using Pinterest keywords.

Curious to get your thoughts on the variable vs waiting for the keyword data to sync once keywords are being used.

[Igor-Kuzenkov]

Hi @fivetran-joemarkiewicz ! Thank you for taking care of this.

Probably I am the only one who faced this, anyway, as keywords usage is a Pinterest feature, I think dbt ad_reporting should support both cases - when it's enabled and disabled.

For me, as an ad_reporting user, I would prefer to do nothing to to know/remember about one more variable. So, I don't like the idea of introducing a new variable, documenting it, supporting it, and so.

For me, the best option would be to fix the Fivetran connector to create an empty table in any case. Because it's a documented database schema that users expect to get and want to rely on. Currently it looks like the database schema depends on data. Looks odd to me in general.

Not a request, just my thoughts.

[fivetran-joemarkiewicz]

That makes sense to me @Igor-Kuzenkov. I would prefer the same approach where the dbt package user doesn't have to worry about this. However, since it is a feature that may be disable/enabled in Pinterest, I believe the best way to go about addressing this would be a variable within the package to turn off the Pinterest keywords.

I don't believe the Fivetran connector will sync the table unless there is data available within the endpoint. If you would like to raise this as a request to our product team, I would recommend opening a feature request.

In the meantime, I think it may be best to leave this issue open and see if any other users encounter the same issue. If that is the case and others are seeing the same issue, then my team can work to integrate the variable. However, I would agree that the preferred outcome here would to not have to worry about another variable. Let me know your thoughts on this approach.

[Igor-Kuzenkov]

Hi @fivetran-joemarkiewicz! I am not sure what the recent changes to this thread mean (tags, name), however, I checked with my marketing team if they were going to use Pinterest keywords. The answer is: No, they are not going to use keywords for Pinterest. They use keywords for FB, Google but not for Pinterest. Thus, it looks like a true use case. Are you going to somehow manage this case in ad_reporting dbt package? If so, I am Ok with variable you suggested above )).

[fivetran-joemarkiewicz]

Hi @Igor-Kuzenkov the changed name and tags are for our internal prioritization efforts.

Thanks for getting back with the input from your marketing team. The variable seems to be the best approach forward. I will plan to fold this enhancement into our next upcoming sprint to be added to the Ad Reporting package.

[Igor-Kuzenkov]

Three months later...

Any updates on the topic? Our GitHub Actions workflow is still red and I'm still waiting for variable/s to disable Pinterest keywords.

[fivetran-joemarkiewicz]

Hi @Igor-Kuzenkov thanks for bumping your above message. This seems to have been lost in the mix of other updates. I apologize for the delay and will work to rollout the update by next week along with some other features we are incorporating into the Ad Reporting package.

I was actually able to work on this feature and believe I have a working version that you may be able to attempt before we roll the update out in the next official version. If you have availability, you can attempt to install the following version of the Ad Reporting package (which has the ability to disable the Pinterest Ads keyword report).

packages:
  - git: https://github.com/fivetran/dbt_ad_reporting.git
    revision: feature/pinterest-keywords-variable-addition
    warn-unpinned: false 

Once you install this version of the package, you can set the new pinterest__using_keywords variable to false.

vars:
  pinterest__using_keywords: false

This should then succeed in your environment with the only change being the keyword report for pinterest being skipped. Let me know if you are able to attempt this version of the package and if you see success. Thanks again for bumping this message.

[fivetran-joemarkiewicz]

@Igor-Kuzenkov I just wanted to share that I have kicked off our internal review process, but if you are able to give the above branch a test that would be a huge help in ensuring the updates in the PRs above will work for you to disable the pinterest keyword components.

[Igor-Kuzenkov]

@fivetran-joemarkiewicz To be honest, due to my busy schedule, I'm waiting for a release that will manage this issue. Maybe I can find some time to test next week or later. If not released yet. Sorry. Thank you for moving this forward!

[fivetran-joemarkiewicz]

No worries, I am planning to release early next week as the PRs have been approved on our end. You should see the updates live then, but let me know if you are able to test prior. Thanks!

[fivetran-joemarkiewicz]

Hi @Igor-Kuzenkov the functionality highlighted above is now possible in the latest release of the Pinterest and Ad Reporting packages! These changes should be live at the top of the hour. Please review the latest release notes for more details on how to disable the keyword report and for other new features!

As this has been included in the latest release, I will close this issue. Please feel free to reopen this issue or create a new one if you are unable to take advantage of the new feature added to disable the Pinterest keyword reports.