SpeclyFrom business term to column
A business term should be defined once and linked to the columns that use it. Here is how to write the term, link it and handle changes.
Take the web shop from the article on the shared data model. Management asks how many active customers the shop has, and gets three different numbers from marketing, customer service and finance. Why that happens, and why a shared model fixes it, is covered in that article. This one is about how: what to write down about “active customer”, how to link the definition to the tables, and what happens the day someone wants to change it.
What a good term contains
A business term is more than a word with an explanation. For the business, the developers and the analysts to all be able to use it, it needs five parts.
There are established guidelines for how definitions should be written. The standard ISO/IEC 11179-4 says, among other things, that a definition should state what the term is, not only what it is not. It also recommends that a definition be precise and unambiguous, not circular, and that related definitions use the same terms and the same structure. The advice below builds on the same principles.
Name. The name is what people say in meetings and write in reports, not what the column is called in the database. “Active customer”, not is_active or active_flg. Pick one name and stick to it. If the term has synonyms in use, write them down, so that people searching for “purchasing customer” find the same term.
Definition. The definition should be precise enough that two people who read it count the same thing. “A customer who shops with us” is not enough. A better definition is: “A customer who has paid for at least one order in the last twelve months, counted from today. Orders that were returned in full do not count.” It says what is counted, over which period, and how a common edge case is handled.
Owner. The owner is the one who decides what the term means, and the one who answers when someone is in doubt. For “active customer” that might be finance, since finance is who reports the number onward. The owner should be a role or a team, not just a person, so the term is not left without an owner when someone changes jobs.
Examples. Examples make the definition concrete. A customer who paid for an order three months ago is active. A customer who has only created an account and signed up for the newsletter is not. Nor is a customer who bought one item eleven months ago and returned it. Disagreements often surface in the examples, because it is easier to disagree about a concrete case than about an abstract sentence.
What the term is not. Write down the nearby terms that are easily confused with this one. “Active customer” is not the same as “registered customer”, which counts everyone with an account. Nor is it the same as “contactable customer”, which counts those who have consented to marketing. When marketing needs its number, it gets its own term with its own name, instead of borrowing a name that means something else.
A term with these five parts can be read by a new analyst without anyone having to explain it out loud. For the same reason, it can also be read by an AI assistant that has to write a query.
One term, many columns
Once the term is written down, the next step is to find out where it is used. It is rarely just one column. In the web shop, “active customer” might show up as a flag in the customer table in the silver layer, as a filter in the sales report and as a column of its own in the customer dimension in the gold layer. The columns can have different names: is_active in one table, active_customer in another.
The same goes for terms like “order” and “product”. order_id appears in the order table, the order line table, the returns table and the sales fact table. They all refer to the same term, even though the tables were built by different teams.
The link therefore runs from the term to the columns, not the other way around. The term has one definition, and every column that represents it points to it. That lets you answer two questions that are otherwise hard: which columns mean “active customer”, and what does this column mean?
A common mistake is to copy the definition into the description of every column. It looks tidy the day it is done. A year later there are six versions of the definition, with small differences, and nobody knows which one applies. The column description should instead refer to the term and only say what is specific to this particular column, for example that the flag is calculated every night, or that the table only contains customers from the Norwegian shop.
A good column description is therefore short, but can be read on its own: “True if the customer is active. Calculated in the nightly load.” The link to the term sits next to the description, not as a reference in the text. That way the definition lives in one place, and the column inherits it.
What the link looks like in a data contract
If you describe your tables as data contracts, the link can be written straight into the contract. The Open Data Contract Standard has two fields for this on every column: businessName for the term’s name, and authoritativeDefinitions for a link to the definition.
apiVersion: v3.2.0
kind: DataContract
id: customer
version: 1.0.0
status: active
schema:
- name: customer
properties:
- name: is_active
businessName: Active customer
logicalType: boolean
required: true
description: True if the customer is active. Calculated in the nightly load.
authoritativeDefinitions:
- type: businessDefinition
url: https://glossary.example.com/terms/active-customer
description: The definition, owned by financeThe column is called is_active in the database, but businessName says which term it represents. The link in authoritativeDefinitions points to where the definition actually lives, so anyone reading the contract can look it up. The businessDefinition type is one of the types the standard recommends for links to a business glossary. The description does not repeat the definition; it only says what applies to this column.
When a definition changes
Definitions change. Finance might decide that an active customer should be counted over six months instead of twelve, or that partially returned orders should not count either. The change is small in the text, but the number in every report that uses the term changes too. That is why the change needs a fixed procedure.
The owner decides. Anyone can propose a change, but the owner makes the call. That stops one team from changing the definition to make its own report add up without the others knowing.
Find what uses the term. Before the change takes effect, you need to know which columns and reports are linked to the term. This is where the link from the previous section does its job. Without it, someone has to search through tables and reports for anything that might mean “active customer”, and something gets missed.
Tell the consumers. The people who own the reports and tables that use the term need to know what is changing, why, and from when. If the change is big enough that the numbers can no longer be compared with last year’s, that has to be said clearly too, so nobody thinks the customers vanished overnight.
Keep the history. The old definition should not be deleted. Someone will ask why the number of active customers dropped in March, and the answer is that the definition changed. That answer only exists if someone wrote down when the change happened, what changed, and who decided it. If the terms live in version control together with the data model, you get the history at no extra cost.
Where to start
You don’t need a complete glossary before you begin. Pick the terms that often cause disagreement, in the web shop perhaps “active customer”, “order” and “revenue”. Write the name, definition, owner, examples and what the term is not. Find the columns that use them, and replace the copied definitions with a reference to the term.
The next time someone asks how many active customers the shop has, there is one answer, one definition to point to, and one owner who can explain it. To see how the terms connect to the rest of the model, read on about dimensional modeling in practice, or see how a business glossary can keep the terms in order.