What is a data dictionary, and how to build one for analytics

A data dictionary is a structured reference that defines what every field, table, and metric in your data means: its name, type, source, definition, allowed values, and who owns it. It records the answer to the question every analytics team gets ten times a week, “what does this column actually mean?”, so people don’t have to guess.

If you have ever seen two dashboards report different numbers for “active users,” or watched a new hire spend a week reverse-engineering what status = 3 means, a data dictionary fills that gap. This guide covers what belongs in one, how to build it without starting a documentation project that goes stale, and how it differs from a data catalog and a semantic layer.

What is a data dictionary?

A data dictionary describes the contents of a dataset at the level of individual fields. For each column it records the technical facts (name, data type, nullability), the business meaning (what the field represents in plain language), and the governance facts (who owns it, where it comes from, whether it contains sensitive data).

Its main job is to document meaning. A database schema already tells you that orders.amount is a numeric column. A data dictionary tells you that amount is the gross order total in cents before refunds and excluding tax, sourced from Stripe, and that you should use net_amount for revenue reporting. That one sentence keeps people from building revenue reports on the wrong column.

Historically a data dictionary was a static document. Today it can live anywhere your team will read it: a table in your warehouse, a dbt schema file, a wiki page, a column-description field inside your BI tool, or a dedicated catalog. The format matters less than whether it is correct and close to where people use the data.

Data dictionary vs data catalog vs semantic layer

People often use these three terms interchangeably, though each sits at a different scope and solves a different problem.

They layer on top of each other. A small team starts with a data dictionary. As the number of sources grows, a data catalog automates discovery and lineage across them. A semantic layer goes one step further and turns the definitions into computed, reusable metrics so that “revenue” means the same thing in every chart. A data dictionary is the foundation the other two assume already exists.

What should a data dictionary include?

A useful data dictionary records the following for each field. You do not need every attribute for every column, but the first five are close to mandatory for any field that shows up in reporting.

The two most important attributes are definition and notes. A good definition prevents the wrong column from being used. A good caveat (“this excludes refunds”) prevents the right column from being used the wrong way. If you only have time to write two fields per column, write those.

Beyond individual columns, a complete dictionary also documents the table itself: its grain (what one row represents), its primary key, its update cadence, and how it relates to other tables. Grain is the most common source of double-counted metrics, so record it explicitly. For more on why grain matters, see how to model data for BI.

The three layers of a useful data dictionary

Most data dictionaries fail because they try to be a complete technical manual. A more durable approach is to treat the dictionary as three layers and decide which layers each field needs.

1. The physical layer. Column names, types, nullability, keys. This layer can be generated automatically from the database schema. Do not hand-write it. Anything a query against information_schema can produce should be produced that way.

2. The business layer. Definitions, units, caveats, and the mapping from technical names to business concepts (“mrr_cents is monthly recurring revenue”). This is the layer where people add value and the part worth protecting. It cannot be auto-generated reliably, because the meaning lives in people’s heads rather than in the schema.

3. The governance layer. Ownership, sensitivity classification, source, and freshness. This layer matters most for anything that touches customer data, finance, or compliance. It connects directly to access control: if a field is flagged as PII here, your data governance rules should restrict who can see it.

Automate layer one, invest human effort in layer two, and treat layer three as a governance requirement rather than nice-to-have documentation. Teams that try to hand-maintain all three layers usually end up with a stale dictionary people stop trusting.

How to build a data dictionary

Skip the documentation sprint and build the dictionary incrementally, starting with the data people query.

1. Start with reporting tables, not everything. Inventory the tables and views that feed your dashboards and recurring reports. These are the fields where a wrong definition causes real damage. Ignore the long tail of internal tables that rarely get queried.
2. Auto-extract the physical layer. Pull column names, types, and keys from information_schema (or your warehouse’s equivalent) into a table or spreadsheet. This gives you the skeleton in minutes.
3. Write definitions for the fields that appear in reports. Go column by column through the reporting tables and add a one-sentence definition plus any caveats. Prioritize fields that have ever caused confusion or a “which number is right?” conversation.
4. Assign an owner to each table. Ownership keeps a dictionary current, because unowned definitions don’t get updated. Owners are usually the team that produces the data, not the team that consumes it.
5. Flag sensitive fields. Mark anything containing personal, financial, or otherwise restricted data. This list should match the fields your masking and access-control policies cover.
6. Link the dictionary to where data is used. The definition helps most at the moment someone is about to query or chart the field. Putting it next to the column in your BI tool or warehouse beats burying it in a separate wiki.

A realistic first pass covers the 50 to 150 fields that appear in dashboards. That takes a few hours and captures most of the value.

Where should a data dictionary live?

The best location is wherever the people who need it already are. A dictionary that requires opening a separate tool tends to get ignored.

