DuckDB NL2SQL SFT

DuckDB NL2SQL SFT is a compact end-to-end project for fine-tuning, evaluating, and inspecting a single-table natural-language-to-SQL model for DuckDB.

The current model route is:

Schema + natural-language question
  -> SmolLM2-1.7B-Instruct
  -> LoRA adapter
  -> DuckDB SQL
  -> DuckDB execution evaluation

The project focuses on a controlled single-table setting so that the full SFT workflow can be measured clearly: dataset construction, LoRA training, prompt control, SQL execution, evaluator design, and model comparison.

Current Result

Final adapter:

outputs/adapter

Fixed evaluation set:

data/eval/spider_dev_single_table_execution.jsonl

Final execution metrics:

result_match: 92/100 = 0.920
ordered_result_match: 82/100 = 0.820
pred_execution_ok: 98/100 = 0.980
gold_execution_ok: 100/100 = 1.000
row_count_match: 96/100 = 0.960

LLM-as-judge summary on the same LangSmith experiment:

llm_judge.correct: 92/100 = 0.920
major semantic issues: 7
minor semantic issues: 1

The overall LLM-as-judge score matches execution result_match, but the failed examples are not identical. A compact summary is kept at:

outputs/eval/langsmith_judge_summary.json

Base-vs-adapter comparison view on the same 100-example execution benchmark:

Base SmolLM2-1.7B-Instruct result_match: 0.79
LoRA adapter result_match:                 0.91

The comparison CSV is kept at:

outputs/eval/base_vs_adapter_comparison.csv

Model Comparison Figure

The chart below compares the fine-tuned adapter against the base model on the same 100-example single-table execution benchmark.

Scope

The current scope is intentionally constrained:

• single-table SQL generation
• DuckDB SQL dialect
• SELECT / WITH query generation
• local LoRA SFT
• execution-based evaluation
• local and hosted experiment inspection

The project does not currently target multi-table joins, production SQL safety enforcement, query optimization, or enterprise database integration.

Project Logic

The project has four main stages.

1. Build And Verify Data

Training examples are stored as chat-style SFT rows:

{
  "messages": [
    {"role": "system", "content": "..."},
    {"role": "user", "content": "Schema:\n...\n\nQuestion: ..."},
    {"role": "assistant", "content": "SELECT ...;"}
  ],
  "metadata": {
    "layer": "...",
    "verified_by_duckdb": true
  }
}

All SQL examples are intended to be executable DuckDB SQL. The external evaluation set is verified by actually executing gold SQL against DuckDB databases.

2. Fine-Tune A LoRA Adapter

The base model is:

HuggingFaceTB/SmolLM2-1.7B-Instruct

The adapter is trained with PEFT LoRA. The base model remains frozen; only LoRA weights are trained.

3. Generate SQL

At evaluation time, each example provides:

Schema:
Table ...

Question:
...

The model outputs one SQL query. The SQL is extracted and normalized before execution.

4. Execute And Compare Results

Both predicted SQL and gold SQL are executed against the same DuckDB database. The evaluator compares the returned rows, not just the SQL text.

Data

Training Data

Current training files:

data/train/duckdb_sql_sft_train.jsonl
data/train/duckdb_sql_sft_validation.jsonl

Training data summary:

total_examples: 2027
train_examples: 1816
validation_examples: 211

Layer counts:

duckdb_dialect: 200
multidomain_nl2sql: 400
spider_train_execution: 1000
targeted_aov_hard_cases: 30
targeted_grounding: 200
targeted_hard_cases: 80
targeted_result_grain: 200
targeted_single_table_hard_cases: 128

The targeted hard cases focus on recurring execution failures, including:

• aggregate filters that require HAVING
• result grain mistakes
• global aggregate vs grouped aggregate mistakes
• exact schema grounding
• full literal preservation
• COUNT, SUM, AVG, MIN, MAX projection errors
• top/least/most-common ordering patterns
• sorting by hidden columns without projecting them

Evaluation Data

Current evaluation file:

data/eval/spider_dev_single_table_execution.jsonl

The evaluation set is derived from the official Spider dev split and filtered into a fixed single-table execution benchmark.

Build characteristics:

source_dataset: official_spider_dev
total_examples: 100
candidate_examples_after_filtering: 414
sample_seed: 42
db_count: 19
verified_duckdb_version: 1.5.4
verification_mode: single_table_execution_against_spider_duckdb

Filtering rules:

• one FROM table
• no joins
• no nested SQL
• no set operations
• SELECT-compatible SQL
• transpiled to DuckDB
• gold SQL must execute against the converted DuckDB database

Each evaluation example stores its own DuckDB database path in metadata:

{
  "metadata": {
    "db_path": "data/eval/spider_single_table_duckdb/xxx.duckdb"
  }
}

