Changing A Table's Source Key Once It Has Been Established?
B
Brent Van Allen
started a topic
over 2 years ago
Hello,
So we have a source-key for a given table. The source key is made of of three segments like AAA.BBB.CCC. We want to change the CCC part of the key, but have already loaded about 300K records with the original source-key value.
If we do change this key composition, I can calculate the new third keypart outside or inside the Semarchy database, but applying it is a challenge. I do not know every place I would have to change it at once to not break the system and also to ensure the newly applied keys in the DB line up with the Publisher side change to the algorithm to use the new key pattern so records will stilll match up on load into the Source tables.
Has anyone changed their source-key pattern "on the fly" like this once it is already established? If so how did you do it?
Anyone at Semarchy have a list of every single place I would have ot update the key to guarantee continuity after the change?
Thanks Much,
Brent Van Allen
Best Answer
J
Julien Peltier
said
over 2 years ago
Hello Brent,
To move forward with your use case, you will probably enfacing some DB issues when you want to update directly the key at DB level. In this situation, we advise you to:
- Recreate the existing 300K by copying your MI table to SD table and calculating the new sourceId
- After the certification process, these 300K records will have merged normally with the older300K records
To move forward with your use case, you will probably enfacing some DB issues when you want to update directly the key at DB level. In this situation, we advise you to:
- Recreate the existing 300K by copying your MI table to SD table and calculating the new sourceId
- After the certification process, these 300K records will have merged normally with the older300K records
thanks for the details provided for this interesting use case! I have to loop internally at Semarchy to check if someone faced this question earlier and to make sure of the right tables and columns to update within your data location. Let us get back to you as soon as possible.
Best regards,
Stéphanie.
1 person likes this
B
Brent Van Allen
said
about 2 years ago
That is a great idea Julien. We do have a number of matching schemes for these records (11 total with various scoring). This might work well for the solution. What do you think of guaranteeing a perfect row-to-row match by generating a UUID for every row (in place in the GD table) such that this UUID is only set up, generated, copied to the SD table, the dropped after ... not sure if I can even drop columns once created.
Brent VA
J
Julien Peltier
said
about 2 years ago
Hi Brent,
You can add a specific attribute to force matching on the Golden record ID by adding a dedicated matching rule. Still, it usually would not be necessary if you keep exactly the same record and change only the source id. Your proposal seems more secure but requires a lot of technical tasks after (update model and matching rule to remove the specific column + removing the column manually from SD/SA/MI/MD/GD/GA/GF/GH/GI/GP/GX/MX/SF tables. xDM cannot automatically drop the column because we have to support the deployment of a previous model version and keep associated data.
I will advise here to test the procedure without a dedicated column to avoid too many manual operations on DB.
Brent Van Allen
Hello,
So we have a source-key for a given table. The source key is made of of three segments like AAA.BBB.CCC. We want to change the CCC part of the key, but have already loaded about 300K records with the original source-key value.
If we do change this key composition, I can calculate the new third keypart outside or inside the Semarchy database, but applying it is a challenge. I do not know every place I would have to change it at once to not break the system and also to ensure the newly applied keys in the DB line up with the Publisher side change to the algorithm to use the new key pattern so records will stilll match up on load into the Source tables.
Has anyone changed their source-key pattern "on the fly" like this once it is already established? If so how did you do it?
Anyone at Semarchy have a list of every single place I would have ot update the key to guarantee continuity after the change?
Thanks Much,
Brent Van Allen
Hello Brent,
To move forward with your use case, you will probably enfacing some DB issues when you want to update directly the key at DB level. In this situation, we advise you to:
- Recreate the existing 300K by copying your MI table to SD table and calculating the new sourceId
- After the certification process, these 300K records will have merged normally with the older300K records
- Then you can publish deletions for the older 300K records (https://www.semarchy.com/doc/semarchy-xdm/xdm/5.3/Integrate/publish-deletions-with-sql.html) to recover Golden record with only 1 master record for this source
Best Regards
Julien
- Oldest First
- Popular
- Newest First
Sorted by PopularJulien Peltier
Hello Brent,
To move forward with your use case, you will probably enfacing some DB issues when you want to update directly the key at DB level. In this situation, we advise you to:
- Recreate the existing 300K by copying your MI table to SD table and calculating the new sourceId
- After the certification process, these 300K records will have merged normally with the older300K records
- Then you can publish deletions for the older 300K records (https://www.semarchy.com/doc/semarchy-xdm/xdm/5.3/Integrate/publish-deletions-with-sql.html) to recover Golden record with only 1 master record for this source
Best Regards
Julien
2 people like this
Stéphanie FOURRIER
Hello Brent,
thanks for the details provided for this interesting use case! I have to loop internally at Semarchy to check if someone faced this question earlier and to make sure of the right tables and columns to update within your data location. Let us get back to you as soon as possible.
Best regards,
Stéphanie.
1 person likes this
Brent Van Allen
That is a great idea Julien. We do have a number of matching schemes for these records (11 total with various scoring). This might work well for the solution. What do you think of guaranteeing a perfect row-to-row match by generating a UUID for every row (in place in the GD table) such that this UUID is only set up, generated, copied to the SD table, the dropped after ... not sure if I can even drop columns once created.
Brent VA
Julien Peltier
Hi Brent,
You can add a specific attribute to force matching on the Golden record ID by adding a dedicated matching rule. Still, it usually would not be necessary if you keep exactly the same record and change only the source id. Your proposal seems more secure but requires a lot of technical tasks after (update model and matching rule to remove the specific column + removing the column manually from SD/SA/MI/MD/GD/GA/GF/GH/GI/GP/GX/MX/SF tables. xDM cannot automatically drop the column because we have to support the deployment of a previous model version and keep associated data.
I will advise here to test the procedure without a dedicated column to avoid too many manual operations on DB.
Regards
Julien
-
Import Data Into Entities via Azure Data Lake
-
Recover Deleted(soft Delete) Record and Configure in Application
-
Data Quality in batch mode and real-time integration
-
Integration with analytics tools
-
Query/Load/Delete data with the REST API
-
Does the Done Tab in Inbox have a limit?
-
How Can I Trigger Enricher or Sql Procedure when deleting?
-
Matching Rules But Only The Latest Record Creates a Golden Record
-
Unstructured and Semi Structured Data in Semarchy?
-
Read CSV files from AWS S3
See all 73 topics