# Write a recipe

Source: https://mostlyright.md/docs/build/write-a-recipe/

Write the one JSON file that names the dataset, the question, the table, the sources, the transform and the checks.

`mr-data recipe FILE` sends that file. One transaction registers the dataset, the question, every source, every connector configuration and the table plan. The backend canonicalizes the document, digests it, and returns the identifiers it derived.

Field-by-field bounds are in [Recipe document](/docs/reference/recipe/).

## The eight members

All eight are required, and the backend refuses anything else in the object.

| Member | What it is |
| --- | --- |
| `dataset` | Which dataset this table belongs to. |
| `question` | The question the table answers, in a sentence. |
| `table` | The table's name, the columns whose tuple is one row, and its columns. |
| `sources` | Where the rows come from. One entry per source, 1 to 256. A collection source is one entry however many pages it covers. |
| `transform` | How the sources become the table. |
| `checks` | What has to hold about the result. |
| `units` | What the numbers are measured in. |
| `timezone` | The timezone every timestamp in the table is expressed in. `UTC`. |

## A complete worked example

Two yearly CSV files from one publisher, unioned into one daily table, with four declared checks.

```json title="recipe.json"
{
  "dataset": {
    "id": "6b2f1c94-6c3a-4d5e-9a2b-0f7c1d3e5a80",
    "name": "Prague airport temperature history, 2024-2025"
  },
  "question": {
    "text": "What were the daily maximum and minimum air temperatures at Prague Airport on each day of 2024 and 2025?"
  },
  "table": {
    "name": "lkpr_daily_temperature",
    "description": "One row per calendar day at LKPR, January 2024 to December 2025.",
    "grain": ["station", "observed_on"],
    "columns": [
      {
        "name": "station",
        "type": "string",
        "nullable": false,
        "description": "The ICAO identifier of the reporting station.",
        "presentation": {"chart": "none"}
      },
      {
        "name": "observed_on",
        "type": "date",
        "nullable": false,
        "description": "The day the readings were taken.",
        "presentation": {
          "chart": "timeline",
          "bucket": "month",
          "story": "The archive runs unbroken across both years."
        }
      },
      {
        "name": "max_temp_c",
        "type": "decimal",
        "nullable": true,
        "description": "The highest air temperature recorded that day.",
        "presentation": {"chart": "histogram", "bins": 10}
      },
      {
        "name": "min_temp_c",
        "type": "decimal",
        "nullable": true,
        "description": "The lowest air temperature recorded that day.",
        "presentation": {"chart": "histogram", "bins": 10}
      },
      {
        "name": "observation_count",
        "type": "integer",
        "nullable": false,
        "description": "How many hourly observations that day's readings were computed from."
      }
    ]
  },
  "sources": [
    {
      "name": "lkpr_2024",
      "description": "Hourly airport weather observations for the 2024 calendar year.",
      "source_class": "user_url",
      "data_classification": "public",
      "locator": {
        "kind": "https_url",
        "display_locator": "https://mesonet.example.org/asos.csv?station=LKPR&year=2024"
      },
      "rights_claim": {
        "claimed_basis": "public_domain_asserted",
        "claim_evidence_digest": "sha256:2222222222222222222222222222222222222222222222222222222222222222",
        "claim_note": "Published without restriction by the operating agency."
      },
      "connector": {
        "adapter_id": "public.https",
        "credential_mode": "none",
        "origin": "https://mesonet.example.org"
      },
      "limits": {
        "max_source_bytes": 3145728,
        "max_rows": 200000,
        "max_requests": 2
      },
      "closed": true
    },
    {
      "name": "lkpr_2025",
      "description": "Hourly airport weather observations for the 2025 calendar year.",
      "source_class": "user_url",
      "data_classification": "public",
      "locator": {
        "kind": "https_url",
        "display_locator": "https://mesonet.example.org/asos.csv?station=LKPR&year=2025"
      },
      "rights_claim": {
        "claimed_basis": "public_domain_asserted",
        "claim_evidence_digest": "sha256:2222222222222222222222222222222222222222222222222222222222222222",
        "claim_note": "Published without restriction by the operating agency."
      },
      "connector": {
        "adapter_id": "public.https",
        "credential_mode": "none",
        "origin": "https://mesonet.example.org"
      },
      "limits": {
        "max_source_bytes": 3145728,
        "max_rows": 200000,
        "max_requests": 2
      }
    }
  ],
  "transform": {
    "engine": "duckdb_sql",
    "steps": [
      {
        "step_id": "hourly",
        "description": "Both years as one relation, cast off the all-text CSV binding.",
        "sql": "SELECT station, CAST(valid AS DATE) AS observed_on, TRY_CAST(tmpc AS DECIMAL(5,2)) AS temp_c FROM lkpr_2024 UNION ALL SELECT station, CAST(valid AS DATE) AS observed_on, TRY_CAST(tmpc AS DECIMAL(5,2)) AS temp_c FROM lkpr_2025"
      },
      {
        "step_id": "daily",
        "description": "One row per station per day.",
        "sql": "SELECT station, observed_on, max(temp_c) AS max_temp_c, min(temp_c) AS min_temp_c, CAST(count(temp_c) AS INTEGER) AS observation_count FROM hourly GROUP BY station, observed_on"
      }
    ]
  },
  "checks": [
    {
      "check_id": "one_row_per_station_day",
      "kind": "key_uniqueness",
      "enforcement": "required",
      "columns": ["station", "observed_on"],
      "description": "One row per station per day."
    },
    {
      "check_id": "max_temp_present",
      "kind": "null_ceiling",
      "enforcement": "required",
      "columns": ["max_temp_c"],
      "max_null_ppm": 20000
    },
    {
      "check_id": "max_temp_plausible",
      "kind": "value_range",
      "enforcement": "required",
      "columns": ["max_temp_c"],
      "min_value": "-50",
      "max_value": "55"
    },
    {
      "check_id": "two_years_of_days",
      "kind": "row_expectation",
      "enforcement": "advisory",
      "min_rows": 700,
      "max_rows": 740
    }
  ],
  "units": [
    {"column": "max_temp_c", "unit": "Cel"},
    {"column": "min_temp_c", "unit": "Cel"},
    {"column": "observation_count", "unit": "{count}"}
  ],
  "timezone": "UTC"
}
```

This recipe is resync-only. `lkpr_2024` is closed, and `lkpr_2025` declares no continuation at all,
so Studio refuses a scheduled refresh before it fetches anything. Give `lkpr_2025` a `window` whose
`request` names the parameters carrying time, and whose `merge.partition.column` is a column this
table declares. The open year then refreshes while the closed year reuses its sealed bytes. [Publish
and keep it fresh](/docs/build/publish-and-refresh/) carries both gates.

## `dataset`

| Member | Required | What it is |
| --- | --- | --- |
| `name` | yes | The dataset's name. At most 200 characters in the document. `mr-data dataset --name` caps at 160. |
| `id` | no | The dataset this recipe binds to, when one already exists. |
| `description` | no | Markdown, up to 4000 characters. |

Always carry `id`, the identifier `mr-data dataset create` printed. With it, the registration attaches to the page somebody has been watching. Without it, the backend resolves a container from `(workspace, name)` and mints a second page. A dataset in another workspace is refused `409 DATASET_MISMATCH`. The client refuses a non-UUID or a non-canonical UUID before anything is sent.

A document that carries `id` leaves `description` out. A registration that states one writes over the description on the page, including one a person edited by hand. `mr-data dataset set DATASET_ID --description-file F` is the route for a dataset that already exists, and it writes under the version it read. State `description` only where the registration mints the dataset.

## `question`

One member, `text`, required, up to 4000 characters. Write the question the table answers, not the task you were given.

## `table`

| Member | Required | What it is |
| --- | --- | --- |
| `name` | yes | Lowercase snake_case, at most 63 characters, unique within its dataset. |
| `grain` | yes | The column names whose tuple is one row, as a list, 1 to 32 entries. |
| `columns` | yes | The columns, in order, 1 to 512. |
| `description` | no | A sentence about the table. Give it the grain and the window. |

