How Renovara SQL works

What the Renovara SQL API is

The Renovara SQL API gives an organisation's own software read-only SQL access to the Australian National Electricity Market (NEM) and east coast gas market tables that Renovara holds. The data is AEMO's published market data, cleaned and refreshed daily: electricity dispatch at AEMO's five-minute interval, much of it from January 2019, and AEMO Gas Bulletin Board data by gas day.

You send one DuckDB SELECT over HTTPS. A quick query answers in the same call; a slow one returns a statement ID to poll. Results come back as rows, and every result is also a Parquet file.

It is built for organisations that serve many clients and systems from their own code: client reporting and data products, scheduled jobs and notebooks, and AI agents that write and run their own SQL. It is an enterprise plan, separate from the subscription that connects Claude or ChatGPT to Renovara through an MCP server (see How Renovara works). Both read the same tables and the same analyst guide, so a table means the same thing in each. The plan and its trial are described at renovara.co/sql-api.

Where to start

An agent or program needs nothing but the base URL to learn the API and the data. These calls need no credentials:

1. GET https://sql.api.renovara.co/v1/ says what the service is, the SQL dialect and its version, how to sign in, the trial's limits, and links to everything below.
2. GET /v1/guide?domain=nem (or domain=gas) returns the analyst guide: which table answers which question, the traps in each table, worked SQL, and the DuckDB translations. Read it before the first query.
3. GET /v1/tables lists every table; GET /v1/tables/{table} gives one table's columns and their meaning.
4. With an access token, POST /v1/statements runs a query.

The API's own description is OpenAPI 3.1 at https://sql.api.renovara.co/v1/openapi.json, with a plain-English description of every operation, field and error. Agent frameworks can build tools straight from it.

Base URL and endpoints

The base URL is https://sql.api.renovara.co/v1. Every path below is relative to it. Requests and responses are JSON, except the guide, which is markdown.

Public routes

These need no credentials.

Statement routes

These need an access token (see Authentication).

Credentials requested by an agent

These need no credentials. See Credentials for an agent's person.

Authentication

The API uses OAuth 2.0 client credentials.

1. Get credentials. On the Renovara credentials page, which the welcome email links to, the person who manages your organisation's account creates a set of credentials and downloads them as a file, renovara-sql.json. The secret is shown once and never emailed. An organisation can hold several sets, one per system, and revoke any of them.
2. Exchange them for an access token at the token URL, https://auth.api.renovara.co/oauth2/token, with grant_type=client_credentials and scope renovara-sql/query. The token URL is also in your credentials file and in GET /v1/ under authentication.token_url.
3. Send the token on every statement call as Authorization: Bearer <token>.

A token lasts 60 minutes. Cache it and reuse it for its hour, then fetch a new one: tokens are issued at a cost, so fetching one per query is wasteful and slower. The Python client does this for you.

The credentials file looks like this. Keep it private: anyone holding it can query as your organisation.

{
  "api_url": "https://sql.api.renovara.co/v1/",
  "token_url": "https://auth.api.renovara.co/oauth2/token",
  "client_id": "...",
  "client_secret": "...",
  "scope": "renovara-sql/query"
}

Credentials for an agent's person

An AI agent without credentials can ask for them on its person's behalf. It never signs anyone up itself: the person approves.

1. The agent sends POST /v1/trial/requests with {"email": "<the person's work email>", "agent": "<the agent's name>", "credentials_name": "<a name for the set>"}. Nothing is emailed.
2. The response has a user_code, a link (verification_uri_complete) and a device_code. The agent shows the person the link, which opens renovara.co/sql-api-approve. The device_code is the agent's secret: never show it.
3. The person signs in as the email the agent named, sees which agent asked and for what, and approves or denies. If they have no organisation, approving first sends them through Stripe Checkout to start a 30-day free trial (no card; Stripe asks for an address for tax), then back to the request, which is approved once the trial is set up. Until then the agent's poll answers pending.
4. The agent polls GET /v1/trial/requests/{device_code} every interval seconds. Once approved, the response holds the credentials, in the same form as the file above, returned once.

A request expires after 30 minutes.

Running a statement

Statements are submit-and-poll, so a long query never holds a connection open.

1. Submit. POST /v1/statements with {"sql": "SELECT ...", "wait_seconds": 20}. wait_seconds is how long the call waits for the result, from 0 to 25; the default is 20. The SQL can be up to 100,000 bytes.
2. Read the state. If the statement finished in that time, the response has "state": "SUCCEEDED" and the result, or "state": "FAILED" and an error. Otherwise it is PENDING or RUNNING, with a statement_id.
3. Poll. GET /v1/statements/{statement_id}, with a short pause between calls, until the state is SUCCEEDED or FAILED.
4. Cancel if you need to. POST /v1/statements/{statement_id}/cancel. Its cancel field is cancelled, requested (the query stops within a few seconds; keep polling) or finished (it had already finished).

