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 dbt supports in the Supported Databases docs.
Want to go a little deeper on how this works? Consider a snippet of SQL that works on each warehouse:
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 waybegin;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;