In-depth practical guides

Build a usable data dictionary

Describe row grain, columns, types, units, missing values and keys before calculations or joins.

Updated :

Laptop showing an abstract table beside cards defining its columns on a desk. Illustrative scene.

AI-generated illustration

Can every value be checked against its definition?

A column heading is insufficient to understand a file. A dictionary connects fields to definitions, value domains and schema versions. Distinguish the dataset from distributions such as CSV and API.

  1. Define a row

    State object, geography and period: a company-year differs from an establishment-month. Specify the expected unique key and relationships with other tables.

  2. Describe every field

    For each column record exact name, definition, logical type, format, unit, code list, nullability and origin. Preserve leading-zero identifiers as text.

  3. Test and version

    Test uniqueness, domains and required fields on the real file. Empty string, not applicable and zero differ. Date changes to definitions, codes or types.

Reading workshop

A case-record contract, not silent repair

The DEMO contract defines one row per case. Its invented rules describe no real register.

Fictional data and records for learning: none of these results comes from a live service query.

Retain received values and reject deviations

The distribution is JSON: record_id is a four-digit string, state is OPEN or CLOSED, and amount is a decimal number or null. record_id must be unique. Case and types are part of the contract.

If the table exceeds the screen, scroll horizontally; arrow keys work when the region is focused.

Retain received values and reject deviations
Field or ruleTest valueContract readingAction
record_id: text"0042"; number 42The string is allowed; the number violates the typeReject 42 without inventing its zeros.
state: exact codeOPEN; openOPEN is allowed; open is outside the domainDo not change case without an approved rule.
amount: number or null12.5; "12.5"; null; ""The number and null are allowed; both strings are rejectednull means unknown; never default to zero.
Unique record_idTwo rows containing "0042"The key repeats even when amounts differBlock the output from approval and request the correct grain.

Can automatic conversion make every row valid?

Open each proposition to compare its diagnosis. More checks may still be needed.

Yes: convert numbers, case and empty cells

No. This erases anomalies and introduces assumptions absent from the contract.

No: retain raw values and named violations

Yes. Separate type errors, invalid codes and duplicate keys. Resolve them using documentation or the producer.

null and an empty string are always equivalent

No. This JSON contract distinguishes them. A CSV may have another rule, documented separately.

Fictional validation record

Contract: DEMO, one row per case, JSON
record_id: four-digit text, unique key
state: OPEN or CLOSED, exact case
amount: decimal number or null; null=unknown
Violations: type, code, empty string, duplicate key
Decision: raw data retained, output not approved

This notation summarizes a teaching contract, not a complete executable schema. No user file is validated here.

Documentation of the rules, separate from fictional examples

Compare other situations and exercises

Code converted to a number

Preserve the exact code and test loading does not remove leading zeros.

Amount without unit

Identify currency, scale and period before summing; do not infer units from magnitude.

Guided reading of an excerpt

Three rows, four rules to check

In this fictional example, each row describes a site for one year. The dictionary below sets the reading rules; the data deliberately contains an anomaly.

Fictional exercise: identifiers, values and dates illustrate the check. This excerpt is not a live collected response.

  1. Read the key

    The pair site_id and year must be unique. A site may appear in several years.

  2. Read the values

    site_id is a three-digit text identifier. amount_kEUR is an annual amount in thousands of euros; an empty cell means not reported.

  3. Check before calculating

    Compare key pairs, preserve leading zeros and distinguish a zero amount from an unknown amount before aggregation.

Excerpt and simplified dictionary

site_id,year,amount_kEUR
007,2024,
007,2025,0
007,2025,12.5

site_id     : string; pattern = ^[0-9]{3}$; required
year        : integer; required
amount_kEUR : decimal; unit = 1000 EUR; empty = unknown
key         : (site_id, year); unique

What to inspect

Composite key
The last two rows share the pair 007 and 2025: they violate uniqueness. Do not choose arbitrarily which row to keep.
Unit
12.5 represents 12,500 EUR in this example, without currency conversion.
Missing value
The empty amount in the first row is unknown; 0 in the second row is a reported zero amount.
Can the amount column be summed directly?

Reasoning

No. First resolve the duplicate key with the producer and define how to handle missing values. Summing only known values would hide the unknown row and could count the same site-year twice. Keep the original rows and document the decision.

Official references : W3C — Model for Tabular Data and Metadata on the Web

Worked exercise

Missing unit and precision in a dictionary

Fictional example: the values and documents teach the method and do not describe a real organisation.

The producer defines amount in thousands of euros, rounded to one decimal place. The internal dictionary says only “number”; raw amounts are not supplied.

If the table exceeds the screen, scroll horizontally; arrow keys work when the region is focused.

Starting information
Rowamount
A1.2
B2.5

Your checks

  1. Add unit, multiplier and rounding rule.
  2. Separate computer type from column meaning.
  3. Check previous calculations using this field.
