Snapshot windows

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

{
  "dataset": {
    "name": "Y Combinator companies, day by day",
    "description": "The Y Combinator company directory read once a day from the yc-oss JSON mirror and kept day by day, so a company's status, stage, team size and hiring flag are readable as of any date the table has covered."
  },
  "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.

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:

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.

Register, run, inspect

mr-data dataset create --name "Y Combinator companies, day by day" --json
mr-data recipe recipe.json --json

mr-data run --recipe RECIPE_ID --digest RECIPE_DIGEST --sample --max-rows 4096 --json
mr-data peek RUN_ID --json
mr-data checks RUN_ID --json

mr-data run --recipe RECIPE_ID --digest RECIPE_DIGEST --full --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.

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.