How do I uniquely identify one delivery in ball-by-ball cricket data?

Use match_id, innings, over_no and delivery_in_over together. The two columns that look like they would do the job both fail, and both fail quietly.

• The original ball column packs two numbers into one decimal. The tenth delivery of an over reads 1.10, which as a number is the same value as 1.1, the first delivery. Sort by it inside a long over and the tenth ball comes out in the wrong place. It is fine for everything except ordering within an over.
• actual_delivery counts only legal deliveries. So a wide shares its number with the ball that eventually counts, and it is a sequence rather than a key.

The general trap, worth carrying to any dataset: a decimal that encodes two integers is not safely orderable, and a counter that skips is not a key. Both look numeric and behave correctly on small samples, which is why they survive a spot check and break on a long over or a messy innings.

over_no and delivery_in_over are plain whole numbers added on top of the source data for exactly this reason. One master ball_by_ball table of every delivery (with gender and team_type to filter on), ready-made slices of it by format and gender plus the IPL, and match_info with venue, dates, teams, toss, result and officials, all joined on match_id.

The whole database is published, not just an API over it. Three ways to take it, all free, no signup and no key:

• Download it by hand. Every table as Parquet or CSV, or the entire database as one DuckDB or SQLite file: db-mcp.tigzig.com/downloads.
• Download it by API. The same files over one plain GET, so a script or a scheduled job keeps its own copy current.
• Query it where it sits. Read-only SQL over Postgres or DuckDB, plus an MCP server so an agent writes the queries itself.

Rebuilt twice a day, so whichever route you take the data is current rather than a one-off snapshot.

Live SQL and the MCP server: the free cricket SQL endpoint. Column list and field definitions: db-mcp.tigzig.com/redoc.

Credit, because it is a condition of use: the data comes from Cricsheet (cricsheet.org) under the Open Data Commons Attribution License 1.0, ODC-BY (opendatacommons.org/licenses/by/1-0/). If you publish anything built on it, credit Cricsheet and make the licence clear. Full write-up: https://www.tigzig.com/post/cricket-full-data-download-live-aug2026.