---
title: "Data Dictionary Template + an AI Prompt to Fill It In"
url: "https://goremy.ai/blog/articles/data-dictionary-template-an-ai-prompt-to-fill-it-in"
author: Luis Chavez-Mattos
editor: Luis Chavez-Mattos
editor_title: Director, Product
published: "2026-11-16T05:40:16.859Z"
updated: "2026-11-16T05:40:16.859Z"
topic: SQL and Data Analysis
description: Copy a data dictionary template for tables and columns, see a worked example, and paste an AI prompt that drafts the descriptions for you to review.
---

# Data Dictionary Template + an AI Prompt to Fill It In

> Copy a data dictionary template for tables and columns, see a worked example, and paste an AI prompt that drafts the descriptions for you to review.

Edited by Luis Chavez-Mattos, Director, Product · Published 2026-11-16

## What is a data dictionary template?

A data dictionary template is a fill-in structure that documents every table and column in a dataset: what it is called, what it contains, its data type, who owns it, how it is calculated, and how sensitive it is. Use one table-level sheet (one row per table) and one column-level sheet (one row per column). The template below has both. AI can draft the first pass of the descriptions from your schema, and a person who knows the business confirms them.

## TL;DR

- **Use two levels**: a table sheet for grain, owner, source and refresh, and a column sheet for type, meaning, allowed values and sensitivity.
- **The hard fields are meaning and business rules**, not names and types. Names and types you can pull straight from the database.
- **AI is good at drafting** plain-English descriptions from column names, types, sample values and the SQL that builds a table. It is bad at knowing what your company means by "active customer."
- **Make the AI flag what it cannot know** instead of guessing. The prompt below forces an "UNCONFIRMED" marker and a question for the steward.
- **Never paste real customer or employee records** into an AI tool. Schema, masked samples and SQL are usually enough.
- **A dictionary only works if it is reviewed and findable.** Add an owner, a status and a last-reviewed date to every entry.

## The data dictionary template (copy this)

Start with the table-level sheet. One row per table or view.

| Field | What to put here | Example |
|---|---|---|
| Table name | Exact name as it appears in the database | `fct_orders` |
| Schema / database | Where it lives | `analytics.sales` |
| Business description | One or two sentences a non-technical person understands | One row per customer order, including cancelled orders. |
| Grain | What one row represents | One order |
| Primary key | Column or columns that make a row unique | `order_id` |
| Data owner | The accountable business person or team | Head of Sales Operations |
| Data steward | The person who answers questions and approves changes | Name or team alias |
| Source system | Where the data originates | Order management system |
| Upstream tables | What feeds it | `stg_orders`, `stg_payments` |
| Refresh frequency | How often it updates, and when it last did | Daily by 06:00 UTC |
| Time zone | For any timestamps in the table | UTC |
| History handling | Overwritten, appended or snapshotted | Appended, one row per order |
| Sensitivity | Public, internal, confidential, restricted | Confidential |
| Contains personal data? | Yes or no, and which kind | Yes (customer ID, shipping postcode) |
| Retention | How long rows are kept | 7 years |
| Known issues | Gaps, quirks, late-arriving data | Orders before 2019 lack `channel` |
| Common uses | Reports or models that depend on it | Weekly revenue report |
| Status | Draft, confirmed, deprecated | Draft |
| Last reviewed | Date and reviewer | Date, initials |

Then the column-level sheet. One row per column.

| Field | What to put here |
|---|---|
| Table name | Which table the column belongs to |
| Column name | Exact name |
| Data type | As defined in the database (string, integer, timestamp, decimal) |
| Description | What the column means in business terms, not a restatement of the name |
| Example values | Two or three realistic, non-sensitive values |
| Allowed values | The full list for coded or categorical columns, with the meaning of each |
| Unit / format | Currency, percent, timezone, date format, decimal places |
| Nullable? | Yes or no, and what a null means |
| Key | Primary key, foreign key (and to what), or none |
| Calculation / business rule | How a derived value is computed, in words |
| Source field | The upstream column or system field it comes from |
| Sensitivity | Same scale as the table sheet; flag personal data separately |
| Status | Draft, confirmed, deprecated |
| Steward notes | Open questions, caveats |

If you want it in a spreadsheet, paste this header row into the first line of a blank sheet or CSV file:

```
table_name,column_name,data_type,description,example_values,allowed_values,unit_or_format,nullable_and_null_meaning,key,calculation_or_rule,source_field,sensitivity,status,steward_notes
```

Two fields do more work than any others: **grain** on the table sheet and **what a null means** on the column sheet. Most wrong numbers in reports come from someone misreading one of those two.

## How do you fill in a data dictionary?

Work in this order. It keeps you from writing descriptions you later have to rewrite.

