Shape it once: modeling a warehouse that people and AI can read

A warehouse full of raw copies is a library with every book on the floor. Everything is there, and nobody can find anything. Modeling is putting the books on shelves.

Part four ended with Pondside's warehouse full and faithful: the café's Square payments, the Shopify orders, the Stripe subscription, the costs sheet. Faithful included four test orders worth ten thousand pretend dollars, money in cents from two sources and dollars from the third, and a cell in the costs sheet that reads $12.60. Every one of those is a trap for whoever asks the next question, and the next person to ask may well be an AI assistant that has never met Pondside.

This part builds Pondside's model: fewer than two hundred lines of SQL, in views, that turn the raw copies into a handful of tables anyone can use the same way. It is the model the rest of the series queries, so every definition is shown in full.

Three layers, one direction

Every well-kept warehouse, from a two-person roaster to a bank, ends up with the same three layers. The names vary (bronze, silver and gold is popular), but the idea does not.

raw

Exactly what the syncs wrote, one schema per source. Never edited by hand. If it is wrong, it is wrong at the source, and that is worth knowing.

clean

One view per real-world thing: a café sale, a web order, a subscription. Types fixed, units fixed, junk removed, the same way every time.

marts

The few tables people ask questions of, shaped around the questions: sales by day, customers, margins. Built only from the clean layer.

The one rule is direction. Clean reads from raw, marts read from clean, and nothing reads sideways or upwards. That sounds bureaucratic until the day Shopify changes how it reports refunds: you fix one view in the clean layer, and every number above it is right again.

Grain: the sentence every table must finish

Before writing a view, finish this sentence about it: one row per… One row per completed payment. One row per item sold. One row per day per channel. That is the table's grain, and it is the single most useful idea in data modeling.

Grain is what stops double-counting. Join a table with one row per order to a table with one row per item, and every order's total now appears once per item: a three-item order is counted three times. If you know the grain of both sides, you see that coming. If you do not, you find out when the monthly revenue is suspiciously good.

If you cannot say what one row is, nobody can safely add the rows up.

Facts and dimensions

Ralph Kimball, whose The Data Warehouse Toolkit (1996) set the vocabulary most warehouses still use, sorted tables into two kinds. A fact table holds the measurements of something that happened: a sale, a payment, a shipment, with its amounts. A dimension table holds the descriptive details you filter and group those facts by: the customer, the product, the date, the place. Arrange the dimensions around a fact and you get the shape that gave the idea its name.

Pondside's model is a lighter cousin of the textbook star, which suits a small team. The facts are there, each with a clear grain. The dates are plain DATE columns rather than keys into a calendar table, because DuckDB's date functions make one unnecessary until you need fiscal years or holidays. And the one dimension that really matters, the customer, gets a mart of its own.

Grain is also something you can test: a view of payments should have exactly as many rows as distinct payment ids. We will run that check as soon as the view exists.

Building the clean layer

The clean layer lives in its own schema. One practical note first: through a plain DuckDB attachment, DuckDB's remote protocol cannot create a schema yet, so run these statements where SQL executes on the server: the DuckHouse query console, DuckPlus, or wrapped in wh.query() (the querying docs cover why). Running against the sample data on your laptop, they work as written.

CREATE SCHEMA IF NOT EXISTS clean;

Café sales: time zones and cents, fixed once

-- One row per completed café payment, in dollars, on the café's clock.
CREATE OR REPLACE VIEW clean.cafe_sales AS
SELECT
  p.id AS payment_id,
  p.order_id,
  p.created_at AT TIME ZONE 'America/Chicago' AS sold_at,
  CAST(p.created_at AT TIME ZONE 'America/Chicago' AS DATE) AS sold_on,
  p.customer_id,
  lower(trim(c.email_address)) AS email,
  CAST((p.amount - least(p.refunded_amount, p.amount)) / 100 AS DECIMAL(18, 2)) AS net_sales,
  CAST(p.tip_amount / 100 AS DECIMAL(18, 2)) AS tip,
  p.source_type AS paid_with,
  p.team_member_id
FROM square.payments p
LEFT JOIN square.customers c ON c.id = p.customer_id
WHERE p.status = 'COMPLETED' AND NOT p._deleted;

Five decisions are packed into that view, and each one would otherwise be repeated in every query:

