Creating Modular SQL Models

You will write modular SELECT statements that transform raw data into staging tables, promoting reusability and cleaner downstream logic.

Lesson 318: Creating Modular SQL Models

In this lesson, you will learn how to break complex data transformations into reusable, bite-sized SQL blocks using dbt models.

The Problem with Monolithic SQL

When you first start building pipelines, it is tempting to write one massive SELECT statement that performs every join, filter, and calculation at once. However, as your project grows, this creates a "spaghetti" of code that is impossible to debug and reuse. If three different dashboards need the same cleaned version of your orders table, a monolithic approach forces you to copy-paste that logic three times.

Modularizing with Staging Models

To solve this, we use modular SQL models. We create "staging" models—the first layer of transformation—which act as a single source of truth for your raw data. Instead of joining raw tables directly, you build a model that performs basic cleaning (renaming columns, casting data types, and filtering nulls).

Consider this stg_orders.sql model:

-- models/staging/stg_orders.sql

WITH source AS (
    SELECT * FROM {{ source('raw_data', 'orders') }}
)

SELECT
    id AS order_id,
    customer_id,
    CAST(order_date AS DATE) AS order_date,
    LOWER(status) AS status
FROM source
WHERE order_id IS NOT NULL

Why this works

By isolating this logic into its own model, you create a building block. Now, when you want to build a downstream table like monthly_sales_report.sql, you don’t reference the messy raw source. Instead, you reference your modular model:

-- models/marts/monthly_sales_report.sql

SELECT
    date_trunc('month', order_date) AS order_month,
    count(order_id) AS total_orders
FROM {{ ref('stg_orders') }}
GROUP BY 1

By using the {{ ref() }} function, dbt automatically understands the dependency between these files. If you ever need to change how status is calculated, you only have to update the staging model once, and every downstream report will inherit the change automatically. This is the foundation of maintainable, clean ELT pipelines.

Key Takeaway: Modular SQL models allow you to transform raw data into reusable building blocks, ensuring that logic is defined once and referenced everywhere, which eliminates code duplication and simplifies debugging.