Data Dictionary

Goal

The data dictionary fixes, for each data element, a canonical name, the values it may legally take, the format and the length that constrain them and the meaning the organisation gives it. It attacks a problem: a data element rarely carries the same meaning from one system to the next. The customer field in the CRM means the household, the one in billing means the payer, the one in the portal means the logged-in user and all three are called the same thing. That vagueness has a cost: an interface that joins two of these systems is wrong at field level while looking correct, a consolidated report adds up quantities that do not measure the same thing and a migration carries values whose meaning nobody knows any more.

The technique supports every decision that presupposes knowing what a field contains: specifying an interface between two applications, writing a requirement that mentions an attribute, validating an input, framing a data migration, consolidating an indicator produced by several sources, defending the definition of a regulated quantity before an auditor. Without a dictionary, the question "what is this field, exactly" gets an ad hoc answer, different from team to team and day to day. The technique serves the tasks of requirements analysis and design definition, where it specifies the structure and content of a solution's data.

The deliverable is the dictionary itself: one entry per data element, primitive or composite, each carrying its name, its aliases, its values and meanings and its definition, extended for composites by their composition rule. It takes the shape of a table or a set of linked tables, held in a spreadsheet, a wiki or a specialised tool, and it is frequently paired with a data model. BABOK also calls it a metadata repository: its content is the governed description of the organisation's data, its name, its format, its permitted values and its meaning. Its benefit is measured by a test: a developer, an analyst or an auditor who comes across an unknown field finds its meaning, its permitted values and its owner in a minute, without asking the person who wrote the system twelve years ago.

Usage

When to use it

• Integration across several systems: the same field must mean the same thing on both sides of the interface.
• Data migration or conversion: establish what each source field contains before moving it.
• Indicator consolidated from several sources: fix the single definition of the quantity before adding up numbers.
• Data model under construction: give the entity's attributes their format, their length and their permitted values.
• Regulated or audited scope: the definition of a field must be defensible and traceable before an inspector.
• Legacy system without documentation: recover the meaning of the columns before any change or replacement.
• Multiple aliases for one field: several business areas name the same data differently, so a canonical name is needed.
• Analytical work on source data: the analysis depends on what each field contains exactly.

When not to use it

• Ambiguity sitting at the level of the business term: that is the glossary's job, and the field format settles nothing.
• Isolated, throwaway, single-team application: read the schema directly, since the dictionary costs more than it returns.

Description

The dictionary entry: four fields

BABOK records four pieces of information for each data element (§10.12.3.2) and gives all four as mandatory. An entry missing one of these fields stops rendering the service the dictionary exists for.

• Name: the unique name of the element, the one composite elements refer to. It is the key of the dictionary: two distinct elements cannot carry the same name without making every composition ambiguous.
• Aliases: the alternative names used by stakeholders for the same element. The field exists because the organisation does not give up its habits: customer service says "insured number", accounting says "NAVS13", the German-speaking system displays "AHV-Nummer". Recording all three as aliases of one canonical name surfaces the silent duplication while it can still be seen.
• Values and meanings: the list of permitted values, as an enumerated list or as a description of the allowed format, including length and number of characters. When values are abbreviated or coded, their meaning is spelled out: an element whose permitted values are 1, 2 and 3 is unusable until the dictionary says that 1 means "prospect", 2 "active customer" and 3 "terminated customer". This field carries the most work and the most value.
• Description: the definition of the element in the context of the solution. It says what the element measures, denotes or identifies in this system, for this organisation. The standard ISO/IEC 11179-4 sets the rules for writing that definition. A data element definition states what the concept is. A wording confined to the negative defines nothing: "this field is not the termination date" leaves open everything the field does contain. It is stated in the singular. It is a descriptive phrase or sentence. It stands alone, meaning it is understood without opening another entry. It is free of circular reasoning: the defined term does not reappear inside its own definition. It houses neither the rationale, nor the data entry procedure, nor the functional usage of the field, each of which belongs elsewhere. The first of these rules is the one most often broken.

A real dictionary almost always adds two columns that BABOK does not name and that practice imposes: the owner and the validation rules that apply to the value when they go beyond the format (a check digit, a range, a dependency on another element). Both serve maintenance, which is the structural weak point of the technique.

The owner settles three questions that no reading of the schema settles: which values are permitted, what the element means when two systems read it differently and whether a format change goes into build. That authority comes from the owner's place in the organisation: the department answerable for the process where the data originates is the only one that can commit the company to the definition of that data. The dictionary records that accountability and makes it consultable.

Primitive elements, composite elements and the composition notation

