{"id":917,"date":"2025-12-22T22:33:28","date_gmt":"2025-12-23T03:33:28","guid":{"rendered":"https:\/\/blog.data-principles.com\/?p=917"},"modified":"2026-01-06T18:20:10","modified_gmt":"2026-01-06T23:20:10","slug":"know-your-keys","status":"publish","type":"post","link":"https:\/\/blog.data-principles.com\/index.php\/2025\/12\/22\/know-your-keys\/","title":{"rendered":"Know your keys!"},"content":{"rendered":"\n

By Pete Stiglich<\/em><\/p>\n\n\n\n

One question I ask of data architects and data engineers that I\u2019m interviewing is \u201cWhat is the most important thing to know about your data?\u201d It\u2019s a bit of a trick question, it is valid. I frame the question a bit to give some context. For example, I might say \u201cSuppose you have a spreadsheet called \u201cCustomerSales.xlsx\u201d with one tab and 300 columns (with column headings) and you\u2019ve been tasked to understand how to integrate that with other data in your data warehouse. What is the most important thing you need to know about that data?\u201d<\/p>\n\n\n\n

The answer I\u2019m looking for is \u201cThe most important thing I need to know about that is what is\/are the key(s) of that data\u201d.  <\/p>\n\n\n\n

If you don\u2019t know the keys (primary AND <\/em><\/strong>natural\/alternate \/ business), then you really don\u2019t know much about the data \u2013 you\u2019re left with making potentially catastrophic assumptions about the meaning and grain\/level of detail.\u00a0For example, if you assume the grain of CustomerSales.xlsx you would probably assume the grain is sales by customer \u2013 but that\u2019s just an assumption.\u00a0Not knowing what the key(s) are means that you can make some poor design decisions (which will need to get fixed eventually \u2013 of course, it\u2019s much more expensive to fix once you\u2019re application and data are in production\u2026.)\u00a0and let poor data into your system (e.g., duplicates).\u00a0If you don\u2019t know what the key(s) are \u2013 how can you know whether you have a unique record or not?\u00a0\u00a0<\/p>\n\n\n\n

Even if you know, for example, that the key of CustomerSales.xlsx is SaleId (which for the sake of argument we\u2019ll assume is a surrogate key) you still don\u2019t know what the business key is. A surrogate key is a unique sequential number with no inherent business meaning in itself. All it tells you is that you have a unique number representing a record \u2013 but that same record could still be duplicated (just with a different SaleId). You still need to know the natural \/ business key in order to understand the meaning and grain of the data. For example, is the natural key CustomerId, SaleDate, LocationId, SaleNumber or is it CustomerId, SaleDate, LocationId, SaleNumber, and ProductId \u2013 i.e., is it a header or a line item, or are there other perhaps non-obvious columns that should be part of the natural \/ business key e.g., VersionStartDt (in which case it represents a version of a sale record).  You should, whenever possible, enforce unique indexes on the natural \/business key. Of course, for some large cloud databases or NoSQL platforms unique indexes on an alternate key might not be possible or the data volumes might require unique indexes be dropped. If that is the case, you should develop programs to at least occasionally test your data to ensure you don\u2019t have dups.  <\/p>\n\n\n\n

Nearly every data set should have at least one unique key \u2013 but I\u2019ve seen cases where having what looks like a duplicate record is actually acceptable e.g., the same customer executes the same transaction multiple times for the exact same dollar amount. This is poor design but might be outside of your control \u2013 there should at least be a unique surrogate key to differentiate the rows.<\/p>\n\n\n\n

<\/div>\n\n\n\n
\"\"<\/figure>
\n

Pete Stiglich: Trusted Expert in Data Architecture & Modeling<\/strong><\/p>\n\n\n\n

Pete has over 30 years of data architecture, data management, and analytics experience, most of that time as a consultant in industries such as government, finance, healthcare, insurance, and more. He is an industry thought leader in data architecture and data modeling and has developed and taught many courses on these topics. Pete enjoys helping clients solve complex data problems leveraging proven approaches such as \u201cModeling the business before modeling the solution\u201d which provides a benefit to clients that many IT professionals miss.
<\/p>\n<\/div><\/div>\n\n\n\n