Data Build Tool: Ways to Table Reference in DBT Models

How Many ways to Table reference in DBT Model

 

 In dbt (data build tool), there are several ways to reference tables in your models. Here are the primary methods:

 1.      Ref Function:

This is the most common way to reference other models (tables) within your dbt project. It allows you to create dependencies between models and manage them automatically. The syntax is:
 
   {{ref ('model_name') }}
 
 This function generates the appropriate SQL syntax to reference the model, whether    

  it's a table or a view.

 2.    Source Function:

If you are referring to a table that exists outside of your dbt project (for example, raw data from your data warehouse), you would use the `source` function. The syntax is:
 
   {{ source('source_name', 'table_name') }}
   
 This helps dbt track source data and provides better documentation and testing capabilities.

 

3.    Direct SQL Table References:

 While it's less common and not recommended due to lack of dependency management, you can reference tables directly in your SQL with their fully qualified names. For example:

   SELECT  FROM schema_name.table_name
 
   However, using direct references can make it harder to manage dependencies and could lead to issues with schema management during dbt runs.

 

4.    Using Jinja Variables:

 If you have variables defined in `dbt_project.yml` or in your model files, you can use those to create dynamic references based on your environment or other conditions:
 
   SELECT  FROM {{ var('schema_name') }}.{{ ref('model_name') }}

5.     Database Catalogue References:

 Some data warehouses allow you to reference tables by specific database catalog names which can be included in your SQL.
Using the `ref` and `source` functions is generally the best practice in dbt because they help maintain clear dependencies and manage the order of model execution effectively. Always prefer to use these functions over direct SQL table references for a more maintainable and robust project structure.

 Example

{{ config(materialized='table') }}

 /* This model explain how to many ways to take table reference while creating mode */

with table_create as(

SELECT e.empno,e.ename,e.sal,e.deptno,f.sum_sal,d.loc

FROM

-- source function

{{ source ('analytics','emp')}} e

 -- ref function

JOIN {{ ref('first_model') }} f on e.deptno=f.deptno

 -- direct table reference

JOIN analytics.dept d on f.deptno=d.deptno

)

select * from table_create