How to Build a Data Dictionary for Program and Impact Data

SOPACT ACADEMY · DATA DICTIONARY

How do you build a data dictionary for program and impact data?

In short: Build a data dictionary by inventorying the fields you already collect, consolidating duplicates, and documenting each one once — a stable ID, human-readable label, precise definition, data type, unit, permissible values, missing-value rule, collection point, calculation method, owner, validation rule, and version. For a multi-program organization the dictionary becomes the shared source for intake, surveys, case records, follow-ups, and reporting: programs use different subsets of fields but never independently redefine the same indicator. Map fields to a Theory of Change outcome, a Logframe indicator, or an IRIS+ metric where a defensible relationship exists — not for every operational field. Then version changes going forward without rewriting historical records.

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. It shows the complete artifact — with a downloadable template — not just the idea.

The whole process in seven steps

1. Inventory existing fields across forms, spreadsheets, databases, and reports.
2. Consolidate duplicates and assign every approved field a stable identifier.
3. Define meaning and format — definition, type, unit, permissible values, and missing-value rules.
4. Document collection context — population, applicability, source, timing, calculation, and disaggregation.
5. Assign governance — owner, sensitivity, access, and approval status.
6. Map to outcomes or external standards where a defensible relationship exists.
7. Version and reuse the dictionary across programs, forms, integrations, and reporting.

AI extraction and API integration come later, as applications of the dictionary — not part of building it.

What is a data dictionary?

In short: 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 — so people, forms, databases, and reports interpret the same field consistently. (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.)

What columns should a data dictionary contain?

In short: A field name and type are not enough to interpret a field consistently. A practical impact data dictionary carries these columns for every field:

A complete impact data-dictionary example

In short: The table below shows the core columns for readability. Download the complete template to see every governance, access, mapping, and version field.

One complete record, every attribute — easier to read than a 20-column row, and it shows the governance fields the compact table omits:

Download the impact data-dictionary template — a spreadsheet with an instructions sheet, all columns above, four example rows, and blank rows to fill. (Ships with this chapter as .xlsx and .csv.)

Nullable Booleans and missing values

In short: 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

In short: 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

In short: This is a Sopact field-governance test, not a recognized external data-management standard. Framework mapping counts only where applicable; valid operational fields (participant ID, consent status, intake date, referral source) never map to a Theory of Change or IRIS+.

When a field grades Red or Amber, fix the shared definition — not just the one form — so every program using it inherits the correction.

Version a field without breaking history — and know when it's a new field

In short: 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 within 90 days" are different measures — different time windows answering different questions — so they are separate fields, not versions of one:

A true version change, by contrast, preserves the construct — for example tightening the allowed values on the same field:

Use one shared dictionary across programs and forms

In short: Reusing the same field label is necessary for comparability but not 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

In short: 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. That value has to come from a wage question. The dictionary keeps the three outputs separate:

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

Once the internal definitions are approved, the next step is to map portfolio fields to IRIS+, GRI, and ESRS — begin mapping only after population, unit, period, boundary, and calculation are defined.

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?

At minimum: field ID, label, definition, raw/derived, data type, unit, allowed values, missing-value rule, validation rule, population, applicable programs, requirement status, applicability condition, collection point, reporting period, calculation method, disaggregation, source, owner, sensitivity classification, personal-data flag, external mapping (where applicable), version, effective date, and status.

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 within 90 days" 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 how one survey or dataset recorded its variables and response codes. 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.
• Map the dictionary to standards next: 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 Aug 2026. Green/Amber/Red is Sopact's practical field-governance test, not an external data-management standard.