Narrative Functions

Narrative-specific functions

These functions are specific to the Narrative platform.

NORMALIZE_EMAIL

Normalizes an email address for consistent matching.

SELECT NORMALIZE_EMAIL('[email protected]') -- '[email protected]'
SELECT NORMALIZE_EMAIL(email) AS normalized_email FROM company_data."123"

Normalization:

Removes surrounding whitespace, comments and a display name (Jane <[email protected]> becomes [email protected])
Converts the address to lowercase
For gmail.com and googlemail.com addresses only, removes the periods in the username and everything from a + to the @ ([email protected] becomes [email protected])

Other addresses keep their periods and + tag: NORMALIZE_EMAIL('[email protected]') returns '[email protected]'. A value that can’t be parsed as an email address returns NULL.

NORMALIZE_PHONE

Normalizes a phone number to a standard format.

SELECT NORMALIZE_PHONE('NATIONAL', '+15145555555', null) AS normalized
SELECT NORMALIZE_PHONE('E164', phone, 'US') AS e164_phone FROM company_data."123"

NORMALIZE_PHONE(format, phone, default_country) parses phone and writes it in format: E164, INTERNATIONAL, NATIONAL or RFC3966. It drops spaces, punctuation and extensions. default_country is the ISO country code to assume for a number written without +. With null, only numbers that carry their country code get a value:

SELECT NORMALIZE_PHONE('E164', '+1 206 555 0167', null) -- '+12065550167'
SELECT NORMALIZE_PHONE('E164', '(206) 555-0114', null)  -- NULL: no country code
SELECT NORMALIZE_PHONE('E164', '(206) 555-0114', 'US')  -- '+12065550114'
SELECT NORMALIZE_PHONE('E164', '+44 7700 900013', 'US') -- '+447700900013': the + wins over the default

Pick a default country only when every number without + is from that country. A national number from another country is read as the default country’s and comes out wrong: NORMALIZE_PHONE('E164', '07700 900013', 'US') returns '+107700900013'. It returns NULL for a value it can’t parse, such as '' or 'n/a'. It doesn’t check that the number exists: NORMALIZE_PHONE('E164', '0000000000', 'US') returns '+10000000000'.

HASH

Generates a hash from one or more values.

SELECT HASH('value1', 'value2') AS combined_hash
SELECT HASH(user_id, email) AS user_hash FROM company_data."123"

MD5 / SHA1 / SHA2

Standard cryptographic hash functions.

SELECT MD5('hello')           -- MD5 hash
SELECT SHA1('hello')          -- SHA-1 hash
SELECT SHA2('hello', 256)     -- SHA-256 hash

HMAC_SHA256

HMAC using SHA-256.

SELECT HMAC_SHA256('message', 'secret_key') AS hmac

ADDRESS_HASHES

Generates hash variants for address matching.

SELECT ADDRESS_HASHES('123 Main St', 'New York', 'NY', '10001') AS hashes

NARRATIVE_ID_ENCODE

Encodes a clear text identifier into a Narrative ID within a specific encoding space. Narrative IDs are per-partner pseudonymous identifiers that enable privacy-safe data collaboration. Syntax:

NARRATIVE_ID_ENCODE(raw_identifier, key_id)

Parameters:

Parameter	Type	Description
`raw_identifier`	string	The clear text identifier to encode (email, hashed email, phone, etc.)
`key_id`	string	The encoding key ID specifying the target encoding space

Returns: string - The encoded Narrative ID Examples:

-- Encode an email address
SELECT NARRATIVE_ID_ENCODE(LOWER(TRIM(email)), 'your_org_key_id') AS narrative_id
FROM company_data."123"

-- Encode an already-hashed email
SELECT NARRATIVE_ID_ENCODE(email_sha256, 'your_org_key_id') AS narrative_id
FROM company_data."123"

-- Create a dataset with encoded identifiers
SELECT
  NARRATIVE_ID_ENCODE(email_sha256, 'your_org_key_id') AS narrative_id,
  event_timestamp,
  event_properties
FROM company_data."123"
WHERE event_timestamp >= '2024-01-01'

The same identifier encoded with different key_id values produces different Narrative IDs. Always normalize identifiers (lowercase, trim whitespace) before encoding for consistent matching.

Related: Narrative ID Concepts, Using Narrative ID

NARRATIVE_ID_TRANSLATE

Translates a Narrative ID from one partner’s encoding space to another. This enables secure data collaboration by converting identifiers without exposing the underlying clear text values. Syntax:

NARRATIVE_ID_TRANSLATE(narrative_id, target_key_id)

Parameters:

Parameter	Type	Description
`narrative_id`	string	An existing Narrative ID to translate
`target_key_id`	string	The encoding key ID for the target partner’s encoding space

Returns: string - The translated Narrative ID in the target encoding space Examples:

-- Translate Narrative IDs for a partner collaboration
SELECT
  NARRATIVE_ID_TRANSLATE(narrative_id, 'partner_b_key_id') AS partner_b_narrative_id,
  customer_segment,
  activity_score
FROM company_data."123"

