Why AI-Generated SQL Fails on Parquet—and How We Ground It

This query is valid DuckDB:

SELECT count(*)
FROM read_parquet('flights-200k.parquet');

It still fails in ViewParquet. The browser session has no promised filesystem path by that name. The loaded relation is data:

SELECT count(*) AS rows
FROM data;

That is the governing problem. The model must draft from the database context that actually exists. Code—not prompt prose—then decides what can run and what can leave the browser.

We met several versions of this failure while building ViewParquet’s optional data analyst: invented paths and columns, SQL from the wrong dialect, unbounded chart results, and queries that selected values the question did not require. A longer prompt did not create a reliable boundary.

This article follows one practical method: supply a small, accurate view of the dataset, let the model draft one focused query, and enforce the important limits in code. It is not a model benchmark, and it does not claim that one prompt works for every Parquet dataset.

Start with a small, accurate model of the data#

A reader needs a mental model before they need a prompt.

When you open Parquet in ViewParquet, DuckDB exposes the current dataset as the relation data. The assistant does not need the full file in its prompt. It first needs the exact column names, DuckDB types, the row count once available, and a statistical profile. It can then ask one narrow question of the relation.

The prompt helps the model choose useful SQL. It does not decide whether that SQL can run. A parser enforces one read-only SELECT. Sharing settings govern preloaded schema, profile values, and query-result rows. Tool calls and saved context can still provide structural metadata and SQL. DuckDB produces the evidence.

The same rule applies to columns. If the schema says departure_delay, do not let the prompt quietly rename it to delay_minutes. Inspect the schema again after a missing-column error. Do not repeat the same query and hope that the engine changes its mind.

Profile once, then ask a real question#

An assistant can waste several queries “looking around” before it answers anything. ViewParquet prepares a statistical profile when the right panel is open for the active dataset.

As of August 26, 2026, ViewParquet asks DuckDB to run SUMMARIZE data for types, null percentages, approximate distinct counts, ranges, and approximate quantiles. DuckDB still processes the relation; ViewParquet keeps profile results for at most the first 40 columns. On tables with 60 columns or fewer, it can run a separate top-category query for low-cardinality text columns.

The caller waits up to six seconds for each query. Those timers bound waiting time and prompt context, not the cost of the underlying DuckDB work. Query cost and cancellation still depend on the dataset, engine, and surrounding load lifecycle. These are product limits, not general Parquet guidance.

The profile should reduce aimless exploration. It should not remove judgment. If the user asks whether distance has a linear association with flight delay, run a targeted query such as corr(distance, delay). Correlation is descriptive; it does not establish an explanation or a cause. If the user asks for an exact distinct count, do not present a HyperLogLog estimate as exact.

DuckDB documents that SUMMARIZE returns approximate unique counts and approximate quantiles. Its approximate aggregate reference identifies HyperLogLog for distinct counts and T-Digest for quantiles. Label those facts in the answer. Use exact SQL only when exactness matters.

Models use the wrong SQL dialect; keep rewrites mechanical#

Generated SQL often carries habits from another engine. A function name can look familiar while belonging to BigQuery, Spark, Hive, Presto, Trino, Redshift, or Oracle.

ViewParquet currently performs a small set of mechanical function-name substitutions. They target common call shapes, and DuckDB still validates the resulting arguments and types. If a transformation needs context, new arguments, reordered arguments, or a changed expression, the system stops and returns a useful error.

The current layer handles a few other names. It is a text-level convenience, not an abstract-syntax-tree transpiler. A matching function-like token inside a string literal or comment can also change, and unsupported arities can still fail in DuckDB. The accepted SQL is therefore shown for review. A failed query is better than SQL that runs and silently changes the question.

Here is a safe DuckDB binning pattern for a chart. It makes the range and bin count explicit:

WITH bounds AS (
  SELECT min(delay) AS lo, max(delay) AS hi
  FROM data
  WHERE delay IS NOT NULL
)
SELECT
  CASE
    WHEN hi = lo THEN lo
    ELSE lo + least(
      39,
      greatest(0, floor((delay - lo) / nullif((hi - lo) / 40.0, 0)))
    ) * ((hi - lo) / 40.0)
  END AS delay_bin,
  count(*) AS flights
FROM data, bounds
WHERE delay IS NOT NULL
GROUP BY delay_bin
ORDER BY delay_bin;

It is longer than width_bucket. It is also reviewable in the engine that will execute it.

A prompt is not a SQL safety boundary#

The system prompt says “one read-only SELECT.” That instruction is useful, but code enforces the rule.

Before execution, ViewParquet asks DuckDB to parse the SQL. The query tool accepts exactly one statement whose parsed node is a SELECT. It rejects writes, multiple statements, and invalid SQL before execution.

-- Accepted statement class
SELECT
  round(avg(delay), 2) AS average_delay
FROM data;

-- Rejected: more than one statement and a write operation
SELECT count(*) FROM data;
DROP TABLE data;

The parser boundary answers one question: can chat execute this statement class? It does not prove that a SELECT is cheap, statistically sound, or privacy-preserving. A large join, sort, regular expression, or unnest can still consume substantial browser resources. Cost and meaning need separate review.

The profile process is also separate from the chat query tool. It can run DuckDB’s SUMMARIZE statement internally. The chat tool stays limited to SELECT.

Return evidence, not a blob of rows#

A useful query result has structure. The current tool returns whether execution succeeded, the row count, column count, column names and types, execution time, and the accepted SQL for review. It may also return a bounded set of rows to the model.

The user’s result table and the model’s context are not the same thing. If query-result sharing is disabled, the SQL can still run and the complete result can remain visible to the user. The model receives no sample rows from that result.

