Sample Header Ad - 728x90

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

1 vote
1 answer
1823 views
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?
Asked by Jossy (83 rep)
Jun 6, 2022, 08:28 PM
Last activity: Jun 6, 2022, 09:04 PM