Independent benchmarks. Code in the open.

The headline numbers.

Two regimes, reported both ways: a schema-on-read index wins the simple indexed lookups (protocol distribution, long-duration connections), and the lakehouse engines win the hunting-shaped aggregations by 5–62×, averaging 46.8× native and 10.1× over Iceberg across the five-query suite (Tier B, single host, 10M events, CV-gated, identical answers verified). The baseline is OpenSearch 2.18.0; the per-query spread behind the band is top-source-IPs 21× and port-scan 62× on ClickHouse native MergeTree, 5.4× and 14× on ClickHouse-over-Iceberg, while the index takes protocol-distribution 3.4× and long-duration 1.8× over native. Text search is the index's other regime, and a follow-up bench shows even that splits three ways: ClickHouse's full-text index ties OpenSearch on whole-token filtering, the inverted index wins substring-within-a-token, but the columnar engine is the correct one for regex over token-spanning content like base64, where the analyzer's tokenization fragments the value and the index silently undercounts (it returned 3,159 of 7,946 planted blobs while the columnar scan returned all of them). Seven trials per query throughout. ClickHouse's columnar store under blanket ZSTD-22 lands a 9.0× reduction against the raw JSON on the pinned 10M-event Zeek corpus — the columnar layout and the codec together rather than the codec alone. The schema-on-read SIEM's compressed footprint on the same data is roughly 2,385 MB, a 1.4× factor.

The scaling profile is informative. On the early single-query benchmark the schema-on-read SIEM ran 3.47 seconds at 1M events and 27.52 seconds at 10M events — an 8× latency increase for 10× the data, worse than linear, while ClickHouse stayed sub-second. (The CV-gated five-query re-run summarized below is the rigorous headline; the worse-than-linear scaling is the durable point here.) The performance gap widens as data volume grows, which is the opposite of the direction per-GB-ingested licensing models need it to scale.

The workload.

Zeek conn.log is the per-connection network telemetry record produced by the Zeek (formerly Bro) network security monitor — one of the most common high-volume security data formats in production SOCs. The benchmark loads 10 million conn.log events with realistic field distributions, spread across the standard analytical workload shape: time-bucketed aggregation, top-talker queries, protocol analysis, distinct-host counts, and a cross-source JOIN with simulated SIEM alerts.

Five queries, ten iterations each for statistical stability. Hardware: single-node Docker Compose on WSL2, 32 GB RAM, 16 cores. Both engines configured identically — same row counts, same memory limits, same query suite, same iteration count. No per-tool tuning was applied beyond the documentation defaults for either side.

Reproducibility.

The benchmark repository contains: Docker Compose definitions for ClickHouse, the schema-on-read SIEM, and the additional engines tested (Trino, Dremio, StarRocks); data generation scripts that reproduce the Zeek conn.log distribution; the Python query runner; the methodology document; the analysis JSON.

Reproducibility isn't a marketing line. The repository is shared under NDA with engagement prospects and qualifying reviewers; running it on your hardware should land within statistical variance of the published numbers. If it doesn't, the discrepancy is a contribution to my understanding and gets folded into a result update with the new evidence. The reference implementation isn't published publicly because the comparison set includes commercial software whose licensing terms restrict third-party publication of comparative test results — a constraint I respect rather than work around.

The three results.

Each mode plants a known ground truth, then runs the same detection logic twice: once over the schema a cost-pressured pipeline actually produces, and once over a schema that preserves the structure. The gap between the two is the flattening tax, measured rather than asserted.

The absence case is the structural one. CloudTrail encodes "no MFA" as the absence of mfaAuthenticated, so flattening it to a column makes absence and NULL the same byte, and the naive translation WHERE mfa = 'false' recovers none of the planted privilege escalations: zero of 14,140 at a hundred thousand events. The preserved schema recovers all of them. The same bug from the flattening essay's opening case study, reproduced as a measurement.

The grain result is the asymmetry the essay predicted: a five-minute rollup answers volumetric routine queries (bytes per host, connection counts) exactly while losing the inter-arrival timing a beacon hunt depends on, so a benign bursty decoy and a real beacon become indistinguishable and F1 halves. The floating-timestamp result is the quiet one, where the cross-zone half of a correlation set silently falls outside the window while the same-zone half still looks right.

What this is, and what it isn't.

