Datasets

A dataset defines a table: its columns, accepted values, and optional keys. Syncs write source rows, pipelines transform them, and projections turn them into objects.

Define a dataset

A dataset has a stable id and a schema of columns. Build each column with col(name, type).

import { col, defineDataset } from "@sixb/core"

export const rawInvoicesDataset = defineDataset("erp.invoices", {
  schema: [
    col("id", "string"),
    col("number", "string"),
    col("amount", "decimal"),
    col("currency", "string"),
    col("status", "string"),
    col("issuedAt", "timestamp"),
    col("dueDate", "date"),
    col("customer_id", "string"),
    col("project_id", "string"),
  ],
  primaryKey: "id",
  description: "Raw invoice rows from the ERP.",
})

The id erp.invoices is the name every other part of the project references.

defineDataset options

Primary keys

Use one non-nullable string column for a single-column key, or two or more for a composite key:

export const invoices = defineDataset("erp.invoices", {
  schema: [col("id", "string"), col("status", "string")],
  primaryKey: "id",
})

export const invoiceLines = defineDataset("erp.invoice_lines", {
  schema: [
    col("invoiceId", "string"),
    col("lineItemId", "string"),
    col("description", "string"),
  ],
  primaryKey: ["invoiceId", "lineItemId"],
})

Primary-key constraints:

• Every key column must exist in the schema, have type string, and be non-nullable.
• Composite keys contain at least two unique columns. Column order is significant.
• A key cannot be added, removed, changed, or reordered after the dataset is created.
• Rows must be unique by key, and a row's key is immutable. Merge-capable lake providers enforce uniqueness for keyed snapshots, appends, transforms, and merges.

For out-of-order source updates, add sequenceBy. See Source ordering for the definition and merge rules.

Nullable columns

col("amount", "decimal")
col("project_id", "string", { nullable: true })

Pass { nullable: true } when a column may be missing or null. Use the json type to keep an intentionally unstructured payload:

col("raw", "json", { nullable: true })

Sensitive columns

Pass markings to hide a column from roles that are not cleared for it. Derived datasets keep the markings of the columns they copy. See Markings.

col("amount", "decimal", { markings: [financial] })

Column types

Decimal columns reject JavaScript numbers because their exact source value may already have lost precision. Keep decimals as strings from the source, or construct typed values with decimal("...") or decimal(anExactBigInt). Do not convert a number with String(...) and assume precision is restored.

Derive a dataset

Use .derive(parent) to copy a parent's schema, then narrow it with pick or extend it with add.

import { col, defineDataset } from "@sixb/core"
import { rawInvoicesDataset } from "./erp"

// Copy the full parent schema
export const invoicesArchive = defineDataset("invoices.archive").derive(rawInvoicesDataset)

// Narrow to a subset of columns and add new ones
export const invoicesSummary = defineDataset("invoices.summary").derive(rawInvoicesDataset, {
  pick: ["id", "amount", "currency", "customer_id"],
  add: [col("settled_at", "timestamp")],
})

Derived datasets do not inherit their parent's primary key. Declare primaryKey explicitly when the derived rows preserve the same identity contract.

Read rows

sixb.datasets.readRows(dataset) streams the rows of the latest version. Pass versionId to read an earlier version, and columns, limit, or offset to read less. It returns null when the version does not exist.

const result = await sixb.datasets.readRows(rawInvoicesDataset, { limit: 100 })
for await (const row of result?.rows ?? []) {
  console.log(row.id, row.amount)
}

The caller needs a view grant on the dataset. Columns it is not cleared for are left out of columns and of every row, and listed in redactions. See Markings.

sixb.datasets.listVersions(dataset, { limit }) and getVersion(dataset, versionId) read version metadata. A version names its producer's run, and lists its inputs, only as far as the caller may view them.

Storage maintenance

Each write adds a version, and a replaced version keeps its files. Sixb removes them on its own, in every project:

export const sixb = createSixb({
  // ...providers
  lakeMaintenance: { keepVersionsFor: "14d", cleanupEvery: "6h" },
})

• The latest version of each dataset, and the versions projections applied, are always kept.
• The orchestrator requests cleanup and a lake worker runs it, so both must be running. sixb dev and sixb worker-group include the worker; a deployment that names its worker types must list lake, and sixb deploy warns when it does not. Separate sixb worker <type> processes need a sixb worker lake: the orchestrator's output, sixb check and the Console say when none has run.
• Reads running in the same process delay file deletion by about half a minute at most: past that, cleanup leaves the files to its next run.
• Small files are merged by the lake provider itself after the writes that leave them.
• Console › Lake health shows when cleanup was last requested and last started, says which of the two is missing when one is, and shows how each dataset is stored, with warnings such as a partitioned small dataset.
• sixb lake cleanup cleans up now, with the settings above unless its flags say otherwise. --dry-run measures what one pass would remove.
• Cleanup needs a lake provider that supports it, such as DuckLake. Otherwise nothing runs.

File location

Export definitions from datasets/. See Project structure for discovery rules.

Row validation

Every row written to a dataset is validated against its schema:

• A row must be a plain object and may not contain unknown columns.
• Each value must match its column's declared type.
• A non-nullable column may not be missing, undefined, or null.
• A nullable column may be omitted or set to null.

Modeling tips

• Start from the source shape. Keep source column names (account_mgr_id, dept_id) on raw datasets — it makes debugging easier.
• Mark uncertain fields nullable. Reserve json for payloads you intentionally keep unstructured.
• Name datasets by layer and source: erp.invoices, erp.customers for raw, invoices.paid for derived.
• Model money as amount + currency, never as a unit type.
• Shape clean datasets with pipelines rather than overloading the raw dataset.

Related

• Connectors — external systems datasets read from
• Syncs — pull rows into a dataset
• Pipelines — transform one dataset into another
• Projections — turn rows into ontology objects
• Lineage — the versions a version is computed from