[Bug] ad_group_history - error with coalesce between JSON and string

[omirobarcelo]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

When building a project, there's an error with the stg_tiktok_ads__ad_group_history because it is trying to coalesce a JSON value and a string value.
I believe the issue might come because of an update in Fivetran's export of TikTok Ads.

Relevant error log or model output

Database Error in model stg_tiktok_ads__ad_group_history (models/stg_tiktok_ads__ad_group_history.sql)
  No matching signature for function COALESCE for argument types: JSON, STRING. Supported signature: COALESCE([ANY, ...]) at [208:9]
  compiled Code at target/run/tiktok_ads_source/models/stg_tiktok_ads__ad_group_history.sql

---------------------------

Line causing the error:
https://github.com/fivetran/dbt_tiktok_ads_source/blob/c65d8f5140c502db173f46e8e72f9e1a84c5ce4b/models/stg_tiktok_ads__ad_group_history.sql#L39C9-L39C49

---------------------------
Compiled code causing the JSON - string error
    cast(null as STRING) as 

    age

 , 

    age_groups

 as 

    age_groups

,

Expected behavior

Coalesce shouldn't produce an error.
Maybe age is not needed anymore, avoiding the coalesce, or age can be casted to JSON instead of string.

dbt Project configurations

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

vars:
  ad_reporting__facebook_ads_enabled: True
  ad_reporting__google_ads_enabled: True
  ad_reporting__tiktok_ads_enabled: True
  ad_reporting__microsoft_ads_enabled: True
  # The following submodules of the ad_reporting package will be disabled
  ad_reporting__amazon_ads_enabled: False
  ad_reporting__apple_search_ads_enabled: False
  ad_reporting__pinterest_ads_enabled: False
  ad_reporting__linkedin_ads_enabled: False
  ad_reporting__twitter_ads_enabled: False
  ad_reporting__snapchat_ads_enabled: False
  ad_reporting__reddit_ads_enabled: False

models:
  ad_reporting:
    +schema: ad_reporting
  google_ads:
    +schema: ad_reporting
  google_ads_source:
    +schema: ad_reporting
  facebook_ads:
    +schema: ad_reporting
  facebook_ads_source:
    +schema: ad_reporting
  tiktok_ads:
    +schema: ad_reporting
  tiktok_ads_source:
    +schema: ad_reporting

Package versions

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: dbt-labs/codegen
    version: 0.11.0
  - package: fivetran/ad_reporting
    version: 1.7.0

It also happens with 1.6.1. In production we have 1.6.1 and I tried upgrading to 1.7.0 to see if it fixes it, but it still shows the same error (just changes the error line from [208:9] to [218:9]).

What database are you using dbt with?

bigquery

dbt Version

dbt-bigquery==1.6.5

Additional Context

The Fivetran warning mentioning the migration to use JSON.

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

• [ ] Yes.
• [X] Yes, but I will need assistance and will schedule time during our office hours for guidance
• [ ] No.

[fivetran-joemarkiewicz]

Hi @omirobarcelo! Thanks for opening this issue and raising the failure to our team. This is something that we have been made aware of as well and are currently planning on possible solutions. I will be sure to share more once we have a full solution mapped out.

However, in the meantime I would recommend leveraging the following in your packages.yml in place of your ad reporting package to leverage the patch that I developed earlier today. Let me know if this fixes the initial issue. Thanks!

packages:  
  - git: https://github.com/fivetran/dbt_ad_reporting.git
    revision: patch/to-json-string
    warn-unpinned: false

[omirobarcelo]

Hi @fivetran-joemarkiewicz !
The above package solved the TikTok error but or introduced or showed the next one

Database Error in model facebook_ads__url_tags (models/facebook_ads__url_tags.sql)
  No matching signature for function TRIM for argument types: JSON, STRING. Supported signatures: TRIM(STRING, [STRING]); TRIM(BYTES, BYTES) at [41:38]
  compiled Code at target/run/facebook_ads/models/facebook_ads__url_tags.sql

It seems to be caused for the same reason, the introduction of the JSON type.

[fivetran-joemarkiewicz]

Hi @omirobarcelo a few others have raised this issue as well. I just merged a PR in at the Ad Reporting patch that should address this issue for the time being. Let me know if this solves the issue.

[omirobarcelo]

Hello @fivetran-joemarkiewicz . Yes, the new patch solves the issue. Thank you!

[fivetran-joemarkiewicz]

Hi @omirobarcelo, I wanted to share that the Fivetran JSON datatype support for BigQuery rollout has been paused for the time being. The connector teams are working to find a scalable way for users to migrate their downstream transformations to the JSON datatype.

As such, my team will be holding off on releasing any official updates to the packages until the rollout is resumed. Therefore, I will mark this ticket as wontfix until the rollout is resumed. I encourage anyone who runs into the above mentioned issues to open a Fivetran support ticket. This way if you are interested in going back to the JSON as string datatype then this can be arranged.

For the time being I am not sure when the rollout will be resumed, but I will keep this thread updated when I learn more. Thanks again for your patience!