Zendesk Support: Ticket custom fields not extracted properly after normalization

[asyarif93]

Enviroment

• Airbyte version: 0.30.20-alpha
• OS Version / Instance: Amazon Linux / EC2 t3a.large
• Deployment: example are Docker or Kubernetes deploy env
• Source Connector and version: Zendesk Support 0.1.3
• Destination Connector and version: Bigquery 0.4.1
• Severity: Medium
• Step where error happened: When Look table result

Current Behavior

in table tickets item in field custom_fields is {"id":114101613833,"value":"87"} however in table tickets_custom_fields the value field is null

Expected Behavior

in table tickets_custom_fields the value field should be 87

Logs

If applicable, please upload the logs from the failing operation.
For sync jobs, you can download the full logs from the UI by going to the sync attempt page and
clicking the download logs button at the top right of the logs display window.

LOG

replace this with
your long log
output here

Image Reference

Images

custom fields in tickets table

value in tickets_custom_fields table

airbyte generated normalization query

Steps to Reproduce

1. set catalog schema to tickets

Are you willing to submit a PR?

Remove this with your answer.
Yes

[asyarif93]

does when property value is empty object, airbyte might failed generate dbt normalization query?
it this was the case, i can make fix along with PR #7377

(edit) it might be tricky, as value property might be text, numbers or even an array.
considering it has same schema with singer version, i think this was more related with bigquery destination

airbyte/airbyte-integrations/connectors/source-zendesk-support/source_zendesk_support/schemas/tickets.json

Lines 74 to 85 in 03732df

	"custom_fields": {
	"items": {
	"properties": {
	"id": {
	"type": ["null", "integer"]
	},
	"value": {}
	},
	"type": ["null", "object"]
	},
	"type": ["null", "array"]
	},

[sherifnada]

it seems like the cause of this issue is that the value field here does not contain a schema. Therefore the fix here would be to identify the right schema/data types in the catalog, and normalization will be able to address it correctly.