Both vendors publish enough detail to reason about properly, and both publish performance numbers you should not repeat. This chapter separates the mechanisms — which transfer — from the multipliers, which do not.

Photon is a set of physical operators written in C++, running in the same process as the Java Virtual Machine (JVM) engine. Two design decisions are documented and both are more interesting than the benchmark numbers.

• Vectorised interpretation rather than code generation. The authors concede code generation is better for some cases — complex conditional expressions in particular — and chose vectorisation anyway, for two stated reasons. It enables runtime adaptivity: the engine discovers characteristics of each micro-batch and takes specialised code paths accordingly. And it was easier to build, profile, debug and operate, because preserving operator boundaries lets you collect rich per-operator metrics.
• C++ rather than the JVM, for three stated reasons: hitting a performance ceiling; JIT bailouts creating performance cliffs, where exceeding a method-size limit silently deoptimises a hot path; and native performance being easier to explain, with explicit control over memory and vector instructions.

The adaptivity is the part worth teaching. Every kernel specialises on at least two booleans — does this batch contain nulls? and does it contain inactive rows? — as compile-time template parameters, so the branch disappears entirely. Further specialisations include mostly-ASCII strings versus general Unicode, and compacting a sparse batch before a large hash probe, because dense batches parallelise memory loads while sparse ones stall on latency without saturating bandwidth.

The hash join is three vectorised steps — hash, probe to load entry pointers, then column-by-column key comparison producing a list of non-matches — with quadratic probing. The stated source of the speedup is masking cache-miss latency by parallelising loads, and they deliberately chose that over manual software prefetching because prefetch tuning is hardware-specific and does not transfer.

Snowflake stores data in micro-partitions of 50 to 500 MB uncompressed, created automatically in insertion order. Each carries metadata: the value range per column, the number of distinct values per column, and other optimisation properties. Pruning happens in two stages — eliminate micro-partitions on metadata, then eliminate columns within the survivors.

The insertion-order detail is the one that matters. Your physical layout is a consequence of the order you loaded data in, which is why a table loaded by daily batch naturally prunes well on date and badly on everything else, and why clustering exists.

• Clustering depth is the average overlap depth across micro-partitions for the given columns. Lower is better; the minimum is 1.
• The documentation is unusually candid: query performance is the best indicator of how well-clustered a table is — depth is a proxy, not the target.
• Guidance worth memorising: clustering is worth it when the table has a large number of micro-partitions, typically multiple terabytes; use a maximum of three or four columns; and order them from lowest cardinality to highest.
• Very high cardinality columns are poor keys directly — a nanosecond timestamp is the documented example. Reduce the cardinality with an expression instead, such as truncating a timestamp to a date.

Adaptive execution re-plans at the end of a shuffle or broadcast exchange, using actual statistics rather than estimates. Four capabilities: switching a sort-merge join to a broadcast join, coalescing shuffle partitions, splitting skewed partitions, and detecting empty relations.

The limits are what you need in an interview:

• It does not reorder joins. Join order is decided before execution and stays decided.
• It cannot broadcast the left side of a left outer join, or either side of a full outer join.
• Skew detection requires a partition to be both larger than a size threshold and more than five times the median. Both conditions. Which is exactly why moderate skew on a modestly-sized shuffle is silently not handled, and why you can have a visibly skewed job that adaptive execution ignores.

The vocabulary differs and the diagnosis does not. Every slow query is one of three things:

Two platform-specific gotchas worth carrying. On Snowflake, a small nonzero remote spill can be an artefact of the query acceleration service rather than a problem, so do not chase it. And the Most Expensive Nodes panel lists every node taking at least one percent of execution time, which is where to start rather than at the top of the DAG.