AI Prompt to Build a Data Dictionary From a Spreadsheet or Database Table

0 views Published Nov 9, 2026

This data dictionary prompt asks ChatGPT, Claude, or Gemini to turn a raw spreadsheet or database table into a clear reference document: every column named, typed, described, and flagged for quirks like nulls or inconsistent formats. It's built for analysts, data engineers, and anyone handing a dataset off to someone else who shouldn't have to guess what "flag_2" means.

Instead of just describing a table in prose, the prompt pushes the model to infer the likely data type, note which columns look like keys or foreign keys, call out columns with mixed formats or suspicious values, and produce the result as a clean, consistent table rather than scattered notes. That structure is what makes a data dictionary actually usable by the next person who opens the file.

Because the output needs to stay in a strict, repeatable table format across runs, it helps to lock down that structure in the prompt itself. Running the template through Cuelara's Prompt Formatter first can convert the loose instructions below into a stricter Markdown or JSON schema the model follows exactly, every time.

Prompt template

prompt-template

293 tokens

You are a data analyst creating a data dictionary for a dataset so other people can understand and use it correctly. Context: - Source: [e.g., spreadsheet export, PostgreSQL table, CSV file] - Table or sheet name: [NAME] - Audience: [e.g., BI analysts, a new engineer, external partner] Data sample (paste column headers plus 10-20 real rows below): [PASTE SAMPLE DATA HERE] Task: For each column in the sample, produce: 1. Column name as it appears in the source. 2. Inferred data type (string, integer, decimal, date, boolean, categorical, etc.). 3. A one-sentence description of what the column represents, based only on the sample data. 4. Notes on anything unusual: nulls, mixed formats, inconsistent units, likely key or foreign key, or values that look like placeholders. 5. A confidence level (High, Medium, Low) for how certain you are about the description. Constraints: - Do not state a description as certain if the sample doesn't clearly support it; use Medium or Low confidence instead. - Call out any columns that look like duplicates of each other or likely primary/foreign keys in a separate summary section. - Keep descriptions to one sentence each. Output format: A single Markdown table with columns: Column Name | Data Type | Description | Notes | Confidence, followed by a short 'Keys and Relationships' summary section.

Want it sharper? Optimize this prompt with Prompt Optimizer, check it with the Prompt Debugger or shorten it with the Token Optimizer.

Example input

example-input

133 tokens

Source: CSV export from the orders database Table or sheet name: orders_export Audience: New data analyst joining the team order_id,cust_id,order_dt,amt,curr,status,ref_code 10234,C-889,2026-03-01,129.99,USD,shipped, 10235,C-104,2026-03-01,45.00,usd,pending,PROMO10 10236,C-889,2026-03-02,,USD,cancelled, 10237,C-220,03/03/2026,310.50,USD,shipped,PROMO10

When to use it

You're handing a dataset off to another team or a new hire and don't want to field column-meaning questions one by one.
You inherited a spreadsheet or database table with no documentation and need to understand it before building on it.
You're preparing data for a BI tool or dashboard and need a reference for what each field actually represents.
You're auditing a table before a migration and need to know which columns are safe to drop versus load-bearing.

Best practices

Paste in actual sample rows, not just column headers, so the model can infer types and flag format inconsistencies instead of guessing.
Ask the model to explicitly mark columns it's unsure about, rather than confidently describing a field it can't actually verify.
Request the output as a single table with fixed columns (name, type, description, notes) so multiple dictionaries stay easy to compare.
Have the model call out likely primary keys, foreign keys, and duplicate-looking columns separately from the main description.

Common mistakes

Giving the model only column names with no sample data, which forces it to guess meaning instead of inferring it from real values.
Letting the model state data types and descriptions as fact when the sample is too small to actually confirm them.
Skipping a note on units and formats (currency, timezone, date format), which is exactly what causes downstream analysis errors.
Treating the generated dictionary as final without a human reviewing the columns the model flagged as uncertain.

FAQs

What's the difference between a data dictionary and a data catalog?

A data dictionary describes the columns and structure of a single table or dataset — names, types, meanings. A data catalog is broader: it indexes many datasets across a company, including where they live and who owns them. This prompt is built for the single-table case.

Can ChatGPT actually infer data types accurately from a small sample?

It can make reasonable inferences from a sample of 10-20 rows, especially for obvious types like dates or integers, but it can't guarantee correctness for the full dataset. That's why the prompt asks for a confidence level on each column instead of presenting every description as certain.

