NQL Syntax - Narrative I/O Knowledge Base

NQL (Narrative Query Language) uses SQL-like syntax with extensions for data collaboration. This reference covers statement types, clause ordering, and query structure.

Statement types

NQL supports three main statement types:

Statement	Purpose
`SELECT`	Execute a query and return results
`EXPLAIN`	Forecast data availability and cost without executing
`CREATE MATERIALIZED VIEW`	Create a stored dataset from a query

SELECT statement

The SELECT statement retrieves data from one or more datasets.

Syntax

SELECT [DISTINCT]
  column1 [AS alias1],
  column2 [AS alias2],
  ...
FROM table_reference [AS alias]
[JOIN table_reference ON condition]
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[QUALIFY condition]
[ORDER BY column1 [ASC|DESC], ...]
[LIMIT n [OFFSET m] ROWS]

Clause order

Clauses must appear in this order:

SELECT — columns to return
FROM — data source(s)
JOIN — additional data sources with join conditions
WHERE — row-level filters applied before aggregation
GROUP BY — grouping for aggregations
HAVING — filters applied after aggregation
QUALIFY — filters based on window function results
ORDER BY — result ordering
LIMIT — result count restriction

Examples

Basic query:

SELECT
  user_id,
  email,
  created_at
FROM company_data."123"
WHERE created_at > '2024-01-01'

Query with aggregation:

SELECT
  region,
  COUNT(*) AS user_count,
  AVG(lifetime_value) AS avg_ltv
FROM company_data."456"
GROUP BY region
HAVING COUNT(*) > 100
ORDER BY avg_ltv DESC

Query with window function and QUALIFY:

SELECT
  user_id,
  email,
  last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1

EXPLAIN statement

The EXPLAIN statement forecasts query results without purchasing or executing. Use it to preview data availability and estimated costs.

Syntax

EXPLAIN
SELECT ...

What EXPLAIN returns

Estimated row count matching your criteria
Estimated cost based on data pricing
Data freshness information
Result schema

Example

EXPLAIN
SELECT
  company_data."123".user_id,
  company_data."123".email
FROM company_data."123"
WHERE
  company_data."123".created_at > CURRENT_DATE - INTERVAL '30' DAY
  AND company_data."123"._price_cpm_usd <= 1.00

Always run EXPLAIN before creating a materialized view to understand data availability and cost.

CREATE MATERIALIZED VIEW

Creates a persistent dataset from a query. For complete syntax including all options, see Materialized View Syntax.

Basic syntax

CREATE MATERIALIZED VIEW "view_name"
AS (
  SELECT ...
)
BUDGET n USD

Example

CREATE MATERIALIZED VIEW "active_users"
AS (
  SELECT
    user_id,
    email,
    last_login
  FROM company_data."123"
  WHERE last_login > CURRENT_DATE - INTERVAL '30' DAY
)
BUDGET 50 USD

Table references

Dataset references

Reference your datasets using the company_data schema with the dataset ID:

FROM company_data."123"
FROM "company_data"."123"

Dataset IDs must be quoted because they are numeric.

Table aliases

Assign aliases to simplify column references:

FROM company_data."123" AS users
SELECT users.email, users.created_at

Rosetta Stone

Query the identity resolution layer using the narrative schema:

FROM narrative.rosetta_stone

SELECT
  narrative.rosetta_stone.unique_id,
  narrative.rosetta_stone.event_timestamp
FROM narrative.rosetta_stone

Access rules

Query through pre-configured access rules using the provider’s company slug:

FROM provider_company."access_rule_name"

SELECT
  provider_company."ar_fitness".Total_Output
FROM provider_company."ar_fitness"

Column references

Fully qualified names

Use fully qualified column names to avoid ambiguity, especially in joins:

SELECT
  company_data."123".user_id,
  company_data."123".email,
  company_data."456".order_id
FROM company_data."123"
JOIN company_data."456" ON company_data."123".user_id = company_data."456".customer_id

Column aliases

Assign aliases to rename columns in results:

SELECT
  user_id AS id,
  email AS contact_email,
  UPPER(name) AS display_name
FROM company_data."123"

Accessing nested fields

Access struct fields using dot notation:

SELECT
  company_data."1".nested_field.deep_field.value
FROM company_data."1"

Access array elements using bracket notation:

SELECT
  identifiers[0] AS first_id,
  identifiers[0].type AS first_id_type
FROM company_data."1"

Special columns

Price column

Every dataset includes _price_cpm_usd (price per 1,000 rows). Use it to filter by cost:

WHERE company_data."123"._price_cpm_usd <= 1.00

Rosetta Stone access

Access identity resolution data through the ._rosetta_stone special field:

SELECT
  company_data."1"._rosetta_stone.unique_id
FROM company_data."1"

Access specific identifier types:

SELECT
  unique_id.b."type",
  unique_id.b."value"
FROM narrative.rosetta_stone
WHERE unique_id.b."type" NOT IN ('tdid', 'cookie')

DELTA tables

The DELTA function returns only records that have changed since the last query execution. Use it for incremental data processing.

Syntax

FROM DELTA(TABLE table_reference)
FROM DELTA((subquery))

Examples

Delta on a dataset:

SELECT column1
FROM DELTA(TABLE company_data."1") AS t

Delta on Rosetta Stone:

