Data Dictionary Template and Example for Business Analysts

What a data dictionary is and why BAs need one

A data dictionary is a reference table that explains the data your project creates, stores, moves or reports on. Each row is one field. Each column answers a question about that field: what it means, what it looks like, where it comes from and who decides what it should contain.

BAs care about it because most data problems are really meaning problems. A developer reads 'total' and thinks one thing. A finance user reads it and thinks another. A tester has a third idea. The dictionary forces one agreed definition and writes it down where everyone can find it.

It also saves time later. When someone asks why a report number looks odd, or which field an API should send, the answer is already written. Documenting data clearly is one of the practical skills covered in our guide to business analyst skills.

If you are new to entities and keys, read about the entity relationship diagram first. The dictionary adds the field-level detail that a diagram leaves out.

Fields to include in a data dictionary template

You do not need every column below on day one. Start with the core group and add the rest as the project needs them. A spreadsheet with these columns is a perfectly good template.

Core columns (always include)

• Field name: the technical name used in the system, for example order_status. Copy it exactly.
• Business name: the friendly name users see on screens and reports, for example 'Order status'.
• Definition: one or two plain sentences that say what the field means, including what it covers and what it leaves out.
• Data type and format: text, whole number, decimal, date or true/false, plus length or pattern.
• Allowed values or range: a fixed list, a minimum and maximum, or a pattern.
• Mandatory and default: whether the field can be empty, and what value it holds if nobody sets it.
• Example value: one realistic, made-up sample. Examples catch misunderstandings faster than definitions.

Rules and source columns

• Source: the system and table, screen, file or API the value comes from.
• Business rule or derivation: how a calculated field is worked out, and when it is set, changed or reset.
• Sensitivity: whether the field holds personal or confidential data, so it can be masked or restricted.

Governance and traceability columns

• Business owner: the person or role who decides what the field means.
• Technical owner: the team that maintains the field in the system.
• Linked requirements: the requirement or user story IDs that create or use the field.
• Used in reports: the reports, dashboards or KPIs that depend on it.
• Last changed and why: a date, a version or a link to the decision that changed it.

How to build a data dictionary step by step

Build the dictionary alongside the requirements, not after go-live. These steps work for a new feature, a data migration or a reporting project.

1. Agree the scope. List the screens, tables, files, APIs and reports in scope. Only fields inside that scope need entries.
2. Collect the raw field list. Ask developers for table structures, export column names from existing reports, or read the API specification. A simple SELECT on a few sample rows shows you real values, as explained in our SQL guide for business analysts.
3. Draft definitions in plain English. Write what each field means, including what it leaves out. Mark anything you are unsure about as an open question.
4. Review meanings with business owners. Walk through the draft with the people who own the data. Their answers become the agreed definitions.
5. Confirm technical details. With the development team, check types, lengths, defaults and how calculated fields are worked out and when.
6. Link each field to requirements and reports. Add requirement IDs and report names so every field has a reason to exist.
7. Publish and keep a change log. Put it somewhere shared, such as a wiki page, and record every change with a date, a reason and an approver.

Worked example: a data dictionary for a loyalty feature

Marigold Grocers is a fictional online grocery in India. It is adding loyalty points to its app. The agreed rule, requirement REQ-LOY-03, says: customers earn 1 point for every ₹10 of goods value, rounded down. Goods value includes tax, because Marigold shows tax-inclusive prices, but it does not include the delivery fee. The loyalty manager confirmed the tax question, and the answer is recorded as decision D-07.

Here are the example rows the BA, Nadia, drafted for the orders data. The sample order has goods worth ₹1,200 (tax included) and a delivery fee of ₹50, so the customer pays ₹1,250 and earns 120 points.

Field name     | Business name  | Type          | Mandatory      | Allowed values / format                  | Example
order_id       | Order number   | Text(12)      | Yes            | MG- then 9 digits                        | MG-000451234
order_status   | Order status   | Text          | Yes            | Placed, Delivered, Cancelled, Returned   | Delivered
goods_subtotal | Goods value    | Decimal(10,2) | Yes            | 0.00 or more; includes tax; no delivery  | 1200.00
delivery_fee   | Delivery fee   | Decimal(10,2) | Yes, default 0 | 0.00 or more                             | 50.00
order_total    | Amount paid    | Decimal(10,2) | Yes            | goods_subtotal + delivery_fee            | 1250.00
points_earned  | Points earned  | Integer       | Yes, default 0 | 0 or more; rule REQ-LOY-03               | 120
customer_email | Customer email | Text(254)     | Yes            | Valid email; personal data, mask it      | (masked)

Notice that every amount field says what it includes. That one habit prevents most arguments later. Here is the full entry for the calculated field, with the extra columns that do not fit in the summary table.

Two weeks later, during testing, the dictionary paid for itself.

The fix took minutes because the definition already existed. Without the dictionary, the team would have argued about which number was right, and wrong points could have reached real customers.

Who maintains the data dictionary

A data dictionary is shared work, but it needs one clear keeper. On most projects the split looks like this:

• The BA drafts the dictionary, runs the reviews, keeps open questions visible and makes sure entries match the requirements.
• Business data owners (for example a finance lead or a loyalty manager) approve definitions and business rules. They decide what a field means.
• The development or data team confirms types, lengths, defaults, sources and calculations. They decide how a field is stored.
• Testers and report builders flag gaps they find and use the dictionary as the source of expected results.

During the project, the BA is usually the keeper. After go-live, ownership often moves to a data steward, a product owner or the data team. Agree this handover before the project closes, or the dictionary will slowly go out of date.

Treat changes like requirement changes. If a definition changes, record who approved it, when and why, and tell the people whose reports or tests depend on it.

Linking the dictionary to requirements and reports

A field without a link is a field nobody can explain. Two kinds of link matter most.

Fields to requirements

Add a column for requirement or user story IDs. When a story says 'show the customer their points', the dictionary row for points_earned lists that story. This works in both directions: from a requirement you can find every field it touches, and from a field you can see why it exists. If your project keeps a requirements traceability matrix, reference the dictionary from it rather than copying definitions into it.

Fields to reports and KPIs

Add a column for the reports, dashboards and measures that use each field. Report builders then know exactly which field to use and what it means. When someone plans to change a field, this column shows which reports will be affected. It also helps when building measures in reporting tools, as described in our Power BI guide for business analysts.

Keep links as IDs or page names, not copied text. Copies drift apart over time. Links stay true.

Common data dictionary mistakes

• Definitions that repeat the name. 'Order total: the total of the order' tells nobody anything. Say what is included and what is left out.
• Amounts with no basis. Always state whether a money field includes tax, discounts, delivery charges or refunds.
• Calculated fields with no timing. Say when the value is first set, when it changes and what it holds before then.
• Mandatory with no default. If a field cannot be empty, say what it contains before the business event that fills it.
• Documenting every column in the database. Stick to fields in scope. A huge dictionary nobody reads helps no one.
• No named owner. Without a business owner, definitions are guesses, and guesses get built.
• Writing it once and abandoning it. An outdated dictionary is worse than none, because people still trust it.
• Copying real customer data as examples. Use made-up sample values and mask personal data.

Data dictionary review checklist

Run through this list before you share the dictionary for review or sign-off.

Frequently asked questions