How a basketball side project taught me that perfectly valid data can become completely misleading on its way to a database.
It started with basketball
I'm an Olympiacos supporter.
I'm also a data engineer, which means that occasionally, instead of simply enjoying something, I decide it needs a database.
That's more or less how RED Observatory started.
I wanted to build a sports analytics platform where I could explore basketball games beyond the usual final score.
Shot charts, shooting patterns, player performances, historical comparisons. The sort of things I enjoy looking at, combined with the sort of engineering I enjoy doing.
The initial architecture was fairly straightforward:
- Python for data collection and processing.
- SQLite for storage.
- FastAPI for serving the data.
- React for the frontend.
- EuroLeague data as the starting point.
Nothing particularly exotic.
I expected the interesting challenges to involve statistics and visualisation.
Instead, one of the most useful engineering lessons came from something considerably less glamorous.
Three characters in a team identifier.
The first dataset looked promising
The first substantial historical collection covered Olympiacos's 2023–24 EuroLeague season.
Forty-one games.
The early integration used a mixture of a community-maintained Python client and direct requests to EuroLeague endpoints.
The client helped with schedule and shot information. Direct requests handled additional game statistics.
It was a reasonable starting point for a personal project.
And data was making its way into the database.
At that stage, it's tempting to focus on the next visible feature.
Can I render a shot chart?
Can I display a box score?
Can I compare games?
But there's a question that should come before all of those.
Does the database actually represent the games correctly?
When we later examined the original database, the answer was rather uncomfortable.
Forty-one games. Half the picture.
The original database contained all 41 games.
That sounds promising.
But a closer inspection revealed something unexpected.
It contained 489 player-match records, all assigned to the away side.
Not a single player-match record was assigned to the home side.
The database also lacked stored fixture dates and final scores.
In other words, we had a collection of basketball games without a complete representation of what happened in those games.
That's an impressive achievement, if your goal is to build an analytics platform for exactly half a basketball court.
Unfortunately, it wasn't mine.
There was another problem.
Two fixtures involving Bayern Munich had been associated with Barcelona.
And this one had a particularly simple explanation.
The three-character problem
In the original importer, team identifiers were generated from the first three characters of a club's name.
The relevant logic looked like this:
home_code = home_name[:3].upper()
away_code = away_name[:3].upper()
At first glance, this might seem like a convenient shortcut.
Many team names have distinctive beginnings.
But consider two EuroLeague clubs:
FC BARCELONA
FC BAYERN MUNICH
Apply our transformation:
"FC BARCELONA"[:3].upper()
# 'FC '
"FC BAYERN MUNICH"[:3].upper()
# 'FC '
There it is.
Two different teams.
One identifier.
Including the trailing space.
Beautiful.
The importer reused existing team records based on this generated identifier. Once two distinct clubs shared the same key, their identities could become mixed.
In the preserved database, two games that the source schedule identified as Bayern Munich fixtures instead referenced Barcelona.
No malformed JSON was required.
No mysterious server failure.
No dramatic API outage.
We introduced the ambiguity ourselves.
And because the resulting values were still perfectly valid strings, a database could happily accept them.
A valid record isn't necessarily a correct record
This is what makes data-quality problems particularly interesting.
Imagine a pipeline that:
- Successfully retrieves data from an upstream service.
- Parses the response without errors.
- Transforms the records.
- Inserts them into a database.
- Serves the result through an API.
- Displays it in a polished interface.
Every operation might succeed.
The frontend might render exactly what the backend returned.
And yet, somewhere in step three, Bayern Munich quietly became Barcelona.
From the application's perspective, everything might appear operational.
From the perspective of someone trying to analyse basketball games, the result is wrong.
A successful request tells us that communication worked.
A successful database transaction tells us that the database accepted the operation.
Neither tells us that the information still means what it meant at the source.
That's a different kind of validation.
And it's the kind that matters most in analytical systems.
Fixing the identity problem
The repair wasn't simply a matter of changing three characters to four.
The real solution was to stop inventing team identities from display names.
Where the source provides authoritative club codes, use them.
A club's display name is useful for a human interface.
It is not necessarily a stable identifier.
Names can change, contain prefixes, use different alphabets or appear in multiple formats.
Even without an obvious collision, a name-derived key introduces assumptions that the source data may not support.
For RED Observatory, the repaired import process used source club codes and the fixture's home/away designation to restore the correct associations.
The historical data was rebuilt into a separate database rather than blindly changing the accepted one.
But correcting team identities was only part of the work.
The missing player statistics required a more complete approach to box-score ingestion and validation.
Both teams have to exist
The original importer had another weakness.
Its parsing logic conditionally processed the home and away sections when the expected structures were present.
If a section wasn't available in the form it expected, that side could be skipped without treating the incomplete result as a fatal problem.
We don't have enough preserved evidence to establish exactly why every home-side player record went missing in the original capture.
And that's worth being explicit about.
We know what the original database contained.
We can identify the team-code collision in the importer.
But the complete historical debugging sequence wasn't preserved.
What mattered for the repair was ensuring that incomplete game data could no longer pass through the pipeline unnoticed.
The newer importer requires both teams' statistical blocks.
It also handles details that are easy to overlook until they become problematic:
- Player identifier formats.
- Playing-time values.
- Team-level and player-level statistics.
- Reconciliation against final scores.
- Shot-event identifiers and team associations.
- Missing spatial coordinates, without pretending they're zero.
These aren't especially glamorous features.
Nobody opens a basketball analytics website hoping to admire its identity-reconciliation logic.
But without them, the more interesting visualisations become considerably less trustworthy.
Rebuilding the season
The repaired database provided a useful before-and-after comparison.
| Metric | Original database | Repaired database |
|---|---|---|
| Captured games | 41 | 41 |
| Team records | 17 | 18 |
| Player-match records | 489 | 979 |
| Games with both roster sides | 0 | 41 |
| Fixture dates and scores | Missing | Restored |
| Known Bayern/Barcelona mismatches | 2 | 0 |
The repaired import also introduced automated checks for the relationships between fixtures, teams, player statistics and recorded results.
That doesn't prove that every source value is perfect.
Nor does matching shot-event counts establish that every coordinate is spatially accurate.
But it gives the system explicit conditions that must hold before the collected information can be trusted for the analyses we want to perform.
The objective wasn't to make the database bigger.
It was to make the database more faithful to the source.
The architecture grew up a little
The ingestion process also evolved beyond the original collection scripts.
RED Observatory now separates collecting source responses, normalising records, validating relationships and preparing data for publication.
A simplified view looks like this:
EuroLeague source endpoints
|
v
Raw data capture
|
v
Normalise and identify
|
v
Candidate SQLite DB
|
v
Validate and reconcile
|
v
Static JSON export
|
v
Verify before publication
|
v
RED Observatory
An important detail is that updates are prepared as candidates.
They don't automatically become the accepted public dataset just because a collection process completed.
The new data must pass validation and publication checks first.
If it doesn't, the previously accepted baseline is retained.
For a small hobby project, that may sound rather elaborate.
But it solves a real problem.
I don't want a temporary upstream issue or an importer regression to quietly replace trustworthy data with something incomplete.
And I certainly don't want to discover another basketball club masquerading as Barcelona.
One was enough.
What about the Python library?
There's an important clarification here.
The original integration used the community-maintained euroleague_api package alongside direct HTTP requests.
But the investigation didn't establish that the library was responsible for the problems in our historical database.
In fact, inspection of the preserved package showed functionality for retrieving full player statistics for both teams.
So this isn't a story about a broken open-source dependency.
The verified identifier collision was in our own code.
And the original integration's incomplete validation was our responsibility too.
That distinction matters.
It's easy to blame an abstraction when a data integration behaves unexpectedly.
Sometimes the abstraction really is responsible.
Sometimes the upstream source changes.
And sometimes you write name[:3] and accidentally merge two basketball clubs.
Engineering has a way of keeping us humble.
Why side projects are excellent teachers
I work professionally in data engineering.
I spend a lot of time thinking about how data moves between systems, how transformations behave and what happens when assumptions turn out to be wrong.
But RED Observatory is different.
Nobody commissioned it.
There isn't a customer waiting for a delivery.
I started it because I enjoy basketball, enjoy engineering and wanted to see what I could build.
That freedom is part of what makes it valuable.
I can explore problems that interest me, change direction when something unexpected appears and spend time understanding the parts that aren't immediately visible to users.
I originally wanted to make basketball shot charts.
Instead, I ended up revisiting data identity, ingestion design, reconciliation and safe publication.
All useful engineering problems.
Just with considerably more Olympiacos involved.
The lesson I'll keep
If there's one thing this experience reinforced, it's that data correctness is not something you can infer from a successful pipeline execution.
You need to define what correctness means for the domain.
For basketball, that might involve checking that both teams exist, game identifiers match, statistics reconcile and missing values remain distinguishable from real zeros.
For geospatial data, it might involve coordinate systems, geometry validity, source resolution and geographic coverage.
Different domains.
The same underlying responsibility.
A pipeline should do more than move data successfully.
It should preserve the meaning of that data.
Because otherwise, you can build a perfectly functioning application around a beautifully organised misunderstanding.
And if you'll excuse me, I have some basketball statistics to investigate.
This time, preferably involving the correct teams.
RED Observatory is an independent, fan-built sports analytics project. It began with basketball and continues to evolve as an engineering experiment.
More projects and writing: ptzivras.pages.dev.
Top comments (0)