A data mart is a subject-specific slice of analytical data. BookNest's first sales mart, built from generated sample data, is the wide table of Normalized vs Denormalized, one row per order line. Coupons discount whole orders, so lines carry the gross amount and the net total stays on orders.
CREATE SCHEMA IF NOT EXISTS mart;
DROP TABLE IF EXISTS mart.sales;
CREATE TABLE mart.sales AS
SELECT o.order_id, i.line_no, (o.order_ts AT TIME ZONE 'UTC')::date AS order_date,
o.channel, o.status, o.coupon, c.customer_id, c.country,
b.book_id, b.title, b.author, b.genre,
i.qty, i.unit_price, i.qty * i.unit_price AS gross_amount
FROM orders o JOIN order_items i USING (order_id)
JOIN customers c USING (customer_id) JOIN books b USING (book_id);
ANALYZE mart.sales;
\timing on
SELECT b.genre, c.country, sum(i.qty * i.unit_price) AS gross -- normalized
FROM orders o JOIN order_items i USING (order_id)
JOIN customers c USING (customer_id) JOIN books b USING (book_id)
WHERE o.status = 'delivered' GROUP BY 1, 2 ORDER BY gross DESC LIMIT 1;
SELECT genre, country, sum(gross_amount) AS gross FROM mart.sales -- the mart
WHERE status = 'delivered' GROUP BY 1, 2 ORDER BY gross DESC LIMIT 1;Output
Technology|US|350246.50 Time: 339.104 ms Technology|US|350246.50 Time: 75.112 ms
The mart answered faster in every run; across six runs on the shared 4-CPU host the ratio ranged from 2.7:1 to 4.6:1. But the mart is still a row store: its scan reads every column to use four. Column Stores Under the Hood shows engines that avoid that.