You ask your AI assistant for a query. Maybe it's Claude, maybe it's Cortex, maybe it's whatever Copilot your warehouse shipped this quarter. You describe what you want in plain English, it hands back a clean block of SQL, you run it, and it works on the very first try: no red error text, no stack trace, just a tidy number that slots right into the board deck. That moment, the one where it simply works, is exactly when a lot of us should be a little more nervous than we actually are.
Let me be fair to the robots here, because I use these tools every single day and I'm not interested in pretending they're worse than they are. AI-generated SQL is genuinely good now. It scaffolds joins faster than you can type them, it remembers window-function syntax that I still have to look up, and for the boring 80% of analytics work it's a real productivity unlock. If your worry about AI and SQL has been "the code won't run," you can mostly set that worry down. The code runs.
But that doesn’t mean there’s nothing to worry about. The dangerous query isn’t necessarily the one that breaks, because a broken query is honest with you: it throws an error, you see red, and the problem gets fixed. The query that should keep you up at night is the one that runs perfectly, returns a believable answer, and is quietly, confidently wrong. Call these silent failures, and AI assistants produce them for a very specific reason. They learned from a decade of public code where these exact patterns show up everywhere, and they can't see the one thing that would catch the mistake, which is your actual data.
A quick note on what made this list: every trap below runs without a single error message. I deliberately left off the syntax slips and dialect mix-ups, the ones that turn your screen red, because those tell on themselves. These five don't. Each one hands you a clean result set that happens to be incorrect, which is precisely what earns it a spot.
So here are the five worth committing to memory.
Part 1: NOT IN, Meet NULL
This is the one that catches everybody eventually. You want customers who don't own an account, so you write the most natural thing in the world:
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT owner_id FROM accounts);
If owner_id contains even a single NULL, this returns zero rows. Not an error, not a warning, just an empty set that looks like a real (if disappointing) answer. The reason is that NOT IN evaluates against NULL as "unknown," and one unknown poisons the whole comparison. Your AI assistant reaches for NOT IN because it's the phrasing that dominates every tutorial and forum answer it ever read, and none of those examples warned it about your nullable column. The fix is to use NOT EXISTS instead, which handles NULLs the way you actually meant.
Part 2: The Join That Quietly Multiplies Your Revenue
Here's one that could be expensive. You join orders to line items, then sum the order total, and your revenue number comes back looking healthy, maybe a little too healthy. What happened is that the join fanned out: each order now appears once per line item, so an order with four items got counted four times, and your SUM faithfully added up all the duplicates. This is a grain mismatch, the single most important concept on this list, because grain is just the question "what does one row in this table actually represent," and your AI assistant may not necessarily know the answer.
The tempting bandaid makes it worse. When the duplicates show up, the reflex (and the reflex the AI often codes for you) is to slap SELECT DISTINCT on top and move on. That hides the symptom, burns compute, and still leaves your aggregates wrong, because deduplicating rows doesn't un-tangle a number that was already summed. The real fix is to aggregate each table to a common grain before you join them, so that one order stays one order.
Part 3: The LEFT JOIN That's Secretly an INNER JOIN
You write a LEFT JOIN on purpose, because you want every order whether or not it had a refund. Good instinct. Then you add what feels like a harmless filter:
SELECT o.id, r.status
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
WHERE r.status = 'approved';
And just like that, you've quietly converted your LEFT JOIN back into an INNER JOIN. Every order without a refund has a NULL status, and NULL = 'approved' is false, so the WHERE clause drops exactly the rows you used a LEFT JOIN to protect. The condition belonged in the ON clause, not the WHERE. This shows up constantly in generated code because the model treats "filter the joined table" as a single idea rather than two very different ones, and the query runs perfectly either way, so nothing flags it.
Part 4: The Last Day of the Month That Never Happened
Date ranges look simple, which is what makes them dangerous:
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
If order_date is a timestamp rather than a plain date, this silently drops most of January 31st, because an order placed at 9 a.m. on the 31st is greater than 2026-01-31 00:00:00, and BETWEEN stops right there at midnight. You lose a day of revenue and the query never says a word. The robust pattern is >= '2026-01-01' AND < '2026-02-01', which catches every moment in the window regardless of how precise your timestamps are. Worth mentioning too that the AI will happily assume a time zone here and not tell you which one, so that's a second conversation worth having with it.
Part 5: Divide by Zero's Sneakier Cousin
Last one, and it's the quietest. You compute average order value as revenue divided by order count, and for every customer with at least one order it's perfect. Then you hit a customer with zero orders, and depending on your warehouse you either get an error or, worse, a NULL that flows downstream and silently nukes the average it lands in. A careful engineer guards the denominator:
SELECT revenue / NULLIF(order_count, 0) AS aov
The AI usually doesn't, because the happy-path example it pattern-matched to never had a customer at zero. Real data almost always eventually does.
The Five, Side by Side
None of this means stop using AI for SQL, because we're not going back and we shouldn't want to. It just means the number that deserves a second look isn't always just the one that threw an error: sometimes it's the one that came back clean on the first try.
If an AI hands you an important query this week, run it past these five before it goes anywhere that matters. And if your team is generating a lot of pipeline code lately and you'd like a second set of eyes on it, just shoot us a note.
Thanks for reading!
#DataEngineering #SQL #Analytics #AI #ModernDataStack