dbt-polyglot

Run dbt models written in one SQL dialect on a warehouse that speaks another — unchanged. Each model's SQL is transpiled with sqlglot at dbt's compile phase, so the SQL dbt actually executes (and what lands in target/compiled/) is already in your target dialect. Your model .sql files are never edited.

You declare two things in config: the dialect your models are written in (transpile_from) and the dialect of the warehouse you run on (transpile_to, default spark). Drop the package into any existing dbt repo, point profiles.yml at your warehouse, add one flag to dbt_project.yml, and dbt build.

Why this exists: dialects diverge. Spark, for example, has no QUALIFY clause ([PARSE_SYNTAX_ERROR] … near 'QUALIFY'), plus dozens of smaller gaps (IFF, NVL, :: casts, DATEADD, null ordering, …) — a Snowflake-style model simply fails there until its SQL is translated. This package does that translation transparently, in-place, at compile time.

Spark and Databricks are first-class targets — they share a correctness fix-up layer (SPARK_FIXUPS) that repairs cases where sqlglot's output is rejected by the real engine, and are the two targets the dbt build --empty validation story is exercised against. Any other sqlglot dialect works as a target too, best-effort.

---

When to use this — and when not to

This is a transpilation layer, not a migration manager. It shines in two situations:

1. Cheap local / CI validation. Point your Snowflake- or Databricks-dialect project at a free local engine (DuckDB) and run dbt build --empty to catch parse/compile errors in CI without paying for warehouse compute. Here the "real" dialect never changes — the warehouse engine stays Snowflake/Databricks; DuckDB is a disposable validator. No drift, no maintenance cost. This is the safest, highest-value use.

2. A temporary migration bridge. Mid-migration (say Snowflake → Databricks), flip execution to the new platform on day one — stop paying the old warehouse — then rewrite models to the new dialect incrementally, at your own pace. The package buys you the cutover without a big-bang rewrite.

When NOT to use it: as a permanent home for hundreds of foreign-dialect models running on a warehouse they were never written for. If you keep 400 Snowflake models running on Databricks forever — and write new models in a mix of dialects with no end state — you inherit a real maintenance problem that no transpiler solves. The bridge is meant to have a far end. Treat a large, never-shrinking transpiled surface as a smell, not a strategy.

Tracking which model is in which dialect is dbt-native — no bespoke tooling needed. transpile_from is ordinary dbt config, so it's greppable and shows up in the manifest:

# Inventory every model's declared source dialect:
dbt list --output json | jq '.[] | {name, from: .config.transpile_from, to: .config.transpile_to}'

Set transpile_from once at the project level in dbt_project.yml for a single source of truth; a new model written natively just omits it (a no-op — see the No-op guarantee).

---

Install

It is a normal Python package — install it into the same virtualenv your dbt runs in. Installation auto-activates the patch (via a .pth file that imports the module on interpreter start-up; see Installation: why pip, not dbt deps).

pip install dbt-polyglot

From a git checkout (bleeding edge):

pip install "git+https://github.com/Saketkr21/dbt-polyglot.git"

Local / editable (developing the package):

pip install -e path/to/dbt-polyglot

You also need a Spark adapter for dbt (this package does not pull one in, so you can choose your connection method):

pip install "dbt-spark[PyHive]"     # Thrift/HiveServer2, used in the examples below

---

Configure (the only changes you make)

1. profiles.yml — point the output at your warehouse (Spark shown)

your_profile:
  target: dev
  outputs:
    dev:
      type: spark
      method: thrift
      host: "{{ env_var('DBT_SPARK_HOST', 'localhost') }}"
      port: "{{ env_var('DBT_SPARK_PORT', 10000) | int }}"
      schema: analytics

2. dbt_project.yml — declare the source dialect (and target, if not Spark)

models:
  your_project:
    +transpile_from: snowflake     # the dialect your models are WRITTEN in
    # +transpile_to: spark         # your WAREHOUSE's dialect (default: spark)

Both accept any dialect sqlglot understands — snowflake, bigquery, redshift, tsql, postgres, duckdb, databricks, presto, trino, … transpile_to defaults to spark; set it to match the warehouse profiles.yml connects to. It must agree with your dbt adapter, since dbt executes the transpiled SQL there.