SELECT long_value
FROM DELTA(TABLE narrative.rosetta_stone)

Delta with filtering:

SELECT long_value
FROM DELTA((
  SELECT *
  FROM company_data.rosetta_stone
  WHERE long_value > 42
))

Subqueries

Subquery in WHERE

SELECT column1
FROM company_data."1"
WHERE value > (
  SELECT AVG(value)
  FROM company_data."1"
)

Subquery in FROM

SELECT sub.total
FROM (
  SELECT SUM(amount) AS total
  FROM company_data."1"
  GROUP BY category
) AS sub
WHERE sub.total > 1000

EXISTS and NOT EXISTS

SELECT t1.column1
FROM company_data."1" t1
WHERE NOT EXISTS (
  SELECT 1
  FROM company_data."2" t2
  WHERE t1.id = t2.id
)

Common Table Expressions (WITH)

CTEs let you define named subqueries for reuse within a statement.

Syntax

WITH cte_name AS (
  SELECT ...
),
another_cte AS (
  SELECT ...
)
SELECT ...
FROM cte_name

Example

CREATE MATERIALIZED VIEW "high_value_users"
AS (
  WITH daily_totals AS (
    SELECT
      user_id,
      DATE_TRUNC('day', event_timestamp) AS event_date,
      SUM(amount) AS daily_spend
    FROM company_data."1"
    GROUP BY user_id, DATE_TRUNC('day', event_timestamp)
  ),
  user_averages AS (
    SELECT
      user_id,
      AVG(daily_spend) AS avg_daily_spend
    FROM daily_totals
    GROUP BY user_id
  )
  SELECT
    user_id,
    avg_daily_spend
  FROM user_averages
  WHERE avg_daily_spend > 100
)
BUDGET 25 USD

QUALIFY clause

The QUALIFY clause filters results based on window function values. It’s evaluated after window functions, making it useful for deduplication and ranking.

Syntax

SELECT columns
FROM table
QUALIFY window_function_condition

Deduplication example

Keep only the most recent record per user:

SELECT
  user_id,
  email,
  last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1

Multiple window conditions

SELECT column1
FROM company_data."1"
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col) = 1
  AND SUM(1) OVER (PARTITION BY other_col) < 10

Budget clauses

Budget clauses control spending on data queries. They are required for CREATE MATERIALIZED VIEW statements.

BUDGET

Set a maximum total spend:

BUDGET 50 USD

LIMIT with USD

Alternative syntax for budget limits:

LIMIT 100 USD

Recurring limits

Set budget limits per time period:

LIMIT 500 USD PER CALENDAR_MONTH
LIMIT 50 USD PER CALENDAR_DAY

Row limits

Limit by row count instead of cost:

LIMIT 1000 ROWS
LIMIT 100 OFFSET 10 ROWS

SELECT DISTINCT

Remove duplicate rows from results:

SELECT DISTINCT category
FROM company_data."123"

CASE expressions

Conditional logic within queries:

SELECT
  user_id,
  CASE
    WHEN lifetime_value > 1000 THEN 'high'
    WHEN lifetime_value > 100 THEN 'medium'
    ELSE 'low'
  END AS value_tier
FROM company_data."123"

Data Types

Primitive and complex types supported in NQL

Operators

Comparison, logical, and arithmetic operators

Functions

Built-in and Narrative-specific functions

Materialized View Syntax

Complete CREATE MATERIALIZED VIEW reference

Overview

NQL Reference

UI Reference

Integrations

Architecture Reference

Rosetta Stone Reference

SDKs

Glossary

​Statement types

​SELECT statement

​Syntax

​Clause order

​Examples

​EXPLAIN statement

​Syntax

​What EXPLAIN returns

​Example

​CREATE MATERIALIZED VIEW

​Basic syntax

​Example

​Table references

​Dataset references

​Table aliases

​Rosetta Stone

​Access rules

​Column references

​Fully qualified names

​Column aliases

​Accessing nested fields

​Special columns

​Price column

​Rosetta Stone access

​DELTA tables

​Syntax

​Examples

​Subqueries

​Subquery in WHERE

​Subquery in FROM

​EXISTS and NOT EXISTS

​Common Table Expressions (WITH)

​Syntax

​Example

​QUALIFY clause

​Syntax

​Deduplication example

​Multiple window conditions

​Budget clauses

​BUDGET

​LIMIT with USD

​Recurring limits

​Row limits

​SELECT DISTINCT

​CASE expressions

​Related content

Data Types

Operators

Functions

Materialized View Syntax

Statement types

SELECT statement

Syntax

Clause order

Examples

EXPLAIN statement

Syntax

What EXPLAIN returns

Example

CREATE MATERIALIZED VIEW

Basic syntax

Example

Table references

Dataset references

Table aliases

Rosetta Stone

Access rules

Column references

Fully qualified names

Column aliases

Accessing nested fields

Special columns

Price column

Rosetta Stone access

DELTA tables

Syntax

Examples

Subqueries

Subquery in WHERE

Subquery in FROM

EXISTS and NOT EXISTS

Common Table Expressions (WITH)

Syntax

Example

QUALIFY clause

Syntax

Deduplication example

Multiple window conditions

Budget clauses

BUDGET

LIMIT with USD

Recurring limits

Row limits

SELECT DISTINCT

CASE expressions

Related content