Business Intelligence

Traditional Architecture VS Event Sourcing

In a traditional reporting pipeline, an ETL job reads mutable business tables and has to infer what changed. Wow BI instead projects immutable command and state-event Kafka topics into ClickHouse. The event store remains the source of truth; ClickHouse is a rebuildable analytical read model.

That ownership boundary has operational consequences:

• application metadata defines the desired schema and view graph;
• Kafka retention and offsets define what can be replayed;
• ClickHouse object comments and the ownership registry identify objects managed by the current BI deployment;
• BI rows must be reconciled with the event/state source before a cutover is accepted.

Do not write domain state back from BI tables, and do not treat a successful SQL generation as proof that ClickHouse is current.

Generate and Retrieve ETL Scripts

Structured Result API

BiScriptGenerator returns SQL plus an explicit operation and diagnostics:

val options = BiScriptOptions(
    database = "bi_db",
    consumerDatabase = "bi_db_consumer",
    topology = ClickHouseTopology.Standalone,
    consumerGroupNamespace = "orders-blue",
)
val generator = BiScriptGenerator(options)
val preparation = generator.prepare(namedAggregates)
val result: Mono<BiScriptResult> = inspector
    .inspect(options, BiScriptOperation.Deploy, preparation)
    .map { inspection ->
        generator.generate(preparation, BiScriptOperation.Deploy, inspection)
    }

BiScriptResult exposes script, diagnostics, operation, and destructive; statement boundaries are internal so an executor must still run the rendered SQL in order and stop on the first failure. Reset(true) is the only destructive operation. It requires an available deployment inspection and explicit confirmation that a new Kafka consumer generation may replay from earliest.

consumerGroupNamespace is mandatory whenever aggregate consumers are generated. It separates an application's BI ownership scope from another deployment using the same Kafka cluster.

Diagnostics are a stable structured review surface:

Code	Meaning
RAW_JSON_FALLBACK	A value is preserved as scoped raw JSON instead of a typed projection
MAX_DEPTH_REACHED	Expansion stopped at maxExpansionDepth
INSPECTION_UNAVAILABLE	Desired SQL was generated without an authoritative catalog observation
ORPHANED_DATA_TABLE	Managed data is retained because ownership/reconciliation cannot safely remove it
CLUSTER_INTERNAL_REPLICATION_REQUIRED	Cluster-side internal_replication must be configured externally
COMPUTED_OBJECT_DRIFT	A view/materialized-view definition differs and reconciliation is planned

UnsupportedTypeStrategy.RAW_JSON is the default; FAIL rejects unsupported or unverifiable shapes instead of emitting fallback columns.

HTTP Route

When wow.bi.script.enabled=true, WebFlux exposes POST /wow/bi/script. The request body is required; {} means DEPLOY with server-side options. Ask for JSON when diagnostics and the destructive flag are needed:

curl --fail-with-body \
  -X POST http://localhost:8080/wow/bi/script \
  -H 'Content-Type: application/json' \
  -H 'Accept: application/json' \
  -d '{"operation":"DEPLOY"}'

The JSON response is { "script", "destructive", "diagnostics" }; every response also includes Wow-BI-Diagnostic-Count. Accept: application/sql returns only SQL and therefore must not be the approval surface for a destructive operation.

Request fields may lower maxExpansionDepth and override non-null generation options, but the server owns consumerGroupNamespace and offset storage. An authoritative inspector rejects request overrides of database, consumerDatabase, or topology so inspection and generation cannot observe different physical scopes. RESET requires replayFromEarliestConfirmed=true; that field is invalid for DEPLOY.

The default NoOpBiDeploymentInspector returns explicit Unavailable. It supports initial/offline DEPLOY preview with a diagnostic, but cannot clean stale objects, recover a prior consumer identity, or execute RESET. Production reconciliation requires wow.bi.script.inspector.type=CLICKHOUSE and ClickHouse endpoints, or a custom authoritative BiDeploymentInspector. Protect this operational route with the deployment gateway.

Generated SQL Contract

The current renderer owns protocol 3, layout 7. Each managed ClickHouse object carries a wow-bi: JSON comment with deployment/configuration/topology fingerprints, object kind, aggregate owner where applicable, and consumer identity. The __wow_bi_deployment anchor records deployment phase and registry revision. Unknown protocol/layout or an inconsistent registry fails closed; object names alone never establish ownership.

Kafka Offset Lifecycle

KafkaOffsetStorage.BROKER is the default. ClickHouse Kafka engine consumer groups include the deployment's consumer identity. A normal DEPLOY preserves the identity observed in a stable current layout; a confirmed RESET creates or continues a reset generation that starts from earliest.

