Schema Type System v2

The schema YAML SSoT is the dfe-schemas repo (the version-pinned dfe-schemas wheel), registries included, and the engine keeps no copies.

Audience

This document is written for data subject matter experts -- people who understand their data, their queries, and their retention requirements. You do NOT need to be a ClickHouse expert. The schema system handles ClickHouse internals (codecs, index tuning, Nullable wrapping, type widths) under the hood. You pick meaningful types and describe how your data will be queried. The engine does the rest.

If you need a specific ClickHouse type that the primitives don't cover, there's an escape hatch (ch_override). But you shouldn't need it for 95% of columns.

Design Principles

No Schema Registry

There is no schema registry -- no CRUD service, no sync state, no intermediate store. Two sources of truth exist, and that's it:

Source YAML -- the meta schema definition (what the schema should be)
ClickHouse DDL -- the deployed table (what the schema actually is)

The engine reads the Source YAML, generates DDL, and applies it to ClickHouse. The loader reads the deployed table's columns and comments from system.columns at runtime. There is nothing in between.

Schema changes flow in one direction:

Source YAML (intent) → engine generates DDL → ClickHouse (deployed truth)
                                                    ↑
                                        Rust K8s services read at runtime
                                        (loader, archiver -- ONLY SSoT)

The Rust K8s services at scale slave from the deployed ClickHouse schema ONLY. They read column types, comments, and table metadata from system.columns at runtime -- never from YAML, config files, or any intermediate store. This means there is never an in-flight mismatch: the engine deploys DDL, ClickHouse stores it, Rust services read it. No sync, no cache invalidation race, no drift.

Schema Format: YAML

Schema definitions are YAML. YAML supports structured data (lists of attributes, nested overrides) and is consistent with the rest of the Source definition format.

CSVs remain only for enrichment files used by transform stages (e.g. GeoIP lookups, threat intelligence feeds, asset inventories). These are data files, not schema definitions.

Schema Lifecycle

Event	Action
Source created	Engine generates `CREATE TABLE` from common header + source schema
Schema field added	Engine generates `ALTER TABLE ADD COLUMN`
Schema field modified	Engine generates `ALTER TABLE MODIFY COLUMN` (type/codec/comment)
Source disabled	No DDL change -- table stays, no new data
Source deleted	Table preserved -- manual `DROP TABLE` if needed

The deployed ClickHouse table is always authoritative. If someone modifies the table directly (via SQL), the engine detects drift on next schema operation and can reconcile.

Four axes

A schema column definition has four independent axes:

type + attribute + use_case + comment (includes loader directives)

Each axis is optional where it has sensible defaults.

Schema Definition Format (YAML)

# meta_schema.yaml
columns:
  - name: _timestamp_load
    type: timestamp
    default: "now64(3)"
    order: 0
    comment: "@generated: now64(3)"

  - name: _timestamp
    type: datetime
    use_case: range
    order: 1
    comment: "@source: timestamp | now()"

  - name: _uuid
    type: uuid
    default: "generateUUIDv7()"
    comment: "@generated: generateUUIDv7()"

  - name: _org_id
    type: string
    cardinality: low
    use_case: dimension
    comment: "@source: org_id"

  - name: _source
    type: string
    cardinality: low
    use_case: dimension
    comment: "@source: first(_source) | topic_name"

  - name: _raw
    type: text
    use_case: substring_search
    comment: "@captured: raw_payload"

  - name: _json
    type: json
    comment: "@captured: raw_payload as JSON"

  - name: _tags
    type: json
    comment: "@source: first(tags/_tags/meta/metadata.tags)"

  - name: user_name
    type: string
    use_case: dimension
    comment: "@source: first(user_id/uid/id)"

  - name: source_ip
    type: ip
    use_case: range
    comment: "@source: src_ip"

  - name: severity
    type: string
    cardinality: low
    use_case: dimension

  - name: message
    type: text
    use_case: word_search

  - name: event_id
    type: integer
    comment: "@source: event_id"

  - name: latency_ms
    type: float
    use_case: range

Column Fields

