Launch Deal · 50% off 1st month ·

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.

See plans

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.