Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Destination Improvement: Fivetran changing data type due to Type Promotion

Answered
Philip User

Fivetran is converting the data type from a FLOAT/NUMERIC to STRING due to type promotion. If the precision is greater than 38 or if its scale is greater than 37, it will be promoted from a FLOAT/NUMERIC to a STRING to prevent the truncation of the value.

The type promotion is intended behavior, but the expectation is if both the source and destination (Oracle and PostgreSQL) can handle the precision/scale of the data, there should be no conversion regardless of how it is stored/handled by Fivetran internally. As far as we are aware, these values fit nicely in both the NUMERIC or DOUBLE PRECISION columns in PostgreSQL. 

If the destination cannot handle the precision, by all means cast it to a STRING. In the past, we noticed it has not been consistent as columns were initially NUMERIC and then gets converted to a STRING. We start patching the models and a couple of weeks later, it turns back to NUMERIC column again. 

We would really like to have to have a predictable/deterministic output at the destination. We are perfectly fine with the following two options.
  1. An option to truncate/round all FLOATS/NUMERIC columns to certain number of digits after decimal.
  2. Alternatively a global option which forces transfer of all FLOATS/NUMERIC columns as STRINGS.

Please sign in to leave a comment.

Comments

1 comment

Date Votes
  • Official comment
    Kevin Kim Fivetranner

    Hi Philip,

    Thank you for your post here! I definitely understand this behavior can be confusing, and will work with the team to clarify this for our users.

    To be clear, this is intended behavior of the platform. Because Fivetran is a heterogeneous data platform, we support many different sources and destinations for our customers. We have prioritized keeping our data type mapping behavior consistent across different destinations. So this means, even if say Postgres supports a specific precision/scale, we want the data we write to Postgres and Snowflake to be as similar as possible. 

    All of this type mapping occurs in our "Core" layer, where we convert data types to our standard Fivetran types, as shown in these docs: https://fivetran.com/docs/getting-started/core-concepts#datatypes

    Since we do not want a customer who is using both Postgres and Snowflake destinations to have different outputs from the same connector, we have this shared data type mapping in our Core layer.

    Are you able to do type transformations after the loading process?