# Snapshot windows Source: https://mostlyright.md/docs/recipes/snapshot-window/ Save one directory snapshot per day so you can see when a company's status changed. ## The question A directory has no time in it. It says what is true now, and the next fetch overwrites the answer. The directory does not publish the day a company moved from `Active` to `Acquired`, so a table that rebuilds itself from the current listing can never answer it. On which day did each Y Combinator company's status change, and what was it before? ## The sources | | | | --- | --- | | Publisher | yc-oss, a community mirror of the Y Combinator company directory | | Address | `https://yc-oss.github.io/api/batches/winter-2025.json`, and the same for `spring-2025` and `summer-2025` | | Format | JSON, a bare array of company objects, served as `application/json; charset=utf-8` | | Size | 271,877, 237,459 and 283,765 bytes, and the summer file holds 166 companies | | Cadence | the mirror rebuilds from the directory, and a company's status can move any day | | Rights | `unknown`. The mirror states no licence and no terms of its own, so this recipe claims no permission and no open licence | Three batch files, three sources. Each one carries the same fields, so the transform stacks them. ## The recipe ```json title="recipe.json" { "dataset": { "name": "Y Combinator companies, day by day" }, "question": { "text": "On which day did each Y Combinator company's status change, and what was it before?" }, "table": { "name": "yc_company_status_transitions", "description": "One row per company and the day its status became what it is.", "grain": [ "id", "observed_date" ], "columns": [ { "name": "id", "type": "integer", "nullable": false, "description": "The directory's own identifier for the company." }, { "name": "observed_date", "type": "date", "nullable": false, "description": "The UTC day this reading was acquired on." }, { "name": "status", "type": "string", "nullable": false, "description": "The status the directory reported on that day." }, { "name": "previous_status", "type": "string", "nullable": true, "description": "The status the directory reported on the company's previous covered day, null on the first day the table saw the company." } ] }, "sources": [ { "name": "batch_winter_2025", "description": "The Winter 2025 batch of the company directory, as the mirror serves it.", "source_class": "user_url", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://yc-oss.github.io/api/batches/winter-2025.json" }, "rights_claim": { "claimed_basis": "unknown", "claim_evidence_digest": "sha256:981a1cf0e469ff912bab4e28a52c04a546adfc06e31868ade1785df1cf94d50c", "claim_note": "The mirror republishes the Y Combinator company directory as JSON and states no licence and no terms of its own. This recipe claims no permission and no open licence. The basis stays unknown, and the digest covers the project's README as read on 11 September 2026." }, "connector": { "adapter_id": "public.https", "credential_mode": "none", "origin": "https://yc-oss.github.io", "parameters": [ { "name": "reader.family_id", "value": "json.tabular" }, { "name": "reader.family_version", "value": "1.0.0" }, { "name": "reader.decode_options", "value": "{\"columns\":[{\"name\":\"id\",\"pointer\":\"/id\",\"required\":true},{\"name\":\"name\",\"pointer\":\"/name\",\"required\":true},{\"name\":\"slug\",\"pointer\":\"/slug\",\"required\":true},{\"name\":\"batch\",\"pointer\":\"/batch\",\"required\":true},{\"name\":\"status\",\"pointer\":\"/status\",\"required\":true},{\"name\":\"stage\",\"pointer\":\"/stage\",\"required\":true},{\"name\":\"team_size\",\"pointer\":\"/team_size\",\"required\":false},{\"name\":\"is_hiring\",\"pointer\":\"/isHiring\",\"required\":false},{\"name\":\"launched_at\",\"pointer\":\"/launched_at\",\"required\":false},{\"name\":\"industry\",\"pointer\":\"/industry\",\"required\":false},{\"name\":\"subindustry\",\"pointer\":\"/subindustry\",\"required\":false},{\"name\":\"website\",\"pointer\":\"/website\",\"required\":false},{\"name\":\"top_company\",\"pointer\":\"/top_company\",\"required\":false},{\"name\":\"nonprofit\",\"pointer\":\"/nonprofit\",\"required\":false}],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"\"}" } ] }, "limits": { "max_source_bytes": 4194304, "max_rows": 4096, "max_requests": 1 }, "window": { "start_at": "2026-09-12T00:00:00Z", "granularity": "day", "timezone": "UTC", "lookback_seconds": 0, "max_span_seconds": 86400, "snapshot": { "column": "observed_date", "max_partitions": 1200 }, "merge": { "materialization": "partition_replace", "partition": { "column": "observed_date", "key": "iso_date_value" }, "row_identity": [ "id", "observed_date" ] } } }, { "name": "batch_spring_2025", "description": "The Spring 2025 batch of the company directory, as the mirror serves it.", "source_class": "user_url", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://yc-oss.github.io/api/batches/spring-2025.json" }, "rights_claim": { "claimed_basis": "unknown", "claim_evidence_digest": "sha256:981a1cf0e469ff912bab4e28a52c04a546adfc06e31868ade1785df1cf94d50c", "claim_note": "The mirror republishes the Y Combinator company directory as JSON and states no licence and no terms of its own. This recipe claims no permission and no open licence. The basis stays unknown, and the digest covers the project's README as read on 11 September 2026." }, "connector": { "adapter_id": "public.https", "credential_mode": "none", "origin": "https://yc-oss.github.io", "parameters": [ { "name": "reader.family_id", "value": "json.tabular" }, { "name": "reader.family_version", "value": "1.0.0" }, { "name": "reader.decode_options", "value": "{\"columns\":[{\"name\":\"id\",\"pointer\":\"/id\",\"required\":true},{\"name\":\"name\",\"pointer\":\"/name\",\"required\":true},{\"name\":\"slug\",\"pointer\":\"/slug\",\"required\":true},{\"name\":\"batch\",\"pointer\":\"/batch\",\"required\":true},{\"name\":\"status\",\"pointer\":\"/status\",\"required\":true},{\"name\":\"stage\",\"pointer\":\"/stage\",\"required\":true},{\"name\":\"team_size\",\"pointer\":\"/team_size\",\"required\":false},{\"name\":\"is_hiring\",\"pointer\":\"/isHiring\",\"required\":false},{\"name\":\"launched_at\",\"pointer\":\"/launched_at\",\"required\":false},{\"name\":\"industry\",\"pointer\":\"/industry\",\"required\":false},{\"name\":\"subindustry\",\"pointer\":\"/subindustry\",\"required\":false},{\"name\":\"website\",\"pointer\":\"/website\",\"required\":false},{\"name\":\"top_company\",\"pointer\":\"/top_company\",\"required\":false},{\"name\":\"nonprofit\",\"pointer\":\"/nonprofit\",\"required\":false}],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"\"}" } ] }, "limits": { "max_source_bytes": 4194304, "max_rows": 4096, "max_requests": 1 }, "window": { "start_at": "2026-09-12T00:00:00Z", "granularity": "day", "timezone": "UTC", "lookback_seconds": 0, "max_span_seconds": 86400, "snapshot": { "column": "observed_date", "max_partitions": 1200 }, "merge": { "materialization": "partition_replace", "partition": { "column": "observed_date", "key": "iso_date_value" }, "row_identity": [ "id", "observed_date" ] } } }, { "name": "batch_summer_2025", "description": "The Summer 2025 batch of the company directory, as the mirror serves it.", "source_class": "user_url", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://yc-oss.github.io/api/batches/summer-2025.json" }, "rights_claim": { "claimed_basis": "unknown", "claim_evidence_digest": "sha256:981a1cf0e469ff912bab4e28a52c04a546adfc06e31868ade1785df1cf94d50c", "claim_note": "The mirror republishes the Y Combinator company directory as JSON and states no licence and no terms of its own. This recipe claims no permission and no open licence. The basis stays unknown, and the digest covers the project's README as read on 11 September 2026." }, "connector": { "adapter_id": "public.https", "credential_mode": "none", "origin": "https://yc-oss.github.io", "parameters": [ { "name": "reader.family_id", "value": "json.tabular" }, { "name": "reader.family_version", "value": "1.0.0" }, { "name": "reader.decode_options", "value": "{\"columns\":[{\"name\":\"id\",\"pointer\":\"/id\",\"required\":true},{\"name\":\"name\",\"pointer\":\"/name\",\"required\":true},{\"name\":\"slug\",\"pointer\":\"/slug\",\"required\":true},{\"name\":\"batch\",\"pointer\":\"/batch\",\"required\":true},{\"name\":\"status\",\"pointer\":\"/status\",\"required\":true},{\"name\":\"stage\",\"pointer\":\"/stage\",\"required\":true},{\"name\":\"team_size\",\"pointer\":\"/team_size\",\"required\":false},{\"name\":\"is_hiring\",\"pointer\":\"/isHiring\",\"required\":false},{\"name\":\"launched_at\",\"pointer\":\"/launched_at\",\"required\":false},{\"name\":\"industry\",\"pointer\":\"/industry\",\"required\":false},{\"name\":\"subindustry\",\"pointer\":\"/subindustry\",\"required\":false},{\"name\":\"website\",\"pointer\":\"/website\",\"required\":false},{\"name\":\"top_company\",\"pointer\":\"/top_company\",\"required\":false},{\"name\":\"nonprofit\",\"pointer\":\"/nonprofit\",\"required\":false}],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"\"}" } ] }, "limits": { "max_source_bytes": 4194304, "max_rows": 4096, "max_requests": 1 }, "window": { "start_at": "2026-09-12T00:00:00Z", "granularity": "day", "timezone": "UTC", "lookback_seconds": 0, "max_span_seconds": 86400, "snapshot": { "column": "observed_date", "max_partitions": 1200 }, "merge": { "materialization": "partition_replace", "partition": { "column": "observed_date", "key": "iso_date_value" }, "row_identity": [ "id", "observed_date" ] } } } ], "transform": { "engine": "duckdb_sql", "steps": [ { "step_id": "yc_company_day_rows", "sql": "select id, name, slug, batch, status, stage, team_size, is_hiring, launched_at, industry, subindustry, website, top_company, nonprofit, observed_date from batch_winter_2025 union all select id, name, slug, batch, status, stage, team_size, is_hiring, launched_at, industry, subindustry, website, top_company, nonprofit, observed_date from batch_spring_2025 union all select id, name, slug, batch, status, stage, team_size, is_hiring, launched_at, industry, subindustry, website, top_company, nonprofit, observed_date from batch_summer_2025", "description": "Stack the three batch files into one relation. Every batch file carries the same columns and the day stamp the snapshot window added." }, { "step_id": "yc_company_days", "sql": "select cast(id as integer) as id, cast(observed_date as date) as observed_date, name, slug, batch, status, stage, cast(team_size as integer) as team_size, cast(is_hiring as boolean) as is_hiring, to_timestamp(cast(launched_at as bigint)) as launched_at, industry, subindustry, website, cast(top_company as boolean) as top_company, cast(nonprofit as boolean) as nonprofit from yc_company_day_rows", "description": "Cast the stacked text into the types the directory means. The worker seals the merged relation as CSV, so every column reaches the transform as text." }, { "step_id": "yc_company_status_transitions", "sql": "select id, observed_date, status, lag(status) over (partition by id order by observed_date) as previous_status from yc_company_days qualify status is distinct from previous_status", "description": "Keep the day a company's status became what it is. lag reads the previous covered day for the same company, and the step drops a day equal to it. The first day the table saw a company has no previous reading, and the step keeps that row." } ] }, "checks": [ { "check_id": "transition_grain_unique", "kind": "key_uniqueness", "enforcement": "required", "columns": [ "id", "observed_date" ], "description": "One row per company and the day its status changed." }, { "check_id": "status_present", "kind": "null_ceiling", "enforcement": "required", "columns": [ "status" ], "max_null_ppm": 0 }, { "check_id": "transition_rows_bounded", "kind": "row_expectation", "enforcement": "required", "min_rows": 1, "max_rows": 1000000 } ], "units": [], "timezone": "UTC" } ``` ## Part by part ### Which window this is A source states `window.request` or `window.snapshot`, never both. `window.request` names the query parameters of the address that carry time. It suits a publisher who will serve a past range on demand, like the observation endpoint in [Forecast against observation](/docs/recipes/forecast-vs-observation/). `window.snapshot` suits a source whose address carries no time at all. A directory, a jobs board or a rank list. The partition key is the run's own acquisition day rather than a parameter of the address. The worker fetches the address literally and renders nothing in it. `display_locator` stays exactly what the host allowlist, the origin agreement check and the Sources card already read. ### What one explicit resync does The applied window is one partition, the acquisition day. Call it `D`, the UTC calendar date of the source's acquisition instant, which the receipt already records as `fetched_at`. The window is `[D, D+1)`. The worker stamps every decoded row with `observed_date = D` after the Reader and before the merge. The transform then sees an ordinary text column and casts it like any other. An explicit resync is a full run over the source material it acquires now. It has no predecessor input to preserve: it can replace the live table with that current material. The `observed_date` stamp describes this run's acquisition day; it is not a promise that earlier partitions remain. ### The five members the snapshot fixes | Member | Value here | Why | | --- | --- | --- | | `snapshot.column` | `observed_date` | the column the day is stamped into | | `snapshot.max_partitions` | `1200` | how many daily partitions the table keeps, 1 to 3660 | | `lookback_seconds` | `0` | there is nothing to re-request | | `max_span_seconds` | `86400` | a snapshot never spans two days | | `merge.partition.key` | `iso_date_value` | the stamp is a complete ISO calendar date | `merge.partition.column` must be the snapshot column and `merge.row_identity` must carry it. Registration refuses anything else. A snapshot holds each identity once per partition. `row_identity` of `id` alone would make duplicate identities within one acquired snapshot fail. `start_at` is the first partition a full run may produce. A full run seals exactly one partition, today's, so the history begins on the day of registration. Anything earlier loads as its own static source. A snapshot refuses three members beside it: `physical_scopes`, `bootstrap` and `closed: true`. Each of the first two binds a range a snapshot never requests. A closed source reuses its predecessor's bytes instead of reading the publisher today. No run can stamp a day it did not read. ### The reserved column This source reserves the name `observed_date`, the way a page collection reserves its provenance columns. The stamp would overwrite a publisher's own value of that name, and no reader of the sealed table could tell that it had. Registration answers that from the document, because the Reader pin enumerates its columns. The decode options list fourteen pointers, and none of them names `observed_date`, so the name is free. ### The transform Three steps. `yc_company_day_rows` stacks the three batch files with `union all`. Each source relation already carries `observed_date`, because the stamp lands before the transform runs. `yc_company_days` casts. The worker seals the merged relation as CSV, so every column reaches the transform as text. `cast(id as integer)` is a bare cast on the identity, so an unparseable value stops the run. `to_timestamp(cast(launched_at as bigint))` turns the directory's epoch seconds into an instant. `yc_company_status_transitions` is the step the window exists for: ```sql select id, observed_date, status, lag(status) over (partition by id order by observed_date) as previous_status from yc_company_days qualify status is distinct from previous_status ``` `lag` reads the previous covered day for the same company. A day whose status equals the day before it drops out. What remains is one row per company and the day its status became what it is. `is distinct from` rather than `<>` keeps the first day. A company's earliest covered day has no previous reading. `null <> 'Active'` is null rather than true and would drop that first row. The window function only reads rows in the material this full resync acquired. It cannot infer a previous-day value from a predecessor table version. ### Checks `transition_grain_unique` on `id` and `observed_date` catches this shape. A duplicate inside one partition emits the same company twice on the same day. `lag` then compares a day with itself. `status_present` at `max_null_ppm: 0` catches the mirror changing the field name. The decode options mark the pointer `/status` as `required: true`, so a missing field refuses the decode. An empty field reaches the table as an empty string. This check does not catch it. A `value_range` or a transform filter can catch it. `transition_rows_bounded` with `min_rows: 1` catches an explicit resync that produced no rows at all. ### Cadence A snapshot source has no incremental boundary. Studio refuses a normal refresh before acquisition with `RESYNC_REQUIRED`; it never silently downloads the whole directory as a default refresh. Choose an explicit table resync when a new daily reading is intended, retaining its caller-held request UUID until Studio gives a definitive answer. No schedule converts that whole-source decision into an incremental refresh, and a declared `rebuild` is refused beside a snapshot source. What changes on a Studio that serves snapshot refresh is under [Needs server 4.14](#needs-server-414). ## 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 "Y Combinator companies, day by day" --json mr-data recipe recipe.json --json 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 peek RUN_ID --json mr-data checks RUN_ID --json mr-data promote TABLE_ID --json mr-data table resync TABLE_ID --request-id UUID --json mr-data diff RUN_A RUN_B --json ``` ## What the run reports The full run writes its current acquisition-day material. No company has a predecessor reading in that run, so `is distinct from` keeps every row it acquires. The receipt's `coverage` entry for each source carries the `window` the run covered, which is the single day `[D, D+1)`. It also carries `bytes`, `digest`, `rows_available` and `rows_kept`. An explicit resync acquires current source material as a full run and can replace the live table. `mr-data diff RUN_A RUN_B --json` compares the two sealed runs; it does not establish that the later run appended to the earlier one. ## Needs server 4.14 Check first: run `mr-data recipe explain recipe.json --json` and read `studio_capabilities`. Use what follows only when `snapshot_refresh` is true. When it is false, snapshot refresh is not available on this server yet, and the table keeps the one day its last full run or resync read. With `snapshot_refresh` true, a refresh of a snapshot source appends the UTC day `D` observed in the receipt's `fetched_at` onto the raw relation the predecessor sealed. Predecessor rows outside `D` survive, partition `D` is replaced, and a second reading on the same day replaces that day. The receipt's merge block then carries `observed_date` and `partitions`. The table refreshes daily at most, and a missed UTC day is a hole nothing repairs, because the directory cannot be asked for a day that has passed. `lag()` then reads the previous covered day from that history, so `yc_company_status_transitions` reports the day each status changed. The history ceiling is fixed at registration, and raising it discards the history. Size `window.snapshot.max_partitions` and `limits.max_source_bytes` for the whole history you mean to keep when you register, because the ceiling cannot be raised later without discarding the history. The byte ceiling in force is the smaller of `limits.max_source_bytes` and the 4 GiB merge ceiling. This recipe declares 1,200 partitions, but its `limits.max_source_bytes` of 4,194,304 bytes is sized for one reading of a file under 300 KB, and a history of those readings passes it in weeks. Measure a day's bytes from the first full, as `snapshot_merged_bytes` in `mr-data status RUN_ID --diagnose --json`, and declare 1,200 times that with headroom before you register. A refresh whose merged history would pass `limits.max_source_bytes` fails `SNAPSHOT_HISTORY_EXCEEDS_SOURCE_CEILING` before it seals, and the live version and its history stay readable. `mr-data recipe forecast RECIPE_ID --digest RECIPE_DIGEST --json` reports the days held, the declared horizon and `days_remaining`, and warns `SNAPSHOT_HISTORY_CEILING_NEAR` at 30 days or fewer. Once the table holds history, Studio refuses every `full` and `sample` of it with `409 SNAPSHOT_HISTORY_WOULD_BE_DISCARDED`, including `mr-data table resync`. Add `--discard-snapshot-history` only when you mean to replace every held day with today's. The discarded days cannot be fetched again. ## Traps The publisher has no record of what it said last month. A snapshot recipe cannot recover or retain that history through a resync; register a time-addressable source when historical continuity matters. `max_partitions` is a ceiling, not a retention promise. 1200 daily partitions is a little over three years. A source whose address carries time should state `window.request` instead. A snapshot requires a deliberate whole-source resync for each new reading, and that full run can replace live material. That larger table answers a smaller question. `records_pointer` is the empty string here, because each batch file is a bare JSON array rather than an object with a records array inside it. A pointer that still selects an array refuses the decode. Three sources at `max_rows: 4096` each can deliver up to 12,288 rows in one run. A row ceiling cuts each source separately, and a batch cut short loses companies from that day's partition without losing the partition. The rights basis here is `unknown`, which is what a mirror stating no licence deserves. A recipe that claims `public_domain_asserted` because the underlying facts feel public is claiming something the evidence digest does not support.