How often should I regenerate a data dictionary for a table that changes often?

Regenerate it whenever the schema changes (columns added, removed, or repurposed) or when you notice the existing dictionary no longer matches what's actually in the data — treat it as documentation that goes stale, not a one-time artifact.

What tool pairs well with this data dictionary prompt?

Prompt Formatter — it's built for exactly this kind of structured-output request, converting the loose instructions into a strict Markdown or JSON template so the model's table format stays identical every time you run it.

Found this prompt useful? Share it.

More in Data Analysis

Data Analysis

AI Prompt to Query and Analyze Supabase Data via Supabase's MCP Server

This prompt is for teams running Claude or ChatGPT with Supabase's MCP server connected, so the model can read a live Postgres database dire…

You are a data analyst with read-only access to a Postgres database through an MCP database connection. Follow these rules for every request:

ROLE
- Act as a careful analyst who verifies before concluding.

CONTEXT
- Database: [DATABASE NAME]
- Relevant tables: [TABLE NAMES, e.g. users, subscriptions, events]
- Business question: [QUESTION, e.g. how many trial users converted to paid in the last 30 days]

CONSTRAINTS
- Only run read-only SELECT queries. Never run INSERT, UPDATE, DELETE, or DDL statements.
- Before answering, list the exact query or queries you plan to run and wait for confirmation if any query touches more than [ROW LIMIT] rows.
- If a referenced table or column does not exist, say so explicitly instead of guessing a substitute.
- Show a sample of up to 5 returned rows alongside any aggregate numbers.

OUTPUT FORMAT
1. Queries run (as SQL)
2. Row counts and sample data
3. Plain-language answer to the business question
4. Any caveats about data quality or missing values

Task: [SPECIFIC ANALYSIS REQUEST]

Data Analysis

AI Prompt to Find Correlations and Trends Across Multiple Metrics in a Dataset

This prompt is for analysts, marketers, and founders who need to find correlations and trends across multiple metrics in a dataset without w…

Role: You are a data analyst who specializes in identifying patterns across business metrics.

Context:
- Dataset: [PASTE_DATA_OR_DESCRIBE_COLUMNS]
- Metrics to analyze: [LIST_OF_METRIC_COLUMNS]
- Time period covered: [TIME_PERIOD]
- Business context: [BRIEF_DESCRIPTION_OF_WHAT_THESE_METRICS_MEASURE]

Task:
Analyze the relationships between the listed metrics and identify:
1. Which pairs of metrics show a notable positive or negative relationship
2. The approximate strength of each relationship (strong, moderate, weak)
3. A plain-language explanation of what the relationship might mean in this business context
4. Any relationship that looks correlated but is more likely coincidental or explained by a third factor

Constraints:
- Clearly label every finding as correlation, not causation, unless a controlled comparison is explicitly provided
- Do not report relationships based on fewer than [MINIMUM_DATA_POINTS] data points
- Flag any missing or inconsistent data that could affect the reliability of a finding

Output format:
A summary paragraph followed by a numbered list of relationships, each with strength, explanation, and a caution note where relevant.

Data Analysis

AI Prompt to Standardize Inconsistent Date Formats Across a Dataset

This AI prompt for standardizing date formats helps analysts, ops teams, and anyone who inherits spreadsheets from multiple sources turn a c…

ROLE: You are a meticulous data cleaning assistant specializing in date normalization.

CONTEXT:
I have a dataset with a column of dates in inconsistent formats. The source systems use these locale conventions: [LIST SOURCE FORMATS, e.g., US month/day/year from System A, European day/month/year from System B]. Here is the raw data:
[PASTE DATE VALUES OR ROWS HERE]

TASK:
1. Identify the format of each date value.
2. Convert every date to this exact target format: [TARGET FORMAT, e.g., YYYY-MM-DD].
3. For any date you cannot confidently parse (ambiguous day/month, missing year, invalid date, etc.), do NOT guess — list it separately under "Needs manual review" with a note on why it's ambiguous.

CONSTRAINTS:
- Do not alter values that are already in the target format.
- Treat two-digit years as [YOUR RULE, e.g., 20XX unless clearly 19XX].
- Preserve the original row order.

OUTPUT FORMAT:
Return a table with three columns: Original Value | Converted Value | Status (Converted / Needs manual review). After the table, list the total count of each status.