# One CSV file

Source: https://mostlyright.md/docs/recipes/csv-snapshot/

Turn one public CSV response into one table, with one transform step and three checks.

## The question

What did the New York Central Park weather station report at each observation time between
1 June and 29 August 2026?

## The source

| | |
| --- | --- |
| Publisher | Iowa Environmental Mesonet, Iowa State University |
| Address | `https://mesonet.agron.iastate.edu/cgi-bin/request/asos.py?station=NYC&data=tmpf&…` |
| Format | comma-separated text, served as `text/plain; charset=UTF-8` |
| Cadence | the archive gains roughly one row per station per hour |
| Rights | United States federal weather observations, redistributed by IEM, recorded as `public_domain_asserted` |

The archive redistributes federal observations, so anybody can make and check the rights claim.
The endpoint returns a header row and a plain comma-separated body, so the document pins no
Reader. This window returned 103,457 bytes, well under the 256 MiB an unpinned `csv` source fetches.

The publisher labels the response `text/plain`. The direct CSV path accepts that label, and so do
`delimited_text@1.1.0` and later. `delimited_text@1.0.0` refuses it.

## The recipe

```json title="recipe.json"
{
  "dataset": {
    "name": "New York Central Park weather, summer 2026"
  },
  "question": {
    "text": "What did the Central Park station report at each observation time?"
  },
  "table": {
    "name": "knyc_observations",
    "description": "One row per station and observation time.",
    "grain": [
      "station",
      "valid"
    ],
    "columns": [
      {
        "name": "station",
        "type": "string",
        "nullable": false,
        "description": "The ASOS station identifier.",
        "presentation": {
          "chart": "none"
        }
      },
      {
        "name": "valid",
        "type": "timestamp",
        "nullable": false,
        "description": "The UTC time the observation was taken.",
        "presentation": {
          "chart": "timeline",
          "bucket": "week"
        }
      },
      {
        "name": "tmpf",
        "type": "decimal",
        "nullable": true,
        "description": "Air temperature.",
        "presentation": {
          "chart": "histogram",
          "bins": 10
        }
      },
      {
        "name": "dwpf",
        "type": "decimal",
        "nullable": true,
        "description": "Dew point temperature."
      },
      {
        "name": "relh",
        "type": "decimal",
        "nullable": true,
        "description": "Relative humidity."
      },
      {
        "name": "sknt",
        "type": "decimal",
        "nullable": true,
        "description": "Wind speed."
      },
      {
        "name": "p01i",
        "type": "decimal",
        "nullable": true,
        "description": "Precipitation reported in the hour before the observation."
      }
    ]
  },
  "sources": [
    {
      "name": "knyc_asos",
      "description": "Hourly air temperature, dew point, humidity, wind and precipitation at New York Central Park.",
      "source_class": "user_api",
      "data_classification": "public",
      "locator": {
        "kind": "https_url",
        "display_locator": "https://mesonet.agron.iastate.edu/cgi-bin/request/asos.py?station=NYC&data=tmpf&data=dwpf&data=relh&data=sknt&data=p01i&year1=2026&month1=6&day1=1&year2=2026&month2=8&day2=29&format=onlycomma&tz=Etc/UTC&report_type=3&latlon=no&missing=empty&trace=empty"
      },
      "rights_claim": {
        "claimed_basis": "public_domain_asserted",
        "claim_evidence_digest": "sha256:1111111111111111111111111111111111111111111111111111111111111111",
        "claim_note": "United States federal weather observations, redistributed by IEM"
      },
      "connector": {
        "adapter_id": "public.https",
        "credential_mode": "none",
        "origin": "https://mesonet.agron.iastate.edu"
      },
      "limits": {
        "max_source_bytes": 4194304,
        "max_rows": 2000000,
        "max_requests": 2
      }
    }
  ],
  "transform": {
    "engine": "duckdb_sql",
    "steps": [
      {
        "step_id": "knyc_observations",
        "sql": "select station, cast(valid as timestamptz) as valid, try_cast(tmpf as decimal(38, 4)) as tmpf, try_cast(dwpf as decimal(38, 4)) as dwpf, try_cast(relh as decimal(38, 4)) as relh, try_cast(sknt as decimal(38, 4)) as sknt, try_cast(p01i as decimal(38, 4)) as p01i from knyc_asos",
        "description": "Carry every column through under its published name, casting the all-text relation into the declared types."
      }
    ]
  },
  "checks": [
    {
      "check_id": "grain_unique",
      "kind": "key_uniqueness",
      "enforcement": "required",
      "columns": [
        "station",
        "valid"
      ],
      "description": "One row per station and observation time."
    },
    {
      "check_id": "temperature_present",
      "kind": "null_ceiling",
      "enforcement": "required",
      "columns": [
        "tmpf"
      ],
      "max_null_ppm": 50000
    },
    {
      "check_id": "rows_present",
      "kind": "row_expectation",
      "enforcement": "required",
      "min_rows": 1
    }
  ],
  "units": [
    {
      "column": "tmpf",
      "unit": "[degF]"
    },
    {
      "column": "dwpf",
      "unit": "[degF]"
    },
    {
      "column": "relh",
      "unit": "%"
    }
  ],
  "timezone": "UTC"
}
```

## Part by part

### Sources

One entry. `source_class` is `user_api` because the address is a request endpoint rather than a
published file. `connector.origin` and the `https_url` locator must name the same host. A run
fetches a source from the one host it declares.

`limits.max_source_bytes` is 4 MiB. It is where fetching stops, not a measurement of the source.
A document may declare 4 MiB over a response of 103 KB. `mr-data recipe` puts a note on the receipt it prints,
naming the source and both numbers, instead of refusing.