• Time zone, once. Square stores the instant a payment happened. The question “how was Saturday?” is about the café's Saturday, not UTC's, which starts at 6 or 7 in the evening the day before. Converting with AT TIME ZONE 'America/Chicago' here means every date downstream is a café date, whatever time zone the person or laptop asking happens to be in.
• Dollars, not cents. Square and Stripe report money in the currency's smallest unit; Shopify reports dollars. Dividing by 100 in the clean layer means every money column in the warehouse means the same thing. (In DuckDB, / is true division, so 1250 / 100 is 12.5, not 12.) The result is stored as DECIMAL(18, 2), exact to the cent: add up ninety thousand floating-point dollars and the total can come out a cent different from one run to the next, which is enough to make two reports disagree.
• Refunds out, tips apart. net_sales is what the café kept, before tips. Tips are real money but they belong to the staff, so they get their own column instead of inflating sales.
• Only what happened. Canceled payments and anything deleted at the source are filtered out here, so no query has to remember to.
• An email for identity. The customer's email is brought in, lowercased and trimmed, because it is the only thing that can recognize the same person in Square, Shopify and Stripe.

Café items: unpacking JSON

-- One row per thing sold in the café, unpacked from each order's line items.
CREATE OR REPLACE VIEW clean.cafe_items AS
SELECT
  order_id,
  sold_on,
  item ->> 'name' AS item,
  item ->> 'variation_name' AS variation,
  CAST(item ->> 'quantity' AS INTEGER) AS quantity,
  CAST(CAST(item -> 'total_money' ->> 'amount' AS BIGINT) / 100 AS DECIMAL(18, 2)) AS revenue
FROM (
  SELECT
    id AS order_id,
    CAST(created_at AT TIME ZONE 'America/Chicago' AS DATE) AS sold_on,
    unnest(CAST(line_items AS JSON[])) AS item
  FROM square.orders
  WHERE state = 'COMPLETED' AND NOT _deleted
);

Square keeps an order's line items as a JSON list, and the sync stores it as it came. unnest() turns the list into one row per item, and the -> and ->> operators reach inside each one: -> returns JSON, ->> returns text. Square even sends the quantity as text, so it is cast to a number here and nowhere else. The grain changes in this view, from one row per order to one row per item, which is exactly why it is a separate view.

Web orders: test orders and cancellations

Before writing the web views, it is worth seeing why the email normalization above matters. Shopify keeps an address exactly as the customer typed it, capitals and all, while in Pondside's Stripe account they are all lowercase. Match them as typed and you lose people:

SELECT
  count(*) FILTER (WHERE email IN (SELECT email FROM shopify.orders)) AS matched_as_typed,
  count(*) FILTER (WHERE email IN (SELECT lower(trim(email)) FROM shopify.orders)) AS matched_after_lowercasing
FROM stripe.customers;

A hundred and six subscribers who also shop on the web would have looked like strangers. Email addresses are not a perfect identity (people have several), but lowercased and trimmed they are the best key a small business has, and the clean layer is where that decision belongs.

-- One row per real web order: no test orders, nothing cancelled.
CREATE OR REPLACE VIEW clean.web_orders AS
SELECT
  id AS order_id,
  name AS order_name,
  created_at AT TIME ZONE 'America/Chicago' AS ordered_at,
  CAST(created_at AT TIME ZONE 'America/Chicago' AS DATE) AS ordered_on,
  customer_id,
  lower(trim(email)) AS email,
  CAST(greatest(subtotal_price - total_refunded, 0) AS DECIMAL(18, 2)) AS net_sales,
  CAST(total_shipping AS DECIMAL(18, 2)) AS shipping
FROM shopify.orders
WHERE NOT test AND cancelled_at IS NULL AND NOT _deleted;

This is where part four's test orders finally go: NOT test, once, for good. Cancelled orders go too, and refunds come off net_sales, which excludes shipping because shipping is not product revenue. Shopify already reports dollars, so there is nothing to divide.

-- One row per product on a web order.
CREATE OR REPLACE VIEW clean.web_items AS
SELECT
  order_id,
  ordered_on,
  item ->> 'title' AS product,
  CAST(item ->> 'quantity' AS INTEGER) AS quantity,
  CAST(item ->> 'price' AS DECIMAL(18, 2)) * CAST(item ->> 'quantity' AS INTEGER) AS revenue
FROM (
  SELECT o.order_id, o.ordered_on, unnest(CAST(s.line_items AS JSON[])) AS item
  FROM clean.web_orders o
  JOIN shopify.orders s ON s.id = o.order_id
);

Notice that the web items view reads from clean.web_orders, not from the raw table alone. Joining to it is how the items inherit the decision about test and cancelled orders without restating it. Define a rule once and let everything else borrow it.

The subscription: reaching into Stripe's JSON

