ref() function for model dependencies in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of ref() in a dbt model SQL

Given the following dbt model SQL code, what will be the fully qualified table name generated by the ref('customers') function if the project is deployed in the analytics schema of the prod database?

dbt

select * from {{ ref('customers') }}

A "prod"."analytics"."customers"

B "analytics"."prod"."customers"

C "prod"."customers"."analytics"

D "customers"."analytics"."prod"

💡 Hint

Remember that ref() resolves to the database, schema, and table name in that order.

The ref() function in dbt returns the fully qualified name of the referenced model in the format database.schema.table. Since the project is deployed in the prod database and analytics schema, the correct output is "prod"."analytics"."customers".

❓ data_output

intermediate

Number of rows returned using ref() in a join

Consider two dbt models: orders and customers. Consider the following model SQL:

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

If orders has 100 rows and customers has 10 rows, and every order has a matching customer, how many rows will this query return?

A 110

B 10

C 1000

D 100

💡 Hint

Think about how a join works when every order has a matching customer.

The join matches each order to exactly one customer. Since there are 100 orders and each has a matching customer, the result will have 100 rows.

🔧 Debug

advanced

Identify the error caused by incorrect use of ref()

What error will occur when running this dbt model SQL?

select * from {{ ref(customers) }}

Note: customers is not in quotes.

A Runtime error: table not found

B Compilation error: 'customers' is not defined

C Syntax error: missing quotes around string

D No error, runs successfully

💡 Hint

Look at how the argument to ref() is passed.

The ref() function requires a string argument. Without quotes, customers is treated as a variable which is not defined, causing a compilation error.

🧠 Conceptual

advanced

Understanding model dependency graph with ref()

In dbt, if model A uses {{ ref('B') }} and model B uses {{ ref('C') }}, which models will be built before A?

A Only model <code>B</code> will be built before <code>A</code>

B Only model <code>C</code> will be built before <code>A</code>

C Models <code>B</code> and <code>C</code> will be built before <code>A</code>

D Models <code>A</code>, <code>B</code>, and <code>C</code> are built simultaneously

💡 Hint

Think about how dependencies chain in dbt models.

dbt builds models in dependency order. Since A depends on B, and B depends on C, both B and C must be built before A.

🚀 Application

expert

Predict the effect of changing ref() argument on model build order

You have three models: sales, customers, and regions. The sales model uses {{ ref('customers') }}. You change the sales model to use {{ ref('regions') }} instead. What is the impact on the build order?

A The <code>sales</code> model will now build after <code>regions</code> instead of <code>customers</code>

B The build order remains the same; <code>sales</code> builds after both <code>customers</code> and <code>regions</code>

C The <code>sales</code> model will build before both <code>customers</code> and <code>regions</code>

D The <code>sales</code> model will build independently without waiting for any other model

💡 Hint

Consider how changing the ref() target changes dependencies.

Changing the ref() argument changes the dependency. Now sales depends on regions instead of customers, so it builds after regions.