1. **List the tables that matter first.** Start with the ten or twenty tables behind your most-used reports, not the whole warehouse. Documenting everything at once is how dictionaries stall at 5 percent.
2. **Pull the facts that need no judgment.** Table names, column names, data types, nullability and keys can be exported from the database's information schema, your modeling tool, or a schema file. Do not type these by hand.
3. **Gather context for the meaning.** For each table, collect the SQL or transformation code that builds it, a few rows of masked sample data, and any existing glossary or report definitions. This is what the AI (or a new colleague) needs to describe things accurately.
4. **Draft descriptions.** This is the step AI speeds up. Use the prompt in the next section.
5. **Have the steward review every entry.** Replace guesses, answer the open questions, set the status to Confirmed, and add the review date.
6. **Publish it where people search.** A dictionary in a shared drive nobody opens is not documentation. Put it in the data catalog, the wiki, or alongside the code (many teams keep descriptions in the same files that define their models, so they change together).
7. **Set a review rhythm.** Review quarterly, and whenever a table's logic changes. Add "update the dictionary" to the change checklist.

## What is the AI prompt that fills in a data dictionary?

Paste this into the AI tool your company approves. Replace the bracketed parts. The key design choice is the last block of rules: it tells the AI to mark what it cannot know rather than invent a plausible answer.

```
You are helping a data steward draft a data dictionary entry. You will
write descriptions for a table and its columns for a business audience
that is not technical.

CONTEXT
- Company/domain: [e.g. retail e-commerce, B2B software, healthcare billing]
- Table name: [name]
- Table purpose, if known: [one line, or "unknown"]
- Glossary terms we use: [paste definitions of key terms, or "none"]

INPUTS
1. Schema (column names, types, nullability, keys):
[paste schema or CREATE TABLE statement]

2. SQL or transformation logic that builds this table:
[paste SQL, or "not available"]

3. Masked sample rows (no real names, emails or IDs):
[paste 5-10 rows, or "not available"]

TASK
Produce two outputs.

A) One table-level entry with these fields: business description
(1-2 sentences), grain, primary key, likely source system, upstream
tables, refresh frequency, whether it appears to contain personal data,
and suggested sensitivity level.

B) A markdown table with one row per column and these columns:
column_name | data_type | description | example_values |
allowed_values | unit_or_format | null_meaning | calculation_or_rule |
sensitivity | confidence (High/Medium/Low) | question_for_steward

RULES
- Write descriptions that explain what the column means to the
  business. Never just restate the column name ("order date: the date
  of the order" is not acceptable).
- Base every statement on the inputs above. If the SQL shows how a
  column is calculated, describe the calculation in plain words.
- If you are inferring something not shown in the inputs, write
  "UNCONFIRMED:" at the start of that statement and add a specific
  question in question_for_steward.
- Do not invent allowed values. List only values that appear in the
  samples or SQL, and mark the list "may be incomplete" if so.
- Flag any column that could identify a person (names, emails, phone,
  address, IDs, free-text fields) in the sensitivity column.
- Set confidence to Low whenever the meaning depends on company-
  specific rules you cannot see.
- Do not guess units, currencies or time zones. Ask.
```

If a table has more than 30 or 40 columns, run it in batches of columns, and give the AI the full SQL each time so the batches stay consistent. Long single runs tend to get vaguer toward the end.

## What does a filled-in example look like?

Here is a worked example for an order table in a retail business. The AI was given the schema, the SQL and masked sample rows. The output below is the AI's draft after the steward's edits. Note which parts the steward had to correct.

**Table entry: `fct_orders`**

| Field | Entry |
|---|---|
| Business description | One row per customer order placed through the website or the mobile app, including orders later cancelled or fully refunded. |
| Grain | One order |
| Primary key | `order_id` |
| Source system | Order management system |
| Upstream tables | `stg_orders`, `stg_payments`, `stg_refunds` |
| Refresh frequency | Daily by 06:00 UTC |
| Contains personal data? | Yes, `customer_id` links to a person |
| Sensitivity | Confidential |
| Status | Draft, pending steward review |

**Column entries (excerpt)**

| Column | Type | Description | Allowed values / format | Null meaning | Rule | Confidence | Question for steward |
|---|---|---|---|---|---|---|---|
| `order_id` | string | Unique identifier assigned when the customer completes checkout. | Format `ORD-` plus digits | Never null | None | High | None |
| `customer_id` | string | Identifier of the customer who placed the order. Links to the customer table. | Foreign key to `dim_customers` | Null for guest checkouts | None | High | None |
| `order_status` | string | Current stage of the order in fulfilment. | `placed`, `shipped`, `delivered`, `cancelled` (may be incomplete) | Never null | Taken from the latest status event | Medium | Are there other statuses, such as `returned`? |
| `order_total` | decimal | Amount charged to the customer for the order. | Currency per `currency_code`, 2 decimals | Null if payment failed | Item subtotal plus shipping plus tax minus discounts | Medium | UNCONFIRMED: is tax included in all regions? |
| `placed_at` | timestamp | When the customer completed checkout. | UTC | Never null | None | High | None |
| `channel` | string | Where the order was placed. | `web`, `app` | Null for orders before 2019 | None | Low | UNCONFIRMED: does `app` include tablet? |

What the steward changed after review: the AI wrote that `order_total` "includes tax," which the SQL did not show. The steward confirmed tax is excluded for US orders and included for EU orders, and rewrote the rule. That one correction is the reason a human review step exists. The draft was fluent and wrong.

