Build the model - Step 6 of 15
Build the daily revenue table
Join orders to customers without duplicating rows, then save the result.
Build for a specific use case
A reporting table is shaped for a specific reporting or analysis need. Data teams often call it a mart. What each row represents and how each total is calculated should match the request, so analysts do not have to reinterpret raw events.
Your model contract calls for one row per customer per day. The order fact supplies activity, while the customer dimension supplies country. You already checked that each order joins to at most one customer, so the daily totals will not multiply revenue.
Create the reporting table
Create commerce/assets/analytics/customer_daily_revenue.sql:
/* @bruin
name: analytics.customer_daily_revenue
type: duckdb.sql
depends:
- analytics.fct_orders
- analytics.dim_customers
materialization:
type: table
strategy: create+replace
columns:
- name: customer_id
type: string
primary_key: true
- name: order_date
type: date
primary_key: true
@bruin */
SELECT
f.customer_id,
f.order_date,
c.country,
COUNT(*) AS total_orders,
SUM(CASE WHEN f.is_paid THEN 1 ELSE 0 END) AS paid_orders,
SUM(f.recognized_revenue) AS recognized_revenue,
SUM(
CASE
WHEN f.order_status = 'refunded' THEN f.order_amount
ELSE CAST(0 AS DECIMAL(12, 2))
END
) AS refunded_amount
FROM analytics.fct_orders f
LEFT JOIN analytics.dim_customers c
ON f.customer_id = c.customer_id
GROUP BY
f.customer_id,
f.order_date,
c.country;
The two primary_key fields form a composite key, so they identify a row together. Do not add a unique check to either column by itself. A customer can appear on several dates, and a date can contain several customers.
create+replace tells Bruin to rebuild this small table on every run. Tools often call this update rule materialization. You will change the rule once the model is correct.
Ask your coding agent
Create or review commerce/assets/analytics/customer_daily_revenue.sql using the model definition and SQL in this lesson.
Before running it, explain why joining customers will not duplicate orders and why customer_id + order_date identifies a row. Then run the model, query the result ordered by date and customer, and compare the C001 row for January 1 with its source orders.
Show the diff, command output, and comparison. Do not change the business definition or source data.
Run and inspect the result
bruin run commerce/assets/analytics/customer_daily_revenue.sql
bruin query --connection duckdb-default \
--description "inspect the customer daily revenue result" \
--query "SELECT * FROM analytics.customer_daily_revenue ORDER BY order_date, customer_id;"
The result should contain five rows:
| customer_id | order_date | country | total_orders | paid_orders | recognized_revenue | refunded_amount |
|---|---|---|---|---|---|---|
| C001 | 2026-01-01 | GB | 2 | 1 | 49.00 | 15.00 |
| C002 | 2026-01-02 | DE | 1 | 1 | 85.50 | 0.00 |
| C003 | 2026-01-02 | TR | 1 | 1 | 25.00 | 0.00 |
| C002 | 2026-01-03 | DE | 1 | 0 | 0.00 | 0.00 |
| C004 | 2026-01-03 | CA | 1 | 1 | 120.00 | 0.00 |
Compare one row with the source
Customer C001 has two orders on January 1. One is paid for 49.00; the other is refunded for 15.00. The reporting table therefore shows two total orders, one paid order, 49.00 recognized revenue, and 15.00 refunded amount.
That reconciliation is simple because the sample is small. In a real warehouse, keep the same habit and compare a narrow set of known records.
Checkpoint
Confirm the table has five rows and that the C001 result matches the two source orders. If the totals differ, inspect the join and grouping columns before adding more logic.