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 DDLData Definition Language (DDL) is a group of SQL statements that you can execute to manage database objects, including tables, views, and more. or DMLData Manipulation Language (DML) is a class of SQL statements that are used to query, edit, add and delete row-level data from database tables or views. The main DML statements are SELECT, INSERT, DELETE, and UPDATE., 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 tableIn simplest terms, a table is the direct storage of data in rows and columns. Think excel sheet with raw values in each of the cells., 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