Migrating Legacy SQL Scripts to dbt Models

In this lesson, you will learn how to break down monolithic legacy SQL scripts into modular, maintainable dbt models using the select and from syntax.

The Problem with Monoliths

Many data teams inherit "mega-scripts"—SQL files spanning hundreds or thousands of lines, filled with nested subqueries, temporary tables, and complex logic that is impossible to debug. Migrating these to dbt isn't just about moving code; it’s about decomposing that logic into a "layered" architecture.

The Strategy: Modular Decomposition

Instead of copying a 500-line script into one .sql file, we break it into smaller logical units. We use the from {{ ref('model_name') }} syntax to link these units together. This allows dbt to build a lineage graph and ensures that each piece of logic is tested and reusable.

Imagine a legacy script that joins orders, cleans customers, and calculates aggregates. Instead of one file, we create three:

1. Staging (stg_orders.sql): Cleans the raw data.

   select id as order_id, user_id, status from {{ source('raw', 'orders') }}
   

2. Intermediate (int_orders_cleaned.sql): Filters or joins business logic.

   select * from {{ ref('stg_orders') }} where status != 'cancelled'
   

3. Mart (fct_orders.sql): Performs final aggregations.

   select user_id, count(order_id) as total_orders 
   from {{ ref('int_orders_cleaned') }} group by 1
   

Why this works

By using ref(), you aren't just writing SQL; you are building a dependency graph. If a column name changes in the staging layer, dbt knows exactly which downstream models might be impacted. You replace "black-box" temp tables with transparent, version-controlled models that you can test independently.

When migrating, start from the "bottom" (raw data) and work your way up. As you move each chunk of logic into a select statement within a new model, replace the old temporary table references with the corresponding {{ ref('...') }} tag. This transforms fragile, linear scripts into a robust, scalable pipeline.

Key Takeaway: Decomposing monolithic scripts into modular models using ref() transforms opaque, fragile code into a transparent and testable lineage of data transformations.