Narrative Functions

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

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

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

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

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

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

NARRATIVE_ID_ENCODE(raw_identifier, key_id)

-- 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'

NARRATIVE_ID_TRANSLATE(narrative_id, target_key_id)

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

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

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

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

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}

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

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

SELECT EMBED_TEXT_768(description) AS embedding
FROM company_data."123"

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

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

SELECT UUID_GENERATE() AS new_id