A primitive element is atomic: it carries a single value, constrained by a format and a length, and it does not usefully decompose any further for the organisation. A composite element is built from other elements, primitive or themselves composite, assembled according to a composition rule. The rule is written in a compact notation. That notation makes the dictionary a structure.

BABOK names three composition mechanisms and gives a symbol for the sequence only: the sequence (the required order of primitive elements concatenated in the composite structure, marked by a plus sign, as in Customer Name = First Name + Middle Name + Family Name), the repetition (one or more elements may appear several times) and the optional element (it may or may not appear in a given instance of the composite). The classical notation of structured analysis, from which the technique descends and which Tom DeMarco fixed in 1978, adds a fourth operator, the selection, and gives each of the four a symbol. Most real dictionaries use this four-operator notation.

The discipline that gives the notation its interest holds in one rule: a composite references elements already inventoried by their name and never redefines them inline. Writing Customer address = Street + House number + (Address supplement) + NPA + Town + Canton presupposes that all six elements exist as dictionary entries, with their format and their values. Copying "four digits, range 1000 to 9999" into the definition of the address instead of naming NPA reintroduces the duplication the technique exists to remove and guarantees that the two copies will diverge.

Relation to neighbouring techniques

These techniques work the same material and are easily confused.

The glossary defines business terms, for humans, in prose: it says what a customer is, what a policy is, what a claim is. It is the entry point to a shared vocabulary. The data dictionary defines data elements and their structure: the format, the length, the permitted values, the composition. The two often converge on the same word: the glossary says what a customer is for the business, and the dictionary says which fields describe it and which values are legal in each.

Concept modelling captures vocabulary and meaning: noun concepts, verb concepts that link them into facts, independently of any technical representation, readable by business people who would turn down a class diagram. The concept model answers "what are we talking about and what do we assert about it". The data dictionary answers "which fields carry that, in which format, with which values". The second is built better when the first exists. Business rules are written in the vocabulary of the first and validated against the permitted values of the second.

Data modelling gives the structure and the relationships: the entities, their attributes, the cardinalities, the keys. The dictionary defines the elements those attributes are made of. BABOK notes that the dictionary is often used alongside an entity relationship diagram and can be extracted from it. In practice the split is clean: the cardinality between Customer and Policy has no place in a dictionary, and the exact format of the AVS number has no place in an entity relationship diagram. The cross-check verifies that every attribute of the model resolves to a dictionary entry.

Building and maintaining the dictionary

1. Delimit the population of elements
   One solution, one domain or the whole enterprise: the decision drives everything else, and an enterprise ambition without enterprise governance produces a dead document. BABOK counts that reach among the limitations of the technique: whoever passes over the metadata that the other usage scenarios call for gets a dictionary that holds little value at enterprise scale. Start from the artefacts that already reference elements (data models, interface specifications, forms, reports, database schemas).
2. Inventory the primitive elements first
   For each field encountered, capture the name, the aliases in use, the values and meanings (enumeration or format with length and range) and the description in the context of the solution.
3. Resolve synonyms and homonyms before settling the names
   Two teams name the same element differently: choose a canonical name, move the others to aliases. Two teams use the same name for two different things: split into two elements with disambiguated names. This is the highest-value step and the one most often skipped.
4. Build the composite elements
   From the resolved primitives, with the composition notation, referencing each constituent by its name. Place the selection at the right level: when two cases call for different constituents, the alternative spans whole groups.
5. Cross-check against the data model
   Where one exists or is being built in parallel: every attribute of the model must resolve to an entry, and the dictionary must not carry an element that no model and no interface uses. Dead entries rot fastest, because nobody notices they are wrong.
6. Name a person per element or per domain
   BABOK flags the upkeep effort as the limitation of the technique: the maintenance discipline presupposes a person accountable for the accuracy and completeness of each entry. A team is accountable for nothing.
7. Publish the dictionary and wire it to the artefacts that consume it
   A dictionary that gets consulted is a dictionary reachable from the place where the question arises: the requirement, the interface specification, the report, the schema. Isolated in its own document, it is opened only by the person who wrote it.
8. Maintain on trigger
   The change that alters the schema is the change that updates the entry, in the same merge request or the same ticket. A separate "review the dictionary" task is a task that gets postponed.
9. Audit the dictionary periodically against the real schema
   An automated comparison between what is documented and what the database holds (types, lengths, constraints, observed values) catches the drift before a stakeholder trusts a false definition.

When the system already carries its dictionary

Some systems declare the structure of their own data, in a machine-readable form that is accurate by construction. The dictionary is extracted there rather than written, and the work shifts to the half the machine does not carry: the canonical name, the aliases, the meaning of the values and the owner.

The catalogue of a relational database

Every relational database management system describes its own schema in system tables that are queryable in SQL. ISO/IEC 9075-11 standardises that access under the name INFORMATION_SCHEMA, and each engine adds views of its own, which Oracle calls its data dictionary. A query against those views returns, for every column of every table, the type, the length, the precision, whether the column is mandatory or optional, the keys and the constraints. That half of the entry is accurate at the instant it is read: the documented format and the actual format are one object.

The permitted values follow from them in part. A CHECK constraint enumerates the legal values of a coded column, and a foreign key to a reference table gives more still, since that table carries the code and its label side by side. The meaning sits wherever somebody put it. The column name suggests it without ever guaranteeing it: dat_resil can be guessed, reads three ways and is authoritative on none. Schema comments (COMMENT ON TABLE and COMMENT ON COLUMN in Oracle, PostgreSQL and Db2, extended properties in SQL Server) are the one place where a definition travels in the same script as the structure it describes, so the one place where the two cannot diverge. Where a team keeps that discipline, part of the Description field arrives with the schema.

This case moves the cost of the technique without removing it. Step 2 becomes an extraction of a few minutes. Step 9 becomes continuous and free. The bulk of the effort shifts onto steps 3 and 6, namely the synonyms, the homonyms and accountability for accuracy. An extracted entry is accurate about the format and silent about the meaning. Taking a column name for a definition produces a dictionary whose every line looks documented.

The active dictionary of a packaged system

A dictionary is active when the system is generated from it rather than described against it. Some enterprise resource planning systems hold the dictionary that way. SAP's ABAP Dictionary is the reference case.

The consequence reverses the one that governs a hand-kept dictionary. SAP's documentation states the first effect: modifying an existing domain affects all users of that domain, and the foreign keys resting on the fields concerned can become inconsistent. A change of length or type goes further and forces a conversion of the tables concerned. Widening the domain of a supplier number touches every table holding a field derived from it, plus the screens and the interfaces that display its labels. Here the platform guarantees the property a manual dictionary pursues through discipline: a definition written once and once only. The price is reach: a format change is global by construction, so it cannot be steered module by module.

Two clarifications are worth stating, because confusing them is expensive. First, a dictionary object is changed through development and transported like code; on a domain delivered by the vendor, the operation is a modification of the standard. Promising "simple configuration" to lengthen a field amounts to promising a table conversion. Second, the length the business uses is almost never set in the dictionary. The customer number rests on a ten-character domain that does not move; through the account group, the number range and the choice between internal and external assignment, configuration fixes how many characters are actually used inside that format. An external range of six digits produces a number the user reads as 123456 and the database stores as 0000123456, the conversion routine attached to the domain filling in the leading zeros.

An entry that copies CHAR(10) out of the dictionary is accurate and misleading: it gives the format of the container and stays silent on the rule that governs the values, which lives in configuration. It is also silent on the element having two forms, one internal and one external, the second being the one the business names and every interchange file carries. The Values and meanings field is therefore filled from configuration, and the Aliases field records both forms. The same gap reappears at the level of the object: since the business partner became the central object, a customer and a supplier are two roles of one party, which carries a number of its own alongside the customer number and the supplier number. Three identifiers then designate the same party, and the dictionary does not say which one the business calls "the number".

What the active dictionary does give is what few sources give: labels already translated, documentation per element and a guarantee of technical accuracy. What it stays silent on is left untouched: the name the business uses, the owner of the definition and the real use of the field. A standard field repurposed by the company reads as correct in the dictionary and false in the data.

The dictionary in an analytics initiative

The validation rules column that practice adds to BABOK's four fields is anchored in IIBA's Guide to Business Data Analytics (§3.4), which extends the entry to business rules and validation rules, including for derived and transformed elements. The owner column rests on practice alone.

The guide names the business rule alongside the validation rule, and the six-column model gives it no column. The question arises with the first derived entry. The rule that calculates a derived element goes into Values and meanings, next to the format and the list of permitted values, because it determines the value the element carries. The validation rules keep what rejects a candidate value: a check digit, a range, a dependency on another element. The business rule that governs the element without calculating it or constraining it belongs to the business rules repository, which the dictionary cites by the name of the rule instead of copying it out.

The guide adds a question a single-solution dictionary never has to ask: where the element is. In an analytics initiative the dictionary locates each element across the organisation's systems, which makes it the inventory of what actually exists before the research question is framed. Read alongside an entity-relationship model, it says how to join data from several sources: type, format and length decide whether the join is possible.

