Data Dictionary Example and Free Template (Excel, CSV, dbt and SQL)

For the full definition and how a data dictionary differs from a business glossary and a data catalog, see What Is a Data Dictionary? This article is the practical part: what a finished one looks like and how to build and keep it.

Data dictionary example for an e-commerce database

The example documents two tables of a fictional online store: customers, with one row per registered account, and orders, with one row per checkout. They are small enough to read in a minute and still contain everything that makes a dictionary useful: personal data, coded status fields, a foreign key, money and nullable timestamps. The values are invented, and the email domain is example.com, which is reserved for documentation.

A data dictionary usually starts with a table-level summary, so readers know what one row means before they read any column.

customers table

The customers table holds three columns with direct personally identifiable information (PII), so its dictionary is also the first place a privacy review looks.

orders table

In orders, the dictionary settles the questions that most often cause two reports to disagree: whether order_total includes tax, what NULL in shipped_at means, and which status values exist.

Allowed values (code lists)

Coded columns point to a list of allowed values instead of repeating them in every row. Country and currency columns point to the ISO standards instead, since ISO 3166 and ISO 4217 maintain those lists. The example limits currencies to the three the store accepts.

A column can come from a different system than the rest of its table: in the template, customer_status is set in the CRM and payment_method comes from the payment gateway. The full template also records the source system, owner, steward, business glossary term and last review date for every column. The diagram below shows all sixteen attributes for one entry.

What should a data dictionary include?

A data dictionary should include whatever a person needs to use a column correctly without asking anyone. In practice that is four groups of attributes: identity, meaning, technical rules and governance.

The data steward keeps the entry correct day to day; the owner is accountable for the data and approves changes to its definition. If those roles are new to your team, data owner vs data steward vs data custodian explains the split.

What to leave out of a data dictionary

• Statistics that change daily, such as row counts, null rates or min and max values. They are out of date the day after you type them; profiling tools and catalogs compute them on demand.
• Full business definitions. "Active customer" or "net revenue" belong in a business glossary, written once and linked from every column that implements them.
• Transformation logic. How a column is calculated lives in the code (for example a dbt model) and in data lineage. The dictionary describes the result.
• Real data. Example values must be invented or masked, especially for PII columns. A dictionary is shared widely, and a real email address in it is a leak.
• Every column of every system on day one. Start with the tables people actually query and extend from there.

Free data dictionary template for Excel and CSV

The template is an Excel workbook pre-filled with the example above, so you can see a finished entry before replacing it with your own. The workbook has six sheets:

1. How to use: a health check that counts columns without a description, without an owner or steward, with an overdue review, and with direct PII classified below Confidential, plus what to put in each column, the sensitivity and PII scales, and a maintenance routine.
2. Tables: one row per table with its grain, primary key, source system, refresh, retention, owner and steward.
3. Column dictionary: one row per column with all sixteen attributes. Filters are on, Table, Nullable, Sensitivity and PII have drop-down lists, and the cells behind every health-check problem are highlighted.
4. Code lists: the allowed values of every coded column, with the columns that use them.
5. Change log: every change to a definition, with the reason and who approved it.
6. Generate with SQL: the PostgreSQL and Snowflake queries from this article, one line per row, ready to copy.

The example is internally consistent: every example value fits its data type and allowed values, every coded column points to a list on the Code lists sheet, and the foreign key joins. We checked that by loading the dictionary into PostgreSQL 16 as real tables with the documented types, constraints and code-list checks, inserting the example row, and comparing the result with information_schema. The health-check formulas were tested in Excel and against a deliberately broken copy of the example, which they flagged correctly. The CSV contains the Column dictionary sheet only, for importing into another tool.

Excel template with example (.xlsx) Column dictionary (.csv)

How to generate a data dictionary with SQL

Most SQL databases, including PostgreSQL, Snowflake, SQL Server and MySQL, expose their structure through information_schema, a set of standard views that list tables, columns, data types and constraints. Querying it gives you the technical half of the dictionary in seconds, so nobody has to type column names or data types by hand.

PostgreSQL query

PostgreSQL keeps column comments outside information_schema, so the query reads them with the col_description() function. It flags primary keys from the pg_index catalog and adds empty columns for the attributes people fill in later.

