Data type matters.
Problem:
When working with Snowflake data, the KEY fields are all NUMBER(38) type and 19 digits long. After imported into Power BI Desktop, joined two tables using these KEYs, I couldn’t get the correct number of data in Power BI.
Example:
Cause:
In Power BI, there are 3 date type for numbers: Decimal Number, Fixed Decimal Number and Whole Number.
When loading data into Power BI, by default, the keys are converted to Decimal Number type, in which the largest precision that can be represented is 15 digits long.
While the Whole Number type has no digits but allow for 19 digits, both positive and negative. It can represent the largest possible precision of the numeric data types (better than Decimal Number) and also can be useful to control rounding (better than Fixed Decimal Number).
Solution:
- Change the date types of the KEYs to Whole Number. This way, we still keep the numeric data type and save memory space.
- However, we might need more considerations, if saving memory space isn’t our first priority. In some occasion, if the KEYs in Snowflake extend the digit length to be beyond 19, they have to be changed to String type, which in Power BI is Text type. At that point, we need to change the data type in Power BI to Text type, again. Also, it’s not necessary to keep them number types since usually we don’t calculate KEYs, it’s better to convert the KEYs to Text type at the beginning. This way, it match data source fully and always.