Some PostgreSQL Numeric Columns Are Synced as VARCHAR in Snowflake

Issue

PostgreSQL numeric columns with no explicit precision or scale defined sometimes sync as VARCHAR in Snowflake instead of NUMBER.

Environment

Connector: Amazon RDS for PostgreSQL
Destination: Snowflake

Resolution

To resolve this issue:

In PostgreSQL, define an explicit precision and scale for the affected columns. For example, change the column type to NUMERIC(19,4).
In Fivetran, go to your Amazon RDS for PostgreSQL connection page.
Select the Schema tab.
Find the affected column and click Re-sync.
In the confirmation pop-up window, click Start re-sync.

If you can’t modify the PostgreSQL source, skip step 1 and re-sync the table. This typically applies when a third-party manages the database. Fivetran re-infers the column type from the current data. If the values still exceed Snowflake limits, the column syncs as VARCHAR again.

Cause

This issue occurs when a PostgreSQL numeric column has no precision or scale. Fivetran determines the Snowflake data type from the values observed during the initial sync:

If the precision and scale are within the Snowflake NUMBER limits, we sync the column as NUMBER.
If either appears to exceed the Snowflake limits, we use VARCHAR to prevent data loss.
If the column contains no values, we use VARCHAR.

For more information, see Type transformation and mapping.