I have been working on a large project with Snowflake as our main data source for some time now.
We have built import tables, Direct Query as well as hybrid tables there.
Recently I saw that most numeric columns from Snowflake show up as decimal in Power Query. I did not realize this before but when I tried to change the data type to Whole Number (or Int64 which is the default in Power Query) it alerted me that query folding would break.
This seemed odd at first since in Snowflake the data type was NUMBER(38, 0) which is 38 digits without any commas - see official documentation here.
However I realized that there might be something else going on.
I started converting this column's data type to another numeric data type (percentage, fixed decimal / currency etc.)
Everything would break folding.
Then I started to play around with the Integer types, we have the following in Power Query:
Int8 - breaks folding
Int16 - breaks folding
Int32 - WORKED!!!
Int64 (default) - breaks folding
When I converted the column from Snowflake from decimal, that showed in Power Query, to Int32 it actually worked.
I have to say that I do not know enough about Snowflake to actually understand why this is the case but somehow this solved my problem.
Conclusion
When querying Snowflake data from Power BI data types can be an issue. Especially numeric ones. All columns (to my knowledge) are shown as decimal even though they might be whole number in Snowflake.
If you want to have actual integers in Power BI you can use Int32 instead of the default Int64 to keep query folding in place.
Comentarios