Natural keys with data type mismatch

  • aaa121

    SSCertifiable

    Points: 5902

    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: 4158

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply