SOPACT ACADEMY · DATA DICTIONARY
How do you build a data dictionary for program and impact data?
Build a data dictionary by naming the few shared fields your decision needs and agreeing what each one means. Record its identifier, definition, source, allowed and missing values, owner and version. For a reported measure, also state the population, unit, period and calculation.
Different programs can keep useful local questions. Map their fields to the shared definition only when the meaning is compatible; retain differences and unresolved mappings. Reuse appropriate registration details and record dated changes instead of asking everyone for the same unchanged information every round.
You will build four example fields, inspect one complete record and practice a definition change. Keep the longer column list as a reference. Map to an outcome or external standard only when your reporting purpose needs it, and preserve the definitions behind earlier observations.
This guide is for a program lead, MEL/evaluation manager, or data administrator at a nonprofit, funder, or multi-site program who needs one indicator to mean one thing across programs, sites, and reporting years. Use the column list and worked record below to build a dictionary in a spreadsheet or your approved workspace.
The whole process in seven steps
- Inventory existing fields across forms, spreadsheets, databases, and reports.
- Consolidate duplicates and assign every approved field a stable identifier.
- Define meaning and format — definition, type, unit, permissible values, and missing-value rules.
- Document collection context — population, applicability, source, timing, calculation, and disaggregation.
- Assign governance — owner, sensitivity, access, and approval status.
- Map to outcomes or external standards where a defensible relationship exists.
- Version and reuse the dictionary across programs, forms, integrations, and reporting.
Begin with the definitions and evidence your team needs. Use AI extraction or system connections where they support the workflow; they are not prerequisites for drafting a useful dictionary.
What is a data dictionary?
A data dictionary is a governed catalog of the data fields an organization collects and what each field means. For every field it records a stable identifier, label, precise definition, data type, unit, permissible values, collection point, source, owner, validation rules, applicable framework mappings, and version history — which makes the field unambiguous but does not yet make the answer readable: knowing that a confidence score is an integer from 1 to 5 still will not tell you whether a 3 is a starting point or a fall from 5, and that interpretation also needs dated observations and context. The dictionary can hold or link the context definitions, while the continuing record supplies the actual history. (Data-element documentation guidance — e.g. NIH/FITBIR and the ISO/IEC 11179 metadata-registry concept — specifies that a data element carry a name, a precise definition, and permissible values: NIH data-element guidance.)
| Artifact | What it does | Scope difference |
|---|---|---|
| Data dictionary | Defines each field's meaning, format, rules, owner, and version | — |
| Codebook | Documents variables, response codes or qualitative coding rules | Often focused on a dataset or coding project; definitions can be reused across projects |
| Data catalog | Helps people discover and govern data assets across systems | Broader scope; may contain or link to field-level dictionary metadata |
What columns should a data dictionary contain?
A field name and type are not enough to interpret a field consistently. Start with the fields needed to interpret and govern your chosen measure. The table below is a fuller reference: add calculation, applicability, sensitivity and mapping details where they apply. Mark an attribute not applicable when appropriate rather than inventing a value to fill a template.
| Column | Purpose |
|---|---|
| Field ID | Stable machine-readable identifier |
| Display label | Human-readable form label |
| Definition | Exact meaning of the field |
| Raw or derived | Collected directly, or calculated from other fields |
| Parent / source fields | For a derived field, the fields it is computed from |
| Data type | Integer, decimal, date, category, text, (nullable) Boolean |
| Unit | People, dollars, hours, percentage, score |
| Allowed values | Valid categories or range |
| Missing-value rule | Unknown, not applicable, declined, not collected |
| Validation rule | Conditions the value must satisfy before acceptance |
| Population | Participant, household, organization, employee |
| Applicable programs | Programs, sites, or services that use the field |
| Requirement status | Required, optional, conditional, not applicable |
| Applicability condition | Rule governing when the field appears |
| Collection point | Application, intake, exit, follow-up |
| Reporting period | Monthly, quarterly, annual, cohort-based |
| Calculation method | Formula for derived indicators |
| Disaggregation | Gender, geography, age, program, cohort |
| Source | Survey, CRM, document, administrative system |
| Owner | Person/team responsible for the definition |
| Sensitivity classification | Public, internal, confidential, restricted (access class) |
| Personal-data flag | Whether the field holds personal / special-category data |
| External mapping | IRIS+, Logframe, SDG, funder metric (where applicable) |
| Version · Effective date · Status | Current version, when it took effect, and Draft/Approved/Deprecated/Retired |
A complete impact data-dictionary example
The table below shows the core columns for readability. Use the columns described above to create a working template that includes governance, access, mapping and version fields.
| Field ID | Label | Type | Allowed / unit | Population | Applicability | Validation | Owner | Ver. |
|---|---|---|---|---|---|---|---|---|
participant_id | Participant ID | Text | UUID | Participant | Always | Unique; non-null | Data admin | 1.0 |
employment_90d | Employed at the 90-day follow-up | Nullable Boolean | Yes / No | Participant | When follow-up done | Requires follow-up date | MEL lead | 1.1 |
hourly_wage_90d | Hourly wage at 90 days | Decimal | ≥0; currency/hr | Participant | Only if employment_90d = Yes | Numeric; currency code | MEL lead | 1.0 |
financial_security_theme | Financial-security theme | Category | Positive/Negative/Mixed/Not present | Participant | When narrative provided | Human-reviewed AI coding | Eval. lead | 1.0 |
One complete record, every attribute — easier to read than a 20-column row, and it shows the governance fields the compact table omits:
| Field ID | employment_90d |
| Definition | Whether a program completer held paid employment at the 90-day follow-up |
| Raw / derived | Raw |
| Data type | Nullable Boolean |
| Allowed values | Yes, No |
| Missing-value reason | Unknown, Not collected, Declined, Not applicable |
| Population | Program completers |
| Applicable programs | Workforce tracks |
| Requirement · applicability condition | Conditional — when the 90-day follow-up is completed |
| Collection point · period | Follow-up · cohort |
| Validation rule | Follow-up date required; value in {Yes,No} |
| Source · owner | Follow-up survey · Program MEL lead |
| Sensitivity · personal-data flag | Confidential · Yes, when linked to an identifiable participant |
| External mapping | Verify applicable IRIS+ metric |
| Version · effective date · status | 1.1 · 2026-07-01 · Approved |
Build your working dictionary. Copy the column list into a spreadsheet or approved workspace. Add the four example fields, then replace them with the small shared core your workflow needs. Keep local-field mappings, exceptions and definition changes beside the approved entries.
Nullable Booleans and missing values
A Boolean has two logical values. "Unknown" is a missingness state, not a third Boolean — so store the value as Yes/No and capture the reason it's absent in a separate missing-value field. Prefer a null value plus a documented missingness reason (or a clearly governed categorical status). Never treat zero as missing, don't mix "not applicable" with "not collected," and avoid arbitrary numeric codes like -99 without metadata.
Define field applicability separately from access permissions
Applicability — which programs and populations use a field, at which stage, required or conditional — belongs in the dictionary. Access — who may view, enter, or edit it, and whether it holds personal data — is an access-control feature around the system. Record both; don't collapse them into one "scope."
Applicability can be conditional: hourly_wage_90d applies only when employment_90d = Yes, which is exactly what a form generator needs. On access, follow least privilege — a wage field is restricted to roles authorized under the organization's privacy and data-governance policy; finance may receive aggregated wage analysis without access to identifiable participant records.
The framework: grade every field Green, Amber, or Red
This is a Sopact field-governance test, not a recognized external data-management standard. Framework mapping counts only where applicable; operational fields such as participant ID, contact preference and intake date do not need an external-framework mapping merely to be useful. Add a mapping only when it serves a real reporting purpose and the definitions support it.
When a shared field needs correction, record the approved change, owner, effective date and affected forms. Keep each existing observation attached to the definition used when it was collected. Apply the new definition prospectively; restate older values only where source evidence supports a documented mapping, retaining the original values and the reason for the restatement.
Version a field without breaking history — and know when it's a new field
Use a new version of the same field only for changes that preserve semantic comparability — a clarified definition, corrected allowed values, non-substantive metadata. If a change materially alters the construct, population, unit, time window, or calculation, create a new field ID and retire or retain the old one.
"Employed at exit" and "employed at the 90-day follow-up" are different measures — different time windows answering different questions — so they are separate fields, not versions of one:
| Field ID | Definition | Status |
|---|---|---|
employment_at_exit | Paid employment on the exit date | Retained or deprecated |
employment_90d | Paid employment at the 90-day follow-up | New field |
A true version change, by contrast, preserves the construct — for example tightening the allowed values on the same field:
| Version | Change to employment_90d |
Effective date | Historical treatment |
|---|---|---|---|
| 1.0 | Allowed values: Yes / No / Unknown | Jan 1, 2026 | Preserve as reported |
| 1.1 | Nullable Boolean (Yes/No) with "Unknown" moved to missing-value reason | Jul 1, 2026 | Preserve raw 1.0 values; in a derived comparison view, map "Unknown" to missing with reason "unknown" and retain the version |
Use one shared dictionary across programs and forms
Local labels and questionnaires may differ. Comparability requires compatible meaning, with an explicit mapping from each local field to the shared definition; an identical label is neither necessary nor sufficient. Two sites' placement rates are meaningfully comparable only when they share the same approved definition, denominator, follow-up window, calculation method, and missing-data rule.
Comparability also depends on eligibility, whether self-employment or part-time counts, evidence source, and reporting-period boundaries. The dictionary fixes all of those on the definition, so "Job Placement Rate" means one thing — not merely hand two sites the same field name.
How AI drafts fields and codes qualitative evidence
AI can draft dictionary fields from existing forms and code open-text evidence into defined fields — but a narrative should never be silently converted into a quantitative value. Distinguish extracted facts, coded qualitative themes, and model-generated interpretations, and route interpretations to human review.
A participant writes, "This was my first paycheck where I wasn't choosing between rent and groceries." AI may tag it as financial security or perceived economic well-being — but it should not infer a wage amount or populate hourly_wage_90d. Use an explicit wage observation, such as an authorized response or pay record, with its date, currency, hourly basis and source. A qualitative statement about financial security cannot supply a missing wage. The dictionary keeps the three outputs separate:
| Output | Example | Treatment |
|---|---|---|
| Explicit fact | "I now earn $22 per hour" | Extract as a candidate wage value; confirm against the wage field |
| Qualitative theme | "I can now afford groceries" | Code as a financial-security theme |
| Interpretation | "Participant appears more confident" | Store as an AI inference requiring human review |
Note the coded theme's own missing states: Not present means the text was reviewed and the theme was absent — different from no narrative provided, not analyzed, or analysis failed, which are missing-value states.
Prompt: draft a data dictionary from existing forms
Where Sopact operationalizes the method — and where humans decide
| Dictionary task | Workflow to configure and test | Human decision |
|---|---|---|
| Inventory fields | Import existing forms and data | Decide which fields are authoritative |
| Consolidate definitions | Shared definitions and local-field mappings | Approve merges and definitions |
| Define valid values | Types and validation rules | Set acceptable values |
| Reuse across programs | Program forms using shared fields | Decide applicability |
| Code narrative evidence | Intelligence Row candidate extraction & themes | Review interpretations |
| Manage changes | Version and effective-date handling | Approve changes |
| Report consistently | Traceable analysis & reporting | Interpret findings |
If your reporting purpose requires external standards, use the reference to map portfolio fields to IRIS+, GRI, and ESRS — begin mapping only after population, unit, period, boundary, and calculation are defined.
Watch the method in practice
These optional demonstrations show how shared definitions support reporting. Complete your four-field exercise first, then watch the example relevant to your reporting question.
Watch (1:26): why one governed definition — population, unit, period, boundary, calculation — turns four framework reports into four views of the same dictionary.
Watch one application of the method: using an IRIS+-aligned data dictionary to support impact reporting (SoPact). It demonstrates the reporting workflow, not a field-by-field dictionary build.
Watch (6:07): where the dictionary stops and context starts — the four layers, data, framework, audience and expression, that turn a correctly defined field into an answer someone can act on.
Frequently asked questions
How do you build a data dictionary for program and impact data?
Inventory existing fields, consolidate duplicates and assign stable IDs, define meaning and format (type, unit, permissible values, missing-value rule, validation rule), document collection context (population, applicability, source, timing, calculation, disaggregation), assign governance (owner, sensitivity, access, status), map to outcomes or standards where defensible, and version changes going forward without rewriting historical records.
What columns should a data dictionary contain?
Begin with the field ID, label, definition, source, allowed and missing values, owner and version. For a reported measure, include the population, unit, period and calculation. Add the validation, access, applicability and mapping details needed for the way the field is collected and used; the full column list is a reference rather than a reason to delay a small pilot.
When do you version a field vs. create a new one?
Version the same field only for changes that preserve the construct — a clearer definition, corrected allowed values, non-substantive metadata. Create a new field ID whenever a change alters the construct, population, unit, time window, or calculation (for example, "employed at exit" vs. "employed at the 90-day follow-up" are separate fields).
How do you handle missing values?
Store the real value and record absence separately: prefer a null plus a documented missingness reason (unknown, not applicable, declined, not collected), or a clearly governed categorical status. Never treat zero as missing, don't mix "not applicable" with "not collected," and avoid arbitrary numeric codes like -99 without metadata.
What's the difference between a data dictionary, a codebook, and a data catalog?
A data dictionary defines each field's meaning, format, rules, owner, and version across the organization. A codebook documents variables, response codes or qualitative coding rules, often for a dataset or project; its definitions may be reused. A data catalog helps people discover and govern data assets across systems, and may contain or link to field-level dictionary metadata — the distinction is scope.
Can AI create a data dictionary from existing forms?
Yes, as a draft. AI can inventory fields and propose IDs, definitions, and types, and code open-text evidence into defined fields. But a human owns the definitions, and AI-generated interpretations — not just extracted facts — must be reviewed before they populate governed fields. Never let AI infer a quantitative value from narrative.
Sources & versions
- Data-element documentation — NIH/FITBIR (name, precise definition, permissible values): fitbir.nih.gov. Metadata-registry concept: ISO/IEC 11179.
- Optional standards-mapping reference: How to map portfolio data to IRIS+, GRI, and ESRS. (For a specific IRIS+ code, cite the exact version consulted.)
Author: Sopact (Unmesh Sheth). Published Aug 2026; last reviewed September 15, 2026. Green/Amber/Red is Sopact's practical field-governance test, not an external data-management standard.