Education
LessonIntroductory data science and research methods · 60 min

Match Records Across Two Catalogs

Joining by name looks easy and fails quietly. Joining by identifier works until you ask who issued the identifier and what they meant by it. Students build a match table with an explicit confidence for every row.

entity-resolutionjoinsidentifiersprovenancedata-quality
Download Word document (.doc)

You may print and copy this lesson for one classroom or one family, for as many years as you teach it. You may not resell it or post the file publicly.

Overview

More than one organisation catalogs objects in Earth orbit, and they do not agree with each other. The US Space Force catalog, distributed publicly through CelesTrak, assigns the NORAD catalog number. GCAT, maintained independently by Jonathan McDowell, keeps its own database with its own primary key, JCAT, alongside its own launch and piece designations. OrbitalWiki builds records from both, plus Wikidata, and documents how on its methodology page.

The moment you want to combine two such sources, you face a question that looks trivial and is not: which column do you join on?

Name is the obvious candidate and the worst one. Names get spelled differently, translated, abbreviated, and reused. Constellation members share a stem with only a number to separate them. An object may be renamed after launch. A name is a label for humans, and it was never promised to be unique.

The NORAD catalog number is far better, because it is a real identifier issued by a specific authority. But that is exactly the subtlety. It means "the object the issuing authority assigned this number to". A second catalog that records a NORAD number is making a judgement about which of its own objects corresponds to that number, and its judgement can differ, particularly for closely-spaced pieces from a single launch, for objects catalogued late, or for fragments.

The COSPAR International Designator is often the more portable key across catalogs because it encodes the launch and the piece rather than a position in a queue. It is not a universal solution either, since assigning a piece letter also requires a judgement about which piece is which.

The professional answer is not a better key. It is to stop pretending the join is exact. Produce a match table where every row states the key used and a confidence, and where rows you could not resolve are visible instead of quietly dropped by an inner join.

At a glance

Learning objectives

  • Compare candidate join keys and explain the failure mode of each.
  • Explain why an identifier is only as meaningful as the authority that issues it.
  • Build a match table in which every row carries an explicit confidence.
  • Report unmatched and ambiguous rows as findings rather than discarding them.
  • Explain why an inner join is a lossy and often misleading summary of a cross-catalog comparison.

Prerequisites

  • Basic spreadsheet or dataframe joins.
  • Willingness to report a negative result.

Required software

  • A web browser and a spreadsheet or notebook.

Dataset version

OrbitalWiki live catalog. Record the dataset-release label from /datasets when available, or the exact access date for a live lookup.

Student materials

Match table worksheet (ten objects, three keys, confidence per row)CSV, opens in any spreadsheet app
Download

Student instructions

  1. 1Choose ten objects, deliberately mixing easy and hard cases: include at least two from the same launch, and at least one whose name contains a number or belongs to a large constellation.
  2. 2For each object, record from OrbitalWiki: NORAD catalog number, COSPAR designator, name, and the access date.
  3. 3Look each object up in a second, independent catalog. Record whatever identifiers and name that catalog gives, and its own access date. Record its primary key even when it differs from anything in the first source.
  4. 4Attempt a name-only match across your ten. Count how many matched exactly, how many needed a human judgement, and how many failed. Write down two specific examples of a name that did not match cleanly.
  5. 5Repeat using the NORAD catalog number, then using the COSPAR designator. Record the counts for each.
  6. 6Build a match table with one row per object: both sets of identifiers, the key you used, and a confidence of confirmed, probable, conflicting, or unresolved. Every one of your ten objects must appear, including the ones you could not match.
  7. 7If any object produced disagreeing information between the two catalogs, write it up as a finding: what each source says, when you accessed each, and what evidence would settle it. Do not choose a value because it looks more plausible.
  8. 8Write one paragraph explaining why an inner join on these two sources would have produced a misleading result, referring to your own unresolved rows.

Expected output

  • A ten-row match table in which every row carries a key and an explicit confidence, with nothing dropped.
  • Match counts for all three candidate keys, with two named examples of name-matching failure.
  • Any disagreement written up as a finding with both sources and both access dates.
  • A paragraph explaining the loss an inner join would have caused, grounded in the student’s own unresolved rows.
[teacher]

Teacher materials, not student-facing

Teacher answer keyCSV, marking guidance rewarding reported non-matches
Download

Teaching notes

  • Grade the match table, not the match rate. A student who reports six confirmed, two probable and two unresolved, with reasons, has done better work than one who reports ten out of ten by quietly reaching for whichever key happened to work.
  • Do not tell students in advance which specific objects will disagree, and do not hand them a prepared conflicting pair. Catalogs are revised, and a pair that disagrees today may be reconciled next month. The transferable skill is the search and the write-up, not memorising a famous example.
  • The key insight to draw out in discussion: an identifier means "the thing the issuing authority attached this number to". When a second catalog records that number, it is asserting a correspondence between its object and theirs. Most of the time the assertion is right. Where it is wrong, both catalogs can be internally consistent and still disagree.
  • Constellation members are the most reliable source of name-matching pain: near-identical stems, sequence numbers, occasional renumbering. Requiring one in step 1 guarantees the lesson has something to bite on.
  • Access dates on both sides are not bureaucracy. A disagreement recorded without dates cannot be re-examined later, because you cannot tell whether the sources have since changed.
  • This pairs well with the catalog-number lesson: silent truncation of a six-digit identifier produces exactly the kind of confident wrong join this lesson teaches students to distrust.

Answer key

Why is name a poor join key?
Names are labels for humans. They vary in spelling, transliteration and abbreviation, get reused across objects, and can change after launch. Nothing guarantees uniqueness.
What does a NORAD catalog number actually identify?
The object that the issuing authority assigned that number to. Another catalog recording the same number is asserting that its object corresponds to that one, which is a judgement.
Why does the launch and piece designator often travel better?
It encodes the launch event and the piece rather than a position in a cataloguing queue, so two catalogs derive it from the same underlying facts. Assigning the piece letter is still a judgement.
What belongs in a match table?
Both sets of identifiers, the key used, an explicit confidence, both access dates, and every object considered, including the unmatched ones.
Why is an inner join misleading here?
It silently discards every row that did not match, so the result looks clean and complete while the interesting cases, the disagreements and the gaps, have been deleted from the output.
Two catalogs disagree about an object. What do you do?
Record both claims with their sources and access dates, mark the row conflicting, and state what evidence would resolve it. Do not pick the more plausible-looking value.
Is an unresolved row a failure?
No. It is a result. Reporting it is what makes the rest of the table trustworthy.

How to cite

Cite each catalog separately with its own access date and record URL or identifier, and honour each source’s licence and attribution terms. For a disagreement, cite both sources in full so a reader can check the conflict for themselves.

Need this lesson in another language?

Educators can request a translation or a language not yet available for this lesson.

Request this lesson language →

Sources

← All education paths