You can scope it to a subtree (models.your_project.staging.+transpile_from: …) or override it per model — a per-model config beats the project default:

-- models/marts/latest_order.sql  (written in Snowflake SQL, runs on Spark)
{{ config(materialized='table', transpile_from='snowflake') }}

select *
from {{ ref('orders') }}
qualify row_number() over (partition by customer_id order by ordered_at desc) = 1

That's it. dbt build now runs your existing models on Spark, no model edits.

---

How it works

At dbt compile, the package wraps dbt.compilation.Compiler._compile_code and runs an extra step on each opted-in model's compiled SQL body:

parse(read=transpile_from)  →  apply fix-ups (spark + databricks targets)  →  generate(transpile_to, pretty=True)

Because the rewrite happens on the model body before dbt wraps it in the materialization DDL (create table … as …), both target/compiled/ and the SQL sent to the warehouse are already in the target dialect — there is no mixed-dialect string and no separate output directory.

The fix-up layer (what makes it trustable)

sqlglot's output is occasionally valid in its model of Spark/Databricks but rejected by the real engine's parser or planner. Two examples currently shipped:

• Quantified subquery comparisons. sqlglot's Snowflake reader canonicalises x NOT IN (subq) into the unsupported x <> ALL (subq) (and x IN → x = ANY). Neither Spark nor Databricks accepts those. fixup_quantified_subquery rewrites them back to NOT x IN / x IN.
• DuckDB list_element on Spark/Databricks. sqlglot emits DuckDB's list_element(arr, i) verbatim in Spark/Databricks output, which Databricks rejects with [UNRESOLVED_ROUTINE] Cannot resolve routine LIST_ELEMENT. fixup_list_element_to_element_at swaps the name for the Spark-native element_at(arr, i) (identical arg shape).

The SPARK_FIXUPS registry is a list of small AST transforms applied to the parsed tree before generation, and it applies to both the spark and databricks targets (Databricks inherits the same class of gaps). Extensible — one EXPLAIN-verified transform per gap.

Targets

At the engine level the transpile is N×N — any sqlglot source dialect to any target, chosen by transpile_from / transpile_to. In practice there's a maturity gradient:

• Spark + Databricks — first-class. SPARK_FIXUPS runs when transpile_to in {spark, databricks}, and these are the two targets the fix-ups + dbt build --empty validation story have been exercised against end-to-end. Production-trustworthy.
• DuckDB, Snowflake, BigQuery, Postgres, … — best-effort. You get raw sqlglot output plus the always-on cross-cutting layers (target-attribute shim, identifier-quoting normalizer). Often correct, but sqlglot can still emit constructs the real engine rejects, with nothing to catch them. When it does, the fail-soft path emits a WARNING and passes the original SQL through unchanged, so the real engine's error surfaces (never silently wrong).

Promoting another target to first-class is a bounded extension: add a <TARGET>_FIXUPS registry beside SPARK_FIXUPS and key fix-up selection on transpile_to. Either way, transpile_to must match your dbt adapter — dbt runs the output against that warehouse.

Trust model — verified, or fails loud (never silently wrong)

A model is either converted to valid target-dialect SQL or it fails loudly with a clear dbt / warehouse error naming the model. It never silently emits a wrong result from an un-converted construct:

• Fail-soft + loud. If sqlglot can't parse the SQL as the source dialect, or produces empty/multi-statement output, the patch logs a WARNING (visible in the dbt run) and passes the original SQL through unchanged. The warehouse then either runs it (it was already valid) or rejects it loudly — so the failure surfaces, it is never hidden.

To certify a whole repo upfront — before a heavy run — use dbt's own native validation. No extra tooling: dbt already runs SQL through your profiles.yml adapter, against whatever warehouse you target.

dbt build --empty              # build every model with 0 input rows (DAG-ordered)
dbt build --empty --select marts.*   # any dbt selector works
dbt show --limit 0 -s my_model # read-only: validate the SELECT without materializing

--empty limits every ref/source to zero rows, so dbt executes each model's real SQL against the warehouse — moving no data — and fails loudly, naming the model, if the transpiled SQL is invalid. Because it builds in dependency order, there is no "upstream not built" ambiguity. That makes dbt build --empty a drop-in CI gate (it exits non-zero on the first invalid model). dbt show --limit 0 is the non-destructive variant when the target role can't create objects.

