Reltio Connect

 View Only
  • 1.  Reused Surrogate keys in Sources

    Posted 06-01-2023 16:23
    One of our source systems is reusing surrogate keys for customers.
    So, using them as a main element of our crosswalk keys will cause undesirable results, because the new record will overwrite the old record since it will have the same crosswalk.   I was thinking of adding another attribute to the crosswalk (an attribute that is likely to be different in the two records with the same surrogate key). Does anyone have any other suggestions as to approaches to work around this?  Can you point me to any educational resources?

    Max Locher
    MDM Architect

  • 2.  RE: Reused Surrogate keys in Sources

    Reltio Partner
    Posted 06-02-2023 01:55

    Hi Max,

    Can you try populating Source table along with surrogate such that if the source table matches only then the record is updated/overwritten.

    You can check this link for reference:

    Sheshadri V
    Fresh Gravity

  • 3.  RE: Reused Surrogate keys in Sources

    Posted 06-02-2023 09:38


    Thank you for your prompt and well-reasoned answer.  I neglected to mention that, unfortunately, in this case the source table is the same for both records.  Keys are recycled after records are purged in the source system. 



    Max Locher
    MDM Architect

  • 4.  RE: Reused Surrogate keys in Sources

    Reltio Employee
    Posted 06-02-2023 10:58

    It would seem that the  source has some process for deleting the current records.  is it possible that they add a notification to that process?  In Reltio, you could add a 'counter' field and make that part of the key.  When the notification of the purge comes, increment the counter.  Not very elegant, but it should be effective.

    Gino Fortunato
    Senior Solution Engineer

  • 5.  RE: Reused Surrogate keys in Sources
    Best Answer

    Reltio Employee
    Posted 06-02-2023 14:24

    If you have not used Reltio Surrogate Crosswalks, that might be a good place to start.  Gloria on our support team wrote a great article a little while back that you can reference here.

    General documentation on Surrogate Crosswalks can be found here.

    The catch with using surrogate crosswalks is that they are effectively a hash key based on some of the input attributes that is used rather than the foreign key.  This works fine if the fields used in the surrogate mapping are static in that system, but they often involve Name - hence if your record is originally created as ("Max", "Locher") it will hash those values and generated a crosswalks key.  But if that system later gets updated and your first name is changed to "Maximillian" and then sent to Reltio, it will generate a different hash and hence a new record in Reltio.  Automated stewarding will likely consolidate that "new" record with the previous, but you will still have an additional crosswalk.  

    If your keys are "mostly" static and you have a piece of information that is unlikely to change - like Date of Birth, then you can define your Reltio crosswalk based on the Foreign key + DoB.  This will allow name, address and other updates to flow in reasonably, but "recycled" keys should create new records - as long as the recycled key doesn't happen to have the same DoB as the previous record.  If this is a concern then you can add a few more fields to the Surrogate mapping to try to ensure a unique hash.

    Just realize the more you add, the more chance you have for a data change to create a new crosswalk - which is potentially more desirable than updating the incorrect record.

    For others reading this; it's a big deal because, let's say Source XYZ creates profile Dan Gage with assigned key 1234.  His record comes to Reltio and is merged with another record for Dan Gage from source PDQ.  If Source XYZ replaces Dan Gage's record with a new Max Locher and assigns him key 1234. That data would flow into Reltio as crosswalk (XYZ, 1234) and be treated as an update to the previous Dan Gage record.  But since that record was already merged with the PDQ record, I now have a consolidated profile with Dan & Max's data commingled.   This industry sometimes calls this an "overlay".

    This is the challenge with recycled keys, and part of why Reltio offers surrogate key support.  Other methods can be applied depending on the ability to detect when a key is being recycled.  

    Dan Gage
    Principal Solution Consultant