Build your Final Model

After creating initial models that transform raw data from your sources, you'll often need to build more complex models that combine and further transform your data. These "final" models typically reference other models rather than directly accessing source data.

Purpose

Building final models in dbt serves several important functions:

• Combines data from multiple upstream models

• Implements more complex business logic

• Creates a final layer of data ready for consumption by BI tools or other stakeholders

• Establishes a clear and maintainable structure in your dbt project

Key Components

Creating a Final Model File

Create a new .sql file in your models directory for your final model. For example:

-- models/customer_orders.sql
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(o.order_id) as number_of_orders,
    SUM(o.amount) as total_order_value
FROM {{ ref('stg_customers') }} c  -- Using ref() to reference another model
LEFT JOIN {{ ref('stg_orders') }} o ON c.customer_id = o.customer_id
GROUP BY 1, 2, 3

Understanding the ref() Function

The ref() function is crucial in building final models:

• It references other models in your dbt project

• Takes a single argument: the name of the model you're referencing

• Allows dbt to automatically handle dependencies between models

• Enables dbt to build models in the correct order

Here's a simplified example to illustrate how models can be "stacked":

-- models/model_a.sql
SELECT *
FROM public.raw_data

-- models/model_b.sql
SELECT *
FROM {{ ref('model_a') }}  -- Referencing model_a

The ref() function performs two key tasks:

1. It interpolates the correct schema name into your model, allowing for flexible deployment configurations.

2. It helps dbt build a dependency graph, ensuring models are built in the correct order during dbt run.

Running Your Final Model

Execute your final model with the following dbt command:

dbt run --models customer_orders

This will ensure all upstream dependencies are built before materializing your final model.

Best Practices

1. Use clear, descriptive names for your final models

2. Leverage the ref() function to create a clear dependency structure

3. Consider using table materializations for final models that will be queried frequently

4. Implement appropriate tests to ensure data quality and business logic

5. Document your final models thoroughly, including descriptions of what they represent and how they should be used

6. Review your model's query performance and optimize as necessary

Remember, final models should represent meaningful business concepts and be structured in a way that makes sense to your end users.