Overview
ansi is one of the values in the conversion pipeline's SQLDialect enum, alongside tsql (SQL Server), plsql (Oracle), plpgsql (PostgreSQL), mysql, spark_sql, and unknown. Unlike those other dialects, ansi isn't tied to one vendor. It's the classification the Discovery phase assigns to SQL that is close to the ANSI SQL standard but doesn't carry the distinctive procedural syntax of a dialect with its own named entry.
In practice, this is where Snowflake, BigQuery, and Teradata scripts usually land, since all three are built on top of standard SQL rather than a heavily proprietary procedural extension the way T-SQL or PL/SQL are. A field validator in the Discovery phase also normalizes a range of LLM spellings and labels down to the dialects that do have dedicated handling. Anything that doesn't match one of those falls through to unknown.
When ANSI Is Used
During Discovery, Claude reads your .sql file and classifies it into the SQLDialect enum as part of building the file's object inventory. ansi is the classification it reaches for when the SQL is clearly standard/relational but doesn't match the syntax fingerprints of a dialect with its own dedicated handling.
Not the Same as unknown
ansi means the file was recognized as standard SQL and classified with reasonable confidence. unknown is reserved for SQL that couldn't be confidently classified into any dialect at all, and 3XCode still attempts conversion either way.
A Deliberate Fallback, Not a Failure
Landing on ansi isn't an error state. It simply means your source platform's SQL is close enough to the standard that it doesn't need a dedicated dialect entry. The pipeline still runs the full five-phase conversion against it.
Vendor Fingerprinting Within ANSI
Dialect classification isn't the only detection layer. A separate, deterministic pass, platform_detect, runs after the LLM inventory returns and scores your file's syntax against markers for 10 vendor platforms, including Snowflake, BigQuery, and Teradata. This layer is what tells 3XCode which specific platform an ansi-classified file most likely came from, even though they all share the same ansi dialect value.
Regex-Only, Zero LLM Cost
Platform fingerprinting is a static, regex-only pass: no additional model call, and it never blocks Discovery. If it can't determine a platform, detection is simply skipped and the pipeline continues.
Fully Explainable
The result includes the winning platform, a confidence label, and the exact syntax markers that fired, never a black-box guess. Every generated PySpark file gets a one-line header comment naming the detected platform and confidence.
What It Means for Conversion Confidence
Because ansi is a generic classification rather than a named dialect, there's no dialect-specific bucket of known behavioral gaps tied to the ansi label itself. What actually decides which gotchas apply (for ansi and every other dialect alike, including tsql's reversed DATEDIFF argument order) is construct-level detection (date functions, window functions, NULL handling, string operations, joins, and so on) at Planning and Validation time, not the dialect name.
Confidence Grading Still Applies
Every output file still gets a per-file completion and confidence score, rolled up into a letter grade (A+ through F) from the same scoring model used for every other dialect: syntax validity, Claude's correctness review, resolved dependencies, and outstanding TODOs all factor in identically.
Gotcha Coverage Is Construct-Based, Not Dialect-Based
Validation and Auto-Fix still catch the same category of issues (date-function differences, NULL-ordering, deprecated APIs) because those checks are triggered by which SQL constructs your file actually uses, not by which named dialect it was classified as.
The practical takeaway: an ansi-classified file converts through the exact same five-phase pipeline as every other dialect, with the same auto-fix safety guardrails (revert on syntax error, revert if the fix shrinks the file by more than 50%). The audit report is where to look. It flags exactly which issues were auto-fixed, which need manual review, and which grade the file received, regardless of which platform it came from.
Gotchas Still Checked
These checks are triggered by the SQL constructs detected in your file, not by dialect name, so they apply to Snowflake, BigQuery, and Teradata sources classified as ansi just as they do to any other dialect:
Construct-driven gotchas the pipeline checks for
| Category | Gotcha | Handling |
|---|---|---|
| Date functions | months_between returns a DOUBLE, not an INT, in PySpark. | Checked during Validation; flagged as a TODO if unresolved. |
| Date functions | date_sub with a negative value moves forward in time, not backward. | Checked during Validation. |
| Window functions | Window frame `RANGE` can include duplicate rows unexpectedly. | Checked during Validation. |
| NULL semantics | NULL ordering in `ORDER BY` can differ between the source platform and Spark. | Checked during Validation's NULL-semantics review. |
| NULL semantics | COUNT(*) vs COUNT(col) behave differently with NULLs. | Checked during Validation. |
| String operations | Spark is case-sensitive by default, which can differ from the source platform's comparison behavior. | Checked during Validation. |
| API deprecations | .unionAll() was removed in PySpark 3.x. Use .union() instead. | Flagged during Validation; targeted by Auto-Fix when detected. |
| No direct equivalent | MERGE/upsert, recursive queries, and pivot with dynamic columns have no safe automatic transform. | Classified as a manual-review item, never auto-fixed. |
Converting an ANSI-Classified File
There's no flag to force or override dialect classification. Discovery handles it automatically, whether your source is Snowflake, BigQuery, Teradata, or plain standard SQL. Point the pyspark agent at your file or directory as usual:
# Convert a single standard-SQL script (Snowflake, BigQuery, Teradata, etc.) 3xcode pyspark convert warehouse_queries.sql # Dry-run: discovery + planning only, no conversion 3xcode pyspark analyze warehouse_queries.sql # Convert every .sql file in a directory (bulk mode) 3xcode pyspark convert --dir ./sql/ --session "warehouse-migration" -w 4 # List sessions to find the auto-generated ID for an interrupted run # (it's not the bare --session name you passed above) 3xcode session list # Resume using the ID shown by "session list" 3xcode pyspark convert --resume <session-id-from-list>
After conversion, check <stem>_audit.md in your output directory. It shows the per-file confidence/completion grade and the detected platform (with confidence and matched markers) in the generated file's header comment, so you can confirm 3XCode correctly identified Snowflake, BigQuery, or Teradata as the specific source even though all three share the ansi dialect classification.
FAQ
Does 3XCode support Snowflake, BigQuery, and Teradata specifically?
Yes. They're recognized at the platform-fingerprinting layer (a deterministic, regex-based pass that identifies the specific vendor), even though all three are classified under the same ansi dialect at the SQLDialect level, since their syntax is close to the ANSI standard rather than carrying a distinctive procedural extension of their own.
Is an ANSI classification less accurate than a named dialect like T-SQL?
Not less accurate: there's no gap in coverage to begin with. Even a dialect-flavored gotcha like T-SQL's reversed DATEDIFF argument order is actually surfaced through construct detection (recognizing a date-function pattern in the file), not because the file was labeled tsql. So ansi sources get checked against the exact same construct-driven gotchas (date functions, NULL semantics, window functions, and so on) as every other dialect. Nothing is gated by dialect name.
What's the difference between ansi and unknown?
ansi means Discovery recognized the file as standard SQL. unknown is reserved for SQL the classifier couldn't confidently place in any dialect. Either way, 3XCode still attempts conversion: dialect classification informs what knowledge gets injected during Planning, it doesn't gate whether conversion runs.
Do I need to tell 3XCode which platform my ANSI file came from?
No. There's no --platform or --dialect flag. Both the dialect classification and the vendor fingerprinting run automatically during Discovery, and the detected platform is written into the output file's header comment for your reference.
Is my ANSI SQL source code sent to the 3XDE backend?
No. Source SQL is never sent to the 3XDE backend for any dialect, including ansi. The CLI talks to your configured LLM provider directly for conversion. The backend only handles auth, licensing, and provider-key metadata.