Microsoft DP-700 Fabric Data Engineer Deep Dive: Lakehouse and warehouse architecture — From Fundamentals to Exam Scenarios

 

Lakehouse and warehouse architecture is one of the places where DP-700 stops being a product-recognition exam and becomes an architecture exam. Microsoft currently describes the Fabric Data Engineer role around data loading patterns, data architectures, orchestration, security, monitoring, and optimization. The skills measured as of July 21, 2026 give roughly equal weight to implementing and managing an analytics solution, ingesting and transforming data, and monitoring and optimizing it. That means storage design cannot be studied in isolation. A choice between a lakehouse and a warehouse changes how data is loaded, transformed, secured, queried, maintained, and exposed to downstream analytics.

For exam preparation, the most useful question is not “What is a lakehouse?” or “What is a warehouse?” It is “Given the data shape, user skills, workload pattern, governance requirements, and performance expectations, which Fabric architecture creates the fewest unnecessary compromises?” That decision-oriented approach is more durable than memorizing feature lists. To connect architecture decisions to the broader blueprint, use the DP-700 readiness matrix to test whether storage design is a genuine strength or just familiar terminology.

Start with the workload, not with the storage label

A Fabric design should begin with the work the platform must support. Ask what arrives, how quickly it arrives, how much transformation is required, how consumers query it, and which team owns the data after ingestion. A lakehouse is usually attractive when engineers need flexible file-oriented storage, Spark or notebook processing, large-scale transformation, mixed structured and semi-structured data, and a direct relationship with OneLake. A warehouse is usually attractive when the workload is strongly relational, the team is comfortable with T-SQL, dimensional models are central, and business-facing analytical queries need predictable SQL semantics.

Those are tendencies, not rigid rules. Fabric deliberately reduces the old boundary between data-lake and warehouse worlds. Delta tables can support SQL access, warehouse data participates in the same Fabric environment, and OneLake creates a common storage fabric. The exam therefore rewards the ability to distinguish the architectural center of gravity rather than treating the two choices as mutually exclusive technologies.

A useful diagnostic is to write five requirements before choosing anything: primary write pattern, primary transformation engine, primary query language, primary serving audience, and lifecycle/governance constraints. If your design cannot explain those five items, the lakehouse-versus-warehouse decision is premature.

OneLake changes the meaning of “where the data lives”

Traditional data architectures often create multiple physical copies because different engines expect different storage. Fabric’s OneLake model encourages a different mental model: the organization has a unified logical data lake, and workloads should avoid unnecessary movement when a shared or referenced dataset can meet the requirement.

For DP-700, this is important because architecture questions can hide a data-movement problem inside what looks like a storage problem. If a scenario says a team needs to use data that already exists elsewhere in OneLake, ask whether a shortcut is more appropriate than another ingestion pipeline. A shortcut can provide a logical reference to data without creating a fresh copy. That can reduce duplicated storage, synchronization work, and the risk that two teams analyze different versions of the same source.

The decision is not simply “shortcuts are good.” You still have to consider ownership, security, performance, schema evolution, and the dependency created when one workload references another. A shortcut is most attractive when the source is authoritative and the consumer should see the same data. A copied dataset may still be justified when transformation, isolation, retention, or workload independence requires a separate lifecycle.

This is the kind of trade-off DP-700 scenarios reward: minimize movement, but do not remove boundaries that the requirement actually needs.

Lakehouse architecture: think files, tables, and engineering flexibility together

A Fabric lakehouse combines data-lake storage patterns with managed table semantics. For a data engineer, the practical value is not the marketing phrase “best of both worlds.” The value is that raw or lightly processed files, Delta tables, Spark transformations, notebooks, SQL access, and downstream analytics can participate in one engineering workflow.

The lakehouse is a strong center for medallion-style processing. A bronze layer can preserve source fidelity and ingestion history. A silver layer can standardize schemas, clean values, deduplicate records, apply business keys, and enforce quality rules. A gold layer can shape data for consumption. DP-700 questions may not always use the bronze/silver/gold vocabulary explicitly, but the underlying idea appears often: separate raw ingestion from trusted transformed data and from serving structures.

Do not turn medallion architecture into a three-folder ritual. The layers exist to create different guarantees. Bronze is valuable because it helps you replay or investigate ingestion. Silver is valuable because downstream consumers should not repeatedly solve the same quality problems. Gold is valuable because serving models should be optimized for how they are queried. If your layers do not change the data guarantee, they are only extra hops.

