[Bug] redshift json_array_length(super) error

[teoria]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

This error occurs when using Amazon Redshift as your database for the fivetran/facebook_ads dbt package. Redshift's SUPER data type (used for storing raw JSON) requires specific, case-sensitive JSON functions, and json_array_length(super) is either unsupported or improperly applied in this context.

models/facebook_ads__url_tags.sql
on macro get_url_tag_query.sql

  flattened_url_tags as (

      select
          source_relation,
          _fivetran_id,
          creative_id,
          json_extract_array_element_text(required_fields.url_tags, numbers.generated_number::int - 1, true) as element
      from required_fields
      inner join numbers
          on json_array_length(required_fields.url_tags) >= numbers.generated_number
       
  ), 

for redshit i need add json_serialize() function

  flattened_url_tags as (

      select
          source_relation,
          _fivetran_id,
          creative_id,
          json_extract_array_element_text(json_serialize(required_fields.url_tags), numbers.generated_number::int - 1, true) as element
      from required_fields
      inner join numbers
          on json_array_length(json_serialize(required_fields.url_tags) ) >= numbers.generated_number
       
  ), 

Relevant error log or model output

00:46:17  Completed with 1 error and 0 warnings:
00:46:17  
00:46:17    Database Error in model facebook_ads__url_tags (models/facebook_ads__url_tags.sql)
  function json_array_length(super) does not exist
  compiled code at target/run/facebook_ads/models/facebook_ads__url_tags.sql

Expected behavior

the table creation works only with the json_serialize()

Possible solution

flattened_url_tags as (

  select
      source_relation,
      _fivetran_id,
      creative_id,
      json_extract_array_element_text(json_serialize(required_fields.url_tags), numbers.generated_number::int - 1, true) as element
  from required_fields
  inner join numbers
      on json_array_length(json_serialize(required_fields.url_tags) ) >= numbers.generated_number

),

dbt Project configurations

vars:

facebook_ads_schema: 'raw_facebook_ads'
facebook_ads_database: 'dw_v2'

models:
facebook_ads:
#+schema: facebook_ads # default schema suffix
staging:
+schema: facebook_ads_source # default schema suffix

Package versions

• package: fivetran/ad_reporting
  version: [">=2.4.0", "<2.5.0"]

• package: fivetran/facebook_ads
  version: 1.4.0

What database are you using dbt with?

redshift

How are you running this dbt package?

dbt Core™

dbt Version

1.11.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]

@teoria thanks for raising this issue and for proposing a solution and being open to create a PR to fix this issue.

While the json_serialize() function will solve this when the url_tags field is of type SUPER on Redshift, I do believe this could potentially result in other error types in certain scenarios in the future. For example, the json_serialize() essentially converts the SUPER value into textual JSON. However, if the size of the SUPER field is larger than the allowable string size, this will result in an error. Therefore, we'll want to update this logic to make sure it runs a SUPER compatibile version for Redshift when the datatype is SUPER and retain the existing logic when it's of type varchar.

To do this I suggest we take the following approach:

1. Create a new macro that allows us to interpret the datatype of the url_tags column. Similar to what we've done here in the dbt_kalviyo package, but check for column_type == super and have the result be a boolean instead of converting the column to string.
2. Call that macro in the existing redshift version of the get_url_tag_query macro and have it influence a conditional which will either have us keep the existing approach if the above macro returns false, or use a different set of logic if it's true.
3. The logic if it's true it would parse the SUPER datatype as you would natively without having to convert it back to a string.

Let me know your thoughts on the above approach. If you're open to creating a PR to account for this we would be more than happy to review and consider it in the next release. Otherwise, we can take this on in the upcoming weeks. Thanks!

[teoria]

nice !
i'll add your suggestions and create a pr

thanks for your attention

[fivetran-joemarkiewicz]

Sounds great! Let me know if you have any questions.

[fivetran-catfritz]

Hi @teoria I created a test branch with the updates to support the SUPER datatype. If you’d like to try it out, you can install it by adding the following to your packages.yml in place of the facebook_ads package:

packages:
  - git: https://github.com/fivetran/dbt_facebook_ads.git
    revision: feature/url-tags-super
    warn-unpinned: false 

If you are able to try it out, let me know how it works for you!