High-Fidelity And Complex Test Data Generation For Real-World SQL Code Generation Services

2025-04-24

AI Summary

In industrial settings, limited production data severely compromises the fidelity of test data for SQL generation services (e.g., NL2SQL), hindering simultaneous preservation of structural integrity and semantic coherence. Method: This paper proposes an LLM-driven high-fidelity test data generation method, integrating Gemini with schema-aware preprocessing, SQL-semantic alignment postprocessing, and constraint-guided sampling—supporting complex patterns including nested columns, multi-table JOINs, aggregations, and deep subqueries. Contribution/Results: The method jointly optimizes semantic consistency, syntactic correctness, and structural fidelity, significantly improving test coverage and defect detection rates. Evaluated on Google’s real-world NL2SQL workloads, it generates high-quality mock data out-of-the-box, effectively addressing the semantic incoherence prevalent in existing approaches under large-scale, complex database schemas.

Technology Category

• Natural Language Processing: Generation
• Search and Optimization: Sampling/Simulation-based Search
• Constraint Satisfaction and Optimization: Satisfiability Modulo Theories

Application Category

• Semantics and Knowledge: Data modeling to support human-machine intelligence, including LLMs agents, intelligent system behavior, explanations, and user-friendly interactions
• Economics, Online Markets and Human Computation: Cost models of using LLMs in production systems
• Web Mining and Content Analysis: Web data generation and simulation

Abstract

The demand for high-fidelity test data is paramount in industrial settings where access to production data is largely restricted. Traditional data generation methods often fall short, struggling with low-fidelity and the ability to model complex data structures and semantic relationships that are critical for testing complex SQL code generation services like Natural Language to SQL (NL2SQL). In this paper, we address the critical need for generating syntactically correct and semantically ``meaningful'' mock data for complex schema that includes columns with nested structures that we frequently encounter in Google SQL code generation workloads. We highlight the limitations of existing approaches used in production, particularly their inability to handle large and complex schema, as well as the lack of semantically coherent test data that lead to limited test coverage. We demonstrate that by leveraging Large Language Models (LLMs) and incorporating strategic pre- and post-processing steps, we can generate realistic high-fidelity test data that adheres to complex structural constraints and maintains semantic integrity to the test targets (SQL queries/functions). This approach supports comprehensive testing of complex SQL queries involving joins, aggregations, and even deeply nested subqueries, ensuring robust evaluation of SQL code generation services, like NL2SQL and SQL Code Assistant services. Our results demonstrate the practical utility of an out-of-the-box LLM ( extit{gemini}) based test data generation for industrial SQL code generation services where generating realistic test data is essential due to the frequent unavailability of production datasets.

Problem

• Generate high-fidelity test data for SQL services
• Address limitations in handling complex schema structures
• Ensure semantic coherence for robust SQL query testing

Innovation

• Leveraging LLMs for high-fidelity test data
• Incorporating strategic pre-post processing steps
• Generating semantically coherent nested SQL data