SELECT
    c.table_name,
    c.column_name,
    CASE
        WHEN c.character_maximum_length IS NOT NULL
            THEN c.data_type || '(' || c.character_maximum_length || ')'
        WHEN c.data_type = 'numeric' AND c.numeric_precision IS NOT NULL
            THEN c.data_type || '(' || c.numeric_precision || ',' || c.numeric_scale || ')'
        ELSE c.data_type
    END AS data_type,
    c.is_nullable,
    c.column_default,
    EXISTS (
        SELECT 1
        FROM pg_index AS i
        WHERE i.indrelid = format('%I.%I', c.table_schema, c.table_name)::regclass
          AND i.indisprimary
          AND c.ordinal_position::int2 = ANY (i.indkey)
    ) AS is_primary_key,
    col_description(
        format('%I.%I', c.table_schema, c.table_name)::regclass,
        c.ordinal_position::int
    ) AS description,
    NULL AS business_name,
    NULL AS owner
FROM information_schema.columns AS c
WHERE c.table_schema = 'shop'
ORDER BY c.table_name, c.ordinal_position;

We ran this query against PostgreSQL 16 on the example schema, including a table with a dropped column, and it returned all 19 columns with correct types, nullability, keys and comments. Two details matter in practice. information_schema.columns only shows columns the current user has a privilege on, so run it with a role that can see the whole schema. The primary-key flag reads pg_index on purpose: information_schema.table_constraints lists constraints only on tables the role owns or holds a privilege other than SELECT on, so a read-only role would see no primary keys there. We tested that with a SELECT-only role too. And ordinal_position keeps gaps where columns were dropped, which is why it can be passed to col_description() directly.

Snowflake query

In Snowflake, INFORMATION_SCHEMA.COLUMNS includes a COMMENT column, so descriptions come back without a function call. Joining TABLES adds the table comment and filters out views.

SELECT
    c.table_name,
    c.column_name,
    c.data_type,
    c.data_type_alias,
    c.character_maximum_length,
    c.numeric_precision,
    c.numeric_scale,
    c.is_nullable,
    c.column_default,
    c.comment AS description,
    t.comment AS table_description
FROM shop_db.information_schema.columns AS c
JOIN shop_db.information_schema.tables AS t
  ON t.table_schema = c.table_schema
 AND t.table_name = c.table_name
WHERE c.table_schema = 'SHOP'
  AND t.table_type = 'BASE TABLE'
ORDER BY c.table_name, c.ordinal_position;

Snowflake reports its standard type names in DATA_TYPE, so a column created as BIGINT shows as NUMBER. DATA_TYPE_ALIAS keeps the name used in the DDL, but it is NULL for tables created before Snowflake's 2025_07 behavior change bundle and not altered since. Unquoted identifiers are stored in uppercase, hence 'SHOP' in the filter. Like PostgreSQL, the view only lists objects the current role has access to. The query is written against the Snowflake documentation for both views; run it with a role that can see the whole schema.

Export the result to the Column dictionary sheet, and the identity and technical columns are done. What remains is the work only people can do: descriptions, business names, owners, classification and glossary links.

Data dictionary in dbt schema.yml

If your tables are built with dbt, the data dictionary can live next to the code, in the YAML files that describe each dbt model. Descriptions sit beside the columns they describe, change in the same pull request, and appear in the documentation site dbt generates. Here is the orders part of the example:

# models/marts/_shop__models.yml
version: 2

