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

Community

Connector Improvement: Keep MySQL TINYINT(1) Data Type

Answered
John Means User

The Fivetran MySQL connector currently converts TINYINT(1) columns with 1s and 0s to BOOLEAN in Snowflake. This conversion is unnecessary and we would like the column type to remain the same from source to destination, wherever possible. 

Can we disable this conversion?

Please sign in to leave a comment.

Comments

4 comments

Date Votes
  • Official comment
    Kevin Kim Fivetranner

    Hi John and Bryan,

    Thanks for your feedback here. One key part of our product is to automate as much of the data pipeline workflow as possible. In MySQL databases, BOOLEAN is a synonym for TINYINT(1), so if a user creates a BOOLEAN column, they actually get stored in the database as TINYINT(1) with 0's and 1's representing corresponding values.

    This automatic transformation is implemented to keep in line with MySQL's handling of bools. Would it be possible to perform a post-load transformation for this scenario?

  • Bryan User

    Need this feature to be implemented as it causes confusion and more importantly different data which will hinder how we move forward with a key initiative we have next year.  In my mind.. if it's TINYINT in the source, it should be TINYINT in the target.. otherwise we are losing the basic concept of Extract/Load

  • Fiona Zhao User

    In theory, the sources can choose to use tinyint(1) for real tiny integer, or use it to store TRUE and FALSE as 1 and 0,   If internally fivetran infer the destination type from data value, it looses the consistency and transparency to destination systems. As from destination perspective, we may unexpectedly experience type change that can break downstream transformation logic and often times no good way of knowing it ahead of time.   The harm of save actual BOOLEAN as integer 0s and 1s maybe that it increases the storage cost on destination space, I can't think of any other harms. But  the harm that comes with infer and change type dynamically from data value is quite obvious and hard to have a good workaround.

    Can Fivetran just map tinyint to INTEGER?

  • John Feilmeier User

    So the official resolution (for now) is to apply a "Transformation" to cast these columns as TINYINT(1)? Or just to cast them as INTEGER?