[Bug] shopify__transactions model is failing due to incomplete JSON field in Redshift

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

It has come to our attention that for some users leveraging the Shopify connector on Redshift, there are some receipt records from the transactions table that are being inserted into their warehouse as a string "null". This is not a support JSON object and also is not a compatible null entry as it is recorded as a string.

This behavior ultimately results in a failure when trying to parse the receipt field within the shopify__transactions model as the string "null" is not interpreted as null or a JSON field. Thus resulting in the below error.

While we should focus on ensuring the connector syncs complete records as this should be expected, but we should also explore how we may make the data model more flexible. We could take a similar approach to JSON catching before parsing as what we did in the recent Fivetran Platform data model release with the inclusion of the fivetran_log_fivetran_parse() macro to allow for better error handling.

This could also be a good time to consider applying the update to fivetran_utils. However, I would want to consider the testing implications before then.

Relevant error log or model output

Error executing query:
SQL Error [XX000]: ERROR: JSON parsing error
  Detail: 
  -----------------------------------------------
  error:  JSON parsing error
  code:      8001
  context:   invalid json object null
  query:     646212249
  location:  loc
  process:   query12345

Expected behavior

The Shopify data model is able to properly parse the compatible JSON fields and ignores the non compatible ones.

dbt Project configurations

Default config for Quickstart

Package versions

v0.10.0

What database are you using dbt with?

redshift

dbt Version

v1.3.1

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 / dbt_shopify