The default data-sharing profile on August 26, 2026 includes column names, types, dataset metadata, and up to 50 rows from a deliberate query result. Value-bearing profile facts—such as ranges, quantiles, and top categories—are off by default. Users can change these controls.

This is why “the data is local” is too broad for an AI feature. Local file viewing and DuckDB SQL run in the browser. Optional AI sends the message, configured preloaded context, and any applicable tool or saved context to the selected provider. The privacy page describes that separate network path.

A prompt that says “do not reveal rows” is not access control. Apply the sharing decision before building the provider request.

A chart can be bounded and still be wrong#

The chart path bounds delivered results at 2,000 rows. That cap limits the input to the renderer; it does not guarantee a cheap chart or make a raw-row chart truthful.

If the question asks for average delay by distance band, first aggregate in SQL:

SELECT
  CASE
    WHEN distance < 500 THEN 'short'
    WHEN distance < 1500 THEN 'medium'
    ELSE 'long'
  END AS haul,
  round(avg(delay), 2) AS average_delay,
  count(*) AS flights
FROM data
WHERE delay IS NOT NULL
  AND distance IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

We ran this query against the checked-in flights sample. The file has SHA-256 6057fc59877fa24f0b9ece1e1807ec2709348cc7e687a871293a96b1040f4976 and 231,083 rows.

The result now has three rows. Each flights count is also the denominator used for that band’s average because the query excludes NULL delay and distance values.

By contrast, an unordered LIMIT 2000 on raw rows returns an arbitrary, engine-dependent subset. A reservoir sample can be valid for an explicitly sampled scatter plot, but say that it is a sample. For category totals, distributions, and time series, aggregate or bin before the renderer sees the data.

A prompt that earns its place#

A strong data prompt is short because the system has already supplied the stable context.

Task: compare average flight delay across distance bands.
 
Relation: data
Dialect: DuckDB
Known schema:
- delay SMALLINT
- distance SMALLINT
- time FLOAT
 
User-supplied bands:
- short: distance < 500 miles
- medium: distance >= 500 and < 1500 miles
- long: distance >= 1500 miles
 
Rules:
- run one read-only SELECT
- use only listed columns
- define each distance band in SQL
- return one row per band with average delay and row count
- ignore NULL delay or distance values and state that choice
- do not return individual flight rows
- use explicit aliases

This prompt gives the model a relation, dialect, schema, question, null policy, and output shape. It does not ask the model to invent business thresholds. In a real analysis, replace the demonstration bands with thresholds from the user or a documented contract.

The code around the prompt still does the harder work:

1. Build context from the active dataset and sharing settings.
2. Parse and validate one SELECT.
3. Apply the small documented rewrite list and surface the executed SQL.
4. Execute in the current DuckDB session.
5. Classify failures such as missing columns, missing tables, syntax, and unsupported functions.
6. Return bounded evidence and the accepted SQL.

purpose: answer one question about the loaded Parquet dataset
relation: data
dialect: DuckDB
context:
  - preloaded schema and values follow sharing settings
  - saved context may add prior schema and SQL
method:
  - inspect schema and the available profile summary first
  - state one testable question
  - run one read-only SELECT
  - aggregate or apply an explicit, meaningful ordered limit
constraints:
  - use only known columns
  - do not invent values or thresholds
  - do not use SELECT *
  - do not expose row values unless sharing permits it
  - keep provider-visible rows within the configured sharing cap
on_error:
  - classify the failure
  - inspect schema or dialect mismatch
  - do not repeat unchanged SQL
output:
  - answer first
  - include sample size, null handling, and approximation caveats

What belongs to ViewParquet, and what travels#

Several details in this article describe ViewParquet as it exists on August 26, 2026:

• the relation name data;
• the profile and row-sharing limits;
• the current function rewrite and rejection rules;
• the one-SELECT chat boundary;
• the 2,000-row chart fallback;
• the structured error categories.

These are implementation facts, not universal Parquet or DuckDB rules. They can change with the product and should carry a date when quoted.

One more boundary matters. ViewParquet uses a lightweight dataset fingerprint for browser-local AI memory. It combines the filename, file size, row and column counts, and schema. It is a convenience key, not a content hash or immutable provenance record. Use a cryptographic file hash or immutable object version when identity matters.

This article does not measure model accuracy, latency, or token cost. It documents failure modes and current controls. It is not a complete evaluation of the AI analysis path.

Review checklist#

Before shipping an AI-to-SQL path for Parquet, check the following:

• The model sees the real relation names, exact columns, and DuckDB types.
• Approximate profile facts are labelled approximate.
• Exact questions trigger exact SQL only when needed.
• The executor parses and permits one read-only statement class.
• Mechanical cross-dialect rewrites are limited, documented, and visible in the executed SQL.
• Ambiguous rewrites fail with a next step.
• Queries aggregate or apply a meaningful ordered limit.
• Chart SQL controls the mark count before rendering.
• Sharing settings govern preloaded schema, profile values, and result rows; tool and saved context are disclosed separately.
• Accepted SQL and structured results remain available for human review.
• Dataset identity is not confused with a filename or convenience fingerprint.

The best prompt is not the longest one. It gives the model the smallest accurate database context that can answer the question. Code then protects the boundaries that prose cannot enforce.

Primary references#

• DuckDB SUMMARIZE statement (opens in a new tab)
• DuckDB aggregate and approximate aggregate functions (opens in a new tab)
• DuckDB SELECT statement (opens in a new tab)
• DuckDB Parquet overview (opens in a new tab)
• ViewParquet optional AI data sharing
• Verified public Parquet samples