models:
  - name: orders
    description: "Orders placed in the online store. One row per order."
    config:
      meta:
        owner: "Head of E-commerce Operations"
        steward: "Order data steward"
    columns:
      - name: order_id
        description: "Unique identifier of an order, assigned by the OMS at checkout."
        data_type: bigint
        config:
          meta:
            glossary_term: "Order"
        data_tests:
          - unique
          - not_null

      - name: customer_id
        description: "Customer who placed the order."
        data_type: bigint
        config:
          meta:
            pii: indirect
            glossary_term: "Customer"
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('customers')
                field: customer_id

      - name: order_status
        description: "Current fulfillment state of the order."
        data_type: varchar(20)
        config:
          meta:
            glossary_term: "Order Status"
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: ['pending', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded']

      - name: order_total
        description: "Amount charged for the order in currency_code, after discounts, including tax and shipping."
        data_type: numeric(12,2)
        config:
          meta:
            glossary_term: "Order Value"
        data_tests:
          - not_null

Two details in this file are recent (as of November 2026). Column meta goes under config (dbt 1.10, backported to 1.9), and test arguments such as values or to sit under arguments (dbt 1.10.5 and later). On older versions, put them one level up, and before dbt 1.8 the data_tests key was called tests. The data_tests turn the allowed values and keys from the dictionary into dbt tests, so a value outside the code list fails the build instead of reaching a report.

dbt can also write the descriptions into the database as table and column comments with the persist_docs config, which Postgres and Snowflake support. On Snowflake, a column whose name mixes upper and lower case is only persisted if the YAML sets quote: true for it or names it in a single case. The SQL queries above then return the same descriptions, and so does any tool that reads comments from the warehouse.

What dbt YAML does not cover well is the governance half: sensitivity, ownership and review dates live in free-form meta keys that nothing enforces, and business users rarely read YAML. That is where a spreadsheet or a catalog takes over.

How to create a data dictionary step by step

1. Pick the scope. Start with the 10 to 20 tables behind your most used reports or one data product, not the whole warehouse.
2. Generate the technical half. Run the information_schema query and paste the result into the template.
3. Name owners and stewards per table. One accountable owner and one steward per table; columns inherit them unless there is a reason not to.
4. Write descriptions with the people who know the data. One or two sentences per column: meaning, unit, time zone and what NULL means. A 30-minute session per table with the steward beats a month of email.
5. Classify sensitivity and PII. Mark every column that identifies a person, directly or in combination, and agree the scale before you start.
6. Move allowed values into code lists and link each column to its business glossary term where one exists.
7. Publish it where people already look, record the review date, and turn allowed values into automated tests.

How to maintain a data dictionary

A data dictionary stays useful only if updating it is part of changing the data. A dictionary maintained as a separate chore drifts out of date within months, and readers stop trusting it.

Automate what you can. A check that fails when a new column has no description, dbt tests generated from the allowed values, and a catalog that reads the schema on a schedule all remove manual steps that people skip under deadline pressure. Data stewardship covers the human side of the routine.

Common data dictionary mistakes

• Descriptions that repeat the name. "customer_status: the status of the customer" tells the reader nothing. Say which values exist and what each one allows.
• Leaving NULL undefined. For shipped_at, NULL means "not shipped yet". For loyalty_tier, it means "not in the program". Without that sentence, analysts guess.
• Missing units and time zones. Is order_total in dollars or cents, gross or net, local time or UTC? These are the questions that make two reports disagree.
• No owner. An entry nobody owns is never reviewed and never corrected.
• A one-time project. A dictionary written for an audit and never touched again is wrong within a quarter.
• Real personal data in examples, copied from production for convenience.
• Mixing the dictionary with the glossary, so business definitions are copied into many columns and drift apart. Write the definition once and link to it.

Data dictionary in a data catalog

A spreadsheet works for a few dozen tables; beyond that, a data catalog keeps the technical half current automatically. As of November 2026, the Dawiso Snowflake connector reads the core INFORMATION_SCHEMA.COLUMNS attributes from the query above (data type, nullability, default, length and precision) and maps each column COMMENT to a Scanned Description. The dbt connector brings in model column descriptions, data types and meta from dbt Cloud, and the PostgreSQL connector maps column descriptions to the same Scanned Description.

The human half is added on top: owners and stewards, classifications, and relations between business glossary terms and the scanned objects that implement them. For bulk work, Dawiso exports the objects of an application to an Excel template and imports it back; in metadata applications such as a scanned database, only the update mode is supported, so a spreadsheet can enrich scanned columns but never creates or deletes them. The template in this article is a standalone format, not the Dawiso import file, but its columns map to the same kinds of attributes.

The data dictionary is the field-level half of shared understanding. Generate what the database already knows, write down what only people know, give every table an owner, and keep the dictionary where the people who use the data already look.

Sources

• PostgreSQL documentation, information_schema.columns
• PostgreSQL documentation, System Information Functions (col_description)
• Snowflake documentation, COLUMNS view
• Snowflake documentation, TABLES view
• dbt documentation, columns
• dbt documentation, data_tests
• dbt documentation, meta
• dbt documentation, persist_docs
• ISO, ISO 3166 Country Codes and ISO 4217 Currency Codes
• IETF, RFC 2606, Reserved Top Level DNS Names (example.com)
• Dawiso Help, Snowflake
• Dawiso Help, dbt and Prepare for dbt integration
• Dawiso Help, PostgreSQL
• Dawiso Help, Relations
• Dawiso Help, Object import (Excel)
• Dawiso Help, Business Glossary attributes and components