Data Dictionary And Glossary

Who this is for

• BI Developers who build dashboards and datasets that must be trusted across teams.
• Analytics Engineers documenting models for reuse.
• Data Stewards formalizing definitions to reduce metric confusion.

Prerequisites

• Basic SQL (SELECT, JOIN).
• Familiarity with your data warehouse tables and BI metrics.
• Ability to edit a spreadsheet or shared doc.

Why this matters

In BI work, most rework comes from unclear definitions: different teams use different filters for the same KPI, or columns are misused because their meaning isn’t documented. A data dictionary and business glossary fix this by defining fields and terms once, and making them discoverable. You’ll use them to:

• Design trustworthy dashboards (definitions match stakeholder expectations).
• Onboard new teammates quickly (clear column/metric meaning).
• Prevent metric drift (one place to propose and approve changes).
• Speed up debugging (lineage, owners, and quality rules are known).

Concept explained simply

Data Dictionary = technical facts about data fields (columns): name, data type, format, meaning, allowed values, source, transformations, freshness, owners.

Business Glossary = business terms and metrics: what the term means in plain language, exact formula/filters, scope, synonyms, stakeholders, and examples.

Mental model

Think of the glossary as the dictionary at the front of a book (shared language for readers), and the data dictionary as the detailed appendix (field-level specs for builders). The glossary tells everyone what “Active Customer” means. The data dictionary tells the developer which table and column implement it, the type, constraints, and where it came from.

What to capture

Worked examples

Table: fact_sales
Column: order_total_amount
Friendly name: Order Total
Business meaning: Final charged amount per order after discounts and before tax.
Type: DECIMAL(12,2) USD
Allowed values: >= 0
Source: checkout_service.order_total
Lineage: from raw_orders.total less discount_total; tax excluded in transform step
Granularity: 1 row per order
Refresh: Hourly; last_load_ts column
Quality rules: Non-negative; if currency != 'USD', convert using fx_rates table
Owner: Sales Analytics
Sensitivity: Non-PII

Term: Active Customer
Definition: A customer with at least one completed order in the last 30 calendar days.
Formula: COUNT(DISTINCT customer_id) where order_status = 'completed' and order_date >= current_date - 30
Scope: Global ecommerce only
Freshness: Daily by 07:00 UTC
Synonyms: none; DO NOT use 'Returning User' as a synonym
Examples: A customer with only refunded orders is NOT active
Owner: Growth Analytics

Term: Order Status
Allowed values: pending, completed, cancelled, refunded
Mapping: raw_system: {PEND → pending, DONE → completed, CANC → cancelled, RFND → refunded}
Rule: Any new status must be added here before appearing in dashboards

Build it step-by-step

1. Pick a high-impact table and 3–5 key metrics (start small).
2. Draft plain-language definitions with stakeholders (sales, finance, ops).
3. Fill technical fields for the data dictionary (type, constraints, lineage, owners).
4. Validate with real rows (sample data) and at least one dashboard that uses them.
5. Publish in a shared place; add owners and a change review cadence.
6. Adopt naming rules (singular vs plural, snake_case, disambiguate with prefixes).
7. Add change logs and version notes when definitions evolve.

Templates you can copy

table | column | friendly_name | business_meaning | data_type | format | allowed_values | source | lineage | granularity | unit | timezone | refresh | quality_rules | owner | sensitivity

term | definition | formula_or_logic | scope | freshness | synonyms | deprecated_terms | examples | owner | review_cycle

Exercises

Note: The quick test is available to everyone; only logged-in users will have results saved.

Common mistakes and how to self-check

• Vague definitions (“usually”, “around”, “often”). Fix: use exact filters, time windows, and inclusions/exclusions.
• Hiding filters in SQL only. Fix: write filters in the glossary so non-technical readers understand them.
• Forgetting refresh expectations. Fix: add freshness targets and last-load fields.
• No owner. Fix: assign owner/steward; log review dates.
• Not documenting units/timezones. Fix: always specify currency, unit, and timezone.
• Duplicated terms. Fix: merge and mark one as deprecated with redirect.

Practical projects

• Project 1: Document one fact table and two dimension tables end-to-end. Include quality rules and lineage notes.
• Project 2: Standardize three revenue-related terms with Finance and Sales; publish synonyms and deprecations.
• Project 3: Add a weekly change log to your glossary and run a 15-min review ritual with stakeholders.

Mini challenge

Your marketing dashboard shows “Active Users” higher than Product’s report. Propose a glossary entry that explains the difference. Include scope, filters, and a note on why they differ. Then update the dictionary to point to the correct source fields for each version.

Learning path

1. Learn the difference between dictionary (fields) and glossary (terms).
2. Document a single critical table and 3–5 KPIs.
3. Add owners, freshness, and quality rules.
4. Introduce change control and deprecate duplicates.
5. Automate pulls for data types and freshness where possible.
6. Expand coverage and integrate with dashboards as tooltips or field help.

Next steps

• Extend documentation to lineage and ownership across pipelines.
• Add data quality checks that reference your definitions.
• Embed definitions into BI fields and dashboard tooltips.