Downloads

Big data: manufacturer orders (mfgorders)

25 million order lines from a manufacturer to wholesalers and retailers, stored as compressed columnstore on SQL Server and computed on the fly on MariaDB; plus a bill of materials.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Query 25 million order lines on SQL Server columnstore, and on MariaDB with a limit.
  • Explain why the big table takes almost no disk, and how it is built.
  • Write queries that stay fast at this size.
  • Use the bill of materials to read how a manufacturer's products are built.

mfgorders is a manufacturer's order history: 25,165,824 order lines (6.3 million orders, about $6.3 billion) from January 2023 to 24 October 2026, sold to wholesalers, retail chains and independent retailers in eleven countries and made in eight plants. It is here to practice querying large tables, and it adds the one thing the other databases lack: manufacturing, with a three-level bill of materials and a customer hierarchy in which retailers belong to wholesalers.

All customers, products and amounts are invented.

How 25 million rows take almost no disk

Nothing big sits in the project folder. The rows are not stored in a data file you load: SQL Server builds them on the server, and MariaDB computes them when you ask.

Engine What order_lines is Disk Speed
SQL Server A real table with a clustered columnstore index: each column is stored and compressed separately About 380 MB for all 25 million rows (an ordinary table would need several gigabytes) Seconds. Every full-table query below runs in under four seconds
MariaDB A view over the virtual seq_1_to_25165824 table: each row is calculated from its number when you query it None Roughly five microseconds a row, so a full scan takes minutes. Limit the rows with order_line_id

Both engines use the same integer arithmetic and no random numbers, so the data is identical. Because order dates grow with the order number, an order_line_id range is also a date range: the first two million lines are January through April 2023, and the last lines are October 2026.

On MariaDB, add a limit to the examples below. Put o.order_line_id <= 1000000 AND at the start of the WHERE clause (or use o.order_line_id > 25000000 for the latest lines). The queries are written for SQL Server and run unchanged there.

Connect to it

The tables

Table Rows What it holds
order_lines 25,165,824 One row per order line: order_id, line_number, order_date, ship_date, customer, product, plant, channel, quantity, unit_price, discount_pct, net_amount, cost_amount
dim_customer 20,000 500 wholesalers, 1,000 retail chains, 18,500 retailers; parent_customer_id links a retailer to its wholesaler; coordinates
dim_product 2,800 1,500 finished goods, 500 subassemblies and 800 components, with list price, standard cost, make or buy and lead time
bom about 8,500 The bill of materials: parent, child, quantity per, scrap percent
dim_product_family, dim_plant, dim_order_channel 12, 8, 4 Families, plants (with coordinates) and order channels
dim_date 1,461 Calendar with fiscal columns

Views: vw_bom_explosion (every component of a finished good at every level, with cost) and vw_customer_hierarchy.

The big table has no enforced foreign keys, which is normal for columnstore fact tables, so the schema explorer shows nothing connected to it. Join on the ids.

Questions to try

How big is it? The whole table in a few seconds:

SELECT COUNT(*) AS order_lines, COUNT(DISTINCT o.order_id) AS orders, COUNT(DISTINCT o.customer_id) AS customers,
       MIN(o.order_date) AS first_order, MAX(o.order_date) AS last_order, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions
FROM order_lines o
WHERE o.order_date >= '2023-01-01';

Sales and margin by fiscal quarter and customer type. A join to two dimensions across all 25 million lines:

SELECT d.fiscal_year, d.fiscal_quarter, c.customer_type, COUNT(*) AS line_count, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions,
       ROUND(100.0 * (SUM(o.net_amount) - SUM(o.cost_amount)) / SUM(o.net_amount), 1) AS margin_pct
FROM order_lines o
JOIN dim_customer c ON c.customer_id = o.customer_id
JOIN dim_date d ON d.calendar_date = o.order_date
WHERE o.order_date >= '2023-01-01'
GROUP BY d.fiscal_year, d.fiscal_quarter, c.customer_type
ORDER BY d.fiscal_year, d.fiscal_quarter, c.customer_type;

Wholesalers get larger quantities at a 25 percent discount, so their margin is lower than retailers'.

Which products sell the most, and what share of the total is each? A window function over the grouped result:

SELECT p.sku, p.product_name, ROUND(SUM(o.net_amount), 0) AS sales, ROUND(100.0 * SUM(o.net_amount) / SUM(SUM(o.net_amount)) OVER (), 2) AS pct_of_total
FROM order_lines o
JOIN dim_product p ON p.product_id = o.product_id
WHERE o.order_date >= '2023-01-01'
GROUP BY p.sku, p.product_name
ORDER BY sales DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

Is it a Pareto? Rank the customers, then band them by rank:

WITH c AS (SELECT o.customer_id, SUM(o.net_amount) AS s FROM order_lines o WHERE o.order_date >= '2023-01-01' GROUP BY o.customer_id),
     r AS (SELECT customer_id, s, ROW_NUMBER() OVER (ORDER BY s DESC) AS rn, SUM(s) OVER () AS total FROM c)
SELECT CASE WHEN rn <= 200 THEN '1 top 1% (200 customers)' WHEN rn <= 1000 THEN '2 next 4%' ELSE '3 other 95%' END AS band,
       COUNT(*) AS customers, ROUND(SUM(s) / 1000000.0, 1) AS sales_millions, ROUND(100.0 * SUM(s) / MAX(total), 1) AS pct_of_sales
