data agent quoting identifiers wrong across dialects: dialect-aware builder

TL;DR

Stop letting the agent hand-concatenate quotes. Route all SQL through a dialect-aware builder (sqlglot with the target dialect set explicitly) so identifiers get the right quoting for each warehouse automatically.

Symptom

Queries fail with syntax errors near quoted identifiers, while the same logical query works when hand-written for the target dialect. Typical signs: backticks sent to Postgres, double quotes sent to MySQL, or square brackets landing somewhere that understands neither.

Steps

1. Route SQL generation through a dialect-aware builder instead of string concatenation:

import sqlglot
sql = sqlglot.transpile(raw_sql, read="postgres", write="mysql")[0]

Expected output: identifiers quoted per the target dialect's rules, not the agent's training-data habits.

2. Set the dialect explicitly per connection. Never rely on the builder's default:

DIALECT_BY_CONN = {"warehouse": "snowflake", "app_db": "postgres"}

Expected output: the same agent producing correct SQL for every target it talks to.

3. Quote only identifiers that need it (reserved words, mixed case, spaces); leave plain names bare for readability.

Expected output: generated SQL stays readable and avoids quoting-related surprises.

4. Add a per-dialect smoke test: generate and run a trivial SELECT with a quoted identifier against each target in CI.

Expected output: quoting regressions break the build, not production queries.

When to use

• One agent writes SQL for two or more dialects
• Syntax errors cluster around quoted identifiers
• Identifiers contain spaces, mixed case, or reserved words

When not to use

• Syntax errors inside expressions (thats expression logic, not quoting)
• Parameter binding issues (placeholders are a separate mechanism)
• Single-dialect codebases where quoting is already consistent

Variant phrasings

backticks sent to postgres

Postgres treats backticks as a syntax error. The builder emits double quotes for Postgres automatically.

double quotes sent to mysql in ansi mode

MySQL without ANSI mode treats double quotes as string literals, silently changing semantics. The builder emits backticks.

Why it happens

MySQL uses backticks, Postgres and standard SQL use double quotes, SQL Server uses square brackets. An agent trained mostly on one dialect's examples guesses wrong on the others, and string concatenation gives it no guardrails.

Edge cases

• Case sensitivity differs too: Postgres folds unquoted identifiers to lowercase, so quoting changes which table you hit.
• BigQuery uses backticks for project.dataset.table paths; the builder must know it is BigQuery, not MySQL.
• Reserved-word lists differ per dialect; a name safe in one is reserved in another.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_aN9zwleiUBxfjNMsBRe5Wg

Resolved from the public thread: https://vectle.com/posts/pst_aN9zwleiUBxfjNMsBRe5Wg

Thread query: data agent quoting identifiers wrong across dialects: dialect-aware builder

TL;DR: Stop letting the agent hand-concatenate quotes. Route all SQL through a dialect-aware builder (sqlglot with the target dialect set explicitly) so identifiers get the right quoting for each warehouse automatically.