Skip to main content

08. Practice Exercises Extend the RAW SILVER GOLD Pipeline

Practice 1: Build silver_customer_payments​

Extend the Silver layer with a model that joins customers, orders, and payments.

Goals​

  • Use {{ ref('stg_customers') }}, {{ ref('stg_orders') }} and {{ ref('stg_payments') }}
  • Keep raw data in Bronze and build cleaned joins in Silver
  • Filter to only successful payments: PAYMENT_STATUS = 'completed'
  • Select meaningful columns such as:
    • CUSTOMER_ID
    • CUSTOMER_NAME
    • ORDER_ID
    • PAYMENT_METHOD
    • PAYMENT_DATE
    • AMOUNT
  • Materialize the model as a table

Suggested file​

models/silver/silver_customer_payments.sql

Why this helps​

  • Reinforces ref() usage across multiple Bronze models
  • Teaches students how to join raw staging tables into a clean Silver result
  • Prepares data for later analytics models

Practice 2: Build gold_product_performance​

Create a Gold-level analytics model that summarizes product sales.

Goals​

  • Use {{ ref('stg_products') }} and {{ ref('silver_orders') }}
  • Join order-level data with product information
  • Compute metrics such as:
    • TOTAL_ORDERS
    • TOTAL_REVENUE
    • AVG_PRICE
    • CATEGORY
  • Group by product and category
  • Materialize the model as a table

Suggested file​

models/gold/gold_product_performance.sql

Why this helps​

  • Reinforces the layered architecture through a Gold metric model
  • Shows how to build product-level reporting for BI consumers
  • Helps students practice grouping and aggregation in DBT

Update models/schema.yml​

Add both new models to models/schema.yml so they are documented and included in DBT docs.

Example additions:

models:
- name: silver_customer_payments
description: "Silver model joining customers, orders, and completed payments"
- name: gold_product_performance
description: "Gold model summarizing product sales performance by category"

Stretch Goal: Add Tests​

Add simple tests for both models in models/schema.yml:

  • customer_id is not null in silver_customer_payments
  • total_revenue is greater than 0 in gold_product_performance

Example:

  - name: silver_customer_payments
tests:
- not_null:
column_name: customer_id

- name: gold_product_performance
tests:
- accepted_values:
column_name: total_revenue
values: [0, 1]

Run the practice models​

dbt run --select silver_customer_payments gold_product_performance

Then generate docs:

dbt docs generate
dbt docs serve

Learning outcome​

After completing these tasks, students will be able to:

  • extend the DBT pipeline with new Silver and Gold models
  • use ref() to build multi-step dependencies
  • document new models in schema.yml
  • validate their work with DBT tests