ClickHouse for security data

Scope

Design and implementation of a ClickHouse store for security telemetry such as CloudTrail, VPC Flow Logs, Tetragon, Falco and Cilium Hubble events.

Area	Included
Ingestion	S3 to ClickHouse with S3Queue and ClickHouse Keeper, or an existing Vector pipeline
Schema	Ordering keys, partitioning, codecs and LowCardinality choices, based on the queries you run
Layers	Raw events kept separately from normalized tables (OCSF where it fits)
Retention	TTL and tiered storage per event class, S3-backed storage
Queries	Investigation query library and dashboards
Operations	Sizing, upgrade procedure, monitoring, backup

Inputs needed

• The data sources and their approximate daily volume.
• The investigations you expect to run, and how far back.
• Retention requirements, including any compliance minimums.
• Where alerts should go, if ClickHouse is not the alerting layer.

Design notes

Ordering key and partition scheme determine query speed more than any other setting, so they are chosen from real query patterns and tested on your data before the schema is finalized. Raw events are kept so a change in normalization does not require replaying from the source. For a worked example of the ingestion path, see Tetragon without Kubernetes.

Phases

Phase	Output
Data review	Source inventory, volumes, retention and query requirements
Design	Schema, ingestion architecture, cost model
Build	Working pipeline and tables loaded with your data
Handoff	Query library, runbooks, walkthrough

What you get

• Ingestion from S3 using S3Queue or your existing pipeline
• Table schemas, ordering keys and TTLs matched to your query patterns
• Raw and normalized storage layers, including OCSF where it fits
• Investigation query library and dashboards
• Sizing, retention and cost model
• Backup, upgrade and operations runbooks

Related engineering notes

• Tetragon without Kubernetes: process and network events from EC2 to ClickHouse