NUMBER equivalent of Oracle in Snowflake

1
votes

So I have this pipeline that migrates data from Oracle to Snowflake. There is one column in Oracle that has datatype NUMBER and I used NUMBER(38, 18) in Snowflake for it.

My pipeline started failing yesterday because that column in Oracle now has 21 digit numbers which NUMBER(38, 18) cannot handle in Snowflake. I switched to NUMBER(38, 17) and it worked for now.

Is there any NUMBER equivalent in Snowflake so that it can handle any value from Oracle? I cannot possibly change the Oracle table datatype.

1
oraclesnowflake-cloud-data-platform
Migrating Oracle Database to Snowflake: Reference Manual Appendix B: CONVERTING ORACLE DATA TYPES TO SNOWFLAKE DATA TYPES "Precision and scale will need to be consistent with that of the data values being loaded." – Lukasz Szozda

1 Answers

0
votes

A column defined as NUMBER in oracle (without scale or precision) doesn't have an equivalent in Snowflake unfortunately. It is some variable-length decimal format that isn't the same as a float.

If you are not 100% sure on what the scale / precision should be you can try to use FLOAT in Snowflake but that could lead to rounding errors on big aggregations so you'll need to figure out if it is worth it or not.