FME-SQL-LLM Report Generator

Type: Template workspace (Form)

Purpose: Turn a plain-language question about a table into a planned set of SQL queries, execute them in DuckDB, and produce a Markdown → HTML report using three lightweight LLM roles (Planner, SQL Generator, Analyst).

What it does

Standardizes input: reads any tabular file (Excel/CSV/Parquet), normalizes headers, and writes a temporary Parquet dataset.

Profiles data: extracts schema, summary stats, and sample rows with DuckDB (compact “metadata” for prompts).

Plans the analysis (Planner LLM): converts the goal + metadata into formal sub-questions and SQL intents.

Drafts SQL (SQL Generator LLM): returns executable SQL per sub-question (minimal JSON: just sql).

Executes safely in DuckDB: runs each query with guardrails (e.g., COPY (SELECT ...), semicolon stripping, optional LIMIT cap).

Analyzes results (Analyst LLM): synthesizes named result sets into a Markdown narrative answering the original question.

Publishes: renders Markdown to HTML with a simple template and writes the final report.

Why it’s useful

Scales expertise: lets non-SQL users ask meaningful questions; the system plans and writes SQL for them.

Keeps LLMs in their lane: models see only metadata + small samples, while DuckDB does the heavy lifting.

Repeatable & portable: drop in any table, update the research question, and re-run—same pipeline, new insight.

Inputs & outputs

Inputs

Any tabular file (e.g., .xlsx, .csv, .parquet).

A natural-language question (e.g., “Which locations have the most cyclists, and when are the peaks?”).

Outputs

report.html — final HTML report (from generated Markdown).

Intermediates (optional to keep):

temp/*.parquet (standardized table)

schema.sql (DDL), summary.json (stats), sample.csv (few rows)

questions.json (planner output), queries.jsonl (SQL list)

results/*.csv (per-query results, capped)

Parameters (exposed)

Research question (string).

Result row cap (int; default 30) for safety/preview.

Keep intermediates (yes/no).

Model choices

Planner model (name / endpoint / temperature)

SQL Generator model (name / endpoint / temperature)

Analyst model (name / endpoint / temperature)

DuckDB work folder (temp path override).

Markdown → HTML template (file/path or inline).

How it works (pipeline)

1) Convert → Parquet

Normalize headers (e.g., replace non-alphanumerics), write a temp Parquet.

2) Profile in DuckDB

Emit schema.sql, summary.json (via SUMMARIZE/aggregates), and sample.csv (tiny SELECT).

3) Plan (Planner LLM)

Input: research question + metadata → Output: sub-questions + SQL intents (strict JSON).

4) Draft SQL (SQL Generator LLM)

For each sub-question → return only sql (strict), flatten to attributes.

5) Execute in DuckDB

- Remove trailing ;

- Wrap non-SELECT so COPY (SELECT ...) TO ... always receives a SELECT

- Apply optional LIMIT {cap} to guard output size

- Save per-query result files

6) Analyze (Analyst LLM)

Provide original task + metadata + named result sets → Markdown narrative with findings/limitations.

7) Publish

Inject Markdown into HTML template and write report.html.

Guardrails & conventions

No raw table → LLM: only schema, summary, sample rows.

Deterministic output shape: use COPY (SELECT ...) for stable CSV schemas.

Safety caps: configurable LIMIT on SELECT queries.

Strict JSON for planner/generator outputs; validate before execution.

Repro hints: log model name, temperature, and prompts with a run ID.

Example prompts (minimal)

Planner (system, abridged):

You are a data analysis planner. Given a goal and compact metadata (schema, summary, sample),

return STRICT JSON with sub-questions and brief SQL intents. No SQL execution.

SQL Generator (system, abridged):

You return ONLY a JSON object: {"sql": "<single SQL statement>"} executable in DuckDB.

No explanation, no comments, no code fences.

Analyst (system, abridged):

Synthesize named result tables into a concise Markdown report answering the original question.

Reference table names; note assumptions and limitations. No code.

Requirements

FME Form 2025.1+

DuckDB (included/embedded or accessible from FME run environment)

LLM endpoints (OpenAI-compatible or local gateway) for the three roles

Extending

Add validation checks (row count, coverage, sum/avg bounds) before the Analyst step.

Swap Markdown → PDF or add small charts from result CSVs.

Cache metadata for repeated runs on the same dataset.

Localize labels / dictionary for non-English reports.

Version & compatibility

v1.0 — initial release (three-role pipeline, HTML reporting).

Tested with FME Form 2025.1 and DuckDB 1.x.

Tags

FME · DuckDB · LLM · OpenAI-compatible · SQL · Analytics · Automation · Parquet · Data Profiling · Markdown · HTML Report