Skip to main content

Which SQL dialect should I write my models in? Or which SQL dialect does dbt use?

dbt can feel like magic, but it isn't actually magic. Under the hood, it's running SQL in your own warehouse — your data is not processed outside of your warehouse.

As such, your models should just use the SQL dialect of your own database. Then, when dbt wraps your select statements in the appropriate DDL or DML, it will use the correct DML for your warehouse — all of this logic is written in to dbt.

You can find more information about the databases, platforms, and query engines that dbt supports in the Supported Data Platforms docs.

Want to go a little deeper on how this works? Consider a snippet of SQL that works on each warehouse:

models/test_model.sql
select 1 as my_column

To replace an existing table, here's an illustrative example of the SQL dbt will run on different warehouses (the actual SQL can get much more complicated than this!)

-- you can't create or replace on redshift, so use a transaction to do this in an atomic way

begin;

create table "dbt_alice"."test_model__dbt_tmp" as (
    select 1 as my_column
);

alter table "dbt_alice"."test_model" rename to "test_model__dbt_backup";

alter table "dbt_alice"."test_model__dbt_tmp" rename to "test_model"

commit;

begin;

drop table if exists "dbt_alice"."test_model__dbt_backup" cascade;

commit;
0