Course overview/Build the model1 of 3
Layer the project
Layer the project
Build staging, core, and mart layers that make cleaning and lineage visible.
One job per layer
Staging cleans one source table at a time: rename, cast, trim, standardize, and deduplicate. It has no joins or business logic. Core holds business entities and facts. Mart shapes an answer for a question and never reads a raw generated table directly.
The duplicate fixes remain different. stg_orders keeps the latest row per ID with QUALIFY; stg_order_items and stg_customers use DISTINCT for exact repeats. Normalize casing and whitespace, then explicitly map values such as U.S.A. and complete that are different spellings rather than case variants.
Explore example project
Explore a finished layered project. Compare the source-to-staging boundary, the duplicate fixes, and the mart metadata with your own files after each task.
/* @bruin
name: weekly_category_revenue
type: duckdb.sql
materialization:
type: table
strategy: create+replace
description: >-
One row per category per ISO week in 2023. Revenue is quantity times net_price.
tags:
- layer:mart
- domain:commerce
meta:
business_owner: commerce-analytics
metric_definition: quantity * net_price
currency: mixed source currencies
expected_update: daily
known_limitation: source amounts are not converted to a common currency
depends:
- stg_orders
- stg_order_items
@bruin */
WITH weekly_lines AS (
-- One row per category and ISO week.
SELECT
date_trunc('week', o.ordered_at)::DATE AS week_start,
oi.category,
SUM(oi.quantity * oi.net_price) AS line_revenue
FROM stg_order_items oi
JOIN stg_orders o USING (order_id)
GROUP BY 1, 2
)
SELECT * FROM weekly_lines