[destination-postgres] Intelligently handle truncation of long column names

[alvinwanyeki]

Connector Name

source-airtable

Connector Version

4.1.6

What step the error happened?

During the sync

Relevant information

Issue

• When syncing data from Airtable to a database or datawarehouse destination. Columns exceeding the character limit in the destination are trunctated (expected behavior).
• Airbyte fails to intelligently handle instances where multiple columns from the same table that have the same 'leading' characters which leads the sync failing because the destination throws an error around there being multiple instances of the same column.

Illustration

• If we have table foo from Airtable with the following columns (note that the last word is different across both columns):
  • this_is_a_column_with_an_overly_exceedingly_long_name
  • this_is_a_column_with_an_overly_exceedingly_long_title
• The above columns will be truncated to:
  • this_is_a_column_with_an_overly_exceedingly_long_
  • this_is_a_column_with_an_overly_exceedingly_long_
• This will lead to the db throwing an error (and the sync failing) because the columns have the same name.

Suggested Solution

• Airbyte should detect instances where column names being truncated will lead to instances of different columns ending up with the same name -- it could append a number to the column (the nth instance of the column), so the fields in the example above would be:
  • this_is_a_column_with_an_overly_exceedingly_long1
  • this_is_a_column_with_an_overly_exceedingly_long2

Relevant log output

No response

Contribute

• Yes, I want to contribute

[marcosmarxm]

What destination are you using? Are you still using normalization or Dv2 destination?

[alvinwanyeki]

@marcosmarxm -- we're using destination-postgres, Dv2.

[marcosmarxm]

Thanks @alvinwanyeki it looks the problem should be solved by the destination and not by the source. I'll update the ticket.