Dify in Practice: Enterprise-Grade End-to-End Pipeline Design for Natural Language to SQL

In enterprise data analytics, enabling business users who don't know SQL to query databases using natural language has always been a high-value application. NL2SQL (Natural Language to SQL) is a classic problem in the natural language processing field, with extensive research predating the rise of deep learning. Traditional approaches relied on syntax rule parsing and semantic slot filling, with accuracy limited by the coverage of predefined templates. With the emergence of large language models, LLM-based NL2SQL solutions have become mainstream, where models generate SQL by understanding natural language semantics combined with database structure information. Commonly used evaluation benchmarks in the industry include Spider and WikiSQL. The current state-of-the-art approaches achieve execution accuracy above 85% on the Spider benchmark, but in real enterprise environments, accuracy often drops significantly due to complex table structures, diverse business terminology, and varying query requirements.

Bilibili content creator Bo Ge recently shared a "Natural Language to SQL" (NL2SQL) visualization case built on the Dify platform, fully demonstrating the end-to-end design from user questions to SQL generation, execution, result analysis, and chart visualization. Dify is an open-source LLM application development platform that provides one-stop capabilities including visual workflow orchestration, knowledge base management, model integration, and API publishing. Unlike code-based frameworks such as LangChain, Dify emphasizes a low-code/no-code development experience, allowing users to build complex AI workflows by dragging and dropping nodes. It supports integration with multiple models including OpenAI, Tongyi Qianwen, and DeepSeek^Ad, and comes with a built-in RAG (Retrieval-Augmented Generation) pipeline that makes knowledge base construction and retrieval very convenient. This article outlines the core architecture and engineering approach of this solution.

Demo Results: From a Single Sentence to Visual Charts

The final output of the entire pipeline is quite intuitive. Users type a natural language question in the chat box — for example, "What's the monthly transaction trend over the past year?" — and the system automatically completes a series of processing steps: it first generates the corresponding SQL statement based on the natural language description, queries and aggregates the past year's sales data, then provides data analysis and business recommendations, and finally outputs the corresponding visualization chart.

In the demo, the author also tested different types of questions such as "sales share by product category," and the system was similarly able to generate SQL, provide analytical insights, and render visualizations like pie charts. It's worth noting that due to the large number of nodes in the pipeline, actual execution has noticeable latency, especially at the core "natural language to SQL" node — which indirectly reflects the tradeoff between accuracy and response speed that enterprise-grade NL2SQL solutions must navigate.

Three Knowledge Bases: Making SQL Generation More Accurate

The first key design element of this solution is the introduction of three knowledge bases that provide the LLM with sufficient context to improve SQL generation accuracy.

• Database Schema Knowledge Base: Stores the database table structures (Schema) along with business descriptions for each table and field. A database schema is metadata describing the database structure, including table names, field names, data types, primary/foreign key relationships, index information, and more. In NL2SQL scenarios, providing schema information to the LLM is the most fundamental and critical step — if the model doesn't know what tables and fields exist in the database, it cannot generate valid SQL. In engineering practice, how the schema is organized directly impacts generation quality: simply dumping CREATE TABLE statements to the model yields limited results. A better approach is to attach business semantic descriptions to each table and field (e.g., annotating the 'order_amount' field as 'order amount, in RMB yuan') and indicate relationships between tables. When the database is large, schema pruning is also necessary — retrieving only relevant table structures based on the user's question to avoid exceeding the model's context window limit.

• Business Synonym Knowledge Base: Addresses the problem of non-standardized user queries. In enterprise scenarios, there's a natural semantic gap between how business users express themselves and how database fields are named. For example, a database field might be called 'customer_id,' but business users might say "buyer," "client," "user," or other variations. More complex cases involve industry-specific abbreviations and colloquialisms, such as "GMV" (Gross Merchandise Volume), "SKU" (Stock Keeping Unit), and "average order value" (average spending per customer) in the retail industry. The synonym knowledge base is essentially a controlled vocabulary that normalizes multiple natural language expressions into a single database field or business concept. This mapping mechanism is also widely used in traditional search engines, known as Query Expansion or synonym normalization.

• High-Frequency Case Knowledge Base: Accumulates common query scenarios, such as "inventory below safety stock," "order status distribution for a specific merchant," "city customer spending ranking," "average order value," and "product sales ranking." Through case retrieval, the model can reference existing patterns to generate more reliable SQL. This example-based generation approach is academically known as Few-shot Learning or In-context Learning, and is one of the most effective methods for improving generation quality in current LLM applications.

After a user inputs a question, the system first retrieves matching information from these three knowledge bases, then consolidates the retrieval results through template transformation to form high-quality input for the LLM. This triple-context design of "Schema + Synonyms + Cases" is the core engineering approach for improving generation quality.

Multi-Model Competition and Judge Mechanism: Selecting the Best

The most noteworthy design in this solution is the introduction of a multi-model competition and judge evaluation mechanism in the SQL generation stage.

Rather than relying on a single model, the author simultaneously calls three LLMs — Tongyi Qianwen, DeepSeek, and GLM — having each generate its own SQL statement. This strategy has deep theoretical foundations in the machine learning field, aligning with the principles of Ensemble Learning. Classic ensemble methods like Bagging reduce variance by training multiple models and voting or averaging results, while Boosting reduces bias through sequential error correction. In NL2SQL scenarios, different LLMs may generate different SQL for the same natural language question due to differences in training data, model architecture, and SQL syntax understanding. By having multiple models independently generate candidate SQL statements, then using a "judge" model or rule system to select the best one, the random errors and hallucination issues of any single model can be effectively mitigated. The cost of this strategy is linear growth in expense and latency — calling three models means triple the API fees and (in serial execution) triple the wait time, so production deployments typically use parallel calls to compress latency.

