Type Locking Matrix
This matrix shows how Fivetran maps data types when using type locking.
| Rows - locked type Columns - changed type | Boolean | Short | Int | Long | Float | Double | BigDecimal | String | Instant | LocalDate | LocalTime | LocalDateTime | Json | Binary | Xml |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BOOLEAN | check | check | check | check | check | check | check | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| SHORT | check | check | check | check | check | check | check | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| INT | check | check | check | check | check | check | check | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| LONG | check | check | check | check | check | check | check | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| FLOAT | check | check | check | check | check | check | check | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| DOUBLE | check | check | check | check | check | check | check | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| BIGDECIMAL | check | check | check | check | check | check | check | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| STRING | check | check | check | check | check | check | check | check | check | check | check | check | check | check | check |
| JSON | check | check | check | check | check | check | check | check | check | check | check | check | check | check | check |
| INSTANT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check | check | check | NULL | check | NULL | NULL | NULL |
| LOCALDATE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check | check | check | NULL | check | NULL | NULL | NULL |
| LOCALTIME | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check | check | check | check | check | NULL | NULL | NULL |
| LOCALDATETIME | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check | check | check | check | check | NULL | NULL | NULL |
| BINARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| XML | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
How to use this matrix
This matrix uses the following symbols:
| Symbol | Meaning |
|---|---|
| ✓ | The incoming value can be converted to the locked type. The result may still be NULL if it violates conversion rules. |
| NULL | The incoming value cannot be converted to the locked type. The resulting value is written as NULL in the destination. |
The locked data types used in a destination are listed as row headings. The changed data types of incoming data are listed as column headings.
To check how we map a changed data type to a locked data type:
- Find the changed type in a column heading.
- In that column, go down to the row of the locked type you are interested in.
To check how a locked data type handles different changed data types:
- Find the locked type in a row heading.
- In that row, go across to the columns of the changed types you are interested in.
Conversion rules
Type locking uses the following conversion rules:
- For numeric types,
NaNandInfinityvalues are not converted, and are written asNULLin the destination. - For numeric types, only the whole numbers are converted. For example, values
123and123.0are converted, but123.45is not, and is written asNULLin the destination. - For numeric types, the value of the changed type must be able to be represented by the original locked type, otherwise it is written as
NULL. - For changed STRING type to locked numeric types, the string value is parsed and must be a valid number, otherwise it is written as
NULL. - For changed STRING type to locked date/time types, the string value is parsed and must be a valid date/time, otherwise it is written as
NULL. - For changed LOCALTIME type to locked LOCALDATETIME time, the date portion is set to the Unix epoch date (
1970-01-01), while the original time value is retained. This ensures the resulting value combines a default date with the incoming time. - For changed SHORT, INT, or LONG type to locked BOOLEAN type:
- Value
1→TRUE - Value
0→FALSE - Any other value is written as
NULL
- Value
- For changed FLOAT or DOUBLE type to locked BOOLEAN type:
- Value
1.0or1f→TRUE - Value
0.0or0f→FALSE NaN,Infinity, or any other numeric value is written asNULL
- Value
- For changed BIGDECIMAL type to locked BOOLEAN type:
- Numeric value equal to
1→TRUE - Numeric value equal to
0→FALSE - Any other numeric value (fractional, non-integer, or unrecognized) is written as
NULL
- Numeric value equal to
- For changed STRING type to locked BOOLEAN type:
- Case-insensitive text
"true"or"1"→TRUE - Case-insensitive text
"false"or"0"→FALSE - Any other text that cannot be parsed as a Boolean is written as
NULL
- Case-insensitive text