Building a Football Analytics Pipeline: Patterns, Tradeoffs, and What Production Would Look Like

Football is the most watched sport on the planet. Millions of fans follow their teams across leagues, tracking form, streaks, and head-to-head records. I built Ballistics — a pipeline that automates the ingestion, transformation, and analytics of football data, currently covering 19 leagues across 15 European countries. The goal is to serve fans with analytical data about their favourite teams: streak tracking, head-to-head breakdowns, and performance metrics.

This isn't a production system. It's a portfolio project. But every pattern in it maps to a real-world equivalent, and every shortcut I took, I can explain what the production version would look like. That's what this post is about.

Repo: github.com/ayoabass777/ballistics

The Architecture

                         ┌──────────────┐
                         │ Football API │
                         │  (RapidAPI)  │
                         └──────┬───────┘
                                │
                      ┌─────────▼──────────┐
                      │  Apache Airflow    │
                      │  (Dockerized)      │
                      └──┬─────────────┬───┘
                         │             │
              ┌──────────▼──┐   ┌──────▼──────────┐
              │  S3 Landing │   │   Postgres       │
              │  Zone (raw) │──►│   raw schema     │
              └──────┬──────┘   └──────┬───────────┘
                     │                 │
              ┌──────▼──────┐   ┌──────▼───────┐
              │  S3 DLQ     │   │     dbt      │
              │  (failures) │   │  (Docker)    │
              └──────┬──────┘   └──────┬───────┘
                     │                 │
              ┌──────▼──────┐    ┌─────┼─────────────┐
              │ Replay DAG  │    │     │             │
              │ (re-extract)│    ┌────▼───┐ ┌─────▼────┐ ┌────▼────┐
              └─────────────┘    │  stg   │ │   int    │ │  mart   │
                                 │ views  │ │  tables  │ │ tables  │
                                 └────────┘ └──────────┘ └─────────┘

The pipeline has three DAGs, each with a distinct job:

1. Bootstrap — one-time setup triggered by config changes
2. Incremental — daily fixture updates, standings refresh, dbt transforms
3. Replay — retries failed extractions from a dead letter queue

Let me walk through each one and explain why I made the choices I did.

Bootstrap: Adding a League Should Be a Config Change

The bootstrap DAG runs on a daily schedule, but most days it does nothing. A custom MetadataChangeSensor compares the modification time of metadata.yaml against the last-processed timestamp stored as an Airflow Variable. If the file hasn't changed, all downstream tasks soft-fail and skip.

class MetadataChangeSensor(BaseSensorOperator):
    def poke(self, context) -> bool:
        current_mtime = os.path.getmtime(self.filepath)
        last_mtime = float(Variable.get("metadata_last_mtime", default_var=0))
        if current_mtime > last_mtime:
            Variable.set("metadata_last_mtime", str(current_mtime))
            return True
        return False

When you do edit metadata.yaml — say, adding the Egyptian Premier League — the sensor triggers the full chain: create schemas, extract metadata from the API, full-load all historical fixtures into S3, then load into Postgres.

The design principle: adding a league is a config change, not a code change. You edit a YAML file, and the pipeline handles the rest.

Why a sensor and not a manual trigger?

I considered using TriggerDagRunOperator from a separate "admin" DAG, or just running the bootstrap manually. The sensor pattern won over because it's self-documenting — the DAG itself encodes when it should run. No one has to remember to trigger it, no runbook to maintain. Edit the file, walk away.

Parallel Branches

The bootstrap DAG has a fan-out/fan-in structure. Two schema creation tasks — metadata schema (dim tables) and raw fixtures schema — run in parallel before converging at the extract step:

sensor ──► metadata_schema ──► extract_metadata ──┐
                                                   ├──► extract_fixtures ──► load_fixtures
sensor ──► raw_fixtures_schema ───────────────────┘

This is a small optimisation, but it demonstrates an important principle: tasks that don't depend on each other shouldn't wait for each other. In a production pipeline with heavier DDL operations or network-bound tasks, this pattern pays off significantly.

XCom: Keep It Lightweight

The extract task returns a list of S3 keys via XCom. The load task consumes them. The critical decision here: pass URIs, not data. XCom stores values in the Airflow metadata database. Stuffing raw JSON payloads into XCom creates bloat and can crash the metadata DB in production. S3 keys are just strings — a few bytes each.