Scope — what it fixes, and what it doesn't

A dbt project leaks its warehouse's dialect into several distinct surfaces, not just the SQL. This package fixes the ones where a mismatch causes a hard, cheaply-and-safely-patchable failure, and deliberately leaves the rest to either fail loud or to dbt's own tolerance. The guiding rule: close the gaps that crash; never paper over a semantic difference silently.

What it fixes

Surface	Example gap	Status	Layer
SQL syntax & functions	IFF, NVL, :: casts, DATEADD, QUALIFY	✅ Always-on	SQL transpile (sqlglot)
sqlglot output the real engine rejects	x <> ALL (subq), LIST_ELEMENT on Spark/Databricks	✅ Always-on	SPARK_FIXUPS (spark, databricks)
Identifier quoting in resolved refs	`cat`.`sch`.`t` fed to a non-backtick source parser	✅ Always-on	pre-parse normalizer
Jinja target.* attributes	target.catalog / warehouse / role / http_path / account missing on the adapter	✅ Always-on	target shim (Phase 1)
Adapter-only materializations	streaming_table, dynamic_table, iceberg, materialized_view not found on target	⚗️ Experimental	config shim (Phase 2)
sources.yml catalog: key	Databricks catalog: unknown to other adapters	⚗️ Experimental	manifest shim (Phase 3)
Contract data_type names	VARIANT / OBJECT / bare NUMBER rejected cross-adapter	⚗️ Experimental	manifest shim (Phase 3)

Always-on = safe, additive, on by default. Experimental = can change semantics; opt in with vars: dbt_polyglot_experimental: true.

What it does NOT fix (by design)

Surface	Example	What happens	Why it's not ours
Untranslatable SQL	Snowflake LATERAL FLATTEN, VARIANT/OBJECT ops, LISTAGG, : path access	Fails loud — WARNING + original SQL passed through → engine rejects it, naming the model	sqlglot has no faithful mapping; a silent guess would risk wrong results
Unknown model config keys	+file_format, +tblproperties, +snowflake_warehouse, +cluster_by, +query_tag	Silently ignored by dbt-core (unknown config → _extra, read only by adapters that know it) — no crash, nothing to fix	dbt already tolerates these; a materialization that needs the key simply doesn't read it on the other engine
Relation namespacing level	Snowflake 3-level db.schema.table on dbt-spark, which drops the database component (2-level Hive) unless a 3-level catalog (Unity/Nessie/Polaris) is configured	Resolves to schema.table; fails loud at run if that relation doesn't exist (only silently wrong on a cross-catalog name collision)	This is the destination adapter's namespacing model + your catalog config, not a SQL-dialect fact the package can know. Fix it in the adapter/profile (see note below)
Incremental strategies	merge vs delete+insert vs append vs microbatch differences	Fails loud if the strategy's macro doesn't exist on the target	Behavioural, not syntactic — belongs to the adapter's materialization
Deep semantic differences	transactionality, type precision (NUMBER(38,0) vs DECIMAL), timezone semantics	Not reconciled (sqlglot handles some, e.g. null ordering — see below)	Correctness here needs domain judgment, not mechanical translation
Adapter-specific SQL in your macros	a macro that emits IFF(...) directly, or branches on target.type	Untouched — only model SQL bodies are transpiled	Use adapter.dispatch / guard the macro; that's the dbt-native pattern

