Performance Patterns

This cookbook contains patterns for optimizing NQL query performance. Apply these techniques to reduce execution time and costs.

Filter early and aggressively

Apply filters before joins

Filter data in subqueries or CTEs before joining to reduce the amount of data processed:

-- Better: Filter before joining
WITH active_users AS (
  SELECT user_id, email
  FROM company_data."100"
  WHERE
    status = 'active'
    AND created_at >= CURRENT_DATE - INTERVAL '90' DAY
),
recent_orders AS (
  SELECT user_id, order_id, amount
  FROM company_data."200"
  WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT
  u.user_id,
  u.email,
  o.order_id,
  o.amount
FROM active_users u
INNER JOIN recent_orders o ON u.user_id = o.user_id

-- Avoid: Filtering after joining
SELECT
  u.user_id,
  u.email,
  o.order_id,
  o.amount
FROM company_data."100" u
INNER JOIN company_data."200" o ON u.user_id = o.user_id
WHERE
  u.status = 'active'
  AND u.created_at >= CURRENT_DATE - INTERVAL '90' DAY
  AND o.order_date >= CURRENT_DATE - INTERVAL '30' DAY

Use price filtering early

Add _price_cpm_usd filters to limit data costs:

SELECT user_id, email, category, event_date
FROM company_data."123"
WHERE
  _price_cpm_usd <= 1.00  -- Apply price filter early
  AND category = 'target'
  AND event_date >= CURRENT_DATE - INTERVAL '30' DAY

Use QUALIFY instead of subqueries

Deduplication with QUALIFY

QUALIFY is more efficient than subquery-based deduplication:

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

-- Avoid: Subquery approach
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

Combined filtering with QUALIFY

Apply multiple window conditions efficiently:

SELECT
  user_id,
  email,
  category
FROM company_data."123"
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1
  AND SUM(1) OVER (PARTITION BY category) >= 100  -- Only categories with 100+ rows

Optimize joins

Use fully qualified column names

Explicit column references help the query optimizer:

SELECT
  company_data."100".user_id,
  company_data."100".email,
  company_data."200".order_id
FROM company_data."100"
INNER JOIN company_data."200"
  ON company_data."100".user_id = company_data."200".customer_id

Avoid OR in join conditions

OR conditions prevent efficient join algorithms:

-- Avoid: OR in join condition
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2
  ON t1.id = t2.id OR t1.alt_id = t2.alt_id

-- Better: Use COALESCE
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2
  ON COALESCE(t1.id, t1.alt_id) = COALESCE(t2.id, t2.alt_id)

-- Or: Union separate queries
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2 ON t1.id = t2.id
UNION
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2 ON t1.alt_id = t2.alt_id
WHERE t1.id IS NULL OR t2.id IS NULL

Aggregate before joining

Pre-aggregate large tables to reduce join complexity:

-- Better: Aggregate first
SELECT
  c.customer_id,
  c.name,
  order_stats.total_orders,
  order_stats.total_amount
FROM company_data."100" c
INNER JOIN (
  SELECT
    customer_id,
    COUNT(1) AS total_orders,
    SUM(amount) AS total_amount
  FROM company_data."200"
  GROUP BY customer_id
) order_stats ON c.customer_id = order_stats.customer_id

Partitioning strategies

Partition by date for time-series data

CREATE MATERIALIZED VIEW "daily_events"
PARTITIONED_BY event_date DAY
AS (
  SELECT
    user_id,
    event_type,
    DATE_TRUNC('day', event_timestamp) AS event_date
  FROM company_data."123"
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90' DAY
)
BUDGET 100 USD

Monthly partitions for longer retention

CREATE MATERIALIZED VIEW "monthly_summary"
PARTITIONED_BY report_month MONTH
AS (
  SELECT
    DATE_TRUNC('month', event_date) AS report_month,
    category,
    COUNT(1) AS event_count,
    SUM(amount) AS total_amount
  FROM company_data."123"
  GROUP BY DATE_TRUNC('month', event_date), category
)
BUDGET 50 USD

Budget management

Set appropriate budget limits

Match budget to expected data volume:

-- Daily budget for daily refreshes
CREATE MATERIALIZED VIEW "daily_data"
REFRESH_SCHEDULE = '@daily'
AS (SELECT user_id, email, event_date FROM company_data."123" WHERE event_date = CURRENT_DATE - INTERVAL '1' DAY)
LIMIT 10 USD PER CALENDAR_DAY

-- Monthly budget for larger datasets
CREATE MATERIALIZED VIEW "monthly_data"
REFRESH_SCHEDULE = '@monthly'
AS (SELECT user_id, email, created_at FROM company_data."123")
LIMIT 500 USD PER CALENDAR_MONTH

Combine price and date filters

Maximize value by filtering on both price and recency:

SELECT user_id, email, event_timestamp
FROM company_data."123"
WHERE
  _price_cpm_usd <= 0.75
  AND event_timestamp >= CURRENT_DATE - INTERVAL '14' DAY

Efficient aggregations

Use APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT …)