A lakehouse also encourages you to think about file size, partitioning, table maintenance, and Spark execution. Many small files can increase metadata and scan overhead. Poor partition choices can create skew or excessive partitions. Transformations that repeatedly rewrite broad tables can become expensive. Architecture and optimization therefore meet directly: the storage choice creates operational responsibilities.

Warehouse architecture: design for relational serving, not just SQL familiarity

A Fabric warehouse becomes compelling when the primary model is relational and the main engineering and consumption patterns are naturally expressed in SQL. Warehouses fit dimensional models, governed reporting structures, star schemas, and workloads where analysts and BI teams expect stable tables, relationships, and predictable query behavior.

The exam-relevant distinction is not that a warehouse is “for clean data” and a lakehouse is “for raw data.” Both can contain curated structures. The better distinction is how the workload is shaped and operated. A warehouse-centered design emphasizes relational modeling, T-SQL-based transformation, constraints imposed by the analytical model, and serving patterns that are comfortable for SQL-oriented teams.

When designing a star schema, think in terms of grain before dimensions and measures. A fact table must have a clear statement of what one row represents. Dimensions should carry descriptive context at the appropriate level. Slowly changing attributes, surrogate keys, late-arriving facts, unknown members, and incremental loads are data-engineering concerns, not just BI concerns. A scenario that asks how to load a warehouse should make you think about these semantics before you think about a pipeline activity.

The warehouse is especially useful when multiple reports depend on the same conformed business definitions. If every downstream team creates its own “customer,” “order,” or “revenue” logic, the architecture has failed even if every query runs quickly.

The strongest Fabric design may use both

A common exam trap is assuming that selecting a lakehouse means rejecting a warehouse. Real Fabric solutions often use a lakehouse for engineering and a warehouse for curated relational serving, or they use separate lakehouses and warehouses for different teams and workload boundaries.

Consider an ecommerce platform. Raw web events, JSON clickstream data, partner files, and high-volume operational extracts arrive into a lakehouse. Spark notebooks standardize schemas, resolve malformed records, and enrich events. The organization then loads curated order, customer, inventory, and finance structures into a warehouse for governed reporting. That is not duplication for its own sake; the second store exists because the serving workload has a different model and different guarantees.

Now change the requirement. Suppose analysts can query curated Delta tables directly and no separate SQL-centric semantic boundary is needed. Adding a warehouse merely because “gold data belongs in a warehouse” would add another load, another dependency, and another place to troubleshoot. The correct architecture is the simplest one that meets the actual consumption pattern.

For practice, take a scenario from DP-700 practical preparation and force yourself to justify every storage boundary. If you cannot state what guarantee a boundary adds, remove it from the design and see whether anything important breaks.

Scenario reasoning: append-heavy operational data

Imagine a manufacturer streams machine telemetry and also receives periodic maintenance files. Engineers need to perform large-scale transformations, data scientists need access to detailed history, and the BI team needs hourly equipment-health summaries.

A lakehouse is a natural architectural center because the data is append-heavy, mixed in format, and engineering-oriented. Raw telemetry can be retained with minimal transformation. Maintenance files can land in an ingestion layer. Spark can normalize device identifiers, standardize timestamps, and derive operational features. Curated Delta tables can support downstream SQL and analytical access.

The difficult part is not choosing “lakehouse.” The difficult part is finishing the architecture. What is the partition strategy? How will late telemetry be handled? How are duplicate events detected? What happens when device firmware changes the schema? Which layer owns data-quality rules? How are failed batches replayed? How will the engineering team monitor table growth and query performance?

An exam answer that chooses the correct store but ignores these lifecycle issues may still be incomplete. DP-700 expects the architecture to survive operation, not just initial ingestion.

Scenario reasoning: finance reporting with governed dimensions

Now consider a finance department that receives already-structured ERP extracts. The team builds monthly and daily reporting around conformed dimensions, requires controlled business definitions, and primarily uses T-SQL. Analysts care more about consistent relational structures than direct access to raw files.

A warehouse-centered solution is easier to defend. The design should establish fact-table grain, dimension keys, incremental loading logic, and data-quality controls. A pipeline can stage incoming data, transformations can apply business rules, and the serving schema can expose a stable relational contract to reporting.

The architecture still needs engineering discipline. A full reload may be simple but unacceptable at scale. An incremental load must identify changed records correctly. A slowly changing dimension process must preserve or overwrite history according to the business requirement. Finance data may require restricted access, auditability, and careful separation of development and production.

