ANALYTICS ENGINEERING / SEMANTIC MODELLING
Jaffle Shop Wren AI
Reliable order reporting starts with the right joins and financial definitions. I built a Wren semantic layer over a dbt-built DuckDB warehouse, defined metrics at order grain and reconciled AUD 1,672 in total order amount against payments.
Independent portfolio work using the Jaffle Shop sample dataset. I validated semantic SQL and cube queries locally. The public dashboard contains saved, independently verified example answers. Arbitrary live AI questions are available only when running the project locally.
Tools used
- SQL
- dbt
- DuckDB
- Wren AI
- Python
01 / BUSINESS PROBLEM
Business questions need consistent definitions.
“How much did customers spend?” depends on currency, order status and the grain of the data. Joining an order to multiple payments can inflate its amount. Converting values that are already in AUD a second time can understate them.
I made the reporting rules explicit in three semantic models, two relationships and an order metrics cube. I kept order measures at one row per order and documented which statuses and payment methods contribute to each amount. The definitions support questions such as:
- How many customers placed orders?
- What is average order value by month or status?
- How is payment amount distributed across payment methods?
I checked the answers through semantic SQL, the metrics cube and a monthly summary view. The validated outcome is agreement between those query paths and the recorded source totals.
02 / ARCHITECTURE
From dbt models to checked query results.
I mapped the existing warehouse objects into Wren, then queried the semantic layer to verify the metric definitions.
01 / TRANSFORM
dbt
Upstream seeds, staging and marts prepare customer, order and payment data.
02 / STORE
DuckDB
The local warehouse stores customer and order marts and the staged payments view.
03 / DEFINE
Wren semantic layer
I defined model grains, joins, an order metrics cube and a monthly summary view.
04 / CHECK
SQL + cube queries
I compared monthly results and reconciled order amounts against payments.
Repository scope: the Wren layer. Reproduction requires a separately supplied upstream dbt project and matching DuckDB database.
03 / SEMANTIC MODELS
Keep customer, order and payment grains explicit.
customers
One row per customer, including customers without orders. Exposes lifetime order count and recorded payment value.
jaffle_shop.main.customersorders
One row per order, with customer, date, status, total amount and amounts by payment method.
jaffle_shop.main.orderspayments
One row per payment. Staging has already converted raw cents into AUD.
jaffle_shop.main.stg_paymentsCustomers → orders
One customer can have zero or many orders. Each order references one customer through the declared many-to-one join:
orders.customer_id = customers.customer_idPayments → orders
Many payments can belong to one order. Each payment references one order through:
payments.order_id = orders.order_idSemantic keys describe expectations checked against the data. DuckDB reported no physical key constraints.
04 / METRIC DECISIONS
Define what each amount actually measures.
I based the order_metrics cube on orders, with order-date and status dimensions. I exposed the same five measures by month in monthly_order_summary so I could compare both query paths.
| Metric | Definition |
|---|---|
| Order count | COUNT(*) over orders in scope. |
| Purchasing customers | COUNT(DISTINCT customer_id); distinct customers who ordered in scope. |
| Total order amount | SUM(amount) in AUD across all statuses and payment methods. |
| Average order value | AVG(amount) over orders with non-null amounts. |
| Completed-order amount | Sum of order amounts where status = 'completed'. |
Currency and financial meaning
- Amounts are already in AUD; do not divide them by 100 again.
- Total order amount includes coupons, gift cards and returned-order statuses.
- No explicit refund transactions are modelled. Total order amount is not net or refund-adjusted revenue.
Aggregation and join rules
- Aggregate payments per order before joining to order-level measures.
- Deduplicate purchasing customers over the full selected scope; monthly distinct counts are not additive.
- Calculate overall average order value from orders, rather than averaging monthly averages.
05 / VALIDATED RESULTS
A reconciled sample-data snapshot.
I checked that the monthly view and metrics cube returned matching order counts, total amounts and average order values. These results describe the sample dataset; the months may be incomplete. AUD 1,672 is total order amount, not net revenue.
Source: repository validation results ↗.
06 / DATA QUALITY
Check the model against the data.
Strict validation and compilation
wren context validate --strict passed for three models, one view and two relationships. Context compilation successfully generated target/mdl.json.
The semantic layer uses physical objects in jaffle_shop.main while Wren's internal namespace remains wren.public.
Keys and relationships
- Customer, order and payment keys were unique and non-null.
- No orders lacked a matching customer.
- No payments lacked a matching order.
- No order amounts were missing.
Reconciliation checks
- Order amounts matched summed payments on matched orders within a tolerance of
0.000001. - Customer lifetime aggregates matched AUD 1,672 and 99 orders.
- The monthly view and cube agreed on counts, amounts and averages.
Validation scope
I ran the data-quality queries manually through Wren and reconciled customer lifetime totals at aggregate level. I inspected upstream dbt tests for modelling context but did not run a fresh dbt test suite in this workflow.
My next checks would automate these assertions and reconcile lifetime measures for each customer.
07 / TECHNICAL CHALLENGES
Make the local Windows workflow reliable.
Windows path handling
An extra slash before the drive prefix prevented Wren from resolving the database directory. I resolved the actual directory and normalised its separators. The connection URL needed the directory containing the DuckDB database.
UTF-8 encoding
The CLI raised a UnicodeEncodeError under the default Windows output encoding. I set PYTHONIOENCODING=utf-8 for the session and wrote the connection JSON as UTF-8 without a byte-order mark to avoid JSON parsing issues.
08 / CODE AND NEXT STEPS
Review the definitions and validation workflow.
The repository documents the models, metric definitions, business rules and commands behind these results. My next priorities are automated reconciliation checks, a pinned upstream dataset and evaluation of natural-language questions against expected answers.