06 Build the native hot path

Outcome

The agent has explained and tested a ClickHouse-native optimization, proved the logical result stayed stable, and either accepted or rolled it back based on a same-condition target benchmark—not an apparent cross-engine ratio.

Give Step 6 the right objective

Before clicking Optimize, send this context in chat:

For Step 6, optimize the weakest ClickHouse query from the Benchmark view. Use
EXPLAIN indexes = 1 and measured cardinality. Consider ClickHouse-native options such as
a projection, a dictionary for the small nation lookup, or an incremental materialized
view for the daily revenue rollup. Propose one change at a time and wait for my approval
before executing DDL. Use clickhousectl only and preserve the query's logical result.
Use EXPLAIN ESTIMATE before expensive reads; cap exploratory results and set
max_execution_time=30 and max_rows_to_read=100000000.

Now click Optimize. For every proposed change, require the agent to show:

1. the observed scan or lookup problem;
2. why the ClickHouse primitive matches that problem;
3. creation/backfill and freshness semantics;
4. the correctness query that must remain unchanged; and
5. operational cost and rollback.

Approve only the change you can defend. Per query-join-consider-alternatives, a dictionary fits a small, repeatedly queried, slowly changing lookup—and its key must be unique because duplicate keys are silently deduplicated. Per query-mv-incremental, an incremental materialized view fits a repeated aggregation over inserted fact blocks; identify the fact table that triggers it and explain that existing history needs an explicit backfill and later dimension changes do not replay old facts. If a projection is chosen, show through EXPLAIN that the optimized query actually uses it.

Example — prune a nested tuple to one subcolumn

In the captured run, Query 4 groups six million lineitem rows by shipping-event status. The initial ClickHouse rewrite expands the complete Array(Tuple(...)), even though the query does not use each event's location or timestamp. MigrationRoom proposes reading only the named status subcolumn.

The query-only candidate changes the expansion to:

ARRAY JOIN l_shipping_events.status AS status

This is a plausible optimization because ClickHouse's columnar storage can avoid reading fields the query does not reference. It also depends on preserving the source structure as the native Array(Tuple(...)) type rather than flattening the whole value into a string, consistent with schema-types-native-types.

No DDL or backfill is required for this candidate, but it still needs semantic and performance proof. Confirm the same grouping keys, the same 98 result rows, and the same counts before comparing target execution time and bytes read.

Show the ClickHouse-native difference

In your conversation record, contrast the accepted primitive with the nearest Databricks approach:

Databricks can solve related problems with broadcast joins, materialized views, clustering, or pipelines, but they are not the same primitives or operational trigger model. State the trade-off instead of claiming every alternative is literally impossible.

Benchmark again

After the correctness check passes, click Benchmark again. Select the updated result in the dashboard and compare it with the Step 5 baseline using the same query semantics, services, timing type, and cache state.

The screenshot is an optimization hypothesis, not proof of a win. Step 5 recorded about 494 ms for the ClickHouse target, while this run reports 542 ms. The apparent 6.7x ratio comes from the Databricks side moving to 3.61 seconds after its result cache was bypassed; it does not demonstrate that the ClickHouse rewrite became faster.

Copy this challenge into chat before accepting the change:

The new ratio is cache-confounded: the Databricks timing changed materially and the
ClickHouse target moved from about 494 ms to 542 ms. Run the original and rewritten
ClickHouse queries under the same service, timing type, cache policy, and repetition
count. Compare median server time, read_rows, and read_bytes; verify identical results.
Keep the rewrite only if the target-side A/B demonstrates a repeatable improvement.

Further target-side candidates

The captured run also tests two candidates whose ClickHouse timings improve relative to the Step 5 target baseline:

• Query 2 — aggregate before joining. Reduce the 1.5 million-row orders input to the required customer-level state before joining the small dimensions. The target changes from about 321 ms to 162 ms with the same 50 result rows. This follows query-join-filter-before, which explicitly recommends filtering or aggregating a large input before the join.
• Query 6 — filter the named array subcolumn. Apply arrayFilter directly to l_shipping_events.status instead of materializing every field of each event tuple. The target changes from about 318 ms to 207 ms with the same seven result rows. This relies on the native Array(Tuple(...)) representation and column pruning.

The follow-up target-side A/B adds the evidence needed to explain those changes. It reports original and rewritten medians for both queries and shows that Query 6 reduces scanned data from about 479 MB to 239 MB. Query 2 scans the same 59.3 MB in both forms, so its lower median is attributed to aggregating before the join rather than reading less data.

Treat the dashboard deltas as cross-engine context, not optimization proof. The follow-up A/B supplies target medians and read-volume evidence, but you must still record its repetition count, service state, timing type, and cache conditions. The Databricks source times changed, so do not use the displayed 9.8x and 4.7x ratios as proof of the ClickHouse optimization. Keep a change only when the controlled target comparison improves without changing the result.

Ask the agent to explain any improvement—or regression—in terms of parts/granules scanned, join removal, or pre-aggregated rows. A valid outcome may show no speedup; in that case, retain the result and roll back the added complexity.

• Step 6 began from an observed weak query and EXPLAIN evidence.
• The agent waited for human approval before DDL.
• Correctness passed after the change.
• The optimization was judged by a same-condition ClickHouse A/B, not a changed source cache.
• Target-side improvements were repeated; cross-engine ratios were not used as the optimization proof.
• The second Benchmark used the same logical query and declared conditions.
• The conversation explains what the ClickHouse primitive replaces and costs.

Continue to 07 Defend and tear down.