Data mesh patterns with dbt - Mini Project: Build & Apply

Data Mesh Patterns with dbt

📖 Scenario: You work in a company that uses data mesh principles to organize data ownership by domains. Each domain manages its own data models using dbt. Your task is to create a simple dbt model that follows data mesh patterns by defining a domain-specific source, a configuration variable for freshness, and a core transformation model that applies this freshness threshold.

🎯 Goal: Build a dbt project with a source definition for a domain dataset, a freshness threshold configuration, a transformation model that filters data based on freshness, and finally display the filtered results.

📋 What You'll Learn

Create a source configuration for the 'sales' domain with a table named 'orders'.

Define a freshness threshold variable called freshness_days set to 30.

Write a dbt model SQL that selects orders from the source where the order date is within the freshness threshold.

Print the final filtered orders in the model output.

💡 Why This Matters

🌍 Real World

Data mesh organizes data ownership by domains. Each domain manages its own data models with dbt, enabling scalable and autonomous data pipelines.

💼 Career

Understanding data mesh patterns with dbt is valuable for data engineers and analysts working in modern data platforms that emphasize domain ownership and modular data transformations.

1

Define the source configuration for the sales domain

Create a source configuration in sources.yml with the source name sales and a table named orders. The source should point to the schema raw_sales and database analytics.

dbt

# Define the sales source with orders table in sources.yml
# Your code here

Need a hint?

Use the sources key and define the source with name, database, schema, and tables.

2

Set a freshness threshold variable

In your dbt_project.yml, create a variable called freshness_days and set it to 30.

dbt

version: 2

sources:
  - name: sales
    database: analytics
    schema: raw_sales
    tables:
      - name: orders

# Add the freshness_days variable in dbt_project.yml
# Your code here

Need a hint?

Use the vars section in dbt_project.yml to define variables.

3

Create a dbt model filtering fresh orders

Create a dbt model SQL file named fresh_orders.sql that selects all columns from the source sales.orders where the order_date is within the last freshness_days days. Use the variable freshness_days in your SQL with the syntax {{ var('freshness_days') }}.

dbt

version: 2

sources:
  - name: sales
    database: analytics
    schema: raw_sales
    tables:
      - name: orders

vars:
  freshness_days: 30

-- Create fresh_orders.sql model
-- Your code here

Need a hint?

Use {{ source('sales', 'orders') }} to reference the source and filter by order_date using the variable.

4

Display the filtered fresh orders

Run the dbt model fresh_orders and print the resulting rows to display the fresh orders filtered by the freshness threshold.

dbt

-- fresh_orders.sql
select *
from {{ source('sales', 'orders') }}
where order_date >= current_date - interval '{{ var('freshness_days') }} day'

# Print the results of fresh_orders model
# Your code here

Need a hint?

Use the command dbt run --models fresh_orders to execute and see the filtered data.