Choose a Stable Primary Key Before You Publish a CSV

A useful public CSV needs a dependable answer to this question: which row in the new release represents the same record as a row in the old release?

Use a primary key: one column, or a combination of columns, whose values identify each row. A valid key is unique and nonblank for every row. In database terms, a primary key may span multiple columns and requires values to be both unique and not null (PostgreSQL documentation).

CSV itself cannot declare that a column must be unique, so the file alone does not enforce this rule (W3C CSV on the Web primer). You need to choose the key, test every release and document it for people downloading or comparing the data.

Start with what one row represents

Write the dataset’s row definition before choosing a column:

One row represents one inspection of one facility on one date.

That definition rules out facility_id as the sole key because a facility can have several inspections. Better candidates are:

1. inspection_id, if the source assigns a permanent identifier to each inspection; or
2. the composite key facility_id + inspection_date + inspection_sequence, if that combination is guaranteed to distinguish inspections.

Prefer a source-system identifier that persists when names, addresses, measurements or statuses change. Do not use a row number generated during export: sorting, filtering or inserting records can assign a different number to the same real-world record in the next release.

Names are usually poor keys. A company, place or category label can be corrected, reformatted or reused. If a natural identifier is unavailable, create a documented publisher-assigned ID and retain the mapping between releases. Do not regenerate it from row order.

Test blanks and duplicates

This Python script parses the finished CSV rather than splitting lines manually. Set KEY to one column, as shown, or use a tuple such as ("facility_id", "inspection_date", "inspection_sequence") for a composite key.

import csv
from collections import Counter

PATH = "inspections.csv"
KEY = ("inspection_id",)

with open(PATH, newline="", encoding="utf-8-sig") as f:
    reader = csv.DictReader(f)
    missing_headers = [name for name in KEY if name not in (reader.fieldnames or [])]
    if missing_headers:
        raise SystemExit(f"Missing key columns: {missing_headers}")

    keys = []
    blank_rows = []
    for row_number, row in enumerate(reader, start=2):
        value = tuple(row[name].strip() for name in KEY)
        if any(part == "" for part in value):
            blank_rows.append((row_number, value))
        keys.append(value)

counts = Counter(keys)
duplicates = {key: count for key, count in counts.items() if count > 1}

print(f"data rows: {len(keys)}")
print(f"rows with a blank key component: {len(blank_rows)}")
print(f"duplicated key values: {len(duplicates)}")

for item in blank_rows[:10]:
    print("blank:", item)
for key, count in list(duplicates.items())[:10]:
    print("duplicate:", key, "rows:", count)

if blank_rows or duplicates:
    raise SystemExit("Primary-key validation failed")

A passing result has zero blank-key rows and zero duplicated key values. Run the test on the actual export you will publish, not only on the source spreadsheet. Preserve key columns as text when formatting matters—for example, 00123 and 123 may be different identifiers. See the leading-zero preservation checklist for import and export checks.

A duplicate does not automatically mean “delete one row.” It can reveal that the proposed key is too broad, the dataset contains legitimate repeated events, or two source records were accidentally combined. Return to the row definition and resolve the cause.

Check stability across releases

Uniqueness within one file is necessary but not sufficient. Before adopting a key, compare at least two releases:

• Records describing the same entity or event should retain the same key.
• A changed name, status or measurement should not produce a new key.
• A genuinely new record should receive a value never assigned to a different record.
• Retired keys should not be recycled.

Then use the key to classify rows as added, removed or matched, and compare the non-key fields only within matched rows. The full workflow is covered in how to compare two versions of a public CSV dataset.

Publish the key definition with the data

Add a short data dictionary entry stating:

• the key column or ordered list of key columns;
• what entity or event one row represents;
• whether the identifier comes from the source or the publisher;
• whether capitalization, whitespace and leading zeros are significant;
• whether identifiers remain stable across releases.

For machine-readable publication, a companion CSV on the Web metadata file can place a primaryKey declaration beside column definitions. The W3C metadata example explains that validators can check whether the declared GID values are all present and unique (W3C Metadata Vocabulary for Tabular Data). A compact companion file could look like this:

{
  "@context": "http://www.w3.org/ns/csvw",
  "url": "inspections.csv",
  "tableSchema": {
    "columns": [
      {"name": "inspection_id", "datatype": "string", "required": true},
      {"name": "facility_name", "datatype": "string"},
      {"name": "inspection_date", "datatype": "date"}
    ],
    "primaryKey": "inspection_id"
  }
}

Keep the human-readable definition too. Readers should not have to inspect a metadata file to learn how records connect across releases.