Power BI is unexpectedly and erroneously forcing a CAST operation on specific numeric keys

[joelgordon]

Power BI is unexpectedly and erroneously forcing a CAST operation on specific numeric keys (NUMBER(19,0) / Whole Number), converting them to BIGINT within the generated SQL sent to Snowflake.

We have already opeend a support ticket on this.  Below is the contents of that ticket.

Summary: 

• We initially connected over a Teams meeting and discussed your use of Snowflake as a data source, which you connected to Power BI Desktop via the Snowflake connector. 

• Power BI is applying a CAST operation on specific numeric keys (NUMBER(19,0) / Whole Number), converting them to BIGINT within the generated SQL sent to Snowflake. 

• You have three columns: "CARRIER_KEY_BIGINT," "CARRIER_KEY_NUM19," and "CARRIER_KEY_VARCHAR." The data type for "CARRIER_KEY_BIGINT" and "CARRIER_KEY_NUM19" is set to "Whole number," while "CARRIER_KEY_VARCHAR" is set to "Text." 

• When a 19-digit number is entered in each column, the "CARRIER_KEY_VARCHAR" column displays the data correctly, but the "CARRIER_KEY_BIGINT" and "CARRIER_KEY_NUM19" columns round off the last three digits. 

• We reproduced the issue on our end by pulling data from SSMS (Data source), and when the data type is "Whole number," the last three digits are also rounded off. 

• Our Engineering team has confirmed that this is expected behavior.