Everything is SELECT

This is a killer feature of the Data Build Tool framework. In other words, DBT abstracts all the code related to the materialization of your queries in the Warehouse (variations from the CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, … commands).

Any model involves writing one SELECT-query, which defines the resulting data set.

At the same time, the transformation logic can be multilevel and consolidate data from several other models. An example of a model that will build a showcase of orders (f_orders):

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}
 
with orders as (
 
   select * from {{ ref('stg_orders') }}
 
),
 
order_payments as (
 
   select * from {{ ref('order_payments') }}
 
),
 
final as (
 
   select
       orders.order_id,
       orders.customer_id,
       orders.order_date,
       orders.status,
       {% for payment_method in payment_methods -%}
       order_payments.{{payment_method}}_amount,
       {% endfor -%}
       order_payments.total_amount as amount
   from orders
       left join order_payments using (order_id)
 
)
 
select * from final

What interesting things can we see here?

First: CTE (Common Table Expressions) are used to organize and understand code that contains a lot of transformations and business logic.

Second: Model code is a mixture of SQL and language Jinja (templating language).

The example used a loop for to generate the amount for each payment method specified in the expression set. Also used function ref – the ability to refer inside the code to other models:

• At compile time ref will be converted to a target pointer to a table or view in the Repository
• ref allows you to build a graph of model dependencies

Exactly Jinja Adds almost unlimited possibilities to DBT. The most commonly used ones:

• If / else statements – branching statements
• For loops – loops
• Variables – Variables
• Macro – Create Macros

Materialization: Table, View, Incremental

Materialization Strategy – an approach according to which the resulting set of model data will be stored in the Repository.

In a basic consideration, this is:

• Table – physical table in the Storage
• View – view, virtual table in the Repository

There are more complex materialization strategies:

• Incremental – incremental loading (large fact tables); new lines are added, modified ones are updated, deleted ones are cleared
• Ephemeral – the model does not materialize directly, but participates as CTE in other models
• Any other strategies you can add yourself

In addition to materialization strategies, opportunities are opened up for optimization for specific Warehouses, for example:

• Snowflake: Transient tables, Merge behavior, Table clustering, Copying grants, Secure views
• Redshift: Distkey, Sortkey (interleaved, compound), Late Binding Views
• Bigquery: Table partitioning & clustering, Merge behavior, KMS Encryption, Labels & Tags
• Spark: File format (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy

The following Repositories are currently supported:

• Postgres
• Redshift
• Bigquery
• Snowflake
• Presto (partially)
• Spark (partially)
• Microsoft SQL Server (community adapter)

Let’s refine our model:

• Make its filling incremental (Incremental)
• Add segmentation and sort keys for Redshift

-- Конфигурация модели: 
-- Инкрементальное наполнение, уникальный ключ для обновления записей (unique_key)
-- Ключ сегментации (dist), ключ сортировки (sort)
{{
  config(
       materialized='incremental',
       unique_key='order_id',
       dist="customer_id",
       sort="order_date"
   )
}}
 
{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}
 
with orders as (
 
   select * from {{ ref('stg_orders') }}
   where 1=1
   {% if is_incremental() -%}
       -- Этот фильтр будет применен только для инкрементального запуска
       and order_date >= (select max(order_date) from {{ this }})
   {%- endif %} 
 
),
 
order_payments as (
 
   select * from {{ ref('order_payments') }}
 
),
 
final as (
 
   select
       orders.order_id,
       orders.customer_id,
       orders.order_date,
       orders.status,
       {% for payment_method in payment_methods -%}
       order_payments.{{payment_method}}_amount,
       {% endfor -%}
       order_payments.total_amount as amount
   from orders
       left join order_payments using (order_id)
 
)
 
select * from final

Model Dependency Graph

He is a tree of dependencies. He is a DAG (Directed Acyclic Graph – Directional Acyclic Graph).

DBT builds a graph based on the configuration of all project models, or rather ref () links inside models to other models. Having a graph allows you to do the following things:

• Running models in the correct sequence
• Parallelization of window dressing
• Running an arbitrary subgraph

Graph visualization example: