Dataset SQL reference

Dataset SQL runs on DuckDB. You write one read-only SELECT; the service validates it, registers the datasets in scope as relations, executes it in an isolated DuckDB session, and returns a paginated result.

#Relations in scope

Names follow the dataset rules: letters, digits, and dashes, starting with a letter. Dash names must be quoted in SQL:

Column names may contain uppercase letters. Unquoted identifiers are folded to lowercase by DuckDB, so quote mixed-case columns: "Status_Code".

#Where SQL runs

The Hub UI's SQL box is scoped to the selected dataset. Project-wide SQL is available through the API; saved views are the UI's way to combine datasets.

#Query policy

A request is rejected before execution unless it satisfies all of these:

• Exactly one statement. For direct dataset queries (QueryDataset, QueryDatasets, and exports) the text must start with SELECT (case-insensitive) and contain no semicolons. A leading WITH CTE or a leading comment is rejected by that check.
• Read-only: INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, COPY, ATTACH, and DETACH are rejected.
• No file, network, glob, or catalog access. Table functions such as read_csv, read_parquet, read_text, glob, http_get, and range are rejected, as are DuckDB replacement scans (FROM '/path/file.parquet') and information_schema.
• Every referenced relation must be registered in the query scope. A query against a different dataset, another project, or a file path fails with "not a dataset in this query scope".

Joins, GROUP BY, HAVING, window functions, UNION, and scalar functions (lower, strpos, coalesce, date_trunc, now, …) are allowed.

Saved views validate with the SQL parser rather than the SELECT-prefix check, so a view may start with WITH ... SELECT. Direct dataset queries cannot. See Saved views.

#Ordering and pagination

The service wraps your statement to paginate it:

That means:

• Pages are ordered by the first column of your projection, ascending. An ORDER BY inside your statement does not survive the wrapper — put the column you want to sort by first. Descending page order is not available through the paginated API.
• Page size: default 100, maximum 1,000 for single-dataset and project-wide queries; default 100, maximum 10,000 for saved views.
• next_page_token is an opaque cursor. Pass it back as page_token; an empty token means the last page. The cursor is an offset, not a snapshot: changing the SQL, filters, or underlying data between pages can shift rows.
• The wrapper fetches one extra row to decide whether another page exists.
• The engine has a 100,000-row materialization guard; normal page sizes are far smaller. Use export for large result sets.

If your statement includes its own LIMIT, the outer pagination applies after that limit. Prefer letting the service paginate.

#Result types

Query results are mapped to dataset value types:

Other DuckDB types (DECIMAL, HUGEINT and other wide/unsigned integers, BLOB, UUID, INTERVAL, TIME, lists, structs, maps) are rejected with an unsupported-type error. Cast unusual expressions to a supported type:

Alias calculated columns; otherwise DuckDB names them (count_star(), sum(x)) and they become the result column names.

#Current state and history

By default the dataset relation is the current logical state: latest observation per identity, stale rows excluded. Columns include your schema plus __ingested_at and __deleted.

Set includeHistory = true (UI Include history, CLI --include-history) to query every observation instead:

History keeps duplicate identities and older versions; there is no history mode for project-wide queries or saved views.

#Time ranges

ingestedAtFrom and ingestedAtTo bound __ingested_at. Like structured filters, they are applied outside your SELECT:

• The default SELECT * FROM dataset includes __ingested_at, so the time range works as expected.
• If you project a subset, include __ingested_at or filter on it inside the SQL:

• ingestedAtFrom must not be after ingestedAtTo.

#Examples

#Inspect a dataset

A bare SELECT * is rewritten to SELECT * FROM dataset for single-dataset queries.

#Filter rows in SQL

DuckDB is case-sensitive for LIKE and = on text. Use lower(host) LIKE ... or ILIKE when you need case-insensitive matching.

#Aggregate by the first column

Results are sorted by severity ascending because it is the first column. To sort the page by count instead, put it first:

#Window functions

#Join datasets (project-wide SQL)

This runs through QueryDatasets; it cannot reference saved views.

#Inspect history

Run it with include_history = true to see every observation.

#Errors

#Operational limits

Operators can tune these with BOUNTYHUB_HUB_DATASET_QUERY_MAX_ROWS, BOUNTYHUB_HUB_DATASET_QUERY_MEMORY_LIMIT, BOUNTYHUB_HUB_DATASET_QUERY_MAX_CONCURRENT_PER_USER, and BOUNTYHUB_HUB_DATASET_QUERY_PER_MINUTE.

#Related

• Structured filters — typed filters applied on top of a query.
• Saved views — reusable SQL over multiple datasets.
• API and CLI — request and response fields.
• Pushing dataset rows — pushing data with step.update_dataset.