Field	Required	Description
`name`	Yes	Column name
`type`	Yes	Primitive type (see below)
`cardinality`	No	`low`, `high` or `unknown` (the default) -- see below
`attribute`	No	List of storage attributes (e.g. `[not_null]`)
`use_case`	No	Query pattern hint -- determines indexing (see below)
`default`	No	DEFAULT expression
`order`	No	Position in ORDER BY / PRIMARY KEY
`comment`	No	Human description + loader directives
`ch_override`	No	Exact ClickHouse type -- bypasses primitive mapping

Type Axis: Primitives

Primitives are human-readable type names that map to ClickHouse types with sensible defaults. You don't need to know what Int64 or ZSTD(1) means -- pick the primitive that describes your data.

Primitive	What It Is	Default ClickHouse Type
`string`	Short-to-medium text (names, IDs, codes)	`String`
`text`	Large text (log messages, bodies, descriptions)	`String`
`integer`	Whole number (counts, IDs, event codes)	`Int64`
`float`	Decimal number (scores, latency, percentages)	`Float64`
`boolean`	True/false	`Bool`
`datetime`	Date and time with timezone	`DateTime64(3,'UTC')`
`timestamp`	Date and time -- never null (for ORDER BY / time columns)	`DateTime64(3,'UTC')`
`date`	Date only (no time component)	`Date`
`ip`	IP address (v4 or v6)	`IPv6`
`uuid`	Unique identifier (GUID, trace ID)	`UUID`
`json`	Structured/semi-structured data	`JSON`
`geo_point`	Latitude/longitude pair	`Point`
`enum`	Fixed set of allowed values (values defined in `default` field)	`Enum8(...)`
`map`	String keys to string values, never null	`Map(LowCardinality(String), String)`
`vector`	Embedding of floats, never null	`Array(Float32)`

Why simplified primitives?

A data expert knows they have an "integer" column. They don't need to decide between Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, or UInt64 -- that's 8 choices that require understanding ClickHouse storage internals. The engine picks Int64 because it covers the vast majority of use cases without overflow risk.

If you need a narrower or unsigned type (performance-critical high-volume tables), use ch_override:

  - name: http_status
    type: integer
    ch_override: UInt16       # Override: HTTP status codes fit in UInt16
    use_case: dimension

ClickHouse Type Override

When a primitive isn't sufficient, specify the exact ClickHouse type via ch_override. When set:

No automatic Nullable wrapping
No automatic codec selection
The type string is used verbatim in DDL
Attributes are still applied if specified

  - name: precision_ts
    ch_override: "DateTime64(6,'UTC')"
    comment: "Microsecond precision timestamp"

  - name: nested_data
    ch_override: "Nested(key String, value String)"

  - name: bitmap
    ch_override: "AggregateFunction(groupBitmap, UInt32)"

ClickHouse Type Registry

