DP-700 - Ingest and Transform Data (33% of the exam) - Section 2.4
Choose an appropriate data store for a workload, including Lakehouse, Warehouse, and Eventhouse.
Compare a Lakehouse, a Warehouse, and an Eventhouse with its KQL database to identify which store fits a given workload. Weigh the Spark and Delta Lake surface of a Lakehouse against the T-SQL surface of a Warehouse and its SQL analytics endpoint when matching read-heavy or write-heavy patterns.
Lakehouse versus WarehouseEventhouse and KQL databaseSpark and Delta versus T-SQL surfaceread versus write workload fitSQL analytics endpoint
Practice question for this objective
lock_openFree sampleIngest and Transform Datahard
A central data team in Microsoft Fabric keeps curated dimension tables in a Warehouse and large raw event Delta tables in a separate Lakehouse in the same workspace. An analyst must run a single T-SQL query that joins the Warehouse dimensions to the Lakehouse event tables and writes the joined result into a managed table for nightly reporting. The team works only in T-SQL and wants one store to author and persist this cross-item query. Which data store should host the query and its output table?
AA Microsoft Fabric Lakehouse queried through its SQL analytics endpoint, because the endpoint can join the Warehouse tables and persist the joined managed table from the same T-SQL session.
BA Microsoft Fabric Warehouse, because its T-SQL engine can issue a cross-database query that joins its own tables to the Lakehouse SQL analytics endpoint and then write the joined result into a managed table.check_circle Correct
CA Microsoft Fabric Lakehouse written by a PySpark notebook, because Spark can read the Warehouse and Lakehouse tables and persist the joined result as a managed Delta table for reporting.
DA Microsoft Fabric Eventhouse with a KQL database, because a KQL cross-cluster query joins the Warehouse and Lakehouse tables and materialises the joined result for reporting.
Choose a Warehouse when a T-SQL team must cross-query a Lakehouse endpoint and persist the joined result, because only the Warehouse can both reference and write. The decisive factors are a T-SQL-only team, a single query that spans two items, and a persisted managed output table; the Warehouse T-SQL engine performs cross-database queries against a Lakehouse SQL analytics endpoint and is a read-write store, whereas the Lakehouse endpoint is read-only and cannot persist the result.
Why A is wrong: The Lakehouse SQL analytics endpoint is read-only, so although it can reference other items it cannot persist a managed table from the joined result, which means it fails the write half of the requirement.
Why B is correct: The Warehouse T-SQL engine supports cross-database queries that reference a Lakehouse SQL analytics endpoint in the same workspace, and it is a full read-write store, so it can both join across items and persist the managed output table.
Why C is wrong: Spark could combine the data, but the team works only in T-SQL and asked for a single T-SQL query, so introducing a PySpark notebook changes the engine rather than meeting the stated skills and authoring requirement.
Why D is wrong: An Eventhouse targets append-only telemetry queried in KQL, not T-SQL joins across a Warehouse and a Lakehouse, so it neither matches the team skills nor serves a curated cross-item reporting join.
Answers that look right on this material and are not. Each one is a distractor from a different question in the DP-700 bank for this domain.
A Microsoft Fabric Warehouse, because its read-write T-SQL surface and stored procedures give the most governed home for the Spark-authored feature Delta tables.
errorWhy it is wrong: A Warehouse adds a T-SQL write surface and stored-procedure governance that this workload never uses, and Spark cannot author its tables natively, so it imposes overhead the requirement explicitly says is unnecessary.
A Microsoft Fabric Warehouse, because Spark notebooks can write the curated Delta tables straight into the Warehouse while T-SQL serves the read queries.
errorWhy it is wrong: Spark notebooks cannot write directly into a Warehouse as their primary engine; the Warehouse is a T-SQL store, so this misroutes a Spark-centric engineering workload that also needs to hold unstructured files.
A Microsoft Fabric Warehouse, because its T-SQL surface and clustered indexes give the fastest interactive aggregation over high-volume time-series event data.
errorWhy it is wrong: A Warehouse serves curated analytical T-SQL workloads well, but it is not optimised for very high-rate event ingestion or the time-window and KQL semantics the analysts need, so it fits the requirement worse than a purpose-built event store.