What makes the exercise fail

Silent divergence from the real schema

This is the central trap, lethal because it is invisible. The dictionary is written once, at project close, then the schema evolves: a column is widened, an enumeration gains a value, a field is repurposed and nobody touches the corresponding entry. The stale dictionary then costs more than no dictionary at all, because it is still consulted and believed: a developer reads the documented format, builds a validation on it and gets it wrong in a way that looks authorised. Without a dictionary, the same person would have gone and looked at the system. Steps 8 and 9 exist for this reason alone.

Duplicating the glossary

Writing into the Description what "customer" means for the business produces two documents that will contradict each other as soon as one is updated and the other is not. The Description stays confined to the element in the context of the solution's data, and the debate about the business term goes back to the glossary.

Blindness to homonyms

Treating the CRM's customer and billing's customer as one element because they carry the same name produces an integration that is wrong at field level, while the dictionary announces perfect consistency. Step 3 is the only guard.

The composite that copies out its constituents

Copying the constituents out in full in the definition of a composite ruins the single source and restores the duplication the technique removes.

The selection placed too low

Writing the alternative between two identifiers, then requiring of every case the attributes of one of them, produces a composition rule that admits a case and immediately makes it invalid. The alternative spans the whole group of constituents that depend on the case selected.

Drift toward an enterprise catalogue without the matching governance

Trying to keep by hand, on top of the four fields, end-to-end lineage, quality indicators and access rights produces a document too heavy to maintain. Those dimensions belong to a cataloguing platform and its accountability model.

The coded value whose meaning is never written down

An enumeration 1, 2, 3 without its legend turns the dictionary into an empty index. BABOK says it explicitly: an abbreviated value carries the explanation of its meaning.

AI considerations

Reverse engineering is the clearest use. A language model reads a database schema (column names, types, constraints, indexes), reads the code that writes and reads it, observes a sample of values and produces a first draft of entries: a proposed canonical name, aliases spotted across the various systems, a format and a length inferred from the type, an enumeration reconstructed from the distinct values encountered. On a legacy schema of several hundred undocumented columns, that first draft turns a job of several weeks into a review of a few days.

Drift detection is the highest-value use, because it attacks the central trap of the technique. Continuously comparing what the dictionary declares (format, length, permitted values) with what the live schema actually holds (actual type, actual constraint, distribution of observed values) is mechanical, repetitive and thankless work, exactly the kind of vigilance human attention does not sustain over time and an automated pipeline sustains without effort. An unknown value appearing in an enumerated column, an exceeded length, a vanished column: each of these gaps is raised as an alert to the element's owner.

The third use covers synonyms and homonyms across systems. Faced with two schemas or two existing dictionaries, a model flags the pairs "these two fields probably describe the same thing under two names" and the cases "this name denotes two different things in these two databases", from the names, the types, the value distributions and the context. It accelerates step 3 by producing the candidates; the ruling stays human.

The limit is sharp and bears on meaning. The authoritative meaning of a data element is a business fact. A model that observes a customer_status column holding the values 1, 2 and 3, notices that rows with 3 have no recent activity and concludes that "3 probably means terminated" has produced a conjecture presented as a definition, indistinguishable in the dictionary from a value the business confirmed. The column may just as well encode an administrative hold inherited from a system replaced in 2014. No model can settle it, because the answer is a decision the organisation has taken or must take. Every entry drafted by a machine is a proposal addressed to the element's owner, who confirms it before it enters the dictionary as a fact. The same principle covers data sensitivity: exposing a production schema, its samples and its distributions to an external service is a data protection decision, taken before the use is made.

Examples

The example is the dictionary of a health insurer in French-speaking Switzerland, restricted to the elements that describe an insured person and their contact details. Each entry carries the four BABOK fields (name, aliases, values and meanings, description) and the two that practice adds (owner, validation rules). Format and length live inside "Values and meanings": BABOK files the description of allowed formats there, including the number of characters. The Swiss identifiers test that column field by field, because their format is constrained and the constraint is checkable.

One unintentional reading comes out of the table. "Police number" is the cadastral designation of the building number in French-speaking Switzerland, and "policy" is the insurance contract, the same word in French. In the dictionary of an insurer, one French word denotes two unrelated things, and one of the two also appears in the cardinality between Customer and Policy. This is a homonym in French, and the guard is the one from step 3: drop the alias or keep it disambiguated, "Building police number". The table takes the second route. The Aliases field alone surfaced the conflict.

