One CSV file

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

{
  "dataset": {
    "name": "New York Central Park weather, summer 2026",
    "description": "Each row is one hourly observation at the New York Central Park ASOS station between 1 June and 29 August 2026, from the Iowa Environmental Mesonet archive."
  },
  "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:

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 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

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 run --recipe RECIPE_ID --digest RECIPE_DIGEST --sample --max-rows 2000 --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 run --recipe RECIPE_ID --digest RECIPE_DIGEST --full --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:

"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 and Troubleshooting.