How to deal with fact table data that needs to be version controlled?

Question

I have the following simplified sport_match 'fact' table:

match_id	tournament_id	player_id_p1	player_id_p2	p1_final_score	p2_final_score
1	1	1	2	1	0
2	1	1	2	3	1
3	2	3	2	2	3
4	2	3	2	4	0

The table is updated from an API that issues INSERT, UPDATE and DELETE SQL instructions via text files.

Occasionally there is a mistake in the scores and because I need to be able to run historical analyses from a specific point in time I need to capture the incorrect entry and the correct entry. For this reason I started to look at adopting a Slowly Changing Dimension Type 2 method and translating all the API instructions to INSERT. This would give me a table that looked like this:

match_key	match_id	tournament_id	player_id_p1	player_id_p2	p1_final_score	p2_final_score	start_date	current_flag
1	1	1	1	2	1	0	01/01/2000 00:00	Y
2	2	1	1	2	3	1	02/01/2000 00:00	Y
3	3	2	3	2	2	3	03/01/2000 00:00	Y
4	4	2	3	2	4	0	04/01/2000 00:00	N
5	4	2	3	2	4	1	04/01/2000 00:01	Y

However, I realised I was applying a 'dimension' principle to a 'fact' table.

Is this a viable approach or should I be looking at a different design?

Answer 1

As is often the case; a little more googling gave me an answer. Just in case the link ever breaks:

So the way the solution works is that the fact table changes are simply treated like SCD type 2 changes that are commonly used with dimension processing. Add a Start and End Effective Date to each row to track the versions and allow point-in-time analysis. Also, in addition to what the Kimball model shows, I ended up using a currentFlag to make it easier for my client to filter out history (similar to how one would do on a dimension with history).

With historical fact changes handled, let's focus on dimension changes. In this case, the complication is that typically an SCD2 change in a dimension spawns a new surrogate key for the dimension row. And that key would need to be embedded in the fact table as a foreign key. But what if the fact row didn't have a change? You would need to create a new fact row with the new dimension surrogate key to keep the relationship intact. Obviously, that would cause some serious growth in your fact table. Row splits without much benefit.

Fortunately, that approach isn't necessary. The Kimball article lays out a dimension design where you keep the same surrogate key on each Type 2 dimension row, but manage the End Dates appropriately. Equivalent of assigning a DW surrogate key for the master entity (not each SCD2 row) to replace the business key. Then use that master key, in combination with the End Effective Date, to define a unique row (or PK on the table). The reality with this approach is that you have a many-to-many between the fact table and the dimension on the dimension master key. So when querying the dimension and fact table, you must force your query/anlaysis tools to choose a "version" or as-of date that falls between the dimension effective dates. This allows you to avoid bringing back all combinations and resulting in overstated measurements. Makes the ETL and the presentation layer a bit more challenging, but avoids the fact table headaches that would have resulted from the row splitting approach.