1. Staging Model for Raw Orders

Difficulty: Easy · Topic: Sources & staging

The shop's app writes orders to {{ source('shop', 'raw_orders') }}. Write the staging model stg_orders, the one place where raw columns are cleaned up for every model downstream.

• Rename id to order_id and user_id to customer_id.
• order_date is text like '2024-03-05': return it as a DATE named ordered_on.
• status arrives with mixed case and stray spaces (' Shipped'): return it trimmed and lower case.
• amount_cents becomes amount in currency units (1999 → 19.99).
• Drop test orders: rows where _is_test is true. A null flag means a real order.

Return order_id, customer_id, ordered_on, status, amount.

Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.

Sample inputs

{{ source('shop', 'raw_orders') }}

id	user_id	order_date	status	amount_cents	_is_test
1	10	2024-03-01	Completed	1999	false
2	11	2024-03-02	Shipped	4500	null
3	10	2024-03-02	PLACED	250	true
4	12	2024-03-03	returned	1200	false

Expected output

order_id	customer_id	ordered_on	status	amount
1	10	2024-03-01	completed	19.99
2	11	2024-03-02	shipped	45
4	12	2024-03-03	returned	12

Hints

Hint 1

Read the raw table with {{ source('shop', 'raw_orders') }}, never with a hard-coded table name: dbt uses it to draw lineage and check freshness.

Hint 2

In SQL, not _is_test is null when the flag is null, and a WHERE clause drops null. coalesce(_is_test, false) = false (or _is_test is not true) keeps those rows.

Hint 3

The usual staging shape is two CTEs: source (select * from the source) and renamed (the cleaned columns).

More dbt problems

• Latest Version of Each Customer · Medium

All dbt problems