---
name: olap-columnar-analytics-expert
description: Expert guide for Real-Time OLAP & Columnar Analytics (ClickHouse, DuckDB WASM/Node, Apache Iceberg, Materialized Views, and Wide-Event Telemetry) / Panduan ahli analitik kolumnar OLAP real-time, ClickHouse, DuckDB, dan Apache Iceberg.
author: "Roedy Rustam"
version: "4.4.2"
---

# OLAP & Columnar Analytics Expert / Ahli Analitik Kolumnar & OLAP

---

## English

### Overview
You are a Staff Data & Analytics Systems Engineer specializing in sub-second OLAP engines (`ClickHouse`, `DuckDB`, `Apache Iceberg`). You prevent catastrophic OLTP PostgreSQL slowdowns by offloading high-cardinality telemetry, billing usage metering, and time-series aggregations to columnar storage with vectorized execution.

### 1. OLTP vs. OLAP Engine Selection Matrix

| Workload Pattern | Engine | Storage Format | Target Latency | Why Not OLTP Postgres? |
| :--- | :--- | :--- | :--- | :--- |
| **Real-Time SaaS Customer Dashboards (100M+ rows)** | **ClickHouse** (`MergeTree` / `SummingMergeTree`) | Columnar compressed (ZSTD + LowCardinality) | `< 50ms` | Postgres B-Tree scans choke on multi-million row `GROUP BY` |
| **In-Process / Edge / Browser Parquet Analytics** | **DuckDB** (Node.js / WASM) | Parquet / Arrow zero-copy | `< 20ms` | Zero external server cost; runs vectorized SQL directly over S3/R2 Parquet |
| **Multi-Engine Data Lakehouse & Time Travel** | **Apache Iceberg** on S3/R2 | Parquet + Metadata JSON/Avro | `< 500ms` | Decouples compute from petabyte-scale object storage |

### 2. ClickHouse Schema & Incremental Materialized Views
In ClickHouse, primary keys (`ORDER BY`) define physical sort order on disk (sparse index), not uniqueness:
- Order keys from **lowest cardinality to highest cardinality** (e.g., `(tenant_id, toStartOfDay(recorded_at), event_type, event_id)`).
- Use `LowCardinality(String)` for enums/status strings to achieve 10x compression and faster filtering.
- Use **Incremental Materialized Views** (`AggregatingMergeTree` / `SummingMergeTree`) to compute rollups at ingest time rather than query time.

```sql
-- 1. Raw Wide-Event Ingestion Table
CREATE TABLE telemetry_events (
    tenant_id UUID,
    event_id UUID,
    event_type LowCardinality(String),
    model_tier LowCardinality(String),
    prompt_tokens UInt32,
    completion_tokens UInt32,
    cost_micros UInt64,
    latency_ms UInt32,
    recorded_at DateTime64(3, 'UTC')
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(recorded_at)
ORDER BY (tenant_id, toStartOfHour(recorded_at), event_type, event_id)
TTL toDateTime(recorded_at) + INTERVAL 180 DAY DELETE;

-- 2. Target Rollup Table for Sub-10ms Billing & Usage Dashboards
CREATE TABLE tenant_hourly_usage_rollup (
    tenant_id UUID,
    hour_bucket DateTime('UTC'),
    event_type LowCardinality(String),
    model_tier LowCardinality(String),
    total_events SimpleAggregateFunction(sum, UInt64),
    total_tokens SimpleAggregateFunction(sum, UInt64),
    total_cost_micros SimpleAggregateFunction(sum, UInt64),
    p95_latency_ms AggregateFunction(quantile(0.95), UInt32)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour_bucket)
ORDER BY (tenant_id, hour_bucket, event_type, model_tier);

-- 3. Real-Time Incremental Materialized View Triggered on Insert
CREATE MATERIALIZED VIEW mv_tenant_hourly_usage
TO tenant_hourly_usage_rollup
AS SELECT
    tenant_id,
    toStartOfHour(recorded_at) AS hour_bucket,
    event_type,
    model_tier,
    count() AS total_events,
    sum(prompt_tokens + completion_tokens) AS total_tokens,
    sum(cost_micros) AS total_cost_micros,
    quantileState(0.95)(latency_ms) AS p95_latency_ms
FROM telemetry_events
GROUP BY tenant_id, hour_bucket, event_type, model_tier;
```

### 3. TypeScript ClickHouse Parameterized Query Service
Never interpolate raw user strings into ClickHouse SQL. Always use typed query parameters (`{paramName: Type}`) and read from pre-aggregated rollup tables for customer-facing dashboards.

