Modeling
Staging, intermediate, marts
Each definition lives in one layer: names and types in staging, shared logic in intermediate models, business entities in marts.
Staging: one model per source table, renamed (<entity>_id, the same name everywhere), typed, converted; no joins.
Only staging reads the sources. Intermediate: joins, re-graining, logic several marts share, written once.
Marts: entities and events at a clear grain, one per concept, shared by every team, with a contract when others
depend on them.
-- A staging select: one source table, renamed, typed, converted; no join.
WITH raw_orders("ordId", "AMOUNT_CENTS", "State") AS (VALUES
('17', 4290, ' Paid'), ('18', 1850, 'cancelled '), ('19', 960, 'PAID'))
SELECT CAST("ordId" AS INTEGER) AS order_id,
CAST("AMOUNT_CENTS" * 0.01 AS DECIMAL(10,2)) AS amount,
lower(trim("State")) AS status
FROM raw_orders
ORDER BY order_id;
| order_id | amount | status |
|---|---|---|
| 17 | 42.90 | paid |
| 18 | 18.50 | cancelled |
| 19 | 9.60 | paid |