search

fivetran / dbt_linkedin

Fivetran's Linkedin Ads dbt package
https://fivetran.github.io/dbt_linkedin/
Apache License 2.0
2 stars 12 forks source link

function pg_catalog.date_add("unknown", integer, timestamp with time zone) does not exist #19

Closed ivanblagdan closed 2 years ago

ivanblagdan commented 2 years ago

Is there an existing issue for this?

Describe the issue

When running the ad reporting package, I'm running into an error with the Linkedin dep. Here's what I'm seeing.

17:53:15.691453 [error] [MainThread]: Database Error in model linkedin__ad_adapter (models/linkedin__ad_adapter.sql)
17:53:15.691886 [error] [MainThread]:   function pg_catalog.date_add("unknown", integer, timestamp with time zone) does not exist
17:53:15.692288 [error] [MainThread]:   HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
17:53:15.692671 [error] [MainThread]:   compiled SQL at target/run/linkedin/models/linkedin__ad_adapter.sql

Relevant error log or model output

No response

Expected behavior

Ideally, the Linkedin models and the ad reporting models would successfully complete their run.

dbt Project configurations

models: pinterest: enabled: false

+schema: pinterest

pinterest_source: enabled: false

+schema: pinterest_source

snapchat_ads: enabled: false

+schema: snapchat_ads

snapchat_ads_source: enabled: false

+schema: snapchat_ads_source

tiktok_ads: enabled: false

+schema: tiktok_ads

tiktok_ads_source: enabled: false

+schema: tiktok_ads_source

facebook_ads: enabled: false

+schema: facebook_ads

facebook_ads_source: enabled: false

+schema: facebook_ads_source

facebook_ads_creative_history: enabled: false

+schema: facebook_ads

twitter_ads: enabled: false

+schema: twitter_ads

twitter_ads_source: enabled: false

+schema: twitter_ads_source

microsoft_ads: enabled: false

+schema: microsoft_ads

microsoft_ads_source: enabled: false

+schema: microsoft_ads_source

linkedin:

enabled: false

+schema: linkedin

linkedin_source:

enabled: false

+schema: linkedin_source

google_ads:

enabled: false

+schema: google_ads

google_ads_source:

enabled: false

+schema: google_ads_source

vars: api_source: google_ads ad_reportingpinterest_enabled: False ad_reportingmicrosoft_ads_enabled: False ad_reportinglinkedin_ads_enabled: True ad_reporting__google_ads_enabled: True ad_reportingtwitter_ads_enabled: False ad_reportingfacebook_ads_enabled: False ad_reporting__snapchat_ads_enabled: False ad_reportingtiktok_ads_enabled: False microsoft_ads_schema: bing microsoft_ads_database: fivetran linkedin_schema: linkedin_ads linkedin_database: fivetran
pinterest_schema: pinterest pinterest_database: fivetran twitter_ads_schema: twitter twitter_ads_database: fivetran
facebook_ads_schema: facebook facebook_ads_database: fivetran google_ads_schema: adwords google_ads_database: fivetran snapchat_schema: snapchat snapchat_database: fivetran tiktok_ads_schema: tiktok tiktok_ads_database: fivetran

Package versions

packages:

What database are you using dbt with?

redshift

dbt Version

$ dbt --version installed version: 1.0.4 latest version: 1.0.5

Your version of dbt is out of date! You can find instructions for upgrading here: https://docs.getdbt.com/docs/installation

Plugins:

Additional Context

No response

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

ivanblagdan commented 2 years ago

Attaching the debug log of the run: dbt.log

fivetran-joemarkiewicz commented 2 years ago

@ivanblagdan thanks so much for opening this issue!

I did some initial scoping on this bug and believe to have found the solution. I have a feeling Fivetran is syncing your HubSpot timestamp fields into your warehouse as timestamptz. This in fact causes a downstream error when leveraging date functions as Redshift does not allow for timestamptz to be used in date functions.

Thankfully, we can leverage {{ dbt_utils.type_timestamp() }} in order to safely cast these timestamps and ensure they are able to be used within date functions! I have just made changes to the dbt_linkedin_source that should fix this error. Would you be able to try the following new branch of the dbt_ad_reporting package in your packages.yml and let me know if this error still persists?

##packages.yml

packages:
  - git: https://github.com/fivetran/dbt_ad_reporting.git
    revision: bugfix/timestamp-casting
    warn-unpinned: false
ivanblagdan commented 2 years ago

@fivetran-joemarkiewicz did the trick. I got the whole ad-reporting model built and working. Let me know if you want me to provide any more info.

fivetran-joemarkiewicz commented 2 years ago

That's great to hear!! I will move this PR mentioned above into review.

This PR most likely will not be able to be reviewed until late next week (when our next sprint starts). However, in the meantime, I will not make any changes to the existing branch and you are welcome to leverage the branch version. Feel free to follow this issue or the PR for when it is official merged and released.

Thanks again for raising this issue and your help in identifying the solution đŸ˜„