sqlglot

When implementing the table allowlist, exp.CTE aliases need to be collected and excluded first, otherwise every WITH query would be wrongly rejected, because CTE names look like tables in the syntax tree.

sqlglot renames node classes across versions, so tuples should be built using hasattr(exp, name) filtering, the tested sqlglot version should be pinned, and guardrail tests re-run on upgrade.

Simple string checks (such as sql.strip().lower().startswith('select')) fail in at least three cases: semicolon-chained statements, data-modifying statements hidden in CTEs, and reading files via table functions in DuckDB.

After parsing SQL with sqlglot, the statement list should be required to contain exactly one statement whose root node type is exp.Select, exp.SetOperation or exp.Subquery; otherwise execution is rejected.

Walking the entire syntax tree (using tree.walk()) and checking against a list of forbidden nodes (such as Insert, Update, Delete, Merge, Create, Drop, etc.) is necessary, because data-modifying CTEs and SELECT INTO may be hidden in deep nodes.

Executing the SQL regenerated by tree.sql() rather than the original string guarantees that the audited syntax tree strictly corresponds to the statement actually executed, avoiding differences in how the parser and executor understand it.

The recommended process is to transpile first, and escalate to re-querying the model only when the target engine's execution result is inconsistent with the source.

sqlglot's transpilation process is to read the source dialect and generate an AST, then re-serialize it according to the target dialect's rules.

The guarantee of deterministic transpilation comes from the fact that the mapping from AST to output is purely functional, so the same AST always yields the same text.

MySQL lacks an AST node equivalent to Postgres's jsonb arrow operators, so the transpiler can only choose an approximate substitute representation and silently introduces semantic deviation.

A deterministic transpiler can only perform symbol-level substitutions and cannot be aware of the collation configuration in the schema.

sqlglot's lineage() performs static inference at the syntactic level, and cannot fully reconstruct the real dependencies when encountering SELECT *, dynamic SQL concatenation, or logic inside UDFs.

sqlglot's lineage() API requires schema information to work, and the author obtained the table structure in advance by connecting to Snowflake and executing DESCRIBE TABLE.

跨平台 SQL 兼容通常通过抽象语法树（AST）转换实现，先将语义映射为方言无关的中间表示（IR），再由方言编译器翻译为目标数据库原生 SQL