# The spec already wrote half your ingestion script You need the data from some API. There's no connector for it, or the connector costs more than the problem. So you write a script. The first version is twenty lines. A token in a header, a loop over `?page=`, one JSON file per page. It works. You schedule it and move on. Then the second run downloads everything again, so you add `updated_since` and keep the last timestamp in a file. Then the API starts answering 429, so you add a sleep. Then a retry. Then a retry that reads `Retry-After`. Then someone asks for Parquet, and `age` comes out as a string column, because one record sent `"41"` and that's the record the type inference happened to look at first. Next week's file has it as an integer. (Good luck with the union.) And one night the script dies on page 37. Did it save the timestamp before or after it wrote the files? You open the script to find out. Here's the part that kept bugging me: most of what that script hard-codes was already written down. In the API's OpenAPI spec. So I built [**apisip**](https://github.com/b-luu/apisip) around that idea. Point it at a spec, answer a few questions, and every list endpoint lands as JSONL or Parquet files, on local disk or in an S3-compatible bucket. It's a single Rust binary, MIT or Apache-2.0, and this is version 0.1.0. ## The spec already knows most of it An OpenAPI spec lists the endpoints that return lists. It says where the records sit in the response, which query parameters page through them, which security schemes the API accepts. Often it declares a filter like `updated_since`, or a `sort` parameter that accepts `-updated_at`. And it gives every response a schema, with types. That's most of the script, before you write a line of it. `apisip init` reads the spec once and writes it all down in a plain `apisip.toml` that you can read and edit. Secrets go to `.env`, never into the config. The repository ships a small fake shop API so you can try this without an account anywhere: ```sh cargo run --example mock-api # in a clone: serves http://127.0.0.1:8080, token: mock-token ``` Then, in a new folder (apisip works like git: one folder per API): ```sh mkdir mockshop && cd mockshop apisip init http://127.0.0.1:8080/openapi.json ``` `init` asks for the base URL, where to write the data, the format (I picked Parquet), the auth scheme, the token (masked as you type, stored in `.env` with mode 600) and which streams to keep. Then it tells you what it found: ```plaintext 4 stream(s) enabled: users filter page number key id orders early stop offset key id events early stop cursor key id plans full refresh next url key id Skipped: /users/{id} needs a parent stream (path parameters) Rate limit: 1000 calls per 1m. ``` Four streams, four pagination styles, three ways of fetching only what changed, and a rate limit it found in the spec. Every line of that is a decision you can change in `apisip.toml`. The one that matters most for later runs is the second column, the incremental mode: - **filter:** the API has a parameter like `updated_since`, so the next run sends the last value seen; - **early stop:** no filter, but the API sorts by `updated_at` descending, so the run stops at the first page older than what it already has; - **full refresh:** nothing usable, so every run takes a complete snapshot. Then `apisip run`. Retries with backoff, `Retry-After`, the `RateLimit-*` headers and the rate limit are handled by the runtime. Credentials never follow a redirect or a next-page link to another host: that fails the stream instead. The mock API lies on purpose, because real ones do too. Its `events` endpoint claims to sort by `updated_at` descending and doesn't. apisip notices the cursor going up in the middle of the run, keeps everything from that point, and stops trusting that sort for good: ```text events early stop 20 records 1 file(s) warning: the cursor went up under a descending sort: the API does not sort as documented. This run fetched everything; later runs will too. ``` Fetching everything every time is slower. Quietly missing records because an API doesn't do what its docs say is worse. ## Types come from the spec, not from the first page This is the `"41"` problem from the start. When `init` reads the spec, it writes each stream's response schema to `schemas/.json`. Parquet columns come from that file, before any request is made. So `age` is an integer column because the spec says `integer`, not because of whichever record showed up first. Same type in every file, every run: ```sh duckdb -c "select column_name, column_type from (describe from 'data/users/**/*.parquet') where column_name in ('age', 'address', 'updated_at')" ``` ```text │ age │ BIGINT │ │ address │ STRUCT(city VARCHAR) │ │ updated_at │ TIMESTAMP WITH TIME ZONE │ ``` Now, the data doesn't always agree with the spec. One mock user has its age as the string `"41"`, and another carries a `nickname` field the spec never declared. The run reports both: ```text users filter 25 records 1 file(s) drift: undeclared field nickname (1 records), e.g. "seven" drift: age: string -> integer, cast without loss (1 records) ``` `"41"` became 41 because that conversion loses nothing. That's the rule: a value of the wrong type is cast only when its value survives the trip. `"1.50"` can become 1.5. `"0.30000000000000000001"` can't become a float without rounding a digit away, so it isn't cast. The undeclared field isn't dropped either. It goes into one `_apisip_extra` column: ```sh duckdb -c "select id, age, _apisip_extra from 'data/users/**/*.parquet' where id in (7, 13)" ``` ```text │ 7 │ 27 │ {"nickname":"seven"} │ │ 13 │ 41 │ NULL │ ``` A record that still doesn't fit goes to a quarantine file, whole, with the reason. Fix the schema file (it's yours to edit) or the cast setting, run `apisip replay`, and the records that now pass are committed as if they had never been quarantined. (JSONL output skips all of this: it keeps records exactly as received, and only reports the drift.) ## The record that shares the watermark's second This is the bit I'm proudest of, and the bit most likely to bite you in a hand-written script. An incremental run keeps a watermark: the highest `updated_at` it has seen. Next time, it asks for records from there on. The question is whether "from there on" means after the watermark, or at it. Many APIs return timestamps to the second. Say the last run ended on a record stamped `00:25:00`, and another record was updated in that same second, just after the run read the page. A strict "after" never sees it. Not on the next run, not ever. A plain "at or after" sees it, but also fetches and writes the boundary record again on every single run. apisip compares at-or-after, so delivery is at least once. To avoid rewriting the boundary each time, it keeps a fingerprint of every record it wrote at the watermark. A record with the same cursor and the same content is counted as `unchanged` and skipped. A changed or new one is written. Here it is on the mock API. A second run, with nothing changed upstream: ```text users filter 0 records 0 file(s) since 2026-01-01T00:25:00Z 1 unchanged ``` Now add a user stamped in the watermark's second (the mock API has a small admin endpoint for that) and run again: ```sh curl -X POST 'http://127.0.0.1:8080/_admin/add/users?at=2026-01-01T00:25:00Z' apisip run --stream users ``` ```text users filter 1 records 1 file(s) since 2026-01-01T00:25:00Z 1 unchanged ``` The new record is written, the old one is skipped. The run after that writes nothing, with `2 unchanged`. One limit I can't fix from the client side: in filter mode, the API applies `updated_since` itself. If it treats it as strictly after, it never returns that record, and apisip can't see what it wasn't sent. The README says so too. And the night the script died on page 37? Each stream is written to a staging area first. When it finishes, apisip moves the files into place, then advances the watermark, then writes the run manifest, in that order. A run that fails before the files move leaves no data and no moved watermark. A crash between the first two steps leaves the files without the watermark, so the next run fetches those records again. You get duplicates, never a gap... which is the right way round, because the next part removes duplicates. ## Query it with DuckDB apisip never updates or deletes a row it has written. Each run appends what it captured, with `_apisip_run_id` and `_apisip_extracted_at` on every record. The grain is one row per version that a run observed: a user updated twice between two runs shows up once, in its latest state. It's a history of snapshots, not a change-data-capture feed. After each run, apisip writes two DuckDB views per stream that has a primary key. `users_current` has one row per key, its latest version. `users_history` has one row per distinct version, with `valid_from`, `valid_to` and `is_current`. Edit user 3 on the mock API, run again, and ask for its history: ```sh curl -X POST http://127.0.0.1:8080/_admin/touch/users/3 apisip run --stream users duckdb -c ".read data/_apisip/views/all.sql" \ -c "select id, name, valid_from, valid_to, is_current from users_history where id = 3" ``` ```text │ 3 │ user 3 │ 2026-01-01 00:03:00+00 │ 2026-01-01 01:16:00+00 │ false │ │ 3 │ user 3 (edited) │ 2026-01-01 01:16:00+00 │ NULL │ true │ ``` (Times are in UTC here. DuckDB prints them in your own time zone.) The duplicates from the at-or-after boundary, from a re-run after a crash, or from full-refresh snapshots all disappear in these views. They use only standard window functions, so porting the current-state views to a warehouse that reads the Parquet files means swapping `read_parquet` for your external table and spelling out the column list. The README shows how. ## When the spec changes APIs change, and by then you've probably edited `apisip.toml` and a schema file or two. `apisip update` compares three versions: the spec at `init`, the new spec, and your current files. A value you never touched follows the spec. A value you both changed asks you. A removed endpoint is disabled, not deleted, so its data and watermark stay. A type change that doesn't match the files already written asks before anything happens. `apisip update --check` prints the diff and exits 1 when the spec changed, so a scheduled CI job can tell you before your next run finds out the hard way. ## Where it stands apisip is young. This first version handles list endpoints, the common auth schemes (bearer, API key, basic, OAuth2 client credentials) and five pagination styles, from OpenAPI 3.0, 3.1 or Swagger 2.0. Some things are plans, not features: endpoints with path parameters fed by a parent stream, the OAuth2 browser login, a webhook receiver, Iceberg metadata over the Parquet output. They're on the [roadmap](https://github.com/b-luu/apisip/blob/main/docs/roadmap.md). It isn't the right tool for everyone either. If the API publishes no spec, apisip has nothing to read. If you already live in Python and want many destinations, there are mature libraries for that. The README has a [comparison](https://github.com/b-luu/apisip#alternatives) that tries to be honest about it. To try it, install it with `cargo install apisip` ([crates.io](https://crates.io/crates/apisip)) or take a prebuilt binary from the [latest release](https://github.com/b-luu/apisip/releases), then run the quickstart above. Everything in this post ran against the mock API in the repository. The mock API knows two ways a spec can lie. Real APIs know many more. If you point apisip at one and `init` guesses wrong, I'd like to hear about it: which API, and which guess. Specs lie in creative ways, and I'd rather collect them than discover them one at a time... ;-) [apisip on GitHub](https://github.com/b-luu/apisip)