The document carries no `connector.parameters` array, so it pins no Reader. The direct CSV path
reads the response, which suits an ordinary comma-separated table with a header row.

### Transform

One step. Every acquired source reaches the SQL bound as all-text CSV, so each declared type needs
an explicit cast:

```sql
select station,
       cast(valid as timestamptz) as valid,
       try_cast(tmpf as decimal(38, 4)) as tmpf,
       …
from knyc_asos
```

A column declared `timestamp` needs `cast(valid as timestamptz)`. The statement must return
`TIMESTAMP WITH TIME ZONE`, and the engine refuses a bare `TIMESTAMP` with
`TRANSFORM_COLUMN_TYPE_MISMATCH`. `try_cast` on the measurements turns an unreadable cell into null
instead of ending the run. A bare `cast` on a key column stops the run instead.

The step takes the name `knyc_observations`, which is also the table's name. That is allowed. The engine
refuses a `step_id` equal to a source name, because the two would name one relation.

### Columns

Seven columns, each with a `description`. `mr-data recipe` counts the ones that arrive without one
and reports `columns_without_description` on the receipt. It never refuses on that count.

Three columns declare `presentation`. The station identifier gets `none`. The observation time
gets `timeline` at week buckets. The temperature gets `histogram`. A column that declares nothing
still gets a chart, picked from the declared type, and the dataset page records that the builder
chose it.

### Checks

Three checks, all `required`. A failure ends the run with `failure_code: CHECK_FAILED`.

- `key_uniqueness` on `["station", "valid"]` is the grain. Without it a duplicated response doubles
  the table and nothing says so.
- `null_ceiling` on `tmpf` at 50,000 parts per million, which is five per cent. IEM writes an empty
  cell where a reading is missing, and `missing=empty` in the address asks for that.
- `row_expectation` with `min_rows: 1` guards against a source that returned only a header and
  nothing else.

### Cadence

This document declares no `window`, so it is resync-only. A refresh would have to read the whole
address again, and Studio refuses that with `RESYNC_REQUIRED` before it acquires anything. Reading
the address again is an explicit resync.

A `window` is what changes it. It says which parameters carry time, so a refresh asks for a few days
instead of the whole range. [Daily city temperatures](/docs/recipes/city-temperatures/) writes one.
`closed: true` does not change it. A closed source states that its bytes are finished, and a plan
whose every source is closed has nothing to acquire, so it is refused the same way. A closed source
earns its predecessor reuse beside a source that declares a request window.

## Register, run, inspect

Use the real table for one full build. For a bounded test first, follow the [scratch-table workflow](/docs/build/run-and-inspect/#test-before-the-full-build). A successful sample on the real table can publish truncated data. Stop if the forecast refuses; size the recipe before running.

```sh
mr-data dataset create --name "New York Central Park weather" --json
# put the printed dataset_id into the document's dataset.id, then:

mr-data recipe recipe.json --json
# → recipe_id, recipe_digest, dataset_id, table_id, source_ids

mr-data recipe explain RECIPE_ID --json
mr-data recipe forecast RECIPE_ID --digest RECIPE_DIGEST --json
mr-data run --recipe RECIPE_ID --digest RECIPE_DIGEST --full --json
mr-data watch RUN_ID --json
mr-data status RUN_ID --json
mr-data peek RUN_ID --json
mr-data checks RUN_ID --json
mr-data receipt RUN_ID --json

mr-data query RUN_ID "select count(*) from run_table" --json
```

The table's name in a query is always `run_table`, whatever the recipe called it.

## What the run reports

`mr-data receipt RUN_ID --json` carries one entry per source. Here the single entry names the
address as registered, the response media type, the byte count and the digest of what arrived. The recorded evidence:

| Field | Value |
| --- | --- |
| `media_type` | `text/plain; charset=UTF-8` |
| `bytes` | 103,457 |
| `sha256` | `d179b6fd772bab603717736514ff3d3b34fc65c2133bcf9cd50c22b038e4bd41` |

Read `coverage` off the run rather than counting rows in the table. Every entry carries
`truncated`, which separates a complete response from one cut at a row ceiling.

`mr-data checks RUN_ID` reads the results out of the receipt and exits non-zero when one did not
pass. It reports `checks_scope` too. `version` means the pass covered every row of the table the
run composed.

## Variant: a pinned Reader and a closed source

A closed source is still resync-only on its own. This variant records that the bytes are finished,
which is a fact about the archive rather than a refresh strategy.

Pin the Reader when the publisher's layout needs settings (a different delimiter, a title row, a
non-UTF-8 encoding). Pin it also to record the Reader name and version. The pin is three reserved
parameters. The options value is the canonical JSON text of what the family accepts, with every
defaulted setting written out:

```json title="the same source, pinned and closed"
"connector": {
  "adapter_id": "public.https",
  "credential_mode": "none",
  "origin": "https://mesonet.agron.iastate.edu",
  "parameters": [
    {"name": "reader.family_id", "value": "delimited_text"},
    {"name": "reader.family_version", "value": "1.1.0"},
    {"name": "reader.decode_options", "value": "{\"delimiter\":\",\",\"encoding\":\"utf-8\",\"header\":true}"}
  ]
},
"closed": true
```

A stated subset of the settings, `{"delimiter": ","}` alone, fails when the engine reads the
document. The refusal quotes the spelling that works.

## Traps

The refusals this recipe shape runs into are in [Write a recipe](/docs/build/write-a-recipe/) and
[Troubleshooting](/docs/build/troubleshooting/).