-- Join across partner datasets using translation
SELECT
  a.campaign_name,
  a.impressions,
  b.conversions
FROM partner_a."ad_data" a
JOIN partner_b."conversion_data" b
  ON NARRATIVE_ID_TRANSLATE(a.narrative_id, 'partner_b_key_id') = b.narrative_id

-- Prepare data for multiple partners
SELECT
  narrative_id AS original_id,
  NARRATIVE_ID_TRANSLATE(narrative_id, 'partner_a_key_id') AS partner_a_id,
  NARRATIVE_ID_TRANSLATE(narrative_id, 'partner_b_key_id') AS partner_b_id,
  audience_segment
FROM company_data."123"

Use case: When sharing data with a partner, translate your Narrative IDs to their encoding space so they can match against their own Narrative IDs. This enables cross-organization matching without exposing underlying identifiers. Related: Narrative ID Concepts, Using Narrative ID

UNIVERSE_SAMPLE

Deterministic sampling based on an identifier.

SELECT user_id, email, created_at
FROM company_data."123"
WHERE UNIVERSE_SAMPLE(user_id, 0.1)  -- 10% sample

DETERMINISTIC_RAND

Generates a deterministic random number from a seed.

SELECT DETERMINISTIC_RAND(user_id) AS random_value
FROM company_data."123"

PARSE_JSON / TRY_PARSE_JSON

Parses a JSON string.

SELECT PARSE_JSON('{"key": "value"}') AS parsed
SELECT TRY_PARSE_JSON(maybe_json) AS safe_parsed  -- Returns NULL on error
FROM company_data."123"

OBJECT_REMOVE_NULLS

Returns a struct with null-valued fields removed. On AWS hosted data planes, the function is accepted for cross-platform query compatibility but operates as a no-op.

SELECT OBJECT_REMOVE_NULLS(NAMED_STRUCT('a', 1, 'b', NULL, 'c', 3))
-- Input schema:  STRUCT<a: INT, b: INT, c: INT>
-- Output schema: STRUCT<a: INT, c: INT>
-- Result: {a: 1, c: 3}

TOKENIZE

Tokenizes text using a specified model.

SELECT TOKENIZE(text_column) AS tokens
FROM company_data."123"

TOKENIZE_SIZE

Returns the token count for text.

SELECT TOKENIZE_SIZE(text_column) AS token_count
FROM company_data."123"

COSINE_SIMILARITY

Calculates the cosine similarity between two embeddings of the given dimension, such as two outputs of AI_EMBED from the same model.

SELECT COSINE_SIMILARITY(embedding1, embedding2, 768) AS similarity
FROM company_data."123"

TEXT_SEARCH

Scores text relevance against a search query.

SELECT TEXT_SEARCH(description, 'machine learning') AS relevance_score
FROM company_data."123"

UUID_GENERATE

Generates a random UUID.

SELECT UUID_GENERATE() AS new_id

Aggregate Functions

GROUP BY computations

SELECT

DELTA tables and budget clauses

All Functions

Browse all function categories

NQL Design Philosophy

Why NQL extends standard SQL

Overview

Data Types

SQL Commands

Operators

Functions

General Reference

Narrative-specific functions

NORMALIZE_EMAIL

NORMALIZE_PHONE

HASH

MD5 / SHA1 / SHA2

HMAC_SHA256

ADDRESS_HASHES

NARRATIVE_ID_ENCODE

NARRATIVE_ID_TRANSLATE

UNIVERSE_SAMPLE

DETERMINISTIC_RAND

PARSE_JSON / TRY_PARSE_JSON

OBJECT_REMOVE_NULLS

TOKENIZE

TOKENIZE_SIZE

COSINE_SIMILARITY

TEXT_SEARCH

UUID_GENERATE

Aggregate Functions

SELECT

All Functions

NQL Design Philosophy

​Narrative-specific functions

​NORMALIZE_EMAIL

​NORMALIZE_PHONE

​HASH

​MD5 / SHA1 / SHA2

​HMAC_SHA256

​ADDRESS_HASHES

​NARRATIVE_ID_ENCODE

​NARRATIVE_ID_TRANSLATE

​UNIVERSE_SAMPLE

​DETERMINISTIC_RAND

​PARSE_JSON / TRY_PARSE_JSON

​OBJECT_REMOVE_NULLS

​TOKENIZE

​TOKENIZE_SIZE

​COSINE_SIMILARITY

​TEXT_SEARCH

​UUID_GENERATE

​Related content

Aggregate Functions

SELECT

All Functions

NQL Design Philosophy

Narrative-specific functions

NORMALIZE_EMAIL

NORMALIZE_PHONE

HASH

MD5 / SHA1 / SHA2

HMAC_SHA256

ADDRESS_HASHES

NARRATIVE_ID_ENCODE

NARRATIVE_ID_TRANSLATE

UNIVERSE_SAMPLE

DETERMINISTIC_RAND

PARSE_JSON / TRY_PARSE_JSON

OBJECT_REMOVE_NULLS

TOKENIZE

TOKENIZE_SIZE

COSINE_SIMILARITY

TEXT_SEARCH

UUID_GENERATE

Related content