Analytics Engineering Pro — Trusted Business Models

Trusted Revenue Mart — Design Review

Decision and contract

Consumer: daily SKU revenue reporting.

Metric: net line revenue for paid or partially refunded orders; exclude cancelled/test orders.

Time: UTC order date until a business reporting timezone is chosen.

Currency: USD output is blocked until an authoritative FX source, rate date, and conversion policy are supplied. Do not label unconverted source-currency totals as USD.

Grain: one row per order_id × line_id in the core fact; one row per order_date × sku × currency in the aggregate mart until conversion is defined. order_lines is line grain; join to orders on order_id only after checking uniqueness of orders.order_id. Do not join line rows directly to other one-to-many tables such as payments or refunds without first aggregating each relation to the intended key.

Draft transformation (PostgreSQL-style; validate types and dialect)

-- stg_order_lines: preserve source line grain and source currency
select order_id, line_id, sku, quantity, unit_price,
       discount_amount, refunded_amount, updated_at as source_updated_at
from raw.order_lines;

-- fct_order_line_revenue: one row per order_id + line_id
select o.order_id, l.line_id, o.customer_id, l.sku,

           o.order_ts_utc::date as order_date_utc,
           o.currency as source_currency,
           l.quantity * l.unit_price

         - coalesce(l.discount_amount, 0)
         - coalesce(l.refunded_amount, 0) as net_revenue_source_currency,
       greatest(o.updated_at, l.source_updated_at) as updated_at
from stg_order_lines l
join raw.orders o using (order_id)
where o.status in ('paid', 'partially_refunded')
  and coalesce(o.is_test, false) = false;

is_test is not in the supplied schema, so the predicate is an explicit contract gap: confirm the authoritative test-order field before implementation. Confirm whether discounts/refunds are line-level, whether negative net lines are valid, how status history is retained, and whether order date means purchase, payment, or fulfillment time. If status is overwritten, use a reliable updated timestamp and a 7-day reprocessing window; late changes outside the lookback need a monitored exception/backfill path.

Aggregate and controls

Aggregate only after the core fact passes uniqueness at (order_id, line_id). Group by UTC order date, SKU, and source currency; apply FX later using the approved rate-date policy. Candidate tests: unique/not-null order and line keys; accepted order statuses; nonnegative quantity (confirm returns); revenue arithmetic reconciliation to source at the same grain; relationship test for every order; freshness on updated_at; currency accepted values; and a fanout row-count/revenue comparison before and after joins.

Incremental, deployment, and rollback

Use updated_at watermark with a 7-day lookback, deduplicate changed source keys deterministically, and merge by (order_id, line_id). Test a representative historical backfill, compare row counts and revenue by date/currency to the source, and record known exclusions. Deploy behind a versioned model or parallel table; compare outputs with consumers, then switch the semantic reference. Roll back by restoring the prior model/version and retain the new output for investigation. Document owner, contract version, lineage, freshness SLA, access/PII review, and change notice before production use.

Open blockers: authoritative test-order indicator; source uniqueness/history; refund and discount semantics; currency conversion authority/date; business timezone; accepted quantity and negative-revenue rules; SQL dialect/type details. No production SQL or USD KPI should be certified until resolved.