Logic & Lore: How I Engineered The King’s Pantry for Real-World Analytics

1. The Premise

When I started building The King’s Pantry, I wanted to create something that bridged two worlds: the logic-driven precision of ERP systems and the narrative pull of storytelling.

This wasn’t just another “fun dataset.”
It was my way of reimagining how analysts learn — by building something that feels alive, while still grounded in the same business systems I’ve worked with in real life.

Most SQL tutorials stop at random numbers, placeholder IDs, or overly simplified schemas.
But in real distribution environments, nothing is random. Freight rolls into landed cost. Discounts and taxes follow logic. Customer hierarchies define performance.

That’s what I wanted to capture.
The King’s Pantry is fantasy on the surface — but under the hood, it’s an ERP dataset built with the same reasoning I’d apply to real-world clients in distribution, retail, or wholesale.

---

2. The Architecture of the Realm

The dataset is composed of six core tables. Together, they represent a full transactional flow (from supplier to sale) — modeled after a real ERP structure.

Below is a quick overview of how each table fits into that system.

Table	Purpose	Real-World Parallel
vendor	Contains supplier details, including name, region, and supply type.	Mirrors a Vendor Master table — used to track supplier relationships, contracts, and sourcing regions.
brand	Defines product ownership and private-label tiers.	Represents Brand or Business Unit tables that define ownership, brand hierarchy, and margin strategy.
product	Holds all product-level data including pricing, taxonomy, and vendor/brand relationships.	Equivalent to an Item Master or SKU table in an ERP — typically linked to cost, brand, and category data.
customer	Contains customer and account details, including type (B2B or B2C), region, and loyalty.	Mirrors Customer Master data in ERP systems — used for segmentation and account-level reporting.
sales_order	Captures order headers with date, channel, and customer info.	Functions like an Order Header table — includes high-level transaction data for each purchase or shipment.
sales_order_details	Contains the line-level details of each sales order.	Matches an Order Line or Sales Detail table — the core of most ERP reporting, capturing product, quantity, and revenue.

The structure intentionally mirrors a normalized ERP schema — one that analysts could use to model a star schema in Power BI or query directly for sales and profitability analysis.

Every relationship, from vendor to product, product to brand, sales order to detail was designed to reflect how a real business system would operate.

In other words, this dataset could easily pass as a small-scale ERP sandbox.
The only difference is that the world happens to have noble vendors, royal brands, and customers from the Seven Realms because there’s no way in hell I’d spend my free time making another boring dataset that probably already exists out there 😂

3. Table-by-Table Breakdown

Each table in The King’s Pantry was built with a specific purpose and grounded in ERP-style logic.
The goal wasn’t just to simulate data, but to replicate the relationships and dependencies that analysts see every day in real-world systems.

---

vendor — The Merchants’ Guild

Purpose:
The vendor table serves as the backbone of the supply chain. Each row represents a supplier entity responsible for delivering products to the kingdom.

Key Columns:

• vendor_id: Unique identifier (used as a foreign key in product).
• vendor_name: Supplier name (represents actual business entities).
• region: Geographical distribution hub or territory.
• supply_type: Defines the category of goods supplied (raw material, finished goods, etc.).

Real-World Parallel:
This mirrors a Vendor Master in ERP systems — used to manage supplier relationships, purchasing, and sourcing performance.

Design Decisions:
Regions were distributed intentionally to mirror real-world coverage (North, South, East, West), allowing for variance when analyzing regional supplier impact, lead times, or cost structures.

---

brand — The Houses and Private Labels

Purpose:
The brand table connects to both vendor and product tables, representing brand ownership and tiered hierarchy.

Key Columns:

• brand_id: Primary key; linked to products.
• brand_name: Name of the house or private label.
• brand_type: “Private Label” or “National Brand.”
• private_label_tier: Defines whether it’s value, mid-tier, or premium.

Real-World Parallel:
This parallels a Brand Hierarchy in ERP or CRM systems, often tied to pricing strategy, ownership, and profit margin segmentation.

Design Decisions:
Private-label brands were deliberately given margin advantages in the product table (via cost-to-sale ratio), mimicking how retailers drive profitability through owned brands.

---

product — The Royal Inventory

Purpose:
The product table is the dataset’s analytical core. It defines SKUs, cost structure, pricing tiers, and links to both vendor and brand data.

Key Columns:

