Ingest and Transform Data · Topic 2 of 6
Choosing a Data Store and a Transformation Tool
DP-700 choices: lakehouse, warehouse, Eventhouse or SQL database, and Dataflow Gen2, notebooks, KQL or T-SQL for transforming batch data in Fabric.
- 5 min
- Lakehouse: files and Delta tables, Spark writes, read-only SQL analytics endpoint.
- Warehouse: full T-SQL DML, multi-table transactions, stored procedures.
- Eventhouse: time-series and event data queried with KQL.
What it is
Fabric offers several places to store analytical data and several engines to transform it. All of them keep data in OneLake, mostly as Delta Parquet, so the choice is about who works with the data, in which language, and with what workload, not about copying data between silos.
Many DP-700 questions hide the answer in the skills of the team ("SQL developers", "data scientists using PySpark", "business analysts") and the shape of the data (files, relational tables, time series).
How it works
Choose an appropriate data store
| Store | Data and workload | Write with | Read with |
|---|---|---|---|
| Lakehouse | Files of any format plus Delta tables; large-scale data engineering | Spark (PySpark, Spark SQL), Dataflow Gen2, pipelines | Spark, and T-SQL read-only through the SQL analytics endpoint |
| Warehouse | Relational tables for SQL teams; full DML and multi-table transactions | T-SQL, pipelines, Dataflow Gen2 | T-SQL, Spark (read) |
| Eventhouse (KQL database) | Time series, logs, telemetry, high-volume event data; fast queries over fresh data | Eventstream, KQL ingestion, update policies | KQL, and T-SQL for simple queries |
| SQL database | Operational (OLTP) application data | T-SQL | T-SQL; replicated to OneLake for analytics |
Key differences to remember:
- The SQL analytics endpoint of a lakehouse is read-only.
INSERT,UPDATEandDELETEfail there; write with Spark or use a warehouse. - A warehouse supports transactions that span several tables,
CREATE TABLE AS SELECT, stored procedures andCOPY INTO. - A lakehouse is the natural home for unstructured and semi-structured files such as JSON, CSV and images in its Files area.
- An Eventhouse is optimized for data that arrives continuously and is filtered by time.
A common design uses a lakehouse for bronze and silver and either a lakehouse or a warehouse for gold, depending on the team.
Choose a transformation tool
| Tool | Language | Choose it when |
|---|---|---|
| Dataflow Gen2 | Power Query (M), visual editor | Low-code transformations, many connectors, analysts maintain them |
| Notebook | PySpark, Spark SQL, Scala, SparkR | Large volumes, complex logic, semi-structured data, reuse of Python libraries |
| T-SQL | Warehouse SQL | Set-based transformations by SQL developers, stored procedures, gold tables in a warehouse |
| KQL | Kusto Query Language | Transforming event and time-series data inside an Eventhouse, update policies, materialized views |
Some quick rules:
- Data volume and complexity point to notebooks.
- No-code or low-code and Power Query skills point to Dataflow Gen2.
- A warehouse target and SQL skills point to T-SQL, for example
CREATE TABLE AS SELECTin a stored procedure. - Streaming or log data already in an Eventhouse points to KQL.
A pipeline usually orchestrates the chosen tool, so it is rarely the transformation answer itself; its Copy activity moves data without changing it much.
Cross-engine access
Every engine can read the others' data because it is all Delta in OneLake:
- T-SQL in a warehouse can query a lakehouse table in the same workspace with a three-part name, such as
SELECT * FROM SalesLakehouse.dbo.orders. - Spark reads warehouse tables and Eventhouse tables (with OneLake availability on) through OneLake.
- An Eventhouse queries lakehouse Delta tables through OneLake shortcuts.
A worked example
A company has three teams. Data engineers process 2 TB of JSON clickstream files a day with PySpark, and SQL developers build the finance gold tables with stored procedures that need multi-table transactions. Analysts clean a small supplier spreadsheet in Power Query.
The clickstream goes to a lakehouse transformed by notebooks, and the finance gold tables go to a warehouse built with T-SQL. The supplier file uses Dataflow Gen2 with the warehouse or lakehouse as its data destination. Reports read all three through OneLake.
How the exam asks about this
- "SQL developers must insert and update data with T-SQL and need multi-table transactions." Warehouse.
- "Store raw JSON and images, then process them with PySpark." Lakehouse.
- "Analyze billions of telemetry events by time with sub-second queries." Eventhouse with KQL.
What to remember for the exam
- Lakehouse: files and Delta tables, Spark writes, read-only SQL analytics endpoint.
- Warehouse: full T-SQL DML, multi-table transactions, stored procedures.
- Eventhouse: time-series and event data queried with KQL.
- Dataflow Gen2 for low-code, notebooks for scale and complexity, T-SQL for SQL teams, KQL for event data.
- Three-part names let a warehouse query lakehouse tables in the same workspace.
If a scenario needs T-SQL UPDATE or INSERT on data in a lakehouse, the SQL analytics endpoint is not the answer. It is read-only; use Spark, or move the table to a warehouse.
Common mistakes
Picking a warehouse for unstructured files. The warehouse holds tables only; files belong in a lakehouse.
Picking a pipeline as the transformation tool. A pipeline orchestrates and copies; the transformation itself runs in a dataflow, notebook, stored procedure or KQL.
Assuming each store keeps a private copy of data. All Fabric stores write to OneLake, so other engines can read the data without copying it.
Check yourself
Why can't you run
UPDATEthrough a lakehouse's SQL analytics endpoint?The endpoint is read-only; changes to lakehouse tables are made with Spark or other lakehouse writers.
Which tool fits a low-code transformation maintained by business analysts?
Dataflow Gen2, which uses the Power Query editor.
Which store suits high-volume telemetry queried by time?
An Eventhouse (KQL database), queried with KQL.
Topic 2 of 6 in Ingest and Transform Data
Test yourself on Ingest and Transform Data
The full Microsoft DP-700 question bank has 645 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.