Data Type Customization

Data Type Conversion during a database migration process is something that many users need to personalize and set up precision values more accurately. For this reason, SnowConvert enables the Data Type Customization to specify rules of data type transformation considering data type origin and column name.

Supported Data Types

The following data types are supported

• NUMBER

Structure of JSON

SnowConvert receives a JSON file with the following structure.

{
  "types" : {
  },
  "columns": [
      {
          "nameExpression" : "",
          "targetType" : ""
      }
  ]
}

The object types defines the origin data type and the data type the target should be. Also, you might define a general rule that converts the origin data type with or without precision.

For Example:

"types" : {
    "NUMBER" : "NUMBER(11, 2)",
    "NUMBER(10, 0)" : "NUMBER(3, 0)"
}

The object columns is an array, and each object of the array defines the name or the regex expression of the columns to change the type.

"columns": [
    {
        "nameExpression" : "MONTH",
        "targetType" : "NUMBER(2,0)"
    }
]

When using regular expressions customization, the migration could be affected if the regex isn't correct.

Customization Priority

There might be rules of customization that apply to the same object; however, only one will be chosen. SnowConvert will consider the following considerations to prioritize which rule to follow.

1. The first rule in columns is located from top to bottom.

2. The rule defined in type considering the precision.

3. The general rule is defined for Data Type.

4. The rule is defined in SnowConvert.

Example

Let's say we have the following input code.

Input Code

CREATE TABLE employees (
	employee_ID NUMBER,
	manager_YEAR NUMBER(10, 0),
	manager_MONTH NUMBER(10, 0)
);

And the following renaming information

Customization File (.JSON)

{
    "types" : {
        "NUMBER" : "NUMBER(11, 2)",
        "NUMBER(10, 0)" : "NUMBER(3, 0)"    
    },
    "columns": [
        {
            "nameExpression" : "MONTH",
            "targetType" : "NUMBER(2,0)"
        }
    ]
}

This would be the output code with and without customization data types.

Snowflake output code

Without renaming

CREATE OR REPLACE TABLE employees (
	employee_ID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
	manager_YEAR NUMBER(10) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
	manager_MONTH NUMBER(10) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
;

With renaming

CREATE OR REPLACE TABLE employees (
	employee_ID NUMBER(11, 2),
	manager_YEAR NUMBER(3, 0),
	manager_MONTH NUMBER(2, 0)
)
;

Notice how all NUMBER types are migrated to the types specified in json file.