Skip to content

narrative-io/narrative-skills-marketplace / plugins/narrative-common/skills/rosetta-stone-mapping-rules

rosetta-stone-mapping-rules

Verified

The rules for mapping a Narrative dataset's columns to Rosetta Stone attributes: choosing the attribute, mapping the source of a derivation, value_mapping vs object_mapping, discriminators, validating each expression with NQL, and scoring confidence. No flow of its own; the skills that generate or infer mappings read it. Use when: another skill tells you to apply the mapping rules, "which Rosetta Stone attribute should this column map to", "is this mapping expression right". (narrative-common)

FileKindSize
SKILL.mdskill md21.3 KB
SKILL.md.tmplother19.8 KB
references/EDGE_CASES.mdmarkdown2.3 KB
references/ENUM_HANDLING.mdmarkdown5.3 KB
references/EXPRESSION_SYNTAX.mdmarkdown8.7 KB
references/KB_RESEARCH.mdmarkdown6.9 KB

references/EXPRESSION_SYNTAX.md

View on GitHub
# NQL / SQL expression syntax for Rosetta Stone mappings

Mapping expressions are evaluated by the Narrative NQL engine. They
look like SQL but with quoting rules that trip up most agents on the
first try.

Always validate with `narrative_nql_validate(nql: ...)` before
suggesting an expression to the user. The tool takes a full NQL
query, so wrap the expression as
`select <expression> from company_data."<dataset_id>"`.

## Quoting rules

Reserved identifiers MUST be double-quoted when used as column or
property names:

`type`, `value`, `user`, `order`, `group`, `select`, `from`, `where`,
`join`, `case`, `when`, `then`, `else`, `end`, `null`, `true`, `false`

Safe column names can be unquoted: `email_address`, `user_name`,
`created_at`, `device_id`.

| Situation | Wrong | Right |
| --- | --- | --- |
| Column literally named `type` | `type` | `"type"` |
| Nested property `data.value` | `data.value` | `data."value"` |
| Safe column | `"email_address"` (works but noisy) | `email_address` |
| String literal | `"email"` | `'email'` |
| Identifier discrimnator | `email` | `'email'` (it's a *value*, not a column) |

Double quotes = identifier. Single quotes = string literal. Mixing them
up is the #1 cause of NQL validation errors.

### Backslash escapes

Don't put backslash escapes in string literals. NQL passes `'\s'` to
Spark, which reads an escape it doesn't know as the bare letter: `\s`
becomes `s`, `\d` becomes `d`, `\w` becomes `w`. The expression still
validates and then changes data on every row.

| Wrong | What Spark runs | Right |
| --- | --- | --- |
| `REGEXP_REPLACE(email, '^\s+\|\s+$', '')` | strips a leading or trailing `s` | `TRIM(email)` |
| `zip RLIKE '^\d{5}$'` | five letters `d` | `zip RLIKE '^[0-9]{5}$'` |

## Functions

Common, NQL-supported:

- `LOWER(x)`, `UPPER(x)`, `TRIM(x)`
- `COALESCE(x, default)` — but see "null handling" below; usually unneeded
- `NULLIF(x, value)`
- `CAST(x AS type)` — types: `string`, `long`, `double`, `boolean`, `timestamp`, `timestamptz`
- `to_timestamp(text, format)` — parse a date/time string with a Presto-style format mask (see "Timestamp normalization"). `date_parse` and `parse_datetime` are NOT supported.
- `REGEXP_REPLACE(string, pattern, replacement)`
- `SUBSTRING(x, start, length)`
- `CONCAT(a, b, ...)`

Conditional:

```sql
CASE WHEN condition THEN value
     WHEN other_condition THEN other_value
     ELSE default_value
END
```

CASE WHEN expressions are the workhorse for enum normalization — see
`ENUM_HANDLING.md`.

## Null handling

The NQL engine automatically propagates nulls at runtime. You almost
never need `COALESCE` in a mapping expression:

- Source column is null → output is null (correct behavior, not a bug).
- `LOWER(null_email)` → null (no need to wrap in COALESCE).
- Sample-data test results showing nulls when input was null are
  expected; don't flag them as edge cases.

Use `COALESCE` only when:

- You're combining two source columns and want to fall back
  (`COALESCE(preferred_email, backup_email)`).
- You're producing a literal default that the target attribute requires
  (rare).

Never use `COALESCE(x, '')` to convert null to empty string — empty
strings break enum matching and identifier comparison.

## Validations the platform applies

The platform applies an attribute's `validations` every time it reads a mapped
value: a value that fails any of them becomes NULL. Read them from the
`schema` section of `narrative_attributes_describe`: a `- validations:` line
for a scalar attribute, a `"validations"` array on each property inside the
`properties` JSON for an object attribute. A property that references another
attribute comes back with that attribute's type filled in, its validations
included. Don't repeat them in the expression — `LOWER(hashed_email_sha256)` is
enough for a `^[a-f0-9]{64}$` validation. Still transform values so they pass
(lower-case them). SKILL.md section 2 has the full rule.

## Type discriminators in object_mappings

For object attributes with a `type` property (e.g., the standard
Identifier object: `{ type, value }`), the `type` expression is almost
always a **literal string**, not a column reference:

```json
{ "path": "type", "expression": "'email'" }
```

If you find yourself writing `{ "path": "type", "expression": "some_column" }`,
double-check: is every sampled value of `some_column` already in the
attribute's enum? If not, you need a CASE WHEN — see `ENUM_HANDLING.md`.

## Examples by Rosetta Stone shape

### Primitive identifier (email)

```json
{
  "type": "value_mapping",
  "expression": "TRIM(email_address)"
}
```

### Hashed-identifier object (sha256_email)

```json
{
  "type": "object_mapping",
  "property_mappings": [
    { "path": "type",  "expression": "'sha256_email'" },
    { "path": "value", "expression": "LOWER(hashed_email_sha256)" }
  ]
}
```

### Mixed-hash identifier (one column holds the hash type)

```json
{
  "type": "object_mapping",
  "property_mappings": [
    { "path": "type",  "expression": "CASE WHEN hash_algo = 'SHA256' THEN 'sha256_email' WHEN hash_algo = 'MD5' THEN 'md5_email' WHEN hash_algo = 'SHA1' THEN 'sha1_email' ELSE NULL END" },
    { "path": "value", "expression": "LOWER(hashed_value)" }
  ]
}
```

### Reserved-name nested property

If the source data has a struct column `payload` with subfields `type`
and `value`:

```json
{
  "type": "object_mapping",
  "property_mappings": [
    { "path": "type",  "expression": "payload.\"type\"" },
    { "path": "value", "expression": "payload.\"value\"" }
  ]
}
```

### Timestamp normalization

NQL date-parsing function naming is **not** the standard SQL set — the
engine accepts `to_timestamp(text, format)` with Presto-style format
masks, plus `CAST(... AS timestamp)` for already-ISO-shaped strings.
Several look-alikes (`date_parse`, `parse_datetime`) are **not**
supported and will fail validation with `No match found for function
signature ...`.

| Source shape | Use this | Notes |
| --- | --- | --- |
| ISO 8601 string (`'2026-05-18T12:34:56Z'`) | `CAST("event_ts" AS timestamp)` | Cheapest path; engine parses ISO directly. |
| ISO date string (`'2026-05-18'`) | `CAST("event_date" AS timestamp)` | Promotes to midnight UTC. |
| Locale string (e.g., `'8/15/25'`) | `to_timestamp("check_in_date", '%c/%e/%y')` | Presto-style format mask; see table below. |
| Epoch seconds (long) | `CAST(FROM_UNIXTIME("epoch_s") AS timestamp)` | Validate first; older engines may need `to_timestamp(CAST("epoch_s" AS varchar))`. |
| Epoch millis (long) | `CAST(FROM_UNIXTIME("epoch_ms" / 1000) AS timestamp)` | Same caveat. |

Presto-style format mask tokens (most common):

| Token | Meaning | Example |
| --- | --- | --- |
| `%Y` | 4-digit year | `2026` |
| `%y` | 2-digit year | `26` |
| `%m` | Zero-padded month | `05` |
| `%c` | Non-padded month | `5` |
| `%d` | Zero-padded day | `08` |
| `%e` | Non-padded day | `8` |
| `%H` | 24-hour | `14` |
| `%i` | Minute | `34` |
| `%s` | Second | `56` |

```json
{
  "type": "value_mapping",
  "expression": "to_timestamp(\"check_in_date\", '%c/%e/%y')"
}
```

Drop confidence by ≥15 points and add an `ambiguous_date_format`
warning when (a) the format mask is locale-dependent (M/D vs D/M), or
(b) you're using `%y` rather than `%Y` (century rollover depends on
the engine's pivot year — confirm with `narrative_nql_execute` if any
records may be pre-2000). Always validate the exact mask against the
schema before suggesting it.

