Mapping Schemas - Narrative I/O Knowledge Base

This guide covers how to create and manage mappings between your dataset columns and Rosetta Stone attributes. Mappings are the translation layer that enables your data to be queried through the normalized narrative.rosetta_stone table.

For background on how mappings work, see How Rosetta Stone Works.

Prerequisites

A dataset uploaded to Narrative
Understanding of your dataset’s column semantics
Familiarity with basic NQL syntax (for transformation expressions)

Map to the source of a derivation

Some attributes can be computed from others through derivation rules. A dataset mapped to the source attribute of a rule gets a derived mapping to the rule’s target automatically, so one mapping to normalized_email can also answer queries for sha256_hashed_email. A mapping made directly to the derived attribute covers only that attribute. Before you choose a target attribute, check what it is derived from. Open the attribute under Rosetta Stone → Attributes: the Derivable From card lists the active rules that can produce it, and the Can Derive card lists the rules that take it as their source. See Attributes Interface for what each row shows. The API answers the same question:

curl -H "Authorization: Bearer YOUR_API_TOKEN" \
  "https://api.narrative.io/attributes/sha256_hashed_email/derivation-graph?direction=ancestors"

Each node with "relationship": "ancestor" is an attribute the target can be derived from. If your column holds one of those forms, map to it instead. Rules are followed one step at a time: a mapping covers the attributes derived directly from it, not the attributes derived from those in turn. Map directly to a derived attribute only when the dataset holds nothing but the derived form, such as hashed emails with no plain addresses.

Using the UI

View suggested mappings

Navigate to Datasets and select your dataset
Click the Rosetta Stone tab
Review the auto-generated mapping suggestions

The UI displays your columns alongside suggested attributes. Each suggestion includes:

The target attribute name and description
A confidence score
A preview of sample data after transformation

Accept a suggested mapping

For high-confidence suggestions:

Review the suggested attribute and sample output
Click Accept to create the mapping

The mapping becomes active immediately.

Modify a suggested mapping

If a suggestion is close but not quite right:

Click Edit on the suggestion
Modify the target attribute or transformation expression
Click Save

Create a manual mapping

For columns without suggestions or when you need a custom mapping:

Click Add Mapping
Select the source column
Search for and select the target attribute
Write a transformation expression if needed
Click Create

Reject a suggestion

If a suggestion is incorrect:

Click the X or Reject button
The suggestion is removed and won’t reappear

Review derived mappings

The Rosetta Stone Mappings section on a dataset lists the derived mappings your stored mappings imply alongside the stored ones. A derived row is tagged Derived, and carries a Lossy or Imprecise tag when the rule behind it loses or approximates information. Derived mappings are computed when a query runs and never stored, so they cannot be tested, edited, or deleted. Their row menu offers a single action:

Open the row menu on a derived mapping and choose View Provenance
Read the chain in the drawer: the stored mapping it started from, each derivation rule applied in order, and the attribute it produces
Click View source mapping to open the stored mapping it came from, read-only

To change a derived mapping, change the stored mapping it came from or the rule that produced it.

Using the API

List available attributes

Before creating mappings, browse available attributes:

curl -X GET "https://api.narrative.io/v1/attributes" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json"

Response:

{
  "data": [
    {
      "id": "hl7_gender",
      "name": "hl7_gender",
      "description": "Gender using HL7 administrative gender codes",
      "type": "enum",
      "allowed_values": ["male", "female", "other", "unknown"]
    },
    {
      "id": "event_timestamp",
      "name": "event_timestamp",
      "description": "The timestamp when the event occurred",
      "type": "timestamp"
    }
  ]
}

Get mapping suggestions for a dataset

Request auto-generated mapping suggestions:

curl -X GET "https://api.narrative.io/v1/datasets/{dataset_id}/mappings/suggestions" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json"

Response:

{
  "data": [
    {
      "source_column": "gender",
      "suggested_attribute": "hl7_gender",
      "confidence": 0.95,
      "suggested_transformation": "LOWER(gender)",
      "sample_output": ["male", "female", "male"]
    },
    {
      "source_column": "event_date",
      "suggested_attribute": "event_timestamp",
      "confidence": 0.87,
      "suggested_transformation": "TO_TIMESTAMP(event_date, 'YYYY-MM-DD')",
      "sample_output": ["2024-01-15T00:00:00Z", "2024-01-16T00:00:00Z"]
    }
  ]
}

Create a mapping

Create a new mapping for a dataset column:

curl -X POST "https://api.narrative.io/v1/mappings" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "dataset_id": "your-dataset-id",
    "source_column": "sex",
    "target_attribute": "hl7_gender",
    "transformation": "CASE sex WHEN '\''M'\'' THEN '\''male'\'' WHEN '\''F'\'' THEN '\''female'\'' ELSE '\''unknown'\'' END"
  }'

Response:

{
  "data": {
    "id": "mapping-12345",
    "dataset_id": "your-dataset-id",
    "source_column": "sex",
    "target_attribute": "hl7_gender",
    "transformation": "CASE sex WHEN 'M' THEN 'male' WHEN 'F' THEN 'female' ELSE 'unknown' END",
    "status": "active",
    "created_at": "2024-01-15T10:30:00Z"
  }
}

Accept a system-proposed mapping

Accept a suggestion from the auto-generated mappings:

curl -X POST "https://api.narrative.io/v1/mappings/{suggestion_id}/accept" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json"

Update an existing mapping

Modify a mapping’s transformation:

