How to Structure DBT Models for Complex Data Pipelines

Introduction

We all know of a metaphor called spaghetti programming and how it imposes a huge panic for us for maintenance and testing. Imagine there is a bug in your code and you have spent hours and days and still not sure how to fix it. With spaghetti thinking you should just go through the whole code and try all the streams to see which one is the cause. And after finding the source of error, your panic has just begun. You should fix it by changing a lot of code because your software has been designed in a spaghetti form. And it (spaghetti programming) does not allow us to think clearly and simply for software design.

Clarity and simplicity are two essential key factors of design and development in any sector of production, particularly in software engineering. This has been achieved in software programming but NOT in database design since years ago. In software, we can think of a complex problem as various sub-problems or smaller problems. This would be the first stage in systematic thinking where we can walk through our clarity and simplicity direction.

After knowing the sub-problems, the next stage would be how to design a software component to resolve each of the problems. A huge work and research have been conducted in the last decades in this direction to achieve this methodology which is called modular programming or procedural programming. This has resulted in a much better way in design and coding irrespective of the syntax or compile, it could be JAVA, C, C++, Python, PERL, etc. All these programming languages have invested a lot in functions and methods. BUT what about SQL?

Methodology

The modularity in all the mentioned compilers is simple: just detect the strings of input-output inside a problem and define a function for it. This works best and is the basis of procedural programming. So how can we achieve this in a database programming language such as SQL?

Answer: Think as atomic as you can and try to define integrations as the JOIN operations we use in SQL. The following shows an example of how to define and structure a DBT for a shopping situation. We have three layers in DBT design: Staging (to prepare data from a single table), Intermediate models (as a transformation layer for joins), and Final model(s) (integration of the previous layers for final output).

Here is a shopping example to demonstrate the three layers of Data Build Tool (DBT) structuring — starting with the staging layer, which prepares raw data from individual source tables:

select
    order_id,
    customer_id,
    order_date,
    status
from {{ source('shop', 'orders') }}

select
    order_item_id,
    order_id,
    product_id,
    quantity,
    unit_price
from {{ source('shop', 'order_items') }}

select
    product_id,
    product_name,
    category,
    supplier_id,
    unit_cost
from {{ source('shop', 'products') }}

select
    customer_id,
    first_name,
    last_name,
    email,
    signup_date
from {{ source('shop', 'customers') }}

select
    review_id,
    product_id,
    customer_id,
    rating,
    review_date
from {{ source('shop', 'product_reviews') }}

select
    supplier_id,
    supplier_name,
    country
from {{ source('shop', 'suppliers') }}

Intermediate and Final Layers

Once we have all the data ready from the staging layer, we can now transform them all together with SQL operations to make it closer to the final result. Filtering, grouping, summing, averaging, and using conditional statements are the SQL operations we use in this layer.

int_order_details.sql combines orders and order items for detailed insights, int_sales.sql creates a sales fact table aggregating sales data, and int_product_reviews.sql aggregates product review scores:

select
    o.order_id,
    o.customer_id,
    o.order_date,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price as line_total
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} oi
    on o.order_id = oi.order_id

select
    product_id,
    date_trunc('month', order_date) as sales_month,
    sum(line_total) as total_sales,
    sum(quantity) as units_sold
from {{ ref('int_order_details') }}
group by 1, 2

select
    product_id,
    count(review_id) as review_count,
    avg(rating) as avg_rating
from {{ ref('stg_product_review') }}
group by 1

Final Integration

In this stage, we integrate the intermediate and staging models as designed to provide data close to the final output. fin_customer_analytics integrates the staging and intermediate models to produce the final results:

select
    c.customer_id,
    c.first_name,
    c.last_name,
    count(distinct od.order_id) as total_orders,
    sum(od.line_total) as lifetime_value,
    avg(pr.avg_rating) as avg_rated_product_quality
from {{ ref('stg_customer') }} c
left join {{ ref('int_order_details') }} od
    on c.customer_id = od.customer_id
left join {{ ref('int_product_reviews') }} pr
    on od.product_id = pr.product_id
group by 1, 2, 3

Reading the DBT Lineage

A DBT diagram (the DAG dbt generates from your `ref()` calls) shows this exact flow as a graph: the six staging models sit on the left, each intermediate model draws lines back to the staging models it joins, and fin_customer_analytics sits on the right, pulling from stg_customer, int_order_details, and int_product_reviews. Nothing in the diagram skips a layer — that is what makes the pipeline traceable when something breaks downstream.

In more complex cases, we need to think about how to break down the situation into more models and DBT models. We might need to define more staging models, perform additional transformations, integrate more DBT models, but we should never try to cope with the complexity using a single complex SQL query.

Conclusion

DBT is indeed a key technology in bringing modularity and reusability to SQL programming. The DBT platform allows for the definition and design of models and modules that are atomic and can be reused in many use cases. The join operation serves as the main connection point of DBT models and is better utilized in the intermediate or final stages. A DBT diagram provides another view of how different DBT models collaborate for a single result, which is a beneficial aspect of database engineering.