Streaming records into Strata with no continuous DuckDB writer

Certant Strata builds its data model from the tables inside a document corpus (the pipeline is in Building a data model from PDFs instead of designing one). Plenty of useful data never arrives as a PDF, though: a fleet of sensors, or a payroll system that exports every week. We wanted those producers to POST batches of records over HTTP and have the rows queryable in the same knowledgebase, without a loader service running in between.

What shipped is an HTTP route that turns each accepted batch into one immutable Parquet file, plus a DuckDB view created once that reads the whole folder at query time. In the validation run on 29 June 2026, five POSTs of 100 rows each came back as 500 queryable rows through that view. No background process ran between the POST and the query.

Why a loader process was the wrong shape

The brief we started from described the textbook design: producers write to a landing area, and a single loader process, the sole read-write connection to DuckDB, drains it into tables. When we checked that against how Strata stores data, most of it didn't fit.

There is no single DuckDB database. Each knowledgebase has its own DuckDB file, and "single writer" is already enforced by a per-knowledgebase lock in Redis with a 65-second TTL. At least six code paths take that lock to write: extracted tables, CSV and spreadsheet uploads, profile comments, generated ontology views, and two separate writes to the monitors' alert log. That last pair matters, because it means the serving side isn't purely read-only either. A loader would have been a seventh writer queuing for the same lock, forever, on every knowledgebase with a stream attached. It would also be exposed to the problem where a reader holding a DuckDB connection open blocks every writer behind it, which is the lock storm in Getting Strata to corpus scale.

There was also no append path to reuse. Raw extracted tables are written as CREATE OR REPLACE per document, so a streaming loader would have needed new append semantics, plus a separate ledger of processed files so a redelivered task didn't load the same batch twice.

And the path we already had for pushed data, Strata Feeds, sends the whole row payload through the Redis broker as a Celery task argument on every tick, capped at 50,000 rows and 10 MB. That suits a cron job or a webhook, but that per-batch trip through the broker is exactly the cost a stream has to avoid.

What settled it was a product decision about modelling. We made streamed data raw-queryable by default and put ontology modelling behind an explicit customer action per stream, never automatic per batch. Once the expensive profile, match and synthesis steps stop running per batch, the raw path doesn't need a DuckDB writer at all. We wrote down what we were not building while we were at it: no Iceberg, no Hive Metastore, no Kafka or other broker, no Spark. Hive-style key=value folder names are allowed if a stream ever gets big enough to want date partitions, because DuckDB parses those at read time and they need no service.

producer ──POST NDJSON──▶ ingest route ──validate + convert──▶ atomic rename ──▶ per-stream Parquet folder
                                                                                        │
                                            one-time view: read_parquet glob ◀──────────┘
                                                                                        │
                                   (opt-in only) snapshot → profile → match → synthesise → ontology views

The route: validate, convert, rename

A producer sends POST /knowledgebase/{kb_id}/strata/ingest/{stream} with an application/x-ndjson body. The body is one JSON object per line, like the synthetic sensor data we tested with:

{"ts":"2026-06-29T00:00:00Z","sensor_id":"sensor-001","reading_celsius":"21.4","site":"north-basin"}
{"ts":"2026-06-29T00:00:05Z","sensor_id":"sensor-002","reading_celsius":"22.1","site":"south-basin"}

The handler does four things, in this order:

1. Parse every line. A line that isn't valid JSON, or is valid JSON but not an object, fails the batch. If the receiver has registered columns, a record carrying a column outside that list fails it too.
2. Coerce every cell to a string. Raw landing is all-VARCHAR, the same discipline the PDF path uses; typing happens later, in the ontology views, with TRY_CAST.
3. Write the Parquet file into a _tmp/ folder. The design said pyarrow; the build uses a COPY ... TO ... (FORMAT PARQUET) on an in-memory DuckDB connection instead, the same way the PDF path writes its table sidecars. That connection is duckdb.connect() with no path, so it never opens the knowledgebase's DuckDB file and takes no lock.
4. fsync the staged file, os.rename it into data/, fsync the directory, and only then return 200.

A batch that fails steps 1 to 3 gets a 400, and its body and error message are written to a failed/ folder for later inspection. It is never renamed into data/.

The strictness is there because the view reads every Parquet file in the folder, so a single corrupt file would break every query against that stream, for every reader, until someone removed it. The layout keeps staging, rejects and any future compacted originals as siblings of data/ rather than inside it:

strata/streams/{stream_id}/
    data/<batch-id>.parquet        immutable landed batches (the only thing the view reads)
    data/dt=YYYY-MM-DD/...         optional date folders, only if volume ever calls for it
    _tmp/<batch-id>.parquet        staging before the rename
    failed/<batch-id>.ndjson|.err  rejected batches
    processed/                     reserved for compacted originals

An earlier draft of the design had everything under one folder with a glob of **/*.parquet, which would have happily read half-written staging files. The note that fixed it is still in the design doc: glob only data/**/*.parquet.

A 200 means the batch has landed durably. It makes no promise that the rows are queryable at that instant, even though the next read picks the file up, and we kept the contract that narrow on purpose.

