2.3 — Choose a Data Store and a Transformation Tool
DP-700 Domain 2 · Skill 2.3
What You Need to Know
Many DP-700 questions start with a team's skills, data shape and access pattern, then ask where to store the data or what to transform it with. Every Fabric store keeps its data in OneLake. Eventhouse data is the exception: it reaches OneLake only if you turn on OneLake availability.
Choose a data store
| Store | Best for | Write with | Read with |
|---|---|---|---|
| Lakehouse | Big data, data engineering and ML on structured, semi-structured and unstructured data | Spark (notebooks, Spark job definitions), pipelines, dataflows | Spark, T-SQL (read-only SQL analytics endpoint), Power BI. KQL can query it through a shortcut |
| Warehouse | Enterprise data warehouse and SQL-based BI with multi-table transactions and full DML | T-SQL, pipelines, dataflows | T-SQL, Spark (read), Power BI |
| Eventhouse (KQL databases) | High-volume streaming, time-series, log and telemetry data that needs fast interactive analysis | Eventstreams, ingestion, KQL commands | KQL and T-SQL |
| SQL database | Operational OLTP apps that need enforced keys and high concurrency; vector data for AI | T-SQL | T-SQL, and analytics through its automatic mirror to OneLake |
Ways to tell them apart:
- Write only with Spark, read with SQL, Spark and KQL: a lakehouse, with a KQL shortcut or OneLake availability for the KQL side.
- T-SQL INSERT, UPDATE and DELETE across several tables in one transaction: a warehouse. The lakehouse SQL analytics endpoint is read-only.
- Millions of events per minute, queried by time window: an eventhouse.
Choose a transformation tool
| Tool | Skill set | Strengths | Limits |
|---|---|---|---|
| Dataflow Gen2 | Power Query (M), low-code | 150+ connectors, 300+ transformations, visual data profiling; good for analysts | Lower ceiling for very large or very complex logic |
| Notebook (PySpark, Spark SQL, Scala, R) | Code | Unlimited complexity, any library, very large volumes, ML | Needs Spark skills |
| T-SQL (warehouse stored procedures, CTAS, INSERT ... SELECT) | SQL | Set-based transformations inside the warehouse, close to the data; cross-database queries with three-part names | Only data that the warehouse or SQL endpoints can reach |
| KQL (update policies, materialized views) | KQL | Transforms streaming data as it's ingested | Eventhouse only |
| Copy activity / Copy job | No code | Moving data with light changes (type conversion, column mapping) | Not a transformation engine |
Ways to tell them apart:
- Analysts who know Power Query need to cleanse data with little code: Dataflow Gen2.
- Engineers who prefer Python with heavy cleansing (deduplication, missing values, standardizing values): a notebook.
- SQL developers loading a star schema in a warehouse: T-SQL stored procedures, run by a pipeline.
- Moving data without changing it ("only convert the file format"): a Copy activity or Copy job, not a dataflow.
Key Concept: Two questions settle it: who writes the code, and in which language? and how is the data shaped and accessed? The team's stated skills almost always point to the answer.
Exam Traps
- Choosing a lakehouse when the requirement is multi-table T-SQL transactions (its SQL endpoint is read-only)
- Choosing a warehouse for unstructured files such as images or JSON documents
- Using a dataflow for heavy, very large transformations by a Python team
- Forgetting that eventhouse data reaches OneLake only after OneLake availability is turned on
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Fabric decision guide: choose a data store, Microsoft Learn
- Fabric decision guide: copy activity, Copy job, dataflow, Eventstream, or Spark, Microsoft Learn
- Warehouse and lakehouse decision guide, Microsoft Learn
- Eventhouse OneLake availability, Microsoft Learn