Notice how the same DP-700 domains interact: storage architecture, loading pattern, security, orchestration, monitoring, and optimization all show up in one scenario.

Scenario reasoning: shared enterprise data without another copy

Suppose a central data platform team owns a certified customer dataset in a separate Fabric item. A marketing engineering team needs that customer data alongside campaign events. The requirement says the marketing team should use the central team’s current data and should not create another independently refreshed copy.

The important clue is ownership. If the central dataset remains authoritative, a OneLake shortcut may be more suitable than a new ingestion pipeline. The marketing lakehouse can reference the source while maintaining its own campaign data and transformations.

However, you should then reason about dependency. If the source team changes a schema, marketing may be affected. Security must still be correct. Performance expectations must still be tested. The shortcut reduces data movement, but it does not eliminate architecture governance.

This scenario illustrates a broader DP-700 lesson: “do not copy data” is not the same as “do not manage the dependency.” Logical reuse changes the operational problem; it does not make the operational problem disappear.

Partitioning is a query and maintenance decision

Partitioning can improve pruning and operational manageability, but poor partitioning can make a table worse. A column with very high cardinality may create too many small partitions. A column rarely used in filters may provide little benefit. A partition key that matches time-based ingestion and common query predicates can be effective, but even date-based partitioning should reflect data volume and access patterns.

For exam scenarios, look for evidence rather than habits. If queries usually filter by date and data arrives chronologically at high volume, a time-oriented partition strategy is plausible. If a table is small, the overhead may not be justified. If users mostly filter by customer and there are millions of customers, partitioning by customer could create an operational disaster.

The same evidence-driven approach applies to file compaction and table maintenance. Small-file accumulation can result from frequent micro-batches. Maintenance should be planned around how data is written and queried. Optimization is a feedback loop: observe, identify the bottleneck, apply the smallest effective change, and verify the result.

Security belongs inside the architecture diagram

A storage diagram without identity and access arrows is incomplete. Fabric workspaces, items, OneLake data, SQL endpoints, and downstream consumers all participate in an authorization model. DP-700 scenarios can test whether you choose a technically functional design that accidentally grants too much access.

Apply least privilege at the right boundary. Separate engineering administration from consumer access. Avoid solving every access problem by putting users into a broad workspace role. Understand whether the requirement is item-level, workspace-level, row-level, object-level, or data-level. If a central dataset is shared, make sure the sharing mechanism does not expose unrelated assets.

Security also affects architecture choice. A separate warehouse or workspace may be justified when a workload requires a stronger governance boundary, but isolation has a cost. More boundaries mean more deployments, more permissions, more monitoring, and more orchestration. The correct answer balances governance with operational simplicity.

Loading patterns should match the chosen architecture

A lakehouse or warehouse is only useful if the loading pattern preserves correctness. Full loads are easy to reason about but can become wasteful. Incremental loads reduce work but require a trustworthy change-detection mechanism. Streaming loads reduce latency but add state, ordering, watermark, and late-event concerns.

For batch ingestion, identify the source of truth for “what changed.” That might be a modified timestamp, a monotonically increasing key, change data capture, or a source-provided watermark. Store the high-water mark only after the batch has completed successfully. Design the load so a retry does not duplicate data. Idempotency is an architecture property, not a cleanup task.

For a warehouse, incremental loading must also maintain relational semantics. For a lakehouse, merges and upserts must be designed around table keys and concurrency. In both cases, a robust load separates extraction state from business transformation state so failures can be diagnosed accurately.

The DP-700 ingestion and transformation guide goes deeper into those loading decisions. Architecture questions become much easier when you can connect the chosen store to a concrete ingestion pattern.

Orchestration should expose the architecture, not hide it

Pipelines, notebooks, and Dataflows Gen2 should not be chosen by habit. A pipeline is useful for coordinating activities and dependencies. A notebook is useful when code-centric Spark or data-engineering logic belongs together. A dataflow can be effective for Power Query-oriented transformation. The exam may ask you to choose among these tools based on the skill set, transformation complexity, scheduling need, or reuse requirement.

Good orchestration makes stages visible. You should be able to tell whether ingestion failed, transformation failed, a validation gate failed, or a serving load failed. If one giant notebook performs every step, troubleshooting becomes harder. If dozens of pipeline activities duplicate small pieces of logic, maintenance also becomes harder. Use boundaries that correspond to meaningful operational stages.

