Why comparing Microsoft Fabric data stores never gets you to a decision – and the six questions that do.
Somebody on your team asks a reasonable question at the start of a Fabric project: where should the data actually live?
There are four answers. A SQL Database. A Warehouse. A Lakehouse. An Eventhouse.
Every one of them sounds correct. Every one of them is genuinely good at something. And not one of them tells you what it is bad at.
So you go looking for help. And here is where it gets frustrating, because the help is real, it is accurate, and it does not work.
Why the comparison tables don’t help
Every guide you can find on this – including Microsoft’s own, which is thorough and well maintained – is organised the same way: by product. A column for each store, and a long list of capabilities running down the side. Volume. Data types. Compute engines. Ingestion patterns. Transaction support.
To use a table like that, you have to already know which of those rows matter to you.
And if you knew that, you would have decided already.
So you read all forty rows, you feel better informed, and you are exactly as stuck as when you started. Which feels like the document was missing something – so you go and find another one. That one is organised by product too.
I want to be direct about why this happens, because it is not anyone’s failure. Microsoft can document what a Lakehouse does. Microsoft cannot know that your source system emits nested JSON, or that your team has no Spark skills, or that finance restates the ledger every March. Those are the facts that actually decide this, and every one of them lives inside your organisation.
The guidance is organised by product. Your decision is organised by workload. Those are different shapes, and no amount of reading closes the gap.
Turn the table on its side
So stop starting at “what is a Lakehouse” and working towards yourself.
Start with what you have and what it has to do. Six questions about your data. The store falls out at the end as a consequence rather than a starting point.
I am not going to define the four stores in this article. If the method works, you will not need me to.
The six questions – and why the order matters more than the questions
Three of them are constraints. Shape of the data. Volume and velocity. Whether it changes after it lands. These are physics. No amount of skill, budget or preference moves them, and they eliminate options outright.
Three of them are preferences. How it will be read. Who writes to it. How it joins the rest of the estate. These are real and they matter, but they are negotiable – each one costs you something rather than ruling something out.
Ask the constraints first. The preferences last.
Here is why that ordering matters more than anything else in this article. Most teams start at question five – who writes to it – because it is the one they have an opinion about. “We’re a SQL team.” That is not a starting point. That is a preference, and starting there means you spend the whole design conversation defending a conclusion you reached in the first thirty seconds.
Constraints first, and then you are choosing between options that are all genuinely possible.
Constraint 1 – What shape is the data?
Not what types a store supports. What have you actually got?
Rows, columns and keys. Clean relational tables from an ERP or a line-of-business system. Nothing is eliminated. Keep going.
JSON, XML, nested payloads. An API feed, an event body, a config blob. Relational stores can hold this, but parsing becomes the job rather than a step in the job. They do not stop working. They get expensive.
Files, documents, images, audio. Contracts, scans, clinical notes, call recordings. One survivor, and the question is over. Everything after this is confirmation rather than decision.
The mistake I see most often here is answering for today. Ask what lands in eighteen months, not what landed last week. Almost every team I have worked with answered “structured” honestly and correctly – and then a roadmap item arrived with a document attached, and the answer had already changed. If there is a PDF, an image or an event payload anywhere on the plan, your answer today is not “structured.”
Constraint 2 – How much is there, and how fast does it arrive?
Microsoft is specific here, and the numbers are worth knowing:
- Small – up to a few hundred GB in total, individual tables up to roughly 100 GB, up to tens of GB landing per day.
- Big – tens of TB to PB in total, tables measured in TB, hundreds of GB landing per day.
- Moderate – everything in between, which is where most enterprise workloads honestly sit.
This question eliminates. It never selects. There is exactly one store with a real ceiling, and at big-data scale it drops out. That is the entire yield.
I am flagging it because it is the question teams spend twenty minutes on. It feels like the most important one – it is measurable, it is arguable, it has numbers attached. It is nearly the least useful.
Constraint 3 – Once it lands, does it change?
This is the question nobody asks in month one and everybody regrets in month twelve.
Never. Data arrives and is only appended to. Events, telemetry, logs, immutable transactions. Appends are efficient everywhere, so you keep the widest choice.
Occasionally. Late-arriving facts, the odd correction, a restatement at quarter end. Workable – but understand what “an update” means on a lake-based store. It is not an edit. It is a file rewrite. Five rows change, and the engine rewrites whole files to satisfy it.
Constantly. Type-2 dimensions with real history, GDPR deletion processes, a finance ledger that gets restated. This pushes you hard towards a relational store, because otherwise rewrite volume becomes the thing that defines your nightly window.
I want to be concrete about how this fails, because it is not dramatic. Nothing breaks. Your pipeline runs and your report renders. It shows up eleven months in as a nightly job that started at four minutes and now takes fifty and nobody can point to the change that caused it, because there wasn’t one. The workload simply grew into a shape the store was never good at.
That is why this is a constraint and not a preference. You cannot skill your way out of it.
Preference 4 – How will it actually be read?
One row, fast. An application, an API, a lookup service, a human waiting on a screen. This is a point lookup, and it wants an indexed store. Subsecond, or the feature is broken.
Millions of rows, aggregated. A report, a semantic model, a data science job. This is a scan, and analytics engines are built for exactly this.
Both, on the same data. Entirely legitimate, and more common than people admit. The answer is not to find one store that does both adequately. It is to serve the selective slice from one store and the scans from another.
Almost every “why is Fabric slow” conversation I get pulled into starts here. Somebody pointed an application-shaped query at a scan-shaped engine, and then tried to fix it with capacity. Capacity does not fix a shape mismatch. It makes the wrong answer more expensive.
Preference 5 – Who writes to it every day?
Not who queries it. Who builds it.
Reading is generous in Fabric. A Lakehouse can be read with Spark, with T-SQL through its SQL analytics endpoint, and with KQL. Writing is not generous at all – each store has essentially one write path.
Which produces a sentence I have heard in real meetings: “we can query a Lakehouse with SQL, so our SQL team can own it.” They can query it. They cannot build it. Those are different people on different days.
And note this is question five, not question one. It is a staffing question wearing a technical hat. If you let it lead, you will pick the store your team likes and then spend two years explaining why the workload keeps fighting it.
Preference 6 – How does it join the rest of the estate?
Direct Lake for Power BI. Data arriving by shortcut. Cross-item queries across the workspace.
This is the most solvable of the six. Almost every integration requirement has a route, because the whole platform sits on one lake.
Which is exactly why it goes last. It is the question with the tidiest architecture diagram, and letting it lead is how teams end up with a beautiful drawing and the wrong engine underneath it.
Name what you gave up
One more step, and skipping it is how a decision becomes an argument six months later.
Say out loud what you traded away:
- Chose Warehouse? You gave up unstructured data, Spark, and subsecond point lookups.
- Chose Lakehouse? You gave up multi-table transactions and a low-maintenance platform.
- Chose SQL Database? You gave up scale. There is a ceiling and you will meet it.
- Chose Eventhouse? You gave up efficient updates and column-level security.
A decision you cannot state the cost of is not a decision. It is a preference you have not examined yet — and the difference shows up the first time somebody challenges it in a design review.
How to know you are done
No document tells you when to stop, and I think that is the real reason the searching never ends. There is no closure, so you keep looking for a guide that feels final.
Here is the stopping rule.
You are done when:
- You can state your workload in six answers.
- You can name what you gave up.
- You can say which answer decided it.
Three things. If you have all three, stop reading and go build.
You are not done when you are still comparing stores rather than describing work, or one of your six answers is “it depends,” or – the honest one – you are looking for a document that agrees with a decision you have already made.
If an answer is missing, go and get that answer. Do not go and get another guide. And notice that the missing answer is almost never something a document can give you. It is a conversation with the source system owner, or the application team, or whoever will be on call for this at 2am.
That is why the searching never ends. You are searching for an answer that lives inside your own organisation.
So what is the comparison matrix for?
I am not telling you to ignore it. Microsoft publishes a genuinely excellent capability matrix for these four stores in the Azure Architecture Center, covering ten capability groups right down to which ingestion tools work with which store. If you have never come across it, it is worth an hour.
It is not a decision tool. It is a verification tool.
Once the six questions have given you an answer, that is when you open it — to check the detail, and to find the footnote that changes your design. Lakehouse has no multi-table transactions. Eventhouse has no column-level security. Lakehouse security is enforced at the SQL analytics endpoint, which means a Spark user and a SQL user are governed differently.
Decide with the questions. Verify with the matrix. In that order.
Reversing them is how this got confusing in the first place.
Free workload profile card – six blanks. Fill them in and the answer is determined. No email, no signup. [link]
Watch the full walkthrough on the TheBIMasters YouTube channel, where I take four real client-shaped scenarios through all six questions. [link]
Prathamesh Sable is a Microsoft Certified Trainer and Head of Fabric CoE at Exponentia.ai, based in London. He writes about Microsoft Fabric and Power BI at thebimasters.pro.
Leave a Reply