Datatype mess & data management

For products on data management, there is always a big challenge of mapping data types of one data source to another. The challenge becomes bigger for testers and engineers in test who have to test applications and products doing data integration across various databases. Imagine a software product used for data migration from Oracle to SQLServer or there can be any other combination. I faced this issue while verifying data warehousing and migration capabilities. For example: if a table in Oracle is defined as “NUMBER” then how should the target be created in SQLServer because unlike Oracle SQLserver has various datatypes representing NUMBER such as FLOAT, INT, BIGINT,  SMALLINT or NUMERIC? What is the best fit? Should some sample data be considered to guess the data type? Should it be random data sample or something like the MAX value should be able to tell?

To overcome this challenge, I created an utility which tells me the mapping of the data type from one DB to another or how should the conversion work. This is loosely represented by a JSON which defines the conversion rules. For example: If the condition is met that the NUMBER is defined as NUMBER(18,0) or that data precision is less than or equal to 18, the target datatype could be BIGINT.

{
“@source_db”: “ORACLE”,
“@destination_db”: “MS SQL Server”,
“DataTypes”: [
{
“OriginalDataType”: “NUMBER”,
“Condition”: “DataPrecision EQ -1 AND DataScale EQ -1”,
“DestinationDataType”: “FLOAT”
},
{
“OriginalDataType”: “NUMBER”,
“Condition”: “DataPrecision EQ -1 AND DataScale EQ 0”,
“DestinationDataType”: “NUMERIC(38, 0)”
},
{
“OriginalDataType”: “NUMBER”,
“Condition”: “DataPrecision LTE 4 AND DataScale EQ 0”,
“DestinationDataType”: “SMALLINT”
},
{
“OriginalDataType”: “NUMBER”,
“Condition”: “DataPrecision LTE 9 AND DataScale EQ 0”,
“DestinationDataType”: “INT”
},
{
“OriginalDataType”: “NUMBER”,
“Condition”: “DataPrecision LTE 18 AND DataScale EQ 0”,
“DestinationDataType”: “BIGINT”
}, ……………..

The above is an incomplete JSON but this was just to demonstrate how one can create such rules and based on this utilities or applications be built such that this mapping of data type is easily verified. I have created such conversion rules for various other combinations too. I have created an util which I will be sharing in public soon but in case of any questions or people who want to reach out to me or provide any feedback do leave your question in the comment section below.