What's changed: Deepened DP-700 Chapter 2 (ja figures; comparison tables/scenarios/FAQ/traps/deep paragraphs in all sections)
2.2Data Transformation (Spark, Notebooks, T-SQL)
Understand transformation in Fabric—Spark notebooks (PySpark/Spark SQL), Dataflows Gen2, T-SQL (Warehouse)—and the medallion architecture (bronze/silver/gold) for staged refinement.
Raw ingested data must be shaped, joined, and aggregated into usable form. Use Spark for large/complex, T-SQL if SQL-savvy, or Dataflow for no-code.
2.2.1Transformation methods and medallion
- Spark notebooks: large/complex transforms with PySpark/Spark SQL (Lakehouse-centric).
- T-SQL (Warehouse): set-based SQL transforms; suits SQL-savvy teams.
- Dataflows Gen2: no-code (Power Query) shaping.
- Medallion: raise quality in stages—bronze (raw) → silver (cleansed/joined) → gold (aggregated/business-ready).
Common on DP-700: large/complex transform = Spark notebooks (PySpark), set-based SQL = T-SQL (Warehouse), no-code shaping = Dataflows Gen2, staged quality = medallion (bronze/silver/gold). Data is stored as Delta tables in OneLake.
Regardless of method, results are stored as Delta in OneLake and readable via the SQL analytics endpoint or Power BI (Direct Lake).
Choose transform methods by strength. Spark notebooks (PySpark, Spark SQL, Scala/R) suit large/complex/ML work and can MERGE (upsert) and write partitions to Lakehouse Delta tables. T-SQL (Warehouse) is set-based, fits SQL-savvy ETL/ELT, and reuses logic via stored procedures. Dataflows Gen2 do no-code shaping with Power Query (small/medium, citizen developers). Design refinement with the medallion architecture—bronze (raw as-is) → silver (cleanse, dedupe, join, standardize types) → gold (aggregate, star schema, business-ready)—storing each layer as a Delta table in OneLake. This makes reprocessing easy and gives downstream (Power BI Direct Lake) consistent reads. Leverage Delta benefits (ACID, time travel, schema evolution) and ingest deltas via MERGE. Decision axes: “large/ML = Spark,” “SQL ELT = T-SQL,” “no-code = Dataflow,” “staged quality = medallion.”
| Method | Language/UI | Best for |
|---|---|---|
| Spark notebook | PySpark/Spark SQL/Scala/R | Large/complex/ML |
| T-SQL (Warehouse) | Set-based SQL | SQL-team ETL/ELT |
| Dataflows Gen2 | Power Query (no-code) | Small/medium, citizen dev |
Scenario: ingest raw logs, dedupe and join, then aggregate to per-department KPIs for BI. → Design with medallion: store raw logs as Delta in bronze, cleanse/join in silver with Spark (or T-SQL), and build aggregate tables in gold. Each layer is Delta in OneLake, and Power BI reads gold fast via Direct Lake.
FAQ: Q. Are all medallion layers mandatory? → A. Not by rule, but bronze/silver/gold is the standard for easier reprocessing, quality control, and downstream consistency. Q. Spark or T-SQL for transforms? → A. Spark for large/complex/ML; T-SQL for SQL-team set processing. Both write Delta in OneLake.
Trap: “put raw data directly in the gold layer” is wrong—raw goes in bronze; gold is aggregated/business-ready. Also “each transform method stores in a different format and can’t be cross-referenced” is wrong—results are Delta in OneLake regardless, enabling cross-use.
2.2.2Section summary
- Transform = Spark (large) / T-SQL (SQL) / Dataflow (no-code)
- Refinement stages = bronze → silver → gold (medallion)
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. You want large, complex transforms with PySpark in Fabric. What do you use?
Q2. In the medallion architecture, which layer is "aggregated and business-ready"?
Q3. A SQL-savvy team wants set-based transforms in the Warehouse. What do they use?