Build a SQL data dictionary from a reporting question

A useful data dictionary explains how fields should be interpreted, not only how they are stored. SQL types can tell you that an amount is an integer or a timestamp is text. They cannot tell you whether the amount includes discounts, whether a missing value means zero, or which status makes an order eligible.

Start from a reporting question, then document the fields needed to answer it. This keeps the dictionary focused enough to maintain while capturing the assumptions most likely to change the result.

Our question is: What is the recorded value of completed orders before refunds? The synthetic commerce lab supplies the tables and expected calculations used below.

Document the entity and grain first

For orders, one row represents one order header and order_id is the unique key. For order_items, one row represents an item line, and order_id can repeat. Those two sentences prevent many errors that a list of column types will not catch.

Write the intended relationship as well: one order can have several items; an order can have several refund events; each order may refer to a customer record, but the fixture deliberately includes one unmatched reference.

The last point distinguishes an intended relationship from an enforced guarantee. An analyst should not assume every reference is valid merely because the schema suggests it should be.

Add meaning, units and missingness

The unit is essential. Treating paise as rupees would overstate the displayed amount by a factor of 100. The discount note is equally important: a technically valid expression could subtract the same discount twice.

For fields with enumerated values, list the allowed states and the meaning of transitions if available. A “completed” label may refer to payment, shipment or workflow completion in different systems. Use the source owner's definition.

Separate the field dictionary from the metric contract

The dictionary explains the ingredients. The metric contract explains how they are combined:

SELECT COUNT(*) AS completed_orders,
       SUM(order_total_paise) AS completed_value_paise
FROM orders
WHERE status = 'completed';

Expected: eight orders and 104,000 paise. The contract states that refunds are separate and that a missing customer dimension does not remove an otherwise eligible order.

Someone could use the same fields to calculate a different measure, such as refund-adjusted value or known-customer order value. Those should have separate names and definitions rather than silently replacing the original metric.

This separation makes a dictionary useful across several reports without pretending that one column has a single universal aggregation rule.

Verify the dictionary with data checks

A dictionary is stronger when its claims are executable. For uniqueness:

SELECT order_id, COUNT(*) AS rows_per_order
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Expected: no rows. For the status vocabulary:

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;

Expected: cancelled 1, completed 8 and pending 1. Compare observed values with the allowed set; do not automatically add every new spelling to the approved vocabulary.

Check nullability, value ranges, join coverage and line-to-header reconciliation in the same way. Database constraints can enforce some properties, while other expectations belong in the analytical pipeline. The distinction between source constraints and observed checks should remain visible. SQLite table constraints.

Record ownership and change history

For a working team, add the source system, data owner, refresh expectation, first availability, known limitations and last substantive definition change. These fields help answer “Who can explain this?” and “Is this still the same measure as last quarter?”

A change from order-created date to payment date should trigger a metric-version decision. A new display label may not. Define what counts as a meaningful change rather than updating a timestamp for cosmetic edits.

Keep sensitive source details out of a public portfolio dictionary. You can demonstrate the structure using synthetic data and explain that a real engagement would include controlled-access provenance records.

Use the dictionary in handover and AI review

A new analyst should be able to reproduce the reference calculation from the dictionary and metric contract. If they need to ask whether discounts have already been applied, the document is missing a material rule.

An AI assistant also benefits from these explicit definitions, but supplying a dictionary does not remove the need to validate generated SQL. Check whether the model used the correct grain, eligible status, amount unit and refund policy.

Exercise: write a second metric contract for refunded-order percentage. Identify which definitions can be reused and which new rules are required. In particular, distinguish refund events from refunded orders and specify the observation window.

The Data Analytics with Generative AI programme connects querying, pipelines and verified analysis. A concise, tested dictionary is a practical artifact that helps those parts agree on what the data means.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL text cleaning: normalize categories without merging different people.
• Continue with Validate an analyst SQL query with five independent checks.