Every data team has a column like status. It holds five values. Nobody wrote them down. Someone filters on 'Active' instead of 'active', the dashboard shows zero, and an afternoon disappears. A data dictionary prevents that, and in 2026 it has a new and very literal reader: the AI agent writing SQL against your warehouse.

This guide covers what a data dictionary is, a complete example you can copy, a template, how to build one step by step (including SQL that generates a first draft for you), how it differs from a business glossary and a data catalog, and why it has become the most important context an AI agent can have.

What Is a Data Dictionary?

A data dictionary is a structured reference that documents every table and column in a database or dataset: its name, data type, meaning, allowed values, keys, source, and owner. It tells people, and increasingly AI agents, what each field contains and how to use it correctly.

The classic definition, from the IBM Dictionary of Computing via Wikipedia, calls it a “centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.”

Other Names for a Data Dictionary

You’ll also see it called a metadata repository, a codebook (common in research, as Harvard Medical School’s data management guide notes), or, inside a database, the system catalog. A data catalog is related but not the same thing; more on that below.

Active vs Passive Data Dictionaries

An active data dictionary is maintained by the database itself and updates automatically when the schema changes. Oracle’s data dictionary views and PostgreSQL’s system catalogs are examples. A passive data dictionary is maintained separately, in a spreadsheet, a wiki, or a documentation tool, and has to be updated by hand. Most teams need both: the active one for structure, a passive one for meaning.

Data Dictionary Example

Here’s a complete data dictionary entry for a contracts table. The values are illustrative.

Table: contracts · One row per signed customer contract · Source: Salesforce → Fivetran → analytics.contracts · Owner: RevOps · Refreshed hourly

Three things make this entry useful rather than decorative. The allowed values are listed exactly, including case. The description of arr_usd includes units and an exclusion. And is_test_account carries a business rule that anyone calculating revenue needs to know.

What’s in a Data Dictionary? (Components)

Drawing on USGS guidance, OSF’s how-to, and common practice, a complete data dictionary covers:

• Name and readable label: arr_usd → “ARR (USD).”

• Data type, size, and format: NUMBER(12,2), DATE in YYYY-MM-DD.

• Description: What the field means, in one sentence.

• Units: Dollars, minutes, percentage points.

• Allowed values or range: The domain for categorical fields; min and max for numbers.

• Nullability and missing-value codes: What a null or 999 means.

• Keys and relationships: Primary keys, foreign keys, and the table each references.

• Source and lineage: Where the field comes from and how it’s derived.

• Owner or steward: Who to ask, and who approves changes.

• Refresh frequency: How current the data is.

• Business rules: Exclusions, validations, and calculation notes.

• Sensitivity: PII or confidential flags.

If you only do five, do these: name, type, description, allowed values, and owner.

Data Dictionary Template

Copy these columns into a spreadsheet to start:

The glossary_term column links a field to the business glossary. For example, arr_usd links to the glossary entry “ARR.” That one link connects technical documentation to business meaning. When those business terms are metrics, the calculation itself belongs in a metrics layer.

How to Create a Data Dictionary

1. Scope it. Start with the 10–20 tables behind your most-asked questions, not the whole warehouse.

2. Auto-extract the structure. Pull names, types, nullability, and keys from the system catalog (SQL below).

3. Write descriptions. One sentence each, with units, grain, and exclusions. Don’t restate the column name.

4. Document allowed values for low-cardinality categorical columns like status, region, and plan tier.

5. Declare keys and relationships, including which table is authoritative when several look alike.

6. Assign an owner, source, and refresh cadence to every table.

7. Link fields to glossary terms so technical and business definitions stay connected.

8. Store it where it’s used. Push descriptions into the warehouse as comments, and into the tools and agents that read them.

9. Keep it fresh. Review on every schema change and refresh allowed-value lists on a schedule.

In Excel or Google Sheets vs in Your Warehouse

A spreadsheet is the fastest way to start and the easiest way for business owners to contribute. Its weakness is drift: the moment someone adds a column, the sheet is out of date. Storing descriptions in the warehouse itself, or generating them from code, keeps the dictionary next to the data and lets every tool read it.