This avoids evaluating all examples against a single shared database.

Training

Main training script:

scripts/train.py

Default model:

HuggingFaceTB/SmolLM2-1.7B-Instruct

Main command:

python scripts/train.py \
  --model HuggingFaceTB/SmolLM2-1.7B-Instruct \
  --train-file data/train/duckdb_sql_sft_train.jsonl \
  --validation-file data/train/duckdb_sql_sft_validation.jsonl \
  --output-dir outputs/adapter \
  --run-name smollm2-1.7b-duckdb-sql-final \
  --wandb-mode online \
  --num-train-epochs 1

Important default hyperparameters:

assistant_only_loss: true
max_length: 2048
num_train_epochs: 1
per_device_train_batch_size: 1
gradient_accumulation_steps: 8
learning_rate: 2e-4
warmup_ratio: 0.03
lora_r: 16
lora_alpha: 32
lora_dropout: 0.05
target_modules:
  - q_proj
  - k_proj
  - v_proj
  - o_proj
  - gate_proj
  - up_proj
  - down_proj

Final token-level validation metrics:

eval_loss: 0.1445
eval_mean_token_accuracy: 0.9576
epoch: 1.0

Token-level validation is useful for monitoring training, but final model quality is judged by execution evaluation.

Prompt

Prompt config:

configs/sql_prompts.yaml

Only the current default prompt is kept:

v4: single_table_execution_prompt

Earlier prompt variants were removed after testing because prompt-only changes had limited impact once the adapter reached the low-90% execution-match range. Further improvement should come from targeted data and evaluator design rather than longer prompt rules.

Evaluation

Main evaluation script:

scripts/evaluate.py

Evaluate the final adapter locally:

python scripts/evaluate.py \
  --dataset data/eval/spider_dev_single_table_execution.jsonl \
  --prompt-version v4 \
  --model HuggingFaceTB/SmolLM2-1.7B-Instruct \
  --adapter outputs/adapter \
  --local-only \
  --local-output outputs/eval/final_matrix.jsonl

Analyze the local matrix:

python scripts/analyze.py \
  --input outputs/eval/final_matrix.csv \
  --report-output outputs/eval/final_report.txt \
  --failure-output outputs/eval/final_failures.csv

Open the local dashboard:

python scripts/dashboard.py \
  --input outputs/eval/final_matrix.csv

Evaluator Metrics

The evaluator computes:

result_match
ordered_result_match
pred_execution_ok
gold_execution_ok
row_count_match
pred_error_type

Metric meanings:

• result_match: predicted SQL and gold SQL return equivalent rows; row order and column order are ignored.
• ordered_result_match: predicted SQL and gold SQL return exactly the same rows in the same order.
• pred_execution_ok: predicted SQL executes successfully in DuckDB.
• gold_execution_ok: gold SQL executes successfully in DuckDB.
• row_count_match: predicted result and gold result have the same number of rows.
• pred_error_type: normalized DuckDB error category for failed predicted SQL.

Error categories include:

not_select_or_with
syntax_error
table_not_found
column_not_found
group_by_error
unsupported_function
type_conversion_error
binder_error
duckdb_execution_error
none

The evaluator compares execution results instead of SQL strings, so semantically equivalent SQL can pass even when the query text differs from the gold query.

LangSmith

LangSmith can be used to inspect hosted experiment runs and compare prompts/models.

Run with LangSmith:

python scripts/evaluate.py \
  --dataset data/eval/spider_dev_single_table_execution.jsonl \
  --prompt-version v4 \
  --model HuggingFaceTB/SmolLM2-1.7B-Instruct \
  --adapter outputs/adapter \
  --langsmith-dataset c2d-single-table-spider-100 \
  --experiment-prefix adapter-smollm2-v4

The LangSmith evaluator uses the same execution evaluator functions as local evaluation:

• result_match
• ordered_result_match
• pred_execution_ok
• gold_execution_ok
• row_count_match
• pred_error_type

Local evaluation remains the source of truth for final scores because it writes a complete matrix with predicted SQL, gold SQL, execution results, row counts, and error diagnostics.

Backfill LLM-as-Judge Feedback

If an experiment has already been run in LangSmith, the SQL model does not need to be rerun. Export the existing LangSmith experiment table as CSV, then use scripts/judge.py to call a judge model and write judge feedback back to the original LangSmith runs.

The judge compares the question, schema prompt, gold SQL, and predicted SQL, then returns a structured JSON verdict:

{
  "judge_correct": true,
  "judge_error_type": "correct",
  "severity": "none",
  "explanation": "short explanation"
}

Dry-run CSV parsing without calling a judge model:

python scripts/judge.py \
  --input path/to/langsmith_experiment_export.csv \
  --dry-run \
  --limit 3

Run the judge locally and save a JSONL audit file:

python scripts/judge.py \
  --input path/to/langsmith_experiment_export.csv \
  --model gpt-4o-mini \
  --output outputs/eval/langsmith_judge_results.jsonl

Run the judge and upload feedback to the existing LangSmith runs:

python scripts/judge.py \
  --input path/to/langsmith_experiment_export.csv \
  --model gpt-4o-mini \
  --output outputs/eval/langsmith_judge_results.jsonl \
  --upload

If the judge JSONL has already been generated, upload it without calling the judge model again:

python scripts/judge.py \
  --upload-existing outputs/eval/langsmith_judge_results.jsonl

The script writes these feedback keys:

llm_judge.correct
llm_judge.error_type
llm_judge.severity

Feedback is uploaded with deterministic feedback IDs, so rerunning an upload updates the existing judge feedback instead of creating duplicate feedback rows.

The judge error labels are:

correct
wrong_projection
wrong_filter
wrong_aggregation
wrong_grouping
wrong_order_or_limit
wrong_literal_value
schema_grounding_error
unsupported_or_invalid_sql
other_semantic_error

Current judge result summary:

llm_judge.correct: 92/100

judge_error_type counts:
correct: 92
wrong_aggregation: 3
wrong_grouping: 1
wrong_projection: 1
wrong_filter: 1
wrong_literal_value: 1
wrong_order_or_limit: 1

Comparison with execution evaluation:

execution result_match: 92/100
llm_judge.correct:      92/100

both wrong:                         5 examples
execution wrong, judge correct:     #26, #69, #93
judge wrong, execution correct:     #6, #60, #100

This means the two evaluators agree on the overall score but not on every sample. Execution evaluation remains the primary metric because it is deterministic and database-grounded. LLM-as-judge is used as a diagnostic layer for semantic risks such as missing DISTINCT, wrong aggregation grain, wrong literal values, or ordering mistakes that may not always be exposed by a fixed database instance.

The raw judge JSONL is intentionally kept local because it contains LangSmith run IDs. The committed summary file contains aggregate counts and mismatch indexes only:

outputs/eval/langsmith_judge_summary.json

Langfuse

Langfuse integration is available for local execution traces and scores:

python scripts/evaluate.py \
  --dataset data/eval/spider_dev_single_table_execution.jsonl \
  --prompt-version v4 \
  --model HuggingFaceTB/SmolLM2-1.7B-Instruct \
  --adapter outputs/adapter \
  --local-only \
  --langfuse \
  --langfuse-session-id adapter-smollm2-v4

Langfuse records generation inputs/outputs, execution-eval metadata, and score observations for the local matrix run.

Weights & Biases

W&B is used for training logs when enabled:

--wandb-mode online

For offline or disabled logging:

--wandb-mode offline
--wandb-mode disabled

The training script stores W&B local files under:

.wandb/

This directory is ignored by Git.

Repository Layout

configs/
  sql_prompts.yaml

data/
  DATASET_EVOLUTION.md
  train/
    duckdb_sql_sft_train.jsonl
    duckdb_sql_sft_validation.jsonl
    manifest.json
  eval/
    spider_dev_single_table_execution.jsonl
    spider_dev_single_table_execution.manifest.json
    spider_single_table_duckdb/

outputs/
  adapter/
    adapter_model.safetensors
    adapter_config.json
    tokenizer.json
    tokenizer_config.json
  eval/
    final_matrix.jsonl
    final_matrix.csv
    final_report.txt
    final_failures.csv
    base_vs_adapter_comparison.csv
    langsmith_judge_summary.json

scripts/
  train.py
  evaluate.py
  analyze.py
  dashboard.py
  judge.py

Setup

conda create -n duckdb-nl2sql-sft python=3.10
conda activate duckdb-nl2sql-sft
pip install -r requirements.txt

Optional environment file:

cp .env.example .env

Fill .env only if you want Hugging Face, W&B, LangSmith, or Langfuse integration. Do not commit .env.

Model Artifact

The final adapter is committed with Git LFS:

outputs/adapter/adapter_model.safetensors

The current project loads the adapter separately from the base model:

base model: HuggingFaceTB/SmolLM2-1.7B-Instruct
adapter:    outputs/adapter

For deployment, the adapter can be merged into the base model with PEFT's merge_and_unload() workflow.

Current Failure Modes

The remaining failures are concentrated in a small number of SQL reasoning patterns:

• best rank should map to MIN(rank_column)
• most recent/latest should sort by the relevant date column
• exact schema grounding such as pet_age instead of PetAge
• aggregate filtering with HAVING
• full literal values such as Hawaii, Wisconsin, and English
• SUM(tours) vs COUNT(*)
• duplicated or missing projected columns in grouped results

These are better addressed with targeted SFT data than by adding longer prompt rules.