Natural keys with data type mismatch

• aaa121

  SSCertifiable

  Points: 5902

  December 10, 2018 at 6:06 am

  #409373

  Hi All,

  I have incoming data from 2 source systems which have datatypes of int or uniqueidentifier which relate to the same dimension.

  My aim is to keep the solution simple. My current thinking is to have the natural key field on each dim as a varchar and prefix the incoming data which a letter denoting the source system.

  This would enable me to join the staged data to the dimension on this column but performance would suffer as I'd be joining on a character field.

  How would you guys approach this?

• Brian Carlson

  SSCarpal Tunnel

  Points: 4154

  January 23, 2019 at 11:28 am

  #2019853

  I would use the letter denoting the source system. I would convert the int to a uniqueidentifier though with a hashing algorithm. The natural key is then [Source System], [UniqueId]. I've tested joins on uniqueidentifier