Llm Prompt Formulation Sql Transformation Quality Rules

What this prompt engineering strategy is

This strategy is a comprehensive, directive prompt design framework that establishes strict boundaries, explicit personas, exact syntactic targets, and negative constraints for the Large Language Model (LLM). When generating mission-critical data transformation code and data quality rules, LLMs require structured instructions that leave no ambiguity regarding expected dialect standards and behavioral limits.

How it meets the pipeline requirements

• Role definition: Defining an explicit persona (such as an expert SQL and data quality engineer) primes the model's attention mechanism on database semantics and transformation best practices.
• Granular task specifications: Clearly defines the required steps—extracting exact entities, writing BigQuery standard SQL, and creating Dataform assertion tests.
• Strict negative constraints: Explicitly forbidding inference, assumption, interpretation, or editorializing eliminates hallucinations and ensures transformations remain strictly faithful to source specifications.
• Exact output schema and structure: Enforcing structured formats (such as specific JSON schemas or Markdown code blocks) allows downstream orchestration services to parse and validate outputs deterministically.
• Explicit optimization directives: Providing concrete transformation rules (such as converting non-trivial, repeatedly referenced CTEs into temporary tables and precomputing independent scalar predicates) forces the LLM to output production-grade, cost-efficient SQL.

Key advantages

• High fidelity and reliability: Prevents semantic drift by forcing the LLM to process and extract only explicitly stated business logic.
• Dialect precision: Ensures generated SQL conforms strictly to BigQuery execution optimizations rather than generic database syntax.
• Orchestration integration: Structured output enables seamless ingestion into CI/CD pipelines and automated Dataform test suites.

Why this is optimal

Open-ended or under-constrained prompting leads to nondeterministic code structures, unoptimized queries, and logical hallucinations. By combining detailed task parameters with rigorous negative constraints and explicit output templates, the data team achieves reproducible, enterprise-grade code generation.