DP-600 prepare data explained
Prepare data is the largest DP-600 domain at 45–50%. It covers three skills: getting data into or connecting to it from Fabric, transforming it into an analysis-ready shape, and querying it with the visual query editor, SQL, KQL and DAX. If you only have time to go deep in one place, go deep here and practise in a real workspace.
Get data
Choosing a data store
The first decision in many scenarios is which item holds the data.
| Item | Engine and language | Writes | Best fit |
|---|---|---|---|
| Lakehouse | Spark for files and Delta tables; read-only T-SQL through the SQL analytics endpoint | Spark, pipelines, dataflows | Mixed structured and unstructured data, notebook work |
| Warehouse | T-SQL | Full T-SQL DML, multi-table transactions | Relational, SQL-first teams |
| Eventhouse | KQL | Streaming and batch ingestion | Telemetry, logs, time-series |
If the scenario says “T-SQL UPDATE across several tables with rollback”, it is a warehouse. If it says “files, notebooks and Delta tables”, it is a lakehouse. If it says “streaming events, query by time window”, it is an eventhouse.
Ways to bring data in
- A shortcut exposes data in another location, such as another lakehouse or external storage, in OneLake without copying it. Use it when you want one copy of the data and no refresh job.
- Mirroring replicates data from supported operational databases into OneLake as Delta, so it stays near real time.
- A pipeline copy activity moves data on a schedule or trigger, good for bulk loads and orchestration.
- Dataflow Gen2 gives a low-code Power Query transformation and loads to a lakehouse, warehouse or other destination.
- Notebooks are for code-based transformations in Spark.
- In a warehouse,
COPY INTOloads files from storage and cross-database queries read other items in the workspace.
To discover data, use the OneLake catalog, which lists items you can access, and the Real-Time hub, which lists streams and events. A data connection stores source credentials and settings so that pipelines and dataflows can reuse them. OneLake integration can also expose eventhouse and semantic model data as Delta tables in OneLake, so other engines can read it without copying.
Transform data
Star schema and denormalising
Analytics workloads favour a star schema: a central fact table of measurable events with foreign keys to dimension tables that describe who, what, where and when. Each dimension gets a surrogate key; the fact table holds numeric measures and keys. If a source has snowflaked dimensions (product to subcategory to category), denormalise them by joining into one flat dimension. This reduces joins in queries and gives a cleaner semantic model.
T-SQL objects
A warehouse supports views, scalar and table-valued functions, and stored procedures. The SQL analytics endpoint of a lakehouse supports views, functions and stored procedures too, but not data modification on tables. Use a view to present a cleaned shape without storing it, and a stored procedure to package a repeatable load.
Common transformation tasks
- Duplicates: rank with
ROW_NUMBER() OVER (PARTITION BY key ORDER BY load_date DESC)and keep row 1, or useDISTINCTwhen whole rows match. In Spark usedropDuplicates. - Missing values and nulls:
COALESCEorISNULLto substitute; a filter to remove; or a decision to keep them and handle them in the model. - Data types:
CASTorCONVERTin T-SQL, and type changes in Power Query or Spark, ideally early so downstream joins match. - Joins and merges: inner for matching rows, left to keep all rows from one side, anti joins to find unmatched rows.
- Aggregate and filter:
GROUP BYwithHAVINGfor filtering aggregates;WHEREfor filtering rows before aggregation.
Query and analyze data
You need to be fluent in four ways of asking a question.
| Tool | Example | Notes |
|---|---|---|
| Visual query editor | Drag steps to filter, group and join | Generates SQL for a warehouse; no code |
| SQL | SELECT region, SUM(amount) FROM sales GROUP BY region | Warehouse and SQL analytics endpoint |
| KQL | Events | where Time > ago(1h) | summarize count() by Device | Eventhouse; pipe-based syntax |
| DAX | EVALUATE SUMMARIZECOLUMNS(...) | Semantic models; runs in the DAX query view |
KQL reads top to bottom, with each operator taking the table on its left. T-SQL has a fixed clause order. DAX queries start with EVALUATE. Questions often give you a scenario and ask you to complete the missing keyword.
Example questions
Question 1. Sales data lives in an external storage account. You want it to appear as tables in a Fabric lakehouse without copying it or scheduling a refresh. What should you create?
- A. A pipeline copy activity that runs hourly
- B. A Dataflow Gen2 loading to the lakehouse
- C. A shortcut in the lakehouse
- D. A stored procedure in the SQL analytics endpoint
Show answer
Answer: C
A shortcut makes the external data visible in OneLake in place, with no copy and no load job. A copy activity and a Dataflow Gen2 both create a separate copy that must be refreshed, and a stored procedure does not connect to external storage by itself.
Want more questions like this? Full DP-600 practice tests →
Question 2. A customer table in a warehouse contains duplicate rows for the same CustomerID, differing only in LoadDate. You must keep the most recently loaded row for each customer. Which approach is correct?
- A. Use ROW_NUMBER over CustomerID ordered by LoadDate descending and keep row 1
- B. Use SELECT DISTINCT on all columns
- C. Use SELECT TOP 1 from the table
- D. Wrap LoadDate in COALESCE
Show answer
Answer: A
ROW_NUMBER partitioned by CustomerID and ordered by LoadDate descending numbers rows within each customer, and keeping row number 1 retains the latest. DISTINCT does nothing here because the LoadDate values differ. TOP 1 returns a single row for the whole table, and COALESCE substitutes nulls, it does not remove duplicates.
Want more questions like this? Full DP-600 practice tests →
Question 3. Product data has three related tables: Product, Subcategory and Category. The semantic model should have a simple star schema with a single dimension. What should you do?
- A. Keep all three tables and chain the relationships
- B. Create many-to-many relationships between them
- C. Delete Subcategory and Category and keep only Product
- D. Join them into one Product dimension
Show answer
Answer: D
Joining the three tables into one flat Product dimension denormalises the snowflake and gives the fact table a single relationship. Keeping three tables retains the snowflake, a many-to-many relationship adds complexity without need, and removing the category data would lose attributes that reports need.
Want more questions like this? Full DP-600 practice tests →
Continue with semantic models explained.