SQL Dialect Divergences Across Warehouses

SQL dialects share SELECT, WHERE, and GROUP BY syntax, but diverge in three categories that matter for macros that generate SQL dynamically: date operations, type casting, and array handling. Understanding these divergences is relevant even for single-database projects, since it makes explicit what is idiosyncratic about a given warehouse’s SQL.

Date and Time Functions: Argument Ordering

Date functions are a common source of cross-database issues. The core difference is argument order: BigQuery puts the datepart at the end; Snowflake and Databricks put it first.

BigQuery also uses the INTERVAL keyword where other warehouses do not. For date truncation, Snowflake and Databricks agree on argument order but differ from BigQuery.

A Jinja template that concatenates DATE_ADD with BigQuery-correct arguments produces invalid SQL on Snowflake. The solution is the dispatch pattern or dbt’s built-in cross-database macros, not string manipulation.

Type Casting and Safe Casting

Regular CAST throws an error when a value can’t be converted. Safe casting returns NULL instead, which is almost always what you want for analytics pipelines where dirty data shouldn’t halt the entire DAG.

Beyond function names, the type names themselves vary. What BigQuery calls STRING, Snowflake calls VARCHAR. Length specifications differ. INT64 vs INTEGER vs BIGINT. These differences are invisible when you write SQL for a single warehouse but become blockers for portable macros.

dbt addresses both issues with dbt.safe_cast() and api.Column.translate_type():

{{ dbt.safe_cast("user_input", api.Column.translate_type("string")) }}

The translate_type() function converts generic type names to the adapter-specific equivalent. Use it whenever you’re generating DDL or type-aware SQL. For quick access to common types, dbt.type_string(), dbt.type_int(), and dbt.type_timestamp() return the correct type for your current adapter:

CAST({{ column }} AS {{ dbt.type_string() }})

Why This Matters on a Single Database

Even if you only use BigQuery today, understanding dialect divergences pays off in three ways.

Migration readiness. Companies change databases. An acquisition brings Snowflake into a BigQuery shop. A cost optimization initiative moves workloads to Databricks. Code that’s already portable survives these transitions.

Package compatibility. If you publish dbt macros as a package, they need to work across warehouses. Even if you don’t plan to publish, understanding divergences helps you evaluate and debug third-party packages.

Documentation through code. A dispatch macro with adapter-specific implementations makes explicit what’s unique about each database’s SQL dialect. The BigQuery implementation of my_dateadd documents that BigQuery uses INTERVAL syntax. That’s knowledge embedded in runnable, testable code rather than a wiki page.

The Hierarchy of Solutions

When you hit a cross-database syntax difference, work through these options in order:

1. Use dbt built-in macros — dbt.dateadd(), dbt.safe_cast(), dbt.concat() handle the most common divergences automatically.
2. Write a dispatch macro — for anything the built-ins don’t cover (array operations, JSON parsing, database-specific optimizations), the dispatch pattern is the standard approach.
3. Accept the limitation — some database features have no cross-database equivalent. BigQuery’s APPROX_COUNT_DISTINCT with HyperLogLog is uniquely powerful. Trying to abstract away a feature that only one database has well leads to lowest-common-denominator code.

Array operations deserve special attention because they fall squarely in category 2 — no built-in macros exist, and the syntax diverges completely across platforms. See dbt Cross-Database Array Operations for the full treatment.