KafkaOffsetStorage.KEEPER adds kafka_keeper_path, kafka_replica_name, and allow_experimental_kafka_offsets_storage_in_keeper=1. Keeper availability, replication, Kafka retention, and earliest-offset behavior are external prerequisites, not properties the generator can prove.

Deployment Topologies

ClickHouseTopology.Standalone creates ReplacingMergeTree stores directly. Cluster mode creates ReplicatedReplacingMergeTree local stores and Distributed facades, using ON CLUSTER and the configured installation/cluster macros. The operator owns the matching ClickHouse cluster configuration and internal_replication setting.

Changing database, consumer database, consumer-group namespace, or topology changes a durable contract. Inspect and plan it as a deployment/cutover, not a harmless SQL regeneration.

Aggregate Commands

For each aggregate, the command topic feeds a Kafka queue table, a consumer materialized view, a physical command store, and a public ..._command view. Stable columns include message/aggregate identity, owner/space/request fields, aggregate version, create/void flags, command body, and create time. The public view reads the store with FINAL.

Full State Events

The state topic feeds the same queue → materialized view → store pattern. The public ..._state view exposes complete state-event records; ..._state_event expands the event-body array with a one-based event sequence and event metadata. The stored state JSON is retained for authoritative recovery.

Latest State

The ..._state_last_store is populated from the state store and ordered by tenant/aggregate identity so the public ..._state_last view yields the latest version with FINAL. It is a derived latest-state read model, not the source event stream. Reconcile aggregate count, maximum version, deleted state, and representative replay before using it for business cutover.

Root Expansion View

The expansion planner reads the configured Jackson wire shape and creates a root view over state_last. Scalar and verified structural properties become typed columns. Every root row also retains __state and uses the empty RFC 6901 pointer in __path, so typed projection never removes the recovery source.

Child Expansion View

Each verified collection of objects becomes a child view. arrayJoin emits one row per element while preserving parent identity, __state, zero-based __index, and an RFC 6901 __path such as /orders/2/lines/5. Property segments escape ~ as ~0 and / as ~1.

Nullable Types and Raw Values

Nullability is structural: nullable scalar → Nullable(T), nullable element → Array(Nullable(T)), nullable map value → Map(String, Nullable(T)). When typed extraction cannot distinguish missing, explicit null, and a nullable container, the view adds a scoped __raw__<property> convenience column.

Structural Types and Lossless Semantics

Nullability Propagation Rules

• A nullable object ancestor makes its typed descendants nullable.
• Only the nullable ancestor receives the raw companion; descendants do not duplicate it.
• A nullable object element keeps the current raw element in its child view.
• Unannotated Java reference types are potentially nullable; proved Kotlin/Java non-null contracts remain non-null.
• A whole-value fallback is already raw and receives no second raw column.

Authoritative State Recovery

__state is the lexical authority because it projects the stored state string without parsing and reserializing it. Use __path to source-slice the required subtree or token. Root rows use the empty path; child rows carry the complete pointer. __raw__* is convenient for queries but is not lexical-authoritative.

The prefixes/names __raw__, __state, __path, __index, and __cursor__ are reserved by the generator.

Scoped Raw Convenience Values

JSONExtractRaw can distinguish missing (''), explicit null ('null'), and empty array/object ('[]'/'{}') in many query cases. ClickHouse may normalize numeric spelling, so exact recovery still comes from __state plus __path.

Unsupported Types

Object-valued maps, non-String or nullable map keys, unresolved generics, and platform-specific objects cannot be projected safely. RAW_JSON emits a diagnostic and preserves a scoped raw value; FAIL stops generation. Reaching the configured maximum expansion depth follows the same recovery rule with MAX_DEPTH_REACHED.

Opaque Jackson Shapes

Recursive expansion is allowed only when the configured Wow JsonSerializer proves that the declared object shape matches its JSON object. Polymorphic/abstract/sealed objects, @JsonValue, @JsonUnwrapped, @JsonAnyGetter, custom serializers/converters, and otherwise unverifiable shapes remain opaque. They are kept raw or rejected by FAIL.

Lossless Scalar Mappings

Wire value	Representative JVM values	ClickHouse projection
String	String, Char, ordinary enum	String
Integer	integer primitives, Year	exact signed integer / Int32
Boolean	Boolean	Bool
Number	Float, Double	Float32, Float64
UUID string	UUID	UUID
ISO/string time	Java time/date/duration values	String
Arbitrary precision number	BigDecimal	scoped raw plus __state recovery

A configured serializer that changes one of these wire shapes makes the property opaque unless the resolver can prove the new mapping.

See BI Deployment and Recovery before executing generated SQL.