The query Claude wrote is genuinely elegant.
It's also a self-join across a table you didn't mention has half a billion rows. It ran fine against your dev sample. Production is a different story.
Nothing went wrong in that exchange. You described the problem accurately. You got a correct answer. The answer was correct for the problem as you stated it, and the problem as you stated it was missing the part that mattered.
🔍 The two problems in every query
Every query you write is solving two problems at once.
The first is logical. What data do you need, how does it relate, what shape should the output take. This is the problem you think about, the one you can describe in a sentence, and the one that naturally goes into a prompt.
The second is physical. How much data, how it's distributed, how fast the answer needs to come back. This is the problem that determines whether your correct query finishes in four seconds or four hours.
Describe only the first one and you get an answer that solves only the first one. Logical correctness is the harder thing to get right in general, so the answer looks complete. It reads as finished work.
Two examples.
A window function over an unpartitioned table. The logic is right: rank records within each customer, take the latest. Against a dev sample it returns instantly. Without a PARTITION BY clause the engine treats the entire table as a single window, and the sort that follows is the most expensive part of window function execution. A query that should take seconds takes twenty minutes and puts pressure on everything else running at the time.
A self-join that's fine at a million rows. Join a table to itself to compare each record against its predecessor. At a million rows, fine. At five hundred million, the intermediate result is enormous and the job either spills to disk or fails. The logic never changed. The physics did.
🧠 Why intermediates specifically
Beginners aren't usually handed production-scale problems. Seniors have been through enough of these that scale questions come first automatically, before the logic is settled.
Intermediates are in the specific position of owning real systems while still thinking correctness-first. That ordering is a strength in most contexts. It's how you build things that work. But it means scale facts get filed as background information rather than as requirements.
The row count feels like a property of your environment. The latency budget feels like something the business decided. Neither feels like part of the technical question, so neither reaches the prompt.
They are part of the technical question. At five hundred million rows they are most of it.
✅ Three numbers, before the question
Lead every performance-relevant request with these:
Row count. Cardinality of the join or partition key. Latency budget.
Instead of:
"How do I get the latest record per customer from this table?"
Send:
"Table has roughly 500M rows, about 2M distinct customers, and this needs to complete in under 2 minutes. How do I get the latest record per customer?"
Same question. Different answer. The first gets you a window function. The second gets you a discussion of whether you should be maintaining a separate current-state table instead.
Why these three:
Row count determines whether an approach is viable at all. Some patterns hold at any scale. Others fall apart past a threshold, and the threshold is the thing worth knowing.
Cardinality determines what your partitioning options are, and the right answer depends on your system. In a warehouse or lakehouse, low cardinality is usually what you want: a date column with a few hundred distinct values gives manageable partitions and effective pruning. Partitioning by customer ID with two million distinct values gives you two million tiny partitions, heavy metadata overhead, and queries slower than no partitioning at all. In distributed key-value stores the guidance inverts, because there the goal is spreading write load evenly rather than enabling pruning. Either way the number is what determines the recommendation, so state it.
Latency budget determines how much you're willing to spend. A query that runs nightly can afford things a query behind a dashboard cannot. Without this number you get an answer optimised for nothing in particular.
Put them before the question, not after. Context that arrives afterwards tends to be treated as clarification rather than as a constraint that reshapes the whole answer.
⚖️ The objection worth taking seriously
The weak version is "I shouldn't have to specify everything, a good engineer would ask." True, and easy to answer: you won't be asked, so state it.
The stronger objection: you often don't know these numbers precisely, and a wrong number can be worse than no number. Guess two million distinct customers when it's actually forty thousand and you get an answer optimised for a distribution you don't have, delivered with the extra confidence that comes from having been given specifics. Precision you don't actually possess is a real risk.
The answer isn't precision. It's order of magnitude, stated as such. "Hundreds of millions of rows, millions of distinct keys, needs to finish in minutes not hours" is enough to change the recommendation correctly, and you almost always know that much. Being off by a factor of two inside the right order of magnitude rarely changes anything. Being off by a factor of a thousand changes everything, and that's the error this catches.
If you genuinely don't know the order of magnitude, that's worth finding out before you optimise anything, independent of AI.
🎯 The takeaway
Three numbers, before the question:
1. Row count. Order of magnitude is enough. Say it's an estimate.
2. Cardinality of the join or partition key. And name your engine, because the right partitioning answer differs between warehouses and key-value stores.
3. Latency budget. Nightly batch and dashboard-backing are different problems.
The logical problem is the one you naturally describe. The physical problem is the one that decides whether the answer works. Only one of them reaches your prompt by default.
This is post #4 in a series on AI practice for data engineers. Next up: **The Debugging Intuition That Never Developed.**
Top comments (0)