OCSF lakehouse storage turns on engine choice, not schema width

At a glance

What this is: This analysis argues that OCSF storage topology is determined by the engine beneath the lakehouse, with Iceberg favouring wide sparse tables and ClickHouse favouring promoted columns plus payload spillover.

Why it matters: IAM, SOC, and data platform teams building OCSF pipelines need to align schema design with engine behaviour, or they risk poor performance, brittle ingest, and avoidable rework.

Context

OCSF defines the normalized event shape, but it does not define how those events should be stored for analysis. That separation matters because storage layout changes query performance, metadata growth, and pipeline complexity, especially once OCSF spans many classes and profiles.

The practical governance gap is assuming that one schema strategy works everywhere. In reality, lakehouse engines and OLAP engines impose different costs on width, sparsity, schema evolution, and partitioning, so the right design depends on where the data lands.

For identity and security platforms, this is a data engineering decision with governance impact: the storage model influences how reliably telemetry can support detection, investigation, and retention at scale.

Key questions

Q: When should teams use a wide OCSF table instead of splitting by source or class?

A: Use a wide OCSF table when the engine handles sparse columns efficiently, the query model benefits from cross-class analysis, and schema evolution needs to stay metadata-driven. In Iceberg, that often makes a single nested table practical. In ClickHouse, the same design usually becomes too expensive unless the hot fields are promoted and the rest spill into payload storage.

Q: Why do wide OCSF tables work better in Iceberg than in ClickHouse?

A: Iceberg stores sparse columns cheaply because absent values cost nothing in a file and schema changes are mostly metadata updates. ClickHouse pays a physical cost per column during inserts and merges, so very wide tables become expensive much faster. That difference is why the same OCSF schema can be fine in one engine and painful in another.

Q: What are the signs that an OCSF table layout is too wide for the chosen engine?

A: The warning signs are slower planning, bloated metadata, expensive merges, rising insert latency, and a growing number of columns that almost never carry values. In Iceberg, that usually points to manifest and statistics overhead. In ClickHouse, it often means the table needs column promotion or a split between hot fields and a payload column.

Q: How should security data teams balance query performance and schema completeness in OCSF pipelines?

A: Prioritise the fields analysts query most often, then preserve completeness in a spillover structure or nested payload. That approach keeps the active query path fast without throwing away low-frequency data needed for investigations. The right balance is not full flattening versus full nesting, but selecting the engine-friendly layout that keeps both performance and fidelity acceptable.

Technical breakdown

Why OCSF schema normalisation does not solve storage design

OCSF standardises the event contract, not the physical layout. JSON defines the normalized event shape, but the table strategy still depends on the engine, the query pattern, and the amount of sparsity the storage layer can absorb. A single wide table, per-source tables, per-class tables, and a semi-structured payload pattern all solve different parts of the problem. The key technical mistake is treating schema normalization as if it also resolved file layout, statistics cost, and partitioning behaviour. It does not. Once OCSF events are mapped, the storage engine determines whether width is mostly harmless metadata or an expensive physical design choice.

Practical implication: Choose the storage pattern after you choose the engine, not before.

Why Iceberg tolerates wide sparse OCSF tables

Iceberg with Parquet files makes width relatively cheap on the data path. A column absent from a file simply reads back as null, and null-heavy columns compress very efficiently. Adding a column is a metadata operation, not a file rewrite, and partition spec changes work the same way. The real constraint is metadata hygiene: Iceberg still tracks per-file statistics for pruning, and very wide schemas can bloat manifests and slow planning. That means the storage question in Iceberg is less about raw bytes and more about how much schema width you want to expose to the statistics layer and any engine-level column limits.

Practical implication: Use width sparingly for Iceberg planning hygiene, not because the files themselves cannot handle it.

Why ClickHouse pushes OCSF toward promoted columns and payload spillover

ClickHouse behaves differently because each physical column adds real per-part and per-insert overhead. Wide tables increase file counts, memory use, merge cost, and the overhead of Nullable columns. That is why production ClickHouse SIEMs often promote only the hot fields into real columns and keep the rest in a semi-structured payload. This pattern preserves query speed where it matters while avoiding the cost of carrying thousands of sparse columns through every insert and merge. In practice, the engine makes the promotion decision for you: if you want one table, you usually need a deliberate promoted-column design.

