Blog
data dictionary templatedata dictionary exampledata dictionary AI prompt

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.

Portrait of Luis Chavez-Mattos
Edited by Luis Chavez-Mattos, Director, Product··RSS

For Data & Analytics

A neatly organized data dictionary table with rows of column names, data types and description fields, shown as a clean template on a plain background.

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.

FieldWhat to put hereExample
Table nameExact name as it appears in the databasefct_orders
Schema / databaseWhere it livesanalytics.sales
Business descriptionOne or two sentences a non-technical person understandsOne row per customer order, including cancelled orders.
GrainWhat one row representsOne order
Primary keyColumn or columns that make a row uniqueorder_id
Data ownerThe accountable business person or teamHead of Sales Operations
Data stewardThe person who answers questions and approves changesName or team alias
Source systemWhere the data originatesOrder management system
Upstream tablesWhat feeds itstg_orders, stg_payments
Refresh frequencyHow often it updates, and when it last didDaily by 06:00 UTC
Time zoneFor any timestamps in the tableUTC
History handlingOverwritten, appended or snapshottedAppended, one row per order
SensitivityPublic, internal, confidential, restrictedConfidential
Contains personal data?Yes or no, and which kindYes (customer ID, shipping postcode)
RetentionHow long rows are kept7 years
Known issuesGaps, quirks, late-arriving dataOrders before 2019 lack channel
Common usesReports or models that depend on itWeekly revenue report
StatusDraft, confirmed, deprecatedDraft
Last reviewedDate and reviewerDate, initials

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

FieldWhat to put here
Table nameWhich table the column belongs to
Column nameExact name
Data typeAs defined in the database (string, integer, timestamp, decimal)
DescriptionWhat the column means in business terms, not a restatement of the name
Example valuesTwo or three realistic, non-sensitive values
Allowed valuesThe full list for coded or categorical columns, with the meaning of each
Unit / formatCurrency, percent, timezone, date format, decimal places
Nullable?Yes or no, and what a null means
KeyPrimary key, foreign key (and to what), or none
Calculation / business ruleHow a derived value is computed, in words
Source fieldThe upstream column or system field it comes from
SensitivitySame scale as the table sheet; flag personal data separately
StatusDraft, confirmed, deprecated
Steward notesOpen 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

FieldEntry
Business descriptionOne row per customer order placed through the website or the mobile app, including orders later cancelled or fully refunded.
GrainOne order
Primary keyorder_id
Source systemOrder management system
Upstream tablesstg_orders, stg_payments, stg_refunds
Refresh frequencyDaily by 06:00 UTC
Contains personal data?Yes, customer_id links to a person
SensitivityConfidential
StatusDraft, pending steward review

Column entries (excerpt)

ColumnTypeDescriptionAllowed values / formatNull meaningRuleConfidenceQuestion for steward
order_idstringUnique identifier assigned when the customer completes checkout.Format ORD- plus digitsNever nullNoneHighNone
customer_idstringIdentifier of the customer who placed the order. Links to the customer table.Foreign key to dim_customersNull for guest checkoutsNoneHighNone
order_statusstringCurrent stage of the order in fulfilment.placed, shipped, delivered, cancelled (may be incomplete)Never nullTaken from the latest status eventMediumAre there other statuses, such as returned?
order_totaldecimalAmount charged to the customer for the order.Currency per currency_code, 2 decimalsNull if payment failedItem subtotal plus shipping plus tax minus discountsMediumUNCONFIRMED: is tax included in all regions?
placed_attimestampWhen the customer completed checkout.UTCNever nullNoneHighNone
channelstringWhere the order was placed.web, appNull for orders before 2019NoneLowUNCONFIRMED: 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 wellAI gets this wrong or cannot know
Turning cust_acq_dt into a readable name and plain-English descriptionYour company's definition of "active," "churned," or "qualified"
Describing a calculation visible in the SQLCalculations done upstream in code it was not shown
Spotting likely personal data columnsLegal classification of data, which your privacy team decides
Keeping tone and format consistent across hundreds of columnsWhether a coded value list is complete
Drafting steward questions from gapsUnits, currencies and time zones when they are not in the inputs
Summarizing what a table is for from its columnsWhy 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, 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

5 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.

How we source, write, and correct our articles. Editorial standards

Related articles

Straight to your inbox.
New pieces as they publish. No noise.