Row store meets column store

Ten sessions, one running project: Daybreak's six OLTP tables (customers, products, orders, order items, subscriptions, events) play the source system, raw and messy - dates stored as text and all. Session by session you will stage it (b2), reshape it into a star schema (b3-b4), track history with slowly changing dimensions (b5), load it properly (b6), serve it to the business (b7), make it fast and cheap (b8), meet the lakehouse (b9), and assemble the whole warehouse end to end (b10). If you took learn-sql-with-phoebe, this is the same database - now you build what b10 only hinted at.

Daybreak's app database answers thousands of tiny questions a day: "insert this order", "update this address", "show Ava her subscription". Each touches one row. The founder's questions are the opposite shape: "revenue by month by channel, all time" touches every row but only three columns. One database serving both is the design mistake warehouses exist to fix.

The single deepest idea in warehousing: change how bytes sit on disk. A row store keeps each record's fields together - perfect for "fetch order 1017, all of it". A column store keeps each column's values together - perfect for "sum line_amount across every order ever". Same data, opposite physical layouts, and the layout decides which questions are fast.

SELECT count(*) AS orders,
       min(order_date) AS first_order,
       max(order_date) AS last_order
FROM orders;

SELECT order_id, order_date,
       typeof(order_date) AS stored_as
FROM orders
LIMIT 4;

Here is the whole track on one strip. Every session adds one layer to Daybreak's warehouse, and each layer exists to answer a business question the raw source fumbles.

Thirty-three orders prove nothing. So this playground generates 300,000 synthetic order lines (table big_orders) before your query runs - then lets a columnar engine loose on them. Watch the millisecond counter under each result.

SELECT channel,
       count(*) AS orders,
       ROUND(SUM(quantity * unit_price), 0) AS revenue
FROM big_orders
GROUP BY channel
ORDER BY revenue DESC;

Same rules as the SQL course: write it, run it, read the error, fix it, run again. All three run against the fresh 300k-row big_orders table.

SELECT strftime(order_date, '%Y-%m') AS month,
       ROUND(SUM(quantity * unit_price), 0) AS revenue
FROM big_orders
GROUP BY month
ORDER BY month;

SELECT channel,
       count(*) AS orders,
       ROUND(AVG(CASE WHEN status = 'refunded' THEN 1.0 ELSE 0 END) * 100, 2) AS refund_pct
FROM big_orders
GROUP BY channel;

SELECT CASE WHEN dayofweek(order_date) IN (0, 6)
            THEN 'weekend' ELSE 'weekday' END AS day_type,
       count(*) AS orders
FROM big_orders
GROUP BY day_type;

• Bookmark this page - the editors are your permanent warehouse scratchpad, 300k rows on demand.
• Write one OLTP-shaped question and one OLAP-shaped question about any system you use at work. Check: does the first touch one row, and the second touch one column?
• On big_orders, find the single biggest revenue day. (GROUP BY order_date, order, limit.)
• Explain to a colleague in two sentences why the company dashboard should not query the production app database. If they push back, you have Part 1's diagram.
• Bring your slowest known dashboard or query to b2 - the staging layer is where its fix begins.

This track teaches the working core of the major data-warehousing curricula, run on a live engine instead of slides. Certificates, graded labs, and videos stay on the official platforms. This page covers: