Purpose

This article covers building a custom dataset — a SQL view written for a widget when none of the governed, centrally-maintained datasets have the right shape of data. It covers when to reach for a custom dataset instead, how to inspect existing datasets first via the Dataset Model, the SQL editor and validation rules the platform enforces, and how reuse works across widgets.

Audience

• BI Analyst — primary author, writes and validates the SQL.
  
• Admin — needs authoring privileges to create dashboards/widgets in the first place.

The following video provides a quick walkthrough of this process.

Configuration

1. Understand what a dataset is

1. Datasets are predefined SQL views that act as tables when building widgets — they sit between the raw underlying tables (positions, applications, server logs, etc.) and the widget builder, and every measure, filter, date/time anchor, and drill-down configured on a widget runs against the chosen dataset.
   
2. The dataset determines which columns the widget builder offers in its dropdowns, so the dataset chosen should match the shape of the question the widget needs to answer.

2. Check the governed dataset catalogue first

1. In the widget builder, click the Dataset dropdown to see the datasets already available in your tenant — examples include application_x_positions_sr, Time to fill a position - SR, Recruitment Funnel - SR, recruitment_funnel_source_mapping, AI interview, ta_feature_usage, and positions_x_ai_interviews.

2.These are governed — schema, column types, and refresh cadence are managed centrally — so prefer them whenever possible for consistency across widgets and dashboards.
3.Before creating anything custom, check whether the same shape can be achieved by combining an existing governed dataset with widget-level filters and Group by, since custom datasets add ongoing maintenance overhead that becomes your responsibility rather than the analytics team's.

3. Inspect a dataset via the Dataset Model

1. Below the Dataset dropdown, click View dataset schema to open the Dataset Model — a read-only, side-by-side reference with every tenant dataset listed on the left and the selected dataset's structure on the right.
   
2. In the default Dataset schema view, review: the dataset name (renameable inline via the pencil icon), the source path breadcrumb showing which raw schemas it joins (e.g. customer_views.positions • customer_views.applications), and a column table listing each column's name, data type, and whether it's usable as a Dimension, Measure, or both.

3.Toggle to the SQL view (top-right) to see the raw SQL definition (a WITH-clause CTE followed by SELECT) — this view is read-only for governed datasets (indicated by a lock icon) and exists purely for inspection and as a reference pattern for writing your own.
4.Use schema view for quick column-name lookups; switch to SQL view when a column's meaning isn't obvious from its name alone.

4. Open the custom dataset builder

1. In the widget builder, click the Dataset dropdown.
   
2. Select "+ Create custom dataset" — the first option in the list. This opens the Create custom dataset modal, a two-step flow: define the dataset here, then return to the widget builder with the new dataset pre-selected.

5. Fill in the dataset definition

1. Click the pencil icon next to Add title to name the dataset — keep it short and descriptive (e.g. "Hires joined with recruiter and source"). This becomes the label shown in the Dataset dropdown afterward.
   
2. Click the pencil icon next to Add description to record the dataset's grain and what it returns (e.g. "one row per application, with recruiter, source, and position metadata joined in") — this is what colleagues will read to decide whether to reuse it.
   
3. Write the query in the SQL Editor — a complete SELECT statement returning a tabular result set, using standard StarRocks SQL syntax. This result becomes what downstream widget measures, filters, and date/time anchors query against.
   
4. Use the Dataset model link in the top right of the modal as the authoritative reference for underlying table and column names while writing the SQL.

6. Follow the validation rules

The platform enforces four rules on the outer SELECT when you click Next, because a dataset must stay a row-level tabular view — aggregation belongs at the widget level, not the dataset level:

1. No aggregate functions in the outer SELECT — COUNT, SUM, AVG, MIN, MAX, etc. aren't allowed in the outermost SELECT (aggregates inside a sub-query or CTE are fine). Aggregation happens later, in the widget's Define Measure step (e.g. SUM(is_hired) AS total_hires).
   
2. No GROUP BY in the outer query — grouping is reserved for the widget builder's Group by field; the outer query must return one row per record.
   
3. Every computed column needs an alias — use AS on any expression (e.g. COUNT(*) AS application_count); a bare expression will fail validation.
   
4. No SELECT * from table — explicit column names must be defined to avoid pulling in unused columns.

7. Submit and handle validation results

1. Click Next. The SQL is sent to the engine for validation — the four rules above plus a standard SQL compile check.
   
2. If it passes, the modal closes and the widget builder reopens with the new dataset selected — its columns immediately populate every dataset-driven dropdown (filter Column, date/time column, X-axis, Y-axis, Group by, drill-down).
   
3. If it fails — an aggregate in the outer SELECT, a GROUP BY in the outer query, a missing alias, or a compile error — an inline error identifies the issue in the editor. Fix the query and click Next again.
   
4. Clicking Back instead discards the in-progress dataset entirely — title, description, and SQL are not preserved.

8. Reuse thoughtfully

1. Once created, the custom dataset appears in the Dataset dropdown for any new widget on this dashboard.
   
2. If multiple widgets need the same shape of data, reuse the same custom dataset rather than creating a near-duplicate for each — this is more efficient since the compiled query is shared, and easier to maintain than several near-identical custom datasets.
   
3. Note there's no cross-dashboard dataset library — a custom dataset created here isn't available to widgets on a different dashboard.