Query Datasets

Query Datasets let you define a reusable dataset with SQL. Use them when the data shape you need does not fit neatly into a Modified Dataset or Joined Dataset, or when you want one SQL result that several widgets can share.

A Query Dataset can use regular synced datasets from any connected source. The original data might come from ConnectWise, Autotask, HaloPSA, QuickBooks, a database, a custom integration, or an uploaded source. Once it has synced into Resplendent Data, you can reference it in a Query Dataset.

When to use a Query Dataset

Use a Query Dataset when you need:

Joins across multiple datasets
Common table expressions (CTEs)
Subqueries
Window functions, such as “latest row per ticket”
Reusable business logic shared by more than one widget
A SQL-defined source that can be materialized for performance

Use a Modified Dataset when you only need normal transformations such as filtering, calculated columns, grouping, find and replace, or type conversion.

Use a Joined Dataset when you want a no-SQL join or union that can be configured through the join editor.

How Query Datasets relate to Query Widgets

Query Datasets are sources. Query Widgets are dashboard widgets.

Synced datasets -> Query Dataset -> Query Widget -> Dashboard

Put complex SQL in the Query Dataset. Then point one or more Query Widgets at that Query Dataset to display tables, charts, or metrics. See Query Widgets and Query Widget YAML.

This split is especially useful when working with AI tools. You can ask an AI assistant to help draft the Query Dataset once, then reuse the result in several smaller Query Widgets. The Query Dataset holds the cross-source logic, while each widget only has to handle display-specific filtering, grouping, and formatting.

Creating a Query Dataset

Go to Settings → Query Datasets.
Click Add Query Dataset.
Name the Query Dataset.
Click Browse Sources to find the datasets you want to query.
Write the YAML definition.
Click Preview to validate the SQL and inspect sample rows.
Click Save when the result looks right.

The editor stores the validated definition, not the raw draft text. If validation fails, the editor shows the error and the Query Dataset is not saved.

Basic YAML structure

Keep query as the last top-level key.

schemaVersion: 1
sources:
  - type: dataset
    alias: tickets
    source: 46b0bbe8-619a-4b23-bc44-f11842775640
materializationPolicy:
  mode: automatic
timezone: America/Chicago
query: |-
  select *
  from tickets

YAML field reference

Field	Required	What it does
`schemaVersion`	yes	Must be `1`
`sources`	yes	One or more Dataset sources available to the SQL query
`sources[].alias`	yes	SQL table name for this source
`sources[].type`	no	If present, must be `dataset`
`sources[].source`	yes	Dataset UUID copied from Browse Sources
`materializationPolicy.mode`	no	`live`, `materialized`, or `automatic` (the default)
`timezone`	no	IANA timezone such as `America/Chicago`. Defaults to `UTC`
`query`	yes	Read-only DuckDB SQL that selects from the aliases you declared. Keep this last

Source aliases must be simple SQL names. Use letters, numbers, and underscores, starting with a letter or underscore. Aliases must be unique.

type is optional because every Query Dataset source is a Dataset. If you include it, write dataset. Do not use modifiedDataset, joinedDataset, or queryDataset in sources.

Query Dataset SQL

The query is sandboxed and read-only. It may use:

Joins across the aliases you declared
Common table expressions (CTEs)
Subqueries
Window functions
Aggregates, filters, sorting, and limits

It may not change data, attach other databases, or read or write files.

Select from the aliases in sources, not from UUIDs. Quote dotted column names such as tickets."board.name".

Invalid sources

Do not use modifiedDataset, joinedDataset, or queryDataset in sources. A Query Dataset reads synced Datasets only.

# Invalid
sources:
  - type: modifiedDataset
    alias: filtered_tickets
    source: 46b0bbe8-619a-4b23-bc44-f11842775640

For SQL joins, declare each Dataset in sources and join the aliases in query.

Materialization policy

The materialization policy controls how Resplendent Data reads the Query Dataset.

Mode	Behavior
`automatic`	Resplendent decides whether to run live or materialize based on source count and size
`materialized`	Resplendent stores the result and refreshes it after related source updates
`live`	Resplendent runs the query when the result is needed, instead of relying on stored output

Most Query Datasets should start with automatic.

Materialized Query Datasets keep their last good result if a later refresh fails. That gives dependent Query Widgets something to show while the problem is fixed.

Materialization stores at most 10,000,000 rows. There is no YAML field to raise that cap. If a row-level query would exceed it, aggregate in the Query Dataset or narrow the date window so later analysis is not missing older months. Editor preview uses a smaller sample (100 rows by default) and does not write the stored result.

Example: latest email per ticket

