NQL Budget Syntax

This article explains how to set budgets on a per-run basis in NQL queries. By applying budgets, you can control costs and prevent overspending when querying external data such as demographic or geolocation feeds. The syntax is similar to SQL but includes special keywords for budget management.

Why Use a Budget?

When purchasing data (e.g., demographics or geolocation) from an external source like narrative.rosetta_stone, you often have a price per record. Budgets let you:

• Cap the total spend for a single materialized view creation
• Limit specific subqueries referencing expensive external datasets
• Protect against unexpected costs when exploring new data sources

If the projected cost of your query exceeds the specified budget, NQL will stop retrieving additional data so you don't overshoot your spending limit.

1. Budget Syntax at the Top Level

You can specify a budget at the top level of a materialized view creation.

CREATE MATERIALIZED VIEW "enriched_user_data" AS (
  SELECT
    external_data.maid AS user_maid,
    external_data.latitude,
    external_data.longitude,
    external_data.country_code,
    internal_data.customer_segment
  FROM
    narrative.rosetta_stone external_data
  JOIN
    {{your_company_name}}."customer_segment" internal_data
  ON
    external_data.maid = internal_data.maid
)
BUDGET 1000 USD

How It Works

1. CREATE MATERIALIZED VIEW: Standard NQL syntax to build a new dataset.
2. BUDGET 1000 USD: The maximum spend (e.g., 1,000 USD) for fetching data.
3. If the query's cost goes beyond 1,000 USD, NQL will stop pulling additional records from narrative.rosetta_stone.

2. Budget Syntax in Subqueries

Sometimes, only one part of your query is particularly expensive—for instance, an enrichment subquery against a premium dataset. Use BUDGETED() to isolate that budget limit.

CREATE MATERIALIZED VIEW "country_demographics" AS (
  WITH budgeted_demog AS (
    SELECT
      maid,
      age_range,
      gender
    FROM
      BUDGETED(
        (SELECT maid, age_range, gender
         FROM narrative.rosetta_stone
         WHERE country_code = 'US'),
        500,
        'USD'
      )
  )
  SELECT
    demog.maid,
    demog.age_range,
    demog.gender,
    local_data.loyalty_score
  FROM
    budgeted_demog demog
  JOIN
    {{your_company_name}} local_data
  ON
    demog.maid = local_data.maid
)
BUDGET 100 USD

How It Works

• BUDGETED(subquery, amount, 'USD'):
  • subquery: The part you want to cost-limit (here, a demographics feed).
  • amount: The per-subquery limit, e.g., 500 USD.
  • 'USD': The currency.
• Outer BUDGET 100 USD: A second, global budget for the entire materialized view.

In this example, pulling from rosetta_stone cannot exceed 500 USD, and the entire view is further capped at 100 USD. Note that the global budget should typically be greater than or equal to any subquery budgets to be meaningful.

3. Budgeting With MERGE ON

If you want to incrementally update an internal dataset with new demographic or location information, you can use a MERGE ON query with a budget. Only matching records from the external data source are charged against your budget.

CREATE MATERIALIZED VIEW "refined_demographics" AS
SELECT
  source_data.maid,
  source_data.latitude,
  source_data.longitude,
  source_data.age_range,
  source_data.gender
FROM
  narrative.rosetta_stone source_data
MERGE ON
  source_data.maid = target.maid
WHEN MATCHED THEN UPDATE
  SET age_range = source_data.age_range,
      gender = source_data.gender
WHEN NOT MATCHED THEN INSERT (
  maid,
  latitude,
  longitude,
  age_range,
  gender
)
VALUES (
  source_data.maid,
  source_data.latitude,
  source_data.longitude,
  source_data.age_range,
  source_data.gender
)
BUDGET 2500 USD

Key Points

• Unmatched Rows: Only new records (non-matches) will consume your budget.
• Matched Rows: Typically do not add to your cost, though scanning overhead may still apply for large data volumes.
• Append Mode: Queries that include MERGE ON use Append mode automatically.
• Cost Efficiency: This approach is ideal for regular updates where you want to pay only for new data.

4. Error Handling

If you use invalid budget syntax (e.g., omitting currency, specifying a non-numeric amount, or misplacing parentheses), NQL will return an error and halt the query. Ensure that you:

• Use a numeric value for the budget (e.g., 250.0 or 1000).
• Specify the currency as 'USD' (or whichever currency is supported).
• Nest subqueries correctly when using BUDGETED().

5. Example Scenarios

5.1 Simple Budget on an Entire Query

CREATE MATERIALIZED VIEW "loyalty_enrichment" AS
SELECT
  loyalty.maid,
  loyalty.loyalty_score,
  ros.latitude,
  ros.longitude
FROM
  {{your_company_name}}."loyalty_program" loyalty
JOIN
  narrative.rosetta_stone ros
ON
  loyalty.maid = ros.maid
WHERE
  loyalty.loyalty_score > 50
BUDGET 300 USD

• Best for a straightforward join that shouldn't exceed 300 USD in costs.

5.2 Limiting a Subquery With BUDGETED()

CREATE MATERIALIZED VIEW "premium_profile" AS (
  WITH purchased_data AS (
    SELECT maid, age_range, gender
    FROM BUDGETED(
      (SELECT maid, age_range, gender
       FROM narrative.rosetta_stone
       WHERE gender IS NOT NULL),
      200,
      'USD'
    )
  )
  SELECT
    pd.maid,
    pd.age_range,
    pd.gender,
    local_data.internal_rating
  FROM
    purchased_data pd
  JOIN
    {{your_company_name}}."user_ratings" local_data
  ON
    pd.maid = local_data.maid
)

• Here, only fetching from the external rosetta_stone is capped at 200 USD.

6. Frequently Asked Questions

Q: What happens if my budget is exceeded?

A: NQL will stop retrieving new data once the budget is hit. You'll get partial results within the allowed spend.

Q: Can I set multiple budgets in the same statement?

A: Yes, you can have a top-level budget and additional budgets for specific subqueries via BUDGETED().

Q: Do matched rows in MERGE ON queries cost anything?

A: Generally, no. Only newly inserted (non-matching) records are directly charged. However, large scans can still incur overhead.

Q: Are there special permissions required for budgets?

A: Typically, if you can create materialized views, you can set budgets. Check with your admin for details.

Q: What percent of a budget does Narrative attempt to fill?

A: Narrative aims to use 95-100% of a given query's budget.

Q: Is there a lower bound for the budget or size of potential result set?

A: We don't enforce any minimum limit. Please keep in mind that querying small amounts of entries (hundreds instead of thousands/millions) can lead to underutilization of the budget.

Q: Can I change the budget for an existing materialized view?

A: You would need to recreate the materialized view with a new budget. The budget is set at creation time.

Q: Does the budget carry over if not fully used?

A: No, budgets are per-query execution and don't accumulate or carry over to future queries.

Summary

NQL's budgeting features give you precise control over spending when you purchase or enrich data:

1. Top-Level Budgets cap the entire materialized view creation.
2. BUDGETED() Subqueries let you isolate expensive feeds.
3. MERGE ON with a budget allows incremental updates, costing you only for newly inserted records.

By using these budget syntaxes, teams can confidently run queries—merging internal data with external data from narrative.rosetta_stone—without inadvertently exceeding their spend.