This is a Tier-B result: reproducible, first-party, controlled. It is also synthetic, and not a production claim. The corpus is built to isolate one mechanism at a time, so the corpus is the argument, and two of the three magnitudes are corpus parameters rather than universal constants — the grain F1 scales with the decoy-to-beacon ratio, and the floating-time recall tracks the cross-zone fraction. The methodology document says so plainly. The absence result is the one that is structural enough to quote without that hedge. The preserved store uses DuckDB's JSON functions to stand in for Iceberg V3's Variant type, which is the production answer the essay recommends, so DuckDB is the local reproducible analog, not the deployment target.

Ten million OCSF events, each engine's native store.

Five queries a SOC actually runs, from a full-scan rollup to a selective single-user time-window lookup, timed as medians after warmup. The answers matched on both engines for every one of them.

Every query returned identical rows on both engines, in both the query-the-file-in-place and native-store configurations, at one million events and at ten. That is the half of the interchangeability claim that has to be true, and it held: four of the five queries are byte-identical SQL across the two engines, and the fifth differs by a single token, DuckDB's // against ClickHouse's intDiv. The latencies traded by query shape and the gaps stayed modest, no wider than about 2.6×, with DuckDB generally ahead at this single-node scale and ClickHouse edging the time-bucket rollup inside its own MergeTree, and DuckDB loaded the ten-million-row corpus several times faster (457 ms against 3.9 s). So the swap is cheap and the answers come out the same.

What this is, and what it isn't.

The boundary matters as much as the result. This is embedded ClickHouse, not a tuned cluster, and a million to ten million rows sits below the scale where MergeTree's ordering and indexing and ClickHouse's distributed scale-out start to pay, and neither engine was hand-tuned. So this does not contradict the lab's other ClickHouse number: the flagship two-regime result against a schema-on-read SIEM (the lakehouse engines 5–62× faster on the hunting-shaped aggregations, 46.8× native / 10.1× Iceberg on the five-query average, the index winning the simple lookups) is a different comparison at a different scale — and it does not lower ClickHouse's score for the dashboard-serving archetype in the matrix, which rests on exactly that large-scale latency, so the two results sit beside each other. As with the flattening benchmark this is Tier B, the corpus is synthetic, and the millisecond figures are medians on one machine; read them as which engine wins which query shape rather than as numbers to expect on your own hardware.

Single-event rules ported to all four backends. Correlation is where they split. Lucene refuses every correlation rule, which is the safe failure: a filter-only language cannot aggregate, so it raises at compile time rather than emit something that means less than the rule. Splunk SPL and ES|QL preserve the count, distinct-count, and unordered-temporal correlations in full (aggregation, threshold, and the time window all present), and both refuse the ordered-sequence type.

OpenSearch PPL is the one to watch. It translates every correlation type, including the ordered sequence the others refuse, but on the brute-force and spray detections it drops the time window: a rule meant to fire on ten failed logons in five minutes compiles to one that counts over the whole search range and fires on ten-ever. The query is valid, it runs, it looks correct, and nothing errors, which is what makes the loss easy to miss. PPL keeps the window on the temporal rules, so the drop is specific to its count path.

What this is, and what it isn't.

This is Tier B and it measures what the pySigma compiler emits, not what a target SIEM executes, since a window absent from the query might be supplied by a dashboard time range, and a present construct is not proof of correct execution. But the query is the artifact a practitioner copies into a SOC, so a dropped window is one someone has to add back. The fidelity checks are disclosed, the verbatim queries are recorded, and a newer backend release can move any cell, so the result is pinned to specific versions and meant to be re-run.

Both sources map into the class built for them, and both lose meaningful fields on the way. Okta's System Log lands 58% of its login fields on a typed OCSF attribute and loses the rest to coercion or the unmapped bag: a seven-value outcome enum narrows to three, the risk and threat signals sit in a free-form map OCSF has no typed home for, the forwarding chain flattens to a bare list of IPs, and the anonymizing-proxy flag has no endpoint attribute to land on. CrowdStrike's detections do better at 70%, because hashes and command lines are well modeled, but its ATT&CK tactic and technique arrive as flat strings where OCSF wants an id and a name both, its IOC type has to squeeze into a fixed enum, and its multi-action response bitmask collapses to one disposition.