Data Dictionary in SQL: INFORMATION_SCHEMA and System Catalogs

Every relational database maintains an active data dictionary. Oracle describes its own as “a read-only set of tables that provides administrative metadata about the database,” exposed through USER_, ALL_, and DBA_ views. Most other databases expose the SQL-standard INFORMATION_SCHEMA, which PostgreSQL notes is portable across databases, unlike vendor-specific system catalogs.

You can generate a first draft of your data dictionary in one query. In Snowflake, where INFORMATION_SCHEMA.COLUMNS includes column comments:

SELECT table_name, column_name, data_type, is_nullable, comment AS description
FROM information_schema.columns
WHERE table_schema = 'ANALYTICS'
ORDER BY table_name,

SELECT table_name, column_name, data_type, is_nullable, comment AS description
FROM information_schema.columns
WHERE table_schema = 'ANALYTICS'
ORDER BY table_name,

SELECT table_name, column_name, data_type, is_nullable, comment AS description
FROM information_schema.columns
WHERE table_schema = 'ANALYTICS'
ORDER BY table_name,

SELECT table_name, column_name, data_type, is_nullable, comment AS description
FROM information_schema.columns
WHERE table_schema = 'ANALYTICS'
ORDER BY table_name,

In PostgreSQL, add descriptions with COMMENT ON and read them back with col_description:

COMMENT ON COLUMN contracts.arr_usd IS
  'Annual Recurring Revenue in USD, MRR x 12. Excludes one-time fees.';

SELECT c.relname AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type,
       col_description(a.attrelid, a.attnum) AS description
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY c.relname,

COMMENT ON COLUMN contracts.arr_usd IS
  'Annual Recurring Revenue in USD, MRR x 12. Excludes one-time fees.';

SELECT c.relname AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type,
       col_description(a.attrelid, a.attnum) AS description
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY c.relname,

COMMENT ON COLUMN contracts.arr_usd IS
  'Annual Recurring Revenue in USD, MRR x 12. Excludes one-time fees.';

SELECT c.relname AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type,
       col_description(a.attrelid, a.attnum) AS description
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY c.relname,

COMMENT ON COLUMN contracts.arr_usd IS
  'Annual Recurring Revenue in USD, MRR x 12. Excludes one-time fees.';

SELECT c.relname AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type,
       col_description(a.attrelid, a.attnum) AS description
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY c.relname,

If you use dbt, the persist_docs config writes your YAML descriptions into warehouse comments automatically, so documentation written once in code reaches every tool that reads the catalog.

The catch: the system catalog gives you names, types, and keys for free. It does not give you meaning, allowed values, ownership, or caveats. That’s the half you have to write, and it’s exactly the half an AI agent is missing.

Data Dictionary vs Business Glossary vs Data Catalog

What Is a Business Glossary?

A business glossary is, in Collibra’s definition, “a collection of data related terms described in clear language that everyone in an organization can understand.” Each entry typically has a term, a definition, an owner, an approval status, synonyms, and links to the data assets that implement it. Alation’s comparison gives a good example of why it matters: “revenue” can mean recognized revenue to finance, booked ARR to sales, and influenced pipeline to marketing. A glossary that also captures how terms relate to each other becomes a data ontology.

Do You Need All Three?

Not on day one. Start with a data dictionary for the tables people query most, and a short glossary for the 10–20 terms people argue about. Link them. A catalog becomes worth it when you have more data assets than anyone can keep in their head. And once metric definitions need to be exact across every tool, that’s the job of a semantic layer (see our comparison of semantic layer tools).

Why AI Agents Need a Data Dictionary

Data dictionaries were written for humans who could ask a colleague when something was unclear. AI agents can’t. They read every word, never ask a follow-up, and fail silently on a missing allowed value.

What an Agent Can’t Infer From a Schema

• Meaning: Is amount gross or net? Pre-tax? In cents?

• Allowed values: Without the list, an agent writes WHERE status = 'Active' and gets zero rows, or a plausible-looking wrong answer.

• The authoritative table: When contracts and contracts_v2 both exist, which one is current?

• Exclusions: Without the is_test_account rule, ARR is quietly overstated.

