SQL Queries

A model project uses SQL in its data fetchers, sensitivities, run datetime query, input aggregations, and views. This page covers which SQL dialect each query is written in and the parts of pd4castr that behave differently from a plain database. Each query’s own page covers what it must return.

Dialect

Data fetcher queries run in PostgreSQL against pd4castr’s AEMO MMS database. Every other query runs in DuckDB, so write it in DuckDB SQL. The DuckDB SQL reference covers the syntax and functions available.

Data fetcher queries

The check and fetch queries of an AEMO MMS fetcher read tables under the mms schema, such as mms.dispatchprice. Both run in a read-only session with a 10-second statement timeout, so a query that scans too much data fails instead of returning late. When you publish the model, the platform plans both queries with EXPLAIN without running them, and rejects the model if either query references a table or column that doesn’t exist.

Table names

How a query refers to an input depends on where the query runs:

  • Sensitivity, run datetime, and input aggregation queries use the input’s key as the table name, so an input with "key": "demand" is the table demand.
  • View queries and the Data Explorer prefix every table by its kind: input_<key>, dataset_<key>, and output. The same input is input_demand there.

The platform rewrites a key into a valid table name. Each - in a key becomes _minus_, so an input with "key": "spot-price" is the table spot_minus_price, or input_spot_minus_price in a view.

Next steps