Parameters and dynamic expressions can make orchestration reusable, but do not confuse reuse with opacity. Metadata-driven frameworks are powerful only when engineers can still explain what a particular run will do and why.

Monitoring ties architecture choices back to evidence

Architecture is not finished when the first load succeeds. Monitor pipeline duration, failure rates, data freshness, table growth, query latency, Spark execution patterns, and capacity pressure. Establish expected ranges before an incident so you can distinguish normal variation from degradation.

If query performance worsens, do not immediately add resources. Check whether data volume changed, partitions stopped pruning, small files increased, transformations caused skew, or the serving model changed. If a warehouse load slows down, inspect whether the incremental predicate still limits data or whether a dimension process is scanning too much history.

DP-700’s monitoring and optimization domain is intentionally connected to the architecture domains. The exam is looking for the candidate who can reason from symptom to the relevant layer instead of applying a generic performance fix.

Common architecture mistakes DP-700 scenarios expose

One mistake is product absolutism: “lakehouse for engineers, warehouse for analysts” is too shallow. Another is copying data automatically whenever teams cross workspace boundaries. A third is choosing partition keys by intuition rather than by access pattern. A fourth is using full reloads because they are easier during development and never redesigning them for production scale.

Candidates also confuse an architecture diagram with an implementation plan. A box labeled “lakehouse” does not explain schema evolution, deduplication, security, data quality, retention, or recovery. A warehouse box does not explain fact grain or slowly changing dimensions. A shortcut does not explain what happens when the upstream owner changes the schema.

The best exam response is usually the one that satisfies the explicit constraints with the least unnecessary moving parts while preserving data correctness and operability.

A hands-on architecture drill

Build a small Fabric practice solution with two source types: one structured relational extract and one semi-structured file source. Land both into a lakehouse. Preserve a raw representation, then create curated Delta tables. Add a transformation that standardizes business keys and handles a duplicate record deliberately introduced into the source.

Next, decide whether a warehouse adds value. If yes, create a small star schema with one fact and two dimensions. Load it incrementally. If no, write down why curated lakehouse tables are sufficient. The act of rejecting an unnecessary warehouse is as educational as creating one.

Create a second workspace or data item and practice referencing data through a shortcut. Record which permissions are required and what happens when the source schema changes. Then run a query before and after creating a deliberately poor partition or many small files. You do not need enterprise volume to understand the mechanics; you need observable cause and effect.

Finally, draw the architecture from memory and mark each point where data is copied, transformed, secured, validated, and monitored. Every arrow should have a reason.

Lakehouse and warehouse readiness checklist

Before considering this topic exam-ready, you should be able to explain why a scenario belongs in a lakehouse, a warehouse, or a combination without relying on a slogan. You should be able to identify the role of OneLake and shortcuts, explain the guarantees of medallion layers, design a fact table at a stated grain, distinguish full from incremental loading, and recognize when streaming changes the state-management problem.

You should also be able to discuss partitioning, small-file behavior, table maintenance, schema evolution, security boundaries, orchestration choices, and monitoring. Most importantly, you should be able to connect these concerns. A partition strategy that improves one query but destroys ingestion performance is not a successful optimization. A shortcut that saves storage but violates governance is not a successful architecture. A warehouse that creates a perfect star schema but duplicates data without a business need may be unnecessary.

DP-700 architecture questions become manageable when you treat every answer as a set of trade-offs. Start with requirements, choose the simplest architecture that fits them, make data movement explicit, secure the boundaries, and define how you will know the design still works after it has been running for months. That is the difference between recognizing Fabric features and thinking like a Fabric data engineer.

Decision drill: choose the serving layer from the question language

One way to improve architecture judgment is to translate scenario wording into architectural pressure without immediately translating it into a product. If the scenario emphasizes large-scale engineering, notebooks, semi-structured files, and flexible transformation, write “engineering-oriented storage with file and table access.” If it emphasizes dimensional models, governed SQL access, and stable relational serving, write “relational serving contract.” If it emphasizes reusing an authoritative dataset with no extra copy, write “logical reuse with preserved ownership.” Only then map those pressures to lakehouse, warehouse, or shortcut choices.

This two-step method prevents feature recognition from dominating reasoning. It also helps when the answer is a combination. A lakehouse can be the engineering system of record while a warehouse provides a relational serving boundary. The question is whether both roles are actually required.

