What is a data dictionary in ERP?

Every ERP vendor documents their software. Nobody documents your account. The gap between those two things — the custom fields somebody added in 2019, the status list your ops team renamed, the two fields that both look like they hold the ship date — is where reports go wrong, onboarding takes months, and AI answers become confidently incorrect. An ERP data dictionary is how you close it.

Data dictionary

A structured inventory of the data in a specific system: what records exist, what fields each record has, what those fields mean, what values they legitimately contain, and how records relate to each other. In ERP terms it is account-specific — two companies running the same ERP have different dictionaries, because the customisations, statuses and conventions are theirs.

What is in an ERP data dictionary

A useful dictionary has seven layers. The first three are structural and readable from the system. The next two must be observed from the data itself. The last two require a human, which is the part most tooling skips.

Fields, including the ones only you have

Custom fields are the whole reason a generic schema is not enough. In NetSuite they announce themselves by prefix: custentity_ on entities like customers and vendors, custbody_ on transaction bodies, custcol_ on transaction lines, custitem_ on items, custrecord_ on custom records, and customrecord_ for the custom record types themselves. The prefix tells you the field is yours. It tells you nothing about what it means.

A dictionary captures both the internal id and the label, because your team asks for "credit tier" and the query needs custentity_credit_tier. It also records the unglamorous facts: which custom fields are populated, which are leftovers from a project that ended, and which two fields hold overlapping versions of the same concept. Custom fields also behave differently in reporting depending on type and configuration — see reporting on NetSuite custom fields.

Join paths, not just foreign keys

Knowing that two tables can be joined is not the same as knowing how they should be. A transaction joins to its lines one-to-many, so any sum of a header amount across a line-level join is inflated. Some records connect through an intermediate table. Some connections are valid in one direction only. Some look valid and are semantically wrong — joining a payment to an invoice through the customer rather than the applied-to link gives you a number that is arithmetically fine and meaningless. A dictionary records which paths are real, their cardinality, and the grain each produces. That is the layer that stops the most common category of silently wrong total.

Observed value domains, not documented ones

This is the layer that separates a dictionary from a schema list. Documentation tells you a status field exists and lists the values the software supports. It cannot tell you which values your account contains, what your team renamed them to, or which are so rare they signal a data problem. So you ask the data.

-- Read the status domain instead of assuming it
SELECT status, COUNT(*) AS row_count
FROM transaction
WHERE type = 'SalesOrd'
GROUP BY status
ORDER BY row_count DESC

The same pattern gives you populated-field rates, currency mixes, subsidiary distribution and the date range each table covers. None of it comes from a manual. If you are collecting these probes into something your team can read, run them through the SuiteQL formatter first — a dictionary nobody can read is not documentation.

Row counts, and why they earn their place

Row counts look like trivia and do three jobs. They flag dead tables, so a question is not routed to a custom record with 4 rows in it. They set expectations about cost, since a query over 48,112 transactions and one over 6.2 million transaction lines are different operations against a paged API. And they give you the best sanity check available: a filter returning 0 of 48,112 rows is almost always a wrong filter, not a real zero.

Why vendor documentation is not a data dictionary

NetSuite publishes a records browser describing the standard schema, and your customisations are listed under the customisation menus. Both are useful and neither is a dictionary, for the same reason a dictionary of English is not a transcript of your meeting. The standard schema is shared by every account. The meaning is local.

• Renamed lists. A status called "Pending Review" matches nothing in any documentation, because you named it.
• Unused standard fields. Hundreds of documented fields are empty in your account. A system treating them as available builds a query on top of nothing.
• Convention in free text. A memo field where everyone types PO#12345 is load-bearing structure that no schema records.
• Archaeology. Custom fields from a migration that ended three years ago still exist, still look authoritative, and return values for old records only.
• Duplicate concepts. Two ship-date fields, one from the original build and one from the integration, agreeing most of the time. Choosing between them is a business decision.

How a per-account dictionary is built

Whether a tool does this or a person does, the sequence is the same.

1. 1.Connect with a least-privilege, read-only role. The dictionary should describe what that role can see, not what an administrator could — otherwise it promises answers the connection cannot deliver.
2. 2.Enumerate structure and count rows. Every record type and field — internal id, label, type, custom flag — plus one row count per table, which separates the live account from the theoretical one.
3. 3.Sample the domains. Group-by on every status, type and list field. Record values and frequencies, not just the distinct list.
4. 4.Establish join paths. Confirm which joins are real and what grain each produces. Cardinality is what prevents inflated sums.
5. 5.Capture semantics with humans. Metric definitions, synonyms, which of two duplicate fields is authoritative, which subsidiaries belong in a consolidated figure. This step cannot be automated, and it decides whether answers match the numbers finance already publishes.
6. 6.Version it. Date it, store it, and diff it against the next build so you can see what changed and when.

Keeping it fresh

A dictionary is accurate until your next customisation, which may be this afternoon. Five drift sources matter, and every one of them is invisible until something answers wrongly.

Two properties matter more than refresh frequency: a stated staleness window, and a feedback loop so a confirmed answer improves the dictionary rather than being forgotten. Ask any vendor for both. "It learns automatically" is not a mechanism.

What a dictionary changes about the answers

The difference shows up on ordinary questions, not exotic ones.

The semantic layer earns its keep here too. "What's our DSO?" has more than one defensible answer depending on the variant you report and whether cash sales are excluded — the DSO calculator settles the arithmetic independently of your ERP, and the AR aging calculator does the same for overdue balances.

Build a lightweight one even if you never buy AI

Most of the value here is not about AI. Four spreadsheets will shorten every onboarding and settle most reporting arguments — and they make an AI layer more accurate later, because semantics are the part software cannot derive:

• Field register. Every custom field: id, label, meaning, owner, whether it is still in use.
• Status glossary. Every status value in every list your business uses, in your words.
• Metric definitions. One page per reported number: formula, fields, exclusions, owner, date agreed.
• Join map. The five or six joins your reporting depends on, with cardinality marked.

Limits of a data dictionary

A dictionary describes your data. It does not improve it. If two integrations write conflicting ship dates, it can record that both exist and which you trust — it cannot make them agree. It cannot infer intent, so an ambiguous question stays ambiguous and the right behaviour is to ask. It cannot see past the connected role's permissions, so a dictionary built on a narrow role describes a narrow account. And its semantic layer is only as current as the last person who maintained it.

This is why the dictionary is a component rather than the product. It is the first of the four properties of grounded ERP AI — the others are validating each query against the real schema before it runs, showing the query with the answer, and refusing outside what is known. Take away one and you are back to plausible answers nobody can check.

ERPray builds this dictionary of your account during onboarding — record types with row counts, every standard and custom field, your subsidiaries, the real join paths, and status domains read from your data rather than assumed — and you can watch it being built. Queries are generated from it, validated against your schema before they run, and shown as SuiteQL under every answer. What we cannot invent is the semantic layer: if your team has three definitions of revenue, we will ask which you mean.