Building a Horse Racing Data Pipeline: Backfill, Then Daily Updates

A horse racing data pipeline looks like one job and is really two. The first is a backfill: load a decade or more of history, once. The second is a daily job that adds yesterday’s races and stays out of trouble. They have different tools, different costs and different failure modes, and teams that treat them as one end up either hammering an API for years of data or running a heavy bulk load every night.

We would build them like this, using the UK and Hong Kong tables.

Job one: the backfill

Use files. A full UK history is about 197,000 races and 1.86 million runner rows, and Hong Kong adds another 30,000 races and 365,000 runner rows. Fetching that one request at a time would burn through any API quota for nothing. A bulk package gives it to you in a form you can load directly, as CSV, SQL or a MySQL or PostgreSQL dump.

Three habits make the load safer.

Load into a staging schema first, run your checks, and only then move it into your working tables. Our dataset checklist has the audit to run at that point.

Put a unique constraint on the pair of race and horse in the records table. A duplicate should fail loudly, not slip in.

Keep the original text for fields you parse, such as distances, finishing times and margins, next to the numeric version. You will want to change your parsing later.

The data model post lists the tables and the keys that join them.

Job two: the daily update

New data arrives once a day, after racing. There is no live feed, so a job that polls every few minutes does nothing but use up calls. Schedule one run in the early morning, after the night’s update has finished, and have it pull yesterday.

The API route for a UK day is short: get an access token from your API key, list the day’s races, and fetch the records for each. A token lasts 24 hours.

import datetime as dt
import requests

BASE = "https://api.horseracedatabase.com"

def get_token(api_key):
    r = requests.post(f"{BASE}/auth/token", json={"api_key": api_key}, timeout=30)
    r.raise_for_status()
    return r.json()["access_token"]

def fetch_day(market, day, headers):
    races = requests.get(f"{BASE}/{market}/races/date/{day}", headers=headers, timeout=30)
    races.raise_for_status()
    rows = []
    for race in races.json():
        rec = requests.get(f"{BASE}/{market}/records/race/{race['raceID']}",
                           headers=headers, timeout=30)
        rec.raise_for_status()
        rows.extend(rec.json())
    return rows

def run(api_key, market="uk", window_days=2):
    headers = {"Authorization": f"Bearer {get_token(api_key)}"}
    today = dt.date.today()
    for back in range(1, window_days + 1):
        day = today - dt.timedelta(days=back)
        yield from fetch_day(market, day.isoformat(), headers)

Both the races and records endpoints return a plain list, so there is nothing to unpack. Use market="hk" for Hong Kong.

Re-pull a short window

Pulling only yesterday sounds efficient, but results are sometimes corrected after the first publication: a finishing time or a margin filled in later. We rebuild our own published files over a rolling seven day window for exactly this reason. Make the window a parameter, as in the function above, and upsert on the race and horse pair. An upsert, an insert that updates on conflict, is what makes the re-pull safe. Running the job twice should leave your database exactly as running it once did.

What the daily job costs

Count the calls, because the API meters them. The UK has averaged about 36 races on a racing day, so one day is one call for the race list plus roughly 36 for the records, around 37 in all.

The Essential UK plan includes 6,500 calls a month. Re-pulling a full week every night would overshoot it, while two days nightly with a full week once a week stays well inside. Hong Kong is lighter: a meeting has 9 or 10 races, and there are about 87 meeting days a year. The usage endpoint shows where you stand at any time and does not use a call.

Guard rails

A few cheap protections.

Alert if a racing day returns no races. A day with nothing is possible, but you should know about it.

Compare the number of runners you stored with the number the race declares.

Do not trust the schedule. Hong Kong has no racing in August, and British racing varies, so write the job to handle an empty day without failing.

Start small: one day, one market, one table, run twice to prove the upsert works. Scale up after that. The API quickstart takes about five minutes, the datasets cover the backfill, and the API plans list the quotas for the UK and Hong Kong.