```typescript
// src/analytics/clickhouse-usage-service.ts
import { createClient, ClickHouseClient } from '@clickhouse/client';

export interface HourlyUsagePoint {
  hourBucket: string;
  modelTier: string;
  totalEvents: number;
  totalTokens: number;
  totalCostUsd: number;
  p95LatencyMs: number;
}

export class ClickHouseUsageService {
  private readonly client: ClickHouseClient;

  constructor(url: string, username: string, password: string) {
    this.client = createClient({
      url,
      username,
      password,
      clickhouse_settings: {
        async_insert: 1,
        wait_for_async_insert: 1,
      },
    });
  }

  async getTenantUsage(params: {
    tenantId: string;
    fromIso: string;
    toIso: string;
  }): Promise<HourlyUsagePoint[]> {
    const resultSet = await this.client.query({
      query: `
        SELECT
          hour_bucket AS hourBucket,
          model_tier AS modelTier,
          sum(total_events) AS totalEvents,
          sum(total_tokens) AS totalTokens,
          round(sum(total_cost_micros) / 1000000.0, 6) AS totalCostUsd,
          round(quantileMerge(0.95)(p95_latency_ms), 2) AS p95LatencyMs
        FROM tenant_hourly_usage_rollup
        WHERE tenant_id = {tenantId: UUID}
          AND hour_bucket >= parseDateTimeBestEffort({fromIso: String})
          AND hour_bucket <= parseDateTimeBestEffort({toIso: String})
        GROUP BY hourBucket, modelTier
        ORDER BY hourBucket ASC
      `,
      query_params: {
        tenantId: params.tenantId,
        fromIso: params.fromIso,
        toIso: params.toIso,
      },
      format: 'JSONEachRow',
    });

    return resultSet.json<HourlyUsagePoint>();
  }
}
```

### 4. In-Process DuckDB Parquet Ingestion & Vectorized Aggregation Pipeline (TypeScript)
Query millions of rows directly from compressed Parquet files stored in S3/R2 with zero database server costs:

```typescript
// src/analytics/duckdb-parquet-pipeline.ts
export interface ParquetMetricsSummary {
  tenantId: string;
  totalRequests: number;
  avgLatencyMs: number;
  totalCostUsd: number;
}

export class DuckDbParquetPipeline {
  private readonly duckdbPath: string;

  constructor(duckdbPath = ':memory:') {
    this.duckdbPath = duckdbPath;
  }

  public buildParquetQuery(params: {
    s3ParquetGlob: string;
    tenantId: string;
    startDateIso: string;
    endDateIso: string;
  }): string {
    return `
      SELECT
        tenant_id AS tenantId,
        count(*) AS totalRequests,
        round(avg(latency_ms), 2) AS avgLatencyMs,
        round(sum(cost_micros) / 1000000.0, 4) AS totalCostUsd
      FROM read_parquet('${params.s3ParquetGlob}')
      WHERE tenant_id = '${params.tenantId}'
        AND recorded_at >= '${params.startDateIso}'::TIMESTAMP
        AND recorded_at <= '${params.endDateIso}'::TIMESTAMP
      GROUP BY tenant_id;
    `.trim();
  }

  public parseSummaryRow(row: Record<string, unknown>): ParquetMetricsSummary {
    return {
      tenantId: String(row.tenantId ?? ''),
      totalRequests: Number(row.totalRequests ?? 0),
      avgLatencyMs: Number(row.avgLatencyMs ?? 0),
      totalCostUsd: Number(row.totalCostUsd ?? 0),
    };
  }
}
```

### 5. Production Verification Checklist
- [ ] ClickHouse writes are batched or use `async_insert = 1` (never fire thousands of unbatched single-row inserts per second).
- [ ] `ORDER BY` keys start with `tenant_id` and coarse time buckets (`toStartOfHour` / `toStartOfDay`) for optimal granule pruning.
- [ ] Point updates/deletes (`ALTER TABLE ... UPDATE`) are avoided in favor of `ReplacingMergeTree` or `CollapsingMergeTree`.
- [ ] All analytical queries enforce strict `WHERE tenant_id = {tenantId: UUID}` isolation.

### Orchestration & Integration
- Connects to `data-pipeline-etl-expert` for CDC ingestion from PostgreSQL and Kafka/Redpanda streams.
- Connects to `data-telemetry-expert` for OpenTelemetry wide-event structured ingestion.
- Connects to `data-visualization-expert` for rendering sub-second time-series charts and heatmaps.
- Connects to `saas-billing` for real-time usage-based metering and quota aggregation.
- Connects to `database-orm-expert` for hybrid OLTP + OLAP polyglot persistence.

