Say this first: Migrating is a full rewrite plus a reader cutover because there is no in-place conversion, so it only pays when the partition scheme is already costing you.
Remember these
- No in-place conversion: migration is CTAS or clone plus rewrite
- CLUSTER BY and PARTITIONED BY cannot coexist on one table
- Migrate for measured pain: ZORDER cost, tiny files, drifted predicates
- replaceWhere writers and path-reading tools break on the cutover
Why: A table is either partitioned or clustered — the two cannot be combined, and you cannot turn an existing partitioned table into a clustered one with an ALTER. The migration is a CREATE TABLE ... CLUSTER BY ... AS SELECT, or a clone followed by a rewrite, then repointing every reader and writer. So the argument has to come from pain you can measure: a second filter column forcing a nightly ZORDER whose cost scales with table size, a partition column with the wrong cardinality producing tiny files or enormous skewed partitions, or a query mix that has drifted away from event_date and cannot be fixed without recreating the table. If the dominant predicate is genuinely event_date and DESCRIBE DETAIL shows healthy file sizes, there is nothing to buy. Also count what breaks: writers using replaceWhere on partition predicates, external tools that read partition paths directly, and anything that assumes the directory layout. The real purchase is optionality — after migrating, the next layout change is an ALTER instead of another migration.
CREATE TABLE ops.silver.orders_clustered
CLUSTER BY (country, event_date)
AS SELECT * FROM ops.silver.orders;
-- cut readers over, then swap names
Senior depth: I would not migrate a healthy partitioned table on principle — the rewrite and the cutover are real risk for no measured gain. What I would do is make clustering the default for new tables and keep a short list of migration candidates ranked by what the current layout costs: nightly ZORDER DBUs, average file size from DESCRIBE DETAIL, and bytes scanned per query class. For the ones that qualify, I would run the CTAS into a new name, dual-write or backfill the gap, verify pruning and row counts on the new table, then swap names in one step so the rollback is a swap back.
Common mistake: Adding CLUSTER BY to the existing DDL in the repository and assuming the next deploy converts the table.
Follow-up they will ask: How would you cut readers over on a table that is written to every hour?