LLM SQL in BigQuery, Snowflake and Redshift: What Differs

The same SQL can mean different things in different warehouses, and the quiet failures cost the most. Integer division shows how: PostgreSQL's documentation shows 5 / 2 returning 2, while BigQuery documents INT64 / INT64 as returning FLOAT64, so a conversion rate built on two integer columns changes with the warehouse. No study cited here measures dialect errors on Shopify data, or on GA4 data alone.

What do the studies and documentation show?

Two preprints and one peer-reviewed benchmark paper tested models across dialects. The vendors' manuals settle what each engine does.

• PolySQL (preprint, May 2026) moved three public benchmarks to five dialects (PostgreSQL, MySQL, Snowflake, BigQuery, ClickHouse) and ran 16 models on 300 shared questions in six dialects, SQLite included. It reports "a 10.1% average accuracy drop from SQLite to other dialects", with BigQuery and Snowflake the hardest tier. Of the queries that failed only because of the dialect change, "61% result in syntactically valid but logically altered SQL, while only 8% fail due to invalid syntax". The authors add that their design "cannot fully disentangle whether these failures stem from a lack of parametric knowledge" or from ignored instructions.
• UniQL (preprint, June 2026) aligns 1,534 questions across 16 dialects and concludes that "current models remain far from dialect-universal", with "limited transfer from SQLite success to other dialects".
• Spider 2.0 (peer-reviewed, ICLR 2025 Oral) covers BigQuery, Snowflake, SQLite and other engines. In its review of 300 randomly sampled examples it attributes 10.3% of the errors to dialect function usage, such as DATE_TRUNC: "These functions require a nuanced understanding, which the models often fail to exhibit."

Both preprints say most text-to-SQL benchmarks run on SQLite, and PolySQL concludes that "SQLite performance is an unreliable proxy for other dialects". All three papers used public benchmarks, and the two preprints state no venue. A syntax error is the cheap failure; the valid but wrong query is the one that returns a plausible number. No study cited here measures dialect errors on Shopify data, or on GA4 data alone.

What does the same division do in each engine?

Each vendor documents it differently:

• PostgreSQL: "for integral types, division truncates the result towards zero", and its example shows 5 / 2 returning 2.
• Amazon Redshift: its return-type table lists an integer divided by an integer as an integer, and "divide-by-zero conditions return errors".
• BigQuery: INT64 divided by INT64 returns FLOAT64, and "Divide by zero operations return an error", with SAFE_DIVIDE as the documented alternative.
• Snowflake: division keeps a decimal scale and "rounds the output (rather than truncating the output)". Its example divides 2 by 7 and shows 0.285714, and DIV0 and DIV0NULL cover division by zero.

For illustration: 40 orders over 1,000 sessions is 0.04, which an engine that truncates integer division returns as 0, so a conversion rate computed from two integer columns reads 0 until one side is cast.

How do I test my warehouse and my queries?

Name the target dialect in every prompt, for example "BigQuery Standard SQL", then run this probe in your own warehouse:

SELECT 5 / 2 AS int_over_int, 5 / 2.0 AS int_over_decimal;

Read the output like this:

1. int_over_int comes back whole. Your warehouse truncates integer division, so every ratio of two integer columns needs a decimal on one side. For example, write orders * 1.0 / NULLIF(sessions, 0), which also avoids a divide-by-zero error.
2. int_over_int comes back with a fraction. Your warehouse keeps the fraction. A query written for a truncating engine will still run against it and give a different number, so check any query that arrives from another engine.
3. Compare a rate. Take a conversion rate from a generated query and compare it with Shopify's or GA4's rate for the same dates and time zone. A rate that is zero, or rounded to a whole number, points to integer division.
4. Look up unfamiliar names. For any date or string function you don't recognise, open the target engine's reference page before you run the query on a full date range.

An LLM can draft a translation between dialects and list the functions that differ, which gives you a checklist. Ask it to name the target engine's division and date functions, then verify each one on the vendor's page.

Sources, 30 September 2026: PolySQL and its full text (arXiv preprint, 2026); UniQL (arXiv preprint, 2026); Spider 2.0 paper (Lei et al., ICLR 2025); PostgreSQL, mathematical functions (PostgreSQL Global Development Group, 2026); Amazon Redshift, computations with numeric values (Amazon Web Services, 2026); BigQuery operators (Google Cloud, 2026); Snowflake arithmetic operators (Snowflake, 2026).

Related answers

• Why an average of averages is not the total
• Schema size and what a model is shown
• Natural language to SQL for marketing
• The Spider2-SQL benchmark, read carefully