The 40 Tables That Answer 90% of the Questions

Forty tables answer ninety percent of the questions

A mid-market retail stack exposes somewhere between 300 and 900 tables across POS, ERP, inventory, e-commerce, and labor. A data lake project that models all of them takes nine months.

Roughly 40 of those tables carry the answers to nearly every question an operator asks in a given month. The rest are configuration, audit logs, staging artifacts, integration scratch, and features you do not use.

The distinction is not exotic. It just requires starting from the questions rather than from the schema browser, which is the opposite of how most data projects begin.

Start from twenty questions, not four hundred tables

Write down the twenty questions your operators actually ask. Not use cases. Questions, in the words the person uses.

"Which stores are behind plan this week and why." "Which SKUs have not sold in 30 days and are still on order." "What did the promo do to margin in the categories next to it." "Where is on-hand wrong enough to break replenishment."

Then trace each one backwards to the tables that answer it. Twenty questions traced properly converge fast. By question eight you are hitting tables you already listed. By question fifteen you are adding one or two per question. That convergence is the signal that you have found the core.

Do this in a room with the person who writes SQL against the source systems today. It takes two days, not two months, because that person already knows.

What the forty usually are

POS, 8 to 12 tables. Transaction header, transaction line, tender, discount, return, store master, register master, employee. Everything else in a POS schema is configuration.

Inventory and WMS, 6 to 10. On-hand by location, receipts, transfers, adjustments, cycle counts, purchase order header and line, in-transit.

ERP and finance, 6 to 8. Item master, vendor master, cost history, GL summary, invoice header and line.

E-commerce, 5 to 8. Orders, order lines, fulfillment, customer, product mapping to the POS SKU, which is where most of the pain lives.

Labor, 3 to 5. Scheduled hours, actual hours, store payroll summary.

Reference, 4 to 6. Fiscal calendar, store hierarchy, category hierarchy, SKU cross-reference, plan or budget by store and week.

That is 32 to 49 tables. The number is stable across retailers because the operating questions are stable across retailers.

What to defer, and why deferring is safe

Defer anything that exists to make the source application work rather than to describe the business: permissions, workflow states, sync queues, notification logs, soft-deleted rows, EDI transmission records.

Defer any table that only one report reads, until you know that report matters.

Deferring is safe because raw ingestion is cheap and reversible. Landing a new table into a lake you already have is under an hour of work. The expensive part was never adding a table. It was modeling, documenting, and reconciling a table nobody queries.

The cost asymmetry is the whole argument. Adding table 41 in month three costs an hour. Modeling tables 41 through 400 in month one costs a quarter.

The sequence that gets to an answer in week two

Days 1 to 2. The twenty questions, traced to tables, with the source-system SQL person in the room.

Days 3 to 5. Land the identified tables raw. No modeling, no cleanup, no naming conventions. Just get them queryable.

Week 2. Build the six reference objects: fiscal calendar, store hierarchy, category hierarchy, SKU cross-reference, store open and close dates, plan by store and week. These are small tables that unlock every join in the set, and they are the ones nobody remembers to ask for.

Week 3. Answer five of the twenty questions end to end and reconcile each against how it is answered today. Expect three of the five to be wrong on the first pass. That is the point of doing it in week three rather than month seven.

Week 4 onward. One new question per day or two, each one adding zero to two tables.

The one table that always breaks it

SKU cross-reference. The POS calls it a UPC, the ERP calls it an item number, e-commerce calls it a product ID with a variant suffix, and the vendor calls it something else on the invoice.

In every mid-market retailer, some version of that mapping exists in a spreadsheet maintained by one person. It is incomplete, it has trailing whitespace, and it has duplicate rows from a 2022 catalog migration.

Getting the mapping to 95% coverage takes two to four days. Getting it to 99% takes another two weeks, and the last 1% is discontinued items that nobody will query.

Take 95% and move on, with a report of unmatched SKUs by sales volume so the gaps that matter get fixed first. Projects that hold at the gate for a perfect mapping lose a month to items with no sales in two years.

What you actually give up

Be honest about the tradeoff. A 40-table start means some questions cannot be answered on day one, and you will hit that wall in public.

You give up completeness on the tail: unusual questions, deep audit trails, historical configuration changes. You give up the ability to say "everything is in the lake," which matters to some governance conversations.

You get a platform that answers real questions in week three, which means the sponsor sees value before the budget review, which is the difference between a platform that survives and one that gets cut at month eight with 400 modeled tables and no users.

How to defend the scope internally

Someone will ask why the plan does not include their system. The answer is not "later," which sounds like never. The answer is a number: adding a source to a running lake takes hours to two days, and we will do yours the week you have a question that needs it.

Then keep that promise the first time it is asked, quickly and visibly. The 40-table scope holds only if the marginal table is genuinely cheap, and the way to prove it is cheap is to add one in a day, once, early, for someone with an audience.