Does it belong in the stage tables or fact tables?

If I have a fact table with a "counter" fact, for example a customer places an order, but then goes back and changes parts of that order any number of times, I want to store how many times the customer changed his/her order. The grain of the fact table is the orderID. The changes are captured at the source in a change history table, but that table only stores the previous 5 days changes.

I see two ways to do this: create another table, a factless fact table, and capture the history of changes going forward. Use this table to count the # of changes and update the result in the Orders fact table.

Or, alternatively I could load a persisted staging table with the history of changes, and use that to update my fact table counter. This approach is cleaner in that it doesn't involve a separate fact table that only serves the purpose of updating another fact table row, as users will never query the factless fact table directly (or indirectly) because they are only interested in the # of changes, not each individual change.

What are your recommendations?

Why not store net changes in the order fact table itself?

Well, the # changes will be stored in the fact table itself, but we need to capture the history and store it somewhere in case the fact table needs to be reloaded or adjusted for whatever reason. The source only keeps 5 days of changes, so that wouldn't be accurate if we report changes on a monthly or yearly level.

As an example, if we store it in the Order fact only, say 30 changes for a particular order, and if the current day load had 4 changes, we could update the row to 30 + 4 or 34 changes. But if the business came back and told us, "there was a operational error in the source system that was double counting the changes from Jan 15th through Jan 30th, so adjust your figures to 1/2 for that date range." If we didn't store the history anywhere, we would never be able to know how many changes occurred during those dates; only the total changes to-date.

I have not seen a situation where the business wanted that level of detail. Given the situation you describe, a separate fact table logging every known change would be the way to go.

I agree with ngalemmo. In fact, now you have a fact table where the granularity is an Order. If you want to keep track of the changes done in each order, the granularity of this fact table becomes "each change in an existing order (or the creation of the order initially)". So that becomes another fact table.

Also, having this new fact table can be useful at some point to analyse the reason of the changes in the orders, such as part changes, promotion code, expected delivery date, etc.

Best regards,