Skip to main content

02. Writing Modular SQL

The Magic of Modular SQL​

Legacy SQL scripts are notoriously massive: 1,000-line behemoths filled with nested subqueries that are impossible to debug.

DBT solves this by allowing you to write Modular SQL. You write small, bite-sized SQL files and stack them together using the {{ ref() }} function.


1. The ref Function​

The ref() function (short for reference) is the single most important function in DBT. It allows one model to query the output of another model without hardcoding database or schema names.

** The Anti-Pattern: Hardcoding**​

-- This breaks if you deploy to a 'production' schema instead of 'dev'
SELECT * FROM dev_alice.stg_orders

** The DBT Way: Using ref()**​

-- This automatically resolves to the correct schema based on your profiles.yml
SELECT * FROM {{ ref('stg_orders') }}

️ 2. Building a Directed Acyclic Graph (DAG)​

Because you use {{ ref() }} instead of hardcoded table names, DBT can instantly map out the exact relationships between all your files. This dependency mapping is called a DAG (Directed Acyclic Graph).

Because DBT understands this DAG, if you run dbt run, DBT knows exactly what order to build the tables in. It will naturally build stg_users and stg_orders before trying to build int_user_behavior.


3. Jinja Templating (Variables & Loops)​

Because DBT models are technically Jinja templates, you can use Python-like logic directly inside your SQL files to make them dynamic.

Example: Using variables to avoid hardcoded dates

{% set recent_days = 30 %}

SELECT *
FROM {{ ref('fct_orders') }}
WHERE order_date >= current_date - interval '{{ recent_days }}' day

Summary​

Using {{ ref() }} and Jinja completely transforms SQL from a static string of text into a dynamic, compiled programming language. It ensures your logic is DRY (Don't Repeat Yourself) and your dependency chains are flawless.