Skip to main content
This reference documents all attribute types available in Rosetta Stone, including their properties, validations, and usage examples.

​
Attribute structure

All attributes share a common structure based on the platform’s type system: 
{
  "id": 123,
  "name": "attribute_name",
  "display_name": "Human Readable Name",
  "description": "Description of the attribute",
  "type": "string",
  "validations": ["LENGTH($this) >= 1", "LENGTH($this) <= 255"]
}
Required properties:
  • id (number): Unique identifier for the attribute
  • name (string): Machine-readable name
  • type (string): One of the supported types
Optional properties:
  • display_name (string): Human-readable name for UI display
  • description (string): Explanation of the attribute’s purpose
  • validations (string[]): Array of validation rules
  • metadata (object): Additional metadata including co-occurrence data

​
Primitive types

Primitive types represent basic data values.
TypeDescriptionNQL equivalentExample values
stringVariable-length textVARCHAR"hello", "[email protected]"
longWhole numbers (64-bit signed)BIGINT42, -17, 0
doubleDouble-precision decimalDOUBLE3.14159, -0.001
booleanTrue or falseBOOLEANtrue, false
timestamptzDate and time with timezoneTIMESTAMP2024-01-15T14:30:00Z

​
String

Variable-length Unicode text. Can optionally include an enum property to restrict allowed values. Basic definition: 
{
  "id": 1001,
  "name": "customer_name",
  "type": "string",
  "description": "Full name of the customer"
}
With validations: 
{
  "id": 1002,
  "name": "email_sha256",
  "type": "string",
  "description": "SHA-256 hash of email address",
  "validations": ["LENGTH($this) = 64"]
}
With enum (restricted values): 
{
  "id": 1003,
  "name": "hl7_gender",
  "type": "string",
  "enum": ["male", "female", "other", "unknown"],
  "description": "Gender using HL7 administrative gender codes"
}
When a string attribute has an enum property, only those values are allowed. Values are case-sensitive.

​
Long

64-bit signed whole numbers. Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Definition: 
{
  "id": 1004,
  "name": "age",
  "type": "long",
  "description": "Age in years",
  "validations": ["$this >= 0", "$this <= 150"]
}

​
Double

Double-precision (64-bit) floating-point numbers. Definition: 
{
  "id": 1005,
  "name": "latitude",
  "type": "double",
  "description": "Geographic latitude",
  "validations": ["$this >= -90", "$this <= 90"]
}

​
Boolean

True or false values. Definition: 
{
  "id": 1006,
  "name": "opted_in",
  "type": "boolean",
  "description": "Whether the user has opted in to marketing"
}
Mapping from source data: 
-- From string values
CASE UPPER(opt_in_col)
  WHEN 'Y' THEN true
  WHEN 'YES' THEN true
  WHEN 'TRUE' THEN true
  WHEN '1' THEN true
  ELSE false
END

​
Timestamptz

Date and time with timezone, stored in UTC. Follows ISO 8601 format. Definition: 
{
  "id": 1007,
  "name": "event_timestamp",
  "type": "timestamptz",
  "description": "When the event occurred"
}
Mapping from source data: 
-- From various formats
TO_TIMESTAMP(date_col, 'YYYY-MM-DD HH24:MI:SS')
TO_TIMESTAMP(date_col, 'MM/DD/YYYY')

​
Object type

Objects group related fields into a single composite value. Use properties to define the fields and required to specify which fields are mandatory. Definition: 
{
  "id": 2001,
  "name": "geo_coordinates",
  "type": "object",
  "description": "Geographic coordinates",
  "properties": {
    "latitude": {
      "type": "double"
    },
    "longitude": {
      "type": "double"
    },
    "accuracy_meters": {
      "type": "double"
    }
  },
  "required": ["latitude", "longitude"]
}
Properties:
  • properties: Object mapping field names to field definitions
  • required: Array of field names that must be present
Nested objects: Objects can contain other objects: 
{
  "id": 2002,
  "name": "address",
  "type": "object",
  "properties": {
    "street": { "type": "string" },
    "city": { "type": "string" },
    "state": { "type": "string" },
    "postal_code": { "type": "string" },
    "coordinates": {
      "type": "object",
      "properties": {
        "latitude": { "type": "double" },
        "longitude": { "type": "double" }
      }
    }
  },
  "required": ["street", "city"]
}
Mapping example: 
STRUCT(
  CAST(lat AS DOUBLE) AS latitude,
  CAST(lon AS DOUBLE) AS longitude,
  CAST(acc AS DOUBLE) AS accuracy_meters
)
Accessing object fields in queries: 
SELECT
  geo_coordinates.latitude,
  geo_coordinates.longitude
FROM narrative.rosetta_stone
WHERE geo_coordinates.accuracy_meters < 100

​
Array type

Arrays contain multiple values of the same type. Definition: 
{
  "id": 3001,
  "name": "interest_categories",
  "type": "array",
  "items": {
    "type": "string"
  },
  "description": "List of interest categories"
}
Properties:
  • items: Definition of the element type
Array of objects: 
{
  "id": 3002,
  "name": "identifiers",
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "type": { "type": "string" },
      "value": { "type": "string" }
    }
  }
}
Mapping examples: 
-- From comma-separated string
SPLIT(interests, ',')

-- From JSON array
JSON_PARSE(json_array_col)

-- Building an array from multiple columns
ARRAY(email_sha256, phone_sha256)
Querying arrays: 
-- Check if array contains a value
SELECT unique_id, interest_categories
FROM narrative.rosetta_stone
WHERE ARRAY_CONTAINS(interest_categories, 'sports')

-- Unnest array for row-level operations
SELECT
  id,
  interest
FROM narrative.rosetta_stone
CROSS JOIN UNNEST(interest_categories) AS t(interest)

​
Reference type

References link to other attribute definitions using their numeric ID. This enables reuse of standardized definitions. Definition: 
{
  "id": 4001,
  "name": "purchase_event",
  "type": "object",
  "properties": {
    "timestamp": {
      "$ref": 1007
    },
    "user": {
      "$ref": 5001
    },
    "amount": {
      "type": "double"
    }
  },
  "required": ["timestamp", "user", "amount"]
}
Properties:
  • $ref: The numeric ID of the referenced attribute
Behavior:
  • The field inherits the full type definition of the referenced attribute
  • Validations from the referenced attribute apply
  • Changes to the referenced attribute automatically propagate
Use cases:
  • Reusing standardized definitions (like event_timestamp)
  • Ensuring consistency across attributes
  • Building complex schemas from well-defined components

​
Validation expressions

Validations are NQL expressions that filter out invalid values. Use $this to reference the value being validated. These expressions are injected into compiled NQL queries to enforce data quality.

​
Numeric validations

Comparison operators check value ranges: 
{
  "id": 10,
  "name": "birth_year",
  "type": "long",
  "description": "The year that an entity was born",
  "validations": ["LENGTH($this) = 4", "$this >= 1900"]
}
ExpressionDescription
$this >= 0Value must be at least 0
$this <= 100Value must be at most 100
$this > 0 AND $this < 1000Combined range check

​
String length validations

Use LENGTH() to validate string length:
ExpressionDescription
LENGTH($this) >= 1At least 1 character
LENGTH($this) <= 255At most 255 characters
LENGTH($this) = 2Exactly 2 characters

​
Pattern matching

Use LIKE for pattern validation:
ExpressionDescription
$this LIKE 'A%'Starts with “A”
$this LIKE '%@%.%'Contains @ and . (email-like)

​
Examples

{
  "id": 5001,
  "name": "age",
  "type": "long",
  "validations": ["$this >= 0", "$this <= 150"]
}

{
  "id": 5002,
  "name": "country_code",
  "type": "string",
  "validations": ["LENGTH($this) = 2"]
}

​
Join key attributes

Primitive attributes can be marked as join keys using the is_join_key property: 
{
  "id": 6001,
  "name": "email_sha256",
  "type": "string",
  "is_join_key": true,
  "description": "SHA-256 hash of email for identity matching"
}
Join key attributes are optimized for use in JOIN operations across datasets.

​
Related content

Transformation Functions

Functions for mapping transformations

How Rosetta Stone Works

Understand attributes and mappings

The Normalization Model

Type system and validation concepts

Creating Normalized Attributes

Best practices for designing attributes