facebook_ads__url_tag support BigQuery JSON datatype

[KomissarovSemyon]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Materialization of facebook_ads__url_tag fails, when using bigquery adapter

Relevant error log or model output

09:51:16  17 of 24 ERROR creating sql table model dbt_skomissarov_facebook_ads.facebook_ads__url_tags  [ERROR in 2.56s]

09:51:27
09:51:27  Completed with 1 error and 0 warnings:
09:51:27
09:51:27  Database Error in model facebook_ads__url_tags (models/facebook_ads__url_tags.sql)
09:51:27    No matching signature for function TRIM for argument types: JSON, STRING. Supported signatures: TRIM(STRING, [STRING]); TRIM(BYTES, BYTES) at [37:38]
09:51:27    compiled Code at target/run/facebook_ads/models/facebook_ads__url_tags.sql

Expected behavior

Should materialize without error

dbt Project configurations

facebook_ads_schema: fivetran_facebook_ads

Package versions

• package: fivetran/facebook_ads
  version: [">=0.6.0", "<0.7.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt --version
Core:

• installed: 1.3.0
• latest: 1.5.0 - Update available!

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

Plugins:

• bigquery: 1.3.0 - Update available!
• duckdb: 1.3.0 - Update available!

At least one plugin is out of date or incompatible with dbt-core.
You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Additional Context

No response

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
• No.

[fivetran-joemarkiewicz]

Hi @KomissarovSemyon thanks for opening this issue!

We have actually seen a number of these issues sprout up over the last week across all our Fivetran dbt packages. It seems to be due to newer BigQuery driver that handles JSON datatypes differently. My team and I are currently investigating and are hoping to respond soon with a possible fix.

Be sure to follow this issue for more updates!

[fivetran-joemarkiewicz]

This issue has been rolled back at the connector level. I will plan to keep this open however in order to ensure the package can properly support the JSON datatype once it is rolled out at the connector level.

[ryan-systematik]

Hi @fivetran-joemarkiewicz , I hope all is well. I'd like to know if there are any updates to this please.

[fivetran-joemarkiewicz]

Hi @ryan-systematik are you currently encountering this issue on a new or old connector?

[ryan-systematik]

Hi @fivetran-joemarkiewicz , how do I know if I'm using the old or new connector?

[fivetran-joemarkiewicz]

Apologies, I mainly meant was this previously running on an active connector and just started failing due to this error. Or was this a new connector you just set up and are seeing this error?

[ryan-systematik]

Oh,

Apologies, I mainly meant was this previously running on an active connector and just started failing due to this error. Or was this a new connector you just set up and are seeing this error?

I'm using an active connector (since January this year), and it the facebook_ads__url_tag model just failed today.

[ryan-systematik]

Can you perhaps change the cleaned_url_tags under the macro: bigquery__get_url_tags_query into this:

json_extract_array(TO_JSON_STRING(url_tags)) as cleaned_url_tags

[fivetran-joemarkiewicz]

Hi @ryan-systematik thanks for bringing this up. I was able to confirm that this issue has arisen due to a connector change which went live yesterday that is defining JSON fields that were string to be the proper JSON datatype. This however is resulting in the issue you are seeing.

I agree that the solution you proposed should do the trick for users who are have the JSON datatype fields. I actually just created a patch branch with this solution if you wanted to give it a try and let me know if that resolves the issue. Let me know!

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

[autonomous-developer]

@fivetran-joemarkiewicz I was having this same issue while on the BQ Adapter. I tested your patch branch and it worked for me.

[ryan-systematik]

@fivetran-joemarkiewicz Thanks you very much. Can you also do the same thing for the dbt_ad_reporting package?

[fivetran-joemarkiewicz]

Of course! I just merged a PR to a patch branch in the Ad Reporting package that should address this issue temporarily. Let me know if this fixes your ad reporting runs. Thanks!

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

[ryan-systematik]

Thanks @fivetran-joemarkiewicz

[fivetran-joemarkiewicz]

@ryan-systematik @autonomous-developer 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 blocked and wontfix for the time being. 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!