Derivation rule

What is a derivation rule?

A derivation rule defines how a field is calculated from other data instead of being stored or copied straight across. In a source-to-target mapping, every target column is either moved over unchanged or worked out from one or more inputs, and the derivation rule is the exact expression, condition, or lookup that produces the computed value.

Most fields you report on are derived rather than raw. gross_margin = net_revenue - cost_of_goods_sold, age = today() - date_of_birth, and a flag like is_active_customer are all derivations: each one turns raw records into a business figure. The rule is the written-down version of that logic, in language a business reviewer and an engineer both accept.

What a derivation rule pins down

A one-line formula is rarely the whole rule. Written properly in a mapping specification or a data catalog, a derivation rule settles a handful of questions that decide whether two people compute the same number:

• Inputs and their source. Which fields feed the calculation, and which table or system each one comes from. A net_revenue value from the sales fact table is not the same thing as an invoice total pulled from the finance ledger.

• The expression or decision. The arithmetic or the conditional logic, stated so a business reviewer can follow it. Code can sit alongside the definition, but it does not replace the plain-language statement of intent.

• Which rows count. The filter that scopes the rule. Test orders, credit notes, internal accounts, and cancelled transactions usually have to be excluded, and that exclusion is part of the rule, not a footnote.

• Null and zero handling. What happens when an input is missing: the result becomes null, zero, unknown, or the row is dropped. A division that meets a zero denominator has to say what it returns.

• Grain and identity. The level the value is computed at, and the identifier used to group or match. Counting repeat customers on a customer id gives a different answer than counting on email address.

Computation versus inference

The business-rules literature splits derivations into two kinds, and the split is worth holding onto because it changes how you test the rule. A computation does arithmetic on values. An inference deduces a new fact, usually a flag or a category, from a set of conditions.

Take a computation first. Gross margin is a straight calculation on two columns:

select
  order_id,
  net_revenue - cost_of_goods_sold as gross_margin,
  (net_revenue - cost_of_goods_sold)
    / nullif(net_revenue, 0) as gross_margin_pct
from orders
where order_status = 'paid'
  and is_test_order = false

Three decisions are already baked in: the exclusion of test orders in the filter, the nullif that stops a divide-by-zero when net_revenue is 0, and the choice of net rather than gross revenue as the base. On an order with net_revenue of 100000 and cost_of_goods_sold of 62000, the rule returns a gross margin of 38000 and a margin of 38 percent.

An inference looks different. Consider is_active_customer: a customer counts as active when they have at least one paid order in the trailing 12 months. The rule has to name the qualifying event (a paid order), the window (12 months back from today), the exclusions (cancelled and test orders do not qualify), and the identifier it matches on (customer_id, not email, because a customer who changes email keeps the same id). Change any one of those and the active-customer count moves, even though the source data has not.

Derivation rule versus validation rule

A derivation rule and a validation rule often read from the same columns, so they get confused. The difference is what each one outputs. A derivation produces a value. A validation judges a value and returns a verdict, pass or fail.

From net_revenue and cost_of_goods_sold, a derivation computes gross_margin. A validation rule over the same fields asserts something that must hold, for example that cost never exceeds revenue, and flags the rows that break it. In business-rules terms a derivation creates a new derived fact, while a validation is a constraint on what values are allowed. The two sit side by side in a data contract and in most data quality suites, and a mature pipeline keeps both: rules that build the numbers, and rules that check them.

What to watch out for with derivation rules

Derivations are where reported numbers quietly diverge. The same label, active customer or margin, computed two different ways by two teams, produces two different figures from identical source data. The usual fix is to define each derivation once in a shared place, a metrics layer, a semantic model, or a dbt model, rather than re-deriving it in every report.

Three failure modes come up again and again:

• Silent null and zero handling. A missing cost treated as zero inflates margin. A divide-by-zero that returns 0 instead of null hides the problem rather than surfacing it.

• Unstable identity. Matching on email, name, or another value that changes splits one real entity into several, or merges two into one. Match on a stable identifier.

• Stale meaning. A rule keeps executing cleanly long after the business meaning changed. A new order type or a dropped product line can leave the SQL valid and the number wrong.