Skip to main content
Some Rosetta Stone attributes are different forms of the same fact. A raw email address, its normalized form, and its SHA-256 hash all describe one mailbox, and the second and third can be computed from the first. A derivation rule records that relationship once, so a dataset mapped to one attribute can answer queries for the others.

​
The problem derivations solve

Without derivations, every form of a value needs its own mapping on every dataset. A provider with an email column who wants to be found by queries for raw_email, normalized_email, and sha256_hashed_email writes three mappings, each repeating the same logic. Every other provider does the same, and nothing guarantees they all normalize or hash the same way. A derivation rule moves that logic out of the dataset and onto the attributes. The rule says how sha256_hashed_email is computed from normalized_email, no matter which dataset the value comes from. A provider maps the column once, to the form the dataset actually holds, and the rule supplies the rest.

​
How a rule becomes a mapping

A rule has a source attribute, a target attribute, and a transformation. The transformation is an expression that reads the source attribute through $source:
A rule on its own produces no data, because it names no dataset. It takes effect when a dataset has a stored mapping to the rule’s source attribute. The platform substitutes the stored mapping’s expression for $source, and the result is an ordinary mapping expression over the dataset’s columns. Suppose a dataset maps normalized_email with this expression:
Combined with the rule above, the dataset gains a derived mapping to sha256_hashed_email:
Derived mappings are computed when they are needed and never stored. They have no id, and you cannot update or delete one. To change a derived mapping, change the stored mapping or the rule it came from.

​
How queries use derived mappings

When you run a query, the platform collects the stored mappings you can see and the active rules your company may use, works out the derived mappings they imply, and compiles the query against both sets together. A query for a derived attribute reads like any other:
If dataset 123 maps only normalized_email, this query still returns hashes. Nothing in the query refers to the rule. Transformations run when the query runs. A rule that computes an age from a birthdate with CURRENT_DATE gives the age as of each query, not as of the day the rule was written.

​
Which mapping wins

A stored mapping beats a rule. If a dataset has an active stored mapping to the target attribute, no derived mapping is produced for that attribute on that dataset. A provider who already holds hashed emails keeps serving the stored hashes, whatever rules exist. The cheapest rule wins. When more than one rule reaches the same attribute on the same dataset, the rule with the lowest cost is used. Ties go to the rule with the lower id. Resolution follows one rule, not a chain. With a rule from raw_email to normalized_email and another from normalized_email to sha256_hashed_email, a dataset mapped only to raw_email reaches normalized_email and stops. A query for sha256_hashed_email returns nothing from that dataset. To reach the hash in one step, the dataset needs a stored mapping to normalized_email, or a rule must go from raw_email to sha256_hashed_email directly.

​
When a rule produces nothing

A rule can exist and still imply no derived mapping on a particular dataset. This happens when the stored mapping cannot be substituted into the rule’s expression:
  • The stored mapping is a cached_mapping. It has no expression to substitute.
  • The stored mapping is a value_mapping and the rule reads a property, such as $source.value. A single expression has no properties to pick from.
  • The stored mapping is an object_mapping that leaves out a property the rule reads, and the rule uses that property to fill a required property of the target. An unmapped property that feeds an optional target property becomes NULL instead.
In each case the dataset simply has no mapping to the target attribute. No error is raised, because the rule is still correct for other datasets.

​
Fidelity

Not every derived value is as good as a stored one, so each rule carries two flags: A rule can also carry a fidelity note explaining the flags in words. Derived mappings report the flags of the rule that produced them, so anyone deciding whether to accept derived data can see what was lost along the way.

​
Who can use a rule

A rule belongs to the company that created it. The owner can share it with named companies, and Narrative can share its own rules with every company. When you run a query, only the rules your company owns or has been given are applied. A rule that is not shared with you has no effect on your queries, even over a dataset whose owner can use it. Setting a rule to inactive stops it being applied without deleting it.

​
Derivations and access rules

A derivation never widens what an access rule shares. If a provider shares normalized_email through an access rule, the buyer can query normalized_email. The buyer cannot query sha256_hashed_email through a derivation, because the access rule did not include it. To share a derived attribute, name it in the access rule.

​
Why cycles are allowed

Two rules can point at each other. A rule from birthdate to age and another from age to an approximate birth year are both reasonable, and together they form a cycle. The platform writes such rules and returns a warning instead of rejecting them. Because resolution follows one rule at a time and a stored mapping always beats a derived one, a cycle cannot cause a query to loop.

​
What this means for mapping

Derivations change where a mapping is most valuable. A mapping to an attribute that other attributes are derived from also covers the attributes one rule away. A mapping made directly to a derived attribute covers only that one. Map each column to the most original form the dataset actually contains, and let rules supply the rest. See Map to the source of a derivation for how to check this before you create a mapping.

​
Related content

Creating Derivation Rules

Create, check, and retire a rule through the API

Derivations API

Endpoints, fields, permissions, and error responses

How Rosetta Stone Works

Attributes, mappings, and the normalization pipeline

Mapping Types

The mapping shapes that rules reuse