Zendesk support ticket object schema conflict with BigQueryCache

Inizia riproducendo il malfunzionamento con il flusso dei ticket di Zendesk Support, BigQueryCache e i valori di satisfaction_rating mostrati, quindi esamina get_stream_json_schema(), get_records() e source.read(cache). Confronta la gestione di BigQueryCache con quella di DuckDBCache. Il lavoro è completato quando i valori misti di oggetto, stringa e null non fanno più rifiutare a BigQuery i dati dei ticket.

Hello 👋

I'm using PyAirbyte to fetch some data from Zendesk Support connector (mainly tickets) and I would like to use BigQueryCache to store the tickets data before sending them to an established BigQuery table.

The issue I'm facing is that some columns from Zendesk tickets have several types defined by Airbyte such as :

satisfaction_rating': {'type': ['null', 'object', 'string']

But then when I'm trying to read the tickets data and store it in BigQuery Cache table I have the following error because the column has been infered as a string while it's in reality a json (ex : 'satisfaction_rating': {'score': 'unoffered'}) ⬇️

google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more details.; reason: invalid, message: Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more details.; reason: invalid, message: Error while reading data, error message: JSON processing encountered too many errors, giving up. Rows: 1; errors: 1; max bad: 0; error percent: 0; reason: invalid, message: Error while reading data, error message: JSON parsing error in row starting at position 0: JSON object specified for non-record field: satisfaction_rating

This is a type inconsistency issue between the inferred schema (string or object) and the actual data, where satisfaction_rating is sometimes a JSON object (e.g., {'score': 'unoffered'}), and sometimes a string or null.

This causes BigQuery to fail because it expects a consistent type per field (e.g., either a STRING, RECORD, or NULLABLE RECORD), and in this case, Airbyte is attempting to use STRING due to the union type, but the data is a JSON object, not a serialized string.

Here is the code that I'm using ⬇️

source = ab.get_source(
    'source-zendesk-support',
    config={
        'subdomain': 'test',
        'credentials': {
            'api_token': 'XXX',
            'email': 'zendesk_api@test.com/token',
            'credentials': 'api_token'
        },
        'start_date': start_date
    },
    install_if_missing=False
)

cache = BigQueryCache(
    project_name='data-sandbox',
    dataset_name='pyairbyte_cache',
    dataset_location='europe-west1',
    credentials_path='XXX'
)

source.check()
source.select_streams(['tickets'])
print(source.get_stream_json_schema('tickets'))

record_sample = next(source.get_records('tickets'))
print(record_sample)
result = source.read(cache)

I tried using get_records() method instead and overwriting the field type but I think source.cache() is the only way to write within a cache as there doesn't seem to be another method to specify a custom schema in a cache table.

Also, I noticed that DuckDBCache manage to automatically serialize these kind of columns to string but not BigQueryCache 🤔

Here are the versions that I'm using ⬇️

airbyte                             0.28.0
airbyte-api                         0.52.2
airbyte-cdk                         6.56.5
airbyte_protocol_models_dataclasses 0.17.1
airbyte_protocol_models_pdv2        0.13.1
airbyte-source-zendesk-support      4.9.1
google-api-core                     2.25.1
google-auth                         2.40.3
google-cloud-bigquery               3.30.0
google-cloud-bigquery-storage       2.32.0
google-cloud-core                   2.4.3
google-cloud-secret-manager         2.24.0

Could you please advise on this issue ? :)
Thanks