Data mesh patterns with dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a dbt model that selects all columns from the source table.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

Aref('source_table')

Bmodel('users')

Ctable('users')

Dsource('raw', 'users')

Attempts:

3 left

💡 Hint

Common Mistakes

Using ref() instead of source() for raw tables

Trying to use table() which is not a dbt function

In dbt, source() is used to refer to raw source tables defined in the sources.yml file.

2fill in blank

medium

Complete the code to create a dbt model that references another model named 'customer_orders'.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

Aref('customer_orders')

Bsource('raw', 'customer_orders')

Ctable('customer_orders')

Dmodel('customer_orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() for models

Using table() which is not a dbt function

The ref() function in dbt is used to reference other models within the project.

3fill in blank

hard

Fix the error in the dbt model code to correctly filter customers with more than 5 orders.

dbt

select customer_id, count(order_id) as order_count from [1] group by customer_id having order_count > 5

Drag options to blanks, or click blank then click option'

Asource('raw', 'orders')

Bref('orders')

Ctable('orders')

Dmodel('orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() for models instead of ref()

Using table() or model() which are not valid dbt functions

To reference another dbt model, use ref(). This allows dbt to manage dependencies and build order.

4fill in blank

hard

Fill both blanks to create a dbt model that builds a dictionary of customer names and their total amounts, filtering for customers with more than 3 orders.

dbt

select customer_name, sum(order_amount) as total_amount from [1] where order_count [2] 3 group by customer_name

Drag options to blanks, or click blank then click option'

Aref('customer_orders')

B>

C<

Dsource('raw', 'customer_orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref()

Using the wrong comparison operator

Use ref() to reference the model and the greater than operator > to filter customers with more than 3 orders.

5fill in blank

hard

Fill all three blanks to create a dbt model that selects customer_id, total orders, and filters for customers with order counts less than 10.

dbt

select [1], count([2]) as total_orders from [3] group by [1] having total_orders < 10

Drag options to blanks, or click blank then click option'

Acustomer_id

Border_id

Cref('orders')

Dcustomer_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref()

Mixing up customer_name and customer_id

Counting wrong column

We select customer_id, count order_id from the orders model referenced by ref().

Practice

1. What is the main purpose of using data mesh patterns with dbt?

easy

A. To write complex Python scripts for data processing

B. To enable teams to share clean and trusted data easily

C. To create visual dashboards directly in dbt

D. To store raw data without transformation

Solution

1. Step 1: Understand data mesh goals

   Data mesh patterns focus on decentralizing data ownership and sharing clean, trusted data across teams.

2. Step 2: Role of dbt in data mesh

   dbt helps build and test data models, making data reliable and easy to share.

3. Final Answer:

   To enable teams to share clean and trusted data easily -> Option B

4. Quick Check:

   Data mesh = share clean data [OK]

Hint: Data mesh is about sharing clean data across teams [OK]

Common Mistakes:

• Confusing dbt with dashboard tools
• Thinking data mesh stores raw data only
• Assuming dbt runs Python scripts

2. Which of the following is the correct way to reference another model in a dbt SQL file?

easy

A. SELECT * FROM ref('model_name')

B. SELECT * FROM model_name()

C. SELECT * FROM {{ ref('model_name') }}

D. SELECT * FROM $ref.model_name

Solution

1. Step 1: Recall dbt ref syntax

   dbt uses the Jinja function ref() inside double curly braces to reference models.

2. Step 2: Match syntax with options

   SELECT * FROM {{ ref('model_name') }} uses the correct syntax: {{ ref('model_name') }} inside SQL.

3. Final Answer:

   SELECT * FROM {{ ref('model_name') }} -> Option C

4. Quick Check:

   dbt ref() = {{ ref('model') }} [OK]

Hint: Use double curly braces and ref() to reference models [OK]

Common Mistakes:

• Omitting curly braces around ref()
• Using parentheses after model name
• Using incorrect variable syntax

3. Given this dbt model SQL snippet, what will be the output columns?

SELECT user_id, COUNT(*) AS order_count
FROM {{ ref('orders') }}
GROUP BY user_id

medium

A. An error because GROUP BY is missing

B. Only user_id column without counts

C. All columns from orders table without aggregation

D. user_id and order_count columns with counts per user

Solution

1. Step 1: Analyze SELECT and GROUP BY

   The query selects user_id and counts rows grouped by user_id, so output has user_id and order_count.

2. Step 2: Confirm aggregation correctness

   GROUP BY user_id matches the aggregation COUNT(*), so query runs correctly and outputs two columns.

3. Final Answer:

   user_id and order_count columns with counts per user -> Option D

4. Quick Check:

   GROUP BY user_id + COUNT(*) = user_id, order_count [OK]

Hint: GROUP BY columns appear with aggregated counts [OK]

Common Mistakes:

• Thinking all orders columns appear
• Missing GROUP BY causes error
• Ignoring COUNT(*) aggregation

4. You wrote this dbt test to check for nulls:

SELECT * FROM {{ ref('customers') }} WHERE customer_id IS NULL

But it always fails even when nulls exist. What is the likely issue?

medium

A. The test should return zero rows to pass, but it returns rows with nulls

B. The ref function is used incorrectly without quotes

C. The test SQL syntax is invalid and causes failure

D. dbt tests cannot check for null values

Solution

1. Step 1: Understand dbt test logic

   dbt tests pass if the query returns zero rows (no errors found).

2. Step 2: Analyze test query behavior

   This query returns rows where customer_id is null, so if nulls exist, rows are returned and test fails.

3. Final Answer:

   The test should return zero rows to pass, but it returns rows with nulls -> Option A

4. Quick Check:

   dbt test passes if zero rows returned [OK]

Hint: dbt tests pass when query returns zero rows [OK]

Common Mistakes:

• Expecting test to pass when rows are returned
• Misusing ref without quotes
• Thinking dbt can't test nulls

5. In a data mesh using dbt, you want to create a shared model that combines sales data from multiple domains. Which approach best fits data mesh principles?

hard

A. Create a dbt model in a shared repository that references domain models via ref() and applies transformations

B. Copy all domain data into one big table manually outside dbt

C. Write a single SQL file that queries raw source tables directly without using refs

D. Use dbt to create separate models per domain but never combine them

Solution

1. Step 1: Understand data mesh domain ownership

   Each domain owns its data models; shared models combine them via references.

2. Step 2: Use dbt refs to combine domain models

   dbt's ref() function allows building shared models that depend on domain models cleanly.

3. Step 3: Avoid manual copying or bypassing refs

   Manual copying or querying raw tables breaks mesh principles and dbt best practices.

4. Final Answer:

   Create a dbt model in a shared repository that references domain models via ref() and applies transformations -> Option A

5. Quick Check:

   Shared model + ref() = data mesh best practice [OK]

Hint: Use shared dbt models referencing domain models with ref() [OK]

Common Mistakes:

• Copying data manually outside dbt
• Querying raw tables directly
• Not combining domain data at all