1

What's the best practice when choosing a fact table's Primary Key (PK for brevity): Should we have an auto-incremented Surrogate Key (SK), or should we use Natural Key's (NKs) columns?

The advantage I see in using a SK is INSERT performance, which is very relevant when we have almost a million rows inserted daily, which is my case. I'm expecting almost 20M rows per day, 200M rows per month, and the table must sustain data for decades to come without needing to be split.

But query performance should be better if we have a NK as PK, and the order of the PK is well designed.

Of course we can create non-clustered indexes for queries, but that eats storage and makes INSERT even slower.

Considering that INSERT and DELETE (yes, we have that too, a lot, because data is reprocessed and new files versions must be "updated") performance is very relevant, almost as much as query performance, what's best?

Other issue to consider is that when rows are deleted a SK count doesn't decrease. With 200M rows per month, I'll have 2,4G rows per year, which int datatype won't hold, so I'll need to use bigint for PK...

Improve this question
2
  • 2
    Have you considered columnstore? You'll get compression that should mitigate natural key storage requirements.
    – Dan Guzman
    Aug 31, 2017 at 16:16
  • @DanGuzman Developers should note that this space savings from Columnstore compression only exists for disk-based tables. I'm using a lot of memory-optimized tables, and the clustered columnstore is an ANCILLARY structure in that subsystem. The data rows are still stored in the in-memory row format, with no pages, and you actually consume about 10-20% extra space when you add a columnstore to a RAM-resident table.
    – Pittsburgh DBA
    Feb 27, 2018 at 15:25

2 Answers 2

Reset to default
3

What's the context? It sounds like you're talking about a data warehouse. If so, I strongly recommend creating a synthetic key. I can think of three good reasons, offhand:

  • It will be narrower than the business key. While adding a column to a fact table should not be done lightly, because it will make your other indices narrower it may actually reduce the total size of your database.
  • Natural keys get dirty. Sooner or later someone will reuse a product code for something unrelated, or you'll need to start tracking sub-customers. If you have a mapping process between your natural keys and your synthetic keys, it's simple to account for these kinds of things.
  • You can use it to store synthetic values: -1 for "Not applicable", 0 for "Unknown", 1 for "Missing", 2 for "Illegible", etc. Depending on your natural keys, you might be able to do the same there, but there would always be a risk of collision.

48+ billion records is a lot. If you haven't already, you should consider aggregating data, perhaps after the current month. Synthetic keys could make this simpler too: perhaps values 1,000,000,001..1,999,999,999 are reserved for 2017/08 details, and in October the August data gets collapsed into a single record with ID 1,000,000,000 (this probably wouldn't be a good idea, it's just an example of how you may need to disconnect business keys from warehouse keys).

One last note: don't forget that your choices for PK and for clustering key by no means need to be the same! Each should be evaluated on its own merits.

Improve this answer
5
  • Yes it's a DW. I googled synthetic key and it seems to be a concatenation of columns that form NK into a unique column, is it? In that case I'd rather use incremental SK...
    – Hikari
    Aug 31, 2017 at 17:41
  • Indeed there's the risk of the same value be reused for different elements. For precaution I'm considering year-month as part of NK. If I didn't misunderstand synthetic key, I'll keep my choice between Surrogate or Natural keys.
    – Hikari
    Aug 31, 2017 at 17:43
  • I think the question and this answer conflate two different issues. One is whether to use Business Keys or Surrogate Keys for dimension tables. The other is whether to make the fact table key a compound key consisting of the dimension table keys that define its grain (natural key), or whether to have a single-column surrogate key for the fact.
    – David Browne - Microsoft
    Aug 31, 2017 at 18:31
  • I don't see where the OP has suggested using the set of dimensions as a key. If so, I recommend against it; such a key would be VERY broad. You could put a UQ on such, for data validation, but it would nearly double the size of your table.
    – Jon of All Trades
    Aug 31, 2017 at 18:49
  • @Hikari: typically, I'll have a table which maps a set of values from the business table to synthetic keys, which are an INT IDENTITY or similar construct. So your fact table can use a nice narrow INT (or BIGINT) as a PK; it makes a good base for your other indices, if you cluster off of this.
    – Jon of All Trades
    Aug 31, 2017 at 18:59
1

In a fact table for a star schema, a synthetic key (surrogate, auto-incremented) will serve no useful purpose. The combination of all the foreign keys in the fact table is a candidate key, perforce. Otherwise, the star is not well formed. You can declare it as a primary key, if you like. I choose to do this.

however, if you have a nice tight ETL program for periodically updating the star, then you can in fact get away without declaring a primary key at all.

I echo what you said concerning performance.

Improve this answer
3
  • 1
    Thanks. What do you mean by "the start is not well formed"? And what about "if you have a nice tight ETL program for periodically updating the star"? ETL runs when flat files arrive. Data may be reprocessed, in that case I'll delete data from previous version and insert updated data.
    – Hikari
    Aug 31, 2017 at 17:45
  • 1
    A fairly typical fact table, in my experience, might have ten INT dimensions, a couple of ~10-char VARCHAR degenerate dimensions, and a few housekeeping fields. Call it 80 bytes. An index on those ten INTs would be 40 bytes, or 894 gigabytes based on OPs 24 B records, before accounting for overhead. Such an index would only satisfy queries that filter on the dimension(s) at the start of the index. That sounds like a lot of pain for minimal gain, to me.
    – Jon of All Trades
    Aug 31, 2017 at 18:55
  • Should have been "the star". Will fix. I presume you know what a star schema is.
    – Walter Mitty
    Aug 31, 2017 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.