ETL Reliability Roadmap
This is the long-term plan for operating SynDB’s ETL and graph-derived data products without replaying imports, losing provenance, or weakening scientific validation. The plan is intentionally split between changes this repository can make and changes that require the cluster administrator or the ClickHouse operator’s GitOps owner.
Current boundary
At the 2026-07-26 audit:
- ETL Jobs are disabled by default and no ETL Job is active in
syndb. - The ten ClickHouse data pods and five API pods are Ready with no restarts.
- The immutable ETL image is pinned by digest in the ETL, backup, ClickHouse-prune, and Meilisearch-reconcile paths.
validate-clickhouse-layoutpasses. Its graph-product gate uses bounded active-part metadata; exact manifest row-count parity remains a separate scientific gate.- The current operator identity can manage the
syndbnamespace but cannot inspect the externalclickhouseoperator namespace or cluster-wide pods. That is an authorization boundary, not a reason to weaken Kubernetes RBAC.
Do not enable flagship imports until the artifact-first readiness check, the lakehouse contract check, and a small real-data smoke import all pass.
Implementation checkpoint — 2026-07-28
The counts below are a dated record. The running service states the current
ones at GET /v1/neurodata/corpus, using the same gate.
The first availability and provenance controls are now deployed and live:
- the authoritative ledger sees 41 registered datasets, with 1 dataset and 2 tables currently available; 40 datasets remain blocked by explicit source, release, manifest, parity, or derived-product gates;
- 38 of those records are active and 3 are explicitly inactive because their upstream source is unavailable. The 100%-available SLO must be 38/38 active records; inactive records stay visible as explicit exceptions and must not be hidden by changing the denominator;
- source-versioned derived-product fields are present in PostgreSQL and ClickHouse, and graph precompute publishes source table-version IDs, content hash, producer run ID, and publication state;
- the ten ClickHouse replicas have the same provenance schema and the durable migration path handles the Replicated database DDL contract;
- the API and ETL releases are deployed; the four ClickHouse backup CronJobs and PostgreSQL statistics sampler are enabled with corrected digest-pinned images, and the restore drill is enabled after its in-cluster manual Job gate;
The ledger and health sweep are therefore operational, but the platform is not
yet a 100%-available state. A successful health-script process exit only means
the sweep completed; its recorded DEGRADED result is authoritative until all
dataset and recovery gates are green.
Verification checkpoint — 2026-07-29
The first end-to-end availability canary is now complete for celegans-pharynx:
- the download and import Jobs completed successfully, with 66 neuron rows and 420 synapse rows;
- both artifact-backed table versions are released, and PostgreSQL artifact, table-version, and serving-state records agree with exact ClickHouse counts and content hashes;
- graph precompute completed successfully, and every applicable derived
product is
currentfor the same source table-version IDs and source hash; - the serving-index contract is green (
synapses, graph edges, graph summary, and analysis-status tables all present); - the availability ledger now reports 2/38 eligible datasets available, 4 available tables, and 2 derived products ready at the dataset level;
- the current ETL producer image is pinned as
docker.io/caniko/syndb-etl@sha256:e92688d8817f51972c5fbe1928d2ba8afee111b8dbd79f8a182cd82df71e1d40. It verifies release visibility after coordinated publication. The API guard is deployed atdocker.io/caniko/syndb-api@sha256:b62c49065566615fa5026a7068c35f5973844ee237ad396d0e8a3af8e48b9003. - the current Central ClickHouse full backup
v2_central_full_202607290534completed with 51,058 objects, 66,003,879,414 backup bytes, 48 provenance rows, and durable manifest and completion markers; the published r11 restore producer passed parity and cleanup against it.
The remaining degraded state is explicit: 66 eligible source tables are still blocked, 20 eligible datasets lack complete/current derived evidence, three records are inactive, the scheduled restore drill still needs its four-week evidence window, and the capacity forecast producer remains fail-closed. Do not convert these counts into availability by marking unverified sources released or by treating a derived product as current when its source identity differs.
During verification, one ClickHouse replica briefly failed closed after its
node became NotReady and lost the rook-system.rbd.csi.ceph.com driver
registration. The pod restarted twice and returned Ready without data-plane
mutation; the availability verifier correctly reported an infrastructure
failure while the replica was unavailable. The cluster-admin recovery item is
to restore node/CSI registration and add an alert/runbook for this condition;
the service operator must not delete the pod or PVC as a first response.
The canary also measured a performance debt: graph precompute scanned roughly 400–570 million graph rows for this small dataset because the current aggregate view path did not prune by dataset tightly enough. The next graph work should add a measured dataset-pruning/index or partition strategy and repeat the same exact-row scientific checks before changing the query plan.
Next execution order
- Finish the recovery evidence gate. The corrected immutable toolbox,
West/Central credentials, five-shard full backup, completion markers, and
current manual restore parity are green; the digest-pinned in-cluster
restore Job passed and the weekly schedule is enabled. Require four
consecutive weekly PASS runs. The distributed
ON CLUSTERcoordinator was observed to hang after its five workers completed, so it is not an acceptable completion path. - Reconcile active hub-direct datasets one at a time. For each source, run
its real
download/validate/prepare/importpath, then gate publication on the artifact-first and lakehouse validators. Never enable the full Job registry to accelerate this queue. - Resolve the three active local-raw-stage datasets (
fish1,dryad-mitochondria, andhemibrain-mitochondria) by staging approved raw inputs through their documented provider/export workflow. FlyWire now uses the credential-free Zenodo 783 release. The existing CLI deliberately refuses the remaining local paths without the explicitSYNDB_ETL_ALLOW_LOCAL_RAW_STAGE=1admission. - Re-run graph precompute only after each dataset’s table versions are released; accept a dataset only when source IDs, content hash, producer run, and current publication state match. Optional products that require annotations remain visible but do not block core readiness.
- Keep the three inactive records as explicit exceptions until a product owner either supplies a new approved source or retires the record with a durable replacement/retirement decision.
The per-dataset acceptance commands are:
syndb ops k8s etl validate-artifact-first-readiness \
--namespace syndb --database syndb --bucket syndb-etl
syndb ops k8s etl validate-lakehouse-contract --database syndb
target/release/syndb ops k8s --namespace syndb etl availability \
--json --cluster syndb_distributed
The first two checks are necessary but not sufficient: the availability ledger is the final source/release/manifest/ClickHouse/derived-product gate.
Target architecture
Four ownership boundaries must remain explicit:
- Cluster control plane. The ClickHouse operator and the ClickHouse Installation (CHI), Keeper, PVCs, and operator RBAC are owned by a cluster-admin/GitOps release.
- Service release. API, UI, workers, observability, and ETL Job templates are reconciled independently of CHI topology. A service or ETL image update must not submit a CHI update.
- Data publication. Hub artifacts and PostgreSQL table-version metadata are the durable input contract. Flight may publish only a complete, verified table version; derived products consume a specific source version.
- Scientific validation. Structural checks, exact manifest parity, endpoint integrity, and derived-product status are separate gates. A fast structural query must never be presented as an exact biological row count.
Execution phases
Phase 1 — isolate deployment ownership
The current umbrella release is named syndb-etl but also renders the
syndb-clickhouse subchart. This couples an ETL image rollout to operator-owned
CHI reconciliation and caused an ETL-only apply to coincide with broad
ClickHouse pod reconciliation.
Move to two releases:
syndb-clickhouse: CHI, Keeper, storage, and ClickHouse maintenance CronJobs; changed only through an explicit topology change workflow.syndb-services: API/UI/workers/observability and the ETL Job templates; ordinary service and ETL rollouts do not render the CHI.
Migration acceptance:
- Render both releases and compare the normalized CHI
specwith the liveClickHouseInstallation; there must be no topology, template, resource, or storage diff during the ownership transfer. - Adopt the existing resources through the cluster-admin/GitOps workflow, never by deleting the CHI, StatefulSets, or PVCs.
- Run an ETL image-only upgrade and prove that the CHI resource version and ClickHouse pod UIDs do not change.
- Keep a separate, reviewed CHI change command for topology, storage, and capacity changes. The ordinary service path should fail closed if a CHI diff is detected.
Until the split is complete, keep syndb-etl.jobsEnabled=false for ordinary
Helm applies. The explicit ETL rollout path may run only the reviewed Job
allow-list; it uses the fixed finalization budget and a namespace-scoped Flight
finalization Lease. It does not mutate the CHI or ClickHouse StatefulSets.
ClickHouse capacity changes remain a separate operator/GitOps change.
Phase 2 — close operator authorization through GitOps
The application operator must not grant itself cluster-admin access. A cluster
administrator must identify the actual operator ServiceAccount in namespace
clickhouse, then apply the least-privilege binding from the cluster’s RBAC
source of truth. The required permissions are the Altinity resources needed to
reconcile the CHI, its templates/configuration, finalizers/status, and Keeper
objects:
- apiGroups: ["clickhouse.altinity.com"]
resources: ["clickhouseinstallations/status"]
verbs: ["get", "patch", "update"]
- apiGroups: ["clickhouse.altinity.com"]
resources: ["clickhouseinstallations/finalizers"]
verbs: ["get", "patch", "update"]
- apiGroups: ["clickhouse.altinity.com"]
resources:
- clickhouseinstallationtemplates
- clickhouseoperatorconfigurations
verbs: ["get", "list", "watch"]
- apiGroups: ["clickhouse-keeper.altinity.com"]
resources: ["clickhousekeeperinstallations"]
verbs: ["get", "list", "watch"]
The admin workflow must discover the real ServiceAccount rather than assuming
the historical name, apply the binding in GitOps, and validate both the
operator namespace and the syndb CHI. The exact checks are:
kubectl -n clickhouse get deploy,sa
kubectl auth can-i --as=system:serviceaccount:clickhouse:<operator-sa> \
get clickhouseinstallations/status -n syndb
kubectl auth can-i --as=system:serviceaccount:clickhouse:<operator-sa> \
update clickhouseinstallations/finalizers -n syndb
kubectl auth can-i --as=system:serviceaccount:clickhouse:<operator-sa> \
list clickhousekeeperinstallations -n syndb
kubectl -n clickhouse logs deploy/<operator-deployment> --since=48h
kubectl -n syndb get chi syndb-cluster -o jsonpath='{.status.status}{"\n"}'
The current user is not authorized to perform these cross-namespace checks; the missing admin artifact is the operator’s GitOps RBAC change, not a missing ETL code path.
Phase 3 — make derived products versioned (implemented; maintain)
The source-versioned provenance contract is now implemented in
precomputed_analysis_status and derived_product. It records source
table-version IDs, source content hash, producer run ID, completion time, and
publication state. A successful graph-precompute run is accepted only when
its evidence remains tied to the currently released source versions.
Keep the producer and schema aligned with:
- source table-version IDs (or a canonical source manifest hash);
- source row counts and source content hashes;
- a producer run ID and completion timestamp;
- an explicit
current,superseded, orfailedpublication state.
The graph-precompute Job receives the source version from the publication workflow, refuses production execution when it is absent, and writes the source identity with every derived artifact status. The validator checks:
published source version
== graph-precompute input version
== derived-product status version
Only after that equality holds may cache population, manuscript figures, or
flagship downstream analyses consume the product. Existing
dataset_artifact_manifest, dataset_table_version, and
dataset_table_state records remain the source of truth; no expected count is
to be synthesized from an empty ClickHouse table.
The remaining work in this phase is maintenance: add regression coverage for each new derived product, alert on stale publication state, and re-run the ledger after every source release and graph-precompute job.
Phase 4 — bound transient storage and startup cost
Artifact-first publication intentionally retains isolated
__syndb_ingest_*/__syndb_rollback_* databases for diagnosis. Their metadata
eventually increases ClickHouse startup time; the audited Pacific pod spent
about 892 seconds loading roughly 7,237 tables.
Implement a lifecycle controller with these rules:
- Record the upload/publication state and source manifest before any cleanup.
- Retain active, pending, and failed databases until an operator decision or the documented forensic retention period.
- Delete only databases whose publication is terminal, whose retention period has expired, and whose source manifest/status record is durable.
- Emit deletion audit events and keep a dry-run report listing every candidate.
- Alert on database/table count and startup duration before they threaten the readiness budget.
Implement cleanup as a Flight service action that holds the existing finalization lease through deletion. Direct CLI cleanup remains unavailable; inspection must preserve abandoned databases until that action exists.
Phase 5 — operational SLOs and recovery drills
Track these signals per rollout and per dataset:
- Job queue age, active-job count, terminal failure class, and retry count;
- ClickHouse pod readiness, restart count, operator reconcile errors, and startup duration;
- active-part bytes/rows, merge backlog, Keeper quorum, and free storage;
- artifact upload completeness, manifest parity, publication latency, and derived-product freshness;
- whether the current release has changed CHI topology or pod UIDs.
Exercise recovery quarterly with a small real dataset: failed download, failed staged finalization, interrupted publication, operator reconcile loss, and rollback. Each drill must end with either a verified published version or an explicit retained failed artifact/database. A retry must never be the default response to an unknown failure.
Exit criteria for flagship ETLs
Flagship ETL Jobs may be enabled only when all of the following are evidenced for the target dataset:
- source artifacts exist in the approved hub bucket and their manifests have schema, content hash, and exact row count;
- table versions are released and ClickHouse counts match those manifests;
- endpoint integrity returns zero missing pre/post neurons;
- graph-derived status is
ok, exactness is appropriate to the product, and its source version matches the released table versions; - the fixed finalization memory/thread/concurrency envelope and scratch budget are admitted for the source size; application rollouts do not change CHI or ClickHouse StatefulSet capacity;
- the operator RBAC checks and service-release/CHI ownership checks pass;
- the smoke import and rollback/retention checks have completed.
If any prerequisite is missing, leave Jobs disabled and report the producer, regeneration workflow, and validation command for the missing artifact.