I'd like to scrape details of tennis matches from various websites and then combine them into a single database. Matches partially overlap across the websites and they contain different amounts of info.
As a simplified example of tables populated from scraping two websites:
website_A table:
| id_ | date | p1_name | p2_name | p1_serve_1_pct | p2_serve_1_pct |
|---|---|---|---|---|---|
| 1 | 01-Jan | Roger Federer | Novak Djokovic | 70 | 65 |
| 2 | 02-Jan | Rafael Nadal | Andy Murray | 58 | 57 |
website_B table:
| id_ | date | p1_name | p2_name | p1_serve_1_pct | p2_serve_1_pct | p1_serve_2_pct | p2_serve_2_pct |
|---|---|---|---|---|---|---|---|
| 1 | 01-Jan | Roger Federer | Novak Djokovic | 70 | 65 | 65 | 60 |
| 2 | 02-Jan | Rafael Nadal | Andy Murray | 68 | 67 | 59 | 58 |
| 3 | 03-Jan | Holgar Rune | Carlos Alcaraz | 70 | 67 | 57 | 58 |
In the above example the website_B table has an extra match and two additional fields.
I need to matches these matches together, dedupe them and then combine them into a structure that I can query. Most of the ETL articles I've read seem to seem to suggest merging the data and then creating a combined table:
| id_ | date | p1_name | p2_name | p1_serve_1_pct | p2_serve_1_pct | p1_serve_2_pct | p2_serve_2_pct |
|---|---|---|---|---|---|---|---|
| 1 | 01-Jan | Roger Federer | Novak Djokovic | 70 | 65 | 65 | 60 |
| 2 | 02-Jan | Rafael Nadal | Andy Murray | 68 | 67 | 59 | 58 |
| 3 | 03-Jan | Holgar Rune | Carlos Alcaraz | 70 | 67 | 57 | 58 |
Note: In the above table the data in p1_serve_1_pct and p2_serve_1_pct are taken from website_B during the merge process.
Querying the combined table is simple however it bugs me that I'm duplicating lots of the data. Additionally I won't know which fields were populated from which data source if I ever wanted to track back.
I think there's an alternative option with a master table that references the other two tables:
| id_ | website_A_id | website_B_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 |
I would then write some logic into my queries to try and extract p1_serve_1_pct and p2_serve_1_pct from website_B first and only if they were blank then to extract from website_A.
This second option means I'm not duplicating data but my queries will be more complicated.
To my questions then...
- Should I follow the first approach and just accept the data duplication? If yes, how would I create an audit trail back to the original data sources?
- Would my second approach work? Are there are considerations to be aware of before going down this route?
It's probably worth mentioning that in real life there are tables for players and tournaments too. I anticipate the row and column counts to be:
- Matches: 1.5m, 200
- Players: 100k, 30
- Tournaments: 30k: 30
Also probably worth mentioning that I've specifically steered clear of going into detail about how the merging process is done. I can add details if needed but it's basically a lot of fuzzy matching as there aren't common fields across data sources.