👨🏻 Brother Talk — Phase 10, Off the Record
The query layer — where users feel the platform and where the bill arrives. Let me give you the one mantra that makes you the person who fixes "the query is slow."
Brother, here's a mantra to carry forever, and it'll make you look like a wizard in this phase: the cheapest data to process is the data you never read. Every query optimization, every cost saving, every "make this faster" boils down to making the engine read less. Internalize that and you'll stop reaching for "bigger cluster" and start reaching for "why is this reading so much?" — which is the actual fix 90% of the time.
Let me tell you the thing that reframed query performance for me. On Athena, you pay $5 per terabyte scanned. That's it. Not per query, not per second — per byte read. Which means performance and cost are the same problem: a fast query is a cheap query, because both come from reading less data. The first time a panicked PM showed me a "$50 query" and I realized $50 = 10 terabytes scanned, the whole thing clicked: this query is reading ten terabytes to answer a question that probably touches a few gigabytes. The fix isn't a faster engine. The fix is making it not read the 10 TB. And that fix lives upstream — in the partitioning, the columnar projection, the sorting, the compaction (everything you learned in P08/P09). The query engine is where bad layout decisions finally send you the invoice.
So here's the debugging ritual, and it's almost mechanical once you know it. Query slow or
expensive? Run EXPLAIN and find the scan. How many bytes is it reading? Then ask the
four questions:
- Is it scanning all partitions because the filter isn't on the partition column? → fix the partitioning or the query.
- Is it
SELECT *reading every column when it needs three? → project. - Is it scanning a million tiny files? → compact (P09).
- Is the data unsorted so row-group stats can't skip anything? → sort/Z-order on the filter column (P08).
It's almost always one or more of those, and none of them is "buy a bigger warehouse." When you can walk a room through that ritual calmly while everyone else is debating cluster sizes, you're demonstrating exactly the "diagnose slow Athena/Trino/Spark SQL queries" skill the JD lists.
Now, the stats thing, because it causes the weirdest, most maddening bugs. The cost-based
optimizer makes its biggest decisions — which join, what order — based on statistics about
your tables. And statistics go stale after a big load. So you get the classic ghost story:
"the query was fast yesterday and slow today and nothing changed." Something changed — the
data grew, the stats didn't, and now the optimizer thinks a 500 GB table is 5 MB and tries to
broadcast it to every worker, and everything falls over. ANALYZE TABLE. Refresh the stats.
It's the "did you turn it off and on again" of query optimization, and it fixes a shocking
fraction of "mysteriously slow" queries. Add it to your reflexes.
A subtle one that separates senior from principal: don't fork your business logic into every data mart. It's tempting, when dashboards are slow, to build pre-aggregated marts everywhere — and you should, for performance. But if each mart re-implements "what counts as an active user" slightly differently, you've created a future where three dashboards show three numbers and nobody knows which is right, and lineage is a swamp. The discipline is: pre-aggregate for speed, but keep one canonical definition of each metric (one source of truth), and derive the marts from it. Performance and a single version of the truth. The teams that skip the second half end up in "why don't these two reports match" hell, which is a credibility-destroying place to be.
On the Athena-vs-Trino question: don't overthink it early. Athena is gloriously low-effort — serverless, no cluster, pay only when you query — and for ad-hoc and intermittent analytics it's almost always the right starting point. You move to a dedicated Trino cluster when the usage becomes steady and high-concurrency (lots of dashboards all day), where an always-on, tuned, resource-grouped cluster gives predictable latency and can be cheaper than per-scan billing, and where you need to federate across sources. It's a crossover decision you make with numbers (concurrency, daily scan TB × $5, latency SLOs), not a religious one. The junior move is picking based on what's trendy; the principal move is the crossover math in an ADR.
Career note: being the person who can make queries fast and cheap is visible value — the cost shows up on a dashboard the finance team watches, and the latency shows up in every analyst's day. "I cut our Athena bill 60% and made the exec dashboard load in 2 seconds instead of 40" is a sentence that travels. And the beautiful part is that the skill is mostly understanding layout (P08/P09) and reading EXPLAIN — not arcane engine internals. You already have the hard parts from the previous phases; this one teaches you to cash them in at the query layer.
Build the optimizer. Watch the savings ratio hit 100× when you project and prune. Then come to P11, where we leave the analytics world entirely for the serving world — single-digit- millisecond lookups from Cassandra and DynamoDB, a completely different beast with completely different rules.
— your brother 👨🏻