Changing a handful of column types in a billion-row table is serious work. Apparently, it's also an excellent opportunity to discover how many unrelated objects you can rebuild manually.

The change, without the theater

The job was to change a few column types in a ClickHouse table holding roughly a billion rows. Airflow DAGs fed the table; Moose managed the declared schema. This wasn't a five-minute migration, but it was also not an invitation to rebuild everything in sight.

The conversions were concrete:

  • DateTime → DateTime64(3). Existing timestamps acquire millisecond precision with .000; a DateTime value does not suddenly become unparseable because its type gains precision.
  • String → UInt8. Empty strings and invalid values need to be handled before conversion. That's a data-cleanup problem, not a reason to improvise the rest of the schema.
  • String → LowCardinality(String). The type change itself is straightforward; normalizing the values, if needed, is a separate concern.

Crucially, the ingestion DAGs could be stopped for a maintenance window. One option was therefore to pause ingestion, clean invalid values in bounded batches or partitions, change the column types in place, wait for mutations to complete, validate, and restart.

On a billion rows, ClickHouse mutations can rewrite large amounts of data. Would that fit the maintenance window? Only a benchmark could answer. But it was a real option to evaluate, not a fantasy to dismiss in favor of a more theatrical plan.

A new table is a strategy. Recreating everything is a hobby.

The chosen route was a replacement table: create it with the desired schema, copy data in batches using INSERT ... SELECT, and switch table names.

That approach can be perfectly reasonable. It allows the new data to be prepared and checked before the cutover, and it can leave the original table available for recovery. There is no universal rule that in-place ALTER is better.

The trouble started at the cutover. ClickHouse materialized views still referenced the old source table. Their bindings needed attention, but instead of rebuilding the necessary views from the current Moose models, the migration manually recreated the views and their aggregate target tables using DDL taken from the existing database.

Not every dependent aggregate table needed to be recreated merely because the source table changed. And when an object is already defined in a declarative model, reconstructing it from a historical SHOW CREATE result is a curious choice.

We had a source of truth. So naturally, we restored the schema from an older copy of the truth.

One schema, two realities

The copied definitions were not entirely aligned with the current models. At least one column type in production diverged from what the declarative schema specified.

That's the hidden cost of manual DDL in a model-managed system. The migration might move the data correctly while quietly creating a second authority for what the schema is supposed to look like.

The next engineer doesn't see the heroic cutover. They see a migration plan containing operations unrelated to their change, and have to determine whether those operations are necessary, harmless, or dangerous. A plan that includes unexplained historical debris stops being a reliable review artifact.

And no, not every recurring operation was caused by the manual reconstruction. A separate Moose type-comparison defect could treat equivalent type wrappers as different. Another repeated operation concerned a materialized view present in ClickHouse but missing from the migration tool's tracked state. Different causes; the same operational noise. Blaming every symptom on one engineer would be convenient, but technically lazy.

Green is a color, not a postcondition

The repeated operations could report success and still appear in later plans. A CREATE IF NOT EXISTS can succeed without creating anything. A type comparison can keep proposing a change even when the database already represents the intended type.

Successful execution is not proof of convergence. After an exceptional migration, a useful test is to generate another plan with no model changes. It should be empty. If it isn't, investigate before teaching the entire team to ignore the noise.

There were two defensible paths here: perform a measured in-place migration during the ingestion pause, or use the replacement-table approach while keeping dependent objects under their declarative owner and validating the final state. Neither path required blindly recreating every nearby object by hand.

The interesting failure wasn't choosing a new table. It was taking an already complex migration and adding manual steps that the tooling could have managed, then treating a green deployment as the end of the story.

The migration succeeded. The need to migrate survived.