Read the answer

Answer and decision

Published values sum to 3.7 thousand euros, or 3,700 euros. Writing 3.70 creates no extra precision. Define the field's unit and explain that the sum uses rounded values rather than known exact amounts.

If the case changes

If unrounded raw amounts become available, calculate them separately and identify their version. Add a missing-value case to the fictional dataset: amount=0 means a known zero amount, whereas a cell declared unknown supplies no amount. Document the missing-value code and check that import does not silently turn it into zero before aggregation.

Record to retain

Keep definition, unit, scale, precision, missing codes and a valid example.

Documentation reference : W3C — CSV on the Web: A Primer · Consulted on

DOCUMENTED REFERENCE

JSON Pointer: distinguish missing, zero and null

RFC 6901 addresses a value inside JSON. It is neither JSONPath nor a modification operation. The reader uses a small invented teaching document, not real observations, distinguishing resolved value, missing path and invalid syntax.

items0count/items/0/count → 0
JSON Pointer: distinguish missing, zero and null
ReferenceValue or formWhat it identifies
""Root valueEmpty string selects the whole document, not no result; empty input can be valid.
/Empty member nameA slash selects a member named empty string, if present. This differs from the root.
~1/ inside member nameEncode a slash within a member name as ~1. Property a/b differs from a containing b.
~0~ inside member nameEncode tilde as ~0. Decode ~1 before ~0: ~01 means literal ~1, not slash.
/items/0Zero-based array indexArray indexing starts at zero; use non-negative decimal integers without extra leading zeros.
/items/01Invalid array index01 is not a valid array index but can be an exact object key. Container type matters.
/items/-No existing array memberDash selects a nonexistent position after the final array element. Reading does not automatically apply another protocol’s append convention.
#/a~1bURI-fragment formURI-fragment form starts # and also applies URI decoding. Encode literal percent there; ordinary text form must not be URI-decoded.

Read a JSON Pointer in a teaching example

Invented exercise document: these numbers are not observations. Read locally without requests or document changes.

Teaching document

{
    "items": [
        {
            "id": "A",
            "count": 0,
            "note": null
        },
        {
            "id": "B",
            "count": 3,
            "note": ""
        }
    ],
    "a/b": {
        "m~n": true
    },
    "": false,
    "01": "code",
    "é": "accent",
    "~1": "literal",
    "c%d": "percent"
}

Initial example: /items/0/count gives 0; /items/0/note gives null; /items/1/note gives an empty string; / gives false.

Value found · number

Selected value

0

Property names stay exact: no whitespace removal or accent normalization. The reader adds nothing and does not process JSONPath.

Implication for the record

Compare exact names without accent, case or whitespace correction. Preserve null, false, zero and empty strings. Valid syntax may still resolve no value; do not manufacture zero. Keys and pointer syntax do not change with interface language.

Why does /items/0/note resolve null when another path fails?

The note member exists and explicitly holds null. An absent member resolves nothing; these differ in joins and table conversions.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

JSON: valid text can still lose an identifier

RFC 8259 defines exchange syntax, not unlimited precision in every reader. Long numeric identifiers may change during parsing, before a spreadsheet display. Examples are teaching forms, not assigned identifiers.

JSON: valid text can still lose an identifier
ReferenceValue or formWhat it identifies
Integer interoperability−9007199254740991 … 9007199254740991RFC identifies this integer range as interoperable with common binary64 precision, not JSON’s universal syntax limit.
Beyond 2^53 − 1binary64 · 2^53 + 1Beyond it, some integers remain exact but consecutive integers are not all distinguishable. Check actual reader type.
"00123" / 123"00123" ≠ 123String "00123" preserves zeros; number 123 does not. Codes need not be arithmetic quantities.
NaN / InfinityJSON: ✗NaN and Infinity are not JSON numeric literals; accepting extensions does not establish strict compatibility.
Duplicate object namesSHOULDObject names SHOULD be unique. Readers may retain last, reject duplicates or expose all pairs.
Network exchangeUTF-8 · RFC 8259 §8.1Exchanges outside closed ecosystems require UTF-8. Correct decoding does not prevent numeric rounding.

Implication for the record

Set types and precision before parsing. Stringifying a rounded number cannot recover digits. Detect forbidden duplicate names before overwrite; retain source text.

Does JSON.parse verify all long identifiers?

No. JavaScript numbers may already be rounded and repeated keys overwritten. Validate the source contract as well as parsed objects.

Primary documentation

Documentation consulted on .

Record to retain

  • Row object and expected key.
  • Definitions, types, units, codes and missing-value rules.
  • Schema version, checked file and detected anomalies.

Official references

Related source profiles

Related protocols

Checks before reaching a conclusion

  • Is the expected key unique in the checked file?
  • Are types, units and missing values defined without implicit assumptions?
  • Are deviations from the dictionary recorded before calculation?