The second finding is sharper, because it is about what a shipped mapper does rather than what the schema allows: OCSF 1.8.0 has a typed or coercible home for 36 of Okta's 50 login fields, but Okta's own open-source reference mapper carries only 18 of them into the event, leaving the autonomous-system, ISP, network-zone, and credential-type fields unmapped even though the schema would hold them. So the loss is partly what the standard cannot represent and partly what a shipped integration leaves behind, and that second gap is the larger of the two. Of eleven named detections scored against the result, eight lose at least one field they depend on, while three survive clean, including impossible-travel and known-bad-hash, because geo and file hashes map without a fight.

What this is, and what it isn't.

This is Tier B and it scores a documented mapping judgement against real vendor schemas and the real OCSF 1.8.0 schema, not production telemetry. Every OCSF target is validated against a checked-in copy of the schema, so a mapping cannot point at an attribute that does not exist, and the run is deterministic, scoring twice and asserting the two are identical before it writes anything. The Okta inventory is the login event family; the CrowdStrike inventory is the public Detection Summary Event, not the gated full Falcon Data Replicator schema, so the coverage figures are for those field sets, not for everything either vendor emits. The per-field calls are a judgement, published one line each so they can be argued with.

The headline is +0.72 on a scale where zero would mean coarsening hurt the adversary queries no more than the routine ones. It clears that bar because the routine control sits at exactly zero: the coarse store counts authentications, ranks destination ports, and rolls up egress bytes precisely as the fidelity store does, which is the point, because it isn’t a crippled strawman, it’s the store that serves the dashboards perfectly and then can’t reconstruct the breach. What it loses is specific. The 60-second beacon dissolves into 5-minute flow rollups. The base64 PowerShell payload is truncated away. The privilege escalation with no MFA looks identical to the 267 routine policy attaches because absence was written as false. The one human wearing an endpoint SID, an IAM principal, and an assumed role can no longer be closed into a single actor.

And not everything breaks, which is the honest shape of the result rather than a clean sweep: the kill-chain ordering and the dwell-time calculation mostly survive coarsening, because their evidence still reads through a five-minute rollup. The queries that crater are the ones whose evidence the coarse store physically threw away, and a benchmark that claimed every adversary query broke would be less believable, not more.

What this is, and what it isn't.

This is Tier B, and a first pass. The corpus is synthetic and the headline intrusion is one APT29-style chain on a single machine, so the magnitudes are specific to this testbed, not production rates. The coarse store’s every choice, from indexing on receipt time to rolling network into flows, flattening identity, truncating command lines, coercing missing booleans, and sampling rare DNS, is a documented real-world default written up alongside the code, and the routine control is the evidence it isn’t a strawman. The piece I can’t supply myself is the gate that would move this past a first pass: an independent practitioner confirming the coarse store resembles what shops actually build.

The one thing I did push on, because it’s the obvious objection to a synthetic result, is whether the gap is a property of coarse normalization or just an artifact of this one attack. So I ran a second, independent chain through the same frozen battery, a different actor on a different subnet with a different C2 domain and IP, a different encoded payload, and an SMB lateral move where the first chain used RDP, with the questions reading their indicators from ground truth rather than hardcoding them and the same background noise underneath so the chain itself was the only thing that changed. It landed at +0.71 against the first chain’s +0.72, with the same per-mechanism shape and the routine control still clean. The magnitudes are still specific to this testbed, but the mechanism reproduces across two unrelated attacks, which is the part I had half-expected to be fragile and wasn’t.

Putting the schema in front of the model is what moves the needle — invented paths drop from 0.99 to 0.69 — and the ontology-thinking discipline cuts them furthest, to 0.60. The control is the honest surprise: the conceptual grounding note lands at 0.72, exactly where a wrong-class grounding note lands, and neither beats schema-alone. So the grounding note’s content did no work here; it was extra tokens, and the reasoning discipline, not the grounding, is the lever. Exact path-correctness stays near zero throughout, because a 3.8-billion-parameter local model rarely lands the precisely-right path even with the schema in hand, which is why silent-error rate is the headline. That the formal grounding pays nothing over plain schema-conformance for field mapping is the same result the deductive grounding work found, now seen on a different task. Whether the discipline’s lift is a sub-frontier teaching effect or holds at the frontier is the next question — it needs the frontier leg this first pass doesn’t yet run.

