DataSet

DataSet manifests describe derived tables built using SQL or PRQL (Pipelined Relational Query Language). Datasets typically select from DataSource views and may depend on other datasets.

Minimal definition

The smallest well-formed DataSet: the envelope, a name, a query and the datasources the query reads. dependencies is not required by the schema, but without it the dataset cache does not notice when the underlying file changes, so set it whenever the query reads a datasource. bino lint reports a query that reads a datasource missing from the list (dataset-dependency-undeclared).

apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_by_region
spec:
  query: |
    SELECT
      region AS category,
      row_number() OVER (ORDER BY region) AS categoryIndex,
      SUM(amount) AS ac1
    FROM sales_csv
    GROUP BY region
  dependencies:
    - sales_csv

Instead of query you can write the transformation in PRQL (spec.prql) or skip it entirely with a direct source pass-through (spec.source). All attributes are listed in the Attribute Reference below.

Fields:

• spec.query - SQL query, either inline or loaded from an external file. Required if spec.prql and spec.source are not set.
• spec.prql - PRQL query, either inline or loaded from an external file. When set, takes precedence over spec.query. The PRQL is sent directly to the query engine which compiles it via the prql extension.
• spec.source - direct pass-through to a DataSource without transformation. Mutually exclusive with query and prql.
• spec.dependencies - optional list of datasource references. Can be string names or inline DataSource definitions. Inline definitions are referenced via @inline(N) syntax in queries.

External query files

For complex queries, you can store them in separate .sql or .prql files and reference them using the $file syntax. This keeps your YAML manifests clean and enables syntax highlighting in your editor.

Loading SQL from an external file

apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_summary
spec:
  query:
    $file: ./queries/sales_summary.sql
  dependencies:
    - sales_csv

Loading PRQL from an external file

apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: customer_orders
spec:
  prql:
    $file: ./queries/customer_orders.prql
  dependencies:
    - orders
    - customers

File path resolution

• Paths are resolved relative to the manifest file containing the $file reference
• Both ./relative/path.sql and relative/path.sql formats are supported
• Absolute paths are also supported but not recommended for portability

Auto-reload and caching

External query files are fully integrated with bino’s caching and hot-reload system:

• Cache invalidation: When an external SQL or PRQL file changes, the dataset cache is automatically invalidated
• Hot-reload in preview: Changes to external query files trigger automatic refresh in bino preview mode
• Dependency graph: External files appear in bino graph output for visibility

Organizing query files

A recommended project structure for external queries:

my-report/
├── bino.toml
├── manifests/
│   ├── datasources.yaml
│   └── datasets.yaml
├── queries/
│   ├── sales_by_region.sql
│   ├── monthly_revenue.sql
│   └── customer_analysis.prql
└── data/
    └── sales.csv

Simple aggregation

---
apiVersion: bino.bi/v1alpha1
kind: DataSource
metadata:
  name: sales_csv
spec:
  type: csv
  path: ./data/sales.csv
---
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_by_region
spec:
  query: |
    SELECT
      region,
      SUM(amount) AS total_amount
    FROM sales_csv
    GROUP BY region
  dependencies:
    - sales_csv

Dataset depending on multiple inputs

---
apiVersion: bino.bi/v1alpha1
kind: DataSource
metadata:
  name: sales_fact
