Can records be identified and combined without multiplying or losing rows? This sounds basic, but it is one of the most important questions in data work. A polished chart cannot rescue a dataset whose rows, fields, or time meaning were misunderstood.
The idea in one minute
An identifier names an entity, a key uniquely identifies a record at the declared grain, and a join combines records according to matching keys.
The safe rule is simple: Assert key uniqueness on both sides and state the expected join cardinality before joining. This gives you an explanation that another person can inspect instead of a hidden assumption.
A tiny synthetic example
The package uses four deliberately small records so every conclusion can be checked by eye. The composite key (entity, timestamp) is unique in the fixture. Entity alone is not unique because every entity appears twice.
The independent result is grain = entity × timestamp; composite key unique = true. “Synthetic” matters: these values teach the concept; they are not observations from a company, exchange, survey, or market-data provider.
Use the four-stage check
- Inspect. Read the fields and ask what one record represents.
- Declare. Write the schema, grain, time basis, or measurement meaning that the calculation depends on.
- Test. Run a small diagnostic that could expose a contradiction.
- Explain. State both the result and its boundary.
This is intentionally more careful than “load a file and calculate.” It prevents the most dangerous data errors: the ones that return reasonable-looking numbers.
The tempting mistake
A many-to-many join can inflate totals while still returning syntactically valid data. The problem is semantic, so more decimal places or faster code will not fix it.
There is also an edge case: Null join keys and duplicate dimension rows need an explicit policy; database and dataframe tools may treat them differently. A good pipeline exposes this state to the reader. It does not quietly select a convenient interpretation.
Try the guided lab
Open the self-contained guided lab. Choose Canonical, Edge, or Failure, then use Step to move from input through declaration, diagnostic, and explanation. The lab starts with useful data, works without a server, supports keyboard controls, and has a deterministic reduced-motion mode.
What this result does not prove
The diagnostic does not prove that the source is representative, error-free, licensed for every use, or fit for an investment decision. It tells you whether the narrow assumption in this lesson survives one explicit check. Unknown metadata is a reason to abstain, not permission to guess.
Optional code verification
Python and TypeScript implementations are included for reproducibility and use the same JSON expectation. They are optional: a nontechnical learner should be able to reach the same conclusion from the table and explanation alone.
Takeaway
Assert key uniqueness on both sides and state the expected join cardinality before joining. If you can say what the input means, show the check, and name the boundary, your result is ready for the next analytical step.
Enhancement studio: draw, compare, explain
This additive studio does not replace the beginner lesson above. It gives you two more drawings, a decision comparison, and short practice prompts so you can explain the idea without copying a formula or writing code.
Drawing 1 — name, apply, check
Read left to right: name what the data means, apply the narrow lesson rule, then use an independent check. Open the full-size concept anatomy.
Choose the right idea
| Decision | This lesson | Closest next or comparison | Why the difference matters |
|---|---|---|---|
| Main question | An identifier names an entity, a key uniquely identifies a record at the declared grain, and a join combines records according to matching keys. | Observations, Entities, Variables, and Datasets | Choose the question before choosing the arithmetic. |
| Safe rule | Assert key uniqueness on both sides and state the expected join cardinality before joining. | Uses its own input and boundary contract. | Neighboring lessons can use the same numbers but answer different questions. |
| Required check | grain = entity × timestamp; composite key unique = true | Re-check its own unit, time, denominator, or schema. | A correct answer to the wrong question is still wrong. |
| Stop condition | A many-to-many join can inflate totals while still returning syntactically valid data. | Move only when its prerequisites are satisfied. | Unknown meaning is a reason to pause, not to guess. |
Drawing 2 — common-mistake clinic
The left side states the safe interpretation; the right side shows the mistake that often produces a believable but misleading result. Open the full-size mistake comparison.
Explain it back without code
- Name it: What does the first input or observation mean?
Answer: An identifier names an entity, a key uniquely identifies a record at the declared grain, and a join combines records according to matching keys. - Choose it: Which rule belongs to this question?
Answer: Assert key uniqueness on both sides and state the expected join cardinality before joining. - Challenge it: What check could make you stop?
Answer: grain = entity × timestamp; composite key unique = true
If your explanation leaves out the unit, period, denominator, grain, or availability time that the lesson needs, it is not complete yet.
Related concepts and learning handoff
- Governed glossary: identifier, primary key, foreign key, join cardinality. Browse the full financial glossary when a term is unfamiliar.
- Continue with: Observations, Entities, Variables, and Datasets.
- Evidence boundary: all displayed numbers remain synthetic teaching data; the drawings do not claim a market observation, forecast, or investment result.
Rendered from the canonical Mermaid sources linked by this article.
Concept flow — D00-F03-A05
ReferencesPrimary sources and evidence notesExpand the source trail, evidence role, and limitations behind the engineering choices.
Expand the source trail, evidence role, and limitations behind the engineering choices.
The lesson uses primary standards, official statistical guidance, or official software documentation. The worked data are synthetic and author-derived.
1. PostgreSQL Documentation — Constraints
- URL: https://www.postgresql.org/docs/18/ddl-constraints.html
- Accessed: 2026-08-10
- Supports: primary-key uniqueness, non-null behavior, and foreign-key referential integrity.
- Limitations: SQL constraint semantics do not automatically define the correct business grain.
- Source role: authoritative definition or implementation reference; no numerical teaching values were copied.
2. pandas API Reference — merge
- URL: https://pandas.pydata.org/docs/reference/api/pandas.merge.html
- Accessed: 2026-08-10
- Supports: join modes and cardinality validation such as one-to-one and many-to-one.
- Limitations: Null-key matching differs from common SQL expectations and needs an explicit policy.
- Source role: authoritative definition or implementation reference; no numerical teaching values were copied.
Evidence boundary
The sources support definitions and operational cautions. They do not validate a particular investment decision, provider dataset, or legal interpretation. The historical-example decision is not useful for this foundations lesson: a named market dataset would add licensing and point-in-time complications without making the core distinction clearer.
Full dependency-light reference implementations in both supported languages.
import { runTopic as runD00Topic, type D00Input, type D00Output } from "../../../../shared/typescript/d00Engine.ts";
/** Run the canonical D00-F03-A05 calculation. */
export function identifiersKeysJoinsAndDataGrain(input: D00Input): D00Output {
return runD00Topic("D00-F03-A05", input);
}
The embedded lab now expands to its full document height, keeping the article as the only scroll surface.