Schema
| Column | Type | Description |
|---|---|---|
order_id |
STRING | Globally unique order id. Generated at order creation. [warehouse-schema] |
customer_id |
STRING | FK into customers. Never null for completed orders. [warehouse-schema] |
order_ts |
TIMESTAMP | Order placement time in UTC. This is the timestamp used for fiscal-year assignment. [revenue-policy] |
order_status |
STRING | One of pending, paid, shipped, delivered, cancelled, refunded. Revenue is recognized only when order_status = 'delivered' and the 30-day return window has closed. [revenue-policy] |
gross_amount |
NUMERIC(18,4) | Pre-discount subtotal, in currency. Excludes tax and shipping. [warehouse-schema] |
discount_amount |
NUMERIC(18,4) | Total discounts applied (promo codes, loyalty credits, price adjustments). [warehouse-schema] |
net_amount |
NUMERIC(18,4) | gross_amount - discount_amount. This is the recognized-revenue amount per policy. [revenue-policy] |
shipping_amount |
NUMERIC(18,4) | Carrier charge billed to the customer. Excluded from revenue (pass-through liability). [revenue-policy] |
tax_amount |
NUMERIC(18,4) | Sales tax collected. Excluded from revenue (pass-through liability). [revenue-policy] |
currency |
STRING | ISO 4217 currency code. Non-USD orders convert via finance.fx_daily_rates on order_ts date. [revenue-policy] |
channel |
STRING | Order origin: web, mobile, marketplace. Marketplace orders (Amazon, eBay) are net-settled and recognized on marketplace payout, not on order_status = 'delivered'. |
Notes for consumers
- The grain assumption trips up new analysts:
SUM(net_amount) GROUP BY order_idis a no-op because there is exactly one row per order. For per-SKU revenue, joinorder_lines. - The
refundedstatus is terminal in this table; the refund event itself lives infinance.refunds, keyed onorder_id.
[warehouse-schema]: Acme Retail warehouse schema — sales dataset [revenue-policy]: Revenue Recognition Policy (FY2026)
Markdown file orders.md