FROM r
GROUP BY CASE WHEN rn <= 200 THEN '1 top 1% (200 customers)' WHEN rn <= 1000 THEN '2 next 4%' ELSE '3 other 95%' END
ORDER BY band;

The top 1 percent of customers (200 of about 20,000) place roughly a fifth of the sales, and the top 5 percent roughly 40 percent.

How do monthly sales trend? A three-month moving average with a window frame:

WITH m AS (SELECT d.calendar_year AS yr, d.calendar_month AS mo, SUM(o.net_amount) AS sales
           FROM order_lines o JOIN dim_date d ON d.calendar_date = o.order_date
           WHERE o.order_date >= '2023-01-01'
           GROUP BY d.calendar_year, d.calendar_month)
SELECT yr, mo, ROUND(sales / 1000000.0, 1) AS sales_millions,
       ROUND(AVG(sales) OVER (ORDER BY yr, mo ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) / 1000000.0, 1) AS moving_avg_3_months
FROM m
ORDER BY yr, mo;

Where is the margin, by plant and product family?

SELECT pl.plant_name, f.family_name, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions,
       ROUND(100.0 * (SUM(o.net_amount) - SUM(o.cost_amount)) / SUM(o.net_amount), 1) AS margin_pct
FROM order_lines o
JOIN dim_product p ON p.product_id = o.product_id
JOIN dim_product_family f ON f.family_id = p.family_id
JOIN dim_plant pl ON pl.plant_id = o.plant_id
WHERE o.order_date >= '2023-01-01'
GROUP BY pl.plant_name, f.family_name
ORDER BY sales_millions DESC
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;

Each product is made mostly in its home plant (85 percent of lines), so plants differ by product family.

How many lines have not shipped yet? Open lines are in the latest weeks:

SELECT ch.channel_name, COUNT(*) AS line_count, SUM(CASE WHEN o.ship_date IS NULL THEN 1 ELSE 0 END) AS not_yet_shipped,
       ROUND(100.0 * SUM(CASE WHEN o.ship_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_not_shipped
FROM order_lines o
JOIN dim_order_channel ch ON ch.channel_id = o.channel_id
WHERE o.order_date >= '2026-09-01'
GROUP BY ch.channel_name
ORDER BY ch.channel_name;

Which wholesalers sell the most once you include their retailers? The customer hierarchy is a self-join on parent_customer_id:

SELECT w.customer_name AS wholesaler, w.country, COUNT(DISTINCT c.customer_id) AS accounts_in_hierarchy, ROUND(SUM(o.net_amount) / 1000000.0, 2) AS sales_millions
FROM order_lines o
JOIN dim_customer c ON c.customer_id = o.customer_id
JOIN dim_customer w ON w.customer_id = COALESCE(c.parent_customer_id, c.customer_id)
WHERE w.customer_type = 'Wholesaler' AND o.order_date >= '2023-01-01'
GROUP BY w.customer_name, w.country
ORDER BY sales_millions DESC
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;

Sales by region and customer type for one month:

SELECT c.region, c.customer_type, COUNT(*) AS line_count, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions
FROM order_lines o
JOIN dim_customer c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01' AND o.order_date < '2026-02-01'
GROUP BY c.region, c.customer_type
ORDER BY sales_millions DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

Because order_date grows with the row number, SQL Server skips the compressed row groups that cannot contain January 2026 (segment elimination), so a one-month query reads a small part of the table.

SQL Server only: approximate distinct counts

On a very large table an exact COUNT(DISTINCT ...) is expensive. APPROX_COUNT_DISTINCT is much cheaper and is typically within a few percent:

SELECT APPROX_COUNT_DISTINCT(order_id) AS approx_orders, COUNT(DISTINCT order_id) AS exact_orders
FROM order_lines;

Manufacturing: the bill of materials

vw_bom_explosion is a recursive query. It starts at each finished good and repeatedly follows bom down to subassemblies and components, multiplying the quantities. The full explosion of one finished good, with every level:

SELECT finished_good_sku, bom_level, item_sku, item_name, item_type, make_or_buy, quantity_per_finished_good, ROUND(extended_cost_usd, 2) AS extended_cost_usd
FROM vw_bom_explosion
WHERE finished_good_sku = 'FG-00001'
ORDER BY bom_level, item_sku;

Rolling the component cost up and comparing it with the product's standard cost shows where materials alone exceed the standard cost, which is a margin problem to chase:

SELECT x.finished_good_sku, p.standard_cost_usd AS standard_cost, ROUND(x.component_cost, 2) AS rolled_up_component_cost,
       ROUND(x.component_cost - p.standard_cost_usd, 2) AS gap
FROM (SELECT finished_good_sku, SUM(CASE WHEN item_type = 'Component' THEN extended_cost_usd ELSE 0 END) AS component_cost
      FROM vw_bom_explosion GROUP BY finished_good_sku) x
JOIN dim_product p ON p.sku = x.finished_good_sku
ORDER BY ABS(x.component_cost - p.standard_cost_usd) DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

What makes a table like this fast, and what to do in Power BI

See Data structures and Power BI limits for the limits that apply to models of this size.

Things worth knowing

Try it on the data

Open the mfgorders database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.