-- One row per Pond Club subscription, with its plan and price pulled out of Stripe's JSON.
CREATE OR REPLACE VIEW clean.subscriptions AS
SELECT
  s.id AS subscription_id,
  s.customer AS customer_id,
  lower(trim(c.email)) AS email,
  s.items ->> '$.data[0].price.id' AS plan,
  CAST(CAST(s.items ->> '$.data[0].price.unit_amount' AS BIGINT) / 100 AS DECIMAL(18, 2)) AS monthly_price,
  s.status,
  CAST(s.start_date AT TIME ZONE 'America/Chicago' AS DATE) AS started_on,
  CAST(s.ended_at AT TIME ZONE 'America/Chicago' AS DATE) AS ended_on
FROM stripe.subscriptions s
LEFT JOIN stripe.customers c ON c.id = s.customer
WHERE NOT s._deleted;

Stripe nests a subscription's plan inside its items, as a list of subscription items each holding a price. A JSON path, '$.data[0].price.unit_amount', pulls out the first item's price. Pond Club subscriptions have exactly one item; a business selling bundles would unnest them the way the café items were.

-- One row per paid subscription invoice.
CREATE OR REPLACE VIEW clean.subscription_payments AS
SELECT
  id AS invoice_id,
  customer AS customer_id,
  lower(trim(customer_email)) AS email,
  CAST(created AT TIME ZONE 'America/Chicago' AS DATE) AS paid_on,
  CAST(amount_paid / 100 AS DECIMAL(18, 2)) AS amount
FROM stripe.invoices
WHERE status = 'paid' AND NOT _deleted;

The spreadsheet: text, and the dollar sign

Google Sheets has no column types, so a spreadsheet arrives in the warehouse as text, exactly as typed. Mostly that is harmless. Then there is row three:

SELECT bean, green_cost_per_kg
FROM sheets.bean_costs
ORDER BY _row;

Someone typed a dollar sign. A plain CAST would fail the whole query, and TRY_CAST, which returns NULL instead of failing, would quietly lose Kenya's cost:

SELECT TRY_CAST(green_cost_per_kg AS DOUBLE) AS as_number, green_cost_per_kg
FROM sheets.bean_costs
WHERE bean = 'Kingfisher Kenya';

So the clean view strips the dollar sign and then casts strictly. If someone types twelve sixty next month, the view will fail loudly, which is what you want: a broken number should stop the line, not slip through as a blank.

-- The costs sheet, typed: a spreadsheet arrives as text, dollar signs and all.
CREATE OR REPLACE VIEW clean.bean_costs AS
SELECT
  bean,
  origin,
  CAST(replace(green_cost_per_kg, '$', '') AS DOUBLE) AS green_cost_per_kg,
  CAST(roast_yield AS DOUBLE) AS roast_yield
FROM sheets.bean_costs;

Building the marts

Seven clean views describe what happened. The marts answer what people ask. Pondside needs three, and most small businesses need not many more.

CREATE SCHEMA IF NOT EXISTS marts;

-- One row per day per channel. The table most questions start from.
CREATE OR REPLACE VIEW marts.daily_sales AS
SELECT sold_on AS day, 'cafe' AS channel, count(*) AS orders, sum(net_sales) AS revenue
FROM clean.cafe_sales
GROUP BY ALL
UNION ALL
SELECT ordered_on, 'web', count(*), sum(net_sales)
FROM clean.web_orders
GROUP BY ALL
UNION ALL
SELECT paid_on, 'subscription', count(*), sum(amount)
FROM clean.subscription_payments
GROUP BY ALL;

marts.daily_sales is the table most questions start from: one row per day per channel, with orders and revenue in the same units whatever the source. Three channels stacked with UNION ALL means “sales by month across everything” is a two-line query, and so is “how is the café doing against the web shop.” GROUP BY ALL is DuckDB shorthand for “group by every column that is not an aggregate”, which saves repeating the select list.

-- One row per person, recognized by email across all three channels.
CREATE OR REPLACE VIEW marts.customers AS
WITH activity AS (
  SELECT email, 'cafe' AS channel, sold_on AS day, net_sales AS revenue
  FROM clean.cafe_sales
  WHERE email IS NOT NULL
  UNION ALL
  SELECT email, 'web', ordered_on, net_sales FROM clean.web_orders
  UNION ALL
  SELECT email, 'subscription', paid_on, amount FROM clean.subscription_payments
)
SELECT
  email,
  min(day) AS first_seen,
  max(day) AS last_seen,
  count(*) FILTER (WHERE channel = 'cafe') AS cafe_visits,
  count(*) FILTER (WHERE channel = 'web') AS web_orders,
  count(*) FILTER (WHERE channel = 'subscription') AS subscription_payments,
  round(sum(revenue), 2) AS lifetime_revenue,
  list_sort(list(DISTINCT channel)) AS channels
