03 Migrate, validate, and rewrite
Drive the copy, parity gate, and SQL rewrites from the MigrationRoom UI.
Outcome
The dashboard shows a completed native copy, passing per-table validation, and reviewed ClickHouse rewrites for the analytical workload.
Step 2 — Migrate Data
MigrationRoom supports two data paths. Use the first tab for this workshop; the second shows how the same step can scale through object-storage staging.
Use this path in the workshop. Click Migrate Data. MigrationRoom builds and
dispatches a migrationkit job that reads Databricks in batches and writes directly to
ClickHouse Cloud through the built-in migration runner.
This path needs no staging bucket, cloud-storage credentials, Unity Catalog external location, or additional Terraform resources. It still provides batching, per-batch checkpoints, pause/resume/cancel, and live progress in the KPI dashboard. Direct migration works at any table size, although S3 staging can be faster for large tables.
The agent may show generated Python in its tool activity. That is MigrationRoom's internal execution plan; do not copy it into a terminal or start a second migration.
Optional architecture—not part of this workshop. For larger migrations,
MigrationRoom can ask Databricks to unload a table as Parquet to S3, then load those
files into ClickHouse Cloud with migrationkit. When S3 is configured, the agent can
select m.add_table_via_s3(...) for tables over 1 million rows.
This path requires additional components:
- an S3 bucket, lifecycle policy, and access permissions;
- a Unity Catalog storage credential and external location over the bucket with
WRITE FILESgranted; - S3 credentials exposed to the migration runner through
STAGING_S3_BUCKET,STAGING_S3_REGION,STAGING_S3_ACCESS_KEY_ID, andSTAGING_S3_SECRET_ACCESS_KEY; and - network access from the Databricks serverless SQL warehouse to the external location.
S3 staging does not support per-row transform logic or direct-path batch_size
settings, so tables that need transformations must still use the built-in direct path.
If S3 is absent or unavailable, MigrationRoom falls back to direct migration instead of
failing the run.
For this workshop, leave S3 staging unconfigured. The built-in runner is easier to operate and keeps the exercise focused by avoiding extra AWS and Unity Catalog provisioning.
When the agent says the migration is running, use the dashboard as the authoritative status view:
- Open the active run in the KPI dashboard.
- In the Migration view, watch each table move from queued/running to done.
- Record the run ID and confirm that each table uses the direct path for this workshop.
- Do not repeatedly prompt the agent while chat is quiet—the background runner reports progress in the dashboard.
Wait until every required table is done. A failed or partial table blocks validation. The completed run should show 8/8 tables, 100% overall progress, and the banner that unlocks Step 3.
Step 3 — Validate
Click Validate only after the migration completes. The agent reuses the migration run ID and writes source/target counts into the dashboard's Validation view.
A validator execution error is not a successful parity check—even when the agent can retrieve the target counts through another query. In this example, the target counting query fails with a driver syntax error and all eight tables are marked errored.
Do not continue to Step 4 or rerun the data copy for a validator-only failure. Copy this request into chat so the agent repairs the counting query and reruns validation:
Fix the validation script error and rerun validation. Do not proceed until the
dashboard reports 8 matched, 0 mismatched, and 0 errored.The repaired run must leave the durable result in the Validation view—not only in a chat response.
Every required row must pass. If a count genuinely differs, stop and ask the agent to diagnose the source snapshot, target schema, transform, and failed batch. Repair that data issue and re-fire Migrate Data, then Validate. Never insert or delete target rows by hand merely to make the screen green.
Step 4 — Rewrite Queries
After validation passes, click Rewrite Queries. Review the original Databricks SQL, the ClickHouse rewrite, the reason for each dialect change, and the returned row count.
Pay particular attention to:
QUALIFYbecoming a subquery andWHERE;LATERAL VIEW explodebecomingARRAY JOIN;VARIANTpaths becoming JSON access or typed columns;aggregate/filterbecoming ClickHouse array functions;- divide-by-zero and missing-map-key semantics; and
- three-level Unity names becoming ClickHouse database/table names.
The materialized-view query is an important semantic checkpoint. The Databricks query
reads the source view directly; the ClickHouse rewrite reads the
AggregatingMergeTree target and finalizes stored aggregate states with functions such
as countMerge and sumMerge.
Ask the agent to correct any semantic difference rather than merely producing runnable SQL. When the set is accepted, open Edit · OLAP, replace the source pack with the approved ClickHouse rewrites, and save. Step 5 benchmarks these exact statements.
UI checkpoint
Keep the conversation link plus screenshots of the completed Migration and Validation views. Your checkpoint must show the selected run ID, all required tables completed, passing parity, and the final rewritten query set—never credentials or signed URLs.
- One authoritative migration run completed in the dashboard.
- Every required source/target count passed in Validation.
- Rewrites preserve result shape and semantics, not just syntax.
- The accepted ClickHouse queries were saved through Edit · OLAP.
- The conversation link and redacted UI checkpoints were retained.
Continue after the break to 04 Benchmark the baseline.