Lakehouse or warehouse? The Azure architecture patterns that actually hold up

Most mid-market data platforms are not badly built — they are built twice. Here is how the Azure lakehouse patterns actually work, the medallion architecture, when a lakehouse genuinely beats a warehouse, and the anti-patterns we get called in to unpick.

Most mid-market data platforms are not badly built. They are built twice. A warehouse goes in for the finance reporting. Then a data lake goes in next to it for the “big data” project that someone was promised. Two years later you are paying to keep both, moving data between them, and nobody can say which number is the real one.

The lakehouse pattern exists to stop that happening. It is not a product you buy. It is a way of laying out storage, compute and governance so that one copy of the data serves reporting, engineering and AI without being copied three times first. On Azure you can build it with Fabric and OneLake, with Synapse, or with Databricks. The pattern matters more than the badge. Here is how the patterns actually work, when a lakehouse genuinely beats a warehouse, and the anti-patterns we are called in to unpick.

What a lakehouse actually is (without the marketing)

A warehouse stores structured, cleaned tables optimised for fast SQL. A data lake stores raw files of any shape, cheaply, but with no guarantees about quality or structure. For fifteen years you had to pick one and bolt the other on.

A lakehouse puts a table format, Delta Lake on Azure, over cheap lake storage. That format adds the things a warehouse gave you and a raw lake never did: transactions, schema enforcement, time travel, and the ability to update and delete rows reliably. The result is one storage layer that behaves like a warehouse when you query it and like a lake when you land raw data into it. In Fabric that storage layer is OneLake, and the Delta tables in it are queryable directly by Power BI through Direct Lake, by Spark, and by T-SQL, without copying.

That last point is the whole argument. The value of a lakehouse is not the technology. It is the number of copies of your data you no longer have to keep in sync.

The medallion pattern: bronze, silver, gold

The pattern that holds up in production is the medallion architecture, and it is worth knowing because almost every good Azure data platform is some version of it.

Bronze is raw, landed exactly as it arrived from the source system, with no cleaning and no joins. It is your audit trail and your replay button. If a transformation is wrong, you reprocess from bronze rather than re-extracting from a production system that may no longer hold the old values.

Silver is cleaned, conformed and de-duplicated, the version an engineer trusts. Types are enforced, keys are resolved, and business rules are applied once, consistently, in one place.

Gold is the business-ready layer: aggregated, modelled and shaped for a specific consumer, whether that is the finance mart, the sales semantic model, or the churn feature table. This is what Power BI and the AI models actually read.

The discipline that makes this work is simple and constantly broken: transformations only ever move data downstream, from bronze to silver to gold, and never sideways or backwards. The moment someone writes a quick fix that patches a gold table directly, you have lost your lineage and your ability to reproduce the number, which is the whole reason you built layers in the first place.

When a lakehouse genuinely beats a warehouse

Be honest about this, because a lakehouse is not always the right answer for a mid-market business.

A lakehouse earns its place when you have more than one kind of workload on the same data, reporting and data science, or structured tables and semi-structured event data, and you do not want to maintain separate copies for each. It earns its place when data volume or variety is genuinely growing, because lake storage is cheap and scales without a licensing cliff. And it earns its place when you want one governed copy that Power BI, Spark and SQL all read, rather than a warehouse feeding a lake feeding a model.

A plain warehouse is still the better call when your data is structured, stable and modest in size, your only real consumer is BI, and your team is comfortable in SQL and nothing else. Putting a lakehouse under a business that only needs a well-modelled star schema is over-engineering, and we will tell you so. The number of mid-market firms sold a lakehouse they did not need is not small.

The three anti-patterns we get called in to fix

The data swamp. Everything lands in bronze, nothing is ever promoted to silver with real rules, and six months later the lake is a landfill nobody trusts. A lakehouse without an enforced silver layer is just an expensive lake. The fix is governance at the silver boundary, not more storage.

The accidental copy factory. Direct Lake exists so Power BI can read Delta tables without importing them. Teams ignore it, import everything into the model anyway, and now there are two copies drifting apart, the one thing the lakehouse was meant to prevent. If you are on Fabric and importing your gold tables into Power BI, you have quietly rebuilt the problem you paid to remove.

Layer collapse. Bronze, silver and gold all live in one workspace with no boundaries, permissions or naming discipline, so raw and business-ready sit side by side and get confused. Within weeks someone reports off a bronze table by accident and presents unvalidated data to the board. Layers are only useful if they are actually separated and governed.

Fabric, Synapse or Databricks for the lakehouse?

All three implement the same underlying idea, Delta tables over cloud storage, so the choice is about fit, not capability.

Fabric and OneLake is usually the right call for a Microsoft-centric mid-market business already living in Power BI. One tenant, one bill, Direct Lake into Power BI, and governance that your existing team can actually run. It is the lowest-friction path to a governed lakehouse if you are not doing heavy engineering at scale.

Databricks is the right call when data engineering and machine learning at real volume is the core of the work, large Spark jobs, ML pipelines, teams who live in notebooks. It is more powerful and more demanding, and for most mid-market reporting needs it is more platform than the problem requires.

Synapse is increasingly the legacy answer; Microsoft’s own direction of travel points new build towards Fabric. If you are already on Synapse and it works, there is no crisis, but new lakehouse projects rarely start there now.

The right question is not which is best. It is what workloads do we actually run, and which of these fits the team we have. We have written a fuller side-by-side on that comparison, and it is worth reading before anyone signs a licence.

Frequently asked questions

Is a lakehouse always better than a data warehouse?

No. A lakehouse is better when you have mixed workloads (BI plus engineering or data science), growing volume or variety, and a desire for one governed copy of the data. For a business with structured, stable data and BI as the only consumer, a well-modelled warehouse is simpler and cheaper. Choosing a lakehouse you do not need is one of the more common ways mid-market platforms end up over-engineered.

What is the medallion architecture?

It is a layered pattern, bronze (raw), silver (cleaned and conformed), gold (business-ready), where data only ever moves downstream. It keeps your raw data replayable, applies business rules once in the silver layer, and shapes gold tables for specific consumers like Power BI. It is the pattern behind most well-built Azure lakehouses.

Do we need Databricks to build a lakehouse on Azure?

No. Microsoft Fabric with OneLake gives most mid-market businesses a governed lakehouse using Delta tables, with Direct Lake connectivity into Power BI, without the operational demands of Databricks. Databricks is the stronger choice when heavy Spark engineering or machine learning at scale is the core workload.

What is Direct Lake and why does it matter?

Direct Lake lets Power BI read Delta tables in OneLake directly, without importing or copying the data into the model. It matters because it removes the extra copy of data that traditional import mode creates, which is precisely the duplication a lakehouse is meant to eliminate. Importing your gold tables anyway quietly reintroduces the problem.

Hopton Analytics designs and builds governed Azure data platforms for UK mid-market businesses, lakehouse and warehouse architectures that hold up in production, not just in the demo. If you are weighing a lakehouse and want a straight answer on whether you need one, book a free audit.