SQL Dialects and Lexers
Relevant source files
The following files were used as context for generating this wiki page:
SQL Dialects and Lexers form the foundation of dbt-core's ability to provide cross-platform compatibility and semantic query compilation. The system is designed to handle variations in SQL syntax across major data warehouses while maintaining a unified internal representation for dbt resources. This module ensures that Jinja macros, semantic models, and generated queries are correctly translated into the specific requirements of target adapters.
The architecture relies on adapter-specific implementations that manage quoting policies, reserved keywords, and specialized DDL/DML syntax. By centralizing these dialect definitions, dbt-core can support advanced features like semantic layer compilation through MetricFlow and automated relation resolution across heterogeneous environments.
Supported SQL Dialects
The project explicitly supports several major SQL dialects, each mapped to a specific adapter type. These dialects are primarily used during semantic query compilation and relation rendering.
| Dialect | Implementation Context | Key Characteristics |
|---|---|---|
| DuckDB | Local development / Testing | Default in-memory engine; supports standard SQL and generate_series. |
| Snowflake | Cloud Data Warehouse | Uses FLOAT instead of DOUBLE; specialized GENERATOR syntax. |
| BigQuery | Cloud Data Warehouse | Uses FLOAT64, STRING, and INT64; specific PROJECT.DATASET.TABLE hierarchy. |
| Redshift | Cloud Data Warehouse | Specific quoting requirements for identifiers; uses DOUBLE PRECISION. |
| Databricks | Unified Analytics | Spark-based; utilizes EXPLODE and specific DDL for streaming tables. |
Sources: crates/dbt-metricflow/tests/metricflow_compat.rs:885-940, crates/dbt-metricflow/tests/common/mod.rs:596-670
Dialect-Specific Lexing and Quoting
Lexing behavior in dbt is heavily influenced by the DbtQuoting and ResolvedQuoting structures. These define whether database, schema, and identifier components should be enclosed in quotes during SQL generation.
Quoting Logic
Each adapter provides a default quoting policy that can be overridden by user configuration. For instance, Snowflake often defaults to upper-case identifiers, while ClickHouse may normalize database names differently.
This diagram illustrates the flow of a raw identifier through adapter-specific quoting logic to produce a final resolved relation string.
Sources: crates/dbt-adapter/src/relation/relation_object.rs:528-560, crates/dbt-parser/src/resolver.rs:135-140
Keyword Management
The dbt-sql-keywords crate provides specialized handling for reserved words that must be protected during lexing to avoid syntax errors in target dialects. This prevents collisions between dbt resource names and SQL reserved keywords like GROUP, ORDER, or LIMIT.
Sources: crates/dbt-sql-keywords/src/lib.rs:1-10
Semantic Query Translation
The semantic layer utilizes Dialect enums to drive the compilation of MetricFlow queries into executable SQL. This process involves rewriting generic query specs into dialect-specific syntax.
Translation Patterns
- Type Mapping: Translating
DOUBLE(DuckDB) toFLOAT(Snowflake) orFLOAT64(BigQuery). - Function Mapping: Converting
GEN_RANDOM_UUID()toUUID_STRING()for Snowflake compatibility. - Interval Rewriting: Converting standard intervals into adapter-specific formats, such as
DATEADDcalls in Snowflake.
The sequence above shows how the compiler interacts with dialect handlers to transform a semantic query into adapter-specific SQL.
Sources: crates/dbt-metricflow/tests/common/mod.rs:777-850, crates/dbt-metricflow/tests/metricflow_compat.rs:885-920
Relation Resolution and DDL Generation
SQL lexing and dialect management are critical for DDL (Data Definition Language) generation, particularly for CREATE TABLE AS (CTAS) operations and view definitions.
Adapter Macros
Specialized Jinja macros handle the construction of DDL. For BigQuery, this includes handling partitioning and clustering clauses that are unique to its dialect.
{% macro bigquery__create_table_as(temporary, relation, compiled_code, language='sql') -%}
{%- if language == 'sql' -%}
create or replace table {{ relation }}
{{ partition_by(partition_config) }}
{{ cluster_by(raw_cluster_by) }}
as (
{{ compiled_code }}
);
{%- endif -%}
{%- endmacro -%}
Sources: crates/dbt-loader/src/dbt_macro_assets/dbt-bigquery/macros/adapters.sql:1-40
Relation Object Interface
The RelationObject structure exposes methods to Jinja that allow for dynamic path replacement and quoting adjustments based on the active dialect. It supports methods like quote(), include(), and without_identifier() to manipulate SQL identifiers safely.
Sources: crates/dbt-adapter/src/relation/relation_object.rs:150-250
Summary of Dialect Characteristics
| Feature | DuckDB | Snowflake | BigQuery |
|---|---|---|---|
| Identifier Quoting | " | " (Auto-uppercase) | ` (Project.Dataset.Table) |
| Boolean Values | TRUE/FALSE | TRUE/FALSE | BOOL type |
| Timestamps | TIMESTAMP | TIMESTAMP_NTZ | TIMESTAMP |
| Series Generation | generate_series | GENERATOR | GENERATE_DATE_ARRAY |
Sources: crates/dbt-metricflow/tests/common/mod.rs:610-660, crates/dbt-metricflow/tests/metricflow_compat.rs:900-930
SQL Dialects and Lexers enable dbt-core to act as a bridge between high-level transformation logic and the low-level syntactic requirements of diverse database engines. Through robust quoting policies, reserved keyword protection, and semantic translation, the system ensures that queries remain performant and syntactically correct across all supported adapters.