NQL vs SQL - Narrative I/O Knowledge Base

If you know SQL, you already know most of NQL. This page explains what’s the same, what’s different, and what NQL adds for data collaboration scenarios.

What stays the same

NQL uses standard SQL syntax for core query operations. If you’ve written SQL queries before, these patterns work exactly as you’d expect:

SELECT statements

SELECT
  column1,
  column2,
  UPPER(column3) AS transformed
FROM table_reference
WHERE condition
ORDER BY column1 DESC
LIMIT 100

Aggregations and grouping

SELECT
  category,
  COUNT(1) AS count,
  SUM(amount) AS total,
  AVG(score) AS average
FROM table_reference
GROUP BY category
HAVING COUNT(1) > 10

Joins

SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.foreign_id
LEFT JOIN table3 t3 ON t1.id = t3.ref_id

Subqueries and CTEs

WITH summary AS (
  SELECT category, SUM(amount) AS total
  FROM sales
  GROUP BY category
)
SELECT category, total FROM summary WHERE total > 1000

INSERT, UPDATE, and DELETE

NQL supports the standard DML statements. INSERT accepts both VALUES and SELECT forms; UPDATE and DELETE take an optional WHERE clause:

INSERT INTO company_data."123" (user_id, email)
VALUES (42, '[email protected]')

INSERT INTO company_data."456" (user_id, email)
SELECT user_id, email FROM company_data."123" WHERE is_active = TRUE

UPDATE company_data."123" SET status = 'archived' WHERE user_id = 42

DELETE FROM company_data."123" WHERE email IS NULL

See INSERT, UPDATE, and DELETE for full references.

Standard functions

Most SQL functions work as expected:

String: UPPER, LOWER, CONCAT, SUBSTRING, TRIM
Numeric: ABS, ROUND, FLOOR, CEIL
Date: CURRENT_DATE, DATE_TRUNC, EXTRACT
Aggregate: COUNT, SUM, AVG, MIN, MAX

What’s different

Table references use dataset IDs or names

In traditional SQL, you reference tables by name. In NQL, you reference datasets within the company_data schema by either their numeric ID or their dataset name:

SELECT user_id, email FROM users
SELECT event_type, timestamp FROM analytics.events

SELECT user_id, email FROM company_data.my_users
SELECT event_type, timestamp FROM company_data.web_events

SELECT user_id, email FROM company_data."123"
SELECT event_type, timestamp FROM company_data."456"

Numeric IDs must be quoted because they’re numeric. Dataset names don’t need quoting unless the name is a reserved keyword or contains special characters. The company_data schema contains your datasets.

Queries built with Data Studio’s Query Builder render the FROM clause using dataset names by default, so the NQL editor view shows readable references like company_data.my_users. Numeric IDs are still valid everywhere, and Narrative I/O uses them internally where a stable identifier is required — for example, the NQL persisted with an access rule is always ID-qualified, since dataset names can be reused after a dataset is archived.

Special data sources

NQL provides access to shared resources that don’t exist in traditional databases:

Source	Purpose
`narrative.rosetta_stone`	Identity resolution across datasets
`provider_slug."access_rule"`	Data shared via access rules

-- Identity resolution data
SELECT unique_id, event_timestamp
FROM narrative.rosetta_stone

-- Data from another company's access rule
SELECT user_id, activity_type, calories
FROM partner_company."fitness_data"

Price filtering

Every dataset includes a _price_cpm_usd column representing the cost per 1,000 rows. This doesn’t exist in traditional databases:

-- Only get data priced at $1 or less per 1000 rows
SELECT user_id, email, created_at
FROM company_data."123"
WHERE _price_cpm_usd <= 1.00

What NQL adds

Budget controls

NQL includes budget clauses to control data spending—a concept that doesn’t exist in traditional SQL:

CREATE MATERIALIZED VIEW "my_data"
AS (SELECT user_id, email, created_at FROM company_data."123")
BUDGET 50 USD

-- Recurring budget limits
LIMIT 100 USD PER CALENDAR_MONTH
LIMIT 10 USD PER CALENDAR_DAY

Materialized views with options

NQL’s CREATE MATERIALIZED VIEW includes scheduling, partitioning, and metadata options beyond what most databases offer:

CREATE MATERIALIZED VIEW "daily_summary"
REFRESH_SCHEDULE = '@daily'
DISPLAY_NAME = 'Daily Summary Report'
DESCRIPTION = 'Aggregated daily metrics'
EXPIRE = 'P30D'
TAGS = ('analytics', 'daily')
PARTITIONED_BY event_date DAY
AS (
  SELECT date, COUNT(1) as events
  FROM company_data."123"
  GROUP BY date
)
BUDGET 25 USD

