# Synthetic retail data dictionary

All records are invented for this course. No actual customers, merchant, payments or private source were used. Dates span 1 January through 31 March 2025 for the analysis; signup dates include late 2024. Currency is INR. Monetary columns are exact numeric(10,2). No taxes, delivery charges, costs, exchange rates or marketing attribution are modeled.

## Tables and keys

| Table | Grain and key | Important columns and meaning |
| --- | --- | --- |
| customers | One customer; customer_id | customer_name is synthetic; city can be NULL; signup_date is the registration date |
| products | One current catalogue product; product_id | category groups products; list_price is current catalogue price, not historical sales price |
| orders | One order; order_id | customer_id references customers; order_date is a date without a time zone; status is completed, pending or cancelled; channel is web or app |
| order_items | One product line on one order; (order_id,line_no) | quantity is a positive integer; unit_price is the historical per-unit price; line_discount is the total discount for the whole line |
| payments | One payment attempt; payment_id | amount is positive; only status succeeded contributes to collection; failed attempts remain evidence but not money collected |
| refunds | One recorded refund event; refund_id | amount is positive and relates to an order; more than one refund may belong to an order |
| raw_orders | One received row; batch_row | source_order_id may repeat; order_date_text and status_text are unvalidated; amount and customer_id may be missing or invalid; loaded_at orders versions |

order_items, payments and refunds are independent child tables of orders. They must be separately reduced to compatible grain before combining monetary measures. customer_name, city, product_name and monetary amount are not unique identifiers.

## Metric contract

- Quarter: order_date >= 2025-01-01 and order_date < 2025-04-01
- Included orders: completed only
- Line sales before refunds: quantity * unit_price - line_discount
- Order sales before refunds: sum line sales across the order
- Included refunds: refund_date < 2025-04-01 on included orders
- Net sales: order sales before refunds minus included order refunds
- Monthly attribution: order_date month, including the order's recorded refunds
- Net average order value: net sales / completed orders in the same population
- Buying customers: distinct customer_id among included orders in that period
- All-customer contribution report: retain every customer, including zero completed-order customers

These are course definitions rather than financial-reporting rules. Payment collection, net sales and cash-flow-by-event-date are different measures. The synthetic dataset contains no late-arriving records. For historical examples, the stored completed status is assumed known on order_date; a real as-of analysis needs status history and availability times.

## Known fixture checks

| Check | Value |
| --- | --- |
| Curated customers | 8 |
| Products | 5 |
| Orders | 14 |
| Order lines | 19 |
| Payment attempts | 16, of which 13 succeeded and 3 failed |
| Refund events | 4 |
| Raw intake rows | 8 |
| Completed orders in quarter | 11 |
| Sales before refunds on completed orders | INR 5860.00 |
| Recorded refunds on included orders | INR 215.00 |
| Quarter net sales | INR 5645.00 |

The source CSV files are readable evidence and optional import material. The setup SQL is the canonical runnable fixture. CSV nulls are represented as empty fields; expected-result CSVs use NULL as a visible missing-value marker. Do not confuse the two conventions.

## Raw intake acceptance policy

Choose the latest received row for each source_order_id by loaded_at descending and batch_row descending. Then require a non-null source identifier, known customer, non-null nonnegative amount, normalized status in the accepted domain and a date parseable by PostgreSQL. Keep invalid latest records for review instead of silently taking an older version. No raw rows are deleted by the course queries.

This policy accepts batch rows 2 and 8. It does not establish source completeness, factual accuracy, freshness or a strict date-format contract. In particular, PostgreSQL date parsing accepts more formats than ISO YYYY-MM-DD; add a separately specified format rule if required.