In production, I'd go further and use an S3-backed XCom backend. But for a demo, default XCom with lightweight strings works fine.

S3 as a Raw Landing Zone

Every extraction — whether full load or incremental — writes raw JSON to S3 before touching Postgres. The prefix structure makes each write immutable and replayable:

full_load/{league_id}/{season}/{ds}/fixtures.json
incremental/{ds}/fixtures.json
dlq/{league_id}/{season}/{ds}/error.json

Why not write directly to Postgres?

If the Postgres load fails (network issue, schema mismatch, disk full), the raw data is still sitting in S3. I can replay the load from the landing zone without re-hitting the API, which matters when the API has rate limits and costs per call.

This is a common pattern in production pipelines: extract once, load many times. S3 is cheap storage that gives you a replayable audit trail. Think of it as a recording studio approach — capture the raw signal first, then process it. You can always reprocess, but you can't un-lose a raw recording.

Why date-partitioned prefixes?

Each write goes to a unique prefix that includes the execution date ({ds}). This means incremental loads never overwrite each other. If I need to debug what was loaded on a specific day, the S3 prefix tells me exactly where to look.

In production, you'd add lifecycle policies to move older prefixes to S3 Infrequent Access or Glacier. Football fixture data is public and re-fetchable, so I didn't bother with versioning — but for non-recoverable data, I'd enable it.

Failure Handling: The Dead Letter Queue

Not every API call succeeds. Rate limits, network timeouts, malformed responses — any extraction can fail. The question is: what happens when it does?

The bootstrap DAG doesn't retry inline. Instead, failed extractions write an error record to the dlq/ prefix in S3 with enough context to retry later:

def write_to_dlq(api_league_id, season_year, ds, error, league_name=""):
    payload = {
        "api_league_id": api_league_id,
        "season_year": season_year,
        "league_name": league_name,
        "ds": ds,
        "error": str(error),
        "traceback": traceback.format_exc(),
        "timestamp": datetime.now(timezone.utc).isoformat(),
    }
    s3.put_object(Bucket=bucket, Key=f"dlq/{api_league_id}/{season_year}/{ds}/error.json", ...)

A separate Replay DAG runs daily, scans the DLQ, re-extracts from the API, and loads successfully replayed entries into Postgres. After replay, it checks if any entries remain and logs a warning. In production, this is where you'd wire up a Slack or PagerDuty alert.

Why a separate DAG instead of retries?

Airflow has built-in retries (retries=3, retry_delay=timedelta(minutes=5)). Why not just use those?

Because the failure mode matters. If one league-season fails during a bootstrap of 30 league-seasons, I don't want to re-run the entire bootstrap DAG. The DLQ pattern isolates failures — the 29 successful extractions are safe in S3 and loaded into Postgres. Only the failed one needs another attempt, and it happens independently without blocking anything else.

This is the same pattern AWS SQS dead letter queues use. The idea: separate the happy path from the recovery path. Each can run on its own schedule, with its own retry logic, without coupling them together.

The Incremental Path: Daily Updates and Edge Cases

The daily DAG picks up fixtures that have been played since the last run — specifically, fixtures where kickoff_utc is in the past but fulltime goals are still null. It fetches updates by fixture ID, writes to S3, upserts into Postgres, corrects any rescheduled kickoff times, refreshes league standings, and triggers dbt.

extract_updates ──► load_updates ──► fixture_corrections ──► update_standings ──► dbt_run

The Belgian Playoff Problem

A week after going live, I noticed warnings in the logs: Fixture 1540006 not found in DB; skipping. The fixture correction script fetches the last/next N fixtures per league from the API and checks them against the database. These fixture IDs existed in the API but not in my database.

Investigation revealed these were Belgium's Jupiler Pro League championship playoff fixtures. Belgian football splits into playoff groups mid-season, and the league body creates entirely new fixture IDs for the playoff round. My bootstrap captured the regular season, but the playoff fixtures were created after that.

This exposed a gap: the incremental path only updates existing fixtures. It doesn't insert new ones. Newly created fixtures (playoffs, rescheduled additions, cup ties added mid-season) get silently skipped.

For now, re-bootstrapping the affected league-season picks them up. The proper fix is adding an "insert new fixtures" path to the incremental flow. I've documented this as a known gap — acknowledging it matters more to me than pretending it doesn't exist.

Standings Deduplication

