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.

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

1. Go to Settings → Query Datasets.
2. Click Add Query Dataset.
3. Name the Query Dataset.
4. Click Browse Sources to find the datasets you want to query.
5. Write the YAML definition.
6. Click Preview to validate the SQL and inspect sample rows.
7. 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

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

Fields

Field	What it does
schemaVersion	Use 1 for the current Query Dataset format
sources	Lists the datasets available to the SQL query
sources.type	Currently use dataset
sources.alias	The table name you will use in SQL
sources.source	Source UUID copied from Browse Sources
materializationPolicy.mode	Controls whether the result runs live or is stored for reuse
query	Read-only DuckDB SQL that selects from the source aliases you made

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

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.

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
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:

1. Select a few columns from one source.
2. Add the join.
3. Add the CTE or window function.
4. Add final aliases and sorting.
5. 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. Query Datasets should define reusable data. Query Widgets should define dashboard-specific display and filtering.

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"

Query Dataset Definitions can only use regular ClientTable sources

Query Dataset sources currently need to be regular datasets. Do not use modifiedDataset, joinedDataset, or queryDataset inside the sources list.

Preview works but a widget looks stale

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.

A column is missing in a Query Widget

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.

Next steps

• Query Widgets
• Joined Datasets
• Modified Datasets
• Filter Variables