## How good is AI at writing data dictionary descriptions?

Good at some things, unreliable at others. Knowing the split tells you where to spend your review time.

| AI does this well | AI gets this wrong or cannot know |
|---|---|
| Turning `cust_acq_dt` into a readable name and plain-English description | Your company's definition of "active," "churned," or "qualified" |
| Describing a calculation visible in the SQL | Calculations done upstream in code it was not shown |
| Spotting likely personal data columns | Legal classification of data, which your privacy team decides |
| Keeping tone and format consistent across hundreds of columns | Whether a coded value list is complete |
| Drafting steward questions from gaps | Units, currencies and time zones when they are not in the inputs |
| Summarizing what a table is for from its columns | Why a quirk exists, such as a gap in 2019 |

The most dangerous failure is a description that reads as confident and complete. Plausible text is the thing AI produces best, which is why the prompt asks for confidence ratings and open questions. Treat Low confidence rows and anything marked UNCONFIRMED as the steward's to-do list.

## What should you check before approving an AI-drafted entry?

Use this checklist on every table. It takes a few minutes per table once you are used to it.

- **Grain.** Does "one row per X" match what a query actually returns? Run a count of rows against a count of distinct keys.
- **Descriptions that only restate the name.** "Status: the status of the order" tells nobody anything. Send it back.
- **Coded values.** Compare the allowed values against a `SELECT DISTINCT` on the real column. The AI only saw a sample.
- **Null meaning.** Check whether nulls mean "not applicable," "not yet known," or "data missing." They are different problems.
- **Money, dates and time zones.** Confirm currency, tax treatment, rounding, and whether timestamps are UTC or local.
- **Derived columns.** Read the stated rule next to the real code. If the AI paraphrased, make sure it paraphrased correctly.
- **Sensitivity.** Check every flagged column, and look for ones the AI missed, especially free-text fields that can hold anything.
- **Owner and steward.** Make sure a named person or team is attached. An entry nobody owns goes stale.

## Is it safe to put your data into an AI tool?

Often it is not, so decide before you paste. Your company likely has a policy on which AI tools are approved and what data may go into them. Follow it.

You rarely need real data. Schema definitions, column names, transformation SQL and masked or made-up sample rows are enough for descriptions. Strip or replace names, emails, phone numbers, account numbers and any free-text fields. If the SQL itself contains sensitive logic or credentials, remove those first. If you cannot use an external AI tool at all, a locally hosted or company-provided model can run the same prompt.

## What makes a data dictionary actually get used?

Most dictionaries fail after launch, not at the template stage. A few habits keep yours alive.

- **Keep it next to the work.** If descriptions live in the same place the data models are defined, changing a model prompts a description update in the same review.
- **Make it searchable.** People search by business term ("refund," "churn") rather than table name. Include a short plain-language description and common synonyms.
- **Show status.** A Draft or Deprecated label tells a reader how far to trust an entry.
- **Link it from reports.** A dashboard that points to the definition of its metrics prevents the "why do these two numbers differ" thread.
- **Assign review.** A steward who is accountable by name beats a quarterly reminder to "someone."

## Can you build your own data dictionary app instead of a spreadsheet?

If your spreadsheet has grown to dozens of tabs, or your team keeps asking "who owns this table?", a small internal app can do the job better: a searchable list of tables and columns, a steward assigned to each entry, a Draft/Confirmed status, and a review queue. You can describe that in plain English to [Remy](https://goremy.ai), which builds the full-stack app (database, sign-in, role-based access) and deploys it to a live URL. Stewards could edit entries while everyone else gets read-only search. The code is standard TypeScript in a git repo you own. Remy is a paid product, so a spreadsheet is still the right start for a small dictionary.

## Frequently Asked Questions

### What is the difference between a data dictionary and a data catalog?

A data dictionary describes the structure and meaning of data: tables, columns, types, definitions. A data catalog is a broader tool that adds search, lineage, usage statistics and access requests, and it often includes a dictionary. You can start with a dictionary in a spreadsheet and move it into a catalog later. The same fields carry over.

### What fields should a data dictionary include at minimum?

For each column: name, data type, a business description, allowed values for coded columns, what null means, and sensitivity. For each table: description, grain, owner and refresh frequency. If you can only do two extras, add the owner and a last-reviewed date, because they tell readers whether to trust the entry.

### Can AI write a whole data dictionary on its own?

It can draft one, and the draft will be useful for names, types and calculations visible in the code. It cannot know your business definitions, unwritten exceptions or whether a list of values is complete. Treat the output as a first draft that a data steward confirms, and keep the UNCONFIRMED markers until someone answers them.

### How often should a data dictionary be updated?

Update an entry whenever the table's logic, source or columns change, and review the whole dictionary at least quarterly. Tie the update to your change process so it happens when the code changes, not months later when someone notices the definition is wrong.

### Where should I store the data dictionary?

Store it where the people who use the data already look: your data catalog, your wiki, or in the same repository as your data models. A spreadsheet works for a small set of tables. Whatever you choose, make it searchable and give every entry a named owner.
