1 Reply Latest reply on Sep 27, 2018 12:42 PM by patrick.byrne.0

    Error when joining Snowflake MD5_NUMBER in Tableau

    hadi sulistio



      I have a question about using Snowflake MD5_NUMBER in Tableau.

      We have 2 tables in Snowflake joined by a surrogate key. The surrogate key is created using MD5_NUMBER hash.

      It looks like the MD5_NUMBER datatype is not supported by Tableau. We received the following error when trying to join the 2 tables:


      [Snowflake][Support](40470) Conversion error at column 4 and row 1: Numeric value out of range.


      Any insights would be appreciated!





      Below is the steps to re-produce this issue


      Create dim table:

      create table test_dim_md5number (pk_id number, dim_value varchar(100));

      describe table test_dim_md5number;

      insert into test_dim_md5number select md5_number('snowflake'), 'snowflake';

      select * from test_dim_md5number;



      Create fact table:

      create table test_fact_md5number (pk_id number, fact_value varchar(100));

      describe table test_fact_md5number;

      insert into test_fact_md5number select md5_number('snowflake'), 'snowflake';

      select * from test_fact_md5number;


      When you run the describe statement, you will see that the precision for number datatype = 38

      when you count the length of md5_number value, it is 40.

      so, there is definitely a mismatch at the first place.



      Test join in Snowflake à successful:

      select d.pk_id, f.pk_id

      from test_fact_md5number f

      inner join test_dim_md5number d

      on d.pk_id = f.pk_id;

      Snowflake allows you to perform the join!



      Test in Tableau

      Go to Tableau, and pull the 2 tables in the data source view.

      Then perform Update Now button to refresh the data.

      As you can see, both the Pk Id columns is recognized by Tableau as Number (Whole).