`grain` is a list of column names, not a sentence. One row per city per day is `["city", "day"]`, and `columns` has to declare every name in it. A `key_uniqueness` check that names no columns of its own falls back to `grain`. A grain written as prose leaves that check with nothing to check.

Each column takes `name` and `type`, and may take `nullable`, `description` and `presentation`. The types are `string`, `integer`, `decimal`, `float`, `boolean`, `date`, `timestamp` and `json`.

### Write a description on every column

`description` is optional to the schema and counted by the client. The registration receipt, the record of what the backend registered, reports `columns_without_description` and a warning naming up to twelve of them. It is never an error. The dataset page draws an undescribed column as a name and a type, with nothing beside it to say what its numbers mean. Revise it before the run is presented.

Write it for somebody who has never seen the source: `The highest air temperature recorded that day.`

### `presentation` decides what a reader sees

| Member | Required | What it is |
| --- | --- | --- |
| `chart` | yes | One of `histogram`, `timeline`, `top_values`, `share`, `none`. |
| `story` | no | The sentence a reader should be able to say after looking, up to 240 characters. |
| `bins` | no | 4 to 12. The bin count on a `histogram`, the bucket ceiling on a `timeline`, refused on anything else. |
| `bucket` | no | `hour`, `day`, `week`, `month`, `quarter`, `year`. `timeline` only. |

Each chart takes only some types.

- `histogram` takes `integer`, `decimal`, `float`.
- `timeline` takes `date`, `timestamp`.
- `share` takes `boolean`.
- `top_values` takes anything but `json`.
- `none` takes anything.

Registration refuses a chart the column's type cannot carry, and names the column.

Choose the chart from what the column holds.

- Identifiers and free text: `none`.
- Measurements: `histogram`.
- Event times: `timeline`, with `bucket` matched to the grain the rows arrive at.
- Categories: `top_values`.
- Flags: `share`.

Write `story` about the data, such as "rain is concentrated in a handful of days and most days record almost none", never about the chart. Leave it out rather than write a caption.

A column with no `presentation` still gets a chart, picked from the declared type, and the dataset page records it as the builder's choice.

## `sources`

| Member | Required | What it is |
| --- | --- | --- |
| `name` | yes | Lowercase snake_case. The SQL, the run's progress and the receipt all call the source by this name. |
| `source_class` | yes | `mostlyright_sdk`, `external_adapter`, `user_file`, `user_url`, `user_api`, `database_extract`, `webhook`, `stream`. |
| `data_classification` | yes | `public`, `internal`, `confidential`, `restricted`. |
| `locator` | yes | `kind` and `display_locator`, plus optional `connector_contract_version`. |
| `rights_claim` | yes | `claimed_basis` and `claim_evidence_digest`, plus optional `claim_note`. |
| `connector` | unless `connection` | `adapter_id` and `credential_mode`. `origin`, `parameters`, `generic_api` and `stream_connector_document_digest` are conditional. |
| `limits` | yes | `max_source_bytes` required. `max_rows` and `max_requests` optional. |
| `description` | no | One sentence, up to 240 characters, saying what this source is. |
| `credential` | no | A `secret_name`, or a saved connection reference. |
| `connection` | no | Shorthand for a granted saved connection. It replaces `connector` and `credential`. |
| `binding` | no | `text` (every column `VARCHAR`, the default) or `typed`. |
| `closed` | no | `true` when this address is finished and a refresh must not fetch it again. |
| `window` | no | Which query parameters or path placeholders carry time, so a refresh asks for a bounded range. |
| `collection` | conditional | Required by `public.https.collection@2.0.0`, refused elsewhere. |

`locator.kind` is one of `sdk_connector`, `https_url`, `artifact`, `document_capture`, `webhook`, `stream`, `database`. `rights_claim.claimed_basis` is one of `unknown`, `prohibited`, `permission_asserted`, `public_domain_asserted`, `contractual_license_asserted`, `terms_of_service_asserted`. `claim_evidence_digest` is the `sha256:` wire form.

