Complete Guide to Data Lakehouse Architecture

The running one is a set of pipelines. The finished one is an architecture: layers that each have a stated job, a stated owner, and a stated rule for what is not allowed in them.

This is the architecture reference. It assumes you already know what a lakehouse is. What follows is how one is built, layer by layer, on the Microsoft stack — and which parts you will not be able to change later without paying twice.

A lakehouse architecture has seven layers: object storage holding open-format files; a table format and transaction log providing ACID; an ingestion layer; a curation layer (medallion Bronze–Silver–Gold); a semantic and serving layer; an orchestration layer; and governance and observability running vertically across all of them.

The two decisions that dominate query performance and cost are file sizing and partitioning strategy. Start with file size — the most common performance defect in an estate that "went live and then got slow". Every small file carries a fixed cost: a metadata entry, a read request, a scheduling decision. A Gold fact split across 40,000 tiny files is slower and more expensive to read than the same data in 400 well-sized ones. Fabric Spark's adaptive target scales with the table (128 MB under 10 GB, capping at 1 GB above 10 TB); Microsoft targets 400 MB to 1 GB with row groups of 8 million rows or more for Direct Lake. If your Gold layer serves Direct Lake, size for Direct Lake.

V-Order is a separate, often misunderstood control — a write-time optimisation reorganising Parquet row groups, disabled by default in new workspaces, costing roughly 15% in write time but delivering 40–60% on cold-cache Direct Lake reads. Leave it off for high-churn Bronze and Silver; turn it on for Gold tables feeding Power BI. Partitioning is where good engineers over-engineer: published Delta guidance is not to partition tables under 1 TB, and to keep any partition above 1 GB. Most mid-market fact tables never reach either threshold — partitioning a 40 GB sales fact by date produces thousands of tiny partitions and makes everything slower. For data skipping on a high-cardinality column, liquid clustering or ZORDER is the better tool.

A lakehouse is a data lake plus a transaction log. Delta Lake writes an ordered set of JSON commit files to a _delta_log folder alongside the Parquet data; files are invisible to readers until the log records a new version. Writes proceed in three stages — read the files to modify, stage new data, then validate and commit — and if a concurrent commit conflicts, the write fails rather than corrupting the table. Four capabilities follow, each with a design consequence:

• Schema enforcement and evolution. Adding columns is automatic with mergeSchema; renaming, dropping or retyping requires column mapping or a full rewrite. Decide early whether column mapping is on — retrofitting it is itself a rewrite.
• Time travel. Governed by retention: log retention defaults to 30 days, deleted-file retention and VACUUM to 7 days. A 90-day reproducible answer means setting both deliberately and budgeting storage — otherwise a routine VACUUM quietly removes the ability to reproduce last quarter's number.
• Change data feed. The cleanest basis for incremental Silver and Gold builds — but not enabled by default, and it only records changes made after it was enabled. Turn it on at table creation, not when you finally need incremental loads.
• Open format. OneLake virtualises metadata between Delta and Iceberg V2 both ways, with constraints: 5s–2min conversion latency, under 5,000 commits, updates less than once every 2 minutes.

Four patterns cover almost every source: scheduled batch, log-based CDC through mirroring, event streaming for OT, and shortcuts that reference data in place. The architectural mistake is defaulting to batch for everything because it is the pattern the team knows.

Mirroring is the pattern most estates under-use — it continuously replicates a source database into OneLake as Delta, as often as every 15 seconds, across SQL Server, Oracle, SAP, Snowflake, PostgreSQL and more, with replication compute not consuming capacity units and 1 TB free mirroring storage per capacity unit. Shortcuts are the second under-used pattern — a pointer, not a copy, to ADLS Gen2, S3, GCS, Dataverse or on-premises stores; if you already hold ten years of history in ADLS Gen2, referencing it costs nothing in duplication. Batch remains correct for distributor files and APIs without a change feed — land it in Bronze exactly as received and keep the original file. Streaming applies to MES, SCADA and MQTT telemetry — but be honest about whether the decision window really is seconds; most "real-time" requirements in manufacturing are 15-minute requirements, and 15 minutes is far cheaper to operate.

The single most valuable discipline: write down the grain of every Gold fact table as a sentence — "one row per delivery line per confirmation event" — and refuse any measure that does not work at that grain. This is what separates a governed lakehouse from a data swamp with better branding.

Direct Lake is a Power BI storage mode that loads columns into memory directly from Delta tables, refreshing by framing metadata in seconds rather than copying data. It removes the import-and-refresh cycle that causes most Power BI capacity incidents, but carries guardrails you must design against: an F64 allows 5,000 Parquet files, 1,500 million rows and 25 GB of memory; an F32 allows 1,000 files and 300 million rows. Exceed them and Direct Lake on OneLake fails the refresh, while Direct Lake on SQL falls back to DirectQuery and simply gets slow. That file-count guardrail is why storage-layer compaction discipline is a serving-layer concern.

Two further constraints shape design: Direct Lake supports no calculated columns or tables, so derived attributes belong upstream; and Direct Lake on SQL falls back to DirectQuery when a table is built on a SQL view or when SQL-based access control applies — the usual cause of "fast in UAT, slow after go-live", because RLS was only switched on at go-live. Choose a Fabric Warehouse when you need writes: INSERT, UPDATE, DELETE, stored procedures and multi-table ACID transactions — finance adjustment tables, allocation runs, manual overrides. A lakehouse SQL analytics endpoint can do none of those; it is query-only.

Orchestration is dependency management, not scheduling. Time-based schedules break the moment a source lands late; what you want is a dependency graph. Data Factory pipelines handle sequencing and failure branches; Materialized Lake Views let you declare a Silver or Gold view in Spark SQL with a data quality constraint attached (CHECK ... ON MISMATCH DROP) and report dropped-row counts in lineage — dependency, transformation and data quality in one artefact.

Governance: access control in OneLake is deny-by-default and layered — workspace roles, item permissions, then OneLake security roles with folder- and table-level RBAC plus RLS and CLS. Note the asymmetry: multiple RLS roles combine as a union (least restrictive), while column-level security intersects (deny). Above that, Purview supplies the catalogue, sensitivity labels, DLP and audit logging. A workspace belongs to exactly one domain, which is why domain design and workspace design are the same decision. Observability and cost: Fabric bills capacity, not queries, and smooths usage; beyond roughly 10 minutes of borrowed future capacity come 20-second interactive delays, then rejection. Install the Capacity Metrics app in week one, not month three.

Everything above can be tuned. These four cannot, cheaply:

• Fact grain. Change the grain of a Gold fact and every measure, relationship and report changes with it; restatement is manual. Declare the grain in writing and choose the finest the business genuinely asks questions at — you can always aggregate upward.
• Partitioning strategy. Changing partition columns on a large Delta table means a full rewrite. Given the 1 TB / 1 GB thresholds, the safest default is: do not partition until you have measured that you need to.
• Workspace and domain topology. A workspace belongs to exactly one domain. Splitting Bronze/Silver/Gold across workspaces later means moving items, reissuing permissions and repointing shortcuts. Decide the topology before the second workspace exists.
• Table format. Delta or Iceberg is a ten-year decision about which engines read your data natively. Pick the format your primary compute treats as first-class, and treat the interoperability layer as interoperability, not a migration strategy.

A lakehouse does not fix master data — if the same customer exists four times across ERP, CRM and a distributor system, the lakehouse faithfully stores four customers. Conformance in Silver needs survivorship rules and a named data steward; it is a running process, not a migration task. Mirrored and shortcut data is read-only — corrections happen at source or in a deliberate override table. Change data feed is not retrospective — if it was not enabled before the change happened, that change is gone.

Time travel is only as long as your retention settings — defaults of 30 days log and 7 days deleted-file retention will not satisfy a seven-year audit, and VACUUM enforces that quietly. The architecture cannot survive an unsponsored programme — if nobody senior will retire the legacy report, you now have two numbers and a recurring argument. And capacity is a shared resource: one unoptimised Spark job over full history can throttle interactive reports for every user for the rest of the hour, so architecture includes workload isolation.

Before any platform work, answer these five in writing:

• What is the grain of the first Gold fact table, stated as one sentence?
• Which sources support log-based change capture through mirroring, and which are file drops you will have to schedule?
• What data do you already hold in ADLS Gen2, S3 or Databricks that should be referenced by shortcut rather than copied?
• What is the longest period over which you must reproduce a number exactly — and does your retention configuration support it?
• Which single operating decision changes if this data arrives daily instead of monthly?

If question five has no answer, do not build the platform yet. If it does, that answer is your first slice. We design and build these on Microsoft Fabric, OneLake and Power BI — first value in six weeks: one working slice, modelled properly, not a reference architecture slide.