QUALIFY clause

While some databases support QUALIFY, it’s not part of standard SQL. NQL includes it for filtering on window function results:

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

Without QUALIFY, you’d need a subquery:

-- Equivalent without QUALIFY (more verbose)
SELECT user_id, email, last_seen
FROM (
  SELECT
    user_id, email, last_seen,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY last_seen DESC) AS rn
  FROM company_data."123"
) sub
WHERE rn = 1

DELTA tables

Query only changed records since the last execution—useful for incremental processing:

SELECT user_id, email, updated_at
FROM DELTA(TABLE company_data."123")

This capability requires the platform to track changes, which isn’t standard SQL functionality.

Rosetta Stone integration

Access identity resolution through special columns:

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

Narrative-specific functions

Functions designed for data collaboration scenarios:

SELECT
  NORMALIZE_EMAIL(email) AS clean_email,
  NORMALIZE_PHONE('E164', phone, 'US') AS clean_phone,
  HASH(user_id, email) AS identity_hash,
  UNIVERSE_SAMPLE(user_id, 0.1) AS in_sample
FROM company_data."123"

SQL features not supported

Some SQL features aren’t available in NQL:

Feature	Status	Alternative
`SELECT `, `COUNT()`	Not supported	List columns explicitly
`CREATE TABLE`	Not supported	Use `CREATE MATERIALIZED VIEW`
`CREATE INDEX`	Not supported	Partitioning provides similar benefits
Stored procedures	Not supported	—
Transactions	Not supported	Each query is atomic
`UNION`	Limited support	Some restrictions apply

Dialect differences

NQL is transpiled to different database engines (Snowflake, Spark). The transpiler handles dialect differences, but awareness helps when debugging:

Date functions

-- NQL uses consistent syntax
WHERE event_date >= CURRENT_DATE - INTERVAL '30' DAY

WHERE event_date >= DATEADD(DAY, -30, CURRENT_DATE())

WHERE event_date >= DATE_SUB(CURRENT_DATE(), 30)

NULL handling

NQL follows standard SQL NULL semantics, but the underlying engine may have subtle differences. The transpiler normalizes most cases.

Migration tips

Coming from PostgreSQL/MySQL

Reference datasets through the company_data schema, by name or ID: users → company_data.users or company_data."123"
Add budget clauses to materialized views
Consider _price_cpm_usd filtering for cost control

Coming from Snowflake/BigQuery

Dataset references live under the company_data schema and can use the dataset name or numeric ID
Most functions work the same
QUALIFY is fully supported
Window functions work as expected

Coming from Spark SQL

Similar syntax for complex types (arrays, structs, maps)
UNNEST and LATERAL joins work as expected
UDFs are replaced with Narrative-specific functions

NQL Design Philosophy

Why Narrative created a purpose-built query language

NQL Syntax Reference

Complete query structure and grammar

Write Your First Query

Hands-on tutorial to get started

Functions Reference

All available functions

​What stays the same

​SELECT statements

​Aggregations and grouping

​Joins

​Subqueries and CTEs

​INSERT, UPDATE, and DELETE

​Standard functions

​What’s different

​Table references use dataset IDs or names

​Special data sources

​Price filtering

​What NQL adds

​Budget controls

​Materialized views with options

​QUALIFY clause

​DELTA tables

​Rosetta Stone integration

​Narrative-specific functions

​SQL features not supported

​Dialect differences

​Date functions

​NULL handling

​Migration tips

​Coming from PostgreSQL/MySQL

​Coming from Snowflake/BigQuery

​Coming from Spark SQL

​Related content

NQL Design Philosophy

NQL Syntax Reference

Write Your First Query

Functions Reference

What stays the same

SELECT statements

Aggregations and grouping

Joins

Subqueries and CTEs

INSERT, UPDATE, and DELETE

Standard functions

What’s different

Table references use dataset IDs or names

Special data sources

Price filtering

What NQL adds

Budget controls

Materialized views with options

QUALIFY clause

DELTA tables

Rosetta Stone integration

Narrative-specific functions

SQL features not supported

Dialect differences

Date functions

NULL handling

Migration tips

Coming from PostgreSQL/MySQL

Coming from Snowflake/BigQuery

Coming from Spark SQL

Related content