Ddestination Snowflake: giving errors for BigQuery source when RECORD type with REPEATED mode

[rengler-noodle]

Connector Name

destination-snowflake

Connector Version

3.1.0

What step the error happened?

During the sync

Revelant information

BigQuery -> Snowflake

airbyte/source-bigquery:0.3.0
airbyte/destination-snowflake:3.1.0

Prior to the dv2 rollout in our airbyte cloud account, there weren't issues loading these columns from BigQuery.

Presently when syncing (even after full table deletion, connector reset, and full re-sync), the _AIRBYTE_META column is presenting the following message:

{
  "errors": [
    "Problem with `user_properties`",
    "Problem with `event_params`",
    "Problem with `items`"
  ]
}

The consistent thing between these columns is that they are RECORD types in BigQuery with REPEATED mode.

The problematic Snowflake columns are Object columns. I believe they were formally Variant columns.

Here is an example of the BigQuery schema (this is google analytics 4 raw data):

The result in Snowflake is that the entire column now has null values. The sync completes successfully.

Hopefully this is enough to work with, I want to avoid pasting any logs or data since this is sensitive data. Happy to try to provide more if needed.

Relevant log output

No response

Contribute

• Yes, I want to contribute

[evantahler]

Are you able to provide a sample record in BigQuery that has this problem?

[edgao]

Reproed this in a toy sync. This looks like a bug in source-bigquery. It's discover-ing repeated columns as not-repeated. E.g. with this source table:

source-bigquery discovered it as just a plain integer

even though it correctly read the record as an array:

Normalization was just dumping that array into a VARIANT column, whereas DV2 actually (a) uses OBJECT columns when the source type is an object (i.e. bigquery record), and (b) verifies that the value is actually an object (so when it's an array, DV2 generates an error in airbyte_meta).

.... we could loosen DV2, by switching back to variant + removing the type-check? But that only solves source-bigquery's repeated record case; it doesn't solve e.g. repeated integer.

[evantahler]

Reclassifying this as a sources bug (cc @prateekmukhedkar).
As source-bigquery is an Alpha connector, we will not be able to prioritize fixing this any time soon... @rengler-noodle are you interested in changing how Discover works for source-bigquery (check out the discoverInternal method)?