Skip to main content

​
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 includes:
  • Lowercase conversion
  • Removal of plus-addressing
  • Provider-specific handling (Gmail dot removal, etc.)

​
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"

​
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:
ParameterTypeDescription
raw_identifierstringThe clear text identifier to encode (email, hashed email, phone, etc.)
key_idstringThe 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:
ParameterTypeDescription
narrative_idstringAn existing Narrative ID to translate
target_key_idstringThe 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"

​
EMBED_TEXT_768

Generates a 768-dimensional text embedding. 
SELECT EMBED_TEXT_768(description) AS embedding
FROM company_data."123"

​
COSINE_SIMILARITY

Calculates cosine similarity between two vectors. 
SELECT COSINE_SIMILARITY(embedding1, embedding2) 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

​
Related content

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