Practical implication: Promote frequently queried OCSF fields and spill the long tail into payload storage.

NHI Mgmt Group analysis

Engine choice is the real governance boundary for OCSF. OCSF gives teams a common event contract, but it does not erase physical storage trade-offs. The same wide schema that is tolerable in Iceberg can become operationally expensive in ClickHouse, which means schema governance has to include engine behaviour, not just data model consistency. Practitioners should treat engine selection as part of the telemetry architecture decision, not as an implementation detail.

Wide-table anxiety is often misplaced in lakehouse designs. In Iceberg, width mainly affects metadata and planning, not file storage in the way many warehouse teams expect. That creates a useful named concept here: schema width is not storage cost. Once teams understand that distinction, they can stop over-optimising away columns that are cheap in the data path and focus instead on stats coverage, partitioning, and query pruning.

ClickHouse forces an explicit hot-path versus long-tail split. The engine rewards promoted columns for the fields analysts query most often, while the rest of the OCSF payload belongs in a spillover structure. That is not a compromise to apologise for, it is the control surface. The practical conclusion is that the storage model should mirror access frequency, not the theoretical completeness of the schema.

OCSF lakehouse design is really about preserving analysis fidelity without creating operational drag. Security teams need telemetry that stays queryable as schemas evolve, because detection and investigation depend on reliable structure over time. The practitioner takeaway is to align OCSF routing, partitioning, and promotion strategy with the engine's physical cost model before scale forces the issue.

What this signals

OCSF governance now needs to include storage-engine literacy, because telemetry architecture can fail long before the schema itself does. Teams that design for the engine they actually run will get better retention, faster investigation paths, and fewer surprises when event volume grows.

Schema width is not storage cost: that distinction matters most in lakehouse architectures, where metadata can become the real bottleneck. Security engineering teams should treat table design, partitioning, and column promotion as part of detection readiness, not just data modelling.

For practitioners

• Select the storage strategy by engine first Decide whether the target is Iceberg or ClickHouse before fixing the OCSF table layout, because the same schema carries different physical costs in each engine.
• Use wide nested tables in Iceberg when schema breadth is expected Keep OCSF objects nested and accept sparsity when the engine is Iceberg, then focus governance on metadata hygiene, stats coverage, and partition design.
• Promote only hot OCSF fields in ClickHouse Model frequently queried attributes as real columns and route the long tail into a semi-structured payload so insert and merge overhead stays bounded.
• Treat schema evolution as a metadata workflow Plan for new OCSF classes and widening numeric fields as metadata-driven changes so ingestion does not depend on manual table rewrites.

Key takeaways

• OCSF standardises the event contract, but it does not decide how events should be physically stored for analysis.
• Iceberg can absorb wide sparse schemas more easily than many teams expect, while ClickHouse usually needs a more selective column strategy.
• The practical design rule is to match table layout to the engine’s cost model, then preserve long-tail fidelity through nested or payload storage.

Key terms

• OCSF: The Open Cybersecurity Schema Framework is an open model for describing security events, findings, and objects in a common structure. It lets vendors map their telemetry into a shared language so downstream analytics, investigations, and automation can work across sources more consistently.
• Iceberg metadata planning: The part of Iceberg that decides how files, partitions, and column statistics are tracked for efficient queries. It matters because wide schemas are usually limited by manifests and statistics, not by raw file storage, so metadata discipline becomes a performance control.
• Column promotion: Column promotion is the practice of turning frequently queried fields in semi-structured data into real table columns. It improves query performance for hot attributes while leaving infrequent fields in a payload or JSON column to avoid unnecessary physical overhead.
• Semi-structured payload strategy: A storage pattern that promotes frequently queried fields to typed columns while preserving the rest of the record in JSON or another flexible payload. It balances query speed with schema flexibility, especially in engines where every extra column has a measurable runtime cost.

Deepen your knowledge

The NHI Foundation Level course, the industry's only accredited NHI security programme, covers NHI governance, machine identity security, and secrets management. It helps identity and security practitioners build programmes that can withstand operational complexity across modern platforms.