On a 14-billion-parameter local model, the text-to-SQL baseline answers two of four adversary queries correctly — the single-table filters (the exact encoded payload, the privilege escalation with no MFA) — and fails the two that need more (the beaconing-cadence detection and the first-seen domain), where it writes SQL that errors or returns nothing. Result accuracy is 0.50 and the silent-error rate is zero: the failures are loud. That is the honest and slightly surprising first-pass observation, because the confidently-wrong tax that NL2KQL measured at ~42% in security telemetry is a frontier-model phenomenon, and a sub-frontier local model doesn’t reach it — it breaks visibly instead. Whether a formal rewrite avoids the silent errors a frontier LLM does make is the actual head-to-head, and it needs the frontier leg this first pass doesn’t yet run.

The OBDA arm is the formal counterpoint: Ontop exposes the store as a virtual RDF graph and rewrites SPARQL to SQL over an OWL2QL ontology. On the three adversary-tail queries OWL2QL can express, it is exact — three of three correct, zero silent errors. The five it can’t express (cadence, cross-source ordering, recursive identity closure, dwell, distinct-count — all aggregation or recursion, outside first-order rewriting) it refuses rather than guessing. That is the shape the head-to-head is built to surface: the formal rewrite answers fewer queries but is never silently wrong on the ones it answers, and its failures are a loud out-of-expressivity boundary, where a frontier LLM’s failures on this tail are silent. Which kind of failure you can live with is the real decision; the pending GraphRAG arm and a frontier text-to-SQL leg would complete it.

On the small-commit rung the file-write contract pays a per-commit metadata-and-manifest tax — roughly four files and about twice the commit latency of the transaction contract, and several times the storage. On large batches the gap narrows, because that per-commit overhead is paid once over many rows, which is the honest shape: the write contract matters for streaming, not for bulk load. The systems result underneath is the read-contract check — one engine reads both tiers and returns identical answers for the same logical data, so the unified-read-contract premise holds here for these two backends, once you write a version hint that DuckDB’s Iceberg reader needs and the writer doesn’t emit (a small, real interop wrinkle worth knowing about).

Arrow transport: ADBC vs JDBC

Pulling an OCSF result set out of the store over ADBC (Arrow, columnar) versus JDBC (row-oriented): ADBC is roughly **5–10× faster** than a native-JVM JDBC client on the same DuckDB query, scaling with result size. (An earlier pass reported ~276× by timing JDBC through a Python/JPype bridge; a native Java JDBC baseline shows that was ~40–50× bridge overhead, not transport — the columnar-vs-row advantage is the single digits.) An edge-case type battery (HUGEINT, decimal, microsecond timestamp, null, array, map) came back clean on both transports. Code →

Iceberg metadata & compaction — the small-files tax

As an Iceberg table accumulates small streaming appends, scan-planning cost grows with the file/manifest count (planning reads every manifest before touching data), and compaction recovers it. The magnitudes here are illustrative of that mechanism, not production figures — measured by pyiceberg's own planner on deliberately small files — and this is a within-Iceberg fragmentation cost, not a format comparison. Code →

DuckLake vs Iceberg, large-scan reads

The read-side companion to the write-contract benchmark: 10 million rows materialized in both formats and read by the same engine. They return identical answers and land within ~2× of each other depending on the query shape — interchangeable on read, so the format choice rides on the write pattern, not the scan. Code →

Sigma rules, executed over the OCSF store

The execution complement to the Sigma-portability benchmark: real Sigma rules compiled to SQL and run against the planted-chain store detect all four chain stages. The precision split is the honest half — specific rules (encoded PowerShell, no-MFA escalation, a known C2 domain) fire cleanly, while a naive "any RDP connection" rule catches the lateral-movement needle but drowns in thousands of benign port-3389 connections. Multi-event correlation rules go further: a temporal-ordered exec→lateral sequence is detected (linking the process and network sources via a host key the fidelity store preserved), but it compiles on only one of four backends, and the event-count rule's time window is silently dropped by the SQL backend — correlation portability is where the standard frays. Code →

DuckLake inlining vs Iceberg — small files at the source

A hundred small streaming commits leave 100 data files under classic Iceberg (plus a manifest and metadata file each), 100 under DuckLake with inlining off, and zero under DuckLake with inlining on — the rows live in the catalog until a flush threshold, so the tiny files are never created rather than created and compacted later. The data stays queryable throughout. Code →