FROM activity
GROUP BY email;

marts.customers is Pondside's first look at people rather than transactions: one row per email, with when they first and last appeared, how often they show up in each channel, and what they have spent. The channels column is a list, one of DuckDB's native types, so “people who buy in the café and subscribe” is a simple filter.

-- What each bean earns after the green coffee it took. A 12 oz bag is 340 g roasted.
CREATE OR REPLACE VIEW marts.bean_margins AS
WITH bags AS (
  SELECT item AS bean, quantity, revenue FROM clean.cafe_items WHERE variation = '12 oz bag'
  UNION ALL
  SELECT product, quantity, revenue FROM clean.web_items
)
SELECT
  b.bean,
  sum(b.quantity) AS bags_sold,
  round(sum(b.revenue), 2) AS revenue,
  round(c.green_cost_per_kg * 0.34 / c.roast_yield, 2) AS bean_cost_per_bag,
  round(sum(b.revenue) - sum(b.quantity) * c.green_cost_per_kg * 0.34 / c.roast_yield, 2) AS margin
FROM bags b
JOIN clean.bean_costs c ON c.bean = b.bean
GROUP BY b.bean, c.green_cost_per_kg, c.roast_yield;

marts.bean_margins answers the question from part one about which beans earn the most. It combines bags sold in the café with bags sold on the web, then subtracts what the green coffee cost. Roasting loses weight, so a 340 g bag of roasted coffee takes 340 g divided by the roast yield of green beans. It deliberately leaves out labor, packaging and the café's rent; it is a margin after beans, and the column names say no more than that. Notice, too, that the join to the costs sheet quietly drops mugs and pour-over kits, which have no bean cost; that is the point of a mart shaped around one question.

With the model in place, the grain check can run, and it passes:

SELECT count(*) AS rows, count(DISTINCT payment_id) AS payments
FROM clean.cafe_sales;

Names are the documentation

Look back at the column names in those views: net_sales, sold_on, monthly_price, bean_cost_per_bag. None of them is clever, and that is the point. A good name says what the thing is and, where it matters, its unit or its kind.

DuckDB can also store a description with a table, view or column, using COMMENT ON, and anyone can read it back from DuckDB's catalog:

COMMENT ON VIEW marts.daily_sales IS 'One row per day per sales channel (cafe, web, subscription). Revenue in dollars, net of refunds, before tips.';
COMMENT ON COLUMN marts.daily_sales.day IS 'The calendar day on the café''s own clock (America/Chicago).';

SELECT view_name, comment
FROM duckdb_views()
WHERE schema_name = 'marts' AND comment IS NOT NULL;

Views or tables?

Everything above is a view: a saved query that runs every time someone reads it. Views are always current, cost no storage, and change the moment you edit them. Their price is that the work is redone on every read.

For Pondside, that price is invisible: the marts sit on about two hundred thousand rows, which DuckDB reads in a blink. A larger business will one day find a view that is slow because it unpacks millions of JSON line items on every read. The answer is to materialize it: store the result as a table and rebuild it on a schedule.

-- Store today's answer as a table...
CREATE OR REPLACE TABLE marts.cafe_items_daily AS
SELECT sold_on, item, sum(quantity) AS sold, sum(revenue) AS revenue
FROM clean.cafe_items
GROUP BY ALL;

-- ...and run the same statement again whenever it should be refreshed.

Start with views. Materialize the one that hurts, when it hurts, and keep the view it was built from so the logic still lives in one place.

What the model already knows

One query against the customers mart already says something about Pondside that no single tool could. Here is everyone, grouped by the channels they buy through:

SELECT
  array_to_string(channels, ' + ') AS channels,
  count(*) AS people,
  round(avg(lifetime_revenue)) AS avg_lifetime_revenue
FROM marts.customers
GROUP BY ALL
ORDER BY people DESC;

Of 4,800 people Pondside can recognize, about a third buy through more than one channel, and they are worth more: the 198 who do all three average more than three times what a web-only customer spends. (On the café side this counts only customers Square recognized; most café sales are anonymous.) That is not a dashboard anyone built. It fell out of a model with a clear grain and one definition of a person.

The model is the foundation. Next comes the fun part: the SQL that turns it into answers to Pondside's three questions, and to most of the questions any business asks. That is part six: the SQL behind every business question.