The engine maintains a canonical type registry -- a YAML file that maps each primitive to its ClickHouse implementation details. This is internal to the engine (data SMEs don't edit it), but it's documented here for completeness.

# dfe-schemas registries/types.yaml -- NOT edited by schema authors
primitives:
  string:
    ch_type: String
    codec: ZSTD(1)
    nullable: true

  text:
    ch_type: String
    codec: ZSTD(3)
    nullable: true

  integer:
    ch_type: Int64
    codec: ZSTD(1)
    nullable: true

  float:
    ch_type: Float64
    codec: ZSTD(1)
    nullable: true

  boolean:
    ch_type: Bool
    codec: LZ4
    nullable: false

  datetime:
    ch_type: "DateTime64(3,'UTC')"
    codec: "Delta, ZSTD(1)"
    nullable: true

  timestamp:
    ch_type: "DateTime64(3,'UTC')"
    codec: "Delta, LZ4"
    nullable: false         # Never null -- used in ORDER BY

  date:
    ch_type: Date
    codec: "Delta, ZSTD(1)"
    nullable: true

  ip:
    ch_type: IPv6           # Stores both v4 and v6
    codec: LZ4
    nullable: true

  uuid:
    ch_type: UUID
    codec: ~                # No codec (already compact)
    nullable: true

  json:
    ch_type: JSON           # Native ClickHouse JSON (v25.3+)
    codec: ZSTD(3)
    nullable: true

  geo_point:
    ch_type: Point
    codec: ZSTD(1)
    nullable: true

  enum:
    ch_type: "Enum8(...)"   # Values from `default` field
    codec: ZSTD(1)
    nullable: false

  map:                      # ClickHouse refuses Map and Array inside Nullable
    ch_type: "Map(LowCardinality(String), String)"
    codec: ZSTD(1)
    nullable: false

  vector:
    ch_type: "Array(Float32)"
    codec: ZSTD(1)
    nullable: false

ClickHouse Type Overrides Catalogue

For users who need ch_override, these are the supported ClickHouse types. The engine validates that ch_override values are in this catalogue:

Category	Types
Integers	`Int8`, `Int16`, `Int32`, `Int64`, `Int128`, `Int256`
Unsigned	`UInt8`, `UInt16`, `UInt32`, `UInt64`, `UInt128`, `UInt256`
Floats	`Float32`, `Float64`
Decimal	`Decimal(P,S)`, `Decimal32(S)`, `Decimal64(S)`, `Decimal128(S)`
Strings	`String`, `FixedString(N)`
Dates	`Date`, `Date32`, `DateTime`, `DateTime64(P)`, `DateTime64(P,'TZ')`
Boolean	`Bool`
IP	`IPv4`, `IPv6`
UUID	`UUID`
JSON	`JSON`
Geo	`Point`, `Ring`, `Polygon`, `MultiPolygon`
Complex	`Array(T)`, `Map(K,V)`, `Tuple(...)`, `Nested(...)`
Enum	`Enum8(...)`, `Enum16(...)`
Special	`Dynamic`, `Variant(...)`, `AggregateFunction(...)`, `SimpleAggregateFunction(...)`

GET /api/v1/schemas/vocabulary serves what a schema form offers, read from this registry: the primitives, the index use cases with their labels and the names they replaced, the attributes, the cardinalities with their default, and the override types with the primitive each refines. A type that takes parameters carries a template such as Decimal({precision},{scale}), so a form builds the override text without knowing the type. A label the registry does not declare reads off the entry's name. The retired lowcardinality attribute is not offered: a column declares cardinality, and the vocabulary says a stored lowcardinality reads as low.

Cardinality Axis

How many distinct values the column holds. One declaration, because it decides two things that used to be set apart with nothing keeping them in agreement: attribute: [lowcardinality] was the storage decision, hand-set, and exact_match choosing set(0) over bloom_filter was the index decision, derived separately.

Value	Storage	`exact_match` index
`low`	`LowCardinality(...)` -- dictionary encoding, which pays below about 10K distinct values	`set(0)`, holding every distinct value exactly
`high`	plain	`bloom_filter`, bounded and probabilistic
`unknown` (default)	plain	`bloom_filter`

unknown is the honest default. Nobody re-reviews a field that already looks decided, so a column with nothing measured says so and gets the safe way to be wrong.

An Elasticsearch index template does not carry cardinality, so an imported column is unknown.

attribute: [lowcardinality] is the retired spelling and still reads as cardinality: low. Declaring the two against each other is refused.

Attribute Axis

Attributes modify how the type is stored. Specified as a list in YAML -- multiple attributes can be combined:

Attribute	What It Does
`lowcardinality`	Retired -- the old spelling of `cardinality: low`
`nullable`	Allows NULL values (2x performance cost -- use only when NULL != empty)
`not_null`	Explicitly prevents NULL (overrides the primitive's default)
`materialized`	Column computed on insert, not stored in source data
`alias`	Virtual column computed at query time

Attributes Are Tied to Primitives

Like use cases, not every attribute applies to every type. The engine enforces valid combinations:

Attribute	Valid Primitives	Why
`lowcardinality`	`string`, `text`, `integer`, `float`, `date`, `ip`	Dictionary encoding only works on types with a finite value space. `json`, `geo_point` are not supported.
`nullable`	all	Any type can be nullable
`not_null`	all	Any type can be forced non-null
`materialized`	all	Computed on insert -- any type
`alias`	all	Computed at query time -- any type

If you specify lowcardinality on a json column, the engine rejects it at validation time.

Nullability Defaults

Each primitive has a default nullability (defined in the type registry):

Nullable by default: string, text, integer, float, datetime, date, ip, uuid, json, geo_point
NOT null by default: timestamp, boolean, enum

Override with nullable or not_null in the attribute list.

Why nullable is not the default for everything

ClickHouse stores Nullable(T) as two columns -- the data column plus a UInt8 bitmap tracking which rows are null. This doubles storage and halves query speed (measured: 229M rows/s -> 98M rows/s on GROUP BY with Nullable(Int64)). Booleans and timestamp are non-null by default for this reason.

Design decision: ORDER BY keys must not be Nullable. ClickHouse refuses a Nullable column in a sorting key unless the merge-tree setting allow_nullable_key = 1 is enabled, and even with it on a null in the sort key cripples index effectiveness. Rather than silently flip allow_nullable_key on -- or fail the whole table -- the engine drops any Nullable ORDER BY column from the sorting key and logs a warning. This applies to LowCardinality(Nullable(T)) too. To keep a column in the ORDER BY, mark it not_null (or give it a DEFAULT, which also forces non-null).

For payload columns where NULL means "not provided" (as opposed to empty string or zero), Nullable is correct. Don't fight it -- just keep it off your ORDER BY and high-filter columns.

Examples

# LowCardinality string, not nullable
  - name: category
    type: string
    cardinality: low
    attribute: [not_null]
    use_case: dimension

# Multiple attributes
  - name: region_code
    type: string
    cardinality: low
    attribute: [not_null]
    use_case: dimension

# Materialized column (computed from other columns at insert time)
  - name: day
    type: date
    attribute: [materialized]
    default: "toDate(_timestamp)"

Use Case Axis

Use cases describe how you query the column -- not how it's stored. The engine translates use cases into ClickHouse indexes and codecs in the generated DDL. You don't need to know what a set(0) or tokenbf_v1(8192, 4, 0) index is -- just pick the use case that matches your query pattern.

Use Cases

A use case names the question you ask the column, never the ClickHouse index that answers it. You declare the question; the engine picks the primitive, and can pick a different one on a later ClickHouse without your schema changing.

Use Case	The question	Example Columns
`dimension`	I filter and group by this: `WHERE status = 'error'`	status, severity, region, org_id, category
`exact_match`	I look up specific values	trace_id, request_id, span_id
`range`	I query ranges, between, time windows: `WHERE latency > 100`	latency_ms, timestamp, bytes, risk_score
`word_search`	I search for whole words: `WHERE hasToken(message, 'error')`	message, log_body, description
`substring_search`	I search for fragments inside words: `WHERE message LIKE '%connection refused%'`	syslog_message, windows_event_data
`key_search`	I search the keys and values of a map column	attributes, labels
`similarity_search(<dims>)`	I find records similar to this one	embedding
(empty)	No index	raw payload, metadata

similarity_search is the one use case that takes an argument: ClickHouse needs the vector's dimension count up front and cannot read it off the column. You give it that, and the engine still picks the method and the distance metric.

A field you query often belongs in a typed column with a use case, not inside _json. _json stores each value with its own type, row by row, so a path that holds both strings and numbers breaks =, IN and the string functions at query time, and no _json path has an index. Promoting the field fixes both: see Data evolution.

Use Cases Are Tied to Primitives

Not every use case makes sense on every type. The engine enforces valid combinations:

Use Case	Valid Primitives	Why
`dimension`	`string`, `integer`, `boolean`, `enum`, `ip`, `uuid`	Exact match -- needs discrete values
`exact_match`	`string`, `uuid`, `integer`, `ip`	Point lookups on high-cardinality identifiers, numeric ids and addresses
`range`	`integer`, `float`, `datetime`, `timestamp`, `date`, `ip`	Range queries -- needs orderable values
`word_search`	`string`, `text`	Token search -- only applies to text
`substring_search`	`string`, `text`	Substring matching -- only applies to text
`key_search`	`map`	The keys and values of a map column
`similarity_search`	`vector`	Nearest neighbours in an embedding space

If you specify word_search on an integer column, the engine rejects it at validation time with a clear error.

What the Engine Generates in the DDL

You don't need to know this to use the schema system. This section is for engine developers and anyone curious about what happens behind the scenes. It is what the engine emits today, on ClickHouse 26.3 -- not part of the vocabulary.

Use Case	ClickHouse Index Generated	Granularity	Notes
`dimension`	`set(0)`	4	Exact distinct values per granule
`exact_match`	`set(0)` on `cardinality: low`, else `bloom_filter`	4	The bloom filter is probabilistic -- false positives, no false negatives
`range`	`minmax`	4	Stores min/max per granule
`word_search`	`text(tokenizer=splitByNonAlpha)`	1	Native text index (GA v26.2). Deterministic, no false positives, row-level filtering. 45x faster than without index.
`substring_search`	`text(tokenizer=ngrams(3))`	1	Character n-gram text index for substring matching
`key_search`	two indexes, `text(tokenizer=array)` over `mapKeys(col)` and `mapValues(col)`	1	A text index refuses a `Map` column itself, so the keys and the values are indexed apart
`similarity_search(<dims>)`	`vector_similarity('hnsw', 'cosineDistance', <dims>)`	1	`hnsw` is the only method 26.3 implements
(empty)	No index	--

Note on text indexes: word_search and substring_search generate the GA text index (inverted index, v26.2+) instead of the older bloom-filter based tokenbf_v1 and ngrambf_v1. The text index is deterministic (no false positives), provides row-level filtering instead of granule-level, and is 10-100x faster for text search workloads. There is no pre-25.10 variant: every table gets the text index.

Every index, partition and TTL clause comes from dfe-schemas, which owns the one table renderer and the DDL catalogue (registries/ddl.yaml). The engine builds a TableSpec from the source and calls it.

An index whose answer changes is dropped and added again on the next apply. The new index covers parts written or merged after that, and existing parts gain it on merge: there is no MATERIALIZE INDEX.

Note on word_search vs substring_search: Both use the text index but with different tokenizers. word_search uses word-level tokenization (splitByNonAlpha) -- good for searching whole words in log messages. substring_search uses character n-grams -- good for fragments inside longer strings, like partial hostnames or embedded error codes.

Retention (TTL): every time-series table gets a TTL, 90 days unless the deployment sets DFE_ENGINE_CLICKHOUSE_DEFAULT_TTL_DAYS (clickhouse.default_ttl_days, 0 = no default) or an admin sets a console value (below). Precedence is the source's schema.ttl_days, then the table's own dfe-schemas definition, then the console value, then the deployment default. State tables (the detection checkpoint, the engine's internal and hunt-coordination tables) keep whatever their definition declares and never take the default. The schema apply refuses a changed TTL on one of the engine's own tables as drift rather than expiring rows at boot; an undeclared TTL never removes a live one. Shortening a TTL expires the rows older than the new value.

Changing the default: an admin sets it with PUT /api/v1/system/retention (system:write, which only the admin role holds by default); GET reports the effective value, the console value and the deployment value. The console value is committed to the deploy repo at governance/settings/retention.yaml (default_ttl_days), so it survives the loss of the engine and every change is an audited commit; null clears it. The PUT applies the new value in the same request to every table that follows the default, the engine's own time-series tables and each deployed source's table, with ALTER TABLE ... MODIFY TTL (REMOVE TTL for 0). Every later build, plan, deploy and boot reads the same value.

On a restart the engine brings every deployed source's table to the effective default, but the schema apply refuses a changed default on its own tables, so after changing DFE_ENGINE_CLICKHOUSE_DEFAULT_TTL_DAYS send the PUT with null to apply it there. dfe schema plan and dfe schema apply read the deployment settings, never the deploy repo, so while a console value is set they report the engine's time-series tables as TTL drift, and dfe schema apply --allow-drift rewrites those TTLs to the deployment default.

Comment Axis: Loader Directives

The comment field serves double duty:

Human documentation -- plain text description
Loader directives -- @ prefixed expressions that tell dfe-loader how to populate the field

Directive Types (from dfe-loader DDL Expression Language)

Directive	Purpose	Example
`@source: field`	Copy from source data	`@source: timestamp \| now()`
`@source: first(a/b/c)`	First non-null from list	`@source: first(user_id/uid/id)`
`@generated: expr`	ClickHouse DEFAULT -- loader omits field	`@generated: now64(3)`
`@renamed: field`	Zero-copy field rename	`@renamed: logoriginal`
`@captured: payload`	Raw payload sidecar	`@captured: raw_payload as JSON`
`@computed: expr`	Derived/enriched value	`@computed: geoip(ip).country_code`

Precedence

When the loader resolves field mappings, column comments have highest precedence -- above built-in presets and external remap files. This means the schema definition IS the authoritative field mapping.

Comments in DDL

These directives are emitted as ClickHouse column comments in the generated DDL:

`_timestamp` DateTime64(3,'UTC') CODEC(Delta, ZSTD(1))
    COMMENT '@source: timestamp | now()',
`_raw` Nullable(String) CODEC(ZSTD(3))
    COMMENT '@captured: raw_payload',
`user_name` Nullable(String) CODEC(ZSTD(1))
    COMMENT '@source: first(user_id/uid/id)',

The loader fetches column comments from system.columns and uses them to build its per-table field mapping. No separate mapping config needed -- the schema IS the mapping.

Common Header Profiles

The common header is not one-size-fits-all. Different data types need different system fields. Profiles define which common header fields to inject:

Profile	Fields	Use Case
timeseries (default)	`_timestamp_load`, `_timestamp`, `_timestamp_received`, `_uuid`, `_org_id`, `_source`, `_raw`, `_json`, `_tags`	Full event ingestion (logs, alerts, audit)
minimal	`_timestamp_load`, `_timestamp`, `_uuid`, `_org_id`, `_json`	High-volume structured data (metrics, flow records)
passthrough	`_timestamp_load`, `_uuid`, `_org_id`, `_json`	Transparent bridge -- no timestamp injection

Common Header Names Are Reserved

A common header name belongs to the pipeline, not to the payload. When an incoming record already carries one of these names at the top level, the ingest side renames the payload's value to <name>_original and stamps its own value under the name itself -- the loader routes on _source, and two top-level keys of the same name make ClickHouse reject the whole record rather than dead-letter it. <name>_original is never overwritten: a record arriving with both the name and its _original (a replay, for instance) keeps the _original it came with, and the colliding value is parked under the next free <name>_original_<n> counting from 2.

For a schema author this means two things. A promoted column named after a common header field will not receive the payload's value, and a payload field you want to keep must be given a different name -- or read back from <name>_original, which is a dynamic path in _json unless you promote it explicitly.

Profile Tied to Source

Each Source selects a profile via the header.type field:

# sources/filebeat.yaml
header:
  type: timeseries              # Profile name
  version: 1.0.0                # Common header version (semver)

When a source is created with header.type: timeseries, the schema starts as just the timeseries common header fields. Source-specific data columns are added incrementally.

Profile Versioning

Tables are tagged with profile metadata in the table comment:

COMMENT '@schema_source: core | @schema_version: 2 | @profile: timeseries | @profile_version: 1'

Profile migrations (adding new common header fields) use ALTER TABLE ADD COLUMN -- safe, non-destructive, and automated.

The `all_sources` Merge Table

dfe.all_sources is a ClickHouse Merge table over main and each registered source's MergeTree table, so one Observe source reads every source's rows. Core manifest tables other than main (even under a source's name) and tables without _org_id are never members.

It declares _timestamp_load, _timestamp, _timestamp_received, _org_id, _source, _raw and _json, typed as on main. A member lacking one reads its default: epoch 0 for a timestamp, NULL for a Nullable column. _raw IS NULL misreads a member without _raw, since ClickHouse answers it from the _raw.null subcolumn while optimize_functions_to_subcolumns is on; test length(_raw) or use coalesce. The virtual column _table names each row's member.

The engine rebuilds it after every source deploy or delete. At startup it rebuilds once the schema bootstrap converges or is switched off, never after a failed pass. Each rebuild reads the registry as it stands then; concurrent rebuilds in one engine fold into one. A deleted source leaves the members while its table stays.

Each rebuild applies the tenant row policy (rbac.md) to all_sources itself. Member tables keep their own policies, which also fence a read through it.

The engine registers it on every team as the Observe source all_sources, timed on _timestamp_load because _timestamp reads as epoch 0 for a passthrough member. Registration retries with backoff from 60 s to 15 min until dfe-hyperdx accepts it.

The `dlq` Table

dfe.dlq holds the dead letters every app writes, kept 30 days. The engine registers it on every team as the HyperDX source dlq, timed on _timestamp, alongside all_sources and with the same retry. A source cannot be named dlq, detection or repository, since its table would be the core table of that name.

Sigma Column Mappings

Sigma rules reference fields by standardised names (e.g. SourceIP, CommandLine, EventID). These must map to actual ClickHouse column names.

Approach: ClickHouse View per Sigma Taxonomy

Rather than embedding Sigma field names in the base schema, create ClickHouse views that expose Sigma-compatible column aliases:

-- Auto-generated from Source schema + Sigma field mapping
CREATE VIEW {db}.{source}_sigma AS
SELECT
    source_ip AS SourceIP,
    dest_ip AS DestinationIP,
    user_name AS User,
    process_name AS Image,
    command_line AS CommandLine,
    event_id AS EventID,
    *
FROM {db}.{source}

Benefits:

Base schema stays clean (ClickHouse-native names)
Sigma rules query the view (standard Sigma field names)
Field mapping is per-source (different sources map differently)
Zero storage overhead (view, not materialised table)
Mappings resolved by sigma/source_mapper.py from the source's field map and sigma view

Sigma Mapping as Source Metadata

The Source definition declares its naming-standard views in the generic views list; the sigma entry carries the logsource binding and overrides:

# sources/windows_audit.yaml
views:
  - standard: sigma
    taxonomy: windows                   # Sigma logsource product binding
    custom_mappings:                    # Per-source overrides (win over field_map)
      CommandLine: command_line
      ParentCommandLine: parent_cmd

The engine generates the Sigma view from source schema + the sigma view entry.

Elastic Index Template Converter

The existing Elastic/OpenSearch index template converter generates a source meta schema from a supplied Elastic template JSON. This remains -- but now outputs YAML in the Source schema format:

Elastic Template JSON
  │
  ▼
Template Converter (dfe-engine)
  │  Maps Elastic types → primitives
  │  Maps Elastic analyzers → use_cases
  │  Preserves field hierarchy as flat columns
  │
  ▼
Source schema YAML (new format)
  │  name, type, attribute, use_case, comment
  │
  ▼
Attached to a Source definition

Elastic -> Primitive Type Mapping

Elastic Type	Primitive	Use Case	Notes
`keyword`	`string`	`dimension`	Exact match filtering
`text`	`text`	`word_search`	Searching for whole words
`long`	`integer`		Default Int64 matches
`integer`	`integer`
`short`	`integer`		ch_override: Int16 if needed
`byte`	`integer`		ch_override: Int8 if needed
`double`	`float`
`float`	`float`		ch_override: Float32 if needed
`boolean`	`boolean`
`date`	`datetime`
`ip`	`ip`	`range`
`geo_point`	`geo_point`
`object`	`json`
`nested`	`json`

The converter is invoked when creating a Source from an existing Elastic data source -- it bootstraps the schema so the user doesn't start from scratch.

Source-Scoped Entities

Beyond the schema itself, several other entities are scoped to a Source. See source-definition.md for the full Source definition.

Rules and hunts

A Rule is a detection condition (a SQL WHERE fragment or a CEL filter) on a source's table, so it sees exactly the columns the source's schema defines. A Hunt groups rules, names the customers whose rows they read, and carries the interval schedule. Matches land in <data database>.detection unless the hunt or rule names another table. Current rule and hunt files: source-detections.md.

Sigma Rules (converted)

Sigma rules are converted to DFE Rules by propagation (dfe_engine.sigma.propagation, convert_detection_to_where). The conversion produces a Rule definition with:

SQL query generated from the Sigma detection logic
Source determined by the Sigma logsource -> Source mapping
Sigma view used for field name compatibility

Audience​

Design Principles​

No Schema Registry​

Schema Format: YAML​

Schema Lifecycle​

Four axes​

Schema Definition Format (YAML)​

Column Fields​

Type Axis: Primitives​

Why simplified primitives?​

ClickHouse Type Override​

ClickHouse Type Registry​

ClickHouse Type Overrides Catalogue​

Cardinality Axis​

Attribute Axis​

Attributes Are Tied to Primitives​

Nullability Defaults​

Why nullable is not the default for everything​

Examples​

Use Case Axis​

Use Cases​

Use Cases Are Tied to Primitives​

What the Engine Generates in the DDL​

Comment Axis: Loader Directives​

Directive Types (from dfe-loader DDL Expression Language)​

Precedence​

Comments in DDL​

Common Header Profiles​

Common Header Names Are Reserved​

Profile Tied to Source​

Profile Versioning​

The all_sources Merge Table​

The dlq Table​

Sigma Column Mappings​

Approach: ClickHouse View per Sigma Taxonomy​

Sigma Mapping as Source Metadata​

Elastic Index Template Converter​

Elastic -> Primitive Type Mapping​

Source-Scoped Entities​

Rules and hunts​

Sigma Rules (converted)​