Spike: Validate cross-cluster ClickHouse migration for legacy experiment events #36784

Note: This template is intended for Engineering team use.

Research Question

Can ClickHouse's remote() table function reliably migrate legacy experiment events from the Jitsu-based ClickHouse cluster (clickhouse_test_db.events) to the new CAEM analytics cluster (analytics.events) — with correct column renames and full session pipeline processing — such that experiment goal metrics become queryable via the CAEM endpoints?

Timebox

8h

Acceptance Criteria

• Network connectivity from the new CAEM ClickHouse cluster to the old Jitsu cluster on port 9000 is confirmed; if unreachable, the blocker is documented and the Parquet export/import fallback is tested instead
• At least one customer_id with known experiment events (experiment != '') is successfully inserted into analytics.events on the new cluster using the migration SQL from docs/experiment-data-migration.md
• All column renames are verified in the migrated rows: tenant, project, experiment_id, running_id, variant, session_id, and all dom_element_* columns contain the expected values
• After running SYSTEM REFRESH VIEW analytics.session_facts_rmv and SYSTEM REFRESH VIEW analytics.session_facts_latest_rmv, at least one session for the migrated experiment_id appears in session_facts_latest FINAL with the correct experiment_id, running_id, and variant
• Any issues found (type mismatches, missing fields, pipeline errors) are documented with proposed fixes
• The spike concludes with a go/no-go recommendation on the remote() approach; if go, includes an estimated migration time once the full dataset size is established (size discovery is part of the spike)

Context

The old experiment infrastructure stores analytics events in clickhouse_test_db.events on a Jitsu-based ClickHouse cluster. The new CAEM analytics pipeline uses analytics.events on a separate ClickHouse instance. Before building the full migration, we need to validate the proposed approach end-to-end on a small sample.

The migration uses ClickHouse's built-in remote() table function — no intermediate files needed:

-- Runs on the new CAEM cluster — pulls from the old one
INSERT INTO analytics.events ( ... )
SELECT customer_id AS tenant, cluster_id AS project, experiment AS experiment_id, ...
FROM remote('old-infra-host:9000', clickhouse_test_db, events, 'user', 'password')
WHERE customer_id = 'tenant-abc' AND experiment != '';

After insertion, the session pipeline (session_states_mv, session_facts_rmv, session_facts_latest_rmv) must process the migrated events so experiment goal metrics become queryable. The main prerequisite is network connectivity between the two clusters on port 9000. If that is blocked, the fallback is a Parquet export/import path.

Links

• Full column mapping and migration steps: docs/experiment-data-migration.md