Football data systems / Architecture
Build the warehouse around the football question
A recruitment shortlist needs a record of what happened, to whom, and in which competition. Start there before choosing the cloud platform.
The shortlist has refreshed. A midfielder’s minutes have changed, his former club has disappeared, and nobody can explain whether the provider corrected a match or the pipeline lost a record. This is a hypothetical failure, but a useful design brief. A football data warehouse should let the analyst follow a result back to the records and rules that produced it.
From received records to an explainable shortlist
- Bronze
Preserve
Received exports and source revisions
Source, coverage, and permission recorded
- Silver
Reconcile
Mapped identities, standard units, dated registrations
Unmatched and duplicated records investigated
- Gold
Publish
Validated appearances and agreed metrics
Totals checked against the source
Player appearance
One player in one match
Supplied minutes + context keys
Context keys link the appearance to descriptive tables. Event records stay at their own level of detail; aggregate them before combining their totals with appearances.
The Azure football tutorials supplied for this series connect extraction, cloud storage, SQL processing, and a Tableau dashboard. They provide a learning route through the tools. Our proposed club architecture adds a different requirement: each stage must explain what it accepts, what it changes, and what would stop a questionable refresh reaching the reader.
Decide what a row means
For a first recruitment dataset, use one player appearance in one match as the unit of observation. Store the player, match, team, and competition identifiers with the supplied minutes. Keep shots and passes in an event table at their own level of detail. Joining every event directly to an appearance and then summing its minutes would repeat the same playing time for each event.
Microsoft’s dimensional modelling guidance separates measurements in fact tables from descriptive attributes in dimension tables. Applied here, appearances are the facts; players, teams, matches, and competitions provide the context. This football schema is our design proposal, not a vendor template. Match-team attribution belongs to the appearance, while registration history remains a separate dated record.
Keep the source and the judgement separate
A bronze layer retains the received export and its source metadata. Silver standardises identifiers, dates, and units, with unresolved matches held for investigation. Gold contains the validated appearance tables and documented metrics used by the shortlist. Microsoft describes these layers as successive stages of preparation; the football-specific acceptance rules must still be designed by the club.
Keep a coverage record beside the match data. Was the fixture supplied, did the identifier match, and has the provider revised it? A competition outside the subscription is not a player with no actions. Corrections should retain a trace of the prior release and the reason for the change. The analyst needs to distinguish an altered source from an altered calculation.
Choose a stack the team can operate
For a small, scheduled workflow, a reasonable starting design is Python ingestion, retained source files, SQL transformations, automated checks, and the reporting tool the analysts already use. Keep transformations and metric definitions in version control. Add an orchestrator when dependencies, retries, and monitoring justify it. A catalogue of cloud services does not establish that those responsibilities are covered.
A Microsoft-oriented organisation can evaluate Fabric Warehouse and Power BI against the same brief. Other managed SQL warehouses may fit an existing team better. Compare access control, historical corrections, refresh deadlines, support ownership, and operating cost under the actual workload. We are proposing a selection method, rather than claiming one product is cheapest or fastest.
The first release should answer a bounded question, such as which players merit closer video assessment for a specified role. Agree data-use rights before loading provider exports, and separate recruitment data from restricted personal or medical records. The test of the warehouse is whether a colleague can explain and challenge the shortlist when its author is away.
Specify the first release
- Write the user, decision, row definition, and refresh deadline before selecting tools.
- Preserve exports and identifier mappings; reconcile appearances and minutes before release.
- Assign an owner for failed refreshes, access requests, and provider corrections.