• product_id: SKU-level unique identifier.
• product_name: Descriptive item name (generated logically by category).
• item_taxonomy_1: Product category (e.g., Beverages, Pantry Essentials).
• item_taxonomy_2: Subcategory (e.g., Coffee, Tea, Oils).
• vendor_id: FK to vendor.
• brand_id: FK to brand.
• unit_cost: Simulated landed cost (inclusive of freight).
• unit_price: Selling price to customers.
• margin_pct: Derived column; (unit_price - unit_cost) / unit_price.

Real-World Parallel:
This table mirrors an Item Master — the foundation of inventory, sales, and margin reporting.

Design Decisions:
Pricing and cost logic were tiered using a weighting model:

• Private label brands earned higher margin percentages.
• Commodity categories (like staples) had lower markups.
• Freight costs were simulated through random cost inflation by region.

Initially, I wasn’t even thinking about adding cost structure to this database because it’s tricky stuff to understand, because it definitely was to me when I started out.

But that ultimately led me to decide to include it anyway because it becomes a great learning opportunity for analysts, especially when most datasets online don’t go this deep into business logic.

---

customer — The Subjects of the Realm

Purpose:
The customer table defines who buys from the kingdom — both B2C and B2B customers.

Key Columns:

• customer_id: Primary key.
• customer_name: Entity or consumer name.
• customer_type: “B2C” or “B2B.”
• region: Regional segmentation.
• loyalty_flag: Binary indicator for returning customers.

Real-World Parallel:
Represents a Customer Master used in CRM/ERP systems for segmentation, retention modeling, and churn analysis.

Design Decisions:
The B2B/B2C ratio was intentionally weighted (roughly 60/40), reflecting a modern omni-channel business.
Loyalty customers were more likely to appear in repeat order joins, allowing learners to analyze retention behavior with SQL.

---

sales_order — The Ledger of Transactions

Purpose:
The sales_order table records order-level metadata. It’s the “header” layer above order lines.

Key Columns:

• order_id: Primary key; formatted as SO-XXXX.
• order_date: Transaction date.
• customer_id: FK to customer.
• sales_channel: Lookup from defined channels (RP, MS, GS, CC).

Real-World Parallel:
A direct match to an Order Header table — storing overall transaction details.

Design Decisions:
Channels were distributed as follows to simulate business mix:

• RP (Raven Prime / eCommerce): 40%
• MS (Market Stall / Retail): 30%
• GS (Guild Supply / Wholesale): 20%
• CC (Court Catering / Institutional): 10%

This design allows meaningful channel mix analysis, like margin by channel or seasonality by sales source.

---

sales_order_details — The Fine Print of Commerce

Purpose:
The sales_order_details table expands each order into multiple line items, providing quantity, product, and extended price details.

Key Columns:

• order_detail_id: Primary key.
• order_id: FK to sales_order.
• product_id: FK to product.
• quantity_ordered: Number of units.
• unit_price: Pulled from product.
• discount_amount: Derived logic simulating sales or promotions.
• extended_price: (unit_price * quantity_ordered) - discount_amount.

Real-World Parallel:
This matches an Order Line or FactSales table — the transactional engine behind revenue analysis.

Design Decisions:
Discounts were modeled probabilistically, applied to roughly 20–25% of transactions.
This distribution supports exercises in calculating gross vs. net sales, average order value, and margin impact.

4. Business Logic — Beneath the Magic

Building The King’s Pantry wasn’t just about linking tables; it was about embedding real business logic beneath the fantasy.
Every number in the dataset is grounded in decisions an analyst would actually make — freight allocation, customer segmentation, discount rules, and tax application.

Below is a breakdown of each major logic component and how it shapes the realism of the dataset.

---

4.1 Category Weighting

Purpose:
Category weighting ensures that not every product or department contributes equally to total sales. This mirrors how real-world categories perform differently depending on demand, margin, and mix.

Logic Overview:
In docs_category_weighting.csv, each product category (from item_taxonomy_1) was assigned a weight between 0.05 and 0.25, influencing the likelihood that products appear in sales orders.

Example Concept:

item_taxonomy_1	weight
Beverages	0.25
Pantry Essentials	0.20
Frozen & Prepared	0.15
Produce	0.10
Bakery & Sweets	0.15
Gourmet & Specialty	0.10
Meat & Seafood	0.05

