When I was designing my data pipeline in Microsoft Fabric, one of the first and most important decisions I had to make was this:
Should I store my data in a Lakehouse or a Warehouse?
On the surface, both seem like valid options. But once I looked at the specifics of what my pipeline was actually doing, the answer became clear. And the reasoning behind it taught me a lot about how these two storage paradigms are fundamentally different — and when each one makes sense.
Here's the full breakdown of my thought process.
First, a Quick Recap — What's the Difference?
Before diving into my decision, let me quickly set the context.
Microsoft Fabric Warehouse is a fully managed, SQL-first data store. It works best when your data is structured, your schema is well-defined upfront, and your workload is primarily read-heavy analytics. Think: BI dashboards, aggregated reports, clean dimensional models.
Microsoft Fabric Lakehouse is a more flexible storage layer built on top of OneLake (Delta format). It can store structured, semi-structured, and unstructured data. You can query it with SQL via the SQL analytics endpoint, but it doesn't require a rigid schema at write time. Think: raw files, JSON dumps, logs, diverse data formats all living together.
Now let's talk about why Lakehouse won for my use case.
Factor 1: I Was Dealing with High Data Volume
My pipeline was calling multiple APIs — and doing so continuously. Every API call returned a response, and those responses needed to be stored. Across all the endpoints and the frequency of calls, the volume of data being written was significant and growing.
Warehouse in Fabric works well for structured analytical queries, but it comes with real compute costs every time you write, transform, or query data. At high volumes, that cost compounds quickly.
Lakehouse, on the other hand, stores data as files in OneLake — and file storage is cheap. Writing large volumes of raw data to a Lakehouse doesn't trigger compute the way Warehouse operations do. You write the files, they land in storage, and compute only kicks in when you actually query them.
For a high-volume ingestion scenario, this was a meaningful advantage.
Factor 2: My Workload Was Write-Heavy
Most discussions about data storage focus on reads — dashboards, queries, reports. But my pipeline's primary job was writing — constantly ingesting API responses, writing logs, capturing metadata.
Warehouse in Fabric is optimized for structured reads. Frequent, high-throughput writes — especially to many different tables or with variable schemas — create overhead and can become a bottleneck.
Lakehouse is naturally suited for write-heavy workloads. Writing a file to a Lakehouse folder is simple, fast, and doesn't require a predefined table schema. You just drop the file and move on. No DDL changes, no schema migrations, no blocked writes waiting on locks.
For a pipeline that's primarily producing data rather than consuming it, Lakehouse was the right fit.
Factor 3: My Source Data Was JSON — And I Needed to Store It As-Is
This was perhaps the most decisive factor.
Every API I called returned a JSON response. And a key requirement for my bronze layer was this:
Store the raw API response exactly as received — no transformation, no modification, no flattening.
This matters for two reasons:
- Future analysis — Raw JSON preserves every field the API returned, even ones I didn't think I needed at ingestion time. If my downstream logic changes, the original data is still there.
- Issue handling — When something goes wrong, I want to go back to the exact payload the API sent me. A transformed or flattened record loses that fidelity. The raw JSON is the ground truth.
Warehouse doesn't handle this well. It's a relational store — it expects rows and columns. Storing raw JSON blobs in a Warehouse is technically possible, but it's awkward, schema-unfriendly, and goes against the grain of what Warehouse is designed for.
Lakehouse handles this naturally. I simply saved each API response as a .json file into the appropriate folder in the Lakehouse. No schema required. No transformation needed. The file lands exactly as the API returned it.
lakehouse/
└── bronze/
└── api_name/
└── 2024-03-15/
├── response_001.json
├── response_002.json
└── response_003.json
This folder-per-source, date-partitioned structure is clean, navigable, and exactly what a bronze layer should look like — raw, unmodified, organized.
Factor 4: I Needed Flexibility Across Multiple APIs
I wasn't calling one API with a consistent schema. I was calling multiple APIs, each with its own response structure, field names, nesting depth, and data types.
With a Warehouse, you'd typically define a table per source — which means a schema per source — which means schema management becomes a project in itself. Every time an API changes its response format, you potentially need to alter a table, handle nulls, or deal with breaking changes.
With a Lakehouse, none of that applies at write time. Each API's responses go into their own folder. The schema is inferred or defined later, when you actually need to query the data. This separation of ingestion from schema definition is one of the core strengths of the lakehouse architecture.
It gave me the freedom to onboard a new API source without any infrastructure changes — just a new folder and a new pipeline run.
Factor 5: Compute Costs for Warehouse Didn't Justify My Use Case
Fabric Warehouse runs on compute capacity — every query, every write operation, every transformation consumes capacity units. For analytics workloads where you're running a handful of expensive queries for a dashboard, that's a reasonable trade-off.
But for my pipeline, the primary operation was ingestion — writing raw data, writing logs, tracking metadata. The downstream analytics would come later and would be infrequent. Paying Warehouse-level compute for what was essentially a file-writing operation didn't make economic or architectural sense.
Lakehouse let me ingest freely. When I did need to run queries — for validation, for debugging, for building silver-layer transforms — I used the Lakehouse's SQL analytics endpoint, which is efficient and on-demand.
The Trade-offs I Accepted
Choosing Lakehouse wasn't without compromise. Here's what I gave up:
| What I Gave Up | Why It Was Acceptable |
|---|---|
| Native SQL write semantics | I was writing files, not rows — SQL writes weren't needed |
| Enforced schema at ingestion | Schema enforcement is a silver/gold layer concern, not bronze |
| Easier BI tool integration (direct) | SQL endpoint still supports Power BI and Fabric notebooks |
| ACID guarantees at write time | Delta format in Lakehouse still provides ACID on reads |
None of these trade-offs were dealbreakers for my use case. The bronze layer is not the place for strict schemas or governance — that comes later. Bronze should be raw, faithful, and cheap to write to.
How This Fits the Medallion Architecture
This decision fits neatly into the medallion architecture pattern:
- Bronze (Lakehouse): Raw JSON files, exactly as received from APIs. No transformation. Partitioned by source and date.
- Silver (Lakehouse): Cleaned, flattened, typed data. Schema enforced here.
- Gold (Warehouse or semantic model): Aggregated, business-ready data for reporting and analytics.
The key insight is that different layers can use different storage types depending on what they need to do. Lakehouse is ideal for bronze because of its flexibility and low write cost. Warehouse becomes more appropriate as data becomes more structured and query patterns become more defined.
Final Thoughts
If you're building a pipeline in Microsoft Fabric and wondering which to choose, here's a simple heuristic:
If you're writing raw, varied, or semi-structured data at high volume — start with Lakehouse.
If you're serving structured, well-defined data to BI tools and analysts — Warehouse is worth the compute.
For my pipeline — multiple APIs, write-heavy ingestion, raw JSON responses, high volume, and a need for bronze-layer fidelity — Lakehouse was the clear winner.
It let me store data the way it arrived, at the scale it arrived, without fighting the storage layer to do it.
And when it came time to transform and query that data, Lakehouse had the tools to support that too.
Top comments (0)