Beyond Notebooks: Text-to-SQL Agent Failure Modes & Reflection Loops

⚙️ Beyond Notebooks — sharpening engineering thinking in the age of AI
No big claims. Just small learnings
Post-2

I’ve been studying failure modes of a simple text-to-SQL agent built to answer one question: “Which product color has the highest total sales?” The schema included qty_delta, which represents inventory state change (negative for decrease, positive for increase), not a direct sales metric.

The failures were revealing.

Failure mode 1: qty_delta was treated as quantity sold without considering sign, producing negative “sales.”
Failure mode 2: another model filtered qty_delta > 0, returning zero rows because no sales were positive.
Failure mode 3: one model returned only the top color, omitting the sales metric entirely and removing any way to validate the ranking.

These weren’t syntax problems. They were contract problems — incomplete schema semantics, reasoning shortcuts, and loss of analytical traceability.

That raised a deeper engineering question: when systems fail like this, do we keep adding prompt rules, upgrade to a smarter model, or introduce structural components?

Prompt rules become brittle. Stronger models improve reasoning but can mask architectural fragility. A more durable design adds minimal, model-agnostic structure: enforce output contracts (return both label and metric), execute SQL and apply lightweight validators, and trigger reflection only when inconsistencies appear.

💡 Reflection, in this sense, isn’t about making models smarter. It’s about making systems observable. It shifts reliability from “trust the reasoning” to “verify the outcome.”

The analogy that helped me: in software engineering, we test on the oldest hardware or weakest devices to expose structural flaws early. Similarly, starting with a cheaper model can surface architectural weaknesses; once the system is hardened with contracts and validation, stronger models become an optimization layer rather than a crutch.

Rules prevent known mistakes. Contracts and reflection detect unknown ones. In evolving data systems — and evolving model ecosystems — that distinction matters.

If you’re interested in thinking more deeply about agent design and reflection loops, the Agentic AI course by Andrew Ng and the DeepLearning.AI team is a solid resource:
https://lnkd.in/enWtEH6J
!

#BeyondNotebooks #AIEngineering #Agents #SystemDesign #ContextEngineering #TextToSQL #AgenticAI #SmallLearning