The adapters are `external.openligadb`, `public.https`, `public.https.collection@2.0.0`, `snapshot.artifact@1.0.0`, `authenticated.https.api_key@1.0.0`, `authenticated.https.api_key@2.0.0`, `authenticated.https.connector@3.0.0`, `stream.wss@1.0.0`, `stream.wss@2.0.0` and `document.projection@1.0.0`. See [Source kinds and connectors](/docs/reference/sources/).

Where a source states both an `origin` and an `https_url` locator, the two have to name the same host. Each source may reach its own host and no other, even when a sibling source declares the second one.

### Say what each source is

Every source gets a `description`. Write one sentence saying what that source is: `Daily climatological reports, the official highs and lows.` The dataset page draws it beside the endpoint, under the publisher.

Write it about the data, never about the run: `Hourly airport weather observations`, not `the 2024 half of the window` or `unioned in step one`. Do not restate the address in words. Sources that split one window across the same endpoint all get the same sentence, because the years are already in their names.

### `closed`

`closed: true` says the bytes behind this address cannot change any more. It fits a year that has ended, a published archive, or a file with a version in its name. Write it only where it is true whenever anybody asks.

A query naming a date range that has ended is not a promise that the bytes are finished. Publishers correct historical public data and republish it under the same dates. A source stating no `closed` is revalidated where the publisher offers a validator, and re-fetched where it does not.

A source that declares a `window` is never closed. A source states at most one of the two members.

`closed: true` on its own never makes a table refresh. A plan whose every source is closed has
nothing to acquire, so Studio returns `RESYNC_REQUIRED` before acquisition. A closed source earns
its reuse beside a source that declares a request window.

## `transform`

| Member | Required | What it is |
| --- | --- | --- |
| `engine` | yes | `duckdb_sql` or `none`. `none` is a registered value no run executes. |
| `steps` | yes | The steps, in order, at most 64. |

Each step has a lowercase snake_case `step_id` and its `sql`, and may carry a `description`. Two steps may not share an id. A `step_id` equal to a source's `name` is refused rather than resolved.

Each source becomes one relation under the `name` it declares, and each step becomes a relation under its own `step_id`. A later step reads an earlier one by writing its id. Steps run in array order, and the last step's result is the table.

One read-only statement per step. A step is one `SELECT`. The engine refuses a second statement, `ATTACH`, `COPY`, `INSTALL`, `PRAGMA`, any statement that writes, and any step that reads a file or a URL. It gets the acquired sources and nothing else.

Transforms cannot read the clock. The engine rejects `current_date`, `current_timestamp`, `localtime`, `now()`, `today()` and `random()` by name before the step runs. A later run re-derives the table and compares it byte for byte. A clock-dependent step may agree with itself all day and fail on the next refresh. Qualify a publisher's column spelled like one of those: `observations.current_date`.

`timezone` has to be `UTC`. A document naming another zone registers and then refuses on its first run.

### What the statement must return

Every acquired source arrives as all-text CSV, `read_csv(header = true, auto_detect = false, delim = ',')` with every column `VARCHAR`. Each declared type is an explicit cast, checked against what the engine returns.

| Declared | The engine type the statement must return |
| --- | --- |
| `string` | `VARCHAR` |
| `integer` | `TINYINT`, `SMALLINT`, `INTEGER`, `BIGINT`, not the widest |
| `decimal` | `DECIMAL(p,s)`, never `FLOAT` or `DOUBLE` |
| `boolean` | `BOOLEAN` |
| `date` | `DATE` |
| `timestamp` | `TIMESTAMP WITH TIME ZONE` |
| `json` | `JSON` or `VARCHAR` |

