Loading the Sales Mart

Loading BookNest's Sales Mart into DuckDB

duck_build.py, which built the database used since Column Stores Under the Hood, loads the CSV export PostgreSQL 1,289 loaded in PostgreSQL 18 on the WSL2 Host plus the raw order events, then builds sales with the same rows and columns as mart.sales:

duck_build.py: BookNest's tables and sales mart in DuckDBPython

import duckdb
con = duckdb.connect("booknest.duckdb")
money = {"orders": "discount::DECIMAL(8,2) AS discount, total::DECIMAL(10,2) AS total",
         "order_items": "unit_price::DECIMAL(6,2) AS unit_price",
         "books": "price::DECIMAL(6,2) AS price"}
for t in ("books", "customers", "orders", "order_items"):
    repl = f" REPLACE ({money[t]})" if t in money else ""
    con.execute(f"CREATE OR REPLACE TABLE {t} AS "
                f"SELECT *{repl} FROM read_csv('csv-out/{t}.csv')")
con.execute("ALTER TABLE books RENAME id TO book_id")
con.execute("""CREATE OR REPLACE TABLE order_events AS
               FROM read_json('data/order_events.jsonl', format = 'newline_delimited')""")
con.execute("""CREATE OR REPLACE TABLE 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)::DECIMAL(10,2) 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)
ORDER BY o.order_id, i.line_no""")
con.execute("CHECKPOINT")
print(con.sql("""SELECT count(*) AS lines,
                        sum(gross_amount) FILTER (status <> 'cancelled') AS gross
                 FROM sales""").fetchone())

Output

(137944, Decimal('3303427.30'))

Under /usr/bin/time (scripts/duck_build_timed.sh) the load took 4.7 s and 242 MB of memory, wrote a 19 MB file, and reproduced PostgreSQL's numbers exactly: 137,944 lines and 3,303,427.30 of non-cancelled gross sales. Three details matter. read_csv sniffs types (Inference vs Explicit Types), so SELECT * REPLACE (...) overrides just the money columns with exact decimals. ORDER BY o.order_id writes the facts in time order, which keeps zone maps selective (Zone Maps and Pruning). And gross_amount is cast to DECIMAL(10,2): left as the DECIMAL(25,2) that qty * unit_price produces, it is stored as 128-bit integers and summed about 250 times slower (Columnar Compression).

MENU