Flattening away your detection logic.

This essay argues that flattening CloudTrail's nested JSON loses critical semantic relationships. Most notably, the difference between "field absent" and "field is NULL" for mfaAuthenticated silently broke a privilege-escalation detection. The diagnosis is unchanged: flattening still loses semantics. What changed is the recommended fix.

The original recommendation was ClickHouse Nested types as the primary architectural answer. Apache Iceberg V3 introduces the Variant type (semi-structured / nested JSON, ratified in Parquet October 2025), purpose-built for exactly this scenario: it preserves JSON absence-vs-null semantics natively, supports queryable nested structures inside a columnar table format, and works across multiple query engines.

For new lakehouse deployments in 2026 and beyond, Variant should be the default for security log types where field presence carries information (CloudTrail, Kubernetes audit, Azure Activity, Okta system logs). ClickHouse Nested remains valid for ClickHouse-only stacks; Variant is the cross-engine answer for Iceberg-based deployments. Both preserve the relationships this essay warns about.

Caveat: V3 Variant engine support is still rolling out across Spark, Trino, DuckDB, and Snowflake through 2026. Verify your engine supports variant_get() and equivalent semantics before relying on Variant in production.

When security teams migrate from traditional SIEMs (Splunk, Elastic) to modern lakehouses (ClickHouse, Iceberg, DuckDB), they face a fundamental architectural trade-off.

Traditional SIEM: stores logs as nested JSON documents, so relationships are preserved and userIdentity.sessionContext.mfaAuthenticated remains hierarchical. The semantic meaning stays intact because field presence and absence both carry information, which lets detection logic express security relationships directly. The weakness is that query performance degrades at scale, where billions of events turn into 30+ second queries.

Modern lakehouse: stores data in columnar format for compression and speed, and flattening is common, so userIdentity.sessionContext.mfaAuthenticated becomes the flat column userIdentity_sessionContext_mfaAuthenticated. That obscures semantic meaning because the NULL versus absent versus false distinctions are lost. The strength is 10–100× faster queries and 10–20× better compression; the weakness is that complex hierarchical relationships are harder to express and easier to break.

The migration from SIEM to lakehouse is a semantic translation that happens to involve schema conversion, and in most of the migrations I've watched the team treats it as the schema problem alone until a detection fails and forces the distinction into view.

Security detections often filter on action plus target combination: delete a sensitive S3 bucket, modify an admin user, access a PII database table. Flattening can break the association between action and target attributes.

CloudTrail events have 1–N resources (an S3 event touches the bucket plus the IAM user). Flattening to a single resource_ARN column loses every resource beyond the first. A detection rule checking "sensitive" in ARN misses events where the S3 bucket sits at index 1 instead of index 0.

Approach 1 (pick first resource only) and Approach 2 (fixed-width array of three) both fail:

-- Approach 1: wrong — loses data
CREATE TABLE cloudtrail_flat (
    eventTime DateTime,
    eventName String,
    resource_ARN String,     -- only resources[0].ARN
    resource_type String     -- only resources[0].type
);

-- Approach 2: wrong — brittle
CREATE TABLE cloudtrail_flat_array (
    eventTime DateTime,
    eventName String,
    resource_ARN_0 String,
    resource_ARN_1 String,
    resource_ARN_2 String   -- what if event has 4 resources?
);

The correct approach uses nested arrays:

CREATE TABLE cloudtrail_nested (
    eventTime DateTime,
    eventName String,
    userIdentity_userName String,
    resources Array(Tuple(
        ARN String,
        accountId String,
        type String
    ))
);

-- Query: Alert on DeleteBucket for S3 buckets with "sensitive" in ARN
SELECT eventTime, userIdentity_userName, eventName, resources
FROM cloudtrail_nested
WHERE eventName = 'DeleteBucket'
  AND arrayExists(r -> r.3 = 'AWS::S3::Bucket' AND r.1 LIKE '%sensitive%', resources);