`TRANSFORM_COLUMN_TYPE_MISMATCH: column obs_time is declared timestamp and the statement returns TIMESTAMP` is the time-zone row. It names the same word twice because the difference is the zone rather than the type. Build one from an epoch with `to_timestamp(CAST(x AS BIGINT))`, or from text by concatenating the offset: `CAST(concat(ts, ':00+00') AS TIMESTAMP WITH TIME ZONE)`. `ICU` is not loaded, so `AT TIME ZONE` is unavailable.

`TRY_CAST('5.00' AS INTEGER)` is null, because a decimal spelling does not parse as an integer. Go through decimal first: `CAST(TRY_CAST(x AS DECIMAL(6,2)) AS INTEGER)`. Use `TRY_CAST` where an unparseable value should become null, and a bare `CAST` on a key column where the run should stop instead.

## `checks`

A check that fails is the run failing, with the check named. The worker validates the dialect at the acquire stage, not at registration, so a wrong shape costs a queued run.

| Member | Required | What it is |
| --- | --- | --- |
| `check_id` | yes | A lowercase snake_case name. |
| `kind` | yes | `key_uniqueness`, `null_ceiling`, `value_range`, `continuity`, `row_expectation`. |
| `enforcement` | yes | `required` or `advisory`. A failing `required` check stops the run. |
| `columns` | conditional | Which columns it is about, at most 64. |
| `max_null_ppm` | conditional | 0 to 1,000,000. |
| `min_value`, `max_value` | conditional | The range a value must be inside, as strings. |
| `min_rows`, `max_rows` | conditional | How many rows to expect. |
| `max_gap_seconds` | conditional | The longest gap a continuous series may have. |
| `description` | no | What the check is for, in a sentence. |

| `kind` | Columns | Also required |
| --- | --- | --- |
| `key_uniqueness` | any number, falling back to `grain` | nothing |
| `null_ceiling` | exactly one | `max_null_ppm` |
| `value_range` | exactly one | both `min_value` and `max_value` |
| `continuity` | exactly one | `max_gap_seconds` |
| `row_expectation` | none, and naming one is refused | at least one of `min_rows`, `max_rows` |

`DECLARED_CHECK_INVALID: check X reads exactly one column and names 3` means write one check per column. Bounds are strings because no fractional number may appear anywhere in the document: `"min_value": "-50"`.

The backend settles kind, parameters, domains, duplicate identifiers and every column name against the table's declared columns before a byte is fetched. Only the evaluation needs rows.

## `units`

One entry per column that has a unit: a `column` and its `unit`. A list rather than a map, so the declaration order is part of the document that gets digested.

The unit codes are UCUM expressions, not a fixed list of names. `cm`, `ug/m3` and `km/h` are built from atoms and prefixes, and validation resolves the expression. `.` multiplies and `/` divides. A symbol may carry one integer power from −9 to 9, and nothing outside that range. A `{...}` annotation of up to 32 characters labels a quantity without changing its dimension. Division binds one term at a time, left to right: `J/kg.K` is joules per kilogram multiplied by kelvin, and joules per kilogram-kelvin is `J/kg/K`.

The two temperature scales are offsets rather than multipliers, and they are carried as `Cel` and `[degF]`. `{count}` is a count, `1` is a dimensionless ratio, and `%` is a percentage. `none` is not a code a recipe may declare. A column with nothing to convert is left out of `units`.

Do not invent a unit name. A code outside the accepted subset is refused with the construct that put it there. Every column that measures a physical quantity carries a unit entry. A temperature, a distance, a duration or a rate without one is a number nobody can convert or compare. Full list in [Units and vocabulary](/docs/reference/units/).

## Six rules that cost a round trip

1. One recipe document, one call. `mr-data recipe FILE` sends the whole thing once, and there is no register-the-sources-then-register-the-recipe sequence.
2. No fractional number appears anywhere in the document. Every number in it is a count, a byte ceiling, a parts-per-million bound, or a number of seconds. The client refuses a fractional number by position, so write a rate as a numerator and a denominator, or as parts per million.
3. You do not compute the digest, and you cannot state one. The submission carries the document and nothing else, and every edit produces a new digest.
4. Every column gets a `description`.
5. Every measured column gets a unit.
6. `limits.max_source_bytes` is where fetching stops, not how big the source is. It bounds the largest single source, not the total. Set it from what a run measured. `mr-data receipt RUN_ID --json` reports `bytes_fetched`, `rows_kept`, `rows_available` and `truncated` for each source, and those numbers are the size of the source. The run's own `coverage` is per-run and folds every source into four numbers, so it cannot size one.