Another edge case from Belgium: the API returns multiple standings tables for the same league-season when playoff groups are active. The same team appears in both the regular season table and the playoff table, both tagged with the same league_season_id. Postgres rejected the batch upsert because two rows with the same (league_season_id, api_team_id) can't appear in a single INSERT.

The fix: deduplicate before upserting, keeping the last occurrence (the most current table). A small function, but it's the kind of thing that only surfaces with real data from real APIs — not from tutorials.

dbt: Three Layers of Transformation

The dbt project follows a standard staging → intermediate → mart structure:

Staging views are thin wrappers over raw tables. They rename columns, cast types, and derive flags like is_played from fixture_status = 'FT'. The staging layer uses incremental materialisation keyed on api_fixture_id, so daily runs only process changed fixtures.

Intermediate tables enrich fixtures with league context (league name, season label, current season flag) and team dimensions. This is where streak computation happens — win runs, clean sheet runs, scoring runs — along with relevance scores based on configurable weights stored as dbt seeds.

Mart tables assemble API-ready JSON payloads for the frontend: team pages, fixture pages, head-to-head breakdowns, homepage streak rankings. These rebuild fully on each run — at ~10k fixtures, that's fine. At higher volumes, you'd push incremental materialisation further down the DAG.

Data Quality at Three Levels

I set up tests at each layer, each catching a different class of problem:

Source tests validate the raw data at the door. Not-null and unique constraints on primary keys, accepted-value checks on result columns (win, draw, loss, null). Source freshness monitoring warns at 24 hours stale, errors at 48 hours. Freshness tests don't validate the data itself — they validate that the pipeline is running. A passing not-null test on a table that hasn't been updated in a week is a false sense of security.

Staging model tests validate the output of my transforms. Key uniqueness, not-null on derived columns like is_played, and referential integrity — stg_dim_league_seasons.league_id must reference a valid stg_dim_leagues.league_id. These are like foreign key constraints enforced at test time rather than in the database.

Singular tests encode business logic. I wrote one: assert_no_future_completed_fixtures.sql — no fixture should have is_played = TRUE with a kickoff_utc in the future. This catches data quality issues where the API returns a "finished" status for a match that hasn't happened yet, typically a sync bug or timezone mismatch.

The distinction between generic and singular tests matters. Generic tests (not_null, unique) are reusable patterns. Singular tests are custom SQL queries specific to your domain. Having both shows you understand data quality isn't just "add not_null to everything."

What Production Would Look Like

This project runs on Docker Compose on my laptop. Here's what I'd change for a real deployment:

The biggest shift: moving from access keys to IAM roles. In the demo, Airflow connects to S3 using access keys stored in .env. In production, the Airflow instance would run on EC2 or ECS with an IAM role attached — no keys in code, no keys to rotate, no keys to leak.

I'd also add proper monitoring: the DLQ check task currently logs a warning. In production, that becomes a Slack notification or PagerDuty alert. Source freshness errors would trigger similar alerts. The infrastructure for observability is already there in the pipeline design — it just needs to be wired to real alerting tools.

What I'd Do Differently

The playoff gap is the most concrete lesson. I assumed that bootstrapping a league-season once would capture all fixtures. It doesn't — leagues create new fixtures mid-season for playoffs, rescheduled matches, and cup ties. The incremental path needs an "insert new fixtures" capability, not just updates.

dbt schema management gave me trouble. I didn't initially set up a separate profile for the project, so both the old and new dbt projects shared the same profiles.yml block. Changing the database for one would break the other. The fix was simple — a separate ballistics profile — but it cost me a debugging session that could have been avoided with upfront isolation.

Docker-in-Docker is a footgun. Running dbt via DockerOperator inside a Dockerized Airflow requires mounting the host's Docker socket. This works for local development but wouldn't fly in production — you'd run dbt as a dedicated container service or use the BashOperator with dbt installed directly in the Airflow image.

Wrapping Up

Ballistics currently tracks 19 leagues across 15 countries, ingesting and transforming fixture data daily. The patterns — sensor-gated bootstrapping, S3 landing zones, dead letter queues, layered data quality testing — are all transferable to production pipelines at any scale.

The gap between a portfolio project and production isn't the patterns. It's the operational maturity: IAM roles instead of access keys, alerting instead of log messages, lifecycle policies instead of unbounded storage. Knowing that gap exists, and being able to articulate it, matters as much as the code itself.

Check out the repo: github.com/ayoabass777/ballistics