spec:
  type: parquet
  path: ./warehouse/fact_sales/*.parquet
---
apiVersion: bino.bi/v1alpha1
kind: DataSource
metadata:
  name: currency_rates
spec:
  type: csv
  path: ./data/currency_rates.csv
---
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: revenue_eur
spec:
  query: |
    SELECT
      f.region,
      f.booking_date,
      f.amount * r.rate_to_eur AS revenue_eur
    FROM sales_fact f
    JOIN currency_rates r
      ON r.currency = f.currency
     AND r.valid_on = f.booking_date;
  dependencies:
    - sales_fact
    - currency_rates

Datasets are referenced from layouts via their metadata.name using the dataset field of charts, tables, and text components.

Using PRQL

PRQL is a modern language for transforming data that compiles to SQL. It offers a more readable, pipeline-based syntax compared to traditional SQL.

When you specify spec.prql, bino automatically loads the PRQL extension and sends your PRQL query directly to the query engine for compilation and execution.

Simple PRQL example

---
apiVersion: bino.bi/v1alpha1
kind: DataSource
metadata:
  name: sales_csv
spec:
  type: csv
  path: ./data/sales.csv
---
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_by_region
spec:
  prql: |
    from sales_csv
    filter amount > 0
    group {region} (
      aggregate {
        total_amount = sum amount,
        order_count = count this
      }
    )
    sort {-total_amount}
  dependencies:
    - sales_csv

PRQL with joins and transformations

---
apiVersion: bino.bi/v1alpha1
kind: DataSource
metadata:
  name: orders
spec:
  type: csv
  path: ./data/orders.csv
---
apiVersion: bino.bi/v1alpha1
kind: DataSource
metadata:
  name: customers
spec:
  type: csv
  path: ./data/customers.csv
---
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: customer_orders
spec:
  prql: |
    from orders
    join customers (==customer_id)
    derive full_name = f"{customers.first_name} {customers.last_name}"
    select {
      order_id,
      full_name,
      order_date,
      total
    }
    sort {-order_date}
    take 100
  dependencies:
    - orders
    - customers

For more PRQL syntax and examples, see the PRQL documentation.

Standard Dataset Schema

To work correctly with visualization components like Charts and Tables, your dataset queries should return rows that conform to the standard schema. While not all fields are required for every component, following this structure ensures proper aggregation, drill-down, and filtering behavior.

Measure Values (Scenarios)

The schema supports four parallel sets of scenario columns for comparative analysis (e.g., Actual vs Plan).

Column	Description
ac1 … ac4	Actual values (current measurements)
pp1 … pp4	Previous Period values. What a slot compares against (prior month, prior year, …) is declared per dataset with derive/assert, see Deriving previous period
fc1 … fc4	Forecast values (predictions)
pl1 … pl4	Plan/Budget values (targets)

Grouping Dimensions

These fields determine how data is hierarchically organized and sorted.

Dimension	Index Column	Description
rowGroup	rowGroupIndex	Top-level row grouping (e.g., “Revenue”, “Costs”).
category	categoryIndex	Primary data dimension (e.g., “Product A”, “Region North”).
subCategory	subCategoryIndex	Detail dimension for drill-down (creates “thereOf” rows).
columnGroup	columnGroupIndex	Column-level grouping for breaking down measures.
columnSubGroup	columnSubGroupIndex	Second column level underneath columnGroup.

Note: The two columns of a pair depend on each other in both directions. Provide the index (e.g., rowGroupIndex) whenever you provide the dimension string (e.g., rowGroup) to ensure consistent sort ordering - and likewise, an index column without its dimension string is reported by data validation.

Metadata & Control Fields

Column	Type	Description
date	string	Required for TimeCharts. ISO 8601 date (e.g., 2024-01-15) or datetime (e.g., 2024-01-15T08:30:00Z). Used for time-series axes and normalization.
operation	string	Aggregation sign: '+' (add) or '-' (subtract). Defaults to '+'. Useful for P&L structures where costs subtract from totals.
setname	string	Dataset identifier. Used by some charts to distinguish multiple query results.

Custom _ columns

Any column whose name starts with _ passes through untouched: data validation ignores it, the engine does not read it, and it reaches the rendered rows as an extra column. The one consumer today is the Table’s attributes expressions, e.g. set(_leiter). Use this namespace for anything the report needs per row that is not part of the standard schema, either in the query ('kEUR' AS _unit) or as a constant column.

Inside the _ namespace, the prefix _spec_ is reserved: columns named _spec_<kind>_<field> and _spec_any_<field> are read by bino as dataset defaults and must not be used for your own passthrough.

Example Query

Here is an example SQL query producing a compliant dataset:

SELECT
  'Revenue' as rowGroup,
  1 as rowGroupIndex,
  product_name as category,
  product_rank as categoryIndex,
  '2024-01-15' as date,
  sum(sales_amount) as ac1,
  sum(budget_amount) as pl1
FROM sales_data
GROUP BY product_name, product_rank

Built-in SQL functions

bino registers a few helper functions on the DuckDB engine - two scalar functions and one table macro - so they are available in every DataSet (and DataSource) query without any import or extension.

op(value) - sign as an operator

op reduces a numeric value to its sign as a string, the shape the operation control field (documented above) expects. Note that operation only accepts '+' and '-', so the empty string returned for 0 is reported by data validation - wrap the call (for example nullif(op(x), '')) when zero values can occur.

Input	Result
value > 0	'+'
value < 0	'-'
value = 0	'' (empty string)
NULL	NULL

It accepts any numeric type (INTEGER, BIGINT, DECIMAL, DOUBLE, …) and returns VARCHAR.

SELECT
  account_name    AS category,
  sum(amount)     AS ac1,
  op(sum(amount)) AS operation   -- '+' for revenue, '-' for costs
FROM ledger
GROUP BY account_name

iop(value) - inverse sign

iop is the inverse of op (+ becomes - and vice versa; 0 stays ''). Use it for inverted measures, where an increase is unfavorable - costs, headcount, error rates, churn - so a rising value reads as a negative signal.

Input	op	iop
value > 0	'+'	'-'
value < 0	'-'	'+'
value = 0	''	''

Absolute value

There is no bino-specific helper for absolute values - DuckDB’s built-in abs() already does it (abs(-500) → 500). Combine it with op to render a signed magnitude as text:

SELECT op(variance) || abs(variance)::VARCHAR AS signed_variance
-- variance = -500  ->  '-500'
-- variance =  500  ->  '+500'

bino_shift(src, source, shift, grain) - previous period as a table macro

bino_shift is the table macro behind derive and assert. It returns every row and column of src plus one column named shifted: the value of source on the row with the same identity one shift earlier, or NULL when there is none.

Parameter	Value
src	Name of a view or table in the session (a DataSource or DataSet name). A macro parameter cannot be a subquery.
source	The slot to read from the earlier row: one of ac1…ac4, pp1…pp4, fc1…fc4, pl1…pl4
shift	'<n> <unit>', e.g. '1 year', '1 month', '2 week', '3 day', '1 quarter'
grain	The period one row stands for: 'day', 'week', 'month', 'quarter' or 'year'

The identity of a row is every column except date and the sixteen slot columns; two rows match when their identities are equal and the period of the earlier one (date truncated to grain) equals the period of the later one minus shift. The extra column is always called shifted - a macro cannot name an output column from a parameter - so alias it to the slot you want:

SELECT * EXCLUDE (shifted), shifted AS pp2
FROM bino_shift('sales_view', 'ac1', '1 year', 'month')

Prefer derive on the DataSet unless you need the shifted value inside a larger query; the declaration also gives you the checks and the caption.

Deriving previous period

A DataSet can declare what a previous-period slot means instead of supplying it. derive makes bino produce the column, assert checks a column the query already returns. Both take the same shape, keyed by the slot (pp1 to pp4):

apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_monthly
spec:
  query: |
    SELECT region, regionIndex, ac1, pl1, date FROM sales   -- no pp column here
  dependencies:
    - sales
  derive:
    pp1: { from: ac1, shift: 1 month, grain: month } # previous month
    pp2: { from: ac1, shift: 1 year, grain: month } # same month last year
  assert:
    pp3: { from: pl1, shift: 1 year, grain: month } # pp3 comes from the query; bino checks it

Each declaration has three attributes, all required:

Attribute	Value	Meaning
from	one of ac1…ac4, pp1…pp4, fc1…fc4, pl1…pl4	The slot whose value is copied from the earlier row.
shift	<n> <unit>, n ≥ 1, unit day, week, month, quarter or year	How far back the earlier row lies.
grain	day, week, month, quarter or year	The period one row of the dataset stands for. It is declared, never inferred from the dates.

Grain says what a row is, shift says which earlier row to compare against: on a monthly table, shift: 1 month is the previous month and shift: 1 year the same month last year.

The declarations work with query, prql and source alike. A slot may appear in derive or in assert, not both (dataset-derive-conflict), and a derived slot must not be returned by the query. Two declared slots are independent of each other.

How a row is matched

For every row bino computes two keys:

• identity - the values of every column except date and the sixteen slot columns, so all dimension columns with their index twins, operation and setname;
• period - date truncated to the grain (date_trunc(grain, date::DATE); a week starts on Monday).

The slot receives the from value of the row that has the same identity and whose period equals this row’s period minus shift, provided that period plus shift gives this row’s period again. Without such a row the slot is NULL. With from: ac1, shift: 1 month, grain: month and region as the only dimension:

region	date	ac1	period	period − shift	pp1
North	2024-01-31	100	2024-01	2023-12	NULL - no such row
North	2024-02-29	110	2024-02	2024-01	100
North	2024-03-31	120	2024-03	2024-02	110
South	2024-03-31	80	2024-03	2024-02	NULL - South has no February row
West	2024-02-29	70	2024-02	2024-01	NULL - West is new in February
West	2024-03-31	NULL	2024-03	2024-02	70 - added: West has no March row, its February value is kept visible

What follows from the rule:

• Periods are compared, not dates, so month ends line up (2024-03-31 finds 2024-02-29) and the day within the period does not matter.
• Choose a shift that is a whole number of grains. A shift shorter than the grain (1 week on grain month) never matches. A month shift on grain day matches by day of month, and the round-trip condition leaves the 31st empty when the prior month is shorter.
• Every identity must have at most one row per period; duplicates are an error (see below).
• Rows before the first shifted period have no prior row: the first month of a table has no pp1, its first year no pp2. If that is every row, the build warns.
• Index twins and control columns are part of the identity: categoryIndex, rowGroupIndex, operation and setname must be the same in both periods. An index computed per period (a rank by value) changes the identity and breaks the match; compute indexes from a stable attribute.
• An identity that is new in this period keeps its row with an empty slot.
• An identity that existed one shift earlier but has no row in this period gets a row: the dimension columns of the earlier row, every measure NULL, the slot filled, and the date the data uses for that period. So a region that stopped selling still shows ac1 = NULL, pp1 = 563 and the drop stays visible. Rows are only added for periods the data covers; nothing is created after the last period. assert never adds rows, it checks the rows the query supplied.

Caption. The engine captions pp1…pp4 as PY by default. That is right for a year shift; for any other unit bino emits an Internationalization bundle for the artefact’s language that captions the slot PP. It is placed before your own manifests, so a project override of global.pp1 still wins.

Checks and diagnostics

After the query runs, bino checks every row - not the validation sample - and reports through the same channel as data validation. Four of the checks are errors that fail bino build regardless of --data-validation, because a declared expectation is not a sampled type check:

Check	Message	Outcome
Derived slot supplied	slot pp2 is already in the query result; use assert: for a supplied slot	error, fails the build even under --data-validation warn
Source column missing	slot ac1 (from of pp2) is missing from the query result, column "date" is missing from the query result, slot pp3 is asserted but missing from the query result	error, fails the build even under --data-validation warn
Duplicate identity in period	duplicate rows for identity region=North in period 2024-03-01 (grain month)	error, fails the build even under --data-validation warn
Assert mismatch	assert pp3: 4 row(s) differ from pl1 shifted by 1 year (grain month); first at identity region=North, period 2024-03-01	error, fails the build even under --data-validation warn
Derived slot empty	pp2 derived from ac1 is null on every row - the query window has no prior period	warning

The assert comparison tolerates relative noise below 1e-9; rows whose shifted value is NULL have nothing to compare and are skipped. --warn-on-query-errors downgrades the four errors like any other query failure, so preview and bino lint --execute-queries show them as warnings.

Data Validation

bino can validate query results against the standard schema at build time. This catches common data issues early:

Validation checks

Check	Description
Type validation	String fields (category, rowGroup, etc.) must be strings
Type validation	Number fields (ac1, categoryIndex, etc.) must be numbers
Enum validation	operation must be "+" or "-"
Date format	date must be ISO 8601 (YYYY-MM-DD, or YYYY-MM-DDThh:mm:ss with an optional Z or ±hh:mm offset)
Dependent required	Dimension pairs must appear together in both directions: rowGroup requires rowGroupIndex and rowGroupIndex requires rowGroup, and likewise for category, subCategory, columnGroup and columnSubGroup

The derive/assert checks are not part of this sampled validation: they run on every row, and their four error cases fail the build in every validation mode.

Enabling validation

Data validation is enabled by default in warn mode. Configure via CLI flags:

# Log warnings and continue (default)
bino build --data-validation=warn

# Treat validation errors as fatal
bino build --data-validation=fail

# Skip validation entirely
bino build --data-validation=off

For lint-only validation (without building), use --execute-queries:

bino lint --execute-queries

Sample size

For efficiency, bino validates only the first N rows per dataset. Configure via environment variable:

BNR_DATA_VALIDATION_SAMPLE_SIZE=100 bino build

Default sample size is 10 rows.

Constant columns

A value that is the same on every row - a unit, a note, a setting - does not have to be typed into the SELECT. Declare it under spec.constants and bino adds it as a _ column to every row after the query has run:

apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales
spec:
  query: SELECT region AS category, revenue AS ac1, plan AS pl1 FROM sales_fact
  dependencies: [sales_fact]
  constants:
    unit: kEUR # → _unit = "kEUR"
    spec:
      table:
        barColumns: ac1,pl1 # → _spec_table_barColumns = "ac1,pl1"
        thereof: # → _spec_table_thereof_0_rowGroup = "Revenue"
          - rowGroup: Revenue #   _spec_table_thereof_0_category = "Applications"
            category: Applications

A Table bound to sales can then show the unit with attributes: [{label: Unit, expression: "set(_unit)"}].

Flattening rules

In constants	Column(s) on the row
Top-level key unit	_unit
Nested keys spec.table.barColumns	keys joined with _: _spec_table_barColumns
Scalar (kEUR, 1000, true)	one column, the value keeps its type
List of scalars ([ac1, pl1])	one column holding the comma-joined string ac1,pl1 (the string "ac1,pl1" stays as it is)
Object, or list of objects (thereof: [{rowGroup: Revenue}])	one column per leaf, list items by zero-based index: _spec_table_thereof_0_rowGroup

Keys must be camelCase without underscores (^[a-z][A-Za-z0-9]*$), because _ is the path separator of the column name; the schema rejects anything else. ${NAME} substitution applies as everywhere in a spec.

Constants work the same for query, prql and source datasets, and for inline DataSet definitions. They are materialized in Go after the query has run, so the cached rows carry them (a changed constant invalidates the cache), and every path that shows a dataset’s rows - bino build, the daemon behind the VS Code wizard, and the MCP tools get_columns / get_rows - lists the same columns. The preview’s SQL explorer runs the dataset SQL itself and therefore does not show constant columns.

If the query already produces a column of the same name, the constant wins and the build reports a dataset warning: constants.unit shadows query column _unit.

The keys under constants.spec are checked statically by the lint rule dataset-constants-spec: constants.spec.<kind>.<field> must name one of the kind tokens text, table, chartstructure, charttime, chartscatter, chartbubble, chartbullet or any, and a field of that kind’s spec. These columns are the reserved _spec_ namespace: bino reads them as dataset defaults.

Dataset defaults

The rows a component renders may fill the settings its author left unset. A column named _spec_<kind>_<field> sets <field> on every component of that kind bound to the dataset; _spec_any_<field> sets it on every kind that has the field. Whether the column comes from the query, from a sheet, or from constants makes no difference.

---
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales
spec:
  query: SELECT region AS category, revenue AS ac1, plan AS pl1 FROM sales_fact
  dependencies: [sales_fact]
  constants:
    spec:
      any:
        scenarios: ac1,pl1 # → every bound component with a scenarios field
      table:
        barColumns: ac1,pl1 # → Tables only
        measureUnit: kEUR
---
apiVersion: bino.bi/v1alpha1
kind: Table
metadata:
  name: sales_table
spec:
  dataset: sales # every other setting comes from the rows

The Table renders with scenarios="ac1,pl1", bar-columns="ac1,pl1" and measure-unit="kEUR", exactly as if they had been written on the Table.

Grammar

• <kind> is the lowercase manifest kind: text, table, chartstructure, charttime, chartscatter, chartbubble, chartbullet, or the reserved word any. The kind-specific column beats the any column for the same field.
• <field> is the spec key exactly as written in YAML (barColumns, measureUnit, scenarios). Every spec field of the kind is a target except dataset itself. An any field is applied to every bound kind that has it and skipped for kinds that do not.
• The _spec_ prefix is matched case-sensitively; a sheet header must spell it exactly.
• A further _ in the field part addresses the inside of an object-valued field: _spec_table_thereof_0_rowGroup is the rowGroup of the first thereof entry, see Object-valued fields.

Precedence

Anything written in YAML beats anything the data says. Lowest first:

Level	Set by
engine default and auto-detection	the template engine
ComponentStyle	the selected style
dataset default	the rows of the component’s primary dataset
inherited value	selectedStyle on the page or card
the component’s own spec	the manifest, including a ref override

A dataset default only fills a field nobody wrote. Leaving a field unset is the opt-in; a field the author set to anything, even an empty list, stays as written. A component that binds several datasets takes defaults from its primary dataset, the first entry of dataset; _spec_ columns on any other bound dataset are ignored with a build warning. Components bound directly to a DataSource (dataset: $name) get no defaults.

Reading the cells

The target field’s schema type decides how a cell is read:

Field type	Accepted cell
string or enum (measureUnit, type, level)	the string
number (limit, dataFormatDigitsDecimal)	a numeric cell or a numeric string
boolean (grouped, showMeasureScale)	a boolean cell, true/false, 1/0
list of strings (barColumns, scenarios, variances)	a comma list (ac1,pl1) or a JSON array string
object or list of objects (thereof, attributes, stack)	a JSON cell, index columns, or a row marker; see Object-valued fields

Across rows, NULL and empty cells are ignored, so filling one row of a sheet is enough. A list field merges the distinct values of all rows in row order (ac1 on one row and pl1 on another become ac1,pl1). Any other field must hold the same value on every filled row.

Diagnostics

Every problem is a dataset warning attributed to the DataSet, never a failed build. It appears in bino build output and in bino lint --execute-queries, the daemon and the MCP validate_project tool as a data-validation diagnostic. The setting is dropped and the field falls back to the next level:

• measureUnit: 2 distinct values (kEUR, EUR) in dataset sales - a scalar field disagrees between rows
• _spec_tabel_barColumns: unknown kind token "tabel"; … - the kind token is not one of the eight
• _spec_table_barColumnz: Table has no spec field "barColumnz" - the field does not exist on that kind (for any: on no kind)
• _spec_table_dataFormatDigitsDecimal: cannot parse "lots" as number - the cell does not fit the field’s type

bino serve --live renders per request, so dataset defaults follow the route parameters like the rows do. Component embeds inside a DocumentArtefact’s markdown do not receive dataset defaults.

Object-valued fields

An object-valued field - the Table’s thereof, partof, columnthereof and attributes, a chart’s stack, the axis objects of scatter and bubble charts - can be filled in one of three forms. One form per field per dataset: a dataset that carries two forms for the same field raises the warning thereof: two forms (marker, index) in dataset sales and the field is dropped.

1. JSON cell. The _spec_table_thereof cell holds the value as JSON, told by its first character [ or {:

SELECT …, '[{"rowGroup":"Revenue","category":"Applications"}]' AS _spec_table_thereof FROM sales_fact

A single object with the entry’s keys is accepted for a list field ({"rowGroup":"Revenue"} becomes a one-entry list). A DuckDB list or struct column works the same way. Two special cases follow the field’s own manifest forms: the Table’s attributes also takes its object form ({"Unit":"set(_unit)"}, key order preserved), and the measure objects of scatter, bubble and bullet charts (x, y, size, share, actual, target) also take a bare measure token such as ac1.

2. Index columns. A numeric segment is a zero-based list index, the segment after it a key; a plain object takes its keys directly. This is what constants produces from a YAML list:

constants:
  spec:
    table:
      thereof:
        - rowGroup: Revenue
          category: Applications
        - rowGroup: Revenue
          category: Services
          subCategory: Consulting # narrows the drill to one detail row
    chartstructure:
      stack: { by: scenarios } # → _spec_chartstructure_stack_by

or, in SQL, 'Revenue' AS _spec_table_thereof_0_rowGroup, 'Applications' AS _spec_table_thereof_0_category, …. Indices must be contiguous from 0 (thereof: index columns must be contiguous from 0 (missing 1) otherwise), every key must exist on the entry, a required key must be present (stack needs by), and each column follows the across-rows rule of a scalar. A subGroups column takes a comma list.

3. Row marker. For a list whose entries are keyed by the row dimensions (thereof: rowGroup, category, subCategory; partof: rowGroup, category) the cell names the level to expand on the rows it sits on, and the entry is built from that row’s own dimension values. The rule can be a condition instead of a list of names:

SELECT 'Revenue' AS rowGroup, region AS category, product AS subCategory,
       revenue AS ac1, plan AS pl1,
       CASE WHEN ABS(revenue - plan) / plan > 0.10 THEN 'category' END AS _spec_table_thereof
FROM sales_fact

rowGroup	category	subCategory	_spec_table_thereof	entry
Revenue	Applications	Applications-A	category	{rowGroup: Revenue, category: Applications}
Revenue	Applications	Applications-B	category	same entry, deduplicated
Revenue	Services	Consulting	subCategory	{rowGroup: Revenue, category: Services, subCategory: Consulting}

Rows without a marker contribute nothing; entries keep row order. A level the entry does not have (subCategory on partof) and a marked row without the needed dimension value are warnings that drop the field. A marker set through constants marks every row and expands every group.

columnthereof is column-keyed: the cell holds the scenario on the rows whose columnGroup should be spread, and subGroups collects the distinct columnSubGroup values of those rows:

SELECT …, columnGroup, columnSubGroup,
       CASE WHEN columnGroup = 'DE' THEN 'ac1' END AS _spec_table_columnthereof
FROM sales_fact

→ columnthereof='[{"scenario":"ac1","name":"DE","subGroups":["Berlin","Hamburg"]}]'.

Precedence is the same as everywhere: thereof: [] written on the Table stays empty whatever the rows say, and a Table’s own list replaces the data’s list. A plain object such as stack merges key by key, so stack: {by: scenarios} on the chart keeps a mode the data supplied; write the key explicitly (mode: absolute) to override it.

Conditional inclusion with constraints

DataSet documents support metadata.constraints to conditionally include them for specific artefacts, modes, or environments.

Preview-only datasets

Include debug or diagnostic datasets only during development:

apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: debug_metrics
  constraints:
    - mode==preview
spec:
  query: |
    SELECT * FROM raw_data LIMIT 100
  dependencies:
    - raw_data

Format-specific datasets

Use different datasets for different output formats:

# Detailed dataset for print (A4/Letter)
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_summary
  constraints:
    - spec.format in [a4,letter]
spec:
  query: |
    SELECT region, product, month, revenue, costs, margin
    FROM sales_fact
    GROUP BY region, product, month
  dependencies:
    - sales_fact

---
# Summarized dataset for screen (XGA)
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_summary
  constraints:
    - field: spec.format
      operator: "=="
      value: xga
spec:
  query: |
    SELECT region, SUM(revenue) as total_revenue
    FROM sales_fact
    GROUP BY region
  dependencies:
    - sales_fact

For the full constraint syntax and operators, see Constraints and Scoped Names.

Inline DataSet definitions

Instead of defining DataSets as separate documents, you can define them inline directly within components. This is useful for simple, component-specific data transformations. An inline definition takes the same fields as a DataSet spec, including derive, assert and constants.

Inline DataSet in a Chart

apiVersion: bino.bi/v1alpha1
kind: ChartStructure
metadata:
  name: sales_chart
spec:
  dataset:
    query: |
      SELECT region, SUM(amount) as total
      FROM sales_csv
      GROUP BY region
    dependencies:
      - sales_csv
  chartTitle: Sales by Region
  chartType: bar
  # ... chart configuration

Inline DataSet with inline DataSource

You can nest inline DataSource definitions within an inline DataSet. Use the @inline(N) syntax to reference them by index:

apiVersion: bino.bi/v1alpha1
kind: ChartStructure
metadata:
  name: quick_chart
spec:
  dataset:
    dependencies:
      - type: csv
        path: ./data/sales.csv
    query: |
      SELECT region, SUM(amount) as total
      FROM @inline(0)
      GROUP BY region
  chartTitle: Sales Overview
  # ... chart configuration

The @inline(0) reference points to the first item in the dependencies array (0-indexed). When you have multiple inline DataSources:

spec:
  dataset:
    dependencies:
      - type: csv
        path: ./data/orders.csv
      - type: csv
        path: ./data/customers.csv
    query: |
      SELECT c.name, COUNT(*) as order_count
      FROM @inline(0) o
      JOIN @inline(1) c ON o.customer_id = c.id
      GROUP BY c.name

Mixing inline and named references

You can mix inline DataSource definitions with named references:

spec:
  dataset:
    dependencies:
      - sales_csv # Named reference to existing DataSource
      - type: csv # Inline definition
        path: ./data/rates.csv
    query: |
      SELECT s.*, r.rate
      FROM sales_csv s
      JOIN @inline(1) r ON s.currency = r.code

Note: Named references use their document name directly in SQL, while inline definitions use @inline(N).

Direct source pass-through

For simple cases where you want to use a DataSource directly without transformation, use the source field:

apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_passthrough
spec:
  source: sales_csv

This is equivalent to SELECT * FROM sales_csv but more explicit and efficient. The source field is mutually exclusive with query and prql. The named source is the DataSet’s dependency, so no dependencies entry is needed.

Inline source pass-through

You can also use inline DataSource definitions with source:

apiVersion: bino.bi/v1alpha1
kind: Table
metadata:
  name: raw_data_table
spec:
  dataset:
    source:
      type: csv
      path: ./data/report.csv
  # ... table configuration

How inline definitions work

When bino processes inline definitions:

1. Materialization: Inline definitions are converted to synthetic documents during YAML loading
2. Hash-based naming: Generated documents get unique names like _inline_datasource_a1b2c3d4
3. Deduplication: Identical inline definitions share the same generated document
4. Labels: Generated documents have bino.bi/generated: "true" and bino.bi/inline: "true" labels

This means inline definitions are fully compatible with caching, the dependency graph, and all other bino features.

Naming restrictions

User-defined document names must not start with _inline_ as this prefix is reserved for generated inline definitions. The lint rule inline-naming-conflict enforces this. Names starting with bino_ or _bino_ are reserved as well, for the functions and macros bino registers in every session and the views it creates for derive/assert (reserved-name-prefix).

Attribute Reference

Common Metadata

Attribute	Type	Required	Default	Description
apiVersion	string	yes	-	Must be bino.bi/v1alpha1.
kind	string	yes	-	Must be DataSet.
metadata.name	string	yes	-	Unique identifier, and the table name the query is registered under. Pattern: ^(@[a-z0-9][a-z0-9_-]*/([a-z0-9][a-z0-9_-]*/)?)?[A-Za-z0-9_]([-A-Za-z0-9_]*[A-Za-z0-9_])?$ - a plain name, optionally prefixed with a registry scope such as @acme/ or @acme/kit/.
metadata.labels	object	no	-	Key-value pairs for categorization and constraint matching.
metadata.annotations	object	no	-	Arbitrary key-value metadata, not used by the system.
metadata.description	string	no	-	Free-form description.
metadata.constraints	array	no	-	Conditional inclusion rules. See Constraints.