• Join paths: Without declared foreign keys, an agent may join on similarly named columns.

The Evidence

The research is consistent about what helps:

• Descriptions help, modestly on their own. An Alibaba study on the BIRD benchmark found accuracy rose from 36.25% with no descriptions to 37.18% with generated ones and 38.59% with manual ones. It also found that descriptions over about 20 words hurt. A 2024 study found generated column descriptions help most when column names are uninformative.

• Allowed values help more. In the Stanford CHESS system, removing value and description retrieval dropped accuracy from 64.62% to 59.86%.

• The full picture helps most. Snowflake found that improving descriptions, relationships, and instructions in a semantic model lifted accuracy from 57% to 78% across four BIRD datasets. That’s a vendor test, but the direction matches every study above.

From Data Dictionary to Agent Context

The same status row from the example above shows up in AI tools in nearly identical form. In a Snowflake semantic view, it’s a dimension with a description and an enumerated value list:

dimensions:
-name: status
expr: status
data_type: VARCHAR
description:"Lifecycle stage of the contract"
is_enum:true
sample_values:["draft","active","paused","cancelled","expired"

dimensions:
-name: status
expr: status
data_type: VARCHAR
description:"Lifecycle stage of the contract"
is_enum:true
sample_values:["draft","active","paused","cancelled","expired"

dimensions:
-name: status
expr: status
data_type: VARCHAR
description:"Lifecycle stage of the contract"
is_enum:true
sample_values:["draft","active","paused","cancelled","expired"

dimensions:
-name: status
expr: status
data_type: VARCHAR
description:"Lifecycle stage of the contract"
is_enum:true
sample_values:["draft","active","paused","cancelled","expired"

In a dbt project (the same one that powers the dbt Semantic Layer), the same knowledge is a description plus a test that enforces the allowed values:

columns:
-name: status
description:"Lifecycle stage of the contract"
data_tests:
-accepted_values:
arguments:
values:['draft','active','paused','cancelled','expired'

columns:
-name: status
description:"Lifecycle stage of the contract"
data_tests:
-accepted_values:
arguments:
values:['draft','active','paused','cancelled','expired'

columns:
-name: status
description:"Lifecycle stage of the contract"
data_tests:
-accepted_values:
arguments:
values:['draft','active','paused','cancelled','expired'

columns:
-name: status
description:"Lifecycle stage of the contract"
data_tests:
-accepted_values:
arguments:
values:['draft','active','paused','cancelled','expired'

The same pattern shows up elsewhere: Databricks metric views carry comments, display names, and synonyms on each field, and the Cube semantic layer adds descriptions and AI-only context to every measure and dimension.

The lesson: a data dictionary written for agents needs curated allowed values for categorical columns (not raw sample rows), short descriptions, and freshness. A value list that’s six months stale is worse than none. The dictionary is the foundation of an agent’s context layer, not the whole of it.

Data Dictionary Best Practices

• Keep descriptions under 20 words. Include units, grain, and exclusions.

• Don’t restate the column name. “account_name: the name of the account” adds nothing.

• Document what’s excluded, not just what’s included.

• Give every table one owner who approves changes.

• Version it like code and review changes in pull requests.

• Refresh allowed-value lists automatically instead of by hand.

• Review AI-generated descriptions before publishing them.

• Test it with real questions. If an analyst or agent still gets a question wrong, the dictionary has a gap.

No Data Dictionary Yet? Start With Your Warehouse

You don’t need a finished dictionary, or a semantic layer, before an AI agent can be useful. Upsolve Data Models is a data dictionary built for agents:

• Register tables with descriptions, types, and primary and foreign keys, directly on your warehouse.

• Mark columns as selectable, and Upsolve pre-caches their values nightly or on your schedule. The agent knows your five contract statuses and four payment terms before it writes a WHERE clause.

• Encode glossary definitions like “ARR excludes usage overages” in a versioned system prompt with drafts and rollback.

Start with the tables behind your most-asked questions, and add definitions as real questions expose gaps. If you already document models in dbt, bring them in.

Frequently Asked Questions

What is a data dictionary with an example?

A data dictionary documents each column in a dataset. For example, a status column in a contracts table would be listed as VARCHAR, described as “lifecycle stage of the contract,” with allowed values draft, active, paused, cancelled, and expired, not nullable, sourced from Salesforce, and owned by RevOps.

What is another name for a data dictionary?

A data dictionary is also called a metadata repository, a codebook (especially in research), or, inside a database management system, a system catalog. A data catalog is related but broader: it inventories data assets across the whole organization.

What is a data dictionary in SQL?

In SQL databases, the data dictionary is the set of system tables and views that describe the database’s own structure, such as INFORMATION_SCHEMA or Oracle’s USER_, ALL_, and DBA_ views. You can query it to list tables, columns, data types, keys, and comments.

What is the difference between a data dictionary and a business glossary?

A data dictionary documents technical fields: columns, types, allowed values, and keys. A business glossary defines business terms in plain language, such as “active customer” or “ARR,” for everyone in the organization. Good practice links each glossary term to the columns that implement it.

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

A data dictionary describes the columns of specific tables or datasets. A data catalog is an organization-wide inventory of data assets that adds search, ownership, lineage, usage, and trust signals, and it often includes both data dictionaries and a business glossary.

What is a data dictionary in healthcare?

In healthcare and clinical research, a data dictionary defines every variable collected in a study or registry, including its type, coding, and validation rules. In REDCap, for example, the data dictionary is a CSV that defines each field, while the codebook is the human-readable view.

Do AI agents need a data dictionary?

Yes. AI agents writing SQL can’t ask what a column means or which values are valid. Descriptions, allowed values, keys, and exclusion rules measurably improve text-to-SQL accuracy, and vendors such as Databricks and Snowflake now treat column descriptions as critical inputs for their AI assistants.

Sources

1. Databricks Docs: Genie best practices. https://docs.databricks.com/aws/en/genie/best-practices

2. Gartner: Lack of AI-Ready Data Puts AI Projects at Risk (Feb 26, 2025). https://www.gartner.com/en/newsroom/press-releases/2025-02-26-lack-of-ai-ready-data-puts-ai-projects-at-risk

3. Wikipedia: Data dictionary. https://en.wikipedia.org/wiki/Data_dictionary

4. Harvard Medical School: Data Dictionary. https://datamanagement.hms.harvard.edu/collect-analyze/documentation-metadata/data-dictionary

5. U.S. Geological Survey: Data Dictionaries. https://www.usgs.gov/data-management/data-dictionaries

6. OSF: How to Make a Data Dictionary. https://help.osf.io/article/217-how-to-make-a-data-dictionary

7. Oracle: Data Dictionary and Dynamic Performance Views. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-dictionary-and-dynamic-performance-views.html

8. PostgreSQL: The Information Schema. https://www.postgresql.org/docs/current/information-schema.html

9. PostgreSQL: COMMENT. https://www.postgresql.org/docs/current/sql-comment.html

10. Snowflake Docs: INFORMATION_SCHEMA.COLUMNS. https://docs.snowflake.com/en/sql-reference/info-schema/columns

11. dbt Docs: persist_docs. https://docs.getdbt.com/reference/resource-configs/persist_docs

12. Collibra: What Is a Business Glossary? https://www.collibra.com/blog/what-is-a-business-glossary

13. Alation: Data Dictionary vs. Business Glossary (Jun 2026). https://www.alation.com/blog/data-dictionary-vs-business-glossary/

14. Gao & Luo (arXiv, 2025): Column descriptions and text-to-SQL accuracy on BIRD. https://arxiv.org/html/2502.20657v1

15. Wretblad et al. (arXiv, 2024): Synthetic column descriptions and text-to-SQL performance. https://arxiv.org/abs/2408.04691

16. Talaei et al. (arXiv, 2024): CHESS: Contextual Harnessing for Efficient SQL Synthesis. https://arxiv.org/html/2405.16755v3

17. Snowflake Engineering: Agentic semantic model improvement for text-to-SQL (Mar 2025). https://www.snowflake.com/en/engineering-blog/agentic-semantic-model-text-to-sql/

18. Upsolve AI: Data Models. https://upsolve.ai/features/data-models