On namespacing (the database-dropped-on-Spark case) specifically: whether a Spark target supports three-level names depends on its catalog (plain Hive = two-level; Unity Catalog / Iceberg REST like Nessie or Polaris = three-level) — a property of the destination platform's configuration, not of the source SQL dialect. The package can't safely infer it, and forcing a database component into the relation would emit invalid SQL on a two-level target. So it's the adapter's/profile's job. Phase 3's catalog:→database: alias only reconciles the YAML key name; it does not change how the destination adapter renders the relation. (A future, in-character enhancement could detect and warn on a namespacing mismatch — loud, not a silent fix — but that's not in this release.)

Selecting what gets transpiled: scope +transpile_from to a folder/model subtree (or set it per model) — the dbt-native way — rather than a global on/off.

Cross-adapter target attributes (target.catalog, target.warehouse, …)

SQL isn't the only thing that leaks a warehouse's dialect into a dbt project. Jinja does too: a project written for Databricks references target.catalog in its macros, a Snowflake-first project references target.warehouse / target.role, and none of those attributes exist on adapters like DuckDB — so the project fails at compile time with

Compilation Error … 'dict object' has no attribute 'catalog'

before dbt-polyglot's SQL transpile ever gets a chance to run.

The target shim closes that gap. On every profile load, it fills in a small, well-known set of adapter-specific Jinja target.* attributes with sensible defaults — never overwriting anything the running adapter already provides:

Attribute	Shape	Aliased from (first truthy wins)	Fallback
catalog	Databricks (Unity Catalog)	database	""
project	BigQuery	database	""
dataset	BigQuery	schema	""
warehouse	Snowflake	—	""
role	Snowflake	—	""
account	Snowflake	—	""
query_tag	Snowflake	—	""
http_path	Databricks (SQL warehouse)	—	""
location_root	Spark / Databricks (file layout)	—	""
location	BigQuery / Redshift region	—	""
region	BigQuery / Redshift	—	""

So a model or macro that writes {{ target.catalog }}.{{ target.schema }}.orders compiles on any target without touching a single project file.

The shim is always on. It's additive-only — it never overwrites values the adapter already provides, so a real Databricks target keeps its native catalog; a DuckDB target gets one aliased from database; both work.

Extending the registry. Add rows to dbt_polyglot.target_shim.TARGET_ATTRIBUTES — one per gap, no (from, to) pair matrix:

from dbt_polyglot.target_shim import TARGET_ATTRIBUTES
TARGET_ATTRIBUTES["tenant"] = {"aliases": ["database"], "fallback": ""}

Experimental: config + manifest shims (opt-in)

These two layers are OFF by default and gated behind one project var. They can silently change semantics — the exact thing the always-on layers refuse to do — so they're opt-in. Turn them on only when you understand the trade-off:

# dbt_project.yml
vars:
  dbt_polyglot_experimental: true

With the flag unset, neither of the following runs — the package behaves as a pure SQL-body transpiler plus the (safe, always-on) target-attribute shim.

Phase 2 — materialization coercion. Some materializations are adapter-specific: streaming_table (Databricks DLT), dynamic_table (Snowflake), materialized_view (divergent semantics across engines). Running such a model on a warehouse that lacks it makes dbt-core throw materialization '…' was not found for adapter …. When enabled and a cross-adapter transpile is active (transpile_from ≠ transpile_to), the compile hook downgrades the materialization to a portable fallback before dbt dispatches it:

Original	Fallback
streaming_table	view
dynamic_table	view
iceberg	table
materialized_view	view

Why it's experimental: a streaming/dynamic table and a view are different objects with different refresh semantics. On a validation run (--empty) that difference is irrelevant; anywhere else it's a real behavioural change the package would be making silently. Registry: dbt_polyglot.config_shim.MATERIALIZATION_FALLBACKS. Never touches a native run (src == dst).

Phase 3 — source-key + column-type normalization. Two rewrites at manifest load:

• Source catalog: → database:. dbt-databricks projects declare Unity Catalog sources with catalog:; dbt-core's canonical key is database:. When a source has only catalog:, it's aliased into database: so {{ source() }} resolves on non-Databricks adapters. Populated database: values are never overwritten.
• Column data_type fallbacks (VARIANT/OBJECT → STRING, bare NUMBER → DECIMAL) so contract-mode models don't reject the type name cross-adapter. Registry: dbt_polyglot.manifest_shim.COLUMN_TYPE_FALLBACKS. Only bare type names match — NUMBER(38, 4) passes through untouched.

Why it's experimental: coercing a contract's declared VARIANT to STRING can let a model pass a contract check the real platform would fail — again, a silent semantic change. Opt in deliberately.

Silencing the per-model INFO log

The compile hook prints one line per successfully transpiled model:

[info ] Thread-3: dbt-polyglot adapter: [ dbt-polyglot ] transpiled model.my_project.orders (snowflake -> spark)

That line is useful when you're first onboarding a project but can become noise on large runs. Silence it by adding one line to your project's dbt_project.yml:

vars:
  dbt_polyglot_quiet: true

Or per-run via CLI:

dbt run --vars '{dbt_polyglot_quiet: true}'

WARNING lines from fail-soft (when sqlglot can't parse or generate) always fire — you can't hide a broken transpile.

No-op guarantee

If transpile_from is unset, or equals transpile_to (you're already writing SQL in the target dialect), the model is never touched — sqlglot is not even called and nothing is reformatted.

A note on NULLS LAST in the output (intentional)

Snowflake and Spark have opposite default null ordering (Snowflake sorts NULLs largest → last; Spark sorts them smallest → first). When translating a Snowflake ORDER BY x, sqlglot appends an explicit … NULLS LAST to preserve Snowflake semantics — without it, a QUALIFY ROW_NUMBER() … = 1 top-N pick could choose a different row. It is added only on a true cross-dialect translation, and is semantically required — do not strip it.

---

Installation: why pip, not dbt deps

dbt deps cannot install this — you must pip install it. They do different things:

• dbt deps installs dbt packages: bundles of dbt macros, models, seeds, and tests (the things listed in packages.yml / dependencies.yml). It pulls SQL/Jinja assets into dbt_packages/ and never installs or runs Python code.
• dbt-polyglot is a Python package. It works by monkeypatching a dbt-core function at runtime, and it activates through a .pth file that Python executes on interpreter start-up. Both of those are Python-installer concerns — only pip (or uv, poetry, etc.) places a .pth into site-packages and registers the dependency.

So it is installed exactly like dbt-core or an adapter, into the same environment as your dbt. It does not appear in packages.yml.

---

Package contents

A standard src-layout package — src/dbt_polyglot/ holds the import package, plus a .pth that activates it on start-up:

File	Role
src/dbt_polyglot/__init__.py	Import-time activation — registers all three dbt-core patches (patch_compiler, patch_profile_target_dict, patch_manifest_loader), each import-guarded.
src/dbt_polyglot/transpile.py	Compile-phase patch (patch_compiler) + spark_safe_transpile (parse → fix-ups → generate). Reads dbt_polyglot_quiet / dbt_polyglot_experimental project vars via flags.
src/dbt_polyglot/fixups.py	SPARK_FIXUPS registry of AST transforms — applies to spark + databricks targets.
src/dbt_polyglot/target_shim.py	Phase 1 — always-on Jinja target.* attribute shim + TARGET_ATTRIBUTES registry (catalog, project, dataset, warehouse, role, http_path, …).
src/dbt_polyglot/config_shim.py	Phase 2 (experimental) — materialization coercion + MATERIALIZATION_FALLBACKS registry.
src/dbt_polyglot/manifest_shim.py	Phase 3 (experimental) — source catalog: → database: aliasing + column-type fallbacks. Patches ManifestLoader.load.
src/dbt_polyglot/flags.py	Shared reader for dbt_polyglot_* project vars (node-scoped and global).
dbt_polyglot.pth	One line (import dbt_polyglot); auto-activates on Python startup. Placed into site-packages by three setup.py hooks — build_py (wheel), develop (legacy editable), and editable_wheel (PEP 660).
pyproject.toml / setup.py	PEP 517 metadata + the three .pth-placement hooks.
LICENSE	Apache-2.0.

This package is intentionally scoped to compile-time translation and cross-adapter compatibility shims. Validating the result is left to dbt's native dbt build --empty (see Trust model above); data placement (e.g. ATTACHing a DuckDB catalog to mirror a Databricks catalog), incremental strategy translation, and semantic reconciliation across engines are separate concerns and are not bundled here — the Scope matrix documents each non-goal explicitly.

---

Compatibility & caveats

• dbt-core private method. The patch wraps dbt.compilation.Compiler._compile_code, a private dbt-core method. It forwards *args/**kwargs to tolerate signature drift and is fully import-guarded (if dbt-core or sqlglot aren't importable, or the seam moves, the patch does nothing rather than breaking the interpreter). Still, pin a supported dbt-core range when depending on this in production, and re-verify after major dbt upgrades.
• sqlglot coverage. sqlglot maps a large surface but not everything. Exotic dialect features — Snowflake LATERAL FLATTEN, VARIANT/OBJECT/ARRAY semantics, : path access, LISTAGG, and similar — may not translate cleanly. Those surface via the fail-soft WARNING and dbt build --empty, by design, rather than silently.
• Self-contained. The module imports nothing from any host project, so it can be lifted into its own repo unchanged.

License

Apache-2.0 — see LICENSE.