Spec Attributes

At least one of query, prql, or source is required. source is mutually exclusive with query and prql - combining them is rejected by the lint rule dataset-source-exclusive. Setting query and prql together is accepted by the schema, but only prql is executed (the precedence is source before prql before query). derive and assert combine with any of the three; the same slot in both is rejected by the lint rule dataset-derive-conflict.

Attribute	Type	Required	Default	Description	Sample
spec.query	string or object	conditional	-	SQL query executed against DuckDB, inline or from an external file. DataSource and DataSet names are the table names; inline dependencies are addressed as @inline(N). Required if prql and source are not set.	query: SELECT * FROM sales
spec.query.$file	string	conditional	-	Path to an external .sql file, resolved relative to the manifest. Used instead of an inline string. See External query files.	$file: ./queries/sales_summary.sql
spec.prql	string or object	conditional	-	PRQL query, inline or from an external file, compiled to SQL by DuckDB’s prql extension. Required if query and source are not set.	prql: from sales_csv
spec.prql.$file	string	conditional	-	Path to an external .prql file, resolved relative to the manifest. Used instead of an inline string. See External query files.	$file: ./queries/customer_orders.prql
spec.source	string or object	conditional	-	Direct DataSource pass-through, equivalent to SELECT * FROM <name>. A string name or an inline DataSource definition. Required if query and prql are not set. See Direct source pass-through.	source: sales_csv
spec.dependencies	array	no	-	List of DataSource dependencies. Feeds the dependency graph and the cache digest, so a changed input file invalidates the dataset.	see below
spec.dependencies[]	string or object	no	-	Either the metadata.name of a DataSource, or an inline DataSource definition referenced in the query as @inline(N) with N the zero-based index.	- sales_csv
spec.derive	object	no	-	Previous-period slots bino produces from another slot shifted back in time, keyed by pp1…pp4. The query must not return the slot. See Deriving previous period.	derive: { pp2: { from: ac1, shift: 1 year, grain: month } }
spec.derive.<pp>.from	string	yes	-	Slot the earlier row is read from: one of ac1…ac4, pp1…pp4, fc1…fc4, pl1…pl4.	from: ac1
spec.derive.<pp>.shift	string	yes	-	How far back, <n> <unit> with the unit day, week, month, quarter or year. A year shift keeps the PY caption, any other unit is captioned PP.	shift: 1 month
spec.derive.<pp>.grain	string	yes	-	The period one row stands for: day, week, month, quarter or year. Never inferred.	grain: month
spec.assert	object	no	-	Previous-period slots the query supplies and bino checks, same shape as derive. A mismatch on any row with a prior period fails the build even under --data-validation warn.	assert: { pp3: { from: pl1, shift: 1 year, grain: month } }
spec.constants	object	no	-	Values added as _-prefixed columns to every row after the query has run. Keys are camelCase without underscores; nested keys join with _, lists of scalars become a comma string, objects and lists of objects flatten by key and index. See Constant columns.	constants: { unit: kEUR, spec: { table: { barColumns: ac1,pl1 } } }

An inline DataSource - as spec.source or as an item of spec.dependencies - takes the full DataSource spec (type with the values csv, excel, parquet, inline, postgres_query, mysql_query, plus path, content, columns, connection, delimiter, sample, …). Those attributes are documented in the DataSource reference.

External file reference

spec:
  query:
    $file: ./queries/sales_summary.sql

Dependencies with inline DataSources

spec:
  dependencies:
    - sales_csv # named reference
    - type: csv # inline definition
      path: ./data/extra.csv
  query: |
    SELECT * FROM sales_csv
    UNION ALL
    SELECT * FROM @inline(1)