Data mesh patterns with dbt - Step-by-Step Execution

Concept Flow - Data mesh patterns with dbt

Define Domain Data Models

↓

Create dbt Models per Domain

↓

Apply Transformations & Tests

↓

Publish Domain Data Products

↓

Consume Data Products Across Domains

↓

Monitor & Evolve Mesh

This flow shows how data mesh patterns use dbt to build domain-specific data models, transform and test them, publish as data products, and share across domains.

Execution Sample

dbt

with sales as (
  select * from raw.sales_data
),
customer_summary as (
  select customer_id, sum(amount) as total_spent
  from sales
  group by customer_id
)
select * from customer_summary

This dbt model transforms raw sales data into a customer summary showing total spent per customer.

Execution Table

Step	Action	SQL Query Part	Result Preview
1	Read raw sales data	select * from raw.sales_data	Rows of raw sales records
2	Aggregate sales by customer	select customer_id, sum(amount) as total_spent from sales group by customer_id	Customer IDs with total spent sums
3	Output final customer summary	select * from customer_summary	Table with customer_id and total_spent columns
4	End of model execution	N/A	Data ready for downstream consumption

💡 All transformations complete, data product ready for use

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
sales	undefined	raw sales data rows loaded	unchanged	unchanged
customer_summary	undefined	undefined	aggregated sums per customer	final output table

Key Moments - 2 Insights

Why do we create separate models for each domain in dbt?

Creating separate models per domain helps isolate data logic and ownership, as shown in the flow from defining domain models to publishing data products (see concept_flow). This makes data easier to manage and share.

How does dbt ensure data quality in data mesh?

dbt applies tests and transformations in each model step (see execution_table steps 2 and 3), catching errors early and ensuring reliable data products.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after Step 2?

A Raw sales data rows

B Customer IDs with total spent sums

C Final output table with all columns

D Empty table

💡 Hint

Refer to execution_table row 2 under 'Result Preview' for the aggregated result

At which step does the model output the final customer summary?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Check execution_table row 3 for the final output action

If we add a test to check for null customer_id, where in the flow would it fit?

A During transformations and tests step

B After publishing data products

C Before defining domain data models

D During data consumption across domains

💡 Hint

Look at concept_flow step 'Apply Transformations & Tests' for when quality checks happen

Concept Snapshot

Data mesh with dbt:
- Define domain-specific models
- Use dbt to transform and test data
- Publish as reusable data products
- Share across domains
- Monitor and evolve continuously

Full Transcript

Data mesh patterns with dbt involve creating domain-specific data models using dbt. The process starts by defining data models for each domain. Then, dbt runs SQL transformations and tests to prepare clean, reliable data products. These products are published and shared across domains for easy consumption. Monitoring and evolving the mesh ensures data stays accurate and useful. The example code shows how raw sales data is transformed into a customer summary with total spending. The execution table traces each step from reading raw data to outputting the final summary. Variables like 'sales' and 'customer_summary' change as data flows through the steps. Key moments clarify why domain separation and testing are important. The quiz checks understanding of the execution steps and where tests fit in the flow.

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