Snowflake connector direct query not returning integer surrogate key only decimal/float

[oliversc]

I am in the process of porting a large 100% imported (from postgres) PowerBI model to use Snowflake as the direct query source for some of the larger tables while continuing to import other dimension and aggregation tables.

In PowerBI, we have an external application that has generated integer surrogate keys that we've used to define relationships in PowerBI. 
However when I attempt to return these keys from Snowflake and preview the results in PBI Desktop's PowerQuery viewer, these fields appear as decimal datatypes, not integers.   I cannot use PowerQuery to convert the data type to integer and retain DirectQuery.

Our Snowflake data loading logic specifies integer in the CREATE TABLE statement, but Snowflake creates all numeric datatypes internally as Number(38) which direct query sees as decimal/floating point number which I suspect will not perform as well in PowerBI relationship definitions.

Am I misinterpreting how PowerBI is seeing the datatypes, or has anyone found a way to return integer datatypes from Snowflake via PowerBI direct query?

TIA,

Oliver

[oliversc]

Hi Jayee,  I want to use Snowflake as a direct query data source and do not want to import the data. 
If I try to change the datatype to 'Whole Number' in PowerQuery, then I can only import (rather than direct query) the table which defeats the objective of having a smaller in-memory dataset.  Thanks for responding anyway. 
Best regards, Oliver

[Jayee]

Hi @oliversc 

I'm having the same problem.  It seems to be caused by the fact that Snowflake treats all integer values as number(38,0).  So, when this is imported into PBI, it is seen as a decimal type. 

As you are using Drirect query in PBI model view you can change the datatype to wholenumber for that column it will not impact much and in dataset it will treat as wholenumber itself.

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!