Skip to content

At-rest ingestion

Governance tag: overwatch · Min version: overwatch-ingest 0.0.1 · Anchor: #ingestion

The premium tier turns a tenant's archived Parquet — the flush output of their own agent streams — into a queryable ClickHouse system-of-record. Each file is enriched with path-derived lineage, fingerprinted with a content hash, and landed exactly once. It is SDK-free and arrow-free: ClickHouse reads the Parquet natively.

Per-file pipeline

flowchart LR
  P[Parquet file] --> H[sha256 hash]
  H --> D{Seen before?}
  D -- yes --> SK[Skip]
  D -- no --> S[Stage]
  S --> E[Enrich + re-aggregate]
  E --> A[Archive]
  A --> L[Lineage row]
Each Parquet file is hashed, checked against prior lineage, then staged, enriched into the target table, archived, and recorded with one lineage row.
Step What happens
Hash The file's bytes are sha256-hashed — the dedup key and provenance fingerprint
Idempotency check An existing status = ok lineage row for the hash skips the file
Stage The raw Parquet loads into a per-datatype staging table
Enrich The staging rows project into the target table with lineage columns attached
Archive The file moves to an immutable cold archive, preserving its subpath
Lineage One row per file records the hash, counts, timestamps, and status

Path-derived lineage

market_type and binding_id are not in the Parquet — only the landing path carries them. The convention (one market_type per output directory):

<root>/<binding_id>/<exchange>/<market_type>/<datatype_dir>/<file>.parquet

The tail parses right-to-left: file ← datatype directory ← market_type ← exchange ← binding_id. A file outside a recognised datatype directory is skipped.

Directory Datatype Target table
orderbooks/ orderbook orderbook
trades/ trades trades
liquidations/ liquidations liquidations
fundings/ funding funding
open_interests/ open_interest open_interest

The binding_id in the path is the deploy that owns the stream — it is written onto every enriched row, so the at-rest store stays scoped to the owning tenant.

Enrichment

The staging-to-target projection attaches the path-derived exchange, market_type, binding_id, the source_file name, and the ingested_at version. Orderbook enrichment additionally re-aggregates: per-level rows (one row per price level) collapse into one array-per-snapshot row keyed on (symbol, ts_ms), sorted so index 1 is the best bid/ask, with best_bid, best_ask, n_bids, and n_asks derived in the same pass.

Exactly-once

Layer Mechanism
File level A sha256 already in the lineage log with status = ok skips the file — a re-run never re-processes it
Row level Target tables are ReplacingMergeTree(ingested_at), so any re-run collapses duplicate rows

Both layers together make ingestion safe to re-run and safe to backfill over the archive.

Lineage record

Field Type Meaning
sha256 string Content hash — dedup key and provenance
source_file string File name at ingest time
table_name string Target table the rows landed in
exchange · market_type · datatype string Path-derived stream identity
rows_ingested u64 Rows landed from this file
min_ts_ms · max_ts_ms u64 Time span of the file's rows
bytes u64 File size
status string ok · error · skipped_dup
ingested_at u64 Unix ms; latest version wins

Modes

Mode Behavior
Tail Poll the landing tree and ingest new files as they settle
Backfill Ingest an existing archive directory in one pass

Tail skips files younger than a minimum age so a partially written flush is never read mid-write.

Query at rest

Once landed, a tenant's streams are queryable ClickHouse tables — join on binding_id, source_file, or ingested_at to trace any row back to the exact Parquet file it came from.