A real-world pattern: a SaaS security team used Approach 1 for "simplicity" after migrating CloudTrail from Elasticsearch to ClickHouse. Detection rule alerting on S3 bucket deletions for buckets with "backup" or "archive" in the name missed 17 deletion events over 4 weeks, because the backup buckets were always resources[1] rather than resources[0]. Nobody noticed until a customer reported missing backups, by which point the four-week blind spot had become a customer data loss incident and a CISO escalation.

Advanced detections correlate user activity across multiple data sources (O365 login + VPN connection + endpoint activity). Entity resolution, mapping the same user across different identifier schemes, requires dimension tables.

O365 sign-in log: userPrincipalName = alice@corp.com. VPN connection log: username = CORP\alice. EDR process execution: userSid = S-1-5-21-...-1105. Detection rule: "Alert on O365 login from VPN IP where endpoint shows suspicious process execution within 5 minutes." Requires correlating all three identifiers as the same person.

The naive approach joins on mismatched identifiers and fails. The correct approach builds a user dimension table:

CREATE TABLE users (
    user_id String PRIMARY KEY,
    upn String,                -- alice@corp.com (O365)
    domain_username String,    -- CORP\alice (VPN)
    sid String,                -- S-1-5-21-...-1105 (EDR)
    department String,
    manager String,
    risk_score UInt8,
    mfa_enrolled Bool
);

-- Fact tables reference user_id
CREATE TABLE o365_logins_normalized (
    eventTime DateTime, user_id String, ipAddress String, appDisplayName String);
CREATE TABLE vpn_logs_normalized (
    eventTime DateTime, user_id String, sourceIp String, assignedIp String);
CREATE TABLE edr_logs_normalized (
    eventTime DateTime, user_id String, hostname String, processName String);

-- Query with entity resolution
SELECT o.eventTime, u.upn, u.department, u.risk_score, v.assignedIp, e.processName
FROM o365_logins_normalized o
JOIN users u ON o.user_id = u.user_id
JOIN vpn_logs_normalized v ON v.user_id = u.user_id AND v.assignedIp = o.ipAddress
JOIN edr_logs_normalized e ON e.user_id = u.user_id
WHERE u.risk_score > 6
  AND e.eventTime BETWEEN o.eventTime AND o.eventTime + INTERVAL 5 MINUTE;

Entity resolution happens once during ingestion, not on every query. The user dimension is small (10K–100K rows), indexed lookups are fast, and 200+ detection rules reuse shared user context. Flattening doesn't directly break cross-source correlation, but the absence of an entity dimension table does. Normalize identifiers at ingest; enrich at query time. One caveat worth naming: which identifier becomes canonical is rarely a technical decision. Joe Reis calls identity resolution a political problem disguised as a technical one, and in a security org that is exactly right. To EDR a "user" is a SID; to IAM an account with entitlements; to a cloud audit log an assumed-role principal three hops from a human. Whose definition wins, and whose asset count changes when you dedupe honestly, are ownership questions the dimension table makes visible rather than resolves.

The five patterns above are all structural: they break when you collapse nested shape into flat columns. The data-modeling world has a name for the larger thing they are instances of. Joe Reis calls it context collapse: the distinctions among who did what, when, where, and what it meant, erased to save space or time. Flattening is the structural face of it, but the next two failure modes are not about structure at all, which is part of why they get less attention, and the first of them is grain.

Grain is the answer to one question: what does one row represent? One row per process execution, one row per netflow record, one row per five-minute summary. There is a near-theorem about it that I have never seen contradicted: you can always aggregate up from fine-grained data, and you can never deterministically disaggregate from coarse-grained data. Roll a thousand connection records into a one-minute-per-host summary and the summary is correct and cheap. It is also a one-way door. The 43-byte beacon to a newly registered domain that repeated every 60 seconds is now indistinguishable from noise inside an average. You cannot get it back from the summary. The information is not hidden; it is gone.