Frequently asked questions

Are examples enough?

No: an example illustrates one value; a definition specifies all admissible values.

Is a version needed for each format?

Document the common schema and distribution differences: names, formats and null rules.

What if the definition of a field is missing?

Find the documentation and keep the uncertainty. Observed values can help formulate an assumption, but they are not always enough to confirm the meaning or the relevant operations.

TOOLS / REGISTERS

Information to record

Test uniqueness, domains and required fields on the real file. Empty string, not applicable and zero differ. Date changes to definitions, codes or types.

  • Access date and time zone
  • Exact URL
  • Version / revision
  • Object of one row
  • Exact column name
  • Logical type and format
  • Method / definition
  • Unit / calculation basis
  • Value domain and code list
  • Missing values and nullability
  • Expected key and uniqueness
  • Producer / publisher
  • Decision / next action
  • Review owner
  • Next review

DOCUMENTED REFERENCE

Keys and constraints guarantee different things

An id column is not automatically a primary key. Effective, validated constraints define guarantees. These rules are for PostgreSQL 18; other systems may treat unique-constraint NULLs differently.

[[null], [null]]UNIQUE → ✓UNIQUE NULLS NOT DISTINCT → ✕ · PRIMARY KEY → ✕
Keys and constraints guarantee different things
ReferenceValue or formWhat it identifies
PRIMARY KEY (a)Unique and non-nullSimple primary key requires unique non-null values, not real-world correctness or temporal stability.
UNIQUE (a)NULLs distinct by defaultPostgreSQL UNIQUE allows repeated NULLs by default; uniqueness is not completeness.
UNIQUE NULLS NOT DISTINCT (a)At most one NULL in this columnNULLS NOT DISTINCT permits at most one NULL in single column a but still allows one missing value.
PRIMARY KEY (a, b)Unique pair · non-null componentsComposite primary key makes the pair unique and both non-null, not each component individually unique.
FOREIGN KEY · MATCH SIMPLEAny NULL may exempt referenceMATCH SIMPLE may exempt a composite referencing row when any component is NULL.
FOREIGN KEY · MATCH FULLOnly all-NULL exempts referenceMATCH FULL exempts only all-NULL rows; mixed null/non-null fails. Use NOT NULL for mandatory complete references.
CHECK (x > 0)TRUE or unknown passesCHECK passes TRUE or unknown. Missing x can pass x > 0; presence needs NOT NULL.

Compare three policies on the same keys

JSON array of 0–50 rows; each row has 1–3 components, always the same count. A component is a string of at most 64 code points or null. Both joined tables must have the same width. Maximum 20,000 JSON characters per field. An empty string remains a value distinct from null.

Teaching examples and local calculation, with no database query or transmission of inputs. Case-sensitive comparison without trimming spaces, Unicode normalisation or type conversion. Results describe entered rows only; they do not certify actual database constraints.

Teaching example calculated; edit the inputs, then examine them.

Entered rows
2
Rows with at least one NULL
2
UNIQUE
Compatible with this policy
UNIQUE NULLS NOT DISTINCT
Incompatible with this policy
PRIMARY KEY
Incompatible with this policy
Six teaching datasets: compatibility by policy
Key datasetUNIQUEUNIQUE NULLS NOT DISTINCTPRIMARY KEY
[["001"],["002"]]Compatible with this policyCompatible with this policyCompatible with this policy
[["001"],["001"]]Incompatible with this policyIncompatible with this policyIncompatible with this policy
[[null]]Compatible with this policyCompatible with this policyIncompatible with this policy
[[null],[null]]Compatible with this policyIncompatible with this policyIncompatible with this policy
[["FR",null],["FR",null]]Compatible with this policyIncompatible with this policyIncompatible with this policy
[["FR","001"],["FI","001"]]Compatible with this policyCompatible with this policyCompatible with this policy
Initial teaching-example data
{
    "keys": [
        [
            null
        ],
        [
            null
        ]
    ]
}

Implication for the record

A foreign key alone does not make a one-to-one relationship; many children may reference a parent. Record exact columns, NULL policy and actual validation. CSV does not automatically carry database guarantees.

Does UNIQUE justify deleting export duplicates?

No. Export grain, combined vintages or missing key components may differ from the constrained table. Inspect these before deleting rows.

Primary documentation

Documentation consulted on .

DEEPER READING / PRACTICE

Description of fields before transforming them

A table contains short column names and codes that are difficult to interpret. The dictionary must give a useful definition and keep the ambiguities unresolved.

Updated on .

Priority checks

Document name, type, unit, modalities, missing values and examples. Identify the keys and fields whose meaning depends on another variable.

Interpretation pitfalls

A digital type does not mean that we can add values. A code may represent a category, an identificator or an absent value.

Working record to retain

The dictionary connects the original field, definition, reading rules and documentary source. The assumptions are separated from confirmed definitions.