How to handle situations when there is a combined primary key in the source system?

Now I have an Invoice dimension table. In its original source system, it uses the Project_ID and the Task_ID as a combined primary key. In this case, in my dimensional modeling, of course, I will have an Invoice_Key as the surrogate key. Should I have both Project_ID and Task_ID as the business keys? Thanks.

If they are the business keys, then so be it. But it does seem odd. While someone may invoice a task in a project, I've yet to see a system that used that as a key. What happens if the same task is invoiced a second time?

ngalemmo wrote:If they are the business keys, then so be it. But it does seem odd. While someone may invoice a task in a project, I've yet to see a system that used that as a key. What happens if the same task is invoiced a second time?

It is because an invoice could be generated for a task or a project. A project contains zero or more tasks. Each Task and Project has a unique ID.

Typically an Invoice line item can contain Project/Task, but the entire invoice?

If project + Task is unique for the invoice, then a vendor that supplied products/services for 5 tasks on a single project would be expected to process 5 invoices?

It does sound odd.

It is because an invoice could be generated for a task or a project. A project contains zero or more tasks. Each Task and Project has a unique ID..

No argument with that, but usually an invoice has its own identifier. It may refer to a project and task, but usually an invoice is an independent document.

Regardless, you need to track the identifying columns from your source data.