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

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Dialect-specific syntax needs a dialect-aware parser: use a SQL parser configured for the dump's dialect, handle its quoting, type names, and vendor extensions explicitly, and fall back to extracting table and column definitions with targeted patterns for the statements the parser rejects. A generic parser will fail on the first vendor construct and take the whole schema import down with it.

agent couldn't parse sql schema dump with dialect specific syntax error failed

Use this when

• The check or run fails with this symptom: the agent cannot parse the schema dump from the source database.
• You are working on an agent failing to parse a SQL schema dump with dialect-specific syntax and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A JSON or YAML schema file failing to parse, which involves no SQL dialect at all instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Identify the dump's dialect from its header comments, quoting style, and type names. Verify: the dialect is identified confidently
2. Run a dialect-configured SQL parser over the dump and capture the first statement it rejects. Verify: the first rejected statement is isolated
3. Classify the rejected constructs: vendor types, storage clauses, generated columns, or procedural blocks the import does not need. Verify: rejected constructs are classified by necessity
4. Configure the parser to skip or translate the constructs the import does not need, keeping table and column extraction exact. Verify: the parser processes the dump with stated skips
5. Extract the schema and compare table and column counts with the source database's information schema. Verify: extracted counts match the source catalog

Variant phrasings

agent could not parse sql schema dump dialect syntax

The verbatim dialect-dump phrasing.

sql dump parser dialect vendor types quoting

The vendor-construct phrasing for types and clauses.

schema dump import parser rejected statement dialect

The rejected-statement phrasing for targeted extraction.

Compatibility: SQL schema dumps from Postgres, MySQL, and Snowflake parsed by agents

Why it happens

Every database extends SQL differently, and dumps include the extensions verbatim: vendor types, index clauses, and procedural code. Parsers written against standard SQL or another dialect fail on those constructs, and the failure line rarely explains that the dialect, not the file, is the problem.

Edge cases / pitfalls

• Generate dumps with a portable mode where the source offers one, when the dump exists mainly for tooling.
• Test the parser against a fresh dump after every source database upgrade; dialects evolve.

Provenance

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

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

Thread query: agent couldn't parse sql schema dump with dialect specific syntax error failed

TL;DR: Dialect-specific syntax needs a dialect-aware parser: use a SQL parser configured for the dump's dialect, handle its quoting, type names, and vendor extensions explicitly, and fall back to extracting table and column definitions with targeted patterns for the statements the parser rejects. A generic parser will fail on the first vendor construct and take the whole schema import down with it.

Not specified by the original author.

TL;DR

The agent is guessing table and column names instead of reading them from the catalog. Pull the live schema from information_schema, put it in the agent's context, and validate every generated query against that schema before it runs. Hallucinated names get caught pre-execution instead of failing (or worse, silently returning empty) at runtime.

Symptom

The agent generates confident SQL referencing tables or columns that are not in the warehouse. Queries fail with "relation does not exist", or return empty results when the agent invents plausible-sounding names. The pattern repeats across runs because nothing corrects the agent's mental model of the schema.

Steps

1. Pull the live schema from the catalog. This is ground truth; the agent must never guess names:

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Expected output: the real tables and columns. Save this as the allowlist every query is checked against.

2. Inject the schema into the agent's prompt context, scoped to the schemas it may query. Keep it compact: table names with their columns, refreshed each run or cached with a short TTL.

Expected output: the agent's generated SQL references only listed tables and columns, because it can see what exists.

3. Validate every generated query against the catalog before executing: parse the identifiers and check each against the schema from step 1. Reject or repair on the first unknown identifier instead of running it.

Expected output: hallucinated names are caught pre-execution; the agent gets the validation error plus the real schema and retries with correct names.

4. Log every rejected query with the hallucinated identifier. If one table name keeps appearing, it is either a stale schema cache or a naming convention the agent expects; fix the cache or add the alias mapping.

Expected output: repeat hallucinations drop to zero after the cache or mapping fix.

When to use

• Agent-generated SQL fails on tables or columns that do not exist
• The agent works on one schema fine but invents names in another
• You are giving an agent write access to run queries at all

When not to use

• The tables exist but the query logic is wrong (that is a SQL-logic problem, not hallucination)
• A human writes the SQL (they can read the schema themselves)
• The failure is permissions, not missing objects (check grants)

Variant phrasings

agent keeps using an old table name after a rename

The schema cache is stale, or the old name is baked into the prompt. Refresh the cache and grep the prompt for the old name.

agent invents columns that sound right but do not exist

Same fix. Column-level validation in step 3 catches these; table-only checks do not.

Why it happens

LLMs complete patterns; without the schema in context, the model fills in the most plausible table and column names from its training data. It has no way to know your warehouse calls it fact_orders_v2 instead of orders. Schema grounding replaces guessing with lookup.

Edge cases

• Very wide schemas do not fit in context; scope to the relevant schemas or summarize to table names plus a column list on demand.
• Temp tables and CTEs the agent creates itself are not in the catalog; exempt the agent's own session objects from validation.
• Case sensitivity: quoted identifiers in some warehouses are case-sensitive; normalize case the same way the warehouse does before comparing.

Provenance

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

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

Thread query: data agent hallucinated a table that does not exist: schema-ground every query

TL;DR: The agent is guessing table and column names instead of reading them from the catalog. Pull the live schema from information_schema, put it in the agent's context, and validate every generated query against that schema before it runs. Hallucinated names get caught pre-execution instead of failing (or worse, silently returning empty) at runtime.