Snowflake column with datatype number(p > 15,0) is detected as a decimal number of 15 in PQ

[aj1973_]

Hello. There is a problem when a Snowflake table has columns with datatype number(p > 15,0) column is detected as a decimal number of with a maximum precision of 15 in Power BI desktop. Basically all our primary keys / foreign keys are defined with number(p > 15,0). This then makes Power BI direct query a no-go for us. This is not a limitation of Power BI. More so Snowflake datatype always appearing like decimal in their declaration as numeric(p,s) prevents Power BI desktop to use the whole number datatype for Snowflake columns with number(p,0) data types

[v-xiaoyan-msft]

Hi @aj1973_ ,

The step is not supported in DirectQuery mode. How did you convert the datatype with import mode?

Based on the above information, if you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[samsonfr]

When using Import mode, there is no problem : you can use Power Query to convert to Whole Number (integer) which supports 19 digits.

This is a real problem because converting a column from Decimal Number to Whole Number (or Fixed Decimal Number) is not supported in DirectQuery/Composite on Snowflake. The only supported option is converting to Text which generate { fn convert("SnowflakeColumnName", SQL_VARCHAR) } statements in all queries.

For Snowflake, everything is a NUMBER(1-38 digits, decimals). When you create a column with SMALLINT, INT, or BIGINT, it is a synonym for NUMBER(38, 0).

Power BI decimal number only support 15 digits of precision. Whole Number support 19 digits. Fixed decimal number support 19 digits with 4 decimals.

It would be nice if Power BI Snowflake connector would automatically detect NUMBER(p, 0) Snowflake columns as Whole Number. It would work for Import/DirectQuery/Composite.

It could also be nice to detect NUMBER(19, 4) as fixed decimal number as well.

Thanks for considering it.

Frederick

[aj1973_]

@samsonfr

Thank you Fred, you described very well.

@v-xiaoyan-msft 

Just wanted to add this :

When ingesting a Snowflake data source, integers are not stored as Whole Numbers but rather as Decimal. This is a problem because of Schema drift, meaning that when columns are added or changed I have to go into Power BI and update the data types in Power Query. If they were detected properly to begin with that wouldn't happen. The issue might be a result of how Snowflake stores integers, as "NUMBER(38,0)".

Amine

[samsonfr]

Update : Snowflake was able to make a change in their product so that NUMBER(p, 0) columns are detected as Whole Number by Power BI. They did it for all NUMBER(p, 0) :  p in [1, 38].

We no longer need to manually convert to Whole Number in Import mode or to Text in DirectQuery.

It solves all our problems with the added benefit that integer numbers are properly formated by default (instead of having 2 decimals). For example, a year number, stored as NUMBER(4, 0) in Snowflake will be recognized as Whole Number in Power BI and displayed as 2022 by default (instead of 2022.00). One could always change the formating option but it was an extra step.

For now, the change has been made locally but at least you know you can contact Snowflake if you face the same problem.

Thanks Snowflake!

Frederick

[SamritiGrewal]

We are facing similar issue , Any update on this issue resolution

[aj1973_]

Hi @SamritiGrewal 

Yes Snowflake took over the issue and they are going to apply a fix to it.

It is still under test, but it worked for us.

Good luck

[SamritiGrewal]

@aj1973_  , Thanks for the update , you explanied this issue very well for Snowflake data source(and datatypes) used in Power BI , I am facing another similar issues using Date/Time field in Snowflake to setup incremental (especially Hybrid/Direct mode) refreshes. What should be recommended data type needs to be defined in Snowflake to setup Power BI incremental refresh correctly on that field . Also Is there a link/documentation of snowflake avaiable so that I can follow up once this update is available.

[jeffshieldsdev]

Any update on this?  I would like my NUMBER(1,0) in DirectQuery to NOT be treated as DECIMAL by Power BI.