Project: a shop staging layer
Harbor Cart's merchandising lead needs reusable staging models before an assortment report. Raw orders contain corrections; customer records contain inconsistent text. Deliver stg_orders, stg_customers, and a customer order-count mart with an identifier test. Keep raw data unchanged.
Harbor Cart's merchandising analyst receives duplicate order updates and inconsistent contact codes. You own the staging layer that the reporting team will reuse. Keep the raw sources intact.
Each step adds a rule or a model to the same project, so a later step depends on the work you completed earlier. Start by choosing useful columns, then establish one row per order, normalize the contact data, and connect the models through ref.
The final output is a customer order-count table with a tested identifier. Unknown customer links must not erase a customer who has no orders. Read the task before editing: changing the join type or counting all joined rows can produce plausible totals while breaking the required grain.
select order_id, customer_id, lower(trim(status)) as status, amount, ordered_at, updated_at, upper(trim(currency)) as currency from {{ source('shop', 'orders') }} where order_id is not null qualify row_number() over (partition by order_id order by updated_at desc, amount desc nulls last)=1
A worked pattern with fictional records. Adapt the model name and business rule to each task.