Detect duplicate primary keys

As the uniqueness of the primary key is really important to guarantee optimal DinMo operation, we will help you check the level of duplication:

  • When creating the model

  • Once a day, after model creation

  • On demand by consulting your model

To calculate the percentage of unique primary keys, DinMo uses the following formula:

percentage = (count(distinct primary key) / count(primary key)) * 100

This formula divides the number of distinct primary keys by the total number of primary keys, then multiplies by 100 to get the percentage.

At model creation

When you create a model and click "Save," a pop-up will appear, allowing you to verify the percentage of duplicate primary keys within the model.

There are three possible scenarios: the primary key is unique, the duplicate rate is low or the duplicate rate is too high.

No duplicate primary keys

DinMo checks that the primary key you've selected is unique, i.e. that the percentage of unique primary keys is 100%.

In that case, you are directly invited to save the model.

Primary key is unique - You can save the model

The duplicate rate is low

This is the case if the rate of non-unique primary keys is less than 5%. In this case, DinMo alerts you that your model may have a problem.

Primary key is not unique but duplicate rate is still low

⚠️ You can save your model this way, but we strongly recommend that you correct your source data to restore a duplicate rate equal to 0%.

If you leave your duplicate rate above 0%, the situation is likely to worsen over time, affecting DinMo's operations. Don't worry, you'll be alerted anyway if this becomes the case, thanks to our daily checks.

If you decide to leave your duplicate rate above 0%, we'll also alert you in your model overview.

Alert on the model - Duplate primary keys detected

The duplicate rate is too high

If your duplicate rate is higher than 5%, you won't be able to register your model in DinMo.

Duplicate rate is too high - You can't save the model

You then have several options for correcting your problem:

  • Go back to the configuration and choose an other primary key (e.g. customer_id instead of last_name)

  • Modify your table structure / clean your data directly from your source, to ensure unique primary

You can then re-click on “Check duplicate primary key”. If the duplicate rate falls below 5%, the warning will disappear and you will be able to save your model.

Daily checks

To ensure that the duplicate rate remains low over time, DinMo performs automatic checks every day.

If the primary key remains unique (0% duplication), there will be no impact on your model — and therefore on the associated objects.

On the contrary, if DinMo detects that the primary key is not unique, you will be alerted and a warning message will appear on your model:

No duplicate then a lot: warning message

In that case, you'll need to update your table directly in your data warehouse. Cleaning your data will ensure that the duplicate rate remains as close to 0% as possible.

When the changes have been made, you can click on “Check duplicate primary key” again. If everything has been done correctly, your alert message will disappear.

Manual check

If you've made changes to your data warehouse and want to make sure that this doesn't impact the duplicate rate in your model, you can check this directly in DinMo.

To do so, directly go to your model and click on "..." at the top right of your page:

Manually check duplicates by clicking on "Check duplicates"

A new pop up will open and update the percent of duplicate primary keys. DinMo will then display the warning and the associated error if needed.

Last updated