APPROX_COUNT_DISTINCT is significantly faster and cheaper than COUNT(DISTINCT ...). It returns exact results for low-cardinality columns and near-exact results at scale. Use it for any query that doesn’t require a guaranteed exact count.

-- Better: Use APPROX_COUNT_DISTINCT for unique counts
SELECT
  category,
  APPROX_COUNT_DISTINCT(user_id) AS unique_users
FROM company_data."123"
GROUP BY category

-- Avoid: COUNT(DISTINCT ...) is slower and more expensive
SELECT
  category,
  COUNT(DISTINCT user_id) AS unique_users
FROM company_data."123"
GROUP BY category

Reserve COUNT(DISTINCT ...) for cases where an exact count drives business logic—for example, threshold comparisons in HAVING or CASE WHEN clauses:

-- Exact count is appropriate here because the threshold matters
SELECT category, COUNT(DISTINCT user_id) AS unique_users
FROM company_data."123"
GROUP BY category
HAVING COUNT(DISTINCT user_id) >= 5

Pre-aggregate for common queries

Create summary views for frequently queried aggregations:

CREATE MATERIALIZED VIEW "daily_category_stats"
REFRESH_SCHEDULE = '@daily'
AS (
  SELECT
    DATE_TRUNC('day', event_timestamp) AS event_date,
    category,
    COUNT(1) AS events,
    APPROX_COUNT_DISTINCT(user_id) AS users,
    SUM(amount) AS total
  FROM company_data."123"
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY DATE_TRUNC('day', event_timestamp), category
)
BUDGET 25 USD PER CALENDAR_DAY

Limit result sets

Always use LIMIT during exploration

-- While exploring data
SELECT user_id, email, category
FROM company_data."123"
WHERE category = 'test'
LIMIT 100 ROWS

-- Or with offset for pagination
LIMIT 100 OFFSET 100 ROWS

Use EXPLAIN before large queries

-- Preview before executing
EXPLAIN
SELECT user_id, email, event_timestamp
FROM company_data."123"
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90' DAY

Efficient date handling

Use DATE_TRUNC for grouping

SELECT
  DATE_TRUNC('day', event_timestamp) AS day,
  COUNT(1) AS events
FROM company_data."123"
GROUP BY DATE_TRUNC('day', event_timestamp)

Avoid functions on indexed columns in WHERE

-- Better: Compare directly
WHERE event_date >= '2024-01-01'

-- Avoid: Function on column
WHERE EXTRACT(YEAR FROM event_date) = 2024

CTEs for complex queries

Use CTEs for readability and potential optimization

WITH
  base_data AS (
    SELECT user_id, event_date, amount
    FROM company_data."123"
    WHERE
      event_date >= CURRENT_DATE - INTERVAL '30' DAY
      AND _price_cpm_usd <= 1.00
  ),
  user_metrics AS (
    SELECT
      user_id,
      COUNT(1) AS event_count,
      SUM(amount) AS total_amount
    FROM base_data
    GROUP BY user_id
  ),
  ranked_users AS (
    SELECT
      user_id,
      event_count,
      total_amount,
      RANK() OVER (ORDER BY total_amount DESC) AS rank
    FROM user_metrics
  )
SELECT user_id, event_count, total_amount, rank
FROM ranked_users
WHERE rank <= 100

Anti-patterns to avoid

Use explicit column lists (wildcards not supported)

NQL requires explicit column lists—SELECT * and COUNT(*) are not supported. See Explicit Column Selection for why this design choice improves cost transparency, governance, and query optimization.

-- Not supported in NQL
SELECT * FROM company_data."123"