• In the warehouse / dbt. If you use dbt, column and table descriptions live in schema.yml files alongside the models, and they are version-controlled with the code. This keeps definitions next to the transformations that produce them. It is the strongest option for teams that already model in dbt. See where to define business metrics for how this connects to metric definitions.
• In the BI tool. Many modern BI tools let you attach descriptions to tables and columns so the definition shows up where someone is building a chart. It is the most effective place for the business layer, because it reaches non-technical users who never open dbt or the warehouse.
• In a data catalog. Once you have many sources and teams, a catalog automates ingestion and lineage and becomes the central index. It builds on good definitions and does not replace them.
• In a document or spreadsheet. This works fine for a small team, and a shared sheet with the attributes above beats having no dictionary. It does drift from reality faster than schema-embedded options.

The approach that scales is to generate the physical layer automatically, keep the business and governance layers as close to the data as your tooling allows, and avoid maintaining the same definition in three different places.

How to keep a data dictionary from going stale

A stale data dictionary does more harm than having none, because people trust it and get burned. Three habits keep it current.

Tie definitions to schema changes. When someone adds, renames, or repurposes a column, updating the definition should be part of that change, not a follow-up task. In a dbt workflow this is natural, because the description sits in the same file as the model. Treating documentation as part of the change is the same discipline as versioning your BI as code.

Make ownership real. Every reporting table should have a named owner who is expected to keep its definitions accurate. A short quarterly review where owners confirm or fix their definitions is usually enough.

Capture caveats when they are discovered. The most useful entries are written the moment someone gets burned. When an analyst discovers that a field excludes refunds or resets at month end, that caveat should land in the dictionary immediately, while the details are fresh.

Why data dictionaries matter more with AI-assisted BI

AI-assisted analytics raises the stakes on definitions. When a person writes a natural-language question and a tool generates SQL, the quality of the answer depends heavily on whether the model knows what the columns mean.

An AI assistant pointed at a schema with cryptic names like flg_2 and dt_x will guess, and guesses produce confidently wrong numbers. The same assistant pointed at columns annotated with clear definitions (“net_mrr is monthly recurring revenue after refunds, in cents”) can map a question like “what was net MRR last month?” to the correct field on the first try. Definitions now serve AI systems too, as context they read to generate correct queries.

This is one reason AI-native BI tools make field and metric descriptions a core part of the product. Basedash, for example, surfaces table and column context where analysts and AI both work, so that generated SQL and human queries draw on the same definitions. With a good data dictionary, an AI assistant reflects your team’s real definitions instead of inventing its own.

When you need a data dictionary, and when you do not

You need a data dictionary when:

• More than one person writes queries or builds dashboards, and they need to agree on what fields mean.
• The same metric (“active users,” “revenue,” “churn”) shows up in multiple reports and has to match.
• Column names are cryptic, encoded, or inherited from a system few people still understand.
• You handle sensitive data and need to track which fields are restricted.
• You use AI-assisted querying and want generated SQL to use the right columns.

You can skip a formal one when:

• A single person owns all the data and holds the definitions in their head, and the team is small enough that this is not a risk.
• The schema is small, self-explanatory, and stable.
• You are prototyping and the data is throwaway.

Even then, the moment a second person asks “what does this column mean?”, you need one. The cheapest version is a shared document with the attributes from the table above, and the most durable version lives next to the data itself.

FAQ

What is the difference between a data dictionary and a data catalog? A data dictionary defines the fields, types, and meanings within one dataset or database. A data catalog is a platform that automatically discovers and indexes metadata across your entire data estate, adding lineage, usage stats, and search. A dictionary describes one source in depth, while a catalog indexes many. Small teams start with a dictionary and add a catalog when the number of sources grows.

Is a data dictionary the same as a semantic layer? No. A data dictionary documents what fields mean. A semantic layer goes further and computes consistent metrics from raw tables, so “revenue” resolves to the same calculation everywhere. The dictionary is documentation and the semantic layer is executable definitions, so the two complement each other.

Who should own the data dictionary? Ownership should sit with the team that produces each dataset, not the team that consumes it. Producers know the source and the caveats. A central data or analytics lead can own the structure and process, but per-field definitions should map to the people accountable for that data.

How do I create a data dictionary for a PostgreSQL or MySQL database? Start by querying information_schema.columns to extract column names, types, and nullability into a table or spreadsheet. That gives you the physical layer automatically. Then add one-sentence definitions, owners, and caveats for the fields that appear in reports. If you use dbt, store these in schema.yml so they stay version-controlled with your models.

How often should a data dictionary be updated? Update definitions whenever the schema changes, as part of the change itself rather than as a separate task. Beyond that, a short quarterly review where data owners confirm their definitions is usually sufficient. The fields most likely to drift are calculated metrics and enums, so review those first.