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. Three ball-by-ball tables, one per format, plus match_info with venue, dates, teams, toss, result and officials, all joined on match_id.

You can query it live or download it. Live SQL and the MCP server: the free cricket SQL endpoint. Bulk files are one table at a time in Parquet or CSV, or the whole database as a single DuckDB or SQLite file, refreshed twice a day. 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.