## When validation fails

`narrative_nql_validate` returns an error pointing to a position in
the expression. Common fixes:

| Error symptom | Likely cause | Fix |
| --- | --- | --- |
| "syntax error at or near 'type'" | Unquoted reserved word | Quote with `"type"` |
| "column not found" | Wrong identifier name / casing | Re-check with `narrative_datasets_describe` |
| "function does not exist" | Wrong function name (e.g., `LCASE` instead of `LOWER`) | Use the function table above |
| "No match found for function signature `date_parse`/`parse_datetime`" | NQL doesn't expose these | Use `to_timestamp(text, format)` with Presto-style masks, or `CAST(... AS timestamp)` for ISO strings — see Timestamp normalization. |
| "cannot cast string to long" | Implicit type coercion | Wrap with `CAST(..., AS long)` or use NULLIF |
| "unexpected ELSE without CASE" | Mismatched CASE/END | Count CASE … END pairs |

Re-validate after every fix. Never suggest a mapping with an
expression you have not personally validated against the target
dataset's schema.

## When this file isn't enough

For the official NQL function/operator reference, type-system rules,
or worked examples (joins, materialized views, performance patterns),
query the `narrative-knowledge-base` MCP server. See
`KB_RESEARCH.md` for the recommended search queries and entry-point
docs paths (`/concepts/nql/...`, `/cookbooks/nql/...`,
`/api-reference/nql/...`).
reverse-engineer-anythingmorluto/reaReverse engineer native, managed, Electron/JavaScript, packaged, firmware, and browser targets with REA, and build agent environments (AgentEnv bundles) of web applications from a live instance or a capture, and of Linux desktop apps with a task from a request. Use shipped-artifact or requested runtime evidence to explain features, compare versions, decompile code, or guide a reconstruction. Skip REA for ordinary source-repository architecture analysis.Stars: 472kUpdated Claude CodeCodexVerifiedInstall
diagnosing-superpowersobra/superpowersUse when a superpowers session went wrong and your human partner wants to know why — repeated work, ignored plans, stumbles, poor results, a skill that didn't fire, "it took too long", "why is it so expensive", "what is it doing" — or wants to build a bug report for the superpowers maintainers, for the current session or a past one identified by id or path, on any harness.Stars: 323kUpdated Claude CodeCodexVerifiedInstall
dispatching-parallel-agentsobra/superpowersUse when facing 2+ independent tasks that can be worked on without shared state or sequential dependenciesStars: 323kUpdated Claude CodeCodexVerifiedInstall
executing-plansobra/superpowersUse when executing an implementation plan in the current session as the implementer yourself — your human partner chose inline execution, or no subagent tool is availableStars: 323kUpdated Claude CodeCodexVerifiedInstall