Staging Models

The Mental Model

Staging models are the clean mirror of raw sources. They should make data easier to use without making heavy business decisions.

A staging model is like rewriting messy notes into clean handwriting. You are not changing the story yet; you are making it readable.

Dataset reference: ecommerce tables and grain assumptions.

Standardize values without changing grain

A source sends padded status strings and amounts in cents. Normalize those representations while retaining one row per source order.

with raw_orders(order_id, amount_cents, status) as (
  values (101, 1250, ' PAID '), (102, 0, 'CANCELLED')
)
select order_id,
       amount_cents / 100.0 as amount,
       lower(trim(status)) as status
from raw_orders;

Expected rows: (101, 12.5, paid) and (102, 0.0, cancelled). The cancelled order remains present: deciding which states count as revenue belongs in a documented business model. Validate source types before casting and record the currency separately; a numeric conversion does not perform foreign-exchange conversion.

Interactive Check

Question: Should a staging model calculate lifetime customer value?

Practice: Fix stg_orders

Turn a messy raw_orders table into a clean staging model.

Use the guided lab below to record your result, assumptions, and the check that would catch an incorrect result.