Building a Data Warehouse for a Restaurant Chain: What We Learned at Carl's Jr Mexico

Key takeaway — Very few firms have actually built a data warehouse for a restaurant chain at franchise scale. We have: for Grupo AFAL, the Carl’s Jr Mexico franchise with 100+ locations, we migrated Oracle EBS to Snowflake, modeled in dbt, and delivered the executive layer in Tableau — a single source of truth across all 100+ locations with real-time supply chain visibility. This guide covers what problem that solves, what the architecture looks like, and what to demand from whoever builds yours.

If you have been looking for someone to build a data warehouse for your restaurant chain, you have probably noticed something odd: the search returns firms talking about “retail data solutions” in the abstract, integrators selling the ERP, and very few people who can describe a finished build across real locations. It is not that the work does not exist — it is that almost nobody publishes it in enough detail for you to judge.

This guide does the opposite: what a chain’s data problem actually is, what the architecture that solves it looks like, and what to ask whoever wants to build it — anchored in the one build we can describe by name and stack.

---

Who can build a data warehouse for a restaurant chain?

The honest answer has two halves.

First: very few firms can show you a finished build at franchise scale. There is no shortage of talent that can build a warehouse. What is scarce is having done it against the specific mechanics of a chain — dozens or hundreds of locations, master data that does not match across systems, inventory counted by hand, a corporate ERP that was never designed to answer operational questions.

Second: we did it. Clarivant built the complete modern data stack, from the ground up, for Grupo AFAL — the Carl’s Jr Mexico franchise operating 100+ locations. The franchise was running on Oracle EBS and fragmented operational reports: executives could not see the supply chain in real time, and every department kept its own version of the numbers. We migrated Oracle EBS to Snowflake, built the dbt models for governed transformation, delivered the executive layer in Tableau, and left automated pipelines and governance frameworks throughout. The result: a single source of truth across all 100+ locations, with real-time supply chain visibility for executives — enterprise data infrastructure where there was none, built from scratch and handed over.

That said, this is not a brochure. If you end up hiring someone else, what matters is that you know what you are buying. So read on with the demand list in hand — it is at the end.

---

What problem does a warehouse solve in a restaurant chain?

The problem is not missing data. It is that every system knows one piece of the truth and none of them knows all of it.

In a typical chain, the information lives like this:

• The point of sale, location by location: tickets, product mix, average check, dayparts, channel (counter, drive-thru, delivery aggregator). It knows what was sold. It does not know what it cost.
• The corporate ERP: purchasing, accounts payable, payroll, accounting. It knows what was paid. It does not know at what hour or in which location it turned into a sale.
• Inventory and supply chain: purchase orders, receipts, transfers between locations, waste, physical counts, the distribution center. It knows what moved. Almost always days late.
• Labor and operations: schedules, clock-ins, incidents. It knows who was there. It rarely connects to the sales they produced.

Every department pulls its own report from its own system, and each one is correct in isolation. The problem shows up in the meeting: three revenue figures for the same month that do not tie, and half an hour spent deciding which one to use instead of deciding what to do.

Underneath that symptom are three structural problems a warehouse genuinely fixes:

1. There is no single definition. “Sale” may or may not include tax, discounts, voids, aggregator commissions. “Location” can carry three different keys in three systems. “Day” may cut at midnight in one system and at cash-out in another — and in a chain that sells late, that difference moves serious money from one day to the next.

2. Nobody can join across sources. Without a common place, the most useful question a chain has — which locations are losing margin, and is it input cost, waste, product mix, or labor productivity? — cannot be answered at all. Every piece of the answer sits in a different system.

3. Consolidation is manual, so it is slow and fragile. Someone exports, someone pastes, someone adjusts. The monthly report arrives too late to act on the month, and it depends on one person who will eventually leave.

A data warehouse fixes all three: one place where every source lands, is cleaned, and is modeled under definitions agreed once, with automated processes instead of hands.

---

What does the architecture look like?

The Carl’s Jr build had four layers, and that structure repeats in almost any chain:

Ingestion. Automated pipelines that pull data from each source system into the warehouse on a fixed schedule, with nobody exporting anything. In the published build, the source we migrated was Oracle EBS.

Storage. Snowflake as the warehouse: one place where the full history lives together, separated from the transactional systems so heavy queries never compete with live restaurant operations.

Governed transformation. dbt is where the numbers get defined. Each metric is written once, in code, with visible lineage: you can trace any figure on the executive dashboard back to the source table that produced it. This is where “single source of truth” stops being a slogan and becomes a verifiable property of the system.

Consumption. Tableau as the executive layer: the dashboards where leadership sees the business.

Cutting across all four are two things that do not appear in the diagram but decide whether the warehouse survives: automated pipelines — the system refreshes itself, not because someone remembers — and governance frameworks: who can see what, who can change a definition, and how you find out a number arrived wrong before it reaches a dashboard.

Worth saying what the stack does not determine. The architecture above is a good, proven choice, not the only possible one. What is non-negotiable in any chain is the shape: automated ingestion, storage separated from operations, transformation in code with lineage, and a consumption layer leadership opens on its own.

---

What visibility do you get?

In the Carl’s Jr build, the published result is concrete: a single source of truth across all 100+ locations, with real-time supply chain visibility for executives.

Both matter more in a chain than they sound.

A single source of truth means the figure in the board meeting and the figure the location manager sees come from the same definition. It sounds administrative and it is operational: when every department brings its own version, meetings get spent reconciling and decisions get deferred.

Real-time supply chain visibility is the difference between finding out and reacting. In food service, inventory is perishable and margin is set at the input: a stockout of a high-turn item across twenty locations on a Friday is not a line in Monday’s report, it is a decision today.

That focus was not an accident. The Grupo AFAL engagement began as a supply-chain KPI build and grew from there into the full platform — a good pattern: start with the most expensive pain and let the architecture grow on top of something that already proved its value.

---

What to demand from whoever builds it

Whoever you hire, these are the demands that apply specifically to a chain:

• Make them describe a finished build. Source system, stack, number of locations, what was handed over. Anyone who has done it tells you in five minutes; anyone who has not answers with “experience in the retail sector”.
• Make sure they speak your operation. Waste, physical counts, inter-location transfers, an operating day that does not cut at midnight, master data that does not match between POS and ERP. If you have to explain that from scratch, you will pay for it in weeks.
• Fixed price and fixed date, after a paid scoping exercise. Nobody serious quotes a build without seeing your systems — and an open-ended hourly rate puts scope risk on your side of the table.
• The validation method must exist before you sign. The worst outcome of a migration is not that the new system fails: it is that it works and produces slightly different numbers than the old one, and nobody knows which is right. Ask for the method, and ask for one real discrepancy they found on a past engagement.
• The platform has to end up yours. Code in your repository, data in your cloud, credentials in your name, documentation in your team’s language, automated tests that fail on their own when data arrives wrong. Anything else is a dependency dressed up as a delivery.
• Know who does the work, by name. Not an org chart: names and time allocation, written into the proposal.

All six are worked out in full — including how a serious answer sounds and how an evasive one sounds — in Questions to Ask Before Hiring a Data Consultancy. Use it with anyone.

---

Where to start

Not by choosing a tool. By counting.

Before anyone can quote a build without guessing, someone has to establish how many sources exist, what shape they are in, which reports exist today and which are actually used, and how consistent master data is across systems. That exercise, with its own scope and date, is the data stack diagnostic: two to three weeks inside your real stack, ending in an inventory, a findings document verifiable against your own sources, and a fixed-price, fixed-date quote for the build.

The other path is equally valid — the one Grupo AFAL took: start with one bounded operational pain, in their case supply-chain KPIs, and let the platform grow on top of something that already earned its keep.

What does not work is starting by buying licenses.

If you operate a chain and recognized the problem, here is how we work with retail and supply chain, and a strategy call is enough to find out whether your situation looks like the one we already built. And if you end up hiring someone else, the demand list above is still yours.