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...