One Big Table (OBT)

Every attribute beside the number it describes

An analyst wants October revenue by customer region and product category. In a model that keeps orders and their lines apart from customers and products, that query joins 4 tables, and each of 30 dashboard authors writes those joins again. Some join orders to lines and sum the order total, counting a 3-line order 3 times. One Big Table runs those joins once in the pipeline and stores the result, so the analyst's query has none.

The row meaning is unchanged: one row per order line. What changed is that each line now carries customer_region and category alongside product_name, copied from their source tables at build time. Both rows for order O100 say EMEA, and that repetition is the point: the analyst reads 1 table.

CREATE TABLE order_lines_wide AS
SELECT
  ol.order_line_id,
  ol.order_id,
  o.ordered_at,
  o.shipping_fee_cents,
  o.payment_status,
  c.customer_email,
  c.region AS customer_region,
  p.product_name,
  p.category,
  ol.qty,
  ol.amount_cents
FROM order_lines ol
JOIN orders o
  ON o.order_id = ol.order_id
JOIN customers c
  ON c.customer_id = o.customer_id
JOIN products p
  ON p.product_id = ol.product_id

SELECT
  customer_region,
  category,
  SUM(amount_cents) AS revenue_cents
FROM order_lines_wide
WHERE ordered_at >= TIMESTAMP '2026-10-01 00:00:00'
GROUP BY 1, 2

The second query reads 1 table, so it cannot miss a join condition, and any chart tool can generate its GROUP BY. Storage grows, because the region string is stored once per line rather than once per customer, and that trade is usually worth making for the handful of tables analysts touch most.

An order-level column on an order-line table

Shipping is charged once per order. Copied onto order lines, it appears once per line:

SUM(shipping_fee_cents) over these 3 rows gives 1700, and the true total is 1200. SUM(DISTINCT shipping_fee_cents) happens to return 1200 here, which makes it worse: if order O102 also charges 500, the true total becomes 1700 while DISTINCT still returns 1200 with no sign of error. The fix is orders_wide, one row per order with the fee on it. A column whose row meaning differs from its table's belongs in a different table.

Which moment is the region from?

Say a customer moved from Lisbon, in EMEA, to Singapore, in APAC, in March 2026. Their January orders in order_lines_wide show EMEA if the table stores the region at order time, and APAC if it's rebuilt nightly from the current customer record. Both are valid, and they answer different questions: where the customer lived when they ordered, versus where the customer lives now. Pick one per column and write it in the column description. If both are needed, store them as 2 columns named region_at_order and region_current.

Do

• Name the table after its row meaning: order_lines_wide, orders_wide, sessions_wide.
• Build it from silver so parsing and deduplication happen once, upstream.
• Document for each copied column which moment in time it reflects.

Avoid

• Put an order-level amount on an order-line table and hope nobody sums it.
• Hand-maintain a wide table; rebuild it from upstream on every run.
• Build one wide table per dashboard; build one per row meaning.

Analysts run this on order_lines_wide, where one row is one order line. Which statement is true?

SELECT
  customer_region,
  SUM(shipping_fee_cents) AS shipping
FROM order_lines_wide
GROUP BY 1

• Shipping is overstated because each order's fee appears once per line; the sum needs a table with one row per order.
• The query is right because the wide table copied shipping_fee_cents from orders without changing the value.
• Replacing SUM with SUM(DISTINCT shipping_fee_cents) corrects the total for every region.
• The query fails because shipping_fee_cents belongs to orders and a GROUP BY on the wide table cannot reach it.

About this lesson

This section is part of the Design Patterns lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.