Forum Discussion

crispybc's avatar
crispybc
Frequent Visitor
1 year ago
Solved

Preview Snowflake Connector Bug with boolean values

There seems to be a bug with the new preview Snowflake connector when retreiving BOOLEAN columns using native SQL query.  The values get returned as a string and when converting the column to a TRUE/FALSE column all values are turned to FALSE.

 

When using the connector explorer to return the full table from snowflake, the BOOLEAN columns are correctly detected as TRUE/FALSE columns.

General Comment
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi crispybc - that does seem like a bug. I ran some tests out of curiosity and had exactly the same issue.

     

    I'm not sure what the best approach is while this bug exists, but here are some observations of variations on the query:

     

    1. Native query created using dialog box converts Boolean to text

    -- ❌ Boolean columns > text
let
  Source = Value.NativeQuery(
    Snowflake.Databases("MY_SERVER.snowflakecomputing.com", "MY_WAREHOUSE", [Implementation = "2.0"]){
      [Name = "MY_DATABASE"]
    }[Data],
    "SELECT * FROM MY_SCHEMA.MY_VIEW",
    null,
    [EnableFolding = true]
  )
in
  Source

    2. Setting EnableFolding = false fixes Boolean column type

    I would only recommend this if no further transformation steps a required in Power Query.

    -- ✅ Boolean columns > logical
let
  Source = Value.NativeQuery(
    Snowflake.Databases("MY_SERVER.snowflakecomputing.com", "MY_WAREHOUSE", [Implementation = "2.0"]){
      [Name = "MY_DATABASE"]
    }[Data],
    "SELECT * FROM MY_SCHEMA.MY_VIEW",
    null,
    [EnableFolding = false]
  )
in
  Source

    3. Setting Implementation ="1.0" (old connector) but keeping EnableFolding = true fixes also Boolean column type

    -- ✅ Boolean columns > logical
let
  Source = Value.NativeQuery(
    Snowflake.Databases("MY_SERVER.snowflakecomputing.com", "MY_WAREHOUSE", [Implementation = "1.0"]){
      [Name = "MY_DATABASE"]
    }[Data],
    "SELECT * FROM MY_SCHEMA.MY_VIEW",
    null,
    [EnableFolding = true]
  )
in
  Source

     

3 Replies

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    Hi crispybc ,
    This could be a bug or an issue related to data type interpretation when fetching the results.
    You can try a temporary workaround by setting the column to integer using Advance query option while
    Connecting to a Snowflake database from Power Query:

    SELECT CASE WHEN boolean_column THEN 1 ELSE 0 END FROM your_table;

    If you still encounter any issues you can raise you issues here:
    https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues

    Additionally, you can go through this documentation where you can provide feedback for new preview Snowflake connector and learn about its limitations and known issues
    https://learn.microsoft.com/en-us/power-query/connectors/snowflake#connect-using-advanced-options

    Hope this helps!
    If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    Hi crispybc - that does seem like a bug. I ran some tests out of curiosity and had exactly the same issue.

     

    I'm not sure what the best approach is while this bug exists, but here are some observations of variations on the query:

     

    1. Native query created using dialog box converts Boolean to text

    -- ❌ Boolean columns > text
let
  Source = Value.NativeQuery(
    Snowflake.Databases("MY_SERVER.snowflakecomputing.com", "MY_WAREHOUSE", [Implementation = "2.0"]){
      [Name = "MY_DATABASE"]
    }[Data],
    "SELECT * FROM MY_SCHEMA.MY_VIEW",
    null,
    [EnableFolding = true]
  )
in
  Source

    2. Setting EnableFolding = false fixes Boolean column type

    I would only recommend this if no further transformation steps a required in Power Query.

    -- ✅ Boolean columns > logical
let
  Source = Value.NativeQuery(
    Snowflake.Databases("MY_SERVER.snowflakecomputing.com", "MY_WAREHOUSE", [Implementation = "2.0"]){
      [Name = "MY_DATABASE"]
    }[Data],
    "SELECT * FROM MY_SCHEMA.MY_VIEW",
    null,
    [EnableFolding = false]
  )
in
  Source

    3. Setting Implementation ="1.0" (old connector) but keeping EnableFolding = true fixes also Boolean column type

    -- ✅ Boolean columns > logical
let
  Source = Value.NativeQuery(
    Snowflake.Databases("MY_SERVER.snowflakecomputing.com", "MY_WAREHOUSE", [Implementation = "1.0"]){
      [Name = "MY_DATABASE"]
    }[Data],
    "SELECT * FROM MY_SCHEMA.MY_VIEW",
    null,
    [EnableFolding = true]
  )
in
  Source

     

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    Hi crispybc ,
    Just wanted to check if you had the opportunity to review the suggestions provided?
    If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You