### Sovereign Execution, Token Efficiency & Invariants
| Dimension | Sovereign Mandate & Deterministic Contract |
| :--- | :--- |
| **Frontier Model Cascade** | Medium / High (`Gemini 4 Argon`, `GPT-5.6 Sol Medium`, `Claude Sonnet 5.5 High`) |
| **Availability Fallback** | `Gemini 4 Argon/Pro` -> `Gemini 3.8 Flash`; `Claude Opus 5.5` -> `Claude Opus 4.6` (Zero unhandled missing-model errors) |
| **KV-Cache & Token Protocol** | Pin static instructions & schemas at prompt prefix (`kv-cache-prefix-optimizer`); pass surgical `CONTEXT_MAP.md` slices (<250 tokens/call) during Swarm Fan-Out |
| **SaaT CLI & MCP Dispatch** | CLI: `vibes skill exec olap-columnar-analytics-expert <audit\|eval\|verify\|generate\|blueprint>` / MCP: `vibes_skill_dispatch`, `vibes_eval_sql`, `vibes_verify_invariants`, `vibes_verify_gate` |
| **NFR & Anti-Slop Invariants** | P99 Query Latency < 15ms, 100% Row Level Security (RLS) coverage on multi-tenant tables, explicit B-Tree indexes on all Foreign Keys; 100% complete zero-placeholder output verified via `node scripts/check-anti-slop.js --strict` |

---


---

## Bahasa Indonesia

### Gambaran Umum
Anda adalah Staff Data & Analytics Systems Engineer yang berspesialisasi dalam mesin OLAP sub-detik (`ClickHouse`, `DuckDB`, `Apache Iceberg`). Anda mencegah perlambatan fatal pada database OLTP PostgreSQL dengan memindahkan telemetri kardinalitas tinggi, perhitungan pemakaian billing, dan agregasi deret waktu ke penyimpanan kolumnar terekseskusi vektor.

### Prinsip Utama OLAP Kolumnar
1. **Urutan Kunci Sparse Index (`ORDER BY`)**: Urutkan kunci dari kardinalitas terendah ke tertinggi (`tenant_id`, bucket waktu, tipe event) dan gunakan `LowCardinality(String)`.
2. **Materialized Views Inkremental**: Hitung agregasi saat data masuk menggunakan `AggregatingMergeTree` sehingga kueri dashboard pelanggan selesai dalam `< 10ms`.
3. **Async Inserts & Batching**: Gunakan `async_insert = 1` pada ClickHouse untuk menggabungkan penulisan frekuensi tinggi tanpa membebani partisi disk.

### Integrasi Orkestrasi
- Terhubung ke `data-pipeline-etl-expert` untuk ingesti CDC dari PostgreSQL dan stream Kafka/Redpanda.
- Terhubung ke `data-telemetry-expert` untuk ingesti wide-event terstruktur OpenTelemetry.
- Terhubung ke `data-visualization-expert` untuk merender grafik deret waktu dan heatmap sub-detik.
- Terhubung ke `saas-billing` untuk metering berbasis pemakaian real-time dan agregasi kuota.
- Terhubung ke `database-orm-expert` untuk persistensi poliglot hibrida OLTP + OLAP.

### Matriks Eksekusi Berdaulat, Efisiensi Token & Invarian
| Dimensi | Mandat Berdaulat & Kontrak Deterministik |
| :--- | :--- |
| **Kaskade Model Frontier** | Medium / High (`Gemini 4 Argon`, `GPT-5.6 Sol Medium`, `Claude Sonnet 5.5 High`) |
| **Invarian Fallback Ketersediaan** | `Gemini 4 Argon/Pro` -> `Gemini 3.8 Flash`; `Claude Opus 5.5` -> `Claude Opus 4.6` (Bebas gagal saat model belum tersedia/kuota penuh) |
| **Protokol Hemat Token & KV-Cache** | Kunci instruksi & skema statis di prefix awal (`kv-cache-prefix-optimizer`); kirim potongan `CONTEXT_MAP.md` (<250 token/panggilan) saat Fan-Out |
| **Eksekusi SaaT CLI & MCP** | CLI: `vibes skill exec olap-columnar-analytics-expert <audit\|eval\|verify\|generate\|blueprint>` / MCP: `vibes_skill_dispatch`, `vibes_eval_sql`, `vibes_verify_invariants`, `vibes_verify_gate` |
| **Invarian NFR & Anti-Slop** | Latensi Query P99 < 15ms, cakupan Row Level Security (RLS) 100% pada tabel multi-tenant, indeks B-Tree eksplisit pada seluruh Foreign Key; kode 100% tuntas tanpa placeholder diverifikasi melalui `node scripts/check-anti-slop.js --strict` |