[source-postgres] has 2-dimension arrays that are populated as 2-dimension array with null values in Snowflake

[Wamolambo]

Airbyte version

0.57.1

Connector name and version

source-postgres: 3.3.26 destination-snowflake: 3.7.0

What step the error happened?

The error occurs during sync from a Postgres [source] to a Snowflake [destination].

Relevant information

I have an Airbyte job that syncs a table from Postgres [source] to Snowflake [destination]. The Postgres [source] table consists of two columns. The first column (named state) consists of a 1-dimension array and the second column (named covariance) consists of a 2-dimension array.

The table is created with the following script:

create table matrix( state double precision [], covariance double precision[][] );

Records are inserted in the table using the following script:

insert into matrix (state,covariance) values (Array[0.23,0.45,0.29],Array[Array[0.98,0.67,0],Array[0.67,0.41,0.59]]);

The souce table looks as follows:

state | covariance ------------------+---------------------------------- {0.23,0.45,0.29} | {{0.98,0.67,0},{0.67,0.41,0.59}} (1 row)

On Airbyte, the Postgres source, Snowflake destination and connection objects were configured as follows: Postgres [source]

• Source name: Postgres Source
• Host: localhost
• Port: 5432
• Database: airbyte_01
• Schema: public
• Username: ***
• Pasword: ***
• SSL Modes: disable
• SSH Tunnel Mode: No Tunnel

• Update Method: Detect Changes with Xmin system Column

  Snowflake [destination]

  • Destination Name: Postgres Destination
  • Host: localhost
  • Port: 5432
  • Database: airbyte_01
  • Schema: test
  • Username: ***
  • Pasword: ***
  • SSL Modes: disable
  • SSH Tunnel Mode: No Tunnel

Connection

• Configuration
  • Shedule type: Manual
  • Destination Namespace: Destination default
  • Destination Stream Prefix: Mirror source name
  • Detect and propagate schema changes: Propogate all field and stream changes
  • Activate the streams you want to sync
  • matrix
• Normalization
  • Normalized tabular data

The result of the manual sync loads the 1-dimension array column correctly in the destination. However, the 2-dimension array is loaded as a 1 dimension array with NULL values as shown below.

state | covariance --------------------+-------------- [0.23, 0.45, 0.29] | [null, null] (1 row) From our observation, we noticed that the number of null values corrosponds with the number of inner arrays. For example, if you have {{1,2}, {7,8}} at the source than you will get {null, null} at the destination.

The sync history does not show any errors.

Note: This problem also occurs when syncing from Postgres [source] to Postgres [destination].

Relevant log output

No response

Contribute

• [ ] Yes, I want to contribute

[marcosmarxm]

Hello @Wamolambo I added the issue to the connector team backlog for further discussion in next sprint.

[Wamolambo]

@marcosmarxm Thank you for the response. I have added more details on the ticket so the issue can be replicated.