SQLGlot — SQL Parser, Transpiler & Optimizer in Pure Python

Introduction

SQLGlot solves the universal pain of SQL dialect differences. When migrating between data platforms or building tools that must understand SQL, hand-translating queries is error-prone and slow. SQLGlot parses SQL into an AST, rewrites dialect-specific syntax, and outputs clean SQL for the target engine — all in pure Python with zero native dependencies.

What SQLGlot Does

• Parses SQL from 20+ dialects into a typed abstract syntax tree
• Transpiles queries between dialects (Snowflake to BigQuery, Spark to DuckDB, etc.)
• Optimizes queries by simplifying expressions, pushing down predicates, and eliminating subqueries
• Formats SQL with consistent style and indentation
• Provides a Python API for programmatic SQL generation and transformation

Architecture Overview

SQLGlot's core is a recursive-descent parser that produces a tree of expression nodes. Each SQL dialect is defined as a subclass that overrides tokenization and generation rules. The optimizer applies relational algebra transformations (predicate pushdown, column pruning, join reordering) on the AST. The generator walks the tree and emits dialect-specific SQL. Everything is pure Python with no C extensions or external dependencies.

Self-Hosting & Configuration

• Install with pip install sqlglot — no system dependencies required
• Use sqlglot.transpile(sql, read=source, write=target) for quick conversions
• Parse SQL into an AST with sqlglot.parse_one() for programmatic manipulation
• Apply the optimizer with sqlglot.optimizer.optimize() for query simplification
• Extend with custom dialects by subclassing the Dialect class

Key Features

• Supports 20+ SQL dialects including Snowflake, BigQuery, Spark, Presto, DuckDB, MySQL, and Postgres
• Zero dependencies — pure Python, easy to embed anywhere
• Full AST with typed expression nodes for safe programmatic SQL manipulation
• Query optimizer with predicate pushdown, constant folding, and scope analysis
• Semantic diff tool for comparing SQL queries structurally

Comparison with Similar Tools

• sqlparse — tokenizer and formatter only; SQLGlot builds a full typed AST and transpiles
• sqlfluff — SQL linter focused on style; SQLGlot is a parser, transpiler, and optimizer
• Apache Calcite — JVM-based SQL framework; SQLGlot is pure Python and lighter for scripting
• jOOQ — Java SQL builder for runtime query generation; SQLGlot is a static analysis and transpilation tool
• dbt — SQL templating for warehouses; SQLGlot operates on raw SQL syntax without execution

FAQ

Q: How accurate is the transpilation? A: SQLGlot handles the vast majority of common syntax differences. Edge cases in proprietary functions may need custom mappings, but the dialect system makes extending straightforward.

Q: Can SQLGlot execute SQL? A: SQLGlot includes an in-memory executor for testing, but it is primarily a parsing and transpilation library, not a database engine.

Q: Is it fast enough for production use? A: Yes. SQLGlot parses thousands of queries per second in pure Python. Tools like Tobiko's SQLMesh and multiple data catalogs use it in production.

Q: Does it support stored procedures or DDL? A: SQLGlot supports DDL (CREATE, ALTER, DROP) and many procedural extensions. Coverage varies by dialect.

Sources

• https://github.com/tobymao/sqlglot
• https://sqlglot.com/sqlglot.html