A statement is visible only to the client that submitted it, and lasts 24 hours. A statement's response carries statement_id, state, submitted_at (Unix seconds), started_at, finished_at and query_ms once known, then result or error.

Retries. Send an Idempotency-Key header (1 to 200 characters) with each submit. A retry with the same key returns the statement already made instead of running, and counting, it twice. Use a new key for each new statement and the same key only to retry it. The Python client sends one for you.

Dry runs. EXPLAIN SELECT ... returns DuckDB's query plan without running the query. EXPLAIN ANALYZE SELECT ... runs the query and returns the plan with its timings, not the rows.

Usage. Each submit's response includes usage: your plan, the query time used and allowed, statements today and the daily limit, the statements you may run at once and, on a trial, when it ends.

Results

• result.columns lists each column's name and type; result.row_count is the number of rows.
• A result of up to 10,000 rows and 4 MB comes back inline as result.rows, an array of rows, each an array of values in column order, with rows_included: true.
• Every successful result is also a Parquet file at result.parquet_url. The link works for an hour; get the statement again for a fresh link. Results last 24 hours.
• A result over 5,000,000 rows is refused with RESULT_TOO_LARGE. Aggregate in SQL instead.
• A statement stops at 300 seconds with QUERY_TIMEOUT. Narrow the time range or the units, or aggregate before joining; see Writing fast queries.

DuckDB SQL

The API runs DuckDB, not Databricks SQL. The exact version is in GET /v1/ under dialect. The analyst guide's worked examples are written in Databricks SQL, because the guide is shared with Renovara's other services; the guide served by this API opens with the translations below, which override it where they differ.

• One read-only SELECT per statement, or EXPLAIN / EXPLAIN ANALYZE of one. Anything else is refused with QUERY_REJECTED, as is a call to a function that reports the server's configuration.
• Table names work bare (silver_dispatchis_reports_dispatch_price) or fully qualified (external_data.nemweb.silver_dispatchis_reports_dispatch_price).
• SETTLEMENT_DAY. Every time-series table has this extra integer column first, so SELECT * returns one more column than the same table does through the MCP subscription's Databricks SQL. See Writing fast queries.
• Market time. Market timestamps, such as SETTLEMENTDATE, carry no time zone: they are plain TIMESTAMP values holding AEST wall-clock time (Australia/Brisbane, UTC+10, no daylight saving). Do not shift them. Columns ending _UTC, such as SETTLEMENTDATE_UTC, are real moments, TIMESTAMP WITH TIME ZONE, shown with +00. The session runs in UTC, so now() is UTC: "the last hour" is SETTLEMENTDATE_UTC >= now() - INTERVAL 1 HOUR, and "now" in market time is CAST(current_timestamp AS TIMESTAMP) + INTERVAL 10 HOUR.
• Quotes. Strings take single quotes. Double quotes name columns.
• NULLs. ORDER BY puts NULLs last by default. Databricks puts them first in ascending order.
• Rounding and approximate percentiles can differ from Databricks in the last digit.
• Traps. The guide sets out each table's traps, such as intervention pricing and market time. Read it before the first query.

date_trunc, max_by, min_by, hour, year, concat_ws, coalesce, QUALIFY and window functions work as written.

Writing fast queries

Every time-series table the SQL API serves is a plain copy whose first column is SETTLEMENT_DAY: an INTEGER in the form yyyymmdd (for example 20261020) holding the date of the table's main timestamp. That timestamp is whichever of SETTLEMENTDATE, INTERVAL_DATETIME, DATETIME, GasDate, EFFECTIVEDATE or START_DATE the table has.

• Filter on SETTLEMENT_DAY as well as the timestamp. It lets the server skip whole files without reading them, and it is the main way to make a query fast. Keep the timestamp filter for the exact boundaries.
• It is the timestamp's calendar date, not AEMO's trading day. The interval ending 2026-10-20 00:00 has SETTLEMENT_DAY 20261020. When you select by trading day, make the SETTLEMENT_DAY range cover every timestamp you want, and let the timestamp filter make the exact cut.
• Filter on units, constraints and regions where you can. Each copy is also clustered on whichever of DUID, CONSTRAINTID or REGIONID it has, so a filter on that column lets the server skip files too.
• Name the columns you need. SELECT * returns SETTLEMENT_DAY as well, one more column than the same table through the MCP subscription's Databricks SQL, and reading fewer columns is quicker.
• Aggregate in SQL, and aggregate before joining large tables, rather than fetching rows to aggregate yourself.

For example, the average price in each region for October 2026. SETTLEMENTDATE marks the end of its 5-minute interval, as the guide explains, so October runs from the interval ending at 00:05 on 1 October to the one ending at 00:00 on 1 November, and SETTLEMENT_DAY reaches 20261101 to include that last one:

SELECT REGIONID, round(avg(RRP), 2) AS avg_price
FROM silver_dispatchis_reports_dispatch_price
WHERE SETTLEMENT_DAY BETWEEN 20261001 AND 20261101
  AND SETTLEMENTDATE > TIMESTAMP '2026-10-01 00:00' AND SETTLEMENTDATE <= TIMESTAMP '2026-11-01 00:00'
  AND INTERVENTION = 0
GROUP BY 1 ORDER BY 1

EXPLAIN SELECT ... shows the plan before you run a query, without running it.

Limits and the trial

The trial lasts 30 days. Its allowance is measured in query time, the time your statements spend running, not in a count of queries, because one query can take a hundred times as long as another. Limits apply to the whole organisation, across all its credentials.

Limits on every plan:

A statement that fails through Renovara's fault (INTERNAL_ERROR) is not charged to your allowance. When a limit is reached, the error says which one and what to do; see Errors. Prices are on renovara.co/pricing.

Examples

Python

The Python client is renovara-sql on PyPI. It caches the token for its hour, polls slow statements, sends an idempotency key with every query, and returns rows, a pandas data frame or the Parquet file.

pip install renovara-sql

from renovara_sql import Client

# What the data is: no credentials needed.
public = Client("https://sql.api.renovara.co/v1")
print(public.guide()[:2000])
for t in public.tables():
    print(t["table"], "-", t["description"][:80])

# Queries: the credentials file from the Renovara credentials page.
client = Client.from_file("~/.config/renovara-sql/acme.json")
result = client.query("""
    SELECT REGIONID, round(avg(RRP), 2) AS avg_price
    FROM silver_dispatchis_reports_dispatch_price
    WHERE SETTLEMENT_DAY BETWEEN 20261020 AND 20261027
      AND SETTLEMENTDATE > TIMESTAMP '2026-10-20 00:00' AND SETTLEMENTDATE <= TIMESTAMP '2026-10-27 00:00'
      AND INTERVENTION = 0
    GROUP BY 1 ORDER BY 1
""")
print(result.column_names, result.rows)
print(result.usage)                 # query time used, statements today
frame = result.to_pandas()          # needs pandas
result.download("prices.parquet")   # the whole result as Parquet

Client.from_env() reads RENOVARA_SQL_API_URL, RENOVARA_SQL_TOKEN_URL, RENOVARA_SQL_CLIENT_ID and RENOVARA_SQL_CLIENT_SECRET instead of a file. For control over a long query, use submit(sql), status(statement_id) and cancel(statement_id). A statement that ran and failed raises QueryFailed; a refused request raises RenovaraError. Both carry the error's code and docs_url.

curl

# From your credentials file
API_URL=https://sql.api.renovara.co/v1/
TOKEN_URL=https://auth.api.renovara.co/oauth2/token
CLIENT_ID=...
CLIENT_SECRET=...

# 1. An access token. Keep it for its hour: not one per query.
TOKEN=$(curl -s -u "$CLIENT_ID:$CLIENT_SECRET" -d grant_type=client_credentials \
  -d scope=renovara-sql/query "$TOKEN_URL" | jq -r .access_token)

# 2. Submit a statement and wait up to 20 seconds for it.
curl -s -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -H "Idempotency-Key: $(uuidgen)" \
  -d '{"sql": "SELECT count(*) FROM silver_dispatchis_reports_dispatch_price WHERE SETTLEMENT_DAY = 20261020", "wait_seconds": 20}' \
  "${API_URL}statements"
# -> {"statement_id": "...", "state": "SUCCEEDED", "result": {"columns": ..., "rows": ...}}
#    or "state": "RUNNING": poll until it is SUCCEEDED or FAILED.

# 3. Poll a slow statement, or cancel it.
curl -s -H "Authorization: Bearer $TOKEN" "${API_URL}statements/<statement_id>"
curl -s -X POST -H "Authorization: Bearer $TOKEN" "${API_URL}statements/<statement_id>/cancel"

Data source

The market data comes from the Australian Energy Market Operator (AEMO) and is used under AEMO's copyright permissions. Some reference data comes from other public sources, such as the Australian Energy Regulator; their terms continue to apply to that data. Renovara loads the data as AEMO publishes it and refreshes it daily. Where you show the data to others, credit AEMO as its source, and the Australian Energy Regulator for data that comes from it. You may pass results on to any number of your own clients, in reports, portals, AI assistants and data products; you may not hand out your credentials or resell the API itself. Use of the API is covered by the terms of service.

The data does not include the Western Australian electricity or gas markets, today's intervals before the daily refresh has loaded them, or anyone's private data, such as meter reads or settlement statements.