Business Parallel:
This simulates how a company’s sales mix might skew toward high-volume categories (like beverages) while premium segments remain smaller but higher-margin.

---

4.2 Customer Mix

Purpose:
The docs_customer_mix.csv file establishes the balance between B2B and B2C customers — critical for representing a hybrid business model.

Logic Overview:
Each customer type was assigned a target share of total orders and an expected order frequency.

Example Concept:

customer_type	share_of_orders	avg_order_value_weight
B2C	0.60	0.8
B2B	0.40	1.2

Business Parallel:
This mirrors modern omni-channel organizations where eCommerce (B2C) volume is higher, but wholesale (B2B) drives higher average order value.

Impact:
This mix influences how sales_order and sales_order_details records are generated, ensuring a believable business distribution between retail and institutional buyers.

---

4.3 Discount Logic

Purpose:
Promotional activity was simulated to reflect real-world sales behavior — not every order is sold at full price.

Logic Overview:
The docs_discount_logic.csv file defines conditional discounting based on category and channel.

Example Concept:

item_taxonomy_1	discount_probability	avg_discount_pct
Beverages	0.25	0.10
Bakery & Sweets	0.30	0.15
Gourmet & Specialty	0.10	0.05

Business Parallel:
Promotions are more common in consumable or perishable categories, and less frequent in luxury or specialty goods.

Impact:
Discounts affect net sales, average order value, and margin calculations — teaching learners how to handle promotional adjustments in SQL or Power BI.

---

4.4 Tax Logic

Purpose:
To simulate the complexities of tax and compliance, the dataset applies varying tax rates by region and category using the docs_tax_logic.csv file.

Logic Overview:

region	base_tax_rate	tax_modifier
North	0.07	+0.02 for luxury goods
South	0.06	flat
East	0.08	+0.01 for prepared foods
West	0.05	no modifier

Business Parallel:
This reflects regional tax variations — similar to real ERP or POS systems that apply tax codes dynamically.

Impact:
By blending base rate and modifier logic, analysts can explore derived fields such as “net sales after tax” or “regional tax burden.”

---

4.5 Sales Channel Lookup

Purpose:
The sales_channel_lookup.csv defines each route-to-market and its operational characteristics.

Example Concept:

sales_channel	channel_name	description
RP	Raven Prime	eCommerce orders shipped directly to consumers
MS	Market Stall	Retail and local markets
GS	Guild Supply	B2B/Wholesale orders
CC	Court Catering	Institutional or event-based supply

Business Parallel:
These channels simulate how real businesses diversify revenue sources — retail, digital, and bulk.

Impact:
Each channel’s volume, frequency, and discount behavior varies, supporting analytical exercises like channel performance dashboards or profitability analysis.

---

4.6 Why This Matters

Each logic layer builds analytical depth into the dataset.
The goal wasn’t to make data look pretty, it was to make it behave like the real thing.

By combining deterministic relationships (vendor → product → order) with probabilistic behavior (discounts, weights, customer mix), The King’s Pantry teaches analysts not only how to query data, but how to think like a business partner.

---

5 — Designed for Analysts

The King’s Pantry wasn’t built as a one-off portfolio piece — it was designed as a scalable learning ecosystem.

Every table, measure, and join was created to work across three channels analysts live in daily:

• SQL — for structured logic and business rules
• Python — for modeling, analysis, and storytelling in notebooks
• Power BI — for transforming insights into visuals that drive decisions

This interoperability mirrors the real-world analytics workflow: data pipelines don’t live in silos, and neither should learning.
Whether you’re cleaning data, modeling relationships, or calculating margin % in DAX, the same foundation applies — a clean, well-modeled dataset grounded in business reality.

Because good analysts don’t just learn syntax, they learn systems 🫶

---

Part 6 — Reflection

When I started building The King’s Pantry, I didn’t want another “practice dataset.”
I wanted a world where data felt alive — where logic met imagination, and learning SQL felt like uncovering the economy of a kingdom.

Underneath the fantasy, this project represents something bigger:
a future where analytics education blends creativity and technical mastery.

Too often, tutorials stop at random numbers. But in real business, data tells a story — about people, trade, loyalty, and growth.
That’s what this project captures: the narrative behind the numbers.

This is my reminder — and my invitation — to all analysts:
Your skill set is both art and architecture.
And the stories you can tell with data? That’s your realm.