Optimizing Text-to-SQL Pipeline Using Agent Experiments

idinsight · Tech · 2024 · source Agentic: yes · promotional bias: low · source-backed · extracted with prompt 0.5.6 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Text-to-SQL analytics for social-impact decision makers

IDInsight needed Ask-a-Metric to answer natural-language SQL questions over WhatsApp accurately enough for non-technical users, while keeping latency, cost, safety, and pipeline maintainability within production limits.

Constraints:

• The system had to understand user questions, database structure, analysis requirements, and safety constraints.
• The initial simple pipeline was fast to build but not accurate enough for production.
• Prompt changes had cascading effects across different question sets.
• A full CrewAI agentic runtime achieved high accuracy but exceeded cost and latency targets.
• The production target was under 30 seconds and under USD 0.03 per query.

Architecture

Ask-a-Metric moved from a tightly coupled functional pipeline to a pseudo-agent architecture. IDInsight used CrewAI agents as an experimental search process to discover useful tool order, prompt wording, and task decomposition, then hardcoded the recurring agent behavior into a deterministic object-oriented pipeline. The deployed pipeline breaks text-to-SQL into smaller LLM/tool calls, shares only the required context at each step, uses fixed-scope database tools and guardrails, and returns answers through the WhatsApp-facing product.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  WhatsApp_user_question["WhatsApp user question"] --> Query_processing["Query processing"]
  Query_processing --> Guardrails["Guardrails"]
  Guardrails --> Query_processing
  Query_processing --> Fixed_scope_database_tools["Fixed-scope database tools"]
  Fixed_scope_database_tools --> SQL_database["SQL database"]
  SQL_database --> Fixed_scope_database_tools
  Query_processing --> LLM_prompts["LLM prompts"]
  LLM_prompts --> Answer_response["Answer response"]
  CrewAI_experiment_harness["CrewAI experiment harness"] --> Query_processing

Components

Data flow

A WhatsApp question is processed by an object-oriented pseudo-agent pipeline. The pipeline decomposes the request into steps such as selecting relevant tables, finding columns, extracting sample values, running scoped SQL/database tools, and synthesizing an answer, rather than passing the whole task to a free-form agent at runtime.

Why this architecture

The team kept the useful reasoning pattern discovered by agents but removed the production cost of autonomous planning and reflection. A deterministic pseudo-agent pipeline gave them the agentic flow's accuracy while restoring latency, cost control, modularity, and maintainability.

Models used: Large Language Model · CrewAI agents

Scale

• The full CrewAI agentic version took more than one minute per query.
  “answers took more than a minute to be generated, and the cost per query was approximately USD 0.3.”article, stated
• The target production latency was below 30 seconds.
  “we needed the query response time to be less than 30 seconds (two to three times faster) and the cost per query to be less than USD 0.03”article, stated
• The target production cost was below USD 0.03 per query.
  “we needed the query response time to be less than 30 seconds (two to three times faster) and the cost per query to be less than USD 0.03”article, stated
• The pseudo-agent pipeline averages under 15 seconds per response.
  “The pseudo-agent flow, on average, has a response time of less than 15 seconds and costs less than USD 0.02 per query.”article, stated
• The pseudo-agent pipeline costs under USD 0.02 per query.
  “The pseudo-agent flow, on average, has a response time of less than 15 seconds and costs less than USD 0.02 per query.”article, stated

Alternatives considered (roads not taken)

Keep the initial simple pipeline

Rejected because: It produced inaccurate responses, was tightly coupled, and made prompt experimentation slow.

Deploy CrewAI agents directly in production

Rejected because: The agentic pipeline answered test questions correctly but was too slow and expensive for partner use.

Let the runtime agent keep full self-reflection and planning logs

Rejected because: The production pseudo-agent flow removed self-reflection, planning calls, and broad flow-log sharing to reduce cost and latency.

Use general-purpose agent tools at runtime

Rejected because: The deployed pipeline prefers fixed-scope custom tools that produce required outputs quickly.

Patterns

tool-use-orchestration (orchestration)

The deployed pseudo-agent orchestrates scoped database, schema, SQL, RAG, and prompt steps instead of giving a runtime agent unrestricted tool choice.

prompt-chaining (prompting)

The team decomposed text-to-SQL into multiple smaller LLM steps rather than asking one prompt to solve the full task.

multi-agent-orchestration (orchestration)

CrewAI was used experimentally with a customer-facing manager and data analyst agent to discover a better workflow.

cost-aware-routing (optimization)

Production design removed expensive agent self-reflection and flow-log sharing to meet per-query cost and latency budgets.

guardrails-io-filtering (safety)

The object-oriented pseudo-agent architecture separates guardrails so safety controls can change without destabilizing query processing.

Tradeoffs

Use agents as an experimentation tool rather than the production runtime.

Chose A deterministic pseudo-agent pipeline over Deploying the CrewAI agentic pipeline directly.

Rationale: The agentic version found the right decomposition and prompts, but the pseudo-agent version met latency and cost targets.

Hardcode the agent's repeatable behavior.

Chose Fixed step sequences and scoped tools over Autonomous runtime tool planning.

Rationale: The observed agent behavior was consistent enough to preserve the useful steps without paying for planning and reflection at runtime.

Limit context passed to each model call.

Chose Share only required data per API call over Passing full flow logs to the LLM at each step.

Rationale: The narrowed context reduced token usage and cost while preserving the task-specific information needed for each step.

Gotchas (what bit them)

Prompt improvements did not generalize cleanly across question sets.

Root cause: Prompt changes affected different query types differently inside a tightly coupled sequential pipeline.

Resolution: Use agent experiments and a decomposed pseudo-agent pipeline to isolate steps and prompts.

The original pipeline was hard to modify.

Root cause: The simple pipeline's sequential steps were tightly coupled.

Resolution: Refactor into object-oriented components for query processing, guardrails, and tools.

High-accuracy agentic execution was not production-viable.

Root cause: The full CrewAI flow incurred more than one minute of latency and about USD 0.30 per query.

Resolution: Use the agentic flow as a development-time optimizer and deploy a cheaper pseudo-agent flow.

Evaluation

The team compared the simple pipeline, CrewAI agentic experiments, and pseudo-agent production pipeline using accuracy, response time, and cost. CrewAI answered all test database questions correctly but failed latency and cost requirements; the pseudo-agent pipeline preserved the accuracy gain while meeting production economics.

Metrics: CrewAI answered all questions from the test database correctly · CrewAI took more than one minute per query · CrewAI cost approximately USD 0.30 per query · Production target: less than 30 seconds and less than USD 0.03 per query · Pseudo-agent average: less than 15 seconds and less than USD 0.02 per query

Outcomes

• CrewAI improved test accuracy but was too slow and expensive for production.
• The pseudo-agent pipeline achieved under-15-second average response time.
• The pseudo-agent pipeline reduced cost to below USD 0.02 per query.
• The refactor made guardrails and query processing easier to modify independently.

The case reports per-query production targets and averages rather than deployed usage volume; the product is being piloted across multiple contexts in the social-impact sector.