The route has two smaller behaviours. A producer can send its own batch id as an idempotency key; it becomes the filename, and a retry of the same batch comes back as a 200 with "replayed": true and no new rows. And a request body over 1 MB is refused with a 413.

One view, created once

Queryability is a single DDL statement per stream, run under the per-knowledgebase lock:

CREATE OR REPLACE VIEW raw_extracted.stream_{stream_id} AS
  SELECT * FROM read_parquet('.../strata/streams/{stream_id}/data/**/*.parquet',
                             union_by_name=true, filename=true);

union_by_name=true lets batches with different column sets coexist in one view, which is what schema-on-read needs when a producer adds a field halfway through the week. filename=true adds a filename column that ties every row back to the exact Parquet file, and therefore the exact POST, it came from.

Alongside the view we write one row into Strata's _strata_manifest table with source = stream:{stream_id}. That row is what makes the Tables panel and the downstream pipeline treat a stream like any other extracted table. The document-status code already skipped pseudo-documents whose id starts with feed:; we extended that check to stream: so a stream doesn't turn up as a phantom document in the knowledgebase's document list.

One DuckDB behaviour caught us here. DuckDB binds a read_parquet glob when the view is created, so creating the view over an empty folder fails. The receiver therefore writes its manifest row straight away (the Tables panel shows the stream immediately, with no row count yet) and defers the view until the first file lands. The ingest route notices when it has just written into an empty data/ folder and creates the view then. There is also a "Re-sync view" button on the receiver page that runs the same idempotent step.

After that, the per-knowledgebase lock is touched exactly twice in a stream's life: once to create the view, and once more if the customer promotes the stream into the ontology. Ingest itself is filesystem-only. Even the throughput numbers on the receiver page (rows, files, bytes, last landed) come from walking the folder and a count(*) over the glob on an in-memory DuckDB connection, so looking at the page can't contend with a writer either.

What the validation run showed

We ran the whole stack from source in an isolated VM and drove it with a small producer script sending synthetic sensor NDJSON. The results below are from the completion report written the same day, 29 June 2026.

A sample row from the view, with its provenance column:

('2026-06-30T03:46:40Z', 'sensor-001', '25.00', 'north-basin', '…/data/prod-b57f23524049-b0001.parquet')

The report records row counts and response codes, not query latency, so I haven't quoted a time for the query. It does show the rows were there on the next read without any process running to load them.

Promoting a stream into the ontology

Raw rows are useful for SQL, but the reason to put them in Strata is to get them into the governed data model alongside everything else. The "Apply ontology to this stream" button does that.

It snapshots the view into a native table under the per-knowledgebase lock, then runs the same chain PDF tables go through: profile the table, match its columns against everything already in the model, synthesise object types, generate ontology.* views. It runs as a one-shot Celery task behind a per-knowledgebase run-lock. Continuous promotion is still a separate piece of work because overlapping match runs can corrupt an ontology version; a manually triggered one-shot that holds the run-lock can't overlap itself. Matching needs a PostgreSQL-backed knowledgebase, so a file-backed one gets a clean 409 from the button, while raw queries over the view work on either backend.

For the live test we streamed 60 synthetic compensation rows into a PostgreSQL-backed knowledgebase and pressed the button. Synthesis produced six object types, PayFact as the fact plus Employee, Department, Location, PayGrade and EmploymentType as dimensions, and six views.

It didn't work first time. Two things in the modelling chain assumed every table came from a document with pages, and a stream has neither. The profiler wrote its .profile.json sidecar into a strata/tables/{doc}/ folder that the PDF extract step always creates first. A stream never goes through extract, so the folder didn't exist and the write failed with FileNotFoundError. The second produced no error at all. The function that loads table profiles for synthesis only understood the three-part table id PDFs use ({kb}/{doc}/{page_tbl}) and silently skipped anything else, so the stream's two-part {kb}/stream:{id} id was dropped and synthesis reported no_profiled_tables. The fix maps it to the real raw_extracted.stream_{id} view. Neither bug could appear until a real stream went through the whole chain.

Once the model existed, the Ask your data tab treated the stream like any other source. "What is the average annual salary by department?" came back as five rows, with the provenance chip pointing at the stream rather than a document.

How that question becomes governed SQL, and what happens when it can't, is covered in Ask your data in English, get governed SQL or an honest no.

What we haven't built yet

This shipped as five segments, and two more were scoped and deliberately left for later.

One file per POST means the file count only ever goes up. The plan for that is an optional compaction sweep that only touches sealed partitions (past dt= folders that no longer receive writes), reads the small files through an in-memory DuckDB connection, writes one compacted file and swaps it in atomically. It never opens the knowledgebase's DuckDB file, so it stays off the lock and the view keeps reading the folder unchanged. The plan is to run it only when a stream's file count gets large enough to slow queries, never up front. A retention policy for the compacted originals in processed/ goes with it.

The engine the agent side uses to query a knowledgebase's DuckDB file keeps a pool of two read-only connections open. An open reader pins the file, which is harmless on the raw path where nothing writes but gets in the way at the two moments a stream takes the write lock, so that engine is due to move to a connection per query.

And the validation above was a manual live run. A repeatable acceptance suite (a high-rate producer, several knowledgebases streaming at once, promotion end to end) is written up but not built.