[Bug] Error resulting due to url_tags being a JSON field and the downstream models expected a STRING [In BigQuery]

[magoavi]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The model facebook_ads__url_tags.sql fails due to the following error in BigQuery:

13:21:48 Database Error in model facebook_ads__url_tags (models/facebook_ads__url_tags.sql)
No matching signature for function TRIM
Argument types: JSON, STRING
Signature: TRIM(STRING, [STRING])
Argument 1: Unable to coerce type JSON to expected type STRING
Signature: TRIM(BYTES, BYTES)
Argument 1: Unable to coerce type JSON to expected type BYTES at [41:38]
compiled code at target/run/facebook_ads/models/facebook_ads__url_tags.sql

console_output.log

Relevant error log or model output

The model facebook_ads__url_tags.sql (this is in the facebook_ads DBT package) expects url_tags to be a STRING field.

^Tested in BigQuery

Expected behavior

The model facebook_ads__url_tags.sql should work.

Possible solution

Update the stg_facebook_ads__creative_history.sql model to change url_tags to a STRING field for bigquery.

dbt Project configurations

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

profile: 'default'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

models:
dbt_oooo:
models:
facebook_ads_source:
+schema: dbt_src_facebook_ads
facebook_ads:
+schema: dbt_stg_facebook_ads

vars:
facebook_ads_database: oooo-bi
facebook_ads_schema: src_facebook_ads

Package versions

0.8.0

What database are you using dbt with?

bigquery

How are you running this dbt package?

dbt Cloud™

dbt Version

2

Additional Context

No response

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

• Yes.
• Yes, but I will need assistance.
• No.

[fivetran-joemarkiewicz]

@magoavi thanks for opening this issue and sorry to see you're experiencing this failure.

Ultimately this issue is not as straight forward a solve as we would hope. The reason this is more complicated than it seems is because not all Fivetran Facebook Ads connections are syncing this field as a JSON datatype in BigQuery. That being said, we will want to update this to ensure the data model can function properly and successfully regardless of the JSON or string datatype of the fields.

I'm currently investigating ways we can possibly address this. I hope to come back with some ideas and possible solutions in the coming days. I'll share more once we have some ideas. Thanks again!

[magoavi]

Yup, understood. Initially I did a PR to try and fix for BigQuery but did delete it because I understand that this needs to scale to all destinations and thus just forked the repository and make the changes on my end. Thanks for the update thought. Would be great to fix this so that I can revert back to this package instead of my forked repo. Avi Mago

…

On Thu, 27 Feb 2025 at 21:40, Joe Markiewicz < ***@***.*** > wrote: @magoavi ( https://github.com/magoavi ) thanks for opening this issue and sorry to see you're experiencing this failure. Ultimately this issue is not as straight forward a solve as we would hope. The reason this is more complicated than it seems is because not all Fivetran Facebook Ads connections are syncing this field as a JSON datatype in BigQuery. That being said, we will want to update this to ensure the data model can function properly and successfully regardless of the JSON or string datatype of the fields. I'm currently investigating ways we can possibly address this. I hope to come back with some ideas and possible solutions in the coming days. I'll share more once we have some ideas. Thanks again! — Reply to this email directly, view it on GitHub ( #42 (comment) ) , or unsubscribe ( https://github.com/notifications/unsubscribe-auth/AH7TH2WBQL54IXQ63GHJA332R42H5AVCNFSM6AAAAABXJOWYJOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMOBYGQZDONJXGM ). You are receiving this because you were mentioned. Message ID: <fivetran/dbt_facebook_ads_source/issues/42/2688427573 @ github. com> *fivetran-joemarkiewicz* left a comment (fivetran/dbt_facebook_ads_source#42) ( #42 (comment) ) @magoavi ( https://github.com/magoavi ) thanks for opening this issue and sorry to see you're experiencing this failure. Ultimately this issue is not as straight forward a solve as we would hope. The reason this is more complicated than it seems is because not all Fivetran Facebook Ads connections are syncing this field as a JSON datatype in BigQuery. That being said, we will want to update this to ensure the data model can function properly and successfully regardless of the JSON or string datatype of the fields. I'm currently investigating ways we can possibly address this. I hope to come back with some ideas and possible solutions in the coming days. I'll share more once we have some ideas. Thanks again! — Reply to this email directly, view it on GitHub ( #42 (comment) ) , or unsubscribe ( https://github.com/notifications/unsubscribe-auth/AH7TH2WBQL54IXQ63GHJA332R42H5AVCNFSM6AAAAABXJOWYJOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMOBYGQZDONJXGM ). You are receiving this because you were mentioned. Message ID: <fivetran/dbt_facebook_ads_source/issues/42/2688427573 @ github. com>

[fivetran-jamie]

@magoavi FYI currently working on an official fix to this! It'll dynamically figure out if the field is a JSON or string and adjust accordingly 👍