Key takeaways
- A data dictionary explains meaning and use, not just column names.
- Start with the datasets that support important decisions.
- Record grain, keys, allowed values, and null meaning for every table.
- Give each entry an owner and a review date or it will go stale.
On this page
What a data dictionary is for
A data dictionary is a shared reference that explains what a dataset contains, what each field means, and how the dataset should be used. It is written for the people who query, report, or approve the data, not only for the team that builds it.
It is not a screenshot of the schema. A list of column names with data types answers almost none of the questions people actually have: which date to filter, whether a total is additive, what a blank value means, and whether the table includes deleted records.
Scope it around decisions
Do not start with every table in the warehouse. Start with the datasets behind the reports that matter: revenue, customers, orders, stock, or cash. A dictionary for ten important tables that people use is more valuable than a partial catalogue of two hundred.
For each dataset, write the business question it answers and the decisions it supports. That context helps a new analyst choose the right table and helps an owner decide what to protect when the source changes.
- Name the report or process that depends on the dataset.
- State the refresh frequency and the expected delay.
- List the known limitations and exclusions.
- Identify the person who can confirm the definition.
Document the definition, not the label
A field called revenue can mean several things: gross sales, net of discounts, net of tax, booked, billed, or collected. The dictionary must resolve that ambiguity with a precise definition and, where useful, a formula or source reference.
| Field | What to record | Example |
|---|---|---|
| Name | The physical column name | net_revenue |
| Definition | One precise sentence | Invoiced amount less discounts and credits, excluding tax |
| Type | Data type and format | Numeric, two decimal places |
| Allowed values | Domain or reference list | Zero or positive; null when not invoiced |
| Source | System and table of origin | Billing system, invoice_lines |
| Owner | Person accountable for the definition | Finance data owner |
| Notes | Caveats, exclusions, known issues | Excludes intercompany invoices |
Capture the grain and keys
The grain statement is the most important line in the dictionary. One row per invoice line and one row per invoice produce different totals from the same data. Write the grain in plain language and test it with a uniqueness query.
List the primary key, the foreign keys, and any columns that are unique together. Note whether keys are natural or generated, and whether they remain stable when records are updated. A key that changes is not a reliable join key.
- One row per what?
- Which column or combination is unique?
- Which keys join to other datasets?
- Are deleted records included?
Assign ownership and change control
Every dataset needs an owner who can confirm the definition and approve changes. The owner is not always the person who built the pipeline. It is the person accountable when the meaning or quality of the data is questioned.
Record changes in the dictionary when a field is added, renamed, or redefined. A short change log with the date, the change, and the reason is enough for most teams. Without it, two versions of the same definition circulate and reports disagree.
Keep it current
A dictionary that is wrong is worse than no dictionary, because people trust it. Generate the technical parts from metadata where possible, and review the business definitions on a schedule. Assign a review date and an owner to each entry.
Link the dictionary to the reports and dashboards that use the dataset. When a report breaks or a number is questioned, the link should lead to the definition, the owner, and the last review. That is the practical test of whether the dictionary is useful.
- Generate table and column lists from the database where possible.
- Review high-use datasets at least once a year.
- Record a change note for every definition change.
- Retire entries for datasets that no longer exist.
Frequently asked questions
What is the difference between a data dictionary and a data catalogue?+
A dictionary documents fields and definitions for datasets. A catalogue indexes datasets across an organisation so people can find them. A catalogue often links to dictionaries.
Should the data dictionary be a spreadsheet or a tool?+
A spreadsheet is a reasonable start for a small team. Move to a shared tool when several people need to edit, link, or search entries.
Who should write the definitions?+
The dataset owner writes or approves them, with input from the people who use the data. An analyst can draft the entry, but the owner confirms the meaning.
How detailed should each definition be?+
Detailed enough to settle a real question about a total or a filter. Include the formula, exclusions, and null behaviour where they change the result.
Need help applying this?
Improve your data cleanup & pipelines.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
Data quality dimensions with practical checks
A practical guide to the six data quality dimensions, the checks that test each one, and how to decide which failures should stop a report.
Deduplicating customer records: matching rules
A practical matching process for customer records: normalisation, deterministic rules, fuzzy scoring, review queues, and survivorship.