curl -X PATCH "https://api.narrative.io/v1/mappings/{mapping_id}" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "transformation": "CASE LOWER(sex) WHEN '\''m'\'' THEN '\''male'\'' WHEN '\''f'\'' THEN '\''female'\'' WHEN '\''nb'\'' THEN '\''other'\'' ELSE '\''unknown'\'' END"
  }'

Delete a mapping

Remove a mapping:

curl -X DELETE "https://api.narrative.io/v1/mappings/{mapping_id}" \
  -H "Authorization: Bearer YOUR_API_TOKEN"

Mapping types

The Mappings API accepts three mapping types:

value_mapping — a single SQL expression producing a primitive value.
object_mapping — one expression per property of an object attribute.
cached_mapping — joins the source dataset against a precomputed cache dataset at query time. Use this when the transformation is expensive or comes from an external workflow.

For the full schema, validation rules, and constraints (including the company-scope and opt-out restrictions on cached mappings), see Mapping types.

Writing transformation expressions

Transformation expressions are NQL expressions that convert source values to the target attribute format.

Simple column reference

When no transformation is needed:

source_column

Type conversion

Convert a string to an integer:

CAST(string_column AS INTEGER)

Convert to timestamp:

TO_TIMESTAMP(date_string, 'YYYY-MM-DD')

Conditional logic

Map discrete values:

CASE source_column
  WHEN 'A' THEN 'value_a'
  WHEN 'B' THEN 'value_b'
  ELSE 'default'
END

Map ranges:

CASE
  WHEN age < 18 THEN 'minor'
  WHEN age < 65 THEN 'adult'
  ELSE 'senior'
END

Null handling

Provide a default for nulls:

COALESCE(source_column, 'unknown')

Replace specific values with null:

NULLIF(source_column, '')

String manipulation

Normalize case:

LOWER(source_column)
UPPER(source_column)

Trim whitespace:

TRIM(source_column)

Extract a substring:

SUBSTRING(source_column, 1, 10)

Combining multiple columns

Concatenate values:

CONCAT(first_name, ' ', last_name)

Build a struct:

STRUCT(
  'email_sha256' AS type,
  hashed_email AS value,
  'hashed_email' AS context
)

Complete worked example

Scenario

You have a customer dataset with these columns:

Column	Sample values
`cust_id`	`"12345"`, `"67890"`
`sex`	`"M"`, `"F"`, `"X"`
`signup_date`	`"01/15/2024"`, `"02/20/2024"`
`email_hash`	`"a1b2c3..."`, `"d4e5f6..."`

Step 1: Review suggestions

The system suggests:

sex → hl7_gender (medium confidence)
signup_date → event_timestamp (high confidence)
email_hash → email_sha256 (high confidence)

Step 2: Accept high-confidence mappings

Accept the signup_date and email_hash mappings.

Step 3: Customize the gender mapping

The suggested transformation doesn’t handle "X" correctly. Create a custom mapping:

CASE sex
  WHEN 'M' THEN 'male'
  WHEN 'F' THEN 'female'
  WHEN 'X' THEN 'other'
  ELSE 'unknown'
END

Step 4: Create a manual mapping for customer ID

Map cust_id to the unique_identifier attribute:

STRUCT(
  'customer_id' AS type,
  cust_id AS value,
  'internal' AS context
)

Step 5: Test and activate

Test all mappings, then activate.

Troubleshooting

Issue	Cause	Solution
”Type mismatch” error	Transformation output doesn’t match attribute type	Use `CAST()` or adjust transformation
”Invalid enum value” error	Transformation produces values not in allowed list	Add missing cases to `CASE` statement
Null values in output	Transformation doesn’t handle all source values	Add `ELSE` clause or use `COALESCE`
”Column not found” error	Typo in column name	Verify column name matches exactly

How Rosetta Stone Works

Understand the mechanics of attributes and mappings

Validating Mappings

Test and verify your mappings

Edge Cases

Handle complex mapping scenarios

Transformation Functions

Complete function reference

Mapping Types

API reference for value_mapping, object_mapping, and cached_mapping

​Prerequisites

​Map to the source of a derivation

​Using the UI

​View suggested mappings

​Accept a suggested mapping

​Modify a suggested mapping

​Create a manual mapping

​Reject a suggestion

​Review derived mappings

​Using the API

​List available attributes

​Get mapping suggestions for a dataset

​Create a mapping

​Accept a system-proposed mapping

​Update an existing mapping

​Delete a mapping

​Mapping types

​Writing transformation expressions

​Simple column reference

​Type conversion

​Conditional logic

​Null handling

​String manipulation

​Combining multiple columns

​Complete worked example

​Scenario

​Step 1: Review suggestions

​Step 2: Accept high-confidence mappings

​Step 3: Customize the gender mapping

​Step 4: Create a manual mapping for customer ID

​Step 5: Test and activate

​Troubleshooting

​Related content

How Rosetta Stone Works

Validating Mappings

Edge Cases

Transformation Functions

Mapping Types

Prerequisites

Map to the source of a derivation

Using the UI

View suggested mappings

Accept a suggested mapping

Modify a suggested mapping

Create a manual mapping

Reject a suggestion

Review derived mappings

Using the API

List available attributes

Get mapping suggestions for a dataset

Create a mapping

Accept a system-proposed mapping

Update an existing mapping

Delete a mapping

Mapping types

Writing transformation expressions

Simple column reference

Type conversion

Conditional logic

Null handling

String manipulation

Combining multiple columns

Complete worked example

Scenario

Step 1: Review suggestions

Step 2: Accept high-confidence mappings

Step 3: Customize the gender mapping

Step 4: Create a manual mapping for customer ID

Step 5: Test and activate

Troubleshooting

Related content