The composite elements are then built from these primitives, never redefining any of them. Together, these rules use the four operators of the notation.

A validation gap comes out of the composition. The table of primitives already requires the e-mail address to be confirmed before it can become the preferred contact channel. The presence dependency, by contrast, appears only in the composition: Customer contact details makes the E-mail optional, while Preferred channel allows it to be chosen. The preferred channel can therefore be "E-mail" only if the address is filled in.

Visualisations

The composition tree shows at a glance what the notation says in one line, and it makes visible what the line hides: the depth. Insured party references Customer address and Customer contact details, which in turn reference primitives, and that nesting distinguishes a dictionary from a plain list of fields. The nature of the link is read off the connector: Address supplement is optional in this composition, and the same primitive element would be mandatory in another. The selection demands the tree more sharply still. This operator constrains a group of branches, of which exactly one is taken, and no mark placed on an isolated element can state that constraint.

The tree is also the checking instrument of the dictionary, and it reveals defects that a table of rules read line by line lets through. An orphan leaf, a constituent that appears nowhere as a dictionary entry, is an inline redefinition: the composite has copied its constituent out in full. A primitive that no composite references is either a dead entry, an element that no model and no interface uses and that step 5 prescribes discarding, or the sign that a composite has been forgotten. A cycle, a composite that through a chain of references ends up referencing itself, is a circular composition, undetectable in a set of rules taken one at a time and immediate to the eye on the tree. These three checks catch what step 9 would catch later, against the real schema and at greater cost.

Cost

Tooling

A shared spreadsheet or a wiki page is enough as long as the scope is bounded, there is only one team and the schema is stable. The licence cost is nil, the structure of the dictionary folds into columns and sorting by name or by alias surfaces the duplicates. The limit is known in advance: maintenance is manual, so it rests on discipline, so it gives way the first time a release is rushed. This choice is defensible when the volatility of the schema is low, and it is paid for when it is not.

The next layer lives in the code. dbt docs and the equivalent tools of the transformation layer generate the documentation of the elements from the modelled schema, at every build: the description is written in the same repository as the transformation, it is reviewed in the same merge request and it cannot drift from the schema since it is extracted from it. That is the structural answer to the central trap, and it fits as soon as the data already lives in a transformation pipeline. Schema registries (for event and streaming data) go further on a narrower scope: they version the structural definition and refuse a non-conforming message in production, which offers strong protection against drift, on that data segment alone and without carrying the aliases or the business description.

Data catalogues are justified when the scope is the enterprise, several systems must be reconciled and the organisation can invest in an accountability model. Collibra and Alation are the established commercial platforms: they automatically collect the technical metadata of live schemas, which attacks drift, and lay governance workflows on top (owner, approval, definition lifecycle). DataHub and OpenMetadata offer the same family of functions as open source and are the option to consider when the licence price is the blocker, at the cost of operating the platform.

The reference framework for all this tooling is the ISO/IEC 11179 standard, which defines metadata registries: part 1 lays down the general framework, and part 4 fixes the rules for writing a data element definition. The DAMA-DMBOK, the data management body of knowledge, files the dictionary under the metadata management chapter and gives its accountability model. Both are worth reading before choosing a platform, because they describe what the platform is supposed to carry.

Sources

• IIBA, A Guide to the Business Analysis Body of Knowledge (BABOK Guide) v3, §10.12 Data Dictionary.
• ISO/IEC 11179-1, Information technology - Metadata registries (MDR) - Part 1: Framework: the normative framework for metadata registries, of which the data dictionary is the most common form.
• ISO/IEC 11179-4, Information technology - Metadata registries (MDR) - Part 4: Formulation of data definitions: the rules for writing a data element definition, the direct reference for the Description field.
• ISO/IEC 9075-11, Information technology - Database languages SQL - Part 11: Information and definition schemas (SQL/Schemata): the standard defining INFORMATION_SCHEMA, the standardised access to a relational database's catalogue.
• DAMA International, DAMA-DMBOK: Data Management Body of Knowledge, metadata management chapter: the accountability and governance model for metadata repositories.
• SAP, ABAP Keyword Documentation, ABAP Dictionary: Technical Attributes of Domains: the technical attributes a domain carries and the reach of a change over every field derived from it.
• Tom DeMarco, Structured Analysis and System Specification, Yourdon Press, 1978: the source of the classical four-operator composition notation (sequence, repetition, optional, selection).
• IIBA, Guide to Business Data Analytics, §3.4 Data Dictionary: the entry extended to business rules and validation (derived elements included) and an element located across several systems.