Data dictionary from a table schema and sample rows

You are a data analyst documenting a dataset so other people can use it correctly. Create a data dictionary for the table below.

Table name: [TABLE OR FILE NAME]
What one row represents (if known): [e.g. "one order line", "one user per day", OR "not sure"]
Where the data comes from: [SOURCE SYSTEM, EXPORT, API]
Who will read the dictionary: [e.g. "analysts new to the team", "business users building reports"]

Schema (column names and types):
"""
[PASTE SCHEMA, DESCRIBE OUTPUT OR HEADER ROW]
"""

Sample rows (20-50, with sensitive values masked):
"""
[PASTE SAMPLE]
"""

For each column, give a table with:
- Column name
- Plain-English definition
- Data type and format (units, date format, time zone if visible)
- Allowed values or range observed in the sample
- What a null or empty value seems to mean
- Example value
- Quality warning (mixed formats, suspicious defaults like 1970-01-01 or 0, duplicates)
- Confidence: confirmed by the sample / inferred / guess

Then:
1. State the likely grain of the table and a candidate primary key, and how to verify it.
2. List questions for the data owner, ordered by importance.

Base definitions on the column names and sample only; mark anything inferred.

Example (excerpt):
| Column | Definition | Type/format | Observed values | Null means | Warning | Confidence |
| status | Current state of the order | text | paid, refunded, pending, PAID | not seen | Mixed case: "PAID" and "paid" | confirmed |
| shipped_at | When the order left the warehouse | timestamp, no time zone | 2026-09-01 to 2026-10-20 | not yet shipped (inferred) | Time zone unknown | inferred |
Likely grain: one row per order. Candidate key: order_id (no duplicates in the sample; check with COUNT vs COUNT DISTINCT).
Question 1: is "pending" a payment state or a shipping state?

When to use it

Use this prompt when you receive a new export, inherit a warehouse table without documentation, or need to onboard colleagues to a dataset. It is also a good first step before any analysis, because misunderstanding a single column, such as whether an amount includes tax, can make every later number wrong.

The prompt works best one table at a time. For a whole database, start with the tables people query most often and add the others gradually, keeping each dictionary next to the table definition so it stays up to date when columns change. A dictionary nobody maintains quickly becomes another source of confusion.

How to customize it

• [TABLE NAME] and [SOURCE] help the model recognize common system conventions, such as CRM or ecommerce exports.
• [WHAT ONE ROW REPRESENTS] is the grain. If you are unsure, say so; the prompt asks the model to propose and verify it.
• [READER] changes the vocabulary. Business users need fewer technical details and more examples.
• [SCHEMA] and [SAMPLE] are the evidence. Mask names, emails and payment data before pasting, but keep formats intact.

Why it works

A data dictionary is mostly careful observation, which language models do well when given real rows. The fixed columns ensure that null meaning and quality warnings, the parts people usually skip, are always covered.

The confidence column is the important guardrail. A column called “revenue” might be gross, net or in cents; marking definitions as inferred tells readers which ones still need confirmation. Asking for the grain and a primary key check prevents the classic error of double counting after a join.

Tips and variations

• Send the open questions to the data owner and paste the answers back to produce a final version.
• Ask for the dictionary as CSV or YAML if it will live in a data catalog or in your repository.
• If your assistant can run code on an uploaded file, ask it to compute null rates and distinct counts instead of estimating them from the sample.
• Continue with the CSV data cleaning plan or the KPI definitions prompt.