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_IDCUSTOMER_NAMEORDER_IDPAYMENT_METHODPAYMENT_DATEAMOUNT
- 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_ORDERSTOTAL_REVENUEAVG_PRICECATEGORY
- 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_idis not null insilver_customer_paymentstotal_revenueis greater than 0 ingold_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