Storage endurance — is write-intensive NVMe over-specified?

Measured write amplification on OCSF data is ~0.43 — compression outweighs the compaction rewrite, so fewer bytes hit disk than the raw log volume — which projects to well under 1 drive-write-per-day across realistic security ingest rates, far below the read-intensive (~1 DWPD) and write-intensive (~10 DWPD) tiers. The write-amp is measured; the DWPD is a transparent projection you can re-run with your numbers. Code →

Harness vs model — a schema-constrained OCSF mapper

A deterministic mapper that only ever emits paths that exist in OCSF 1.8.0 has a silent-error rate of zero by construction — versus the local model's 0.60–0.99 from the mapping-oracle benchmark — and even edges it on overall path-correctness, at a coverage cost (it marks unfamiliar fields unmapped rather than guessing). The lesson isn't "rules beat models"; it's that the schema constraint in the harness does the safety work, and a model is only safe behind the same constraint. Putting the model behind that same constraint confirms it: constrained, the model is safe (silent-error zero) but still ~8× worse than the alias rules on correctness, so at this capability tier the harness, not the model, carries the result. Code →

Notebook format — a marimo hunt, reproducible and portable

An OCSF hunt written as a marimo notebook is a plain .py that re-runs identically headless (it finds the planted needles every time) and carries none of the execution-count and embedded-output state a Jupyter .ipynb accretes, and the hunt is portable SQL with no notebook-runtime lock-in. For detection content that has to be reviewed, versioned, and run anywhere, the authoring format is the lock-in frontier. Code →

Do Parquet readers verify page checksums?

A one-byte flip inside a checksummed Parquet page is a three-way split across readers: chDB verifies the CRC by default and errors, pyarrow and Polars ship the verifier but leave it off, and DuckDB and DataFusion have no read-side check at all, so four of five hand back a confident wrong sum (with no checksum, all five do). It's the same silent-wrong-answer failure mode as the engine bugs, one layer deeper, and the integrity backstop is one keyword argument away yet off in the path almost everyone uses. Code →

Parquet encoding × library decode correctness

Six readers across the encoding grid (PLAIN, RLE_DICTIONARY, the DELTA family, BYTE_STREAM_SPLIT) checked against exact ground truth. On these versions there are no silently-wrong cells: the exotic encodings fail safe (fastparquet and DuckDB error on the ones they don't support) rather than mis-decode, and at default settings every writer's output round-trips through every reader, including DuckDB's deprecated PLAIN_DICTIONARY. The empirical companion to the Apache implementation-status matrix, checking that claimed support and a correct answer are the same thing. Code →

Does pruning ever drop the needle?

Row-group statistics, the page index, and bloom filters let an engine skip data it believes can't match, which is sound only if it never skips a block that does. A sorted-versus-shuffled A/B over an identical million-key file (only the row order differs, so any disagreement isolates a pruning bug) plus a bloom-filtered file, across five engines, finds every engine sound. The chDB equality-pushdown undercount does not generalize into unsafe pruning, so this stands as the regression guard for the pushdown paths rather than a new defect. Code →

Compute determinism and Parquet encryption portability

SIMD dispatch (Arrow forced scalar, SSE4.2, AVX2) is byte-identical, so vector width is not a determinism risk; but the same float column summed by five engines lands on three last-ULP-different totals while the integer sum, count, min, and max are bit-identical, so exact-typed answers are safe to compare and to hash for chain-of-custody and float-derived ones need a stated tolerance. And a Parquet-Modular-Encryption file is readable only by the library that wrote it holding the key, so at-rest encryption inside the file revokes the open read contract unless you keep it at the volume layer. Code →

NULL, type-coercion, and timezone semantics across engines

The shapes detections actually use, over byte-identical data against a SQL-standard answer. The everyday NULL traps are portable — <>, NOT IN, and IN all silently drop NULL rows on every engine — but two diverge: NOT IN (a, b, NULL) is empty by SQL three-valued logic on DuckDB and DataFusion, a silent allowlist bypass, while chDB returns the rows, and a time-window count under a non-UTC session disagrees three ways. The same rule gives different answers per engine, the answer-equivalence risk pushed into query semantics. Code →