Summary
The user is facing an issue where updates to a ‘geometry’ type column in Postgres are not being correctly synced to Snowflake. The data appears differently when updated through CDC compared to a full drop and refresh. The user suspects a conversion issue between Postgres and Airbyte. Seeking insights on how to resolve this.
Question
hey folks! back again to see if i am over complicating things!, currently I am syncing:
• source: postgres (using cdc) → destination: snowflake
but I have a table with a ‘geometry’ type that is not receiving updates correctly. If I do a full drop of the destination data and refresh, the destination has the original value (converted to a string) as expected, example:
0101000020E6100000B4C876BE9F74624040A4DFBE0EB440C0
but if I let cdc do its thing the updates end up coming in as:
'{"srid":4326,"wkb":"AQEAACDmEAAA0y07xD8zVsAPfuIA+oFFQA=="}'
A little out of my element here, but googling and search the slack hasn’t led me to anything conclusive. My guess is that values get converted into the first example by postgres but the updates in the wal that are pulled by airbyte end up with a more raw format. I’ll keep on studying up on the geometry data type and see what I can find. Part of me hopes that the second example is actually fine and something in snowflake can just read it naturally and/convert it. I have to look into that as well.
If anyone has any insights that would be welcome, appreciate it.
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.