Cross-team model sharing in dbt - Mini Project: Build & Apply

Cross-team model sharing

📖 Scenario: Your company has multiple teams working on different parts of the data pipeline. Each team builds dbt models for their area. Now, you want to share a model from one team with another team so they can use it in their analysis.

🎯 Goal: Learn how to share a dbt model across teams by creating a model in one project and referencing it in another project.

📋 What You'll Learn

Create a dbt model in the source project

Configure the source project to expose the model

Reference the shared model in the target project

Run dbt to build and verify the shared model

💡 Why This Matters

🌍 Real World

In many companies, different teams build parts of the data pipeline. Sharing dbt models allows teams to reuse work and maintain consistency.

💼 Career

Data engineers and analysts often collaborate using dbt. Knowing how to share models across teams is a valuable skill for maintaining scalable data workflows.

1

Create a dbt model in the source project

In the source dbt project, create a model file called source_team_model.sql inside the models folder. Write a simple SQL query that selects id and name columns from the raw.customers table.

dbt

-- Your code here to create source_team_model.sql with the SQL query

Hint

Write a simple SELECT statement in source_team_model.sql that selects id and name from raw.customers.

2

Configure the source project to expose the model

In the source dbt project's dbt_project.yml file, add the model source_team_model under the models section and set enabled: true and materialized: view to make it available for sharing.

dbt

select id, name from raw.customers

# Add model configuration in dbt_project.yml to enable and materialize source_team_model as a view
# Your code here

Hint

In dbt_project.yml, under models:, add source_team_model: with enabled: true and materialized: view.

3

Reference the shared model in the target project

In the target dbt project, create a model file called target_team_model.sql inside the models folder. Use the ref function to reference the source_team_model from the source project and select all columns from it.

dbt

-- Your code here to create target_team_model.sql referencing source_team_model using ref()

Hint

In target_team_model.sql, write select * from {{ ref('source_team_model') }} to use the shared model.

4

Run dbt to build and verify the shared model

Run the command dbt run in the target project to build the models. Then run dbt test to verify the models run successfully. Finally, print the message "Shared model built successfully".

dbt

# Your code here to print success message

Hint

After running dbt commands, print "Shared model built successfully" to confirm.

Practice

1. What is the main benefit of using ref() in dbt for cross-team model sharing?

easy

A. It allows teams to write raw SQL without any dependencies.

B. It automatically creates new tables in the database without configuration.

C. It connects models from different teams to avoid duplication and ensure consistency.

D. It disables model version control to speed up development.

Solution

1. Step 1: Understand the purpose of ref() in dbt

   ref() is used to reference other models, enabling dependency tracking and collaboration.

2. Step 2: Identify the benefit in cross-team sharing

   By using ref(), teams avoid duplicating models and keep data consistent across projects.

3. Final Answer:

   It connects models from different teams to avoid duplication and ensure consistency. -> Option C

4. Quick Check:

   Cross-team sharing = ref() connects models [OK]

Hint: Remember: ref() links models to share work [OK]

Common Mistakes:

• Thinking ref() creates tables automatically
• Believing ref() removes dependencies
• Confusing ref() with raw SQL execution

2. Which of the following is the correct syntax to reference a model named sales_summary in dbt?

easy

A. select * from ref('sales_summary')

B. select * from {{ ref('sales_summary') }}

C. select * from sales_summary

D. select * from {{ sales_summary }}

Solution

1. Step 1: Recall dbt syntax for referencing models

   dbt uses Jinja templating with double curly braces and ref() to reference models.

2. Step 2: Identify correct syntax

   The correct syntax is select * from {{ ref('model_name') }} to properly resolve the model.

3. Final Answer:

   select * from {{ ref('sales_summary') }} -> Option B

4. Quick Check:

   Use {{ ref('model') }} for model references [OK]

Hint: Use double curly braces with ref() for model references [OK]

Common Mistakes:

• Omitting curly braces around ref()
• Using ref() without quotes
• Trying to reference model without ref()

3. Given two teams with models team_a.orders and team_b.customers, what will be the result of this dbt model SQL?

select o.order_id, c.customer_name
from {{ ref('orders') }} o
join {{ ref('customers') }} c on o.customer_id = c.customer_id

medium

A. A joined table of orders and customers combining data from both teams.

B. An error because ref() cannot reference models from other teams.

C. A table with only orders data, ignoring customers.

D. A table with only customers data, ignoring orders.

Solution

1. Step 1: Understand ref() cross-team referencing

   ref() allows referencing models across teams if they are in the same dbt project or properly configured.

2. Step 2: Analyze the SQL query

   The query joins orders and customers on customer_id, combining data from both models.

3. Final Answer:

   A joined table of orders and customers combining data from both teams. -> Option A

4. Quick Check:

   ref() joins models across teams = combined data [OK]

Hint: ref() joins models across teams if configured [OK]

Common Mistakes:

• Assuming ref() cannot cross teams
• Expecting only one model's data returned
• Ignoring join condition effect

4. You wrote this dbt model to share data across teams:

select * from {{ ref('team_a.orders') }}

But it fails with an error. What is the likely cause?

medium

A. You cannot use dot notation inside ref(); only model names are allowed.

B. The model team_a.orders does not exist in the project.

C. You forgot to wrap ref() in double curly braces.

D. You need to use source() instead of ref() for cross-team models.

Solution

1. Step 1: Check ref() usage rules

   ref() accepts only model names without schema or dot notation.

2. Step 2: Identify error cause

   Using team_a.orders inside ref() causes syntax error; correct usage is just orders.

3. Final Answer:

   You cannot use dot notation inside ref(); only model names are allowed. -> Option A

4. Quick Check:

   ref() = model name only, no dots [OK]

Hint: Use only model names inside ref(), no dots [OK]

Common Mistakes:

• Using schema or team prefix inside ref()
• Confusing ref() with source()
• Missing curly braces around ref()

5. Your company has two teams: marketing and sales. Marketing built a model campaign_performance. Sales wants to use it in their model sales_analysis. How should sales correctly reference marketing's model in dbt to ensure cross-team sharing?

hard

A. Duplicate the campaign_performance SQL in sales_analysis to avoid dependencies.

B. Use select * from marketing.campaign_performance without ref().

C. Use select * from {{ source('marketing', 'campaign_performance') }}.

D. Use select * from {{ ref('campaign_performance') }} assuming both models are in the same dbt project.

Solution

1. Step 1: Understand dbt project structure for cross-team models

   If both teams' models are in the same dbt project, ref() can reference models across folders or teams by model name.

2. Step 2: Choose correct referencing method

   Using ref('campaign_performance') is the correct way to share models and track dependencies.

3. Final Answer:

   Use select * from {{ ref('campaign_performance') }} assuming both models are in the same dbt project. -> Option D

4. Quick Check:

   Cross-team sharing = ref() within same project [OK]

Hint: Use ref() with model name for cross-team sharing in same project [OK]

Common Mistakes:

• Using raw schema.table without ref()
• Duplicating SQL instead of referencing
• Using source() incorrectly for models