How far one source may fetch depends on how the run will read it. A Reader-pinned source and an unpinned `csv` source reach 256 MiB. An unpinned `ndjson` source reaches 37,745,664 bytes. An unpinned `json` or `parquet` source reaches 16 MiB, because neither has a row-at-a-time reader and both are read whole. Pinning a Reader is the way past that. What a source may decode to is its Reader family's output budget, and no run holds more than 1 GiB of decoded rows. [Limits](/docs/reference/limits/) carries both tables. Declaring 8 MiB over a feed that answers 40 KB registers fine. It puts one sentence on the receipt under `warnings`, which says to measure that source. Where the source really is larger, do not lower the ceiling. That truncates a sample or refuses a full run. Narrow the projection where the source is wide. Split the window across several sources and union them in a first step, up to 256 sources.

A seventh rule applies to JSON APIs. `records_pointer` is the RFC 6901 pointer to the array of records, and `""` means the whole body is the array. A reader pinned through `connector.parameters` states the pointer in `reader.decode_options`. A generic API states it once in `generic_api.response.records_pointer`, and it must equal `decode_options.records_pointer`.

## Register the recipe

```bash
mr-data recipe recipe.json --json
```

| Flag | What it does |
| --- | --- |
| `FILE` | The recipe document, as a path to a JSON file. Or the word `show` followed by a recipe identifier, to read one back. |
| `RECIPE_ID` | With `show`, the recipe to read back. |
| `--json` | One JSON object instead of human lines. |
| `--no-activity` | Do not tell the dataset page what this command is doing. The report is best-effort either way. `MR_DATA_NO_ACTIVITY=1` turns it off for a whole schedule. From 0.27.1 the line is also held back while the page shows a current line the agent set itself. |

The client reads the file as strict JSON, at most 1,048,576 bytes.

```json
{
  "schema_version": "mostlyright-thin-client-recipe.v1",
  "status": "recipe_registered",
  "lane": "hosted",
  "recipe_id": "…",
  "recipe_digest": "…",
  "dataset_id": "…",
  "table_id": "…",
  "source_ids": ["…", "…"],
  "columns_without_description": 2,
  "warnings": {"warning 1": "…"},
  "idempotent_by": "recipe_digest"
}
```

The receipt omits `columns_without_description` and `warnings` when there is nothing to say.

`mr-data run` takes `recipe_id` and `recipe_digest`, so hold both. The run states which recipe it means and which bytes of it. `recipe_digest` is bare lowercase hex with no `sha256:` prefix.

Sending the same bytes twice registers one recipe, and the second call returns the same identifiers.

A revision keeps its dataset and its table. Change the document, send it again, and a new `recipe_digest` comes back against the same `dataset_id` and `table_id`. An abandoned revision costs nothing.

`mr-data recipe show RECIPE_ID --json` reads one back. It returns the same identifiers plus the canonical `document`, with a saved connection's shorthand resolved into the connector it stands for.

## Client refusals

The client raises these before it resolves a credential, so a mistyped document fails locally before any network request.

| Refusal | What it means |
| --- | --- |
| `THIN_RECIPE_DOCUMENT_UNREADABLE` | The path is not there, is not a plain file, or is over the size ceiling. |
| `THIN_RECIPE_DOCUMENT_INVALID` | Not strict JSON, not one JSON object, a fractional number, a missing required member, no sources, or a `dataset.id` that is not a lowercase canonical UUID. The sentence names the position. |
| `THIN_REQUEST_INVALID` | `recipe show` with no identifier, or a recipe identifier passed beside a file. |

## Next

[Run and inspect](/docs/build/run-and-inspect/) takes a sample without `--window` and reads what it delivered.