During practice, take ten architecture questions and cover the product names in the answers. Write the requirement in neutral language first. Then uncover the answers. If one option matches your neutral requirement with fewer unnecessary stages, you have a defensible choice.

Table design matters more than the storage icon

A strong architecture can still fail because tables are poorly designed. In a lakehouse, define keys, expected uniqueness, nullability, and update behavior for curated Delta tables. In a warehouse, define fact grain before writing the load. If a fact table mixes order-level and line-level rows, no amount of performance tuning will repair the semantic ambiguity.

Consider a sales model. If one fact row represents one order line, measures such as quantity and extended amount are naturally additive across many dimensions. If the table sometimes stores order headers and sometimes lines, a simple SUM can double-count. Architecture questions may hide this problem behind storage terminology.

For dimensions, decide whether changes overwrite prior values or create new historical versions. A customer changing region can affect trend analysis. If the business wants reports to show the customer’s region at the time of each sale, the dimension process must preserve history and the fact must reference the correct version. These are data-engineering choices, not cosmetic modeling details.

Schema evolution should be planned rather than discovered through failures

Sources change. A JSON payload adds a nested field, a column changes type, a partner renames an attribute, or an ERP extract introduces a new code. A robust lakehouse or warehouse architecture defines which changes are tolerated automatically and which should stop the load.

Raw landing often benefits from preserving source fidelity so that a bad transformation can be replayed after the issue is understood. Curated layers should be stricter because downstream users rely on their contract. If a required key disappears, silently loading null values can be worse than failing visibly. If an optional column is added, an ingestion process may be able to absorb it safely.

For DP-700 practice, write a small data contract for one table: required columns, data types, uniqueness, allowed nulls, freshness, and expected volume. Then decide how each kind of schema change should be handled. That exercise makes architecture operational.

Performance tuning should preserve correctness first

It is easy to optimize the wrong thing. A full reload can be made faster with more compute, but an incremental design may remove most of the work. A highly partitioned table can improve one filter but create thousands of tiny files. A cached intermediate result can help repeated computation but waste resources if the data changes constantly.

Use a hierarchy when troubleshooting: verify correctness, verify that unnecessary data is not being processed, verify data layout and partition behavior, identify expensive transformations or joins, and only then decide whether more capacity is justified. This prevents infrastructure scaling from hiding a design defect.

Performance should also be tested with representative data. A pipeline that is instant on ten thousand rows may behave differently at a billion. You do not need production volume in a study lab, but you should understand which characteristics change with scale: file count, partition skew, shuffle size, concurrency, and scan volume.

Architecture questions often contain governance clues

Words such as certified, shared, sensitive, regional, regulated, development, production, and least privilege are not background decoration. They can determine where a workspace or data boundary belongs.

Suppose a finance dataset is used by many departments but only the finance engineering team should modify it. A shared authoritative serving layer is more defensible than copying the same logic into every consumer workspace. Suppose a development team needs realistic test data but production contains sensitive customer attributes. A separate environment with masked or synthetic data may be more appropriate than broad production access.

Governance is therefore part of architecture selection. The best design is not simply the one with the fewest components. It is the one with the fewest components consistent with the required ownership and control boundaries.

Recovery planning exposes weak architecture early

Ask how each store would be rebuilt. If a curated table is corrupted, can it be regenerated from raw data and versioned transformation logic? If a warehouse load partially commits, can the process identify the affected batch? If a shortcut source is unavailable, does the consumer have a defined dependency and recovery expectation?

A recoverable architecture separates source preservation, transformation logic, control state, and publication state. That separation makes failures diagnosable. An unrecoverable architecture often combines everything into a sequence where the only repair is manual data editing.

For study, intentionally delete or corrupt a small curated table in a lab and rebuild it from earlier layers. Record which metadata and code you needed. Recovery exercises teach architecture faster than perfect happy-path runs because they reveal which assumptions were never made explicit.

How to explain an architecture answer in one paragraph

When practicing, force yourself to justify the answer in a fixed pattern: requirement, chosen architecture, why it fits, why the nearest alternative is weaker, and one operational consideration.

For example: “Use a lakehouse as the engineering store because the solution ingests mixed file formats, requires Spark transformation, and preserves detailed history. A warehouse-only design would force the engineering workload into a relational serving model without adding a stated benefit. Partition curated Delta tables by the dominant time access pattern and monitor file layout as ingestion frequency grows.”

If you cannot write that paragraph, you probably recognize the product but do not yet understand the decision. DP-700 scenarios become easier when your reasoning can be compressed this way.