topic Re: Why are primary keys important? in Data & Schema Discussions

Why are primary keys important?

JC_Data_Wizard — Mon, 09 Oct 2023 20:37:13 GMT

We noticed that starting in 2023.7 we are getting error messages in the loader status, if the Enforce Primary Key Constraint option isn't selected with Primary keys setup in the table. Screenshot below.

Someone on our team asked why we even need primary keys on the table. Outside of the deduplication it provides, I didn't really have a good answer. There isn't much on the importance of using primary keys in the documentation or the community.

Outside of deduplication why do we need primary keys set up in the table?

Thank you,

John Cross

Re: Why are primary keys important?

RADSr — Tue, 10 Oct 2023 13:59:33 GMT

"Need" is a strong word, but besides the mundane ( good practice to ID field usage ) the really important thing the key designation does is determine if an incremental load will append a row or update an existing row.

Additionally - might be an edge case for some users - the key designation needs to be there to allow the lookup() function to actually look something up. It's ( IMHO ) a big pain-in-the-neck, but I've been bitten trying to figure out why a lookup isn't working only to discover I hadn't set the key(s) properly.

Re: Why are primary keys important?

JC_Data_Wizard — Wed, 11 Oct 2023 18:26:56 GMT

Thank for pointing that out Randy. Completely forgot about the incremental load and the lookup. I always set primary keys so I don't run into these issues often.

Are there any performance reasons? Why did Incorta in 2023.7 change to have the load show a failure when the primary key isn't set? Additional errors in the screenshot below.

Re: Why are primary keys important?

RADSr — Thu, 12 Oct 2023 14:05:10 GMT

I've asked if keys impact performance and the answer I got was "no" because of DDM.

That said we just went through a whole exercise changing security filters to use ID ( int ) fields instead of name ( string ) fields because we saw a fairly huge leap in performance due to the data types. So there's that.

As for the errors I run between bemused and enraged at Incorta error messages so I can't be of any help there.

@JoeM - can you share any further insight about the impact or lack of impact on performance when defining keys? Or tag someone - I don't expect you to do *everything* 😉

Re: Why are primary keys important?

JoeM — Thu, 12 Oct 2023 16:33:02 GMT

Thanks for chiming in @RADSr . Tagging @mhelmy who could give more insight on this.

Re: Why are primary keys important?

mhelmy — Thu, 12 Oct 2023 17:13:56 GMT

As mentioned by @RADSr, primary keys are crucial for ensuring the uniqueness of each row identifier in a table. They are particularly important for Incorta during the incremental data loading and deduplication process.

Previously, Incorta used to perform deduplication during both full and incremental loading jobs for tables. However, to optimize the load process, we stopped deduplicating data during full loads by default because most data sources already apply primary key constraints. Nevertheless, we require our clients to enforce the primary key constraint for data sources that don't enforce it.

Specific data sources, especially those storing data in a staging area with batches that might contain duplicates, necessitate enabling this flag to perform the deduplication during the full load and the incremental load. We encountered cases where customers didn't enforce the deduplication process during full loads for such data sources. Hence, we introduced this check to ensure the accuracy of join calculations.
Also, the logic of some MVs might return duplicates, which is why it is common to see issues like this with the MVs with keys identified after the upgrade. Enabling the flag (Enforce Primary Key Constraint) and then applying the loading from staging would fix the issue.

Re: Why are primary keys important?

RADSr — Thu, 12 Oct 2023 18:41:02 GMT

Thanks @mhelmy - and just to confirm, there's no query performance hit/gain associated with the key designation?

Re: Why are primary keys important?

mhelmy — Thu, 19 Oct 2023 15:45:35 GMT

@RADSr

Having keys on tables introduces additional steps during the load process. However, they are necessary for the deduplication step. As for query performance, keys do not have an impact.