-- Required: Select only needed columns
SELECT user_id, email, created_at FROM company_data."123"

-- Not supported: COUNT(*)
SELECT COUNT(*) FROM company_data."123"

-- Required: Use COUNT(1) for row counts
SELECT COUNT(1) FROM company_data."123"

Don’t use LIKE with leading wildcards when possible

-- Slow: Leading wildcard
WHERE email LIKE '%@gmail.com'

-- Better: Use REGEXP_EXTRACT or suffix column
WHERE REGEXP_EXTRACT(email, '@(.+)$') = 'gmail.com'

Don’t nest too many subqueries

-- Avoid: Deeply nested subqueries
SELECT user_id, email FROM (
  SELECT user_id, email FROM (
    SELECT user_id, email FROM (
      SELECT user_id, email FROM company_data."123"
    ) a
  ) b
) c

-- Better: Use CTEs
WITH step1 AS (SELECT user_id, email FROM company_data."123"),
     step2 AS (SELECT user_id, email FROM step1),
     step3 AS (SELECT user_id, email FROM step2)
SELECT user_id, email FROM step3

Query Optimization Guide

Detailed optimization techniques

Join Performance

Understanding join performance implications

Common Queries

Copy-paste query patterns

Complex Joins

Advanced join recipes

Overview

NQL Patterns

Use Cases

SDK Recipes

Filter early and aggressively

Apply filters before joins

Use price filtering early

Use QUALIFY instead of subqueries

Deduplication with QUALIFY

Combined filtering with QUALIFY

Optimize joins

Use fully qualified column names

Avoid OR in join conditions

Aggregate before joining

Partitioning strategies

Partition by date for time-series data

Monthly partitions for longer retention

Budget management

Set appropriate budget limits

Combine price and date filters

Efficient aggregations

Use APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT …)

Pre-aggregate for common queries

Limit result sets

Always use LIMIT during exploration

Use EXPLAIN before large queries

Efficient date handling

Use DATE_TRUNC for grouping

Avoid functions on indexed columns in WHERE

CTEs for complex queries

Use CTEs for readability and potential optimization

Anti-patterns to avoid

Use explicit column lists (wildcards not supported)

Don’t use LIKE with leading wildcards when possible

Don’t nest too many subqueries

Query Optimization Guide

Join Performance

Common Queries

Complex Joins

Overview

NQL Patterns

Use Cases

SDK Recipes

​Filter early and aggressively

​Apply filters before joins

​Use price filtering early

​Use QUALIFY instead of subqueries

​Deduplication with QUALIFY

​Combined filtering with QUALIFY

​Optimize joins

​Use fully qualified column names

​Avoid OR in join conditions

​Aggregate before joining

​Partitioning strategies

​Partition by date for time-series data

​Monthly partitions for longer retention

​Budget management

​Set appropriate budget limits

​Combine price and date filters

​Efficient aggregations

​Use APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT …)

​Pre-aggregate for common queries

​Limit result sets

​Always use LIMIT during exploration

​Use EXPLAIN before large queries

​Efficient date handling

​Use DATE_TRUNC for grouping

​Avoid functions on indexed columns in WHERE

​CTEs for complex queries

​Use CTEs for readability and potential optimization

​Anti-patterns to avoid

​Use explicit column lists (wildcards not supported)

​Don’t use LIKE with leading wildcards when possible

​Don’t nest too many subqueries

​Related content

Query Optimization Guide

Join Performance

Common Queries

Complex Joins

Filter early and aggressively

Apply filters before joins

Use price filtering early

Use QUALIFY instead of subqueries

Deduplication with QUALIFY

Combined filtering with QUALIFY

Optimize joins

Use fully qualified column names

Avoid OR in join conditions

Aggregate before joining

Partitioning strategies

Partition by date for time-series data

Monthly partitions for longer retention

Budget management

Set appropriate budget limits

Combine price and date filters

Efficient aggregations

Use APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT …)

Pre-aggregate for common queries

Limit result sets

Always use LIMIT during exploration

Use EXPLAIN before large queries

Efficient date handling

Use DATE_TRUNC for grouping

Avoid functions on indexed columns in WHERE

CTEs for complex queries

Use CTEs for readability and potential optimization

Anti-patterns to avoid

Use explicit column lists (wildcards not supported)

Don’t use LIKE with leading wildcards when possible

Don’t nest too many subqueries

Related content