The post-generation processing pipeline includes several key steps:

SQL Cleaning and Security Validation

Model output may contain extraneous content such as reasoning processes, which need to be cleaned to extract pure SQL. Security validation is also performed, allowing only safe operations like queries and inserts to prevent accidental deletion or malicious operations. SQL injection and misoperation are among the most common security risks in database applications. In NL2SQL scenarios, this risk is further amplified — LLMs may generate destructive statements like DELETE, DROP TABLE, or UPDATE due to misunderstanding, or generate full-table operations without WHERE clause restrictions. Common validation strategies include: whitelist mechanisms that only allow read-only operations like SELECT; AST Parsing (Abstract Syntax Tree Parsing) to detect dangerous operations by parsing the SQL syntax tree; execution permission control using read-only database accounts for queries; and result set limits that automatically add LIMIT clauses to prevent system overload from massive data returns. More stringent solutions also introduce manual approval workflows for secondary confirmation of queries involving sensitive data.

Probe Queries to Verify Executability

Generated SQL undergoes probe-style query verification first, with grouping, aggregation, and other processing applied as needed to ensure the SQL is executable. Probe queries are a lightweight verification technique in database engineering. The core idea is to verify SQL executability through low-cost means before formal execution. Common probe strategies include: EXPLAIN analysis, using the database's EXPLAIN command to view the SQL execution plan and determine whether it can be properly parsed and optimized; LIMIT 0 or LIMIT 1 queries, wrapping the original SQL with an extremely small result limit to verify SQL syntax correctness and table/field existence while avoiding large data scans; and DRY RUN mode, which some database systems support for simulated execution without actually returning results. In NL2SQL scenarios, probe verification is particularly important because LLM-generated SQL may reference non-existent table or field names. Early detection avoids exposing errors to end users and also provides executability as a dimension for judge evaluation.

Judge Aggregation and Evaluation

This is the finishing touch. The system includes a "judge" node that aggregates and scores multiple candidate SQL statements, ultimately selecting the winning SQL and outputting the winning ID, score, status, and other information.

Only when the status is "OK" does the pipeline proceed to the next step of executing the query. This design pattern of "multi-model generation → security validation → judge evaluation" significantly improves SQL correctness and safety, providing an essential reliability guarantee for enterprise NL2SQL deployment.

From Query Results to Visual Charts

Once the winning SQL passes validation, the system executes the actual query (Warm SQL) and retrieves the result data.

The next phase involves two steps:

• Result Analysis: The returned query data is analyzed to extract business insights and recommendations, rather than simply presenting raw numbers to users. This step fully leverages the LLM's strengths in text generation and data interpretation — the model can identify trends, outliers, and key metric changes in the data, then provide recommendations in natural language, such as "Sales have been declining over the past three months; consider reviewing the Q4 promotional strategy."

• Visualization Generation: This is where the ECharts template library comes in. ECharts is an open-source JavaScript visualization library developed by Baidu and donated to the Apache Foundation. It's one of the most widely used data visualization tools in China, supporting dozens of chart types including line charts, bar charts, pie charts, scatter plots, and maps. ECharts' core design philosophy is "configuration-driven" — developers define chart type, data, styling, and interactive behavior by writing a JSON configuration object (option). This declarative API design naturally lends itself to LLM integration: the model only needs to generate an ECharts option configuration JSON based on the data and user intent, and the frontend can directly render the corresponding chart. The ECharts template library in this solution leverages exactly this characteristic, predefining configuration templates for common chart types that the model fills with query results, reducing the error rate in chart generation.

It's worth mentioning that the author created a modular breakdown of the complete pipeline, splitting it into multiple independently reusable sub-processes: the visualization module (simple version), SQL generation unit, SQL execution unit, result analysis, visualization processing, and more. This decomposition approach is extremely valuable for engineering practice — complex NL2SQL pipelines can be split into independent nodes by responsibility, making them easier to debug and preview, as well as flexibly combine across different business scenarios. This actually reflects the software engineering principles of "Separation of Concerns" and the "Single Responsibility Principle," which are equally applicable in AI application development.

Engineering Insights: Key Elements for NL2SQL Deployment

The value of this case lies not in "whether it can generate SQL," but in demonstrating the complete set of engineering elements that enterprise-grade NL2SQL deployment must consider:

• Context Quality: Simply throwing a question at an LLM isn't enough — you must provide Schema, business terminology mappings, and high-frequency cases as knowledge support. This is essentially a deep application of RAG (Retrieval-Augmented Generation) architecture in a vertical domain — using external knowledge retrieval to compensate for the LLM's lack of domain-specific business knowledge.

• Generation Reliability: A single model is unreliable. Multi-model parallel generation combined with a judge evaluation mechanism improves accuracy. While this ensemble strategy increases costs, it's a worthwhile investment in enterprise scenarios with high accuracy requirements.

• Security: Generated SQL must undergo security validation, restricting executable operation types to prevent data misoperation. In production environments, it's recommended to follow the principle of least privilege, granting query accounts only SELECT permissions.

• Executability Verification: Probe mechanisms verify in advance whether SQL can execute normally, avoiding exposing database errors directly to end users.

• End-to-End Value Delivery: From SQL results to data analysis to visual charts, a complete business value loop is formed. For business users, what they need isn't a SQL statement, but data insights that support decision-making.

For teams looking to deploy NL2SQL in their own enterprises, low-code agent orchestration platforms like Dify provide a relatively low implementation barrier, but what truly determines success or failure is still the handling of the engineering details described above. Of course, the tradeoff is response latency caused by the large number of pipeline nodes. In actual deployment, further optimization between accuracy and performance is needed — for example, caching high-frequency query results, or creating fast-track paths for simple questions that skip multi-model competition to improve response speed.