06 Native hot path — instructor notes

Timing

Budget 40 minutes. Keep at least 10 minutes for the second Benchmark view.

Talk track

Learners send the supplied optimization objective, then click Optimize. Require EXPLAIN indexes = 1, measured cardinality, one change at a time, and explicit approval before DDL. The accepted change may be a projection, dictionary, or incremental MV; it must address the observed weak query rather than satisfy a feature checklist.

For a dictionary, require unique keys. For an incremental MV, state the fact-table insert trigger, one-time history backfill, and non-reaction to later dimension changes. For a projection, show the optimized query reads it. Correctness precedes re-benchmarking.

Learners then click Benchmark again and compare the selected before/after results under the same logical query and timing conditions. Have them explain the ClickHouse primitive versus the nearest Databricks alternative without claiming all alternatives are literally impossible.

Use the Query 4 screenshots as a deliberate evidence challenge. Addressing l_shipping_events.status is a reasonable column-pruning hypothesis, but the captured ClickHouse time changes from about 494 ms to 542 ms while the Databricks cache policy also changes. Require a same-condition ClickHouse-only A/B with server time, read_rows, and read_bytes; the displayed 6.7x cross-engine ratio is not proof that the target optimization worked.

The later screenshots provide better target-side evidence: Query 2 moves from roughly 321 ms to 162 ms after aggregating the large input before its joins, and Query 6 moves from roughly 318 ms to 207 ms after pruning the nested tuple to status. Both preserve their row counts. Still require repeated medians; the displayed cross-engine ratios are not the acceptance criterion because the Databricks timings changed too.

Common failures

• The agent applies DDL before learner approval.
• A fashionable primitive is added without an observed bottleneck.
• Before and after queries return different results or use different timing conditions.
• A slower ClickHouse target is called an optimization because the Databricks cache changed.
• A dictionary, MV, or projection exists but the hot query does not use it.
• Learners keep a regressing optimization to force the intended narrative.

Reset steps

Use the agent to roll back only the approved workshop object, preserve both Benchmark views, and restate the hypothesis. If time permits, test one simpler change. A no-speedup or rollback outcome can pass when the reasoning and correctness are sound.