I am setting up a data pipeline to Snowflake for our Data Science team. I have columns currently defined as FLOAT, but our analysts are asking for NUMBER(38,0) for certain ID fields. Does the choice of numeric data type affect the micro-partitioning or query speed in Snowflake? I want to ensure the data types are optimized for both storage and analytical performance.
3 answers
Snowflake is great because it handles the storage efficiently regardless, but defining the precision helps the BI tools interpret the data correctly.
In Snowflake, the choice between FLOAT and NUMBER is significant. FLOAT is a 64-bit double-precision floating-point, while NUMBER is a fixed-point type. For IDs or any value where exactness is required, always use NUMBER. Snowflake is highly optimized for "compressed" storage, so if you use a smaller precision, like NUMBER(10,0), it won't necessarily save a ton of disk space because of the way it stores data in micro-partitions, but it serves as a crucial constraint for your data quality. For machine learning features like weights or probabilities, FLOAT is perfectly fine and often faster for mathematical computations.
Are you seeing any "bottlenecks" when joining these ID columns if they are set as FLOAT versus NUMBER?
Kenneth, we did see some weirdness where a join failed because of scientific notation in the FLOAT column. That's a classic issue. Joining on FLOAT is risky because 10000 might be stored as 10000.00000000001, failing the equality check. For Join keys, always, always use an integer-based NUMBER type to ensure the joins are deterministic and fast.
Good point, Angela. Our Power BI reports were showing IDs with commas and decimals until we fixed the data type in the warehouse.