This example joins tickets with an audit table, finds the latest outgoing email per ticket, and exposes a reusable result for widgets.

schemaVersion: 1
sources:
  - type: dataset
    alias: tickets
    source: 46b0bbe8-619a-4b23-bc44-f11842775640
  - type: dataset
    alias: ticket_audit
    source: b8b7f7ee-af37-45cf-bd4c-d4bbac616f30
materializationPolicy:
  mode: automatic
timezone: America/Chicago
query: |-
  with email_audit as (
    select
      ticket_id,
      enteredDate as email_sent_at,
      row_number() over (
        partition by ticket_id
        order by enteredDate desc
      ) as rn
    from ticket_audit
    where lower(coalesce(auditType, '')) like '%email%'
  )
  select
    tickets.id as ticket_number,
    tickets.summary as ticket_subject,
    tickets."board.name" as board_name,
    email_audit.email_sent_at as last_email_at
  from tickets
  inner join email_audit
    on email_audit.ticket_id = tickets.id
   and email_audit.rn = 1

Notice the quoted column name:

tickets."board.name"

Many integrations flatten nested fields into column names like board.name. In SQL, board.name means “the name field on board.” If the actual column name contains a dot, wrap it in double quotes.

Previewing results

Use preview while editing. Preview checks:

YAML syntax
Source access
Source aliases
Read-only SQL rules
Query execution
Output columns and sample rows

Preview does not save or materialize the Query Dataset. It is a safe way to test the definition before saving.

Refresh behavior

A Query Dataset tracks every source dataset it depends on. When any related source updates, Resplendent can refresh the Query Dataset using the latest available rows from all sources.

Example:

Tickets sync every 1 minute.
Ticket audit trail syncs every 15 minutes.
A Query Dataset joins both.

The Query Dataset can refresh when Tickets update. It does not wait for the 15-minute source to update at the same time. This keeps dependent widgets as fresh as the most frequently updated source allows.

For two slower sources, such as one that syncs every 3 hours and one that syncs every 6 hours, the same rule applies. A change in either source can refresh the Query Dataset and then update widgets that use it.

Working with AI assistants

Query Datasets work well with AI-assisted authoring because the full definition is plain YAML and SQL. When asking an assistant for help, include:

The current YAML definition
The source names and UUIDs
The important columns and their data types
The business question you want answered
Any preview error you are seeing

Good prompt example:

Build a Resplendent Query Dataset Definition.

Sources:
- tickets, dataset 46b0bbe8-619a-4b23-bc44-f11842775640
- ticket_audit, dataset b8b7f7ee-af37-45cf-bd4c-d4bbac616f30

Goal:
Return one row per ticket with the latest outgoing email timestamp. Use a CTE
and window function if needed. Quote dotted column names like "board.name".

The editor’s Copy AI Instructions button can also provide guidance to paste into a chat.

Best practices

Give output columns simple names

Use aliases that are easy to reference later:

select
  tickets."board.name" as board_name,
  count(*) as ticket_count
from tickets
group by board_name

Then a Query Widget can refer to board_name and ticket_count without quoting integration-specific field names.

Keep source aliases readable

Use names like tickets, invoices, or ticket_audit, not a, b, or source2. This makes the SQL easier to review and easier to hand to an AI assistant.

Start with a small preview

Build the result in stages:

Select a few columns from one source.
Add the join.
Add the CTE or window function.
Add final aliases and sorting.
Preview again before saving.

Move dashboard-specific filters to Query Widgets

If the filter depends on a dashboard Filter Variable, leave it out of the Query Dataset and apply it in the Query Widget through a Query Parameter. Query Datasets should define reusable data. Query Widgets should define dashboard-specific display and filtering.

That rule is separate from using a Query Dataset as a Filter Variable Source. A saved Query Dataset may populate Dynamic or Dynamic Date Range Filter Variable options from its result columns. Option-source population does not add Query Parameters or Filter Variable bindings to the Query Dataset Definition—it only feeds the Filter Variable’s option list. See Filter Variables.

Troubleshooting

`Referenced table "board" not found`

The query probably used an unquoted dotted column like board.name.

Use:

"board.name"

Or, when using a source alias:

tickets."board.name"

Source type is not allowed

Query Dataset sources must be regular Datasets. Do not use modifiedDataset, joinedDataset, or queryDataset in the sources list. If you include type, it must be dataset.

Check whether the Query Widget has saved and refreshed after the Query Dataset changed. Query Dataset preview is immediate, but saved dashboard widgets update through the normal refresh worker.

Make sure the Query Dataset query returns the column and gives it the same name the Query Widget uses in its display or Widget Query.