This is a security problem because the economics push hard toward coarse grain, and hardest on the high-volume sources where an intruder hides. Reis concedes the point in a footnote: machine-generated telemetry is the exception to "when in doubt, go finer," because aggressive pre-aggregation is often required to avoid bankrupting your storage budget. A schema-on-read SIEM at a dollar-per-gigabyte-per- day list price turns full-fidelity DNS, netflow, and EDR into a budget line nobody wants to defend. Sampling, coalescing, and summarizing at ingest are the rational responses, and every one is a grain decision that is irreversible at the normalized store.

What makes this matter is the asymmetry between the two kinds of query. Routine queries tolerate coarse grain, since failed logins per user per day is happy at daily grain, but adversary-relevant queries run the other way: lateral-movement reconstruction needs the individual events in order, low-and-slow beaconing needs the inter-arrival timing an average destroys, and living-off-the-land detection needs the specific command line rather than a count of PowerShell runs. So the queries that coarse grain breaks are disproportionately the ones that catch a competent intruder.

I measured that trade on the APT29 evaluation telemetry, the same corpus the grain claim above rests on. Keeping full fidelity, with un-truncated command lines, script blocks, and call traces at atomic grain, cost about 1.8× the storage of a coarsened store over the same ~143,000 events (1.8 MB against 1.0 MB), and the overhead concentrated in exactly the fields whose truncation goes dark on an intruder: the PowerShell script-block column ran roughly ten times larger, the command line about four. So the fidelity that preserves the adversary-relevant recall, which on this corpus runs close to twice the coarse store's, costs under two times the bytes to keep, which is the shape of a false economy where you save a little under half the storage and lose about half the detection that matters, on the high-volume sources where an intruder hides. (Single corpus, one coarsening config; this is the storage side of the bill.)

I have since measured the compute side too, running the same detection battery over both stores, and it tells the same story from the other direction. In aggregate the fidelity store costs only about 1.3× the query time, but that average hides the shape of it, because the premium is concentrated rather than spread: the adversary-relevant detections cost roughly three to four times more to run against full fidelity while the routine ones cost the same, and almost all of that comes from the full-text scans over the un-truncated PowerShell script blocks and command lines. So the compute leg agrees with the recall leg, since the detections that coarsening blinds are the same ones that fidelity makes most expensive to scan, and the ordering a budget should take from this is that storage rather than query-compute is the costly axis of keeping fidelity. (Single corpus, one coarsening config, single host on DuckDB; the queries run in a few milliseconds over ~143,000 events, so the ratios travel further than the absolute numbers do.)

The honest qualifier: irreversible means irreversible at the normalized store. Keep the raw telemetry somewhere cheap and the hybrid pattern from schema-on-read versus schema-on-write recovers what the analytic store dropped. That mitigation is real and not free, since you are paying to store raw you declined to query. The grain problem does not vanish; it becomes a question of whether you will pay twice.

Schema validation

• Document all optional fields (presence/absence meaningful vs NULL).
• Map all array fields.
• Document hierarchical relationships.
• Flag fields where NULL ≠ absent ≠ false.

Detection logic validation

• Export all detection rules from current SIEM.
• Translate each rule to lakehouse SQL.
• Test translated rules on sample data.
• Validate results match SIEM.

Parallel run

• Dual-write to SIEM and lakehouse for 30+ days.
• Run detection rules in both systems.
• Compare alert volume (zero missed detections).
• Validate alert parity achieved.

Performance vs. correctness

• Measure query latency: flat vs nested.
• Decide: is 2–5× slower acceptable to preserve relationships?
• Priority: correctness over performance. Missed detections are unacceptable.

The section above is the test I said I'd run, so I built a first version of it and put it in a public repository you can clone and re-run. It is not yet the full single-corpus battery with the priced fidelity store and the independent practitioner sign-off; it is the smaller, honest start. Three controlled synthetic corpora, one per mechanism, each scored against a ground truth I planted, with the same detection logic run over a lossy schema and a fidelity-preserving one. DuckDB stands in for the engine and Variant-style JSON stands in for the preserved store, and every number is reproducible, because the suite runs twice and checks the output is byte-identical before it prints, and two separate processes produced the same results digest.

The absence-vs-NULL case is the cleanest, because it is not probabilistic. Plant privilege-escalation calls made without MFA, flatten the column so the absent mfaAuthenticated becomes NULL, and the naive translation that checks = 'false' recovers none of them: zero of 141 at a thousand events, zero of 14,140 at a hundred thousand. The preserved schema and a NULL-aware query recover all of them. That is the opening case study's bug reproduced as a measurement rather than an anecdote, and the miss is 100% by construction, because once the column is flattened, absent and NULL are the same byte.

Grain is where I most wanted to be wrong, since I have an obvious incentive to find the gap I predicted. I planted beacons at a regular sixty-second interval and benign decoys with the same per-bucket volume and the same forty-three-byte payload, bursty within the bucket, so the only thing separating malice from noise is inter-arrival timing. At atomic grain a jitter test separates them perfectly, at F1 1.00. Rolled up to a five-minute (source, destination) grain the individual timestamps are gone, the fair-effort detector falls back to steadiness of per-bucket counts, and it can no longer tell the beacon from the decoy: F1 drops to 0.50 while the volumetric routine queries, bytes per host and connection counts, come back exact to the row. That asymmetry is the whole claim, since the rollup is free for the routine half and ruinous for the timing-dependent adversary half.

The floating-timestamp case is the quiet one. Cross-source chains that are genuinely within a five-minute window in UTC, with their sources sitting in real timezones, correlate perfectly when the offset is preserved and lose exactly the cross-zone half when it is not, so recall drops from 1.00 to roughly 0.46–0.50 at a cross-zone mix near half, and the same-zone chains still look right, which is why nobody notices. I want to be careful about what these numbers are and are not. The grain F1 and the floating recall are not universal constants, they track the corpus I built, and they track it in a way I can write down: the grain F1 is 2 / (2 + decoy-to-beacon ratio) exactly, so the 0.50 is just the ratio-of-two point, and the floating recall is 1 minus the cross-zone fraction, so the 0.46–0.50 is the half-cross-zone point — change the ratio or the cross-zone mix and the lossy number slides along that curve while the preserved store stays pinned at 1.00. So the honest reading is directional, a controlled demonstration of the mechanism rather than a production rate, and the methodology file says so in as many words. Only the absence result is structural enough to quote without that hedge.

So the grain and time claims move from Tier D to Tier B: reproducible and first-party, but synthetic and controlled, not a production measurement. The version that would earn Tier A is still the one in the section above, with one real multi-source corpus, the fidelity-preserving store priced against the storage bill that drove normalization in the first place, and an independent practitioner who has run a security lakehouse confirming the lossy store resembles what shops actually build. This is the first number on a question the field argues about in the abstract; it is not the last one.

There's a second, quieter pressure toward flattening that isn't about information loss at all, and I measured it the same way. Keep the nested OCSF shape — the endpoint structs and the observables[] list-of-structs — and the open read contract holds only one level deep. Across DuckDB, DataFusion, chDB, and Polars, scalar struct access (dst_endpoint.port) and list length read identically, but the natural observables question, "does any observable carry this type", isn't portable: DuckDB, chDB, and Polars each express it and agree, while DataFusion can't apply a per-element struct-field predicate in a WHERE clause at all. So an open table format guarantees every engine can read the bytes, not that every engine can ask the same nested question the same way, and that gap at the list-of-struct boundary is a measured reason teams flatten observables[] into their own columns before querying. Flattening trades the